oracle 实现 split 函数 (返回数组）

拼搏现实的明天。 2022-08-18 11:38 397阅读 0赞

首先，这是最近在公司执行一个任务的时候遇到的情况，分享一下，代码来源于互联网，非原创。  
 如果你的存储过程的参数个数不定，比如你要处理A条件、B条件2种情况，或者A条件、C条件、F条件的情况，  
 每次的条件的个数和条件都是变动的，这样你的存储过程参数就不好做。这种情况下，你可以只输入一个参数，  
 多个条件用特殊字符如逗号分隔，然后对这个条件截断拆分之后循环处理，类似于jquery的split方法。然而，oracle

并没有这样的函数，你需要自己编写，以下是收录的一个有用的将oracle字符串截断成数组(oracle复合数据类型)的function。

CREATE OR REPLACE PACKAGE StatisPackage
    AS
        TYPE StatisList IS REF CURSOR;
        TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);  
    
        PROCEDURE Statis_Planning(PlanningId IN nvarchar2,SolutionID IN nvarchar2,indextypeid in NUMBER,cversionid IN nvarchar2,Indicators in nvarchar2,p_cur out StatisList);
        
        FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) return ty_str_split;  
    END StatisPackage;

CREATE OR REPLACE PACKAGE BODY StatisPackage AS  
   
  /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  Description:实现 split 函数 (返回数组）   
  Param:uni\_array:=f\_split\_string('aa,bb,cc,dd',',')  
  Creator: 　             DATE:2015-12-13  
  \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
 FUNCTION fn\_split (p\_str IN VARCHAR2, p\_delimiter IN VARCHAR2)    
  RETURN ty\_str\_split    
IS    
  j INT := 0;    
  i INT := 1;    
  len INT := 0;    
  len1 INT := 0;    
  str VARCHAR2 (4000);    
  str\_split ty\_str\_split := ty\_str\_split ();    
BEGIN    
  len := LENGTH (p\_str);    
  len1 := LENGTH (p\_delimiter);    
    
  WHILE j < len    
  LOOP    
    j := INSTR (p\_str, p\_delimiter, i);    
    
    IF j = 0    
    THEN    
        j := len;    
        str := SUBSTR (p\_str, i);    
        str\_split.EXTEND;    
        str\_split (str\_split.COUNT) := str;    
    
        IF i >= len    
        THEN    
          EXIT;    
        END IF;    
    ELSE    
        str := SUBSTR (p\_str, i, j - i);    
        i := j + len1;    
        str\_split.EXTEND;    
        str\_split (str\_split.COUNT) := str;    
    END IF;    
  END LOOP;    
    
  RETURN str\_split;    
END fn\_split;    
end StatisPackage;

测试：

DECLARE  
  CURSOR c  
  IS  
    SELECT \*  
      FROM TABLE (CAST (fn\_split ('1;;12;;123;;1234;;12345', ';;') AS ty\_str\_split  
                  )  
              );

r c%ROWTYPE;  
BEGIN  
  OPEN c;

LOOP  
    FETCH c INTO r;

EXIT WHEN c%NOTFOUND;  
    DBMS\_OUTPUT.put\_line (r.column\_value);  
  END LOOP;

CLOSE c;  
END;  
/

结果：  
1  
12  
123  
1234  
12345

方法二;

create or replace function f\_split\_string (var\_str varchar2,var\_split varchar2)  
 return t\_ret\_table

is  
 var\_out t\_ret\_table; --定义变量  
var\_tmp varchar2(100);  
 var\_element varchar2(100);

begin  
 var\_tmp := var\_str;  
 var\_out := t\_ret\_table();  
\--如果存在匹配的分割符  
while instr(var\_tmp,var\_split)>0 loop  
var\_element:=substr(var\_tmp,1,instr(var\_tmp,var\_split)-1);  
 var\_tmp := substr(var\_tmp,instr(var\_tmp,var\_split)+length(var\_split),length(var\_tmp));  
 var\_out.extend(1);  
 var\_out(var\_out.count) := var\_element;  
 end loop;  
 var\_out.extend(1);  
 var\_out(var\_out.count) := var\_tmp;  
 return var\_out;  
 end f\_split\_string;

在代码里，uni\_array是一个table of varchar2的数组，就可以uni\_array:=f\_split\_string('aa,bb,cc,dd',',')  
将aa、bb、cc、dd四个元素装入uni\_array，然后循环处理。