注：本文来源于：《 》
Oracle中函数/过程返回结果集的几种方式:
以函数return为例,存储过程只需改为out参数即可,在oracle 10g测试通过.
(1) 返回游标:
return的类型为:SYS_REFCURSOR 之后在IS里面定义变量:curr SYS_REFCURSOR; 最后在函数体中写: open cur for select ......; return cur; 例:1 REPLACE A_Test( 2 orType 2 3 ) SYS_REFCURSOR 4 5 type_cur SYS_REFCURSOR; 6 7 type_cur 8 col1,col2,col3 testTable ; 9 type_cur; 10 ;
(2)返回table类型的结果集:
首先定义一个行类型:
CREATE OR REPLACE TYPE "SPLIT_ARR" AS OBJECT(nowStr varchar2(18))
其次以此行类型定义一个表类型:
CREATE OR REPLACE TYPE "SPLIT_TAB" AS TABLE of split_arr;
定义函数(此函数完成字符串拆分功能):
1 REPLACE GetSubStr( 2 str 2, --待分割的字符串 3 splitchar 2 --分割标志 4 ) 5 split_tab 6 7 restStr 2(2000) GetSubStr.str;--剩余的字符串 8 thisStr 2(18);--取得的当前字符串 9 indexStr ;--临时存放分隔符在字符串中的位置 10 11 v split_tab := split_tab(); --返回结果 12 13 14 dbms_output.put_line(restStr); 15 length(restStr) != 0 16 LOOP 17 <<>> 18 indexStr := instr(restStr,splitchar); --从子串中取分隔符的第一个位置 19 20 indexStr = 0 length(restStr) != 0 --在剩余的串中找不到分隔符 21 22 v.extend; 23 v(v.) := split_arr(Reststr); 24 v; 25 ; 26 ; 27 28 indexStr = 1 ---第一个字符便为分隔符,此时去掉分隔符 29 30 restStr := substr(restStr,2); 31 ; 32 ; 33 ; 34 35 length(restStr) = 0 restStr 36 v; 37 ; 38 39 v.extend; 40 thisStr := substr(restStr,1,indexStr - 1); --取得当前的字符串 41 restStr := substr(restStr,indexStr + 1);---取剩余的字符串 42 43 v(v.) := split_arr(thisStr); 44 LOOP; 45 v; 46 ;在PL/SQL developer中可以直接调用
cursor strcur is
select nowStr from Table(GetSubStr('111,222,333,,,',','));
(3)以管道形式输出:
1 type row_type object(a 2(10), v 2(10));--定义行对象 2 type table_type row_type; --定义表对象 3 replace test_fun( 4 a 2,b 2 5 ) 6 table_type pipelined 7 8 v row_type;--定义v为行对象类型 9 10 thisrow ( a, b mytable col1=a col2 = b) loop 11 v := row_type(thisrow.a, thisrow.b); 12 row (v); 13 loop; 14 ; 15 ; 16 * (test_fun('123','456'));
注：本文来源于《 》
1 --Oracle中的Function可以返回自定义的数据集，记录参考如下： 2 3 --1，Object对象 4 /*自定义类型 OBJECT Type*/ 5 REPLACE TYPE EMP_ID_TYPE OBJECT(org_cd 2(10)); 6 7 --2，Table对象 8 /*自定义类型 TABLE Type*/ 9 REPLACE TYPE EMP_ID_TABLE EMP_ID_TYPE; 10 11 --3，编写Function 12 13 REPLACE F_EMP_LIST () 14 15 EMP_ID_TABLE PIPELINED 16 17 emp_list_cursor 18 '20001' emp_id dual 19 '20002' emp_id dual 20 '20003' emp_id dual; 21 22 v_emp_id_type EMP_ID_TYPE; --Object对象 23 v_emp_id 2(5); --临时变量 24 25 26 27 emp_list_cursor; 28 loop 29 30 emp_list_cursor v_emp_id; 31 emp_list_cursor%notfound; 32 33 v_emp_id_type := EMP_ID_TYPE(v_emp_id); --取值 34 ROW(v_emp_id_type); --管道 35 36 loop; 37 emp_list_cursor; 38 39 ; 40 41 ; 42 43 --3，测试SQL 44 45 * (F_EMP_LIST);
Oracle 使用函数 function查询数据返回游标
注：本文来源于： 《 》
1 replace test111(itemNumber 2) SYS_REFCURSOR 2 3 return_cursor SYS_REFCURSOR; 4 5 return_cursor 'a' dual 1 = itemNumber; 6 return_cursor; 7 8 test111;使用如下sql返回 游标，在pl sql developer可以直接点开查询结果
1 test111(1) dual;
- 适用条件： 在Sql语句过长时可以适用，避免在java代码中有过长的sql代码！
jdbc调用结果集
1 package com.dahuatech.job; 2 3 import java.sql.CallableStatement; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 7 import oracle.jdbc.driver.OracleResultSet; 8 import oracle.jdbc.driver.OracleTypes; 9 10 public class Test { 11 12 public static void main(String[] args) throws Exception { 13 Class.forName("oracle.jdbc.driver.OracleDriver"); 14 String url = "jdbc:oracle:thin:@10.30.5.106:1521:agile9"; 15 16 Connection conn = DriverManager.getConnection(url, "agile", "***"); 17 18 String sql = "{? = call test111(?)}"; 19 CallableStatement cst = conn.prepareCall(sql); 20 cst.registerOutParameter(1, OracleTypes.CURSOR); 21 cst.setString(2, "1"); 22 cst.execute(); 23 OracleResultSet rs = (OracleResultSet) cst.getObject(1); 24 while (rs.next()) { 25 System.out.println(rs.getString("a")); 26 } 27 } 28 29 }SpringMvc框架的jdbcTemplete调用返回为字符串的函数
1 public String transf(final String inModel) { 2 return jdbcTemplate.execute("{? = call transfModel(?)}", new CallableStatementCallback() { 3 4 @Override 5 public String doInCallableStatement(CallableStatement cs) 6 throws SQLException, DataAccessException { 7 cs.registerOutParameter(1, OracleTypes.VARCHAR); 8 cs.setString(2, inModel); 9 cs.execute(); 10 return (String) cs.getObject(1); 11 } 12 13 }); 14 }