11g中将DBMS_SQL 游标转换为REF CURSOR

ALLSTARS_ORACLE

将DBMS_SQL 游标转换为REF CURSOR


• 要提高本源动态SQL 和DBMS_SQL 之间的交互操作性，
可将DBMS_SQL 游标转换为PL/SQL REF CURSOR，反之
亦然。
• 为了支持这项功能，DBMS_SQL 程序包中新增了两个函数：
– DBMS_SQL.TO_REFCURSOR
(cursor_number IN INTEGER)
RETURN SYS_REFCURSOR;
– DBMS_SQL.TO_CURSOR_NUMBER
(rc IN OUT SYS_REFCURSOR)
RETURN INTEGER;

CREATE OR REPLACE PROCEDURE do_query (rep_id NUMBER)
IS
TYPE num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE cur_type IS REF CURSOR;
src_cur cur_type;
c_hndl NUMBER;
cust_nos num_list;
crdt_nos num_list;
ret INTEGER;
sql_stmt CLOB;
BEGIN
c_hndl := DBMS_SQL.OPEN_CURSOR;
sql_stmt := 'SELECT customer_id, credit_limit FROM customers
WHERE account_mgr_id = :b1';
DBMS_SQL.PARSE(c_hndl, sql_stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c_hndl, 'b1', rep_id);
ret := DBMS_SQL.EXECUTE(c_hndl);

-- continued from previous page
-- switch from dbms_sql to native dynamic SQL
src_cur := DBMS_SQL.TO_REFCURSOR(c_hndl);
-- fetch with native dynamic SQL
FETCH src_cur BULK COLLECT INTO cust_nos, crdt_nos;
IF cust_nos.COUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE ('Customer Credit Limit');
DBMS_OUTPUT.PUT_LINE ('-------- ------------');
FOR i IN 1 .. cust_nos.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(cust_nos(i) || ' ' ||
crdt_nos(i));
END LOOP;
END IF;
CLOSE src_cur;
END do_query;
/



EXECUTE do_query(145)
Customer Credit Limit
-------- ------------
308 1200
309 1200
310 5000
360 3600
344 2400
380 3700
...
934 600
PL/SQL procedure successfully completed.





CREATE OR REPLACE PROCEDURE do_query2 (sql_stmt VARCHAR2, rep_id NUMBER)
IS
TYPE cur_type IS REF CURSOR;
src_cur cur_type;
c_hndl NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER; custid NUMBER; crdvar NUMBER;
BEGIN
OPEN src_cur FOR sql_stmt USING rep_id;
-- switch from native dynamic SQL to DBMS_SQL:
c_hndl := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
DBMS_SQL.DESCRIBE_COLUMNS(c_hndl, colcnt, desctab);
-- define columns
FOR i in 1 .. colcnt LOOP
IF desctab(i).col_type=1 THEN
DBMS_SQL.DEFINE_COLUMN(c_hndl, i, custid);
ELSIF desctab(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(c_hndl, i, crdvar);
END IF;
END LOOP;

-- continued from previous page
-- fetch rows
WHILE DBMS_SQL.FETCH_ROWS(c_hndl) > 0 LOOP
FOR i IN 1 .. colcnt LOOP
IF desctab(i).col_type=1 THEN
DBMS_SQL.COLUMN_VALUE(c_hndl, i, custid);
ELSIF desctab(i).col_type = 2 THEN
DBMS_SQL.COLUMN_VALUE(c_hndl, i, crdvar);
END IF;
END LOOP;
-- could do more processing...
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c_hndl);
END do_query2;
/


EXECUTE do_query2('SELECT customer_id, credit_limit FROM customers -
WHERE account_mgr_id = :b1', 148)
PL/SQL procedure successfully completed.