达梦7的执行效果, 可能和Oracle不一样:
SQL>CREATE OR REPLACE PACKAGE plch_counter
2 AS
3 g_update PLS_INTEGER := 0;
4 g_insert PLS_INTEGER := 0;
5 END;
6 /
操作已执行
已用时间: 00:00:02.856. 执行号:39.
SQL>CREATE TABLE plch_test
2 (
3 id NUMBER,
4 value NUMBER
5 )
6 ;
操作已执行
已用时间: 804.241(毫秒). 执行号:40.
SQL>CREATE OR REPLACE TRIGGER plch_test_asi
2 AFTER INSERT
3 ON plch_test
4 BEGIN
5 plch_counter.g_insert := plch_counter.g_insert + 1;
6 END;
7 /
操作已执行
已用时间: 544.208(毫秒). 执行号:41.
SQL>CREATE OR REPLACE TRIGGER plch_test_asu
2 AFTER UPDATE
3 ON plch_test
4 BEGIN
5 plch_counter.g_update := plch_counter.g_update + 1;
6 END;
7 /
操作已执行
已用时间: 119.263(毫秒). 执行号:42.
SQL>dbms_output.enable;
PL/SQL 过程已成功完成
已用时间: 128.972(毫秒). 执行号:43.
SQL>set serverout on
SQL>BEGIN
2 FOR i_merge IN 1 .. 2
3 LOOP
4 MERGE INTO plch_test tst
5 USING ( SELECT 10 id, 100 value FROM DUAL
6 UNION ALL
7 SELECT 20 id, 1000 value FROM DUAL
8 UNION ALL
9 SELECT 30 id, 10000 value FROM DUAL
10 ) src
11 ON (tst.id = src.id)
12 WHEN MATCHED THEN
13 UPDATE SET tst.value = tst.value + src.value
14 WHEN NOT MATCHED THEN
15 INSERT (tst.id, tst.value)
16 VALUES (src.id, src.value);
17 END LOOP;
18
19 DBMS_OUTPUT.put_line
20 ( 'Ins = ' || plch_counter.g_insert ||
21 ' Upd = ' || plch_counter.g_update
22 );
23 END;
24 /
Ins = 2 Upd = 2
PL/SQL 过程已成功完成