DM 达梦 SQL 编程（存储过程和函数）操作示例 -- cnDBA.cn

在数据库库除了直接对表进行DML 操作之外，还有就是一些业务逻辑的封装处理，比如存储过程和函数，当然也包括我们之前说的触发器，如下：

DM 达梦触发器操作示例
https://www.cndba.cn/dave/article/116500

从设计角度考虑，业务逻辑尽量放在代码层实现，但作为数据库的基本能力，还是有必要学习一下达梦的SQL 编程。

1 DMSQL 数据类型

这里只列清单，具体类型说明参考官方手册。http://www.cndba.cn/dave/article/116504

1.1 常规数据类型

数值类型：

NUMERIC、NUMBER、DECIMAL/DEC、BIT、INTEGER/INT、PLS_INTEGER、BIGINT、TINYINT、BYTE、SMALLINT、BINARY、VARBINARY、REAL、FLOAT、DOUBLE、DOUBLE PRECISION。

字符类型：

CHAR/CHARACTER、VARCHAR

多媒体类型：

TEXT/LONGVARCHAR、IMAGE/LONGVARBINARY、BLOB、CLOB、BFILE

日期类型：

DATE、TIME、TIMESTAMP/DATETIME、TIME WITH TIME ZONE、TIMESTAMP WITH TIME ZONE 、TIMESTAMP WITH LOCAL TIME ZONE

布尔类型：

BOOL/BOOLEAN

1.2 %TYPE 和%ROWTYPE 类型

%TYPE 可以将变量同表列的类型进行绑定。

DECLARE
        v_type dmhr.employee.employee_name%TYPE;
BEGIN
        SELECT employee_name INTO v_type FROM dmhr.employee WHERE EMPLOYEE_ID =1001;
        PRINT v_type;
END;
/

%ROWTYPE 将一个记录声明为具有相同类型的数据库行。

DECLARE
        v_row dmhr.employee%ROWTYPE;
        cur CURSOR;
        BEGIN
                OPEN cur FOR SELECT * FROM dmhr.employee WHERE EMPLOYEE_ID =1001;
                FETCH cur INTO v_row;
        PRINT v_row.employee_name;
        PRINT v_row.email;
        CLOSE cur;
END;

1.3 记录类型

记录类型是由单行多列的标量类型构成复合类型，类似于 C 语言中的结构。

DECLARE
TYPE employee IS RECORD
        (ID dmhr.employee.employee_id%TYPE,
         salary dmhr.employee.salary%TYPE);
v_rec employee;
BEGIN
        v_rec.ID           := 1;
        v_rec.salary:= 5500;
        UPDATE dmhr.employee
        SET salary=v_rec.salary WHERE employee_id =v_rec.ID;
END;
/

1.4 数组类型

静态数组类型

DECLARE
TYPE Arr
IS
        ARRAY VARCHAR[3]; --TYPE 定义一维数组类型
        a Arr;            --声明一维数组
TYPE Arr1
IS
        ARRAY VARCHAR[2, 4]; --TYPE 定义二维数组类型
        b Arr1;              --声明二维数组
BEGIN
        FOR I IN 1..3
        LOOP
                a[I] := I * 10;
                PRINT a[I];
        END LOOP;
        PRINT '--------';
        FOR I IN 1..2
        LOOP
                FOR J IN 1..4
                LOOP
                        b[I][J] = 4*(I-1)+J;
                        PRINT b[I][J];
                END LOOP;
        END LOOP;
END;
/

动态数组类型

DECLARE
TYPE Arr
IS
        ARRAY VARCHAR[];
        a Arr;
BEGIN
        a     := NEW VARCHAR[4]; --动态分配空间
        FOR I IN 1..4
        LOOP
                a[I] := I * 4;
                PRINT a[I];
        END LOOP;
END;
/

多维动态数组：
DECLARE
TYPE Arr1
IS
        ARRAY VARCHAR[, ];
        b ARR1;
BEGIN
        b     := NEW VARCHAR[2, 4];
        FOR I IN 1..2
        LOOP
                FOR J IN 1..4
                LOOP
                        b[I][J]= I*10+J;
                        PRINT b[I][J];
                END LOOP;
        END LOOP;
END;
/

复杂类型数组

CREATE OR REPLACE TYPE COMPLEX
AS
        OBJECT
        (
                RPART INT,
                IPART VARCHAR(100) );
/

DECLARE
TYPE ARR_COMPLEX
IS
        ARRAY SYSDBA.COMPLEX[3];
        arr ARR_COMPLEX;
BEGIN
        FOR I IN 1..3
        LOOP
                SELECT
                        SYSDBA.COMPLEX(PRODUCTID, NAME)
                INTO
                        arr[I]
                FROM
                        PRODUCTION.PRODUCT
                WHERE
                        PRODUCTID=I;
                PRINT arr[I].RPART || arr[I].IPART;
        END LOOP;
END;
/

1.5 集合类型

DMSQL 支持三种集合类型：VARRAY 类型、索引表类型和嵌套表类型。

1.5.1 VARRAY 类型

VARRAY 是一种具有可伸缩性的数组，数组中的每个元素具有相同的数据类型。 VARRAY 在定义时由用户指定一个最大容量，其元素索引是从 1 开始的有序数字。

下面给出一个 VARRAY 的简单使用示例，查询人员姓名并将其存入一个 VARRAY 变量中。VARRAY 最初的实际大小为 0，使用 EXCTEND()方法可扩展 VARRAY 元素个数。

DECLARE
TYPE MY_ARRAY_TYPE IS VARRAY(10) OF VARCHAR(100);
v MY_ARRAY_TYPE;
BEGIN
        v:=MY_ARRAY_TYPE();
        PRINT 'v.COUNT()=' || v.COUNT();
        FOR I IN 1..8
        LOOP
                v.EXTEND();
                SELECT NAME INTO v(I) FROM PERSON.PERSON WHERE PERSON.PERSONID=I;
        END LOOP;
        PRINT 'v.COUNT()=' || v.COUNT();
        FOR I IN 1..v.COUNT()
        LOOP
                PRINT 'v(' || i || ')=' ||v(i);
        END LOOP;
