度量快速开发平台-专业、快速的软件定制快开平台

标题: 自定义oracle拆分字符串函数splitstr [打印本页]

作者: 万望    时间: 2020-5-1 21:29
标题: 自定义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.         IF v_index = 0
  13.         THEN
  14.             PIPE ROW(SUBSTR(p_string, v_start));
  15.             v_start := v_length + 1;
  16.         ELSE
  17.             PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
  18.             v_start := v_index + 1;
  19.         END IF;
  20.     END LOOP;

  21.     RETURN;
  22. 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.   FOR rec IN (SELECT *
  10.                 FROM cux_settle_headers sha
  11.                WHERE EXISTS (SELECT 1
  12.                         FROM TABLE(l_access_org_ids) t
  13.                        WHERE t.column_value = sha.org_id))
  14.   LOOP
  15.     dbms_output.put_line('header_id:' || sha.header_id);
  16.   END LOOP;
  17. END;
复制代码

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







欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://plat.delit.cn/) Powered by Discuz! X3.2