DB2存储过程流程控制
条件语句if语句：
if 条件 then
执行语句;
else
执行语句;
end if;
如下面的存储过程:
create procedure Test(
OUT v_message varchar(100)
)
LANGUAGE SQL
BEGIN
declare v_count int;
set v_count = 1;
if v_count = 0 then
set v_message = '0';
else
set v_message = '1';
end if;
END@
执行后输出参数v_message的值为1
多个if-else的语句结构
if 条件 then
执行语句;
elseif 条件 then
执行语句;
elseif 条件 then
执行语句;
end if;
case语句
case
when 条件 then
执行语句;
when 条件 then
执行语句;
else
执行语句;
end case;
如下存储过程:
create procedure Test(
OUT v_message varchar(100)
)
LANGUAGE SQL
BEGIN
declare v_count int;
set v_count = 1;
case
when v_count = 0 then
set v_message = '0';
when v_count = 1 then
set v_message = '1';
when v_count = 2 then
set v_message = '2';
else
set v_message = '-1';
end case;
END@
循环语句
LOOP 循环 -- 简单的循环
L1: LOOP
SQL statements;
LEAVE L1; --跳出循环
END LOOP L1;
WHILE 循环 -- 进入前检查条件
WHILE condition
DO
SQL statements
END WHILE;
REPEAT 循环 -- 退出前检查条件
REPEAT
SQL statements;
UNTIL condition
END REPEAT;
FOR 循环 -- 结果集上的隐式循环
FOR loop_name AS
SELECT … FROM
DO
SQL statements;
END FOR;
请注意，FOR 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。
loop循环例子
create procedure Test(
OUT sum int
)
LANGUAGE SQL
BEGIN
declare v_count int;
set v_count = 0;
set sum = 0;
L1: LOOP
set v_count = v_count + 1;
set sum = sum + v_count;
if v_count >= 10 then
LEAVE L1; --跳出循环
end if;
END LOOP L1;
END@
while循环例子
create procedure Test(
OUT sum int
)
LANGUAGE SQL
BEGIN
declare v_count int;
set sum = 0;
set v_count = 0;
WHILE v_count < 10
DO
set v_count = v_count + 1;
set sum = sum + v_count;
END WHILE;
END@
REPEAT 循环例子
create procedure Test(
OUT sum int
)
LANGUAGE SQL
BEGIN
declare v_count int;
set sum = 0;
set v_count = 0;
REPEAT
set v_count = v_count + 1;
set sum = sum + v_count;
UNTIL v_count >= 10
END REPEAT;
END@
for循环有些特别，它是迭代结果集的隐式循环。下面举一个实际的例子。
需求：一张实时表，一个人每到一个地方，就有一条记录存在，存放样例：
张三 上海
张三 杭州
.....
要求，按人名进行汇总，将他到过的地方拼接，中间用'#'分隔
数据准备：
--创建表：
CREATE TABLE Recursive_Test
(User_Name VARCHAR(12),
City VARCHAR(12));
--数据插入
INSERT INTO Recursive_Test(User_Name,City)
values ('张三','杭州'),('张三','郑州'),('李四','杭州'),
('张三','南昌'),('李四','广州'),('王五','北京');
存储过程实现
CREATE PROCEDURE
test_con
(
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_name varchar(12);
DECLARE v_str varchar(50);
DECLARE v_city varchar(200);
DECLARE GLOBAL TEMPORARY TABLE Result
(
User_Name VARCHAR(12),
city VARCHAR(200)
)ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;
--双重 for循环实现字符串分组连接 --双重游标实现分组字符串连接
FOR V1 AS CURSOR1 CURSOR FOR select distinct user_name as v_name from Recursive_Test
DO
set v_city = '';
FOR V2 AS CURSOR2 CURSOR FOR select city||' ' as v_str
from Recursive_Test where user_name = v_name
DO
set v_city = v_city||v_str;
END FOR;
insert into session.Result values( v_name,v_city);
END FOR;
BEGIN
DECLARE v_ret CURSOR WITH HOLD WITH RETURN TO CALLER FOR
select * from SESSION.result;
OPEN v_ret;
END;
RETURN v_errCode;
END
页:
[1]