SQL循环拆分

最新推荐文章于 2024-02-08 16:52:26 发布

原创最新推荐文章于 2024-02-08 16:52:26 发布 · 355 阅读

0 ·

CC 4.0 BY-SA版权

ＳＱＬ专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一个使用 Oracle SQL 的示例程序，该程序通过循环遍历和字符串操作功能来解析并插入带有分号分隔符的错误原因字段到另一个表中。此过程能够处理空字符串和多个分号的情况，并将每一条错误信息作为单独的记录存储。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

declare
v_num number :=1;
v_num1 number :=0;
begin
   for jieguo in(
     select d.transcode ,d.errorreason from HEALTH22PERROR d where d.transcode like '9025%'
     )
     loop
       v_num1 :=0;
       loop
         v_num1 :=v_num1+1;
         v_num :=instr(jieguo.errorreason,';',1,v_num1);

       if v_num =0 and v_num1=1 then insert into health21perror values(jieguo.transcode,jieguo.errorreason);
       elsif v_num=0 and v_num1>1 then
       insert into health21perror
       values(jieguo.transcode,
       substr(jieguo.errorreason,instr (jieguo.errorreason,';',1,v_num1-1)+1,
       length (jieguo.errorreason)-instr(jieguo.errorreason,';',v_num1)));
       elsif v_num1>0 and v_num1=1 then
       insert into health21perror values(jieguo.transcode,substr(jieguo.errorreason,1,v_num-1));

       elsif v_num>0 and v_num1>1 then
       insert into health21perror
       values (jieguo.transcode,
        substr(jieguo.errorreason,instr(jieguo.errorreason,';',1,v_num1-1)+1,
       v_num-1-instr (jieguo.errorreason,';',1,v_num1-1)));
       end if;
       exit when v_num=0;
       end loop;
       end loop;
       commit;
       end;
   --select errorreason ,count(transcode) from health21perror group by errorreason