【IT168 Oracle】Oracle 11g中PL/SQL新增了很多特性，在性能和易用性方面做了不少的提升，还有一些功能性的增强。
首先提一下函数的指定参数的调用。
在11g以前，如果一个函数在SQL中被调用的话，那么不能通过指定参数的方式。如果一个函数有10个输入参数，前面9个都可以使用默认值，只有最后一个需要指定，在11g以前，如果函数在SQL中使用，那么必须把前面9个参数补齐。没有办法通过指定参数的方法调用。
SQL> CREATE OR REPLACE FUNCTION F_TEST 2 ( 3 P1 IN NUMBER DEFAULT 0, 4 P2 IN NUMBER DEFAULT 0, 5 P3 IN NUMBER DEFAULT 0 6 ) RETURN NUMBER AS 7 BEGIN 8 RETURN 0; 9 END; 10 / 函数已创建。 SQL> SELECT F_TEST, F_TEST(1), F_TEST(1, 1), F_TEST(1, 1, 1) FROM DUAL; F_TEST F_TEST(1) F_TEST(1,1) F_TEST(1,1,1) ---------- ---------- ----------- ------------- 0 0 0 0 SQL> SELECT F_TEST(P3 => 1) FROM DUAL; SELECT F_TEST(P3 => 1) FROM DUAL *第 1 行出现错误: ORA-00907: 缺失右括号 SQL> SELECT * FROM V$VERSION; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
不过在11g中，这个限制不再存在，调用函数变得更加方便：
SQL> CONN YANGTK/yangtk@ORA11G已连接。 SQL> CREATE OR REPLACE FUNCTION F_TEST 2 ( 3 P1 IN NUMBER DEFAULT 0, 4 P2 IN NUMBER DEFAULT 0, 5 P3 IN NUMBER DEFAULT 0 6 ) RETURN NUMBER AS 7 BEGIN 8 RETURN 0; 9 END; 10 / 函数已创建。 SQL> SELECT F_TEST(P3 => 1) FROM DUAL; F_TEST(P3=>1) ------------- 0 SQL> SELECT F_TEST(P1 => 1, P2 => 2, P3 => 3) FROM DUAL; F_TEST(P1=>1,P2=>2,P3=>3) ------------------------- 0 SQL> SELECT F_TEST(1, P3 => 3) FROM DUAL; F_TEST(1,P3=>3) --------------- 0 SQL> SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for Linux: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production
Oracle另外增加的一个新功能是添加了CONINUE语句：
SQL> CREATE OR REPLACE FUNCTION F_MULTI (P_IN1 IN NUMBER, P_IN2 IN NUMBER) RETURN NUMBER AS 2 V_RETURN NUMBER DEFAULT 1; 3 BEGIN 4 FOR I IN P_IN1..P_IN2 LOOP 5 IF I = 0 THEN 6 CONTINUE; 7 END IF; 8 V_RETURN := V_RETURN * I; 9 END LOOP; 10 RETURN V_RETURN; 11 END; 12 / 函数已创建。 SQL> SELECT F_MULTI(-4, 2) FROM DUAL; F_MULTI(-4,2) ------------- 48
这里不用CONTINUE语句也很容易实现，不过用CONTINUE更加自然一些。这里除了可以使用CONTINUE语句，还可以利用CONTINUE WHEN语句：
SQL> CREATE OR REPLACE FUNCTION F_MULTI (P_IN1 IN NUMBER, P_IN2 IN NUMBER) RETURN NUMBER AS 2 V_RETURN NUMBER DEFAULT 1; 3 BEGIN 4 FOR I IN P_IN1..P_IN2 LOOP 5 CONTINUE WHEN I = 0; 6 V_RETURN := V_RETURN * I; 7 END LOOP; 8 RETURN V_RETURN; 9 END; 10 / 函数已创建。 SQL> SELECT F_MULTI(-4, 2) FROM DUAL; F_MULTI(-4,2) ------------- 48
以前取SEQUENCE的NEXTVAL和CURRVAL只能通过SQL语句，现在可以在PL/SQL中通过赋值语句获取：
SQL> CREATE SEQUENCE S_ID; 序列已创建。 SQL> SET SERVEROUT ON SQL> DECLARE 2 V_ID NUMBER; 3 BEGIN 4 V_ID := S_ID.NEXTVAL; 5 DBMS_OUTPUT.PUT_LINE(V_ID); 6 END; 7 / 1 PL/SQL 过程已成功完成。
这种方法比使用SELECT INTO要方便，更重要的是，由于不执行SQL语句，这种方式的效率更高。
从SQL的运行时间上看，二者的差别不大，使用赋值的方法似乎略快一些。不过采用赋值的方法最大的好处是可以减少LATCH，在多用户并发访问的情况下效率更高。SQL> CREATE SEQUENCE S_1; 序列已创建。 SQL> CREATE SEQUENCE S_2; 序列已创建。 SQL> CREATE OR REPLACE PROCEDURE P1 AS 2 V_SEQ NUMBER; 3 BEGIN 4 FOR I IN 1..100000 LOOP 5 SELECT S_1.NEXTVAL INTO V_SEQ FROM DUAL; 6 END LOOP; 7 END; 8 / 过程已创建。 SQL> CREATE OR REPLACE PROCEDURE P2 AS 2 V_SEQ NUMBER; 3 BEGIN 4 FOR I IN 1..100000 LOOP 5 V_SEQ := S_2.NEXTVAL; 6 END LOOP; 7 END; 8 / 过程已创建。 SQL> SET TIMING ON SQL> EXEC P1 PL/SQL 过程已成功完成。 已用时间: 00: 00: 06.81 SQL> EXEC P2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 06.35 SQL> EXEC P1
PL/SQL 过程已成功完成。
下面通过查询V$SESSTAT视图和V$LATCH视图，分布记录两个存储过程的执行统计信息，并将二者的差异显示出来。
在执行之前，需要先建立一张临时表：
二者的执行时间仍然相差不多，上面的差异中redo和undo都不是关注的内容，采用SELECT INTO的方式会导致共享池的争用，而采用赋值的方式会占用更多的PGA内存。SQL> SET TIMING OFF SQL> CREATE GLOBAL TEMPORARY TABLE T_SESSION_STAT 2 (ID NUMBER, NAME VARCHAR2(100), VALUE NUMBER); 表已创建。 SQL> SET SERVEROUT ON SQL> DECLARE 2 V_NUMBER1 NUMBER; 3 V_NUMBER2 NUMBER; 4 BEGIN 5 6 INSERT INTO T_SESSION_STAT SELECT 1, 'STAT:' || NAME, VALUE 7 FROM V$SESSTAT A, V$STATNAME B 8 WHERE A.STATISTIC# = B.STATISTIC# 9 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1) 10 UNION ALL 11 SELECT 1, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH; 12 13 V_NUMBER1 := DBMS_UTILITY.GET_TIME; 14 P1; 15 V_NUMBER1 := DBMS_UTILITY.GET_TIME - V_NUMBER1; 16 17 INSERT INTO T_SESSION_STAT SELECT 2, 'STAT:' || NAME, VALUE 18 FROM V$SESSTAT A, V$STATNAME B 19 WHERE A.STATISTIC# = B.STATISTIC# 20 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1) 21 UNION ALL 22 SELECT 2, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH; 23 24 V_NUMBER2 := DBMS_UTILITY.GET_TIME; 25 P2; 26 V_NUMBER2 := DBMS_UTILITY.GET_TIME - V_NUMBER2; 27 28 INSERT INTO T_SESSION_STAT SELECT 3, 'STAT:' || NAME, VALUE 29 FROM V$SESSTAT A, V$STATNAME B 30 WHERE A.STATISTIC# = B.STATISTIC# 31 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1) 32 UNION ALL 33 SELECT 3, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH; 34 35 DBMS_OUTPUT.PUT_LINE('P1 EXECUTE ' || V_NUMBER1/100 || ' SECONDS'); 36 DBMS_OUTPUT.PUT_LINE('P2 EXECUTE ' || V_NUMBER2/100 || ' SECONDS'); 37 38 FOR C IN 39 ( 40 SELECT * 41 FROM 42 ( 43 SELECT A.NAME, C.VALUE + A.VALUE - 2 * B.VALUE VALUE 44 FROM 45 T_SESSION_STAT A, 46 T_SESSION_STAT B, 47 T_SESSION_STAT C 48 WHERE A.NAME = B.NAME 49 AND A.NAME = C.NAME 50 AND A.ID = 1 51 AND B.ID = 2 52 AND C.ID = 3 53 ) 54 WHERE ABS(VALUE) > 100 55 ) LOOP 56 DBMS_OUTPUT.PUT_LINE(RPAD(C.NAME, 50, ' ') || C.VALUE); 57 END LOOP; 58 59 END; 60 / P1 EXECUTE 6.29 SECONDS P2 EXECUTE 6.34 SECONDS STAT:session pga memory 131072 STAT:redo size -308 STAT:undo change vector size 292 LATCH:cache buffers chains -171 LATCH:redo allocation -103 LATCH:shared pool simulator -99993 PL/SQL 过程已成功完成。
LATCH是串行操作，一遍情况下内存不是问题，因此应该尽量选择来提高并发性能。不过根据测试也可以看到，二者的性能差异很小，选择哪种方法都不会带来明显的性能变化。
Oracle11g推出了一个新的数据类型SIMPLE_INTEGER，这种数据类型表示-2147483648到2147483647，这个数据类型不为空。
对于这个数据类型，Oracle就不会进行空的判断，也不会处理溢出的情况。因此Oracle可以将这个数据类型的操作直接作用于硬件，从而提高性能。
下面简单对比一下NUMBER类型和SIMPLE_INTEGER类型的性能差别：
SQL> SHOW PARAMETER PLSQL_CODE_TYPE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ plsql_code_type string INTERPRETED SQL> CREATE OR REPLACE PROCEDURE P_NUMBER AS 2 V_NUM1 NUMBER DEFAULT 1; 3 V_NUM2 NUMBER DEFAULT 2; 4 BEGIN 5 FOR I IN 1..1000000 LOOP 6 V_NUM1 := V_NUM1 + V_NUM2 - V_NUM1 * V_NUM2; 7 V_NUM2 := (V_NUM2 + V_NUM1 * V_NUM2) / V_NUM2; 8 END LOOP; 9 DBMS_OUTPUT.PUT_LINE('V_NUM1: ' || V_NUM1); 10 DBMS_OUTPUT.PUT_LINE('V_NUM2: ' || V_NUM2); 11 END; 12 / 过程已创建。 SQL> CREATE OR REPLACE PROCEDURE P_SIMPLE_INTEGER AS 2 V_NUM1 SIMPLE_INTEGER DEFAULT 1; 3 V_NUM2 SIMPLE_INTEGER DEFAULT 2; 4 BEGIN 5 FOR I IN 1..1000000 LOOP 6 V_NUM1 := V_NUM1 + V_NUM2 - V_NUM1 * V_NUM2; 7 V_NUM2 := (V_NUM2 + V_NUM1 * V_NUM2) / V_NUM2; 8 END LOOP; 9 DBMS_OUTPUT.PUT_LINE('V_NUM1: ' || V_NUM1); 10 DBMS_OUTPUT.PUT_LINE('V_NUM2: ' || V_NUM2); 11 END; 12 / 过程已创建。 SQL> SET SERVEROUT ON SQL> SET TIMING ON SQL> EXEC P_NUMBER V_NUM1: 1 V_NUM2: 2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.73 SQL> EXEC P_NUMBER V_NUM1: 1 V_NUM2: 2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.48 SQL> EXEC P_SIMPLE_INTEGER V_NUM1: 1 V_NUM2: 2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.42 SQL> EXEC P_SIMPLE_INTEGER V_NUM1: 1 V_NUM2: 2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.40
从上面的对比可以看到，使用SIMPLE_INTEGER大概可以得到1/6的性能提升，这还是在PLSQL_CODE_TYPE为INTERPRETED的情况下，如果将参数改为NATIVE，性能提升更加明显：
SQL> SET TIMING OFF SQL> ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE; 会话已更改。 SQL> CREATE OR REPLACE PROCEDURE P_NUMBER AS 2 V_NUM1 NUMBER DEFAULT 1; 3 V_NUM2 NUMBER DEFAULT 2; 4 BEGIN 5 FOR I IN 1..1000000 LOOP 6 V_NUM1 := V_NUM1 + V_NUM2 - V_NUM1 * V_NUM2; 7 V_NUM2 := (V_NUM2 + V_NUM1 * V_NUM2) / V_NUM2; 8 END LOOP; 9 DBMS_OUTPUT.PUT_LINE('V_NUM1: ' || V_NUM1); 10 DBMS_OUTPUT.PUT_LINE('V_NUM2: ' || V_NUM2); 11 END; 12 / 过程已创建。 SQL> CREATE OR REPLACE PROCEDURE P_SIMPLE_INTEGER AS 2 V_NUM1 SIMPLE_INTEGER DEFAULT 1; 3 V_NUM2 SIMPLE_INTEGER DEFAULT 2; 4 BEGIN 5 FOR I IN 1..1000000 LOOP 6 V_NUM1 := V_NUM1 + V_NUM2 - V_NUM1 * V_NUM2; 7 V_NUM2 := (V_NUM2 + V_NUM1 * V_NUM2) / V_NUM2; 8 END LOOP; 9 DBMS_OUTPUT.PUT_LINE('V_NUM1: ' || V_NUM1); 10 DBMS_OUTPUT.PUT_LINE('V_NUM2: ' || V_NUM2); 11 END; 12 / 过程已创建。 SQL> SET TIMING ON SQL> EXEC P_NUMBER V_NUM1: 1 V_NUM2: 2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.40 SQL> EXEC P_NUMBER V_NUM1: 1 V_NUM2: 2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.40 SQL> EXEC P_SIMPLE_INTEGER V_NUM1: 1 V_NUM2: 2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.26 SQL> EXEC P_SIMPLE_INTEGER V_NUM1: 1 V_NUM2: 2 PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.25
首先可以看到PLSQL_CODE_TYPE设置为NATIVE带来的性能提升，使用NUMBER提升了1/6，而使用SIMPLE_INTEGER则提升了3/8。
现在采用SIMPLE_INTEGER数据类型只需要NUMBER类型的5/8的时间。可见不管PLSQL_CODE_TYPE设置为何值，采用SIMPLE_INTEGER都能带来性能的提升。不过使用SIMPLE_INTEGER就需要用户自己控制溢出了，否则可能得到错误的结果。
Oracle在调用函数的时候可以将调用过程直接替换为子查询的代码，这样可以避免在运行过程中调用开销，Oracle提供了一个新的PRAGMA INLINE来指示函数是否进行INLINE优化：
分别建立两个递归调用的函数，一个使用了11g提供的PRAGMA INLINE，强制Oracle对函数F_TEST_INLINE进行INLINE优化，看看调用两个函数所需的时间：SQL> CREATE OR REPLACE FUNCTION F_TEST (P_IN IN NUMBER) RETURN NUMBER AS 2 BEGIN 3 IF P_IN > 1 THEN 4 RETURN P_IN + F_TEST(P_IN - 1); 5 ELSE 6 RETURN 1; 7 END IF; 8 END; 9 / 函数已创建。 SQL> CREATE OR REPLACE FUNCTION F_TEST_INLINE (P_IN IN NUMBER) RETURN NUMBER AS 2 BEGIN 3 IF P_IN > 1 THEN 4 PRAGMA INLINE (F_TEST_INLINE, 'YES'); 5 RETURN P_IN + F_TEST_INLINE(P_IN - 1); 6 ELSE 7 RETURN 1; 8 END IF; 9 END; 10 / 函数已创建。
SQL> SET TIMING ON SQL> SELECT F_TEST_INLINE(1000000) FROM DUAL; F_TEST_INLINE(1000000) ---------------------- 5.0000E+11 已用时间: 00: 00: 01.32 SQL> SELECT F_TEST(1000000) FROM DUAL; F_TEST(1000000) --------------- 5.0000E+11 已用时间: 00: 00: 18.68 SQL> SELECT F_TEST_INLINE(1000000) FROM DUAL; F_TEST_INLINE(1000000) ---------------------- 5.0000E+11 已用时间: 00: 00: 00.42 SQL> SELECT F_TEST(1000000) FROM DUAL; F_TEST(1000000) --------------- 5.0000E+11 已用时间: 00: 00: 00.57
无论是第一次调用，还是第二次调用，使用了INLINE的方法都比普通调用有一个明显的性能提升。
除了强制使用PRAGMA INLINE方法外，Oracle还提供了自动INLINE的方式，只需要将初始化参数
PLSQL_OPTIMIZE_LEVLE设置为3，Oracle会自动选择合适的过程进行INLINE优化： SQL> SHOW PARAMETER PLSQL_OPTIMIZE_LEVEL NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ plsql_optimize_level integer 2 SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3; 会话已更改。 SQL> CREATE OR REPLACE FUNCTION F_TEST (P_IN IN NUMBER) RETURN NUMBER AS 2 BEGIN 3 IF P_IN > 1 THEN 4 RETURN P_IN + F_TEST(P_IN - 1); 5 ELSE 6 RETURN 1; 7 END IF; 8 END; 9 / 函数已创建。 SQL> SELECT F_TEST(1000000) FROM DUAL; F_TEST(1000000) --------------- 5.0000E+11 已用时间: 00: 00: 00.40 SQL> SELECT F_TEST(1000000) FROM DUAL; F_TEST(1000000) --------------- 5.0000E+11 已用时间: 00: 00: 00.46
可以看到设置了自动优化功能，F_TEST函数的调用也达到了PRAGMA INLINE函数的相同效果。需要说明的是，INLINE优化在大部分情况下会带来性能的提升，但是这个性能的提升是以占用更多的内存为代价的。下面看看两种方式的统计差别：
SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2; 会话已更改。 SQL> CREATE OR REPLACE FUNCTION F_TEST (P_IN IN NUMBER) RETURN NUMBER AS 2 BEGIN 3 IF P_IN > 1 THEN 4 RETURN P_IN + F_TEST(P_IN - 1); 5 ELSE 6 RETURN 1; 7 END IF; 8 END; 9 / 函数已创建。 SQL> CREATE OR REPLACE FUNCTION F_TEST_INLINE (P_IN IN NUMBER) RETURN NUMBER AS 2 BEGIN 3 IF P_IN > 1 THEN 4 PRAGMA INLINE (F_TEST_INLINE, 'YES'); 5 RETURN P_IN + F_TEST_INLINE(P_IN - 1); 6 ELSE 7 RETURN 1; 8 END IF; 9 END; 10 / 函数已创建。 SQL> SET SERVEROUT ON SQL> DECLARE 2 V_NUMBER1 NUMBER; 3 V_NUMBER2 NUMBER; 4 V_RES NUMBER; 5 BEGIN 6 7 INSERT INTO T_SESSION_STAT SELECT 1, 'STAT:' || NAME, VALUE 8 FROM V$SESSTAT A, V$STATNAME B 9 WHERE A.STATISTIC# = B.STATISTIC# 10 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1) 11 UNION ALL 12 SELECT 1, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH; 13 14 V_NUMBER1 := DBMS_UTILITY.GET_TIME; 15 V_RES := F_TEST_INLINE(1000000); 16 V_NUMBER1 := DBMS_UTILITY.GET_TIME - V_NUMBER1; 17 18 INSERT INTO T_SESSION_STAT SELECT 2, 'STAT:' || NAME, VALUE 19 FROM V$SESSTAT A, V$STATNAME B 20 WHERE A.STATISTIC# = B.STATISTIC# 21 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1) 22 UNION ALL 23 SELECT 2, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH; 24 25 V_NUMBER2 := DBMS_UTILITY.GET_TIME; 26 V_RES := F_TEST(1000000); 27 V_NUMBER2 := DBMS_UTILITY.GET_TIME - V_NUMBER2; 28 29 INSERT INTO T_SESSION_STAT SELECT 3, 'STAT:' || NAME, VALUE 30 FROM V$SESSTAT A, V$STATNAME B 31 WHERE A.STATISTIC# = B.STATISTIC# 32 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1) 33 UNION ALL 34 SELECT 3, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH; 35 36 DBMS_OUTPUT.PUT_LINE('P1 EXECUTE ' || V_NUMBER1/100 || ' SECONDS'); 37 DBMS_OUTPUT.PUT_LINE('P2 EXECUTE ' || V_NUMBER2/100 || ' SECONDS'); 38 39 FOR C IN 40 ( 41 SELECT * 42 FROM 43 ( 44 SELECT A.NAME, C.VALUE + A.VALUE - 2 * B.VALUE VALUE 45 FROM 46 T_SESSION_STAT A, 47 T_SESSION_STAT B, 48 T_SESSION_STAT C 49 WHERE A.NAME = B.NAME 50 AND A.NAME = C.NAME 51 AND A.ID = 1 52 AND B.ID = 2 53 AND C.ID = 3 54 ) 55 WHERE ABS(VALUE) > 100 56 ) LOOP 57 DBMS_OUTPUT.PUT_LINE(RPAD(C.NAME, 50, ' ') || C.VALUE); 58 END LOOP; 59 60 END; 61 / P1 EXECUTE 1.4 SECONDS P2 EXECUTE 2.26 SECONDS STAT:session pga memory 89194496 PL/SQL 过程已成功完成。
Oracle的inline操作将调用操作直接替换为程序的代码，避免了调用的开销，获得了明显的性能提升，但是这种方法消耗更多的PGA内容也是显而易见的。
对于特别复杂的调用情况，或者递归、循环调用次数过多，可能会造成INLINE调用方式的内存占用过大，从而导致性能下降甚至出现内存耗尽的情况。INLINE带来的并非总是性能的提升，使用不当也可能造成严重的性能问题。
Oracle提供的PRAGMA INLINE同样可以强制禁止过程INLINE优化的发生。
SQL> CREATE OR REPLACE TRIGGER TRI_T_3 AFTER INSERT ON T 2 FOR EACH ROW DISABLE 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(3) 5 END; 6 / 警告: 创建的触发器带有编译错误。 SQL> INSERT INTO T VALUES (2, 'B'); 1 2 已创建 1 行。 SQL> CREATE OR REPLACE TRIGGER TRI_T_3 AFTER INSERT ON T 2 FOR EACH ROW DISABLE 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(3); 5 END; 6 / 触发器已创建 SQL> INSERT INTO T VALUES (3, 'C'); 1 2 已创建 1 行。 SQL> ALTER TRIGGER TRI_T_3 ENABLE; 触发器已更改 SQL> INSERT INTO T VALUES (4, 'D'); 3 1 2 已创建 1 行。
11g中对于触发器部分有了一定的增强，主要表现在两个方面。一个是对触发器的触发顺序可以进行控制。另一个是可以定义一个复合触发器。
首先介绍一下触发器的顺序控制。
在11g以前，Oracle的文档上一直是这样描述的：Oracle是不会保证同种类型的触发器的触发先后顺序。不过在测试中发现，Oracle似乎是根据触发器建立时间的先后决定触发顺序的：
SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30)); 表已创建。 SQL> CREATE OR REPLACE TRIGGER TRI_T_1 AFTER INSERT ON T 2 FOR EACH ROW 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(1); 5 END; 6 / 触发器已创建 SQL> CREATE OR REPLACE TRIGGER TRI_T_2 AFTER INSERT ON T 2 FOR EACH ROW 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(2); 5 END; 6 / 触发器已创建 SQL> CREATE OR REPLACE TRIGGER TRI_T_3 AFTER INSERT ON T 2 FOR EACH ROW 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(3); 5 END; 6 / 触发器已创建 SQL> SET SERVEROUT ON SQL> INSERT INTO T VALUES (1, 'A'); 3 2 1 已创建 1 行。 SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM USER_OBJECTS; 3 2 1 3 2 1 3 2 1 3 2 1 已创建4行。
如果将TRI_T_2删除，并重写建立，则这个触发器会最先触发：
SQL> DROP TRIGGER TRI_T_2; 触发器已删除。 SQL> CREATE OR REPLACE TRIGGER TRI_T_2 AFTER INSERT ON T 2 FOR EACH ROW 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(2); 5 END; 6 / 触发器已创建 SQL> INSERT INTO T VALUES (2, 'B'); 2 3 1 已创建 1 行。
如果同一类的触发器在触发的时候需要按照某种顺序触发，那么仅靠触发器创建时间先后这个条件是无法确保顺序不会发生变化的。
11g触发器新增了FOLLOWS语句，使得触发器建立的过程中可以指定顺序：
SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30)); 表已创建。 SQL> CREATE OR REPLACE TRIGGER TRI_T_1 AFTER INSERT ON T 2 FOR EACH ROW 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(1); 5 END; 6 / 触发器已创建 SQL> CREATE OR REPLACE TRIGGER TRI_T_2 AFTER INSERT ON T 2 FOR EACH ROW FOLLOWS TRI_T_1 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(2); 5 END; 6 / 触发器已创建 SQL> CREATE OR REPLACE TRIGGER TRI_T_3 AFTER INSERT ON T 2 FOR EACH ROW FOLLOWS TRI_T_2 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(3); 5 END; 6 / 触发器已创建 SQL> SET SERVEROUT ON SQL> INSERT INTO T VALUES (1, 'A'); 1 2 3 已创建 1 行。
Oracle除了增加了FOLLOWS语法，还可以在创建触发器的时候指定DISABLE/ENABLE。这对于没有把握触发器是否会成功编译的情况很有帮助：
SQL> CREATE OR REPLACE TRIGGER TRI_T_3 AFTER INSERT ON T 2 FOR EACH ROW FOLLOWS TRI_T_2 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(3) 5 END; 6 / 警告: 创建的触发器带有编译错误。 SQL> INSERT INTO T VALUES (2, 'B'); INSERT INTO T VALUES (2, 'B') *第 1 行出现错误: ORA-04098: 触发器 'YANGTK.TRI_T_3' 无效且未通过重新验证
如果触发器建立的时候报错，那么这个基表相应的操作都会由于触发器的错误是失败。11g可以在建立触发器的时候就指定DISABLE，避免上面的问题产生：
SQL> CREATE OR REPLACE TRIGGER TRI_T_3 AFTER INSERT ON T 2 FOR EACH ROW DISABLE 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(3) 5 END; 6 / 警告: 创建的触发器带有编译错误。 SQL> INSERT INTO T VALUES (2, 'B'); 1 2 已创建 1 行。
这样避免了触发器建立中出现的错误影响到基表的DML操作。在触发器编译成功后，就可以通过ENABLE来启动触发器了。
SQL> CREATE OR REPLACE TRIGGER TRI_T_3 AFTER INSERT ON T 2 FOR EACH ROW DISABLE 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(3) 5 END; 6 / 警告: 创建的触发器带有编译错误。 SQL> INSERT INTO T VALUES (2, 'B'); 1 2 已创建 1 行。 SQL> CREATE OR REPLACE TRIGGER TRI_T_3 AFTER INSERT ON T 2 FOR EACH ROW DISABLE 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(3); 5 END; 6 / 触发器已创建 SQL> INSERT INTO T VALUES (3, 'C'); 1 2 已创建 1 行。 SQL> ALTER TRIGGER TRI_T_3 ENABLE; 触发器已更改 SQL> INSERT INTO T VALUES (4, 'D'); 3 1 2 已创建 1 行。
11g中对于触发器部分有了一定的增强，主要表现在两个方面。一个是对触发器的触发顺序可以进行控制。另一个是可以定义一个复合触发器。
介绍一下复合触发器。复合触发器中可以包括BEFORE STATEMENT、BEFORE EACH ROW、AFTER EACH ROW和AFTER STATEMENT四个部分，将四种类型的触发器集成在一个触发器中，如果需要多个类型的触发器配合使用，采用复合触发器会显得逻辑更加清晰，而且不容易出现错误。在复合触发器中定义的变量可以在不同类型的触发语句中使用，不再需要使用外部包存储中间结果。而且利用复合触发器的批量操作还可以提高触发器的性能。
下面先看一个简单的COMPOUND TRIGGER的语法：
SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30)); 表已创建。 SQL> CREATE OR REPLACE TRIGGER TRI_COMPOUND FOR INSERT OR UPDATE OR DELETE ON T 2 COMPOUND TRIGGER 3 BEFORE STATEMENT IS 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE('BEFORE STATEMENT'); 6 END BEFORE STATEMENT; 7 8 BEFORE EACH ROW IS 9 BEGIN 10 DBMS_OUTPUT.PUT_LINE('BEFORE EACH ROW'); 11 END BEFORE EACH ROW; 12 13 AFTER EACH ROW IS 14 BEGIN 15 DBMS_OUTPUT.PUT_LINE('AFTER EACH ROW'); 16 END AFTER EACH ROW; 17 18 AFTER STATEMENT IS 19 BEGIN 20 DBMS_OUTPUT.PUT_LINE('AFTER STATEMENT'); 21 END AFTER STATEMENT; 22 END; 23 / 触发器已创建 SQL> SET SERVEROUT ON SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB; BEFORE STATEMENT BEFORE EACH ROW AFTER EACH ROW BEFORE EACH ROW AFTER EACH ROW BEFORE EACH ROW AFTER EACH ROW AFTER STATEMENT 已创建3行。
了解了COMPOUND触发器的语法，下面看看如何利用COMPOUND TRIGGER来简化变异表的处理。在以前的一篇文章中，介绍了：通过触发器复制包含LONG类型的表：http://yangtingkun.itpub.net/post/468/41936
里面包括了变异表触发器的处理方法，下面用COMPOUND TRIGGER来解决这个问题：
SQL> CREATE TABLE T_LONG (ID NUMBER PRIMARY KEY, COMMENTS LONG); 表已创建。 SQL> CREATE TABLE T_LONG_LOG (ID NUMBER PRIMARY KEY, COMMENTS CLOB); 表已创建。 SQL> CREATE OR REPLACE TRIGGER TRI_T_LONG_COMPOUND FOR INSERT ON T_LONG 2 COMPOUND TRIGGER 3 TYPE T_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 4 V_ID T_NUMBER; 5 BEFORE EACH ROW IS 6 BEGIN 7 V_ID(V_ID.COUNT + 1) := :NEW.ID; 8 END BEFORE EACH ROW; 9 10 AFTER STATEMENT IS 11 BEGIN 12 FORALL I IN 1..V_ID.COUNT 13 INSERT INTO T_LONG_LOG SELECT ID, TO_LOB(COMMENTS) FROM T_LONG WHERE ID = V_ID(I); 14 END AFTER STATEMENT; 15 END; 16 / 触发器已创建 SQL> INSERT INTO T_LONG SELECT ROWNUM, TNAME FROM TAB; 已创建5行。 SQL> COL COMMENTS FORMAT A40 SQL> SELECT * FROM T_LONG; ID COMMENTS ---------- ---------------------------------------- 1 T 2 T_LONG 3 T_LONG_LOG 4 T_SESSION 5 T_SESSION_STAT SQL> SELECT * FROM T_LONG_LOG; ID COMMENTS ---------- ---------------------------------------- 1 T 2 T_LONG 3 T_LONG_LOG 4 T_SESSION 5 T_SESSION_STAT
对比一下就可以看出，使用COMPOUND触发器要比建立三个触发器加一个包要简化很多，而且初始化，处理，清除等所有的步骤都在一起，也不容易出错。
而且由于COMPOUND所有的代码可以集中在一起，现在很多操作可以批量处理，这样COMPOUND还可以提高性能。
现在仍然使用第一个例子，为T增加一张LOG表，对T表所有的INSERT都同时插入到LOG表中，对比一下COMPOUND TRIGGER和普通TRIGGER的性能差异：
SQL> CREATE TABLE T_LOG (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> TRUNCATE TABLE T;
表被截断。
下面建立两种不同的触发器，二者的功能一致，都是向T_LOG表中插入T表新插入的数据：
SQL> CREATE OR REPLACE TRIGGER TRI_COMPOUND FOR INSERT ON T DISABLE 2 COMPOUND TRIGGER 3 TYPE T_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 4 TYPE T_VARCHAR2 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; 5 V_ID T_NUMBER; 6 V_NAME T_VARCHAR2; 7 AFTER EACH ROW IS 8 BEGIN 9 V_ID(V_ID.COUNT + 1) := :NEW.ID; 10 V_NAME(V_NAME.COUNT + 1) := :NEW.NAME; 11 END AFTER EACH ROW; 12 13 AFTER STATEMENT IS 14 BEGIN 15 FORALL I IN 1..V_ID.COUNT 16 INSERT INTO T_LOG VALUES (V_ID(I), V_NAME(I)); 17 END AFTER STATEMENT; 18 END; 19 / 触发器已创建 SQL> CREATE OR REPLACE TRIGGER TRI_A_EACHROW AFTER INSERT ON T 2 FOR EACH ROW DISABLE 3 BEGIN 4 INSERT INTO T_LOG VALUES (:NEW.ID, :NEW.NAME); 5 END; 6 / 触发器已创建
两个触发器都处于DISABLE状态，向T表插入数据，然后依次ENABLE其中的一个触发器，重复插入操作，对比三次的性能：
SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS; 已创建68345行。 SQL> TRUNCATE TABLE T; 表被截断。 SQL> SET TIMING ON SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS; 已创建68345行。 已用时间: 00: 00: 00.75 SQL> TRUNCATE TABLE T; 表被截断。 SQL> ALTER TRIGGER TRI_COMPOUND ENABLE; 触发器已更改 SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS; 已创建68345行。 已用时间: 00: 00: 05.59 SQL> TRUNCATE TABLE T; 表被截断。 SQL> TRUNCATE TABLE T_LOG; 表被截断。 SQL> ALTER TRIGGER TRI_COMPOUND DISABLE; 触发器已更改 SQL> ALTER TRIGGER TRI_A_EACHROW ENABLE; 触发器已更改 SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS; 已创建68345行。 已用时间: 00: 00: 17.31
第一次不记录时间，为了避免CACHE的影响，后面三次记录时间，分别对应不启用触发器、启用COMPOUND触发器和启动AFTER EACH ROW触发器三种情况。对比三次的执行时间，可以看到使用了COMPOUND的FORALL批量处理功能，获得的性能提高还是非常明显的。
Oracle11g中对于动态sql也做了很多功能性的增强。其中最明显的一个增强就是EXECUTE IMMEDIATE和DBMS_SQL包的PARSE都支持CLOB字段作为输入。这就彻底解决了EXECUTE IMMEDIATE语法不支持32k以上sql语句的限制。同时DBMS_SQL包对于32k以上SQL的处理也得到了简化。
首先建立一张大表，建表语句的长度超过32k。建表语句没有必要列出来，这里就省略了：
SQL> SELECT DBMS_LOB.GETLENGTH(DBMS_METADATA.GET_DDL('TABLE', 'T_LONG_SQL')) FROM DUAL; DBMS_LOB.GETLENGTH(DBMS_METADATA.GET_DDL('TABLE','T_LONG_SQL')) --------------------------------------------------------------- 35974
如果希望通过动态SQL的方法重建，原来只能使用DBMS_SQL的VARCHAR2S接口，但是这种方面比较麻烦，现在可以直接通过CLOB的方式来实现。
SQL> DECLARE 2 V_CLOB CLOB; 3 V_CURSOR NUMBER DEFAULT DBMS_SQL.OPEN_CURSOR; 4 V_VARCHAR2S DBMS_SQL.VARCHAR2S; 5 I NUMBER DEFAULT 1; 6 BEGIN 7 SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_LONG_SQL') INTO V_CLOB FROM DUAL; 8 LOOP 9 V_VARCHAR2S(I) := DBMS_LOB.SUBSTR(V_CLOB, 200, (I - 1) * 200 + 1); 10 EXIT WHEN LENGTH(V_VARCHAR2S(I)) < 200; 11 I := I + 1; 12 END LOOP; 13 EXECUTE IMMEDIATE 'DROP TABLE T_LONG_SQL PURGE'; 14 DBMS_SQL.PARSE(V_CURSOR, V_VARCHAR2S, 1, V_VARCHAR2S.COUNT, FALSE, DBMS_SQL.NATIVE); 15 END; 16 / PL/SQL 过程已成功完成。
这是11g以前处理32k以上SQL的方法，下面看看11g中提供的CLOB接口的方法：
SQL> DECLARE 2 V_CLOB CLOB; 3 V_CURSOR NUMBER DEFAULT DBMS_SQL.OPEN_CURSOR; 4 BEGIN 5 SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_LONG_SQL') INTO V_CLOB FROM DUAL; 6 EXECUTE IMMEDIATE 'DROP TABLE T_LONG_SQL PURGE'; 7 DBMS_SQL.PARSE(V_CURSOR, V_CLOB, DBMS_SQL.NATIVE); 8 END; 9 / PL/SQL 过程已成功完成。
这个方法就简单很多了，而使用EXECUTE IMMEDIATE则更加简单：
SQL> DECLARE 2 V_CLOB CLOB; 3 BEGIN 4 SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_LONG_SQL') INTO V_CLOB FROM DUAL; 5 EXECUTE IMMEDIATE 'DROP TABLE T_LONG_SQL PURGE'; 6 EXECUTE IMMEDIATE V_CLOB; 7 END; 8 / PL/SQL 过程已成功完成。
除了增加对CLOB类型的支持外，DBMS_SQL包还增加了将REF CURSOR和DBMS_SQL包的CURSOR进行相互转化的功能。
在打开CURSOR且提前数据前，可以利用DBMS_SQL包将CURSOR类型进行转化。
最后看看DBMS_SQL的CUROSR和REF CURSOR转化的一个例子：
SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30)); 表已创建。 SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB; 已创建3行。 SQL> SET SERVEROUT ON SQL> DECLARE 2 TYPE T_CURSOR IS REF CURSOR; 3 TYPE T_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 4 TYPE T_VARCHAR2 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; 5 V_REF_CURSOR T_CURSOR; 6 V_ID T_NUMBER; 7 V_NAME T_VARCHAR2; 8 V_DBMS_SQL_CURSOR NUMBER := DBMS_SQL.OPEN_CURSOR; 9 V_SQL VARCHAR2(32767) := 'SELECT * FROM T'; 10 V_RES NUMBER; 11 BEGIN 12 DBMS_SQL.PARSE(V_DBMS_SQL_CURSOR, V_SQL, DBMS_SQL.NATIVE); 13 V_RES := DBMS_SQL.EXECUTE(V_DBMS_SQL_CURSOR); 14 V_REF_CURSOR := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_CURSOR); 15 FETCH V_REF_CURSOR BULK COLLECT INTO V_ID, V_NAME; 16 DBMS_OUTPUT.PUT_LINE(LPAD('ID', 10, ' ') || ' ' || 'NAME'); 17 FOR I IN 1..V_ID.COUNT LOOP 18 DBMS_OUTPUT.PUT_LINE(LPAD(V_ID(I), 10, ' ') || ' ' || V_NAME(I)); 19 END LOOP; 20 CLOSE V_REF_CURSOR; 21 END; 22 / ID NAME 1 T 2 T_SESSION 3 T_SESSION_STAT PL/SQL 过程已成功完成。 SQL> DECLARE 2 TYPE T_CURSOR IS REF CURSOR; 3 V_REF_CURSOR T_CURSOR; 4 V_DBMS_SQL_CURSOR NUMBER; 5 V_COLUMN_COUNT NUMBER; 6 V_DEST_TABLE DBMS_SQL.DESC_TAB; 7 V_ID DBMS_SQL.NUMBER_TABLE; 8 V_NAME DBMS_SQL.VARCHAR2_TABLE; 9 V_RES NUMBER; 10 BEGIN 11 OPEN V_REF_CURSOR FOR 'SELECT * FROM T'; 12 V_DBMS_SQL_CURSOR := DBMS_SQL.TO_CURSOR_NUMBER(V_REF_CURSOR); 13 DBMS_SQL.DEFINE_ARRAY(V_DBMS_SQL_CURSOR, 1, V_ID, 10000, 1); 14 DBMS_SQL.DEFINE_ARRAY(V_DBMS_SQL_CURSOR, 2, V_NAME, 10000, 1); 15 V_RES := DBMS_SQL.FETCH_ROWS(V_DBMS_SQL_CURSOR); 16 DBMS_SQL.COLUMN_VALUE(V_DBMS_SQL_CURSOR, 1, V_ID); 17 DBMS_SQL.COLUMN_VALUE(V_DBMS_SQL_CURSOR, 2, V_NAME); 18 DBMS_OUTPUT.PUT_LINE(LPAD('ID', 10, ' ') || ' ' || 'NAME'); 19 FOR I IN 1..V_RES LOOP 20 DBMS_OUTPUT.PUT_LINE(LPAD(V_ID(I), 10, ' ') || ' ' || V_NAME(I)); 21 END LOOP; 22 DBMS_SQL.CLOSE_CURSOR(V_DBMS_SQL_CURSOR); 23 END; 24 / ID NAME 1 T 2 T_SESSION 3 T_SESSION_STAT PL/SQL 过程已成功完成。
