Several ways in which a function/procedure in Oracle returns a result set

Several ways in which a function/procedure in Oracle returns a result set

Note: This article is derived from: "Several ways in which a function/procedure in Oracle Returns a result set"

There are several ways in which a function/procedure in Oracle Returns a result set:

In the case of function return, the stored procedure needs to be changed to an out parameter and passed in the Oracle 10g test.

(1) Returns a cursor:

The

        return type is: Sys_refcursor
         defines variables in is: Curr Sys_refcursor;
        finally write in the function body:
         Open cur for
            select ...;
         return cur;
        Example:

  1 CREATE OR REPLACE FUNCTION a_test (  2                 ortype varchar2  3         ) RETURN Sys_ Refcursor  4is           5                type_cur sys_refcursor;   6         BEGIN  7             OPEN type_cur for  8                     Select Col1,col2,col3 from TestTable;   9                   RETURN  type_cur, ten         END;

(2) Returns the result set of table type:

First define a row type:

CREATE OR REPLACE TYPE "Split_arr" as OBJECT (nowstr varchar2 (18))

Next, define a table type with this line type:

CREATE OR REPLACE TYPE "Split_tab" as TABLE of Split_arr;

Define the function (this function completes the string splitting function):

1CREATE OR REPLACE FUNCTION getsubstr (2Str in VARCHAR2,--string to be split3Splitchar in VARCHAR2--Split flag4)5Return Split_tab6Is7Reststr varchar2 (+) default getsubstr.str;--The remaining string8Thisstr VARCHAR2 (18);--The current string obtained9INDEXSTR int;--temporary position of the delimiter in the stringTen  OneV Split_tab: = Split_tab ();--Return results A  -Begin -Dbms_output.put_line (RESTSTR); theWhile length (reststr)! = 0 -LOOP -<<top>> -Indexstr: = InStr (Reststr,splitchar);--The first position to take a delimiter from a substring +  -If Indexstr = 0 and Length (reststr)! = 0 Then--no delimiters found in the remaining string +Begin AV.extend; atV (v.count): = Split_arr (RESTSTR); -return v; -End -End If; -  -If Indexstr = 1 Then---The first character is a delimiter, the separator is removed at this time inBegin -RESTSTR: = substr (reststr,2); toGoto top; +End -End If; the  *If Length (reststr) = 0 or reststr is null then $return v;Panax NotoginsengEnd If; -  theV.extend; +THISSTR: = substr (reststr,1,indexstr-1);--Get the current string ARESTSTR: = substr (reststr,indexstr + 1);---Take the remaining string the  +V (v.count): = Split_arr (THISSTR); -END LOOP; $return v; $End

In PL/SQL developer, you can call directly

Cursor Strcur is

Select Nowstr from Table (getsubstr (' 111,222,333,,, ', ', '));

(3) Output in pipe form:

1Create type Row_type As Object (a VARCHAR2 (ten), v VARCHAR2 (10));--Define row objects2Create type Table_type as Table of Row_type;--Define Table objects3Create or Replace function Test_fun (4A in Varchar2,b in VARCHAR25)6return Table_type pipelined7Is8V Row_type;--Define V as the Row object type9BeginTenFor Thisrow in (select a, b from MyTable where col1=a and col2 = b) loop OneV: = Row_type (THISROW.A, thisrow.b); APipe row (v); -End Loop; -Return theEnd -SELECT * FROM table (Test_fun ('123‘,‘456‘));

Oracle function functions return data collection

Note: This article is from the Oracle function return data collection

1 the function in--oracle can return a custom dataset, and the records are referenced as follows:2 3 --1,object Object4 / * Custom type OBJECT type*/5CREATE OR REPLACE TYPE emp_id_type as OBJECT (org_cd varchar2 (10));6 7 --2,table Object8 / * Custom type TABLE type*/9CREATE OR REPLACE TYPE emp_id_table as TABLE of Emp_id_type;Ten  One --3, writing function A  -CREATE OR REPLACE FUNCTION f_emp_list () -  theRETURN emp_id_table pipelined is -  -CURSOR Emp_list_cursor is -Select '20001' As emp_id from dual union +Select '20002' As emp_id from dual union -Select '20003' as emp_id from dual; +  AV_emp_id_type Emp_id_type;--object Object atv_emp_id VARCHAR2 (5);--Temporary variables -  -BEGIN -  -OPEN Emp_list_cursor; -Loop in  -Fetch emp_list_cursor into v_emp_id; toExit when Emp_list_cursor%notfound; +  -V_emp_id_type: = Emp_id_type (v_emp_id);--Take value thePIPE ROW (V_emp_id_type);--Pipeline *  $End Loop;Panax NotoginsengCLOSE Emp_list_cursor; -  theReturn +  AEND; the  + --3, test SQL -  $SELECT * FROM table (f_emp_list);

Oracle uses function functions to query data to return cursors

Note: This article is from : Oracle uses function functions to query data return cursors

   1 Create or Replace function test111 (ItemNumber in varchar2) return sys_refcursor  2is    3   return_cursor sys_refcursor;   4 begin  5    OPEN return_cursor for SELECT 'a' from dual WHERE 1 = itemnumber;   6   RETURN Return_cursor;   7  8 end test111;

Using the following SQL to return the cursor, the PL SQL developer can directly open the query results

  1 Select test111 (1) from dual;

1. Applicable condition: Can be applied when the SQL statement is too long, avoid having too long SQL code in Java code!

JDBC Call result set

1  PackageCom.dahuatech.job;2 3 ImportJava.sql.CallableStatement;4 ImportJava.sql.Connection;5 ImportJava.sql.DriverManager;6 7 ImportOracle.jdbc.driver.OracleResultSet;8 ImportOracle.jdbc.driver.OracleTypes;9 Ten  Public classTest { One  A      Public Static voidMain (string[] args)throwsException { -Class.forName ("Oracle.jdbc.driver.OracleDriver"); -String URL = "Jdbc:oracle:thin:@10.30.5.106:1521:agile9"; the  -Connection conn = drivermanager.getconnection (URL, "Agile", "***"); -  -String sql = "{? = Call test111 (?)}"; +CallableStatement CST = conn.preparecall (SQL); -Cst.registeroutparameter (1, oracletypes.cursor); +Cst.setstring (2, "1"); ACst.execute (); atOracleresultset rs = (Oracleresultset) cst.getobject (1); -          while(Rs.next ()) { -System.out.println (Rs.getstring ("a")); -} -} -  in}

Springmvc the Jdbctemplete Call of the framework returns the function as a string

1  PublicString Transf (FinalString Inmodel) {2         returnJdbctemplate.execute ("{? = Call Transfmodel (?)}",NewCallablestatementcallback<string> () {3 4@Override5              PublicString doincallablestatement (CallableStatement cs)6                     throwsSQLException, DataAccessException {7Cs.registeroutparameter (1, Oracletypes.varchar);8Cs.setstring (2, Inmodel);9Cs.execute ();Ten                 return(String) Cs.getobject (1); One} A  -}); -}

Several ways in which a function/procedure in Oracle returns a result set