ITPub博客

首页 > 数据库 > Oracle > 尽量用简单的SQL替代PL/SQL逻辑

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

原创 Oracle 作者:luisedalian 时间:2014-01-17 12:06:06 0 删除 编辑
可以将很多复杂的结果集先用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;

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

  17. --用自己的逻辑实现插入
  18. BEGIN
  19.   INSERT INTO t1 SELECT object_id, object_name FROM t WHERE object_id > 5;
  20.   INSERT INTO t2 SELECT object_id FROM t WHERE object_id <> 2;
  21.   INSERT INTO t3 SELECT object_name FROM t WHERE object_id <= 5 AND object_id = 2;
  22.   COMMIT;
  23. END;
  24. /

  25. EXECUTE runstats_pkg.rs_stop(1000);

  26. Run1 ran in 2265hsec
  27. Run2 ran in 2270hsec
  28. run 1 ran in 99.78% of the time

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1072074/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5641821