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;
  10. begin
  11. -- for 循环游标
  12. for cur in cur_tid_biz_type loop
  13. -- 调用字符串分隔函数split_string(),将结果存储至自定义集合v_biz_type_list 中。
  14. select split_string(cur.BIZ_TYPE, ',') into v_biz_type_list from dual;
  15. -- 遍历集合
  16. for i in v_biz_type_list.FIRST .. v_biz_type_list.LAST Loop
  17. -- 输出结果
  18. DBMS_OUTPUT.PUT_LINE('tid:'||cur.TID||',bizType:'||v_biz_type_list(i));
  19. end loop;
  20. end loop;
  21. 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数据库中,编写自定义函数时可能会遇到各种错误。这里我们将通过一个实际案例来演示如何进行错误处理。 **案例:创建一个自定义函数,计算两个日期之间的天数** `

    相关 Oracle 定义分隔函数

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

    相关 Oracle 定义函数

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