SQLPlus的隱式結(jié)果：12c中，在沒有實際綁定某個RefCursor的情況下，SQLPlus從一個PL/SQL塊的一個隱式游標返回結(jié)果。這一新的dbms_sql.return_result過程將會對PL/SQL 塊中由SELECT 語句查詢所指定的結(jié)果加以返回并進行格式化。
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 /

創(chuàng)新互聯(lián)公司專注于企業(yè)全網(wǎng)營銷推廣、網(wǎng)站重做改版、婁星網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、H5技術(shù)、商城系統(tǒng)網(wǎng)站開發(fā)、集團公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為婁星等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。

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.

網(wǎng)站題目：隱式游標返回結(jié)果
URL鏈接：http://aaarwkj.com/article10/gjoedo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站內(nèi)鏈、App開發(fā)、網(wǎng)站維護、云服務(wù)器、網(wǎng)頁設(shè)計公司

廣告

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