END;
/

1.5.2 索引表

索引表是一组数据的集合，它将数据按照一定规则组织起来，形成一个可操作的整体，是对大量数据进行有效组织和管理的手段之一，通过函数可以对大量性质相同的数据进行存储、排序、插入及删除等操作，从而可以有效地提高程序开发效率及改善程序的编写方式。

DECLARE
TYPE Arr
IS
        TABLE OF VARCHAR(100) INDEX BY INT;
        x Arr;
BEGIN
        x(1) := 'TEST1';
        x(2) := 'TEST2';
        x(3) := x(1) || x(2);
        PRINT x(3);
END;
/

索引表还可以用来管理记录，下面的例子索引表中存放的数据类型为 RECORD，展示了如何存入和遍历输出索引表的记录类型数据。

DECLARE
TYPE Rd IS RECORD
        (ID   INT,
         NAME VARCHAR(128));
TYPE Arr IS TABLE OF Rd INDEX BY INT;
x Arr;
i INT;
CURSOR C1;
BEGIN
        i := 1;
        OPEN C1 FOR SELECT employee_id, employee_name FROM dmhr.employee;
        --遍历结果集，把每行的值都存放入索引表中
        LOOP
                IF C1%NOTFOUND THEN
                        EXIT;
                END IF;
                FETCH C1 INTO x(i).ID, x(i).NAME;
        i := i + 1;
END LOOP;
--遍历输出索引表中的记录
i = x."FIRST"();
LOOP
        IF i IS NULL THEN
                EXIT;
        END IF;
        PRINT 'ID:' || CAST(x(i).ID AS VARCHAR(10)) || ', NAME:' || x(i).NAME;
        i = x."NEXT"(i);
END LOOP;
END;
/

下面的例子定义了一个二维索引表 x，展示了如何存入和遍历输出二维索引表的数据。

DECLARE
TYPE Arr
IS
        TABLE OF VARCHAR(100) INDEX BY BINARY_INTEGER;
TYPE Arr2
IS
        TABLE OF Arr INDEX BY VARCHAR(100);
        x Arr2;
        ind_i INT;
        ind_j VARCHAR(10);
BEGIN
        --存入数据
        FOR I IN 1 .. 6
        LOOP
                FOR J IN 1 .. 3
                LOOP
                        x(I)(J) := CAST(I AS VARCHAR(100)) || '+'||CAST(J AS VARCHAR(10));
                END LOOP;
        END LOOP;
        --遍历多维数组
        ind_i := x."FIRST"();
        LOOP
                IF ind_i IS NULL THEN
                        EXIT;
                END IF;
                ind_j := x(ind_i)."FIRST"();
                LOOP
                        IF ind_j IS NULL THEN
                                EXIT;
                        END IF;
                        PRINT x(ind_i)(ind_j);
                        ind_j := x(ind_i)."NEXT"(ind_j);
                END LOOP;
                ind_i := x."NEXT"(ind_i);
        END LOOP;
END;
/

1.5.3 嵌套表

嵌套表类似于一维数组，但与数组不同的是，嵌套表不需要指定元素的个数，其大小
可自动扩展。嵌套表元素的下标从 1 开始。

DECLARE
TYPE Info_t
IS
        TABLE OF SALES.SALESPERSON%ROWTYPE;
        info Info_t;
BEGIN
        SELECT
                SALESPERSONID,
                EMPLOYEEID   ,
                SALESTHISYEAR,
                SALESLASTYEAR BULK COLLECT
        INTO
                info
        FROM
                SALES.SALESPERSON
        WHERE
                SALESTHISYEAR>1000;
END;
/

1.5.4 集合类型支持的方法

DM 为 VARRAY、索引表和嵌套表提供了一些方法，用户可以使用这些方法访问和修改
集合与集合元素。

COUNT 、LIMIT、FIRST 、LAST、NEXT、PRIOR、EXISTS、DELETE、TRIM、EXTEND

下面的例子简单演示了如何使用集合类型的方法：

DECLARE
TYPE IntList
IS
        TABLE OF INT;
        v IntList := IntList(1, 3, 5, 7, 9); -- 开始赋值 5 个元素.
BEGIN
        DBMS_OUTPUT.PUT_LINE ('最初 v 中共有 ' || v.COUNT || '个元素。');
        v.EXTEND(3);-- 在末尾增加三个元素
        DBMS_OUTPUT.PUT_LINE ('现在 v 中共有 ' || v.COUNT || '个元素。');
        v.DELETE(2); --删掉第 2 个元素
        DBMS_OUTPUT.PUT_LINE ('现在 v 中共有 ' || v.COUNT || '个元素。');
        v.TRIM(2); -- 删除掉末尾的两个元素
        DBMS_OUTPUT.PUT_LINE ('现在 v 中共有 ' || v.COUNT || '个元素。');
END;
/

1.6 游标类型

游标类型用来定义游标变量。对游标变量进行赋值，赋值对象需要是一个游标对象。http://www.cndba.cn/dave/article/116504

DECLARE
CURSOR c1 IS SELECT TITLE FROM RESOURCES.EMPLOYEE WHERE MANAGERID = 3;
c2 CURSOR;
BEGIN
                c2 =c1;
                open c2;
                close c2;
END;

2 DMSQL 中的SQL 语句

DMSQL 支持对数据库对象的 SELECT、INSERT、UPDATE、DELETE 等 DML 操作，还可以定义和操纵游标等。

DMSQL 程序支持的 SQL 语句具体包括：
 数据查询语句(SELECT)
 数据操纵语句(INSERT、DELETE、UPDATE)
 游标定义及操纵语句(DECLARE、OPEN、FETCH、CLOSE)
 事务控制语句(COMMIT、ROLLBACK、SAVEPOINT)
 动态 SQL 执行语句(EXECUTE IMMEDIATE)
