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

时间:2022-10-01 15:06:30
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;