自定义oracle拆分字符串函数splitstr

万望

在oracle中将一个字符串按照某个分隔符进行拆分，然后以数据集合返回，这种需求还是比较常见的，然后oracle本身却没有提供相应的内置函数。

下面是在网上找到的一个自定义的splitstr函数，用来实现该功能：

Step 1：定义一个类型

1. CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);

复制代码

Step 2：编写splitstr函数

1. CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
   
2.     RETURN str_split
   
3.     PIPELINED
   
4. AS
   
5.     v_length   NUMBER := LENGTH(p_string);
   
6.     v_start    NUMBER := 1;
   
7.     v_index    NUMBER;
   
8. BEGIN
   
9.     WHILE(v_start <= v_length)
   
10.     LOOP
    
11.         v_index := INSTR(p_string, p_delimiter, v_start);
    
12. 
    
13.         IF v_index = 0
    
14.         THEN
    
15.             PIPE ROW(SUBSTR(p_string, v_start));
    
16.             v_start := v_length + 1;
    
17.         ELSE
    
18.             PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
    
19.             v_start := v_index + 1;
    
20.         END IF;
    
21.     END LOOP;
    
22. 
    
23.     RETURN;
    
24. END splitstr;

复制代码

Step 3：创建同义词

1. CREATE OR REPLACE PUBLIC SYNONYM splitstr FOR YEDWARD.splitstr;

复制代码

假设Step 2中的splitstr函数是在YEDWARD用户下编译，其owner是YEDWARD，为了方便被公共访问，可以建一个public同义词。

下面就可以测试了：

1. DECLARE
   
2.   TYPE t_char_array IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   
3.   l_access_org_ids str_split;
   
4. BEGIN
   
5.   SELECT to_char(hou.organization_id)
   
6.     BULK COLLECT
   
7.     INTO l_access_org_ids
   
8.     FROM hr_operating_units hou;
   
9. 
   
10.   FOR rec IN (SELECT *
    
11.                 FROM cux_settle_headers sha
    
12.                WHERE EXISTS (SELECT 1
    
13.                         FROM TABLE(l_access_org_ids) t
    
14.                        WHERE t.column_value = sha.org_id))
    
15.   LOOP
    
16.     dbms_output.put_line('header_id:' || sha.header_id);
    
17.   END LOOP;
    
18. END;

复制代码

可以看出splistr的返回值其实是一个pl/sql table（oracle内存表），如果需要对pl/sql table类型的变量做查询，可以使用select * from table(l_plsql_type_var)，如上代码所示。