通过 SQL 语句及上一章介绍的各种控制结构，用户可以编写复杂的 DMSQL 程序，实现复杂逻辑的数据库访问应用。

2.1 普通静态 SQL 语句

2.1.1 数据操纵

在 DMSQL 程序中，可以直接使用 INSERT、DELETE 和 UPDATE 语句对数据库中的表进行增、删、改操作。

DECLARE 
catogory CONSTANT VARCHAR(50):='天文';
BEGIN
        INSERT INTO PRODUCTION.PRODUCT_CATEGORY VALUES(catogory);
END;
/
CREATE OR REPLACE
PROCEDURE proc_ins
        (
                category IN VARCHAR(50)
        )
AS
BEGIN
        INSERT INTO PRODUCTION.PRODUCT_CATEGORY VALUES(category);
END;
/
CALL PROC_INS('动漫');


DECLARE
        catogory1 VARCHAR(50);
        catogory2 VARCHAR(50);
BEGIN
        catogory1='动漫';
        catogory2='高等数学';
        DELETE PRODUCTION.PRODUCT_CATEGORY WHERE NAME=catogory1;
        UPDATE PRODUCTION.PRODUCT_CATEGORY SET NAME=catogory2 WHERE NAME='数学';
END;
/

2.1.2 数据查询

在 DMSQL 程序中可直接使用 SELECT 语句从数据库中查询数据。http://www.cndba.cn/dave/article/116504

DECLARE
        p_name    VARCHAR(50);
        p_publish VARCHAR(50);
BEGIN
        SELECT
                NAME,
                PUBLISHER
        INTO
                p_name,
                p_publish
        FROM
                PRODUCTION.PRODUCT
        WHERE
                AUTHOR LIKE '曹雪芹,高鹗';
        PRINT p_name;
        PRINT p_publish;
EXCEPTION
        WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
        PRINT'NO_DATA_FOUND OR TOO_MANY_ROWS';
WHEN OTHERS THEN
        PRINT 'ERROR OCCURS';
END;
/

2.1.3 事务控制

可以在 DMSQL 程序中直接使用 COMMIT、ROLLBACK 和 SAVEPOINT 语句进行事务控制：
 COMMIT[ WORK]; 提交一个事务
 ROLLBACK[ WORK]; 回滚一个事务
 SAVEPOINT <保存点名>; 在事务中设置一个保存点
 ROLLBACK [WORK] TO SAVEPOINT <保存点名>;将事务回滚到指定的保存点

2.2 游标

2.2.1 静态游标

静态游标又分为两种：隐式游标和显式游标。

隐式游标

隐式游标无需用户进行定义，每当用户在 DMSQL 程序中执行一个 DML 语句（INSERT、 UPDATE、DELETE）或者 SELECT …INTO 语句时，DMSQL 程序都会自动声明一个隐式游标并管理这个游标。

隐式游标的名称为“SQL”，用户可以通过隐式游标获取语句执行的一些信息。

DMSQL 程序中的每个游标都有%FOUND、%NOTFOUND、%ISOPEN 和%ROWCOUNT 四个
属性。

BEGIN
        UPDATE PERSON.PERSON SET PHONE=13818882888 WHERE NAME='孙丽';
        IF SQL%NOTFOUND THEN
                PRINT '此人不存在';
        ELSE
                PRINT '已修改';
        END IF;
END;
/

显式游标
显式游标指向一个查询语句执行后的结果集区域。当需要处理返回多条记录的查询时，应显式地定义游标以处理结果集的每一行。

DECLARE 
v_name VARCHAR(50); 
v_phone VARCHAR(50); 
c1 CURSOR FOR SELECT NAME,PHONE FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE A.PERSONID=B.PERSONID; 
BEGIN 
OPEN c1; 
LOOP 
FETCH c1 INTO v_name,v_phone; 
EXIT WHEN c1%NOTFOUND; 
PRINT v_name || v_phone; 
END LOOP; 
CLOSE c1; 
END; 
/

使用 FETCH…BULK COLLECT INTO 可以将查询结果批量地、一次性地赋给集合变量。FETCH…BULK COLLECT INTO 和 LIMIT rows 配合使用，可以限制每次获取数据的行数。

DECLARE 
TYPE V_rd IS RECORD(V_NAME VARCHAR(50),V_PHONE VARCHAR(50)); 
TYPE V_type IS TABLE OF V_rd INDEX BY INT; 
v_info V_type; 
c1 CURSOR IS SELECT NAME,PHONE FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B 
WHERE A.PERSONID=B.PERSONID; 
BEGIN 
OPEN c1; 
FETCH c1 BULK COLLECT INTO v_info; 
CLOSE c1; 
FOR I IN 1..v_info.COUNT LOOP 
PRINT v_info(I).V_NAME ||v_info(I).V_PHONE; 
END LOOP; 
END; 
/

BULK COLLECT 可以和 SELECT INTO、FETCH INTO、RETURNING INTO 一起使用，BULK COLLECT 之后 INTO 的变量必须是集合类型。

DECLARE 
CURSOR c1 FOR SELECT * FROM OTHER.EMPSALARY; 
my_ename CHAR(10); 
my_empno NUMERIC(4); 
my_sal NUMERIC(7,2); 
BEGIN 
OPEN c1; 
LOOP 
FETCH c1 INTO my_ename, my_empno, my_sal; 
EXIT WHEN c1%NOTFOUND; /* 当游标取不到数据时跳出循环 */ 
PRINT my_ename || ' ' || my_empno || ' ' || my_sal; 
EXIT WHEN c1%ROWCOUNT=5; /* 已经输出了 5 行数据，跳出循环*/ 
END LOOP; 
CLOSE c1; 
END; 
/

2.2.2 动态游标

