达梦数据库-DBA大师实战国产数据库系列—

DBA大师实战国产数据库系列——达梦6.0之PLSQL篇

2010-05-05

　　作者简介：杨廷琨（网名Yangtingkun），现任海虹医药电子商务有限公司首席DBA， ITPUB论坛Oracle数据库管理版版主。2004年曾参与编写《Oracle数据库性能优化》一书，2007年被Oracle公司授予Oracle ACE称号，喜欢研究Oracle相关的技术问题，他的技术博客上积累了1500多篇Oracle相关的原创技术文章。

　　前几天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，不支持索引表、嵌套表和数组。由于不支持嵌套表和索引表，显然也是不支持批量操作的，无论是批量插入和批量读取都是达梦目前所不支持的。

技术文章问题解析培训服务技术支持