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;
- 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