隐式游标返回结果

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

7 from employees

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)