oracle 带有动态 SQL 语句的游标 For 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15786953/
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
Cursor For Loop with dynamic SQL-Statement
提问by heuristican
Is there a way to perform a Cursor For Loop with an dynamic SQL-statement?
有没有办法用动态 SQL 语句执行 Cursor For 循环？
If I don't want to declare a record I can do something like this (only if I declared the cursor..):
如果我不想声明记录，我可以执行以下操作（仅当我声明了游标..）：
For I in cuSelect Loop
dbms_output.put_line(I.NAME);
End Loop;
And I can open a cursor for a dynamic SQL-statement:
我可以为动态 SQL 语句打开一个游标：
Open cuSelect For 'Select * From TAB_X';
Fetch ceSelect Into recSelect;
Close cuSelect;
But to do that I have to first declare the Record.
但要做到这一点，我必须首先声明记录。
Now my problem is that I have to open the Cursor for a very big and complicated dynamic SQL-statement. The structure of the record is unknown. Is there a way to open a variable cursor and iterate through it with an "undeclared" record?
现在我的问题是我必须为一个非常大和复杂的动态 SQL 语句打开 Cursor。记录的结构未知。有没有办法打开一个变量游标并用“未声明”的记录遍历它？
回答by heuristican
I think you can do what you want with DBMS_SQL package.
我认为你可以用 DBMS_SQL 包做你想做的事。
You can also check these:
您还可以检查这些：
For example:
例如：
declare
TYPE curtype IS REF CURSOR;
src_cur curtype;
curid NUMBER;
namevar VARCHAR2(50);
numvar NUMBER;
datevar DATE;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER;
dsql varchar2(1000) := 'select card_no from card_table where rownum = 1';
begin
OPEN src_cur FOR dsql;
-- Switch from native dynamic SQL to DBMS_SQL package.
curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
-- Define columns.
FOR i IN 1 .. colcnt LOOP
IF desctab(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
ELSIF desctab(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
ELSE
DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);
END IF;
END LOOP;
-- Fetch rows with DBMS_SQL package.
WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
FOR i IN 1 .. colcnt LOOP
IF (desctab(i).col_type = 1) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
dbms_output.put_line(namevar);
ELSIF (desctab(i).col_type = 2) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
dbms_output.put_line(numvar);
ELSIF (desctab(i).col_type = 12) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
dbms_output.put_line(datevar);
END IF;
END LOOP;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(curid);
end;