与静态游标不同，动态游标在声明部分只是先声明一个游标类型的变量，并不指定其关
联的查询语句，在执行部分打开游标时才指定查询语句。http://www.cndba.cn/dave/article/116504

DECLARE 
my_ename CHAR(10); 
my_empno NUMERIC(4); 
my_sal NUMERIC(7,2); 
CURSOR c1; 
BEGIN 
OPEN C1 FOR SELECT * FROM OTHER.EMPSALARY; 
LOOP 
FETCH c1 INTO my_ename, my_empno, my_sal; 
EXIT WHEN c1%NOTFOUND; 
PRINT '姓名'||my_ename || '工号' || my_empno || ' 薪水' || my_sal; 
END LOOP; 
CLOSE c1; 
END; 
/

动态游标关联的查询语句还可以带有参数，参数以“?”指定，同时在打开游标语句中使用 USING 子句指定参数，且参数的个数和类型与语句中的“?”必须一一匹配。

DECLARE 
str VARCHAR; 
CURSOR csr; 
BEGIN 
OPEN csr FOR 'SELECT LOGINID FROM RESOURCES.EMPLOYEE WHERE TITLE =? OR TITLE =?' USING '销售经理','总经理'; 
LOOP 
FETCH csr INTO str; 
EXIT WHEN csr%NOTFOUND; 
PRINT str; 
END LOOP; 
CLOSE csr; 
END; 
/

2.2.3 使用静态游标更新、删除数据

可以使用静态游标更新或删除结果集中的数据。若需要使用游标更新或删除数据，则在游标关联的查询语句中一定要使用“FOR UPDATE 选项”。FOR UPDATE 选项出现在查询语句中，用于对要修改的行上锁，以防止用户在同一行上进行修改操作。

当游标拨动到需要更新或删除的行时，就可以使用 UPDATE/DELETE 语句进行数据更新/删除。此时必须在 UPDATE/DELETE 语句结尾使用“WHERE CURRENT OF 子句”，以限定删除/更新游标当前所指的行。

使用游标更新表中的数据。

DECLARE 
CURSOR csr is SELECT SALARY FROM RESOURCES.EMPLOYEE WHERE TITLE='销售经理' FOR UPDATE; 
BEGIN 
OPEN csr; 
IF csr%ISOPEN THEN 
FETCH csr; 
UPDATE RESOURCES.EMPLOYEE SET SALARY = SALARY + 10000 WHERE CURRENT OF csr; 
ELSE 
PRINT 'CURSOR IS NOT OPENED'; 
END IF; 
CLOSE csr; 
END; 
/

下面的例子使用游标删除表中的数据。

DECLARE 
CURSOR dcsr is SELECT EMPNO FROM OTHER.EMPSALARY WHERE ENAME='KING' FOR UPDATE; 
BEGIN 
OPEN dcsr; 
IF dcsr%ISOPEN THEN 
FETCH dcsr; 
DELETE FROM OTHER.EMPSALARY WHERE CURRENT OF dcsr; 
ELSE 
PRINT 'CURSOR IS NOT OPENED'; 
END IF; 
CLOSE dcsr; 
END; 
/

2.2.4 使用游标 FOR 循环

游标 FOR 循环自动使用 FOR 循环依次读取结果集中的数据。当 FOR 循环开始时，游标会自动打开（不需要使用 OPEN 方法）；每循环一次系统自动读取游标当前行的数据（不需要使用 FETCH）；当数据遍历完毕退出FOR 循环时，游标被自动关闭（不需要使用CLOSE），大大降低了应用程序的复杂度。

下面的例子使用了隐式游标 FOR 循环。

BEGIN 
FOR v_emp IN (SELECT * FROM RESOURCES.EMPLOYEE) 
LOOP 
DBMS_OUTPUT.PUT_LINE(V_EMP.TITLE || '的工资' || V_EMP.SALARY); 
END LOOP; 
END; 
/

下面的例子使用的是显式游标 FOR 循环。

DECLARE 
CURSOR cur_emp IS SELECT * FROM RESOURCES.EMPLOYEE; 
BEGIN 
FOR V_EMP IN CUR_EMP LOOP 
DBMS_OUTPUT.PUT_LINE(V_EMP.TITLE || '的工资' || V_EMP.SALARY); 
END LOOP; 
END; 
/

2.3 动态SQL

动态 SQL 指在 DMSQL程序进行编译时是不确定的 SQL，编译时对动态 SQL 不进行处理，在 DMSQL 程序运行时才动态地生成并执行这些 SQL。

动态 SQL 中可以使用参数，并支持两种指定参数的方式：用“?”表示参数和用“:variable”表示参数。
当用“?”表示参数时，在指定参数值时可以是任意的值，但参数值个数一定要与“?”的个数相同，同时数据类型一定要匹配（能够互相转换也可以），不然会报数据类型不匹配
的错误。

CREATE OR REPLACE PROCEDURE proc(cate IN INT, time IN DATE) AS 
DECLARE 
str_sql varchar := 'SELECT NAME,PUBLISHER from PRODUCTION.PRODUCT WHERE PRODUCT_SUBCATEGORYID = ? AND PUBLISHTIME> ?'; 
BEGIN 
EXECUTE IMMEDIATE str_sql USING cate,time; 
EXCEPTION 
WHEN OTHERS THEN PRINT 'error'; 
END; 
/ 
CALL proc(4, '2001-01-01');

当用“:variable”表示参数时，若 SQL 动态语句文本为普通语句方式，则系统将其转化为“?”进行处理。若 SQL 动态语句文本为脚本方式（语句块方式或多条用分号分隔的 SQL 语句），则保留“:variable”参数形式。

例如，下面的 DMSQL 程序中 sql_str 采用普通语句方式，其中的两个:x 被转化为? 进行处理，程序执行成功。

DECLARE 
sql_str VARCHAR := 'UPDATE PRODUCTION.PRODUCT SET SELLSTARTTIME=:x,SELLENDTIME=:x WHERE NAME=''红楼梦'';'; 
BEGIN 
EXECUTE IMMEDIATE SQL_STR USING '2015-01-01','2018-01-01'; 
END; 
/

