本文共 3379 字，大约阅读时间需要 11 分钟。
SQLPlus的隐式结果：12c中，在没有实际绑定某个RefCursor的情况下，SQLPlus从一个PL/SQL块的一个隐式游标返回结果。这一新的dbms_sql.return_result过程将会对PL/SQL 块中由SELECT 语句查询所指定的结果加以返回并进行格式化。
SQL> CREATE PROCEDURE mp1 as 2 res1 sys_refcursor; 3 BEGIN 4 open res1 for SELECT empno,ename,sal FROM emp; 5 dbms_sql.return_result(res1); 6 END; 7 /Procedure created.
SQL> set serveroutput on
SQL> exec mp1;PL/SQL procedure successfully completed.
ResultSet #1
EMPNO ENAME SAL
7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7839 KING 5000 7844 TURNER 1500 7900 JAMES 950 7902 FORD 3000 EMPNO ENAME SAL
7934 MILLER 1300
12 rows selected.
SQL> conn hr/hr@pdbtest Connected. SQL> CREATE OR REPLACE PROCEDURE p AS 2 c1 SYS_REFCURSOR; 3 c2 SYS_REFCURSOR; 4 BEGIN 5 OPEN c1 FOR 6 SELECT first_name, last_name 7 FROM employees 8 WHERE employee_id = 176; 9 10 DBMS_SQL.RETURN_RESULT (c1); 11 -- Now p cannot access the result. 12 13 OPEN c2 FOR 14 SELECT city, state_province 15 FROM locations 16 WHERE country_id = 'AU'; 17 18 DBMS_SQL.RETURN_RESULT (c2); 19 -- Now p cannot access the result. 20 END; 21 /Procedure created.
SQL> exec p
PL/SQL procedure successfully completed.
ResultSet #1
FIRST_NAME LAST_NAME
Jonathon Taylor
ResultSet #2
CITY STATE_PROVINCE
Sydney New South Wales
SQL> CREATE OR REPLACE PROCEDURE get_employee_info (id IN VARCHAR2) AS
2 rc SYS_REFCURSOR; 3 BEGIN 4 -- Return employee info 5 6 OPEN rc FOR SELECT first_name, last_name, email, phone_number 7 FROM employees 8 WHERE employee_id = id; 9 DBMS_SQL.RETURN_RESULT(rc); 10 11 -- Return employee job history 12 13 OPEN RC FOR SELECT job_title, start_date, end_date 14 FROM job_history jh, jobs j 15 WHERE jh.employee_id = id AND 16 jh.job_id = j.job_id 17 ORDER BY start_date DESC; 18 DBMS_SQL.RETURN_RESULT(rc); 19 END; 20 / SQL> set serveroutput on SQL> DECLARE 2 c INTEGER; 3 rc SYS_REFCURSOR; 4 n NUMBER; 5 6 first_name VARCHAR2(20); 7 last_name VARCHAR2(25); 8 email VARCHAR2(25); 9 phone_number VARCHAR2(20); 10 11 job_title VARCHAR2(35); 12 start_date DATE; 13 end_date DATE; 14 15 BEGIN 16 17 c := DBMS_SQL.OPEN_CURSOR(true); 18 DBMS_SQL.PARSE(c, 'BEGIN get_employee_info(:id); END;', DBMS_SQL.NATIVE); 19 DBMS_SQL.BIND_VARIABLE(c, ':id', 176); 20 n := DBMS_SQL.EXECUTE(c); 21 22 -- Get employee info 23 24 dbms_sql.get_next_result(c, rc); 25 FETCH rc INTO first_name, last_name, email, phone_number; 26 27 DBMS_OUTPUT.PUT_LINE('Employee: '||first_name || ' ' || last_name); 28 DBMS_OUTPUT.PUT_LINE('Email: ' ||email); 29 DBMS_OUTPUT.PUT_LINE('Phone: ' ||phone_number); 30 31 -- Get employee job history 32 33 DBMS_OUTPUT.PUT_LINE('Titles:'); 34 DBMS_SQL.GET_NEXT_RESULT(c, rc); 35 LOOP 36 FETCH rc INTO job_title, start_date, end_date; 37 EXIT WHEN rc%NOTFOUND; 38 DBMS_OUTPUT.PUT_LINE 39 ('- '||job_title||' ('||start_date||' - ' ||end_date||')'); 40 END LOOP; 41 42 DBMS_SQL.CLOSE_CURSOR(c); 43 END main; 44 / Employee: Jonathon Taylor Email: JTAYLOR Phone: 011.44.1644.429265 Titles:PL/SQL procedure successfully completed.
本文转自whshurk 51CTO博客，原文链接：http://blog.51cto.com/shurk/2057002，如需转载请自行联系原作者