隐式遊标傳回結果

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

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 /

SQL> exec p

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;

4 -- Return employee info

5

6 OPEN rc FOR SELECT first_name, last_name, email, phone_number

8 WHERE employee_id = id;

9 DBMS_SQL.RETURN_RESULT(rc);

10

11 -- Return employee job history

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> DECLARE

2 c INTEGER;

3 rc SYS_REFCURSOR;

4 n NUMBER;

6 first_name VARCHAR2(20);

7 last_name VARCHAR2(25);

8 email VARCHAR2(25);

9 phone_number VARCHAR2(20);

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: [phone]

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)

     本文轉自whshurk 51CTO部落格，原文連結：http://blog.51cto.com/shurk/2057002，如需轉載請自行聯系原作者