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=1
SQL> /

W_FUNCTION(ID)
--------------
             1

SQL> set serveroutput on
SQL> /

W_FUNCTION(ID)
--------------
             1

id=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 ON
DECLARE
  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 hsecs
N_FUNCTION: Time=8 hsecs CPU Time=3 hsecs

PL/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 行。