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 /

創新互聯公司專注于企業全網營銷推廣、網站重做改版、婁星網站定制設計、自適應品牌網站建設、H5技術、商城系統網站開發、集團公司官網建設、成都外貿網站建設公司、高端網站制作、響應式網頁設計等建站業務，價格優惠性價比高，為婁星等各大城市提供網站開發制作服務。

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.

網站題目：隱式游標返回結果
URL鏈接：http://vcdvsql.cn/article10/gjoedo.html

成都網站建設公司_創新互聯，為您提供外貿網站建設、網站內鏈、App開發、網站維護、云服務器、網頁設計公司

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