create or replace procedure p_demo(str_in varchar2,) as begin loop exit when instr(str_temp,str_sign)<1; insert into tbname(colname) values(substr(str_temp,1,instr(str_temp,str_sign)-1)); str_temp:=substr(str_temp,instr(str_temp,str_sign)+1); end loop; insert into tbname(colname) values(str_temp); end p_demp; / exec p_demo('abc¥def¥ghi','¥'); or exec p_demo('abc,def,ghi',',');
create or replace procedure p_demo(str_in varchar2,) as begin loop exit when instr(str_temp,str_sign)<1; insert into tbname(colname) values(substr(str_temp,1,instr(str_temp,str_sign)-1)); str_temp:=substr(str_temp,instr(str_temp,str_sign)+1); end loop; insert into tbname(colname) values(str_temp); end p_demp; / exec p_demo('abc¥def¥ghi','¥'); or exec p_demo('abc,def,ghi',',');
写个例子给你参考: 生成表:SQL> CREATE TABLE CSDN_DEMO 2 (ID CHAR(3), 3 FIELD_1 VARCHAR2(10), 4 FIELD_2 VARCHAR2(10) 5 ); 表已创建。已用时间: 00: 00: 00.30 过程: CREATE OR REPLACE PROCEDURE INSERT_DEMO ( p_INSERT_STR VARCHAR2 ) /* 参数说明: p_INSERT_STR 输入参数,列以“¥”分隔,行以“,”分隔的字符串,如 '001¥12¥13,002¥22¥23,003¥32¥33' 注意:默认情况,该参数长度小于 250 个字符,如需更改,请根据实际情况调整参数 */ AS v_STR_TEMP VARCHAR2(502); v_STR_CURR VARCHAR2(500); v_STR_INSERT_1 VARCHAR2(100) := 'INSERT INTO CSDN_DEMO (ID, FIELD_1, FIELD_2) VALUES ( '; v_STR_INSERT_2 VARCHAR2(500); BEGIN IF p_INSERT_STR IS NULL THEN DBMS_OUTPUT.PUT_LINE('输入参数为空!'); END IF; v_STR_TEMP := SUBSTR(p_INSERT_STR,1,250) || ','; LOOP v_STR_CURR := SUBSTR(v_STR_TEMP,1,INSTR(v_STR_TEMP,',')-1) || '¥'; v_STR_INSERT_2 := NULL; LOOP v_STR_INSERT_2 := v_STR_INSERT_2 || ''''||SUBSTR(v_STR_CURR,1,INSTR(v_STR_CURR,'¥')-1) || ''','; v_STR_CURR := SUBSTR(v_STR_CURR,INSTR(v_STR_CURR,'¥')+1); EXIT WHEN v_STR_CURR IS NULL; END LOOP; v_STR_INSERT_2 := SUBSTR(v_STR_INSERT_2,1,LENGTH(v_STR_INSERT_2)-1); v_STR_INSERT_2 := v_STR_INSERT_2 || ')'; EXECUTE IMMEDIATE v_STR_INSERT_1 || v_STR_INSERT_2; v_STR_TEMP := SUBSTR(v_STR_TEMP,INSTR(v_STR_TEMP,',')+1); EXIT WHEN v_STR_TEMP IS NULL; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SUBSTR('ERROR:'||SQLERRM,1,255)); END INSERT_DEMO; 调用: SQL> EXEC INSERT_DEMO('001¥12¥13,002¥22¥23,003¥32¥33');PL/SQL 过程已成功完成。已用时间: 00: 00: 00.00 SQL> SELECT * FROM CSDN_DEMO;ID FIELD_1 FIELD_2 --- ---------- ---------- 002 22 23 003 32 33 001 12 13已用时间: 00: 00: 00.10
begin
loop
exit when instr(str_temp,str_sign)<1;
insert into tbname(colname) values(substr(str_temp,1,instr(str_temp,str_sign)-1));
str_temp:=substr(str_temp,instr(str_temp,str_sign)+1);
end loop;
insert into tbname(colname) values(str_temp);
end p_demp;
/
exec p_demo('abc¥def¥ghi','¥');
or
exec p_demo('abc,def,ghi',',');
begin
loop
exit when instr(str_temp,str_sign)<1;
insert into tbname(colname) values(substr(str_temp,1,instr(str_temp,str_sign)-1));
str_temp:=substr(str_temp,instr(str_temp,str_sign)+1);
end loop;
insert into tbname(colname) values(str_temp);
end p_demp;
/
exec p_demo('abc¥def¥ghi','¥');
or
exec p_demo('abc,def,ghi',',');
生成表:SQL> CREATE TABLE CSDN_DEMO
2 (ID CHAR(3),
3 FIELD_1 VARCHAR2(10),
4 FIELD_2 VARCHAR2(10)
5 ); 表已创建。已用时间: 00: 00: 00.30
过程:
CREATE OR REPLACE PROCEDURE INSERT_DEMO
(
p_INSERT_STR VARCHAR2
)
/*
参数说明:
p_INSERT_STR 输入参数,列以“¥”分隔,行以“,”分隔的字符串,如 '001¥12¥13,002¥22¥23,003¥32¥33'
注意:默认情况,该参数长度小于 250 个字符,如需更改,请根据实际情况调整参数
*/
AS
v_STR_TEMP VARCHAR2(502);
v_STR_CURR VARCHAR2(500);
v_STR_INSERT_1 VARCHAR2(100) := 'INSERT INTO CSDN_DEMO (ID, FIELD_1, FIELD_2) VALUES ( ';
v_STR_INSERT_2 VARCHAR2(500);
BEGIN
IF p_INSERT_STR IS NULL THEN
DBMS_OUTPUT.PUT_LINE('输入参数为空!');
END IF;
v_STR_TEMP := SUBSTR(p_INSERT_STR,1,250) || ',';
LOOP
v_STR_CURR := SUBSTR(v_STR_TEMP,1,INSTR(v_STR_TEMP,',')-1) || '¥';
v_STR_INSERT_2 := NULL;
LOOP
v_STR_INSERT_2 := v_STR_INSERT_2 || ''''||SUBSTR(v_STR_CURR,1,INSTR(v_STR_CURR,'¥')-1) || ''',';
v_STR_CURR := SUBSTR(v_STR_CURR,INSTR(v_STR_CURR,'¥')+1);
EXIT WHEN v_STR_CURR IS NULL;
END LOOP;
v_STR_INSERT_2 := SUBSTR(v_STR_INSERT_2,1,LENGTH(v_STR_INSERT_2)-1);
v_STR_INSERT_2 := v_STR_INSERT_2 || ')';
EXECUTE IMMEDIATE v_STR_INSERT_1 || v_STR_INSERT_2;
v_STR_TEMP := SUBSTR(v_STR_TEMP,INSTR(v_STR_TEMP,',')+1);
EXIT WHEN v_STR_TEMP IS NULL;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR('ERROR:'||SQLERRM,1,255));
END INSERT_DEMO;
调用:
SQL> EXEC INSERT_DEMO('001¥12¥13,002¥22¥23,003¥32¥33');PL/SQL 过程已成功完成。已用时间: 00: 00: 00.00
SQL> SELECT * FROM CSDN_DEMO;ID FIELD_1 FIELD_2
--- ---------- ----------
002 22 23
003 32 33
001 12 13已用时间: 00: 00: 00.10