SQLPlus的隱式結(jié)果：12c中，在沒(méi)有實(shí)際綁定某個(gè)RefCursor的情況下，SQLPlus從一個(gè)PL/SQL塊的一個(gè)隱式游標(biāo)返回結(jié)果。這一新的dbms_sql.return_result過(guò)程將會(huì)對(duì)PL/SQL 塊中由SELECT 語(yǔ)句查詢所指定的結(jié)果加以返回并進(jìn)行格式化。
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)公司專(zhuān)注于企業(yè)全網(wǎng)營(yíng)銷(xiāo)推廣、網(wǎng)站重做改版、婁星網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5技術(shù)、商城系統(tǒng)網(wǎng)站開(kāi)發(fā)、集團(tuán)公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為婁星等各大城市提供網(wǎng)站開(kāi)發(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)站題目：隱式游標(biāo)返回結(jié)果
URL鏈接：http://chinadenli.net/article10/gjoedo.html

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

廣告

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