注：如果不熟悉Db2存储过程的基本概念和HelloWorld例子，请参考我另一篇文档（ https://blog.csdn.net/duke_ding2/article/details/124873609 ）。
环境
- 操作系统：Ubuntu 20.04
- Db2：11.5.0.0
SQL PL简介
SQL过程语言（SQL Procedural Language）是SQL的语言扩展，由“可以在SQL语句中实现过程逻辑的语句和语言元素”组成，比如声明变量、条件判断、变量赋值等。
SQL PL主要用于SQL存储过程。在SQL存储过程中可使用SQL PL全集。
先来看一个简单例子。创建文件 test3.sql 如下：
create or replace procedure test3(in score int)
language sql
begin
if score < 60 then
call dbms_output.put_line('Fail');
elseif score >= 60 and score < 80 then
call dbms_output.put_line('Pass');
else
call dbms_output.put_line('Outstanding');
end if;
end@
set serveroutput on@
call test3(70)@
set serveroutput off@
运行脚本，如下：
➜ temp0523 db2 -td@ -f test3.sql
DB20000I The SQL command completed successfully.
DB20000I The SET SERVEROUTPUT command completed successfully.
Return Status = 0
Pass
DB20000I The SET SERVEROUTPUT command completed successfully.
本例中，使用了条件判断，根据不同的 score 值，输出相应的结果。
Inline SQL PL
Inline SQL PL是SQL PL的子集，用于inline复合语句。
复合语句
所谓复合语句，说白了就是把多个语句用 begin/end （或者 begin atomic/end ）包起来。
复合语句又可分为两种：
- Inline复合语句：用
begin atomic/end包起来，顾名思义，它是原子的。可以包含inline SQL PL（前面提到，是SQL PL的子集）； - Compiled复合语句：用
begin/end包起来，非原子性。可以包含所有的SQL PL；
复合语句可以单独运行，也可以包含在数据库对象定义（比如存储过程、函数）中。
创建文件 test1.sql 如下：
begin
declare a, b varchar(100);
set a = (select * from sysibm.sysdummy1);
select * into a from sysibm.sysdummy1;
end@
运行脚本，如下：
➜ temp0523 db2 -td@ -f test1.sql
DB20000I The SQL command completed successfully.
创建文件 test2.sql 如下：
begin atomic
declare a, b varchar(100);
set a = (select * from sysibm.sysdummy1);
-- select * into a from sysibm.sysdummy1;
end@
运行脚本，如下：
➜ temp0523 db2 -td@ -f test2.sql
DB20000I The SQL command completed successfully.
注意本例中被注释的代码，它在inline复合语句中会报错，这难道就是因为前面所提到的，inline SQL PL是SQL PL的子集，所以有些功能不支持？
SQL例程
所谓SQL例程，就是只包含SQL语句和SQL PL语句的例程。和例程的分类相似，SQL例程可分为SQL存储过程、SQL函数、SQL方法。本文中只涉及SQL存储过程。
关于创建SQL例程的语法，请参考 https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-procedure-sql 。
该文档中有一个具体例子：
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END
我们从该例子看一下SQL存储过程的大致结构：
CREATE PROCEDURE MEDIAN_RESULT_SET：我一般会用create or replace，免得多次运行时报错；(OUT medianSalary DOUBLE)：参数列表，本例中只有一个out参数；RESULT SETS 1：可选项，表示返回多少个result set，默认是0；LANGUAGE SQL：可选项，表示SQL存储过程，是默认选项；BEGIN ... END：SQL存储过程的最核心就是一个复合语句。前面提到，复合语句是用begin/end（或者begin atomic/end）所包含的多条语句。
注：本例中只用到了2个选项，其它还有很多选项，具体用法请参考官方文档。
如果连接的是 sample 数据库，则 staff 表和里面的数据都已经准备好了，可以直接运行该存储过程，看一下效果。
新建 test4.sql 文件，对上面内容稍作修改：
- 加上
OR REPLACE，以免多次运行时报错； - 加上
call MEDIAN_RESULT_SET(?)，调用该存储过程； - 加上
@结束符；
如下：
CREATE OR REPLACE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END@
call MEDIAN_RESULT_SET(?)@
运行 test4.sql 脚本，如下：
➜ temp0523 db2 -td@ -f test4.sql
DB20000I The SQL command completed successfully.
Value of output parameters
--------------------------
Parameter Name : MEDIANSALARY
Parameter Value : +7.68582000000000E+004
Result set 1
--------------
NAME JOB 3
--------- ----- -----------
Marenghi Mgr 77506
O'Brien Sales 78006
Pernal Sales 78171
Plotz Mgr 78352
Wilson Sales 78674
Daniels Mgr 79260
Williams Sales 79456
Hanes Mgr 80659
Jones Mgr 81234
Molinare Mgr 82959
Lundquist Clerk 83369
Smith Sales 87654
Lea Mgr 88555
Quill Mgr 89818
Lu Mgr 90010
Fraye Mgr 91150
Sanders Mgr 98357
17 record(s) selected.
Return Status = 0
可见：
- 通过out参数
medianSalary，输出了中位数工资的值：+7.68582000000000E+004（staff表中共有35条记录，按工资排序的第18条记录，就是中位数工资）； - 通过游标
c2，输出了所有大于中位数工资的记录，包括姓名、工作、工资；
下面我们重点来看SQL存储过程的主体，也就是 begin/end （或者 begin atomic/end ）所包含的部分。
在主体中，又可包含多个复合语句。其格式如下：
label: BEGIN
Variable declarations
Condition declarations
Cursor declarations
Condition handler declarations
Assignment, flow of control, SQL statements and other compound statements
END label
官网一个具体例子（ https://www.ibm.com/docs/en/db2/11.5?topic=procedures-structure-sql ）：
CREATE PROCEDURE DEL_INV_FOR_PROD (IN prod INT, OUT err_buffer VARCHAR(128))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE integer DEFAULT 0;
DECLARE NO_TABLE CONDITION FOR SQLSTATE '42704';
DECLARE cur1 CURSOR WITH RETURN TO CALLER
FOR SELECT * FROM Inv;
A: BEGIN ATOMIC
DECLARE EXIT HANDLER FOR NO_TABLE
BEGIN
SET ERR_BUFFER='Table Inv does not exist';
END;
SET err_buffer = '';
IF (prod < 200)
DELETE FROM Inv WHERE product = prod;
ELSE IF (prod < 400)
UPDATE Inv SET quantity = 0 WHERE product = prod;
ELSE
UPDATE Inv SET quantity = NULL WHERE product = prod;
END IF;
END A;
B: OPEN cur1;
END
但是该例子貌似有语法错误…… if 后面不需要 then 吗？
atomic和not atomic
首先创建表 C1_SCHED ：
create table C1_SCHED(class_code varchar(100), day int)
注：不知道为啥，文档中这几个例子并没有对应sample DB的表，只好自己创建了。
创建 test6.sql 文件如下：
CREATE or replace PROCEDURE not_atomic_proc ()
LANGUAGE SQL
SPECIFIC not_atomic_proc
nap: BEGIN NOT ATOMIC
INSERT INTO c1_sched (class_code, day)
VALUES ('R11:TAA', 1);
commit;
SIGNAL SQLSTATE '70000';
INSERT INTO c1_sched (class_code, day)
VALUES ('R22:TBB', 1);
commit;
END nap@
call not_atomic_proc@
本例中， signal 用于触发错误，我理解有点类似于Java中的 throw 。
运行 test6.sql 脚本文件，如下：
➜ temp0523 db2 -td@ -f test6.sql
DB20000I The SQL command completed successfully.
SQL0438N Application raised error or warning with diagnostic text: "".
SQLSTATE=70000
➜ temp0523 echo $?
4
本例中，两个insert语句之间出现了错误，由于是 not atomic ，第一个insert语句并不会受影响，因为在出现错误之前就已经显式commit了。所以，虽然存储过程整体运行失败，但第一个insert语句已经成功了。
注意：如果去掉commit，则出现错误时，虽然第一个insert语句并不会立刻回滚，但是因为没有错误处理，所以最终存储过程失败后，第一个insert语句也会回滚。
创建 test7.sql 文件如下：
CREATE or replace PROCEDURE atomic_proc ()
LANGUAGE SQL
SPECIFIC atomic_proc
ap: BEGIN ATOMIC
INSERT INTO c1_sched (class_code, day)
VALUES ('R33:TCC', 1);
--commit;
SIGNAL SQLSTATE '70000';
INSERT INTO c1_sched (class_code, day)
VALUES ('R44:TDD', 1);
-- commit;
END ap@
call atomic_proc@
运行 test7.sql 脚本文件，如下：
➜ temp0523 db2 -td@ -f test7.sql
DB20000I The SQL command completed successfully.
SQL0438N Application raised error or warning with diagnostic text: "".
SQLSTATE=70000
由于是 atomic ，所以出现错误时，第一个insert语句会立即回滚。
注意：在inline复合语句中不允许使用 SAVEPOINT 、 COMMIT 、 ROLLBACK 等事务控制语句。
Db2自带的示例
请参考IBM官网 https://www.ibm.com/docs/en/db2/11.5?topic=samples-sample-files 。
在安装Db2时，在安装目录下（比如 /opt/ibm/db2/V11.5/ ）有一个 samples 目录，里面有很多示例。其中有一个 sqlpl 目录，里面是SQL PL的示例。
这些示例可以运行在 sample 数据库上。如果安装时没有创建 sample 数据库，可以随时用 db2sampl 命令来创建。
例如 loop.db2 文件：
-- skip lots of comment here
CREATE PROCEDURE loop_until_space(OUT counter INT)
LANGUAGE SQL
BEGIN
DECLARE v_firstnme VARCHAR(12);
DECLARE v_midinit CHAR(1);
DECLARE v_lastname VARCHAR(15);
DECLARE v_counter SMALLINT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT firstnme, midinit, lastname
FROM employee
ORDER BY midinit DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET counter = -1;
-- initialize OUT parameter
SET counter = 0;
OPEN c1;
fetch_loop:
LOOP
FETCH c1 INTO
v_firstnme, v_midinit, v_lastname;
-- Use a local variable for the iterator variable
-- because SQL procedures only allow you to assign
-- values to an OUT parameter
SET v_counter = v_counter + 1;
IF v_midinit = ' ' THEN
LEAVE fetch_loop;
END IF;
END LOOP fetch_loop;
CLOSE c1;
-- Now assign the value of the local
-- variable to the OUT parameter
SET counter = v_counter;
END @
我们来运行一下 loop.db2 脚本：
➜ sqlpl db2 -td@ -f loop.db2
DB20000I The SQL command completed successfully.
➜ sqlpl db2 "call loop_until_space(?)"
Value of output parameters
--------------------------
Parameter Name : COUNTER
Parameter Value : 36
Return Status = 0
注：示例代码中都是 create procedure 而不是 create or replace procedure ，所以只能运行一次……不太明白为何不用后者呢。
扫一扫
577
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
