隐式游标返回结果

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:

• Sales Manager (2007-01-01 00:00:00 - 2007-12-31 00:00:00)
• Sales Representative (2006-03-24 00:00:00 - 2006-12-31 00:00:00)

PL/SQL procedure successfully completed.