达梦6.0试用之PLSQL篇

前几天ITPUB的熊建国主编和我联系,希望我能参加国产数据库达梦的适用活动,并写几篇使用感受。本来最近手工的事情比较多,本打算推辞的,不过熊主编再三邀请,而且强调并非是枪手文,只要写出真实使用感受即可。既然如此,我就本着支持国产数据库的原则,写几篇试用感受。

由于本人唯一熟悉的数据库就是Oracle,因此所有的对比都是与Oracle数据库进行对比,在这个过程中,将尽可能避免将对Oracle数据库的喜爱之情带进来,争取站在一个比较公正的位置上来进行评价。

这一篇简单介绍一下达梦数据库PL/SQL相关的内容。

 

 

达梦数据库对于PL/SQL的支持也是出人意料的,基本上所有的关键性语法都与OraclePL/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.

除了匿名块外,达梦还支持PROCEDUREFUNCTIONTRIGGER

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/

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值