3 DMSQL 中的控制结构

3.1 语句块

语句块是 DMSQL 程序的基本单元。每个语句块由关键字 DECLARE、BEGIN、 EXCEPTION 和 END 划分为声明部分、执行部分和异常处理部分。其中执行部分是必须的，说明和异常处理部分可以省略。

该例中有一个全局变量 X，同时子语句块中又定义了一个局部变量 X。

CREATE OR REPLACE PROCEDURE PROC_BLOCK AS 
X INT := 0; 
COUNTER INT := 0; 
BEGIN 
FOR I IN 1 .. 4 LOOP 
X := X + 1000; 
COUNTER := COUNTER + 1; 
PRINT CAST(X AS CHAR(10)) || CAST(COUNTER AS CHAR(10)) || 'OUTER LOOP'; 
/* 这里是一个嵌套的子语句块的开始 */ 
DECLARE 
X INT := 0; -- 局部变量 X，与全局变量 X 同名 
BEGIN 
FOR I IN 1 .. 4 LOOP 
X := X + 1; 
COUNTER := COUNTER + 1; 
PRINT CAST(X AS CHAR(10)) || CAST(COUNTER AS CHAR(10)) || 'INNER LOOP'; 
END LOOP; 
END; 
/* 子语句块结束 */ 
END LOOP; 
END; 
/

执行这个存储过程： 
CALL PROC_BLOCK;

3.2 分支结构

3.2.1 IF 语句

DECLARE 
PROCEDURE proc_if ( 
sales dec, 
quota dec, 
emp_id dec 
) 
IS 
bonus dec:= 0; 
BEGIN 
IF sales > (quota + 400) THEN 
bonus := (sales - quota)/4; 
ELSE IF sales > quota THEN 
bonus := 100; 
ELSE 
bonus :=0; 
END IF; 
END IF; 
DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus); 
UPDATE RESOURCES.EMPLOYEE SET SALARY = SALARY + bonus WHERE EMPLOYEEID = emp_id; 
END proc_if ; 

BEGIN 
proc_if (30100, 20000, 1); 
proc_if (15000, 10000, 2); 
proc_if (9000, 10000, 3); 
END; 
/

3.2.2 CASE 语句

CREATE OR REPLACE PROCEDURE PROC_CASE(GRADE CHAR(10)) AS 
DECLARE 
appraisal VARCHAR2(20); 
BEGIN 
appraisal := 
CASE GRADE 
WHEN NULL THEN 'IS NULL' 
WHEN 'A' THEN 'Excellent' 
WHEN 'B' THEN 'Good' 
WHEN 'C' THEN 'Fair' 
ELSE 'No such grade' 
END; 
DBMS_OUTPUT.PUT_LINE ('Grade ' ||grade|| ' is ' ||appraisal); 
END; 
/

CREATE OR REPLACE PROCEDURE PROC_CASE(GRADE CHAR(10)) AS 
DECLARE 
appraisal VARCHAR2(20); 
BEGIN 
appraisal := 
CASE 
WHEN grade IS NULL THEN 'IS NULL' 
WHEN grade = 'A' THEN 'Excellent' 
WHEN grade = 'B' THEN 'Good' 
WHEN grade = 'C' THEN 'Fair' 
ELSE 'No such grade' 
END; 
DBMS_OUTPUT.PUT_LINE ('Grade ' ||grade|| ' is ' ||appraisal); 
END; 
/

3.2.3 SWITCH 语句

{ 
VARCHAR appraisal='B'; 
SWITCH (appraisal) 
{ 
CASE NULL: PRINT 'IS NULL'; BREAK; 
CASE 'A': PRINT 'Excellent'; BREAK; 
CASE 'B': PRINT 'Good'; BREAK; 
CASE 'C': PRINT 'Fair'; BREAK; 
DEFAULT: PRINT 'No such grade'; 
} 
}
/

3.3 循环控制结构

DMSQL 程序支持五种类型的循环语句：LOOP 语句、WHILE 语句、FOR 语句、 REPEAT 语句和 FORALL 语句。其中前四种为基本类型的循环语句：LOOP 语句循环重复执行一系列语句，直到 EXIT 语句终止循环为止；WHILE 语句循环检测一个条件表达式，当表达式的值为 TRUE 时就执行循环体的语句序列；FOR 语句对一系列的语句重复执行指定次数的循环；REPEAT 语句重复执行一系列语句直至达到条件表达式的限制要求。FORALL语句对一条 DML 语句执行多次，当 DML 语句中使用数组或嵌套表时可进行优化处理，能大幅提升性能。

3.3.1 LOOP 语句

例如，下面的例子在 LOOP 循环中打印参数 A 的值，并将 A 的值减 1，直到 A 小于等于 0 时跳出循环。 http://www.cndba.cn/dave/article/116504

CREATE OR REPLACE PROCEDURE PROC_LOOP(a IN OUT INT) AS 
BEGIN 
LOOP 
IF a<=0 THEN 
EXIT; 
END IF; 
PRINT a; 
a:=a-1; 
END LOOP; 
END; 
/ 
CALL PROC_LOOP(5);

3.3.2 WHILE 语句

使用 WHILE 语句实现上一小节中 LOOP 循环相同的功能。 http://www.cndba.cn/dave/article/116504

CREATE OR REPLACE PROCEDURE PROC_WHILE(a IN OUT INT) AS 
BEGIN 
WHILE a>0 LOOP 
PRINT a; 
a:=a-1; 
END LOOP; 
END; 
/ 
CALL PROC_WHILE(5);

3.3.3 FOR 语句

例如，使用 FOR 语句实现前面 LOOP 语句和 WHILE 语句例子相同的功能。

