循环遍历 Oracle 中的显式游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5878145/
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
Loop through an explicit cursor in Oracle
提问by rajeemcariazo
How can I loop through an implicit cursor which is created, for example, from a query?
如何循环遍历创建的隐式游标，例如，从查询？
Here is the sample code:
这是示例代码：
SERVEROUTPUT on;
DECLARE
TYPE ref_cursor IS REF CURSOR;
cur REF_CURSOR;
BEGIN
OPEN cur FOR 'SELECT i.item_no,
i.item_descr
FROM ITEMS i
WHERE i.item_no in (1,2,3)';
... loop statement to print all item descriptions?
END;
回答by Dave Costa
Here's how to do it allowing for dynamic SQL. You can build the query string up in code however needed (usual warnings about SQL injection apply).
这是允许动态 SQL 的方法。您可以根据需要在代码中构建查询字符串（通常会出现有关 SQL 注入的警告）。
DECLARE
TYPE ref_cursor IS REF CURSOR;
cur REF_CURSOR;
d_item_no items.item_no%TYPE;
d_item_descr items.item_descr%TYPE;
BEGIN
OPEN cur FOR 'SELECT i.item_no,
i.item_descr
FROM ITEMS i
WHERE i.item_no in (1,2,3)';
LOOP
FETCH cur INTO d_item_no, d_item_descr;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line( d_item_no||' '||d_item_descr );
END LOOP;
CLOSE cur;
END;
/
回答by OMG Ponies
I'm not up on the 11g changes, but this should work:
我不知道 11g 的变化，但这应该有效：
BEGIN
FOR cur IN (SELECT i.item_no,
i.item_descr
FROM ITEMS i
WHERE i.item_no in (1,2,3))
LOOP
DBMS_OUTPUT.PUT_LINE('Row: '|| cur.item_no ||' '|| cur.item_descr);
END LOOP;
END;
