在Oracle中遍历显式游标 [英] Loop through an explicit cursor in Oracle

问题描述

如何遍历例如由查询创建的隐式游标?

How can I loop through an implicit cursor which is created, for example, from a query?

这是示例代码:

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;

推荐答案

此处介绍了允许动态SQL的方法.您可以根据需要在代码中构建查询字符串(通常会出现有关SQL注入的警告).

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).

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;
/

这篇关于在Oracle中遍历显式游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！