oracle預存程序中迴圈插入資料

標籤：int   rom   else   desc   sel   col   ack   llb   div   

//oracle 迴圈插入資料procedure Insert_WData(  p_CODE1 ao_model.code1%type,                                     p_BRANDID    ao_model.brandid%type,                                    p_CODE  varchar2,                                     p_CONF_VAL   varchar2,                                    p_DESC  varchar2,                                     p_CODE2 varchar2,                                     p_DESC  varchar2,                                    ErrOut             in out varchar2) is  begin    --參數    declare      startposition1 number(10);      len1           number(10);      startposition2 number(10);      len2           number(10);      startposition3 number(10);      len3           number(10);      output1        varchar2(1024);      output2        varchar2(1024);      output3        varchar2(1024);      num            number(1);    begin      startposition1 := 1;      startposition2 := 1;      startposition3 := 1;      loop        select instr(p_CODE, ‘|‘, startposition1)          into len1          from dual;        select instr(p_CONF_VAL, ‘|‘, startposition2)          into len2          from dual;        select instr(p_DESC, ‘|‘, startposition3)          into len3          from dual;        if len1 != 0 then          begin            select substr(p_CODE,                          startposition1,                          len1 - startposition1)              into output1              from dual;            select substr(p_CONF_VAL,                          startposition2,                          len2 - startposition2)              into output2              from dual;            select substr(p_DESC,                          startposition3,                          len3 - startposition3)              into output3              from dual;            num := 0;            select count(*)              into num              from ao_model a             where a.c0084_brandid = p_BRANDID               and a.c0001_code1 = p_CODE1               and a.c0001_code = output1;            if num >= 1 then              update ao_model b                 set b.conf_val = output2               where b.c0084_brandid = p_BRANDID                 and b.c0001_code1 = p_CODE1                 and b.c0001_code = output1;            else              insert into ao_model                (C0001_CODE1,                 C0084_BRANDID,                 C0001_CODE,                 CONF_VAL,                 DESC)              values                (p_CODE1,                 p_BRANDID,                 output1 || ‘‘,                 output2 || ‘‘,                 output3 || ‘‘);            end if;            commit;          end;        else          begin            select substr(p_CODE, startposition1)              into output1              from dual;            select substr(p_CONF_VAL, startposition2)              into output2              from dual;            select substr(p_DESC, startposition3)              into output3              from dual;            num := 0;            select count(*)              into num              from ao_model a             where a.c0084_brandid = p_BRANDID               and a.c0001_code1 = p_CODE1               and a.c0001_code = output1;            if num >= 1 then              update ao_model b                 set b.conf_val = output2               where b.c0084_brandid = p_BRANDID                 and b.c0001_code1 = p_CODE1                 and b.c0001_code = output1;            else              insert into ao_model                (C0001_CODE1,                 C0084_BRANDID,                 C0001_CODE,                 CONF_VAL,                 DESC)              values                (p_CODE1,                 p_BRANDID,                 output1 || ‘‘,                 output2 || ‘‘,                 output3 || ‘‘);            end if;            commit;          end;          exit;        end if;        startposition1 := len1 + 1;        startposition2 := len2 + 1;        startposition3 := len3 + 1;      end loop;    end;  EXCEPTION    WHEN OTHERS THEN      ErrOut := SQLERRM;      rollback;  end Insert_WParamStatusData;

 

oracle預存程序中迴圈插入資料