可以将很多复杂的结果集先用WITH的别名定义起来，方便后续调用。

点击(此处)折叠或打开

drop table t;
drop table t1;
drop table t2;
drop table t3;
CREATE TABLE t AS SELECT object_id, object_name FROM dba_objects;
CREATE TABLE t1 AS SELECT object_id, object_name FROM dba_objects WHERE 1 = 2;
CREATE TABLE t2 AS SELECT object_id FROM t1;
CREATE TABLE t3 AS SELECT object_name FROM t1;
EXECUTE runstats_pkg.rs_start;
INSERT ALL
WHEN object_id > 5 THEN INTO t1 VALUES(object_id, object_name)
WHEN object_id <> 2 THEN INTO t2 VALUES(object_id)
ELSE INTO t3 VALUES(object_name)
SELECT object_id, object_name FROM t;
EXECUTE runstats_pkg.rs_middle;
--用自己的逻辑实现插入
BEGIN
INSERT INTO t1 SELECT object_id, object_name FROM t WHERE object_id > 5;
INSERT INTO t2 SELECT object_id FROM t WHERE object_id <> 2;
INSERT INTO t3 SELECT object_name FROM t WHERE object_id <= 5 AND object_id = 2;
COMMIT;
END;
/
EXECUTE runstats_pkg.rs_stop(1000);
Run1 ran in 2265hsec
Run2 ran in 2270hsec
run 1 ran in 99.78% of the time

ITPub博客

尽量用简单的SQL替代PL/SQL逻辑