DBMS_SQL.TO_CURSOR_NUMBER()函数
将一个强或弱的REF CURSOR变量 转换成SQL光标数字,这个SQL光标数字可以被传递给DBMS_SQL子程序。
将REF CURSOR传递给DBMS_SQL.TO_CURSOR_NUMBER()函数之前需要先OPEN。
在把REF CURSOR变量转换为SQL光标数字之后,本地动态SQL语句不能再访问它。
练习:从本地动态SQL转换为DBMS_SQL包
-- 11g12_07_07.prc CREATE OR REPLACE PROCEDURE do_query_2 ( sqlStmt VARCHAR2 ) IS TYPE TypeCursor IS REF CURSOR; curSrc TypeCursor; curID NUMBER; descTab DBMS_SQL.DESC_TAB; colCnt NUMBER; vName VARCHAR2(50); vNum NUMBER; vDate DATE; empNo NUMBER := 100; BEGIN -- sqlStmt := 'SELECT ... FROM employees WHERE employee_id = :b1'; -- 打开光标 OPEN curSrc FOR sqlStmt USING empNo; -- 从本地动态SQL转换为DBMS_SQL curID := DBMS_SQL.TO_CURSOR_NUMBER(curSrc); DBMS_SQL.DESCRIBE_COLUMNS(curID, colCnt, descTab); -- 定义列 FOR i IN 1 .. colCnt LOOP IF descTab(i).col_type = 2 THEN DBMS_SQL.DEFINE_COLUMN(curID, i, vNum); ELSIF descTab(i).col_type = 12 THEN DBMS_SQL.DEFINE_COLUMN(curID, i, vDate); ELSE DBMS_SQL.DEFINE_COLUMN(curID, i, vName, 50); END IF; END LOOP; -- DBMS_SQL包获取行 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, vName); DBMS_OUTPUT.PUT_LINE(vName || ', '); ELSIF (descTab(i).col_type = 2) THEN DBMS_SQL.COLUMN_VALUE(curID, i, vNum); DBMS_OUTPUT.PUT_LINE(vNum || ', '); ELSIF (descTab(i).col_type = 12) THEN DBMS_SQL.COLUMN_VALUE(curID, i, vDate); DBMS_OUTPUT.PUT_LINE(to_char(vDate) || ', '); END IF; END LOOP; END LOOP; DBMS_SQL.CLOSE_CURSOR(curID); END; -- 11g12_07_07.tst BEGIN do_query_2('SELECT employee_id, first_name FROM employees where employee_id = :b1'); do_query_2('SELECT employee_id, first_name, last_name FROM employees where employee_id = :b1'); END; 结果:
|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1131204/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1131204/