CREATE OR REPLACE PROCEDURE PROC_FOR1 (a IN OUT INT) AS 
BEGIN 
FOR I IN REVERSE 1 .. a LOOP 
PRINT I; 
a:=I-1; 
END LOOP; 
END; 
/ 
CALL PROC_FOR1(5);

FOR 语句中的循环计数器可与当前语句块内的参数或变量同名，这时该同名的参数或变量在 FOR 语句的范围内将被屏蔽。如下例所示：

DECLARE 
v1 DATE:=DATE '2017-03-17'; 
BEGIN 
FOR v1 IN 3.. 5 LOOP 
PRINT v1; 
END LOOP; 
PRINT v1; 
END; 
/

3.3.4 REPEAT 语句

REPEAT 语句先执行执行部分，然后判断条件表达式，若为 TRUE 则控制重新回到循环顶部，若为 FALSE 则退出循环。REPEAT 语句的执行部分至少会执行一次。

DECLARE 
a INT; 
BEGIN 
a := 0; 
REPEAT 
a := a+1; 
PRINT a; 
UNTIL a>10; 
END; 
/

3.3.5 FORALL 语句

CREATE TABLE t1_forall(C1 INT, C2 VARCHAR(50)); 
CREATE OR REPLACE PROCEDURE p1_forall AS 
BEGIN 
FORALL I IN 1..10 
INSERT INTO t1_forall SELECT TOP 1 EMPLOYEEID, TITLE FROM RESOURCES.EMPLOYEE; 
END; 
/

3.3.6 EXIT 语句

EXIT 语句与循环语句一起使用，用于终止其所在循环语句的执行，将控制转移到该循环语句外的下一个语句继续执行。

当 EXIT 后面的标号名省略时，该语句将终止直接包含它的那条循环语句；
当 EXIT 后面带有标号名时，该语句用于终止标号名所标识的那条循环语句。需要注意的是，该标号名所标识的语句必须是循环语句，并且 EXIT 语句必须出现在此循环语句中。
当 EXIT 语句位于多重循环中时，可以用该功能来终止其中的任何一重循环。

下面的例子中 EXIT 后没有带标号，其所在的那层循环被终止。

DECLARE 
a INT; 
b INT; 
BEGIN 
a := 0; 
LOOP 
FOR b in 1 .. 2 LOOP 
PRINT '内层循环' ||b; 
EXIT WHEN a > 3; 
END LOOP; 
a := a + 2; 
PRINT '---外层循环' ||a; 
EXIT WHEN a> 5; 
END LOOP; 
END; 
/

下面的例子中 EXIT 后带有标号（flag1），则其终止了指定的那层循环。

DECLARE 
a INT; 
b INT; 
BEGIN 
a := 0; 
<<flag1>> 
LOOP 
FOR b in 1 .. 2 LOOP 
PRINT '内层循环' ||b; 
EXIT flag1 WHEN a > 3; 
END LOOP; 
a := a + 2; 
PRINT '---外层循环' ||a; 
EXIT WHEN a> 5; 
END LOOP; 
END; 
/

3.3.7 CONTINUE 语句

若 CONTINUE 后没有跟 WHEN 子句，则无条件立即退出当前循环，并且将语句控制转移到这次循环的下一次循环迭代或者是一个指定标号名的循环的开始位置并继续执行。

DECLARE 
x INT:= 0; 
BEGIN 
<<flag1>> -- CONTINUE 跳出之后，回到这里 
FOR I IN 1..4 LOOP 
DBMS_OUTPUT.PUT_LINE ('循环内部，CONTINUE 之前: x = ' || TO_CHAR(x)); 
x := x + 1; 
CONTINUE flag1; 
DBMS_OUTPUT.PUT_LINE ('循环内部，CONTINUE 之后: x = ' || TO_CHAR(x)); 
END LOOP; 
DBMS_OUTPUT.PUT_LINE (' 循环外部: x = ' || TO_CHAR(x)); 
END; 
/

下面的例子说明了 CONTINUE-WHEN 语句的使用。

DECLARE 
x INT:= 0; 
BEGIN 
-- CONTINUE 跳出之后，回到这里 
FOR I IN 1..4 LOOP 
DBMS_OUTPUT.PUT_LINE ('循环内部，CONTINUE 之前: x = ' || TO_CHAR(x)); 
x := x + 1; 
CONTINUE WHEN x > 3; 
DBMS_OUTPUT.PUT_LINE ('循环内部，CONTINUE 之后: x = ' || TO_CHAR(x)); 
END LOOP; 
DBMS_OUTPUT.PUT_LINE (' 循环外部: x = ' || TO_CHAR(x)); 
END; 
/

3.4 顺序结构

3.4.1 GOTO 语句

GOTO 语句的使用有下列限制：
 GOTO 不能跳入一个 IF 语句、CASE 语句、循环语句或下层语句块中；
 GOTO 不能从一个异常处理器跳回当前块，但是可以跳转到包含当前块的上层语句块。

DECLARE 
v_name 
VARCHAR2(25); 
v_empid INT := 1; 
BEGIN 
<<get_name>> 
SELECT NAME INTO v_name FROM RESOURCES.EMPLOYEE A,PERSON.PERSON B WHERE A.PERSONID=B.PERSONID AND A.EMPLOYEEID=v_empid; 
BEGIN 
DBMS_OUTPUT.PUT_LINE(v_name); 
v_empid := v_empid + 1; 
IF v_empid <=5 THEN 
GOTO get_name; 
END IF; 
END; 
END;
/

3.4.2 NULL 语句

NULL 语句不做任何事情，只是用于保证语法的正确性，或增加程序的可读性。

CREATE OR REPLACE FUNCTION func_null ( 
a INT, 
b INT 
) RETURN INT 
AS 
BEGIN 
RETURN (a/b); 
EXCEPTION 
WHEN ZERO_DIVIDE THEN 
DBMS_OUTPUT.PUT_LINE('除 0 错误'); 
WHEN OTHERS THEN 
NULL; 
END; 
/

4 DMSQL 异常处理

4.1 预定义异常

