设置
创建测试表。
2. WITH子句中的函数
WITH子句声明部分可用来定义函数，如下所示。
有意思的是，当WITH子句中包含PL/SQL声明时，分号";"不再能用作SQL语句的终止符。如果我们使用它，SQL*Plus会等待更多命令文本输入。即使在官方文档中，也是使用了分号“;”和反斜杠“/”的组合。
从名字解析角度看，WITH子句PL/SQL声明部分定义的函数比当前模式中其他同名对象优先级要高。
3. WITH子句中的过程
即使不被使用，我们也可以在声明部分定义过程。
现实中，如果你打算从声明部分的函数中调用一个过程，你可以在声明部分定义一个过程。
WITH
PROCEDURE with_procedure(p_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.put_line('p_id=' || p_id);
END;
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
with_procedure(p_id);
RETURN p_id;
END;
SELECT with_function(id)
FROM test
WHERE rownum = 1
/
WITH_FUNCTION(ID)
-----------------
1
p_id=1
SQL>- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
4. PL/SQL支持
PL/SQL并不支持该特点。如果视图在PL/SQL中使用将会报编译错误，如下所示。
BEGIN
FOR cur_rec IN (WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM test
WHERE rownum = 1)
LOOP
NULL;
END LOOP;
END;
/
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
*
ERROR at line 3:
ORA-06550: line 3, column 30:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
SQL>- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
使用动态SQL可以绕过这个限制。
SET SERVEROUTPUT ON
DECLARE
l_sql VARCHAR2(32767);
l_cursor SYS_REFCURSOR;
l_value NUMBER;
BEGIN
l_sql := 'WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM test
WHERE rownum = 1';
OPEN l_cursor FOR l_sql;
FETCH l_cursor INTO l_value;
DBMS_OUTPUT.put_line('l_value=' || l_value);
CLOSE l_cursor;
END;
/
l_value=1
PL/SQL procedure successfully completed.
SQL>- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
PL/SQL中将该特点用于静态SQL是未来版本的事情。
5. 性能优势
定义行内PL/SQL代码的原因是为了改善性能。下面创建常规函数来进行比较。
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
/
运行如下测试，测量行内函数查询消耗的时间和CPU。
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 with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM test';
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('WITH_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 normal_function(id)
FROM test';
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
END;
/
WITH_FUNCTION : Time=45 hsecs CPU Time=39 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs
PL/SQL procedure successfully completed.
SQL>- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
从该测试可以看到，行内函数值消耗了普通函数三分之一的时间和CPU。
6. PRAGMA UDF
12c 版本前，人们经常会提到PRAGMA UDF，据说可通过行内PL/SQL来提升性能，同时，允许在SQL语句外定义PL/SQL对象。下列代码用PRAGMA重新定义之前的常规函数。
一旦函数被编译，从先前部分运行该函数会产生相当有趣的结果。
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 with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM test';
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('WITH_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 normal_function(id)
FROM test';
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
END;
/
WITH_FUNCTION : Time=44 hsecs CPU Time=40 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs
PL/SQL procedure successfully completed.
SQL>- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
用PRAGMA UDF的独立函数似乎一直比行内函数还快。
我以为从PL/SQL中调用PRAGMA UDF定义的函数会失败，可事实似乎不是这么个情况。
7. WITH_PLSQL Hint
如果包含PL/SQL声明部分的查询不是顶级查询，那么，顶级查询必须包含WITH_PLSQL hint。没有该hint，语句在编译时会失败，如下所示。
UPDATE test a
SET a.id = (WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(a.id)
FROM dual);
/
SET a.id = (WITH
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause
SQL>- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
加上WITH_PLSQL hint后，语句编译通过且如期运行。
8. DETERMINISTIC Hint
就像刘易斯指出的那样，WITH子句中使用函数会阻止发生DETERMINISTIC优化。
SET TIMING ON ARRAYSIZE 15
WITH
FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
BEGIN
DBMS_LOCK.sleep(1);
RETURN p_id;
END;
SELECT slow_function(id)
FROM test
WHERE ROWNUM <= 10;
/
SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Elapsed: 00:00:10.07
SQL>- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
9. 标量子查询缓冲
前面部分，我们看到行内函数定义对DETERMINISTIC hint优化上的负面影响。 庆幸的是，标量子查询缓冲并不被同样被影响。
SET TIMING ON
WITH
FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
BEGIN
DBMS_LOCK.sleep(1);
RETURN p_id;
END;
SELECT (SELECT slow_function(id) FROM dual)
FROM test
WHERE ROWNUM <= 10;
/
(SELECTSLOW_FUNCTION(ID)FROMDUAL)
---------------------------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Elapsed: 00:00:01.04
SQL>- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
