隐式游标返回结果

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(res开发云主机域名1);
6 END;
7 /Procedure created.SQL> set serveroutput on
SQL> exec mp1;PL/SQL procedure successfully completed.ResultSet #112 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 pPL/SQL procedure successfully completed.ResultSet #1FIRST_NAME LAST_NAMEJonathon TaylorResultSet #2CITY STATE_PROVINCESydney New South WalesSQL> 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.

相关推荐: 介绍三种查看MySQL字符集的方法

MySQL字符集设置 系统变量： – character_set_server：默认的内部操作字符集 – character_set_client：客户端来源数据使用的字符集 – character_set_connection：连接层字符集 – charac…