DM 提供了一些预定义的异常，这些异常与常见的 DM 错误相对应。此外，还有一个特殊的异常OTHERS，它处理所有没有明确列出的异常。OTHERS 对应的异常处理语句必须放在其他异常处理语句之后。

DECLARE 
v_name VARCHAR(50); 
v_phone VARCHAR(50); 
BEGIN 
SELECT NAME,PHONE INTO V_NAME,V_PHONE FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE A.PERSONID=B.PERSONID AND B.TITLE='销售代表'; 
PRINT v_name||' '||v_phone; 
EXCEPTION 
WHEN TOO_MANY_ROWS THEN 
PRINT 'SELECT INTO 中包含多行数据'; 
END; 
/

4.2 用户自定义异常

4.2.1 使用 EXCEPTION FOR 将异常变量与错误号绑定

在 DMSQL 程序的声明部分声明一个异常变量。FOR 子句用来为异常变量绑定错误号(SQLCODE 值)及错误描述。<错误号>必须是-20000 到-30000 间的负数值，<错误描述>则为字符串类型。如果未显式指定错误号，则系统在运行中在-10001 到-15000 区间中顺序为其绑定错误值。

CREATE OR REPLACE PROCEDURE proc_exception (v_personid INT) 
IS 
v_name VARCHAR(50);  
v_email VARCHAR(50); 
e1 EXCEPTION FOR -20001, 'EMAIL 为空'; 
BEGIN 
SELECT NAME,EMAIL INTO v_name,v_email FROM PERSON.PERSON WHERE PERSONID=v_personid; 
IF v_email='' THEN 
RAISE e1; 
ELSE 
PRINT v_name || v_email; 
END IF; 
EXCEPTION 
WHEN E1 THEN 
PRINT v_name ||' '||SQLCODE||' '||SQLERRM; 
END; 
/ 

CALL proc_exception(2);

4.2.2 使用 EXCEPTION_INIT 将特定的错误号与程序中所声明的异常标示符关联起来

下面的例子使用 EXCEPTION_INIT 将 DM 服务器的特定错误“-2206：缺少参数值” 与异常变量 e1 关连起来。

CREATE OR REPLACE PROCEDURE proc_exception2(v_personid INT) 
IS 
TYPE Rec_type IS RECORD(V_NAME VARCHAR(50),V_EMAIL VARCHAR(50)); 
v_col Rec_type; 
e1 EXCEPTION; 
PRAGMA EXCEPTION_INIT(e1, -2206); 
BEGIN 
SELECT NAME,EMAIL INTO v_col FROM PERSON.PERSON WHERE ERSONID=v_personid; 
IF v_col.V_EMAIL=''THEN 
RAISE e1; 
ELSE 
PRINT v_col.V_NAME || v_col.V_EMAIL; 
END IF; 
EXCEPTION 
WHEN e1 THEN 
PRINT v_col.V_NAME ||' '||SQLCODE||':' ||SQLERRM(SQLCODE); 
END; 
/ 
CALL proc_exception2(2);

下面的例子则使用 EXCEPTION_INIT 定义用户自定义异常，并使用 RAISE 语句抛出该异常。

CREATE OR REPLACE PROCEDURE proc_exception3 (v_personid INT) 
IS 
TYPE Rec_type IS RECORD (V_NAME VARCHAR(50),V_EMAIL VARCHAR(50)); 
v_col Rec_type; 
e2 EXCEPTION; 
PRAGMA EXCEPTION_INIT(e2, -25000); 
BEGIN 
SELECT NAME,EMAIL INTO v_col FROM PERSON.PERSON WHERE PERSONID=v_personid; 
IF v_col.V_EMAIL='' THEN 
RAISE e2; 
ELSE 
PRINT v_col.V_NAME || v_col.V_EMAIL; 
END IF; 
EXCEPTION 
WHEN e2 THEN 
PRINT v_col.V_NAME ||'的邮箱为空'; 
END; 
/ 

CALL proc_exception3(2);

4.3 内置函数 SQLCODE 和 SQLERRM

DMSQL 程序提供了内置函数 SQLCODE 和 SQLERRM，可以在异常处理部分通过这两个函数获取异常对应的错误码和描述信息。

SQLCODE 返回错误码，为一个负数。
SQLERRM 返回异常的描述信息，为字符串类型。

DECLARE 
e1 EXCEPTION FOR -20001, 'EMAIL 为空'; 
BEGIN 
RAISE e1; 
EXCEPTION 
WHEN e1 THEN 
PRINT SQLCODE ||' '|| SQLERRM; 
END; 
/

4.4 异常处理部分

一个异常处理器可以同时对多个异常进行统一处理，在 WHEN 子句中用 OR 分隔多个异常名。

CREATE OR REPLACE PROCEDURE proc_exception5 (score INT) AS 
e1 EXCEPTION FOR -20001, '补考'; 
e2 EXCEPTION FOR -20002, '不能补考'; 
BEGIN 
IF score BETWEEN 90 AND 100 THEN 
PRINT '考试通过'; 
ELSEIF score BETWEEN 80 AND 90 THEN 
RAISE e1; 
ELSE 
RAISE e2; 
END IF; 
EXCEPTION 
WHEN e1 OR e2 THEN --同时处理多个异常 
PRINT '考试不通过'; 
END; 
/ 
CALL proc_exception5(50);

5 DMSQL 程序（存储过程、函数）

5.1 存储过程

存储过程可执行部分是存储过程的核心部分，由 SQL 语句和流控制语句构成。支持的 SQL 语句包括：
 数据查询语句(SELECT)
 数据操纵语句(INSERT、DELETE、UPDATE)
 游标定义及操纵语句(DECLARE CURSOR、OPEN、FETCH、CLOSE)
 事务控制语句(COMMIT、ROLLBACK)
 动态 SQL 执行语句(EXECUTE IMMEDIATE)

