看一个触发器的例子:
SQL>CREATE OR REPLACE TRIGGER T_TRI
2 BEFORE DELETE ON T
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO T_BAK VALUES (:OLD.ID);
6 END;
7 /
CREATE OR REPLACE TRIGGER T_TRI
BEFORE DELETE ON T
FOR EACH ROW
BEGIN
INSERT INTO T_BAK VALUES (:OLD.ID);
END;
time used: 13.493(ms) clock tick:22369710.
SQL>INSERT INTO T VALUES (2, 'ABC', SYSDATE);
INSERT INTO T VALUES (2, 'ABC', SYSDATE)
1 rows affected
time used: 0.376(ms) clock tick:614380.
SQL>INSERT INTO T VALUES (3, 'TTT', NULL);
INSERT INTO T VALUES (3, 'TTT', NULL)
1 rows affected
time used: 0.497(ms) clock tick:818040.
SQL>DELETE T;
DELETE T;
3 rows affected
time used: 0.730(ms) clock tick:1203390.
SQL>SELECT * FROM T_BAK;
SELECT * FROM T_BAK;
ID
1 1
2 2
3 3
3 rows got
time used: 0.370(ms) clock tick:602660.
2 BEFORE DELETE ON T
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO T_BAK VALUES (:OLD.ID);
6 END;
7 /
CREATE OR REPLACE TRIGGER T_TRI
BEFORE DELETE ON T
FOR EACH ROW
BEGIN
INSERT INTO T_BAK VALUES (:OLD.ID);
END;
time used: 13.493(ms) clock tick:22369710.
SQL>INSERT INTO T VALUES (2, 'ABC', SYSDATE);
INSERT INTO T VALUES (2, 'ABC', SYSDATE)
1 rows affected
time used: 0.376(ms) clock tick:614380.
SQL>INSERT INTO T VALUES (3, 'TTT', NULL);
INSERT INTO T VALUES (3, 'TTT', NULL)
1 rows affected
time used: 0.497(ms) clock tick:818040.
SQL>DELETE T;
DELETE T;
3 rows affected
time used: 0.730(ms) clock tick:1203390.
SQL>SELECT * FROM T_BAK;
SELECT * FROM T_BAK;
ID
1 1
2 2
3 3
3 rows got
time used: 0.370(ms) clock tick:602660.
达梦支持这种最普通的DML的触发器,还支持INSTEAD OF触发器、但是并不支持基于数据库事件的触发器和基于数据库错误的触发器。
达梦数据库的FETCH语句和PL/SQL的相比更加灵活一些,提供了随机读取的功能:
SQL>INSERT INTO T VALUES (1, 'A', NULL);
INSERT INTO T VALUES (1, 'A', NULL)
1 rows affected
time used: 0.461(ms) clock tick:757930.
SQL>INSERT INTO T VALUES (2, 'B', SYSDATE);
INSERT INTO T VALUES (2, 'B', SYSDATE)
1 rows affected
time used: 0.430(ms) clock tick:701910.
SQL>INSERT INTO T VALUES (3, 'ABC', '');
INSERT INTO T VALUES (3, 'ABC', '')
1 rows affected
time used: 0.333(ms) clock tick:540330.
SQL>DECLARE
2 V_NUM NUMBER;
3 C_CUR CURSOR;
4 BEGIN
5 OPEN C_CUR FOR 'SELECT ID FROM T';
6 FETCH LAST C_CUR INTO V_NUM;
7 PRINT(V_NUM);
8 FETCH PRIOR C_CUR INTO V_NUM;
9 PRINT(V_NUM);
10 FETCH ABSOLUTE 0 C_CUR INTO V_NUM;
11 PRINT(V_NUM);
12 CLOSE C_CUR;
13 END;
14 /
DECLARE
V_NUM NUMBER;
C_CUR CURSOR;
BEGIN
OPEN C_CUR FOR 'SELECT ID FROM T';
FETCH LAST C_CUR INTO V_NUM;
PRINT(V_NUM);
FETCH PRIOR C_CUR INTO V_NUM;
PRINT(V_NUM);
FETCH ABSOLUTE 0 C_CUR INTO V_NUM;
PRINT(V_NUM);
CLOSE C_CUR;
END;
3
2
1
0 rows affected
time used: 0.726(ms) clock tick:1199160.
INSERT INTO T VALUES (1, 'A', NULL)
1 rows affected
time used: 0.461(ms) clock tick:757930.
SQL>INSERT INTO T VALUES (2, 'B', SYSDATE);
INSERT INTO T VALUES (2, 'B', SYSDATE)
1 rows affected
time used: 0.430(ms) clock tick:701910.
SQL>INSERT INTO T VALUES (3, 'ABC', '');
INSERT INTO T VALUES (3, 'ABC', '')
1 rows affected
time used: 0.333(ms) clock tick:540330.
SQL>DECLARE
2 V_NUM NUMBER;
3 C_CUR CURSOR;
4 BEGIN
5 OPEN C_CUR FOR 'SELECT ID FROM T';
6 FETCH LAST C_CUR INTO V_NUM;
7 PRINT(V_NUM);
8 FETCH PRIOR C_CUR INTO V_NUM;
9 PRINT(V_NUM);
10 FETCH ABSOLUTE 0 C_CUR INTO V_NUM;
11 PRINT(V_NUM);
12 CLOSE C_CUR;
13 END;
14 /
DECLARE
V_NUM NUMBER;
C_CUR CURSOR;
BEGIN
OPEN C_CUR FOR 'SELECT ID FROM T';
FETCH LAST C_CUR INTO V_NUM;
PRINT(V_NUM);
FETCH PRIOR C_CUR INTO V_NUM;
PRINT(V_NUM);
FETCH ABSOLUTE 0 C_CUR INTO V_NUM;
PRINT(V_NUM);
CLOSE C_CUR;
END;
3
2
1
0 rows affected
time used: 0.726(ms) clock tick:1199160.
其中FETCH语句指定ABSOLUTE的数值时,是从0开始的,这显然是C语言的习惯。
达梦数据库还有一个优点,无论是匿名块还是过程,在SELECT的时候可以不指定FETCH的变量,这时会将查询结果直接输出到屏幕上:
SQL>BEGIN
2 SELECT * FROM T;
3 END;
4 /
BEGIN
SELECT * FROM T;
END;
id name create_date
1 1 A NULL
2 2 B 2010-04-07
3 3 ABC NULL
3 rows got
time used: 0.496(ms) clock tick:816250.
2 SELECT * FROM T;
3 END;
4 /
BEGIN
SELECT * FROM T;
END;
id name create_date
1 1 A NULL
2 2 B 2010-04-07
3 3 ABC NULL
3 rows got
time used: 0.496(ms) clock tick:816250.
不过达梦数据库的PL/SQL也有不足之处,比如不支持TYPE,不支持索引表、嵌套表和数组。
由于不支持嵌套表和索引表,显然也是不支持批量操作的,无论是批量插入和批量读取都是达梦目前所不支持的。