oracle 如何输出使用本机动态 SQL 执行的 SELECT 语句的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14528853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to output result of SELECT statement which is executed using native dynamic SQL?
提问by spin_eight
I have a string which contains SQL SELECTstatement.
I wonder how can I output result of the execution of that statement on the screen, execution will be done using native dynamic SQL(EXECUTE IMMEDIATE).
我有一个包含 SQLSELECT语句的字符串。
我想知道如何在屏幕上输出该语句的执行结果，执行将使用本机动态 SQL( EXECUTE IMMEDIATE) 完成。
example:
例子：
DECLARE
v_stmt VARCHAR2 := 'SELECT * FROM employees';
BEGIN
EXECUTE IMMEDIATE v_stmt; -- ??? how to output result of that select on the screen.
END;
Important remark: structure of table can be any. I have to write a procedure which accepts name of the table as parameter, so I can't hardcode a table structure and don't want to do it.
重要说明：表的结构可以是任意的。我必须编写一个接受表名作为参数的过程，所以我不能对表结构进行硬编码，也不想这样做。
Thanks for responses. Any ideas very appreciated/
感谢您的回复。任何想法非常感谢/
采纳答案by Horus
If you can change that execute immediate into a dbms_sql cursor, then the following solution should be able to help you, as you can get the column names from a dbms_sql cursor:
如果您可以将立即执行更改为 dbms_sql 游标，那么以下解决方案应该可以帮助您，因为您可以从 dbms_sql 游标中获取列名：
https://forums.oracle.com/forums/thread.jspa?threadID=700648
https://forums.oracle.com/forums/thread.jspa?threadID=700648
回答by William Robertson
If you are on Oracle 12c with a 12c client, this should work:
如果您使用 12c 客户端在 Oracle 12c 上，这应该有效：
declare
rc sys_refcursor;
begin
open rc for 'select * from dual';
dbms_sql.return_result(rc);
end;
回答by user2001117
Yes we can execute select statement dynamically.
是的，我们可以动态执行 select 语句。
Let say we have a table test. It has four column Row_id,Name,Ranketc
When we do select * from test;Result will be
假设我们有一张桌子test。它有四列Row_id,Name,Rank等当我们做select * from test;结果将是
Row_id Name Rank
1 R1 5
2 R2 1
3 R3 2
4 R4 4
Now we can use DBMS_SQL package to execute dynamically SELECT Sql Statament.
现在我们可以使用 DBMS_SQL 包动态执行 SELECT Sql Statament。
Code is below:
代码如下：
DECLARE
v_CursorID NUMBER;
v_table VARCHAR2(50):='test';
v_SelectRecords VARCHAR2(500);
v_NUMRows INTEGER;
v_MyNum INTEGER;
v_Myname VARCHAR2(50);
v_Rank INTEGER;
BEGIN
v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_SelectRecords := 'SELECT * from ' || v_table ;
DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum);
DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_Myname,50);
DBMS_SQL.DEFINE_COLUMN(v_CursorID,3,v_Rank);
v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_Myname);
DBMS_SQL.COLUMN_VALUE(v_CursorId,3,v_Rank);
DBMS_OUTPUT.PUT_LINE(v_MyNum || ' ' || v_Myname || ' ' || v_Rank );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
end;
