insert into 表名(a)values(1); insert into 表名(a)values(2); insert into 表名(a)values(3); insert into 表名(a)values(4); insert into 表名(a)values(5);
INSERT INTO TABLE1(A) SELECT SUBSTR(','||F1||',',INSTR(','||F1||',',',',1,RN)+1,INSTR(','||F1||',',',',1,RN+1)-INSTR(','||F1||',',',',1,RN)-1) "A" FROM ( SELECT '1,2,3,4,5,7' F1 FROM DUAL )T1, ( SELECT ROWNUM RN FROM ALL_OBJECTS WHERE ROWNUM <= (SELECT LENGTH('1,2,3,4,5,7')-LENGTH(REPLACE('1,2,3,4,5,7',',','')) F1 FROM DUAL)+1 ) WHERE INSTR(','||F1||',',',',1,RN) > 0;-- TEST SELECT STATEMENT FOR DATA VALUE "1,2,3,4,5,7" : SQL> SELECT SUBSTR(','||F1||',',INSTR(','||F1||',',',',1,RN)+1,INSTR(','||F1||',',',',1,RN+1)-INSTR(','||F1||',',',',1,RN)-1) "A" 2 FROM ( 3 SELECT '1,2,3,4,5,7' F1 FROM DUAL 4 )T1, 5 ( 6 SELECT ROWNUM RN 7 FROM ALL_OBJECTS 8 WHERE ROWNUM <= (SELECT LENGTH('1,2,3,4,5,7')-LENGTH(REPLACE('1,2,3,4,5,7',',','')) F1 FROM DUAL)+1 9 ) 10 WHERE INSTR(','||F1||',',',',1,RN) > 0;A -------------------------- 1 2 3 4 5 76 rows selected
create table d(number fd) ; insert into d(1); insert into d(2); insert into d(3); insert into d(4); insert into d(5);insert into 表名(a) select fd from d;drop table d;
看看这个行不?DECLARE i integer := 1; BEGIN FOR I IN 1 .. 8 LOOP INSERT INTO 表名 VALUES (i); COMMIT; END LOOP; END;
DECLARE i integer := 1; BEGIN FOR I IN 1 .. 8 LOOP INSERT INTO 表名 VALUES (i); COMMIT; END LOOP; END; 这种方法还是不错的
insert into 表名(a)values(2);
insert into 表名(a)values(3);
insert into 表名(a)values(4);
insert into 表名(a)values(5);
SELECT SUBSTR(','||F1||',',INSTR(','||F1||',',',',1,RN)+1,INSTR(','||F1||',',',',1,RN+1)-INSTR(','||F1||',',',',1,RN)-1) "A"
FROM (
SELECT '1,2,3,4,5,7' F1 FROM DUAL
)T1,
(
SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <= (SELECT LENGTH('1,2,3,4,5,7')-LENGTH(REPLACE('1,2,3,4,5,7',',','')) F1 FROM DUAL)+1
)
WHERE INSTR(','||F1||',',',',1,RN) > 0;-- TEST SELECT STATEMENT FOR DATA VALUE "1,2,3,4,5,7" :
SQL> SELECT SUBSTR(','||F1||',',INSTR(','||F1||',',',',1,RN)+1,INSTR(','||F1||',',',',1,RN+1)-INSTR(','||F1||',',',',1,RN)-1) "A"
2 FROM (
3 SELECT '1,2,3,4,5,7' F1 FROM DUAL
4 )T1,
5 (
6 SELECT ROWNUM RN
7 FROM ALL_OBJECTS
8 WHERE ROWNUM <= (SELECT LENGTH('1,2,3,4,5,7')-LENGTH(REPLACE('1,2,3,4,5,7',',','')) F1 FROM DUAL)+1
9 )
10 WHERE INSTR(','||F1||',',',',1,RN) > 0;A
--------------------------
1
2
3
4
5
76 rows selected
insert into d(1);
insert into d(2);
insert into d(3);
insert into d(4);
insert into d(5);insert into 表名(a) select fd from d;drop table d;
i integer := 1;
BEGIN
FOR I IN 1 .. 8 LOOP
INSERT INTO 表名 VALUES (i);
COMMIT;
END LOOP;
END;
i integer := 1;
BEGIN
FOR I IN 1 .. 8 LOOP
INSERT INTO 表名 VALUES (i);
COMMIT;
END LOOP;
END;
这种方法还是不错的