专家点评:达梦6.0试用之PLSQL篇-技术开发专区

专家点评:达梦6.0试用之PLSQL篇

作者：IT168 杨廷琨编辑：覃里 2010-04-22 00:00 来源：IT168�

　　看一个函数的例子：

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.

　　这个例子显示了包中过程的重载特性。

第1页：达梦数据库对于PL/SQL的支持第2页：达梦的PL/SQL语句第3页：例子展示第4页：触发器的例子及达梦的优缺点

关注我们