Shulou(Shulou.com)06/01 Report--

The implicit result of SQLPlus: in 12c, SQLPlus returns the result from an implicit cursor of a PL/SQL block without actually binding a RefCursor. This new dbms_sql.return_result procedure will return and format the results specified by the SELECT statement query in the PL/SQL block.

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

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

nine

10 DBMS_SQL.RETURN_RESULT (C1)

11-- Now p cannot access the result.

twelve

13 OPEN c2 FOR

14 SELECT city, state_province

15 FROM locations

16 WHERE country_id = 'AU'

seventeen

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

five

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)

ten

11-- Return employee job history

twelve

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

five

6 first_name VARCHAR2 (20)

7 last_name VARCHAR2 (25)

8 email VARCHAR2 (25)

9 phone_number VARCHAR2 (20)

ten

11 job_title VARCHAR2 (35)

12 start_date DATE

13 end_date DATE

fourteen

15 BEGIN

sixteen

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)

twenty-one

22-Get employee info

twenty-three

24 dbms_sql.get_next_result (c, rc)

25 FETCH rc INTO first_name, last_name, email, phone_number

twenty-six

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)

thirty

31-Get employee job history

thirty-two

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

forty-one

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.