Oracle中函數/過程返回結果集的幾種方式

標籤：完成   pen   tab   system   top   into   develop   href   model   

Oracle中函數/過程返回結果集的幾種方式

註：本文來源於：《  Oracle中函數/過程返回結果集的幾種方式  》

Oracle中函數/過程返回結果集的幾種方式:

 

以函數return為例,預存程序只需改為out參數即可,在oracle 10g測試通過.

(1) 返回遊標:

        return的類型為:SYS_REFCURSOR
        之後在IS裡面定義變數:curr SYS_REFCURSOR;
        最後在函數體中寫:
         open cur for
            select ......;
         return cur;
        例:

  1 CREATE OR REPLACE FUNCTION A_Test(  2                 orType varchar2  3         )RETURN SYS_REFCURSOR  4         is  5                type_cur SYS_REFCURSOR;  6         BEGIN  7             OPEN type_cur FOR  8                     select col1,col2,col3 from testTable ;  9                   RETURN  type_cur; 10         END;

(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 CREATE OR REPLACE FUNCTION GetSubStr(  2                    str in varchar2, --待分割的字串  3                    splitchar in varchar2 --分割標誌  4             )  5             return split_tab  6             IS  7               restStr varchar2(2000) default GetSubStr.str;--剩餘的字串  8               thisStr varchar2(18);--取得的當前字串  9               indexStr int;--臨時存放分隔字元在字串中的位置 10  11               v split_tab := split_tab(); --返回結果 12  13             begin 14                  dbms_output.put_line(restStr); 15                  while length(restStr) != 0 16                    LOOP 17                      <<top>> 18                      indexStr := instr(restStr,splitchar); --從子串中取分隔字元的第一個位置 19  20                      if indexStr = 0 and length(restStr) != 0  then--在剩餘的串中找不到分隔字元 21                         begin 22                           v.extend; 23                           v(v.count) := split_arr(Reststr); 24                           return v; 25                         end; 26                      end if; 27  28                      if indexStr = 1 then---第一個字元便為分隔字元,此時去掉分隔字元 29                         begin 30                              restStr := substr(restStr,2); 31                              goto   top; 32                         end; 33                      end if; 34  35                      if length(restStr) = 0 or restStr is null then 36                         return v; 37                      end if; 38  39                      v.extend; 40                      thisStr := substr(restStr,1,indexStr - 1); --取得當前的字串 41                      restStr := substr(restStr,indexStr + 1);---取剩餘的字串 42  43                      v(v.count) := split_arr(thisStr); 44                    END LOOP; 45                  return v; 46             end;

在PL/SQL developer中可以直接調用

cursor strcur is

　　select nowStr from Table(GetSubStr(‘111,222,333,,,‘,‘,‘));

(3)以管道形式輸出:

  1 create type row_type as object(a varchar2(10), v varchar2(10));--定義行對象  2         create type table_type as table of row_type; --定義表對象  3         create or replace function test_fun(  4             a in varchar2,b in varchar2  5         )  6         return table_type pipelined  7         is  8             v row_type;--定義v為行物件類型  9         begin 10           for thisrow in (select a, b from mytable where col1=a and col2 = b) loop 11             v := row_type(thisrow.a, thisrow.b); 12             pipe row (v); 13           end loop; 14           return; 15         end; 16         select * from table(test_fun(‘123‘,‘456‘));

ORACLE函數Function返回資料集合

註：本文來源於《       ORACLE函數Function返回資料集合  》

  1 --Oracle中的Function可以返回自訂的資料集，記錄參考如下：  2   3 --1，Object對象  4 /*自訂類型 OBJECT Type*/  5 CREATE OR REPLACE TYPE EMP_ID_TYPE AS OBJECT(org_cd varchar2(10));  6   7 --2，Table對象  8 /*自訂類型 TABLE Type*/  9 CREATE OR REPLACE TYPE EMP_ID_TABLE  AS TABLE of EMP_ID_TYPE; 10  11 --3，編寫Function 12  13 CREATE OR REPLACE FUNCTION F_EMP_LIST () 14  15 RETURN EMP_ID_TABLE PIPELINED IS 16  17 　CURSOR emp_list_cursor is 18             select  ‘20001‘ as emp_id from dual union 19             select  ‘20002‘ as emp_id from dual union 20             select  ‘20003‘ as emp_id from dual; 21  22     v_emp_id_type EMP_ID_TYPE;   --Object對象 23     v_emp_id varchar2(5);              --臨時變數 24  25 BEGIN 26  27      OPEN emp_list_cursor; 28           loop 29  30               fetch emp_list_cursor into v_emp_id; 31               exit when emp_list_cursor%notfound; 32  33               v_emp_id_type := EMP_ID_TYPE(v_emp_id);  --取值 34               PIPE ROW(v_emp_id_type);   --管道 35  36           end loop; 37       CLOSE emp_list_cursor; 38  39       return; 40  41 END; 42  43 --3，測試SQL 44  45 select * from table(F_EMP_LIST);

Oracle 使用函數 function查詢資料返回遊標

註：本文來源於： 《  Oracle 使用函數 function查詢資料返回遊標   》

   1 create or replace function test111(itemNumber in varchar2) return SYS_REFCURSOR  2  is  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;

使用如下sql返回 遊標，在pl sql developer可以直接點開查詢結果

  1 select test111(1) from dual;

1. 適用條件： 在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<String>() {  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     }

Oracle中函數/過程返回結果集的幾種方式