oracle 中with,Oracle 12c中的with子句增强

最新推荐文章于 2021-04-04 22:59:03 发布

weixin_39721953

最新推荐文章于 2021-04-04 22:59:03 发布

阅读量352

点赞数

文章标签： oracle 中with

Oracle 12c中的with子句增强

1. 设置

创建测试表。

DROP TABLE test PURGE;

CREATE TABLE test AS

SELECT 1 AS id

FROM dual

CONNECT BY level <= 1000000;

2. WITH子句中的函数

WITH子句声明部分可用来定义函数，如下所示。

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

WITH_FUNCTION(ID)

-----------------

SQL>

有意思的是，当WITH子句中包含PL/SQL声明时，分号";"不再能用作SQL语句的终止符。如果我们使用它，SQL*Plus会等待更多命令文本输入。即使在官方文档中，也是使用了分号“;”和反斜杠“/”的组合。

从名字解析角度看，WITH子句PL/SQL声明部分定义的函数比当前模式中其他同名对象优先级要高。

3. WITH子句中的过程

即使不被使用，我们也可以在声明部分定义过程。

SET SERVEROUTPUT ON

WITH

PROCEDURE with_procedure(p_id IN NUMBER) IS

BEGIN

DBMS_OUTPUT.put_line('p_id=' || p_id);

END;

SELECT id

FROM test

WHERE rownum = 1

----------

SQL>

现实中，如果你打算从声明部分的函数中调用一个过程，你可以在声明部分定义一个过程。

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)

-----------------

p_id=1

SQL>

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>

使用动态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>

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>

从该测试可以看到，行内函数值消耗了普通函数三分之一的时间和CPU。

6. PRAGMA UDF

12c 版本前，人们经常会提到PRAGMA UDF，据说可通过行内PL/SQL来提升性能，同时，允许在SQL语句外定义PL/SQL对象。下列代码用PRAGMA重新定义之前的常规函数。

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS

PRAGMA UDF;

BEGIN

RETURN p_id;

END;

一旦函数被编译，从先前部分运行该函数会产生相当有趣的结果。

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>

用PRAGMA UDF的独立函数似乎一直比行内函数还快。

我以为从PL/SQL中调用PRAGMA UDF定义的函数会失败，可事实似乎不是这么个情况。

DECLARE

l_number NUMBER;

BEGIN

l_number := normal_function(1);

END;

PL/SQL procedure successfully completed.

SQL>

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>

加上WITH_PLSQL hint后，语句编译通过且如期运行。

UPDATE /*+ WITH_PLSQL */ t1 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);

1000000 rows updated.

SQL>

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)

-----------------

10 rows selected.

Elapsed: 00:00:10.07

SQL>

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)

---------------------------------

10 rows selected.

Elapsed: 00:00:01.04

SQL>

通过with子句，我们可以把很多原本需要存储过程来实现的复杂逻辑用一句SQL来进行表达，

在12C中，with的功能又有所增强，在with里可以直接定义一个函数。

比如

甚至可以支持自治事务函数，在函数中进行DML操作

而在11G中，则直接报错ORA-00905

需要注意的是：在function定义的end后面不能跟函数名称

这种语法在查询DG备库查询中可以派上用场，在一个STANDBY备库中，我们不能在只读数据库中创建函数，但通过with子句，我们把函数定义在select语句中，就完美规避了这一问题。

创建测试环境

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)

-------------

在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)

--------------

SQL> set serveroutput on

SQL> /

W_FUNCTION(ID)

--------------

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

weixin_39721953

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 中with,Oracle 12c中的with子句增强

Oracle 12c中的with子句增强1. 设置创建测试表。DROP TABLE test PURGE;CREATE TABLE test ASSELECT 1 AS idFROM dualCONNECT BY level <= 1000000;2. WITH子句中的函数WITH子句声明部分可用来定义函数，如下所示。WITHFUNCTION with_function(p_id IN ...
复制链接

扫一扫

oracle 中with,Oracle 12c中的with子句增强

“相关推荐”对你有帮助么？