我们知道,代码流程控制中,最常见的三种逻辑结构是顺序、选择、循环。
顺序就不用说了,我们重点看一下SQL PL中的选择和循环,以及其它流程控制语句。
环境- 操作系统:Ubuntu 20.04
- Db2:11.5.0.0
if 语句的逻辑结构为:
if ...... then ...... elseif ...... then ...... else ...... end if
其中, elseif 和 else 是可选的。
注意: elseif 是一个关键字,不要分开。
casecase 语句有两种用法:
- case 处一个值, when 处一个值,判断二者是否相等:
case ...... when ...... then ...... when ...... then ...... else ...... end case
- case 处为空, when 处为一个判断,看其是否为true:
case when ...... then ...... when ...... then ...... else ...... end case
这两种case的用法其实跟SQL语句里的case非常相似,只不过SQL里的case是表达式,这里的case是语句。
循环 for创建文件 test1.sql 如下:
set serveroutput on@ begin for v as cur1 cursor for select firstnme, lastname from employee do call dbms_output.put_line(firstnme || ', ' || lastname); end for; end@ set serveroutput off@
运行脚本 test1.sql ,如下:
➜ temp0530 db2 -td@ -f test1.sql DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. CHRISTINE, HAAS MICHAEL, THOMPSON SALLY, KWAN JOHN, GEYER IRVING, STERN EVA, PULASKI EILEEN, HENDERSON THEODORE, SPENSER VINCENZO, LUCCHESSI SEAN, O'CONNELL DELORES, QUINTANA HEATHER, NICHOLLS BRUCE, ADAMSON ELIZABETH, PIANKA MASATOSHI, YOSHIMURA MARILYN, SCOUTTEN JAMES, WALKER DAVID, BROWN WILLIAM, JONES JENNIFER, LUTZ JAMES, JEFFERSON SALVATORE, MARINO DANIEL, SMITH SYBIL, JOHNSON MARIA, PEREZ ETHEL, SCHNEIDER JOHN, PARKER PHILIP, SMITH MAUDE, SETRIGHT RAMLAL, MEHTA WING, LEE JASON, GOUNOT DIAN, HEMMINGER GREG, ORLANDO KIM, NATZ KIYOSHI, YAMAMOTO REBA, JOHN ROBERT, MONTEVERDE EILEEN, SCHWARTZ MICHELLE, SPRINGER HELENA, WONG ROY, ALONZO DB20000I The SET SERVEROUTPUT command completed successfully.loop
loop 语句通常与 leave 、 goto 、 iterate 、 return 一起使用。
创建文件 test2.sql 如下:
set serveroutput on@ begin declare v_deptno char(3); declare v_deptname varchar(29); declare at_end int default 0; declare not_found condition for sqlstate '02000'; declare c1 cursor for select deptno, deptname from department order by deptno; declare continue handler for not_found set at_end = 1; open c1; myloop: loop fetch c1 into v_deptno, v_deptname; if at_end = 1 then leave myloop; elseif v_deptno = 'D11' then iterate myloop; end if; call dbms_output.put_line(v_deptno || ', ' || v_deptname); end loop; close c1; end@ set serveroutput off@
运行脚本 test2.sql ,如下:
➜ temp0530 db2 -td@ -f test2.sql DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. A00, SPIFFY COMPUTER SERVICE DIV. B01, PLANNING C01, INFORMATION CENTER D01, DEVELOPMENT CENTER D21, ADMINISTRATION SYSTEMS E01, SUPPORT SERVICES E11, OPERATIONS E21, SOFTWARE SUPPORT F22, BRANCH OFFICE F2 G22, BRANCH OFFICE G2 H22, BRANCH OFFICE H2 I22, BRANCH OFFICE I2 J22, BRANCH OFFICE J2 DB20000I The SET SERVEROUTPUT command completed successfully.
由于 loop 循环没有结束条件,这种循环的做法似乎比较麻烦。
whilewhile (......) do ...... end while
具体参见我另一篇文档(https://blog.csdn.net/duke_ding2/article/details/125012716)里数组元素求和的例子。
repeatrepeat 循环与 while 循环的区别在于,前者是先做再判断,后者是先判断再做,所以 repeat 循环至少会迭代一次。
repeat ...... until (......) end repeat其它流程控制语句 goto
臭名昭著的 goto 语句,一般我们都不会再用,不再赘述。
iterate 和 leave类似Java里面的 continue 和 break ,前面有例子,不再赘述。
return直接返回,与其它语言如Java里的 return 类似,可以直接在循环内部返回。
创建文件 test8.sql 如下:
set serveroutput on@
begin
declare n, i int;
set n = 10;
set i = 1;
while (i <= n) do
if (i < 5) then
call dbms_output.put_line('hi, ' || i);
elseif (i < 7) then
return;
else
call dbms_output.put_line('OK, ' || i);
end if;
set i = i + 1;
end while;
end@
set serveroutput off@
运行脚本 test8.sql ,如下:
➜ temp0530 db2 -td@ -f test8.sql DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. hi, 1 hi, 2 hi, 3 hi, 4 DB20000I The SET SERVEROUTPUT command completed successfully.条件处理
有点类似于其它语言如Java中的异常处理。
当程序出现sqlexceptin、sqlwarning、not found时,如果有对应的handler,就会运行handler的代码逻辑。参见上面 loop 循环的例子。
此外,还可以声明变量 sqlcode 和 sqlstate ,当运行SQL PL语句时,Db2会自动把 sqlcode 和 sqlstate 赋值。
创建文件 test3.sql 如下:
call sysproc.admin_cmd('reorg table xxx')@
假设表 xxx 不存在,则运行脚本 test3.sql ,如下:
➜ temp0530 db2 -td@ -f test3.sql SQL2211N The specified table does not exist. SQLSTATE=01H52
可见,对于reorg操作,如果表不存在,则sqlcode是 -2211 ,sqlstate是 01H52 。
创建文件 test4.sql 如下:
set serveroutput on@
begin
declare sqlcode int default 0;
declare sqlstate char(5 octets) default '00000';
call sysproc.admin_cmd('reorg table xxx');
call dbms_output.put_line('sqlcode = ' || sqlcode || ', sqlstate = ' || sqlstate);
call dbms_output.put_line('sqlcode = ' || sqlcode || ', sqlstate = ' || sqlstate);
end@
set serveroutput off@
假设表 xxx 不存在,则运行脚本 test4.sql ,如下:
➜ temp0530 db2 -td@ -f test4.sql DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. sqlcode = -2211, sqlstate = 01H52 sqlcode = 0, sqlstate = 00000 DB20000I The SET SERVEROUTPUT command completed successfully.
可见,Db2自动给变量 sqlcode 和 sqlstate 赋值了。
注意:
- 这两个变量名字是固定的,不能改变;
- 一旦访问了这两个变量,Db2就会隐式的将其值重置(所以最好把其值赋给其它变量,以便使用);
创建文件 test5.sql 如下:
set serveroutput on@
begin
declare sqlcode int default 0;
declare sqlstate char(5 octets) default '00000';
declare not_found condition for sqlstate '01H52';
declare continue handler for not_found
call dbms_output.put_line('not found. sqlcode = ' || sqlcode || ', sqlstate = ' || sqlstate);
call sysproc.admin_cmd('reorg table xxx');
call dbms_output.put_line('sqlcode = ' || sqlcode || ', sqlstate = ' || sqlstate);
call dbms_output.put_line('sqlcode = ' || sqlcode || ', sqlstate = ' || sqlstate);
end@
set serveroutput off@
假设表 xxx 不存在,则运行脚本 test5.sql ,如下:
➜ temp0530 db2 -td@ -f test5.sql DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. not found. sqlcode = -2211, sqlstate = 01H52 sqlcode = 0, sqlstate = 00000 sqlcode = 0, sqlstate = 00000 DB20000I The SET SERVEROUTPUT command completed successfully.
可见,如果声明了合适的条件处理,当满足条件时(本例中sqlstate为 01H52 ),就会运行相应的handler处理逻辑。
参考- https://www.ibm.com/docs/en/db2/11.5?topic=data-developing-routines
- https://www.ibm.com/docs/zh/db2/11.5?topic=data-developing-routines (中文版)



