我们知道,代码流程控制中,最常见的三种逻辑结构是顺序、选择、循环。
顺序就不用说了,我们重点看一下SQL PL中的选择和循环,以及其它流程控制语句。
环境
- 操作系统:Ubuntu 20.04
- Db2:11.5.0.0
选择
if
if
语句的逻辑结构为:
if ...... then
......
elseif ...... then
......
else
......
end if
其中, elseif
和 else
是可选的。
注意: elseif
是一个关键字,不要分开。
case
case
语句有两种用法:
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
循环没有结束条件,这种循环的做法似乎比较麻烦。
while
while (......) do
......
end while
具体参见我另一篇文档(https://blog.csdn.net/duke_ding2/article/details/125012716)里数组元素求和的例子。
repeat
repeat
循环与 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处理逻辑。