PL/SQL中的字符串拆分 [英] String splitting in PL/SQL

问题描述

我在oracle中有一个这样的表:

I have a table like this in oracle:

servername       description                                                    ObjectState
-----------------------------------------------------------------------------------------
vm1         SP LA -  W IN  S V R S #P19 99 9999 999999 999  QTY 1                   0
VM2         S PL A - V R STD #P29-9 9 99 QTY 2 : SPLVRENT #P3 999999 9 QTY 3        1

等我想要这样的输出

servername   description             ponumber                  qty     objectstate
--------------------------------------------------------------------------------------
vm1          SP LA -  W IN  S V R S  P19 99 9999 999999 999    1        0
vm2          S PL A - V R STD        P29-9 9 99                2        1
vm2          SPLVRENT                P3 999999 9               3        1

帮助我如何使用pl/sql存储过程执行此操作

help me how to do this using pl/sql stored procedure

推荐答案

尝试此过程！

如果描述模式与指定的空格相同，并且描述，phonenum，Qty之类的流程相同，这将起作用

This will work if the description pattern will be the same with the spaces specified and the flow like description,phonenum,Qty

create or replace procedure sp_split_str
as
     end_pos number:=0;
     strt_pos number :=1;
     CNT NUMBER:=0;
     v_desc varchar(200);
begin
     for i in (select * from table) loop
            if(instr(i.description,':',1,1)=0) then
                   dbms_output.put_line('Server Name : '||i.servername);
                   dbms_output.put_line('Description : '||substr(i.description,1,instr(i.description,'#',1,1)-2));
                   dbms_output.put_line('Phone Number : '||substr(i.description,instr(i.description,'#',1,1)+1,instr(i.description,'Q',-1,1)-2-instr(i.description,'#',1,1)));
                   dbms_output.put_line('Qty : '||substr(i.description,instr(i.description,'Q',-1,1)+4));           
                   dbms_output.put_line('Object State : '||i.objectstate);
            else
                   for J in 1..(length(i.description)-length(replace(i.description,':','')))+1 loop
                          IF(J=1) THEN
                                 end_pos := instr(i.description,':',1,1);
                                 v_desc := substr(i.description,strt_pos,end_pos-2);
                          ELSE
                                 end_pos := instr(i.description,':',1,j);
                                 strt_pos := instr(i.description,':',1,j-1);
                          END IF;
                          dbms_output.put_line('Server Name : '||i.servername);
                          dbms_output.put_line('Description : '||substr(v_desc,1,instr(v_desc,'#',1,1)-2));
                          dbms_output.put_line('Phone Number : '||substr(v_desc,instr(v_desc,'#',1,1)+1,instr(v_desc,'Q',-1,1)-2-instr(v_desc,'#',1,1)));
                          dbms_output.put_line('Qty : '||substr(v_desc,instr(v_desc,'Q',-1,1)+4));          
                          dbms_output.put_line('Object State : '||i.objectstate);

                          CNT := CNT+1;
                          IF(CNT=J) THEN
                                  v_desc := substr(i.description,strt_pos+2);
                          ELSE
                                  v_desc := substr(i.description,strt_pos+2,end_pos-2);
                          END IF;
                   END loop;
            end if;
      END LOOP;

END;

这篇关于PL/SQL中的字符串拆分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！