12c with语句增强功能

创建测试环境

SQL> conn loge/china@pdb1;已连接。SQL> drop table t1 purge;表已删除。SQL> create table t1 as  2  select 1 as id from dual  3  connect by level <=1000;表已创建。

说明：在12c中使用ctas创建表示后不需要在收集信息（EXEC DBMS_STATS.gather_table_stats('LOGE','T1');），将自动收集，可以查询（select * from user_tab_statistics）

在with中使用function

SQL> edit已写入 file afiedt.buf  1   with  2      function w_function(p_id in number) return number is  3      begin  4        return p_id;  5      end;  6  select w_function(1)  7  from t1  8* where rownum=1  9  /W_FUNCTION(1)-------------            1

在with中使用procedure

SQL> edit已写入 file afiedt.buf  1   with  2      procedure w_procedure (id in number) is  3      begin  4     dbms_output.put_line('id='||id);  5      end;  6      function w_function(p_id in number) return number is  7      begin  8        w_procedure(p_id);  9        return p_id; 10      end; 11  select w_function(id) 12  from t1 13* where rownum=1SQL> /W_FUNCTION(ID)--------------             1SQL> set serveroutput onSQL> /W_FUNCTION(ID)--------------             1id=1

PLSQL的支持，此示例比较两个函数性能

  1  CREATE OR REPLACE FUNCTION n_function(p_id IN NUMBER) RETURN NUMBER IS  2  BEGIN  3    RETURN p_id;  4* END;SQL> /函数已创建。

-- SET SERVEROUTPUT ONDECLARE  l_time    PLS_INTEGER;  l_cpu     PLS_INTEGER;  l_sql     VARCHAR2(32767);  l_cursor  SYS_REFCURSOR;  TYPE t_tab IS TABLE OF NUMBER;  l_tab t_tab;BEGIN  l_time := DBMS_UTILITY.get_time;  l_cpu  := DBMS_UTILITY.get_cpu_time;  l_sql := 'WITH              FUNCTION w_function(p_id IN NUMBER) RETURN NUMBER IS              BEGIN                RETURN p_id;              END;            SELECT w_function(id)            FROM   t1';  OPEN l_cursor FOR l_sql;  FETCH l_cursor  BULK COLLECT INTO l_tab;  CLOSE l_cursor;  DBMS_OUTPUT.put_line('W_FUNCTION  : ' ||                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');  l_time := DBMS_UTILITY.get_time;  l_cpu  := DBMS_UTILITY.get_cpu_time;  l_sql := 'SELECT n_function(id)            FROM   t1';  OPEN l_cursor FOR l_sql;  FETCH l_cursor  BULK COLLECT INTO l_tab;  CLOSE l_cursor;  DBMS_OUTPUT.put_line('N_FUNCTION: ' ||                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');END;/

结果：

W_FUNCTION  : Time=1 hsecs CPU Time=2 hsecsN_FUNCTION: Time=8 hsecs CPU Time=3 hsecsPL/SQL 过程已成功完成。

WITH_PLSQL提示，大小写不敏感

SQL> update /*+ WITH_PLSQl */ t1 a  2  set a.id=(with  3             function w_function(p_id in number) return number is  4             begin  5                     return p_id;  6             end;  7             select w_function(a.id)  8             from dual);  9  /已更新 1000 行。