为快捷显示Oracle执行计划创建存储过程

为快捷显示Oracle执行计划创建存储过程

第一种：不设置输出格式参数，即用默认的

SQL> create or replace procedure sql_explain(v_sql varchar2,v_format varchar2)  2  is  3  type explain_cursor_type is ref cursor;  4  explain_cursor explain_cursor_type;  5  a varchar2(2048);  6  begin  7    execute immediate 'explain plan for '||v_sql;  8    open explain_cursor for select PLAN_TABLE_OUTPUT  from table(dbms_xplan.display(null,null,v_format));  9    loop 10      fetch explain_cursor into a; 11      exit when explain_cursor%NOTFOUND; 12     dbms_output.put_line(a); 13    end loop; 14  end; 15  /Procedure created.SQL> exec sql_explain('select a.name,b.name from t1 a,t2 b where a.id=b.id and a.id=1','all');Plan hash value: 2680223496--------------------------------------------------| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------|   0 | SELECT STATEMENT             |       |     1 |    17 |     4   (0)| 00:00:01 ||   1 |  NESTED LOOPS                |       |     1 |    17 |     4   (0)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     8 |     1   (0)| 00:00:01 ||*  3 |    INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 ||*  4 |   TABLE ACCESS FULL          | T2    |     1 |     9 |     3   (0)| 00:00:01 |--------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------1 - SEL$12 - SEL$1 / A@SEL$13 - SEL$1 / A@SEL$14 - SEL$1 / B@SEL$1Predicate Information (identified by operation id):---------------------------------------------------3 - access("A"."ID"=1)4 - filter("B"."ID"=1)Column Projection Information (identified by operation id):-----------------------1 - (#keys=0) "A"."NAME"[VARCHAR2,32], "B"."NAME"[VARCHAR2,32]2 - "A"."NAME"[VARCHAR2,32]3 - "A".ROWID[ROWID,10]4 - "B"."NAME"[VARCHAR2,32]PL/SQL procedure successfully completed.

我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html