[转]Easy Stored Procedure Output Oracle Select

本文转自：http://www.oraclealchemist.com/oracle/easy-stored-procedure-output/

I answered a question on a DBA Forum today and I thought it was a common enough question to warrant a blog posting.

Question:  I am new to the wonderful world of Oracle. I want to be able to view the results of a stored procedure in an output window, say out of Oracle SQL developer. Unfortunately it appears I need to write some more code to actually view the data.

… 

On a more generic note, can anyone explain to me why Oracle has chosen to make PL/SQL inordinately more complicated than say MS SQL/Servers tSQL? I mean in tSQL I would just write:

CREATE OR REPLACE PROCEDURE TESTSPROC2 AS select * from test_table order by id_no; GO

and viola, a nice result set spits out in Query Analyzer (or a .net application).

Answer:

Before I go on, let me say I agree that PL/SQL is more powerful.  That being said, here are your options.

1. Test it with REFCURSOR using a FUNCTION and selecting from dual:

SQL> create or replace function testfunc return sys_refcursor 2 as 3 c_test sys_refcursor; 4 begin 5 open c_test for select first_name, last_name, email from employees where rownum < 10; 6 return c_test; 7 end; 8 / Function created. SQL> select testfunc() from dual; TESTFUNC() -------------------- CURSOR STATEMENT : 1 CURSOR STATEMENT : 1 FIRST_NAME LAST_NAME EMAIL -------------------- ------------------------- ------------------------- Steven King SKING Neena Kochhar NKOCHHAR Lex De Haan LDEHAAN Alexander Hunold AHUNOLD Bruce Ernst BERNST David Austin DAUSTIN Valli Pataballa VPATABAL Diana Lorentz DLORENTZ Nancy Greenberg NGREENBE 9 rows selected.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

SQL>create orreplace functiontestfunc returnsys_refcursor   2  as   3    c_test sys_refcursor;   4  begin   5    open c_test forselect first_name,last_name,email from employees where rownum<10;   6    returnc_test;   7  end;   8  /   Functioncreated.   SQL>select testfunc()from dual;   TESTFUNC() -------------------- CURSOR STATEMENT:1   CURSOR STATEMENT:1   FIRST_NAME           LAST_NAME                 EMAIL ---------------------------------------------------------------------- Steven               King                      SKING Neena                Kochhar                   NKOCHHAR Lex                  De Haan                   LDEHAAN Alexander            Hunold                    AHUNOLD Bruce                Ernst                     BERNST David                Austin                    DAUSTIN Valli                Pataballa                 VPATABAL Diana                Lorentz                   DLORENTZ Nancy                Greenberg                 NGREENBE   9rows selected.

2. Use the same function and return it into a variable: 

SQL> variable rc refcursor SQL> exec :rc := testfunc() PL/SQL procedure successfully completed. SQL> print rc FIRST_NAME LAST_NAME EMAIL -------------------- ------------------------- ------------------------- Steven King SKING Neena Kochhar NKOCHHAR Lex De Haan LDEHAAN Alexander Hunold AHUNOLD Bruce Ernst BERNST David Austin DAUSTIN Valli Pataballa VPATABAL Diana Lorentz DLORENTZ Nancy Greenberg NGREENBE 9 rows selected.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL>variable rc refcursor SQL>exec:rc:=testfunc()   PL/SQL procedure successfully completed.   SQL>print rc   FIRST_NAME           LAST_NAME                 EMAIL ---------------------------------------------------------------------- Steven               King                      SKING Neena                Kochhar                   NKOCHHAR Lex                  De Haan                   LDEHAAN Alexander            Hunold                    AHUNOLD Bruce                Ernst                     BERNST David                Austin                    DAUSTIN Valli                Pataballa                 VPATABAL Diana                Lorentz                   DLORENTZ Nancy                Greenberg                 NGREENBE   9rows selected.

3. Use your procedure with a variable:

SQL> create or replace procedure testproc(c_test out sys_refcursor) is 2 begin 3 open c_test for select first_name, last_name, email from employees where rownum < 10; 4 end; 5 / Procedure created. SQL> variable rc2 refcursor SQL> exec testproc(:rc2); PL/SQL procedure successfully completed. SQL> print rc2 FIRST_NAME LAST_NAME EMAIL -------------------- ------------------------- ------------------------- Steven King SKING Neena Kochhar NKOCHHAR Lex De Haan LDEHAAN Alexander Hunold AHUNOLD Bruce Ernst BERNST David Austin DAUSTIN Valli Pataballa VPATABAL Diana Lorentz DLORENTZ Nancy Greenberg NGREENBE 9 rows selected.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SQL>create orreplace procedure testproc(c_test out sys_refcursor)is   2  begin   3    open c_test forselect first_name,last_name,email from employees where rownum<10;   4  end;   5  /   Procedure created.   SQL>variable rc2 refcursor SQL>exec testproc(:rc2);   PL/SQL procedure successfully completed.   SQL>print rc2   FIRST_NAME           LAST_NAME                 EMAIL ---------------------------------------------------------------------- Steven               King                      SKING Neena                Kochhar                   NKOCHHAR Lex                  De Haan                   LDEHAAN Alexander            Hunold                    AHUNOLD Bruce                Ernst                     BERNST David                Austin                    DAUSTIN Valli                Pataballa                 VPATABAL Diana                Lorentz                   DLORENTZ Nancy                Greenberg                 NGREENBE   9rows selected.

#3 is more in-line with your original needs. Personally I’m a fan of #1 and #2 because of the capabilities of returning a refcursor as a function, like passing it into DBMS_XMLGEN.GETXML.