oracle存储过程将某张表一个字段的多值分开成多条数据插入到一张新表

create or replace PROCEDURE PRO_MANDATESCOPE (dataid in varchar2,tablename in varchar2 )

 AS

   v_ID varchar2(1000);
   v_MANDATESCOPE clob;
   av_split varchar2(2) := ';';
   v_length number;
   v_start NUMBER;
   v_index NUMBER;
   v_str varchar2(1000);
   v_MANDATETYPE varchar2(2);
  v_sql     VARCHAR2(1000);--动态sql
BEGIN
  v_sql:='DELETE FROM TLK_P_'||tablename||'_mds WHERE '||tablename||'ID ='''||dataid||'''';
   execute immediate(v_sql);
   v_sql:='SELECT ID ,ITEM_MANDATESCOPE  FROM tlk_p_'||tablename|| ' WHERE  ID ='''|| dataid||'''';
    execute immediate v_sql   INTO v_ID,
        v_MANDATESCOPE; --执行SQL 

   DBMS_OUTPUT.PUT_LINE('output:'||v_ID );   --監視SQL語句
                v_MANDATESCOPE := replace(v_MANDATESCOPE,',',';');
                v_length := LENGTH(v_MANDATESCOPE);
                         --v_length number:=LENGTH(v_MANDATESCOPE);
                         v_start :=1;
                       v_index :=0;
                       v_str :='';
                       IF INSTR(v_MANDATESCOPE ,av_split) >0 THEN
                        WHILE(v_start <= v_length)
                          LOOP
                              v_index := INSTR(v_MANDATESCOPE, av_split, v_start);

                              IF v_index = 0
                              THEN
                                  v_MANDATETYPE := SUBSTR(v_MANDATESCOPE, v_start,1);
                                DBMS_OUTPUT.PUT_LINE('v_MANDATETYPE:'||v_MANDATETYPE );   --監視SQL語句
                                  v_str :=SUBSTR(v_MANDATESCOPE, v_start+2);
                                  v_start := v_length + 1;
                              ELSE
                                  v_MANDATETYPE := SUBSTR(v_MANDATESCOPE, v_start, 1);
                                DBMS_OUTPUT.PUT_LINE('v_MANDATETYPE:'||v_MANDATETYPE );   --監視SQL語句
                                  v_str :=SUBSTR(v_MANDATESCOPE, v_start+2, v_index - v_start);
                                  v_start := v_index + 1;
                              END IF;
                              v_sql:='INSERT INTO TLK_P_'||tablename||'_mds (ID,'||tablename||'ID,MANDATESCOPE,MANDATETYPE) VALUES('''||sys_guid()||''','''||v_ID||''','''||v_str||''','''||v_MANDATETYPE||''')';
                              DBMS_OUTPUT.PUT_LINE('INPUT:'||v_sql );   --監視SQL語句
                              execute immediate(v_sql);

                          END LOOP;
                        ELSE
                          v_MANDATETYPE := SUBSTR(v_MANDATESCOPE, v_start,1);
                          v_str :=SUBSTR(v_MANDATESCOPE, v_start+2);
                          v_sql:='INSERT INTO TLK_P_'||tablename||'_mds (ID,'||tablename||'ID,MANDATESCOPE,MANDATETYPE) VALUES('''||sys_guid()||''','''||v_ID||''','''||v_str||''','''||v_MANDATETYPE||''')';
                         DBMS_OUTPUT.PUT_LINE('INPUT1:'||v_sql );   --監視SQL語句
                          --v_sql:='INSERT INTO TLK_MANDATESCOPE(ID,DATAID,AUTHORITYID) VALUES(sys_guid(),v_ID,v_MANDATESCOPE)';
                         execute immediate(v_sql);

                        END IF;

  --提交
  COMMIT;
END PRO_MANDATESCOPE;

2.批量处理

create or replace PROCEDURE AUTHORIZESAVE (dataid in varchar2)

 AS

   v_ID varchar2(1000);
   v_MANDATESCOPE clob;
   av_split varchar2(2) := ';';
   v_length number;
   v_start NUMBER;
   v_index NUMBER;
   v_str varchar2(1000);

BEGIN
  DELETE FROM TLK_MANDATESCOPE WHERE DATAID = dataid;
  DECLARE CURSOR myCusor IS 
  SELECT ID ,ITEM_MANDATESCOPE FROM tlk_P_Announcements WHERE  ID = dataid;
  BEGIN
            OPEN myCusor;

            LOOP
                FETCH myCusor INTO v_ID,v_MANDATESCOPE;
                --游标取不到数据则退出
                EXIT WHEN myCusor%NOTFOUND;
                v_MANDATESCOPE := replace(v_MANDATESCOPE,',',';');
                v_length := LENGTH(v_MANDATESCOPE);
                         --v_length number:=LENGTH(v_MANDATESCOPE);
                         v_start :=1;
                       v_index :=0;
                       v_str :='';
                       IF INSTR(v_MANDATESCOPE ,av_split) >0 THEN
                        WHILE(v_start <= v_length)
                          LOOP
                              v_index := INSTR(v_MANDATESCOPE, av_split, v_start);

                              IF v_index = 0
                              THEN
                                  v_str :=SUBSTR(v_MANDATESCOPE, v_start);
                                  v_start := v_length + 1;
                              ELSE
                                  v_str :=SUBSTR(v_MANDATESCOPE, v_start, v_index - v_start);
                                  v_start := v_index + 1;
                              END IF;
                              INSERT INTO TLK_MANDATESCOPE(ID,DATAID,AUTHORITYID) VALUES(sys_guid(),v_ID,v_str);
                          END LOOP;
                        ELSE
                          INSERT INTO TLK_MANDATESCOPE(ID,DATAID,AUTHORITYID) VALUES(sys_guid(),v_ID,v_MANDATESCOPE);
                        END IF;

            END LOOP;
            CLOSE myCusor;
  END;
  --提交
       COMMIT;
END AUTHORIZESAVE;