定义 SQL 过程
SQL 过程的 CREATE PROCEDURE 语句对该过程进行命名，定义参数及其属性，提供有关调用该过程时使用的过程的其他信息，并定义过程主体。 它以称为 SQL PL 的 SQL 过程语言编写。
过程主体 是过程的可执行部分，并且是单个 SQL 语句。
请考虑以下简单示例，该示例采用员工编号和费率作为输入，并更新员工的工资:
CREATE PROCEDURE UPDATE_SALARY_1
(IN EMPLOYEE_NUMBER CHAR(10),
IN RATE DECIMAL(6,2))
LANGUAGE SQL MODIFIES SQL DATA
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * RATE
WHERE EMPNO = EMPLOYEE_NUMBER; 此 CREATE PROCEDURE 语句:
- 将过程命名为 UPDATE_SALARY_1。
- 定义参数 EMPLOYEE_NUMBER ，它是输入参数，是长度为 6 的字符数据类型，参数 RATE 是输入参数，是十进制数据类型。
- 指示该过程是修改 SQL 数据的 SQL 过程。
- 将过程主体定义为单个 UPDATE 语句。 当调用该过程时，将使用为 EMPLOYEE_NUMBER 和 RATE 传递的值来执行 UPDATE 语句。
可以使用 SQL 控制语句将逻辑添加到 SQL 过程，而不是单个 UPDATE 语句。 SQL 控制语句由以下语句组成:
- 赋值语句
- CALL 语句
- CASE 语句
- 复合语句
- FOR 语句
- GET DIAGNOSTICS 语句
- GOTO 语句
- IF 语句
- ITERATE 语句
- LEAVE 语句
- LOOP 语句
- REPEAT 语句
- RESIGNAL 语句
- RETURN 语句
- SIGNAL 语句
- WHILE 语句
以下示例将上次评估时收到的员工编号和评级作为输入。 此过程使用 CASE 语句来确定更新的相应增加值和奖金。
CREATE PROCEDURE UPDATE_SALARY_2
(IN EMPLOYEE_NUMBER CHAR(6),
IN RATING INT)
LANGUAGE SQL MODIFIES SQL DATA
CASE RATING
WHEN 1 THEN
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.10,
BONUS = 1000
WHERE EMPNO = EMPLOYEE_NUMBER;
WHEN 2 THEN
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.05,
BONUS = 500
WHERE EMPNO = EMPLOYEE_NUMBER;
ELSE
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.03,
BONUS = 0
WHERE EMPNO = EMPLOYEE_NUMBER;
END CASE;此 CREATE PROCEDURE 语句:
- 将过程命名为 UPDATE_SALARY_2。
- 定义参数 EMPLOYEE_NUMBER ，该参数是输入参数，是长度为 $TAG1 的字符数据类型，参数评级是输入参数，是整数数据类型。
- 指示该过程是修改 SQL 数据的 SQL 过程。
- 定义过程主体。 调用该过程时，将检查输入参数额定值并执行相应的更新语句。
可以通过添加复合语句将多个语句添加到过程主体。 在复合语句中，可以指定任意数目的 SQL 语句。 此外，还可以声明 SQL 变量，游标和处理程序。
以下示例将部门编号作为输入。 它返回该部门中所有员工的总工资以及该部门中获得奖金的员工人数。
CREATE PROCEDURE RETURN_DEPT_SALARY
(IN DEPT_NUMBER CHAR(3),
OUT DEPT_SALARY DECIMAL(15,2),
OUT DEPT_BONUS_CNT INT)
LANGUAGE SQL READS SQL DATA
P1: BEGIN
DECLARE EMPLOYEE_SALARY DECIMAL(9,2);
DECLARE EMPLOYEE_BONUS DECIMAL(9,2);
DECLARE TOTAL_SALARY DECIMAL(15,2)DEFAULT 0;
DECLARE BONUS_CNT INT DEFAULT 0;
DECLARE END_TABLE INT DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT SALARY, BONUS FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = DEPT_NUMBER;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET DEPT_SALARY = NULL;
OPEN C1;
FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
WHILE END_TABLE = 0 DO
SET TOTAL_SALARY = TOTAL_SALARY + EMPLOYEE_SALARY + EMPLOYEE_BONUS;
IF EMPLOYEE_BONUS > 0 THEN
SET BONUS_CNT = BONUS_CNT + 1;
END IF;
FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
END WHILE;
CLOSE C1;
SET DEPT_SALARY = TOTAL_SALARY;
SET DEPT_BONUS_CNT = BONUS_CNT;
END P1; 此 CREATE PROCEDURE 语句:
- 将过程命名为 RETURN_DEPT_SALARY。
- 定义参数 DEPT_NUMBER (它是输入参数并且是长度为 3 的字符数据类型) ，参数 DEPT_SALARY (它是输出参数并且是十进制数据类型) 和参数 DEPT_BONUS_CNT (它是输出参数并且是整数数据类型)。
- 指示该过程是读取 SQL 数据的 SQL 过程
- 定义过程主体。
- 将 SQL 变量 EMPLOYEE_SALARY 和 TOTAL_SALARY 声明为 DECIMAL 字段。
- 声明 SQL 变量 BONUS_CNT 和 END_TABLE ，它们是整数并且已初始化为 0。
- 声明用于从员工表中选择列的游标 C1 。
- 声明 NOT FOUND 的继续处理程序，当调用该处理程序时，该处理程序会将变量 END_TABLE 设置为 1。 当 FETCH 没有更多要返回的行时，将调用此处理程序。 调用处理程序时， SQLCODE 和 SQLSTATE 将重新初始化为 0。
- 声明 SQLEXCEPTION 的出口处理程序。 如果调用，那么 DEPT_SALARY 将设置为 NULL ，并且复合语句的处理将终止。 如果发生任何错误，即 SQLSTATE 类不是 "00" ， "01" 或 "02" ，那么将调用此处理程序。 由于指标总是传递给 SQL 存储过程，因此当存储过程返回时，DEPT_SALARY 的指标值为 -1 。 如果调用此处理程序，那么会将 SQLCODE 和 SQLSTATE 重新初始化为 0。
如果未指定 SQLEXCEPTION 的处理程序，并且发生了未在另一处理程序中处理的错误，那么会终止复合语句的执行，并在 SQLCA 中返回该错误。 与指示符类似， SQLCA 始终从 SQL 过程返回。
- 包括游标 C1的 OPEN ， FETCH 和 CLOSE。 如果未指定游标的 CLOSE ，那么将在复合语句末尾关闭游标，因为在 CREATE PROCEDURE 语句中未指定 SET RESULT SETS。
- 包括一个 WHILE 语句，该语句循环直到访存最后一个记录。 对于检索到的每行， TOTAL_SALARY 将递增，如果员工的奖金超过 0 ，那么 BONUS_CNT 将递增。
- 返回 DEPT_SALARY 和 DEPT_BONUS_CNT 作为输出参数。
可以使复合语句成为原子语句，因此如果发生不期望的错误，那么将回滚原子语句中的语句。 原子复合语句是使用 SAVEPOINTS 实现的。 如果复合语句成功，那么将落实事务。
以下示例将部门编号作为输入。 它确保EMPLOYEE_奖金表存在，并插入部门中获取奖金的所有员工的名称。 此过程将返回获得奖金的所有员工的总计数。
CREATE PROCEDURE CREATE_BONUS_TABLE
(IN DEPT_NUMBER CHAR(3),
INOUT CNT INT)
LANGUAGE SQL MODIFIES SQL DATA
CS1: BEGIN ATOMIC
DECLARE NAME VARCHAR(30) DEFAULT NULL;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710'
SELECT COUNT(*) INTO CNT
FROM DATALIB.EMPLOYEE_BONUS;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
SET CNT = CNT - 1;
DECLARE UNDO HANDLER FOR SQLEXCEPTION
SET CNT = NULL;
IF DEPT_NUMBER IS NOT NULL THEN
CREATE TABLE DATALIB.EMPLOYEE_BONUS
(FULLNAME VARCHAR(30),
BONUS DECIMAL(10,2),
PRIMARY KEY (FULLNAME));
FOR_1:FOR V1 AS C1 CURSOR FOR
SELECT FIRSTNME, MIDINIT, LASTNAME, BONUS
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = CREATE_BONUS_TABLE.DEPT_NUMBER
DO
IF BONUS > 0 THEN
SET NAME = FIRSTNME CONCAT ' ' CONCAT
MIDINIT CONCAT ' 'CONCAT LASTNAME;
INSERT INTO DATALIB.EMPLOYEE_BONUS
VALUES(CS1.NAME, FOR_1.BONUS);
SET CNT = CNT + 1;
END IF;
END FOR FOR_1;
END IF;
END CS1; 此 CREATE PROCEDURE 语句:
- 将过程命名为 CREATE_BONUS_TABLE。
- 定义参数 DEPT_NUMBER ，它是输入参数，是长度为 3 的字符数据类型，而参数 CNT 是输入/输出参数，是整数数据类型。
- 指示该过程是修改 SQL 数据的 SQL 过程
- 定义过程主体。
- 将 SQL 变量 NAME 声明为可变字符。
- 声明 SQLSTATE 42710 的继续处理程序，表已存在。 如果EMPLOYEE_奖金表已存在，那么将调用处理程序并检索表中的记录数。 SQLCODE 和 SQLSTATE 将重置为 0 并继续处理 FOR 语句。
- 声明 SQLSTATE 23505 的继续处理程序，重复键。 如果该过程尝试插入表中已存在的名称，那么将调用该处理程序并减少 CNT。 在 INSERT 语句之后继续对 SET 语句进行处理。
- 声明 SQLEXCEPTION 的 UNDO 处理程序。 如果调用，将回滚先前的语句，将 CNT 设置为 0 ，并在复合语句之后继续处理。 在这种情况下，由于复合语句后面没有语句，因此过程返回。
- 使用 FOR 语句来声明游标 C1 以从 EMPLOYEE 表读取记录。 在 FOR 语句中，来自选择列表的列名用作包含所访存行中的数据的 SQL 变量。 对于每行，来自 FIRSTNME ， MIDINIT 和 LASTNAME 列的数据将与空白并置在一起，并将结果放入 SQL 变量 NAME 中。 SQL 变量 NAME 和 BONUS 将插入到 EMPLOYEE_BONUS 表中。 因为在创建过程时必须知道选择列表项的数据类型，所以在创建过程时， FOR 语句中指定的表必须存在。
可以使用定义了 SQL 变量名称的 FOR 语句或复合语句的标签名称对其进行限定。 在示例中， FOR_1.BONUS 是指 SQL 变量，该变量包含所选每行的列花红的值。 CS1.NAME 是复合语句中定义的变量 NAME ，其起始标签为 CS1。 还可以使用过程名称来限定参数名称。 CREATE_BONUS_TABLE.DEPT_NUMBER 是过程 CREATE_BONUS_TABLE 的 DEPT_NUMBER 参数。 如果在还允许列名的 SQL 语句中使用未限定的 SQL 变量名，并且变量名与列名相同，那么将使用该名称来引用该列。
您还可以在 SQL 过程中使用动态 SQL。 以下示例创建一个包含特定部门中所有员工的表。 部门号将作为输入传递到过程，并与表名并置。
CREATE PROCEDURE CREATE_DEPT_TABLE (IN P_DEPT CHAR(3))
LANGUAGE SQL
BEGIN
DECLARE STMT CHAR(1000);
DECLARE MESSAGE CHAR(20);
DECLARE TABLE_NAME CHAR(30);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET MESSAGE = 'ok';
SET TABLE_NAME = 'CORPDATA.DEPT_' CONCAT P_DEPT CONCAT '_T';
SET STMT = 'DROP TABLE ' CONCAT TABLE_NAME;
PREPARE S1 FROM STMT;
EXECUTE S1;
SET STMT = 'CREATE TABLE ' CONCAT TABLE_NAME CONCAT
'( EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL,
LASTNAME CHAR(15) NOT NULL,
SALARY DECIMAL(9,2))';
PREPARE S2 FROM STMT;
EXECUTE S2;
SET STMT = 'INSERT INTO ' CONCAT TABLE_NAME CONCAT
'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = ?';
PREPARE S3 FROM STMT;
EXECUTE S3 USING P_DEPT;
END;此 CREATE PROCEDURE 语句:
- 将过程命名为 CREATE_DEPT_TABLE
- 定义参数 P_DEPT ，它是输入参数，是长度为 3 的字符数据类型。
- 指示过程是 SQL 过程。
- 定义过程主体。
- 将 SQL 变量 STMT 和 SQL 变量 TABLE_NAME 声明为字符。
- 声明 CONTINUE 处理程序。 该过程尝试在表已存在的情况下将其 DROP。 如果该表不存在，那么第一个 EXECUTE 失败。 使用该处理程序时，将继续处理。
- 将变量 TABLE_NAME 设置为 "DEPT_" ，后跟在参数 P_DEPT 中传递的字符，后跟 "_T"。
- 将变量 STMT 设置为 DROP 语句，并准备和执行该语句。
- 将变量 STMT 设置为 CREATE 语句，并准备和执行该语句。
- 将变量 STMT 设置为 INSERT 语句，并准备和执行该语句。 在 where 子句中指定了参数标记。 执行该语句时，将在 USING 子句上传递变量 P_DEPT。
如果该过程被称为传递部门的值 "D21" ，那么将创建表 DEPT_D21_T ，并使用部门 "D21" 中的所有员工初始化该表。