Oracle 自定义分隔函数

电玩女神 2023-10-10 17:15 114阅读 0赞

今天上班时，产品经理反馈客户有一批存量数据中，biz_type 存在多个业务键号使用逗号分隔，现在需要将该字段调整为基于逗号分隔的多行记录。

Oracle 自定义分隔函数：

  1. create or replace function split_string(t_string in varchar2, p_delimiter in varchar2)
  2.     return sys.Odcivarchar2list
  3.     pipelined
  4.     is
  5.     l_idx Pls_integer;
  6.     v_string varchar(245) := t_string;
  7. begin
  8.     loop
  9.         l_idx := instr(v_string, p_delimiter);
  10.         if l_idx > 0 then
  11.             pipe row(substr(v_string, 1, l_idx-1));
  12.             v_string := substr(v_string, l_idx + length(p_delimiter));
  13.         else
  14.             pipe row(v_string);
  15.             exit;
  16.         end if;
  17.     end loop;
  18.     return;
  19. end;

调用字符串分隔函数

  1. select * from table (split_string('1, 2, 3'), ',')

输出结果，符合业务要求。将相关SQL 脚本提供给客户现场的运维同事，让他处理剩下的存量数据迁移修正的问题。

中午午休刚刚睡醒，就被产品经理@, 说需要给运维的同事远程支持。

功能要求：存量数据中存在biz_type 字段使用逗号分隔，转换为多行记录。

  1. -- 定义数转存储过程
  2. create or replace procedure conver_procedure is
  3.     -- 定义查询游标
  4.     cursor cur_tid_biz_type is
  5.         select tid, biz_type from **** where biz_type like '%,%';
  6.     -- 定义游标记录
  7.     cur cur_tid_biz_type%ROWTYPE;
  8.     -- 定义集合列表（突然发现,split_string 分隔函数返回的sys.Odcivarchar2list类型是char 类型,无法与兼容，解决办法修改split_string 函数自定义type）
  9.     v_biz_type_list TYPE_SPLIT;
  11.     begin
  12.         -- for 循环游标
  13.         for cur in cur_tid_biz_type loop
  14.             -- 调用字符串分隔函数split_string(),将结果存储至自定义集合v_biz_type_list  中。
  15.             select split_string(cur.BIZ_TYPE, ',') into  v_biz_type_list from dual;
  16.             -- 遍历集合
  17.             for i in v_biz_type_list.FIRST .. v_biz_type_list.LAST Loop
  18.                 -- 输出结果
  19.                 DBMS_OUTPUT.PUT_LINE('tid:'||cur.TID||',bizType:'||v_biz_type_list(i));
  20.             end loop;
  21.         end loop;
  22.     end;

调用存储过程

  1. declare
  2. begin
  3.     conver_procedure()
  4. end;

补全自定义Type

  1. create or replace type TYPE_SPLIT as table of varchar2(245);

修改自定义函数split_string的返回数据类型为TYPE_SPLIT.

  1. create or replace function split_string(t_string in varchar2, p_delimiter in varchar2)
  2.     return TYPE_SPLIT
  3.     pipelined
  4.     is
  5.     l_idx Pls_integer;
  6.     v_string varchar(245) := t_string;
  7. begin
  8.     loop
  9.         l_idx := instr(v_string, p_delimiter);
  10.         if l_idx > 0 then
  11.             pipe row(substr(v_string, 1, l_idx-1));
  12.             v_string := substr(v_string, l_idx + length(p_delimiter));
  13.         else
  14.             pipe row(v_string);
  15.             exit;
  16.         end if;
  17.     end loop;
  18.     return;
  19. end;

遇到的问题：

Oracle 应用管道函数时出现PLS-00653：在PL/SQL 定义域内不允许有聚集/表函数。

造成此问题原因：

1、自定义表类型:TYPE_SPLIT

2、自定义字符串分隔函数 :split_string 返回TYPE_SPLIT 管道流。

3、直接调用存储conver_procedure ,去执行split_string 函数，将相关集合赋值给TYPE_SPLIT类型。出现”在PL/SQL 定义域内不允许有聚集/表函数”

错误写法:

  1. v_biz_type_list := split_string(cur.BIZ_TYPE, ',')

正确写法

  1. select split_string(cur.BIZ_TYPE, ',') into  v_biz_type_list from dual;

因为管道函数需要用TABLE 操作符从SQL 查询中调用它所以这边不能直接赋值。

发表评论

表情：
评论列表 （有 0 条评论，114人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Oracle自定义函数错误处理案例

    在Oracle数据库中，编写自定义函数时可能会遇到各种错误。这里我们将通过一个实际案例来演示如何进行错误处理。 **案例：创建一个自定义函数，计算两个日期之间的天数** `

    怼烎@怼烎@/ 0 赞/ 69 阅读

    相关 Oracle 自定义分隔函数

    今天上班时，产品经理反馈客户有一批存量数据中，biz\_type 存在多个业务键号使用逗号分隔，现在需要将该字段调整为基于逗号分隔的多行记录。 Oracle 自定义分隔函数：

    电玩女神电玩女神/ 0 赞/ 115 阅读

    相关 Oracle 自定义函数

    一、函数 函数与存储过程相似，也是数据库中存储的已命名PL-SQL程序块。函数的主要特征是它必须有一个返回值。通过`return`来指定函数的返回类型。在函数的任何地方可

    深藏阁楼爱情的钟深藏阁楼爱情的钟/ 0 赞/ 641 阅读