前几天ITPUB的熊建国主编和我联系,希望我能参加国产数据库达梦的适用活动,并写几篇使用感受。本来最近手工的事情比较多,本打算推辞的,不过熊主编再三邀请,而且强调并非是枪手文,只要写出真实使用感受即可。既然如此,我就本着支持国产数据库的原则,写几篇试用感受。
由于本人唯一熟悉的数据库就是Oracle,因此所有的对比都是与Oracle数据库进行对比,在这个过程中,将尽可能避免将对Oracle数据库的喜爱之情带进来,争取站在一个比较公正的位置上来进行评价。
这一篇简单介绍一下达梦数据库PL/SQL相关的内容。
达梦数据库对于PL/SQL的支持也是出人意料的,基本上所有的关键性语法都与Oracle的PL/SQL没有本质的区别。
SQL>BEGIN
2 INSERT INTO T VALUES (3, 'PL/SQL', SYSDATE);
3 END;
4 /
BEGIN
INSERT INTO T VALUES (3, 'PL/SQL', SYSDATE);
END;
1 rows affected
time used: 68.921(ms) clock tick:115154870.
SQL>BEGIN
2 EXECUTE IMMEDIATE 'TRUNCATE TABLE T';
3 END;
4 /
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE T';
END;
0 rows affected
time used: 31.029(ms) clock tick:51357350.
SQL>SELECT * FROM T;
SELECT * FROM T;
id name create_date
0 rows got
time used: 0.278(ms) clock tick:449080.
可以看到,不仅支持PL/SQL中包含的DML语句,而且连DDL语句都是支持的。
SQL>DECLARE
2 V_FLAG BOOLEAN;
3 BEGIN
4 FOR I IN 2 .. 100 LOOP
5 V_FLAG := TRUE;
6 FOR J IN 2 .. TRUNC(POWER(I, 0.5)) LOOP
7 IF MOD(I,J) = 0 THEN
8 V_FLAG := FALSE;
9 EXIT;
10 END IF;
11 END LOOP;
12
13 IF V_FLAG = TRUE THEN
14 --DBMS_OUTPUT.PUT_LINE(I);
15 NULL;
16 END IF;
17 END LOOP;
18 END;
19 /
DECLARE
V_FLAG BOOLEAN;
BEGIN
FOR I IN 2 .. 100 LOOP
V_FLAG := TRUE;
FOR J IN 2 .. TRUNC(POWER(I, 0.5)) LOOP
IF MOD(I,J) = 0 THEN
V_FLAG := FALSE;
EXIT;
END IF;
END LOOP;
IF V_FLAG = TRUE THEN
--DBMS_OUTPUT.PUT_LINE(I);
NULL;
END IF;
END LOOP;
END;
0 rows affected
time used: 108.993(ms) clock tick:181319230.
这时Oracle中计算100以内质数的一个PL/SQL过程,可以看到,除了调用DBMS_OUTPUT包之外,其他部分不用进行任何的修改就可以顺利执行,在达梦的PL/SQL语句中,提供了PRINT语句来代替DBMS_OUTPUT包:
SQL>DECLARE
2 V_FLAG BOOLEAN;
3 BEGIN
4 FOR I IN 2 .. 100 LOOP
5 V_FLAG := TRUE;
6 FOR J IN 2 .. TRUNC(POWER(I, 0.5)) LOOP
7 IF MOD(I,J) = 0 THEN
8 V_FLAG := FALSE;
9 EXIT;
10 END IF;
11 END LOOP;
12
13 IF V_FLAG = TRUE THEN
14 PRINT(I);
15 END IF;
16 END LOOP;
17 END;
18 /
DECLARE
V_FLAG BOOLEAN;
BEGIN
FOR I IN 2 .. 100 LOOP
V_FLAG := TRUE;
FOR J IN 2 .. TRUNC(POWER(I, 0.5)) LOOP
IF MOD(I,J) = 0 THEN
V_FLAG := FALSE;
EXIT;
END IF;
END LOOP;
IF V_FLAG = TRUE THEN
PRINT(I);
END IF;
END LOOP;
END;
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97
0 rows affected
time used: 29.325(ms) clock tick:48929860.
除了匿名块外,达梦还支持PROCEDURE、FUNCTION和TRIGGER:
SQL>CREATE OR REPLACE PROCEDURE P_TEST AS
2 BEGIN
3 INSERT INTO T VALUES (1, 'TEST', SYSDATE);
4 COMMIT;
5 END;
6 /
CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
INSERT INTO T VALUES (1, 'TEST', SYSDATE);
COMMIT;
END;
time used: 41.239(ms) clock tick:68574010.
SQL>SELECT * FROM T;
SELECT * FROM T;
id name create_date
0 rows got
time used: 0.319(ms) clock tick:520320.
SQL>BEGIN
2 P_TEST;
3 END;
4 /
BEGIN
P_TEST;
END;
1 rows affected
time used: 11.769(ms) clock tick:19487570.
SQL>SELECT * FROM T;
SELECT * FROM T;
id name create_date
1 1 TEST 2010-04-07
1 rows got
time used: 0.295(ms) clock tick:477140.
看一个函数的例子:
SQL>CREATE OR REPLACE FUNCTION F_TAX
2 (P_SALARY IN NUMBER, P_START IN NUMBER DEFAULT 2000)
3 RETURN NUMBER AS
4 V_SALARY NUMBER := P_SALARY - P_START;
5 BEGIN
6 IF V_SALARY <= 0 THEN
7 RETURN 0;
8 ELSIF V_SALARY <= 500 THEN
9 RETURN V_SALARY * 0.05;
10 ELSIF V_SALARY <= 2000 THEN
11 RETURN V_SALARY * 0.1 - 25;
12 ELSIF V_SALARY <= 5000 THEN
13 RETURN V_SALARY * 0.15 - 125;
14 ELSIF V_SALARY <= 20000 THEN
15 RETURN V_SALARY * 0.2 - 375;
16 ELSIF V_SALARY <= 40000 THEN
17 RETURN V_SALARY * 0.25 - 1375;
18 ELSIF V_SALARY <= 60000 THEN
19 RETURN V_SALARY * 0.3 - 3375;
20 ELSIF V_SALARY <= 80000 THEN
21 RETURN V_SALARY * 0.35 - 6375;
22 ELSIF V_SALARY <= 100000 THEN
23 RETURN V_SALARY * 0.4 - 10375;
24 ELSE
25 RETURN V_SALARY * 0.45 - 15375;
26 END IF;
27 END;
28 /
CREATE OR REPLACE FUNCTION F_TAX
(P_SALARY IN NUMBER, P_START IN NUMBER DEFAULT 2000)
RETURN NUMBER AS
V_SALARY NUMBER := P_SALARY - P_START;
BEGIN
IF V_SALARY <= 0 THEN
RETURN 0;
ELSIF V_SALARY <= 500 THEN
RETURN V_SALARY * 0.05;
ELSIF V_SALARY <= 2000 THEN
RETURN V_SALARY * 0.1 - 25;
ELSIF V_SALARY <= 5000 THEN
RETURN V_SALARY * 0.15 - 125;
ELSIF V_SALARY <= 20000 THEN
RETURN V_SALARY * 0.2 - 375;
ELSIF V_SALARY <= 40000 THEN
RETURN V_SALARY * 0.25 - 1375;
ELSIF V_SALARY <= 60000 THEN
RETURN V_SALARY * 0.3 - 3375;
ELSIF V_SALARY <= 80000 THEN
RETURN V_SALARY * 0.35 - 6375;
ELSIF V_SALARY <= 100000 THEN
RETURN V_SALARY * 0.4 - 10375;
ELSE
RETURN V_SALARY * 0.45 - 15375;
END IF;
END;
time used: 4.685(ms) clock tick:5683670.
SQL>SELECT F_TAX(10000) FROM T;
SELECT F_TAX(10000) FROM T;
1 1225
1 rows got
time used: 30.050(ms) clock tick:50174960.
这时以前写的一个计算个人所得税的函数,同样没有做任何的修改,放在达梦数据库上就可以直接运行。
下面是一个PACKAGE的例子:
SQL>CREATE OR REPLACE PACKAGE PA_TEST AS
2 PROCEDURE P_TEST(P_IN NUMBER);
3 PROCEDURE P_TEST(P_IN VARCHAR);
4 END;
5 /
CREATE OR REPLACE PACKAGE PA_TEST AS
PROCEDURE P_TEST(P_IN NUMBER);
PROCEDURE P_TEST(P_IN VARCHAR);
END;
time used: 80.545(ms) clock tick:134160000.
SQL>CREATE OR REPLACE PACKAGE BODY PA_TEST AS
2 PROCEDURE P_TEST(P_IN NUMBER) AS
3 BEGIN
4 PRINT('NUMBER');
5 END;
6
7 PROCEDURE P_TEST(P_IN VARCHAR) AS
8 BEGIN
9 PRINT('VARCHAR');
10 END;
11 END;
12 /
CREATE OR REPLACE PACKAGE BODY PA_TEST AS
PROCEDURE P_TEST(P_IN NUMBER) AS
BEGIN
PRINT('NUMBER');
END;
PROCEDURE P_TEST(P_IN VARCHAR) AS
BEGIN
PRINT('VARCHAR');
END;
END;
time used: 20.445(ms) clock tick:33862040.
SQL>BEGIN
2 PA_TEST.P_TEST(1);
3 END;
4 /
BEGIN
PA_TEST.P_TEST(1);
END;
NUMBER
0 rows affected
time used: 0.552(ms) clock tick:913600.
SQL>BEGIN
2 PA_TEST.P_TEST('1');
3 END;
4 /
BEGIN
PA_TEST.P_TEST('1');
END;
VARCHAR
0 rows affected
time used: 0.441(ms) clock tick:725060.
这个例子显示了包中过程的重载特性。
看一个触发器的例子:
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.
达梦支持这种最普通的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.
其中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.
不过达梦数据库的PL/SQL也有不足之处,比如不支持TYPE,不支持索引表、嵌套表和数组。
由于不支持嵌套表和索引表,显然也是不支持批量操作的,无论是批量插入和批量读取都是达梦目前所不支持的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-668414/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-668414/