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

标题: 自定义oracle拆分字符串函数splitst [打印本页]
作者: fteair    时间: 2020-2-16 17:51
:L
作者: 万望    时间: 2020-6-9 22:17
标题: 自定义oracle拆分字符串函数splitst

在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);

  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;

  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;

  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)，如上代码所示。



作者: 陈晓龙    时间: 2020-6-11 14:12
作者: 陈晓龙    时间: 2020-6-11 14:13
作者: 陈晓龙    时间: 2020-6-11 14:13




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