CREATE OR REPLACE PROCEDURE RESOURCES.proc_1(a IN OUT INT) 
AS 
b INT:=10; 
BEGIN 
a:=a+b; 
PRINT a; 
EXCEPTION 
WHEN OTHERS THEN NULL; 
END; 
/

5.2 函数

存储函数与存储过程在结构和功能上十分相似，主要的差异在于：
1．存储过程没有返回值，调用者只能通过访问 OUT 或 IN OUT 参数来获得执行结果，而存储函数有返回值，它把执行结果直接返回给调用者；
2．存储过程中可以没有返回语句，而存储函数必须通过返回语句结束；
3．不能在存储过程的返回语句中带表达式，而存储函数必须带表达式；
4．存储过程不能出现在一个表达式中，而存储函数可以出现在表达式中。 http://www.cndba.cn/dave/article/116504

CREATE OR REPLACE FUNCTION RESOURCES.fun_1(a INT, b INT) RETURN INT AS 
s INT; 
BEGIN 
s:=a+b; 
RETURN s; 
EXCEPTION 
WHEN OTHERS THEN NULL; 
END; 
/ 

调用这个存储函数： 
SELECT RESOURCES.fun_1(1,2);

5.3 参数

存储模块及模块中定义的子模块都可以带参数，用来给模块传送数据及向外界返回数据。在存储过程或存储函数中定义一个参数时，必须说明名称、参数模式和数据类型。三种可能的参数模式是：IN(缺省模式)、OUT 和 IN OUT，意义分别为：

IN：输入参数，用来将数据传送给模块；
OUT：输出参数，用来从模块返回数据到进行调用的模块；
IN OUT：既作为输入参数，也作为输出参数。

DECLARE 
emp_num INT := 1; 
bonus DEC(19,4) := 6000; 
title VARCHAR(50); 
PROCEDURE raise_salary (emp_id IN INT, --输入参数 
amount IN OUT DEC(19,4), --输入输出参数 
emp_title OUT VARCHAR(50) --输出参数
) 
IS 
BEGIN 
UPDATE RESOURCES.EMPLOYEE SET SALARY = SALARY + amount WHERE EMPLOYEEID = emp_id; 
SELECT TITLE,SALARY INTO emp_title,amount FROM RESOURCES.EMPLOYEE WHERE EMPLOYEEID = emp_id; 
END raise_salary; 

BEGIN 
raise_salary(emp_num, bonus, title); 
DBMS_OUTPUT.PUT_LINE('工号：'||emp_num||' '||'职位：'||title||' '||'加薪后薪水：'||bonus); 
END; 
/

使用赋值符号“:=”或关键字 DEFAULT，可以为 IN 参数指定一个缺省值。如果调用时未指定参数值，系统将自动使用该参数的缺省值。例如：

CREATE PROCEDURE proc_def_arg(a varchar(10) default 'abc', b INT:=123) AS 
BEGIN 
PRINT a; 
PRINT b; 
END; 
/ 

调用过程 PROC_DEF_ARG，不指定输入参数值： 
CALL proc_def_arg;

也可以只指定第一个参数，省略后面的参数： 
CALL proc_def_arg('我们');

5.4 变量

DECLARE -- 可以在这里赋值 
salary DEC(19,4); 
worked_time DEC(19,4) := 60; 
hourly_salary DEC(19,4) := 1055; 
bonus DEC(19,4) := 150; 
position VARCHAR(50); 
province VARCHAR(64); 
counter DEC(19,4) := 0; 
done BOOLEAN; 
valid_id BOOLEAN; 
emp_rec1 RESOURCES.EMPLOYEE%ROWTYPE; 
emp_rec2 RESOURCES.EMPLOYEE%ROWTYPE; 
TYPE meeting_type IS TABLE OF INT INDEX BY INT; 
meeting meeting_type; 
BEGIN -- 也可以在这里赋值 
salary := (worked_time * hourly_salary) + bonus; 
SELECT TITLE INTO position FROM RESOURCES.EMPLOYEE WHERE LOGINID='L3'; 
province := 'ShangHai'; 
province := UPPER('wuhan'); 
done := (counter > 100); 
valid_id := TRUE; 
emp_rec1.employeeid := 1; 
emp_rec1.managerid := null; 
emp_rec1 := emp_rec2; 
meeting(5) := 20000 * 0.15; 
PRINT position||'来自'||province; 
PRINT ('加班工资'||salary); 
END; 
/

5.5 调用、重新编译与删除存储模块

5.5.1 调用

对存储过程的调用可通过 CALL 语句来完成，也可以什么也不加直接通过名字及相应的参数执行即可，两种方式没有区别。
对于存储函数，除了可以通过 CALL 语句和直接通过名字调用外，还可以通过 SELECT语句来调用，且执行方式存在一些区别：

通过 CALL 和直接使用名字调用存储函数时，不会返回函数的返回值，仅执行其中的操作；
通过 SELECT 语句调用存储函数时，不仅会执行其中的操作，还会返回函数的返回值。SELECT 调用的存储函数不支持含有 OUT、IN OUT 模式的参数

CREATE OR REPLACE FUNCTION proc(A INT) RETURN INT AS 
DECLARE 
s INT; 
lv INT; 
rv INT; 
BEGIN 
IF A = 1 THEN 
s = 1; 
ELSIF A = 2 THEN 
s = 1; 
ELSE 
rv = proc(A - 1); 
lv = proc(A - 2); 
s = lv + rv; 
print lv || '+' || rv || '=' || s; 
END IF; 
RETURN S; 
END; 
/ 

通过 CALL 来调用函数 proc： 
CALL proc(3); 

使用 SELECT 来调用这个函数 proc: 
SELECT proc(3);

5.5.2 重新编译存储模块

ALTER PROCEDURE RESOURCES.person_account COMPILE;

5.5.3 删除存储模块

DROP PROCEDURE RESOURCES.proc_1; 
DROP FUNCTION RESOURCES.fun_1;

签到成功

CNDBA社区