儘量用簡單的SQL替代PL/SQL邏輯

LuiseDalian發表於2014-01-17
SQL
可以將很多複雜的結果集先用WITH的別名定義起來，方便後續呼叫。

點選(此處)摺疊或開啟

  1. drop table t;
  2. drop table t1;
  3. drop table t2;
  4. drop table t3;
  5. CREATE TABLE t AS SELECT object_id, object_name FROM dba_objects;
  6. CREATE TABLE t1 AS SELECT object_id, object_name FROM dba_objects WHERE 1 = 2;
  7. CREATE TABLE t2 AS SELECT object_id FROM t1;
  8. CREATE TABLE t3 AS SELECT object_name FROM t1;

  10. EXECUTE runstats_pkg.rs_start;
  11. INSERT ALL
  12.   WHEN object_id > 5 THEN INTO t1 VALUES(object_id, object_name)
  13.   WHEN object_id <> 2 THEN INTO t2 VALUES(object_id)
  14.   ELSE INTO t3 VALUES(object_name)
  15.   SELECT object_id, object_name FROM t;
  16.   
  17. EXECUTE runstats_pkg.rs_middle;

  19. --用自己的邏輯實現插入
  20. BEGIN
  21.   INSERT INTO t1 SELECT object_id, object_name FROM t WHERE object_id > 5;
  22.   INSERT INTO t2 SELECT object_id FROM t WHERE object_id <> 2;
  23.   INSERT INTO t3 SELECT object_name FROM t WHERE object_id <= 5 AND object_id = 2;
  24.   COMMIT;
  25. END;
  26. /

  28. EXECUTE runstats_pkg.rs_stop(1000);

  30. Run1 ran in 2265hsec
  31. Run2 ran in 2270hsec
  32. run 1 ran in 99.78% of the time

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1072074/，如需轉載，請註明出處，否則將追究法律責任。

相關文章