例10:
--在存储过程中for循环进行遍历分所有直接子分类
create procedure test17(in sid integer,out result varchar(2000))
language sql
begin
declare sname varchar(500);
set result = '';
set sname = '';
--使用for循环查询dtdoctype中的数字,拼接到结果集中
for xunhuan as
select id,parentid,name from dtdoctype where parentid = sid
do
set result = concat(result,strip(DIGITS(id)));
set result = concat(result,strip(DIGITS(parentid)));
set result = concat(result,name);
set result = concat(result,';'),
--将查出来的数据,存入到实体表test_dtdoctype中
sname = 'import:'||name;--name进行处理,将处理的name插入到表test_dtdoctype中
insert into test_dtdoctype(id,parentid,name)values(id,parentid,sname);
END FOR;
end;
---------------------------------------------------------
--在存储过程中for循环进行遍历分所有直接子分类
create procedure test18(in sid integer,out result varchar(2000))
language sql
begin
declare snum integer;
declare sname varchar(500);
--建立一个临时表
declare global temporary table session.testdtdoctype
(
id integer,
parentid integer,
name varchar(500)
);
set result = '';
set sname = '';
set snum = 0;
--使用for循环查询dtdoctype中的数字,拼接到结果集中
for xunhuan as
select id,parentid,name from dtdoctype where parentid = sid
do
--将查出来的数据,存入到实体表test_dtdoctype中
set sname = 'import:'||name;--name进行处理,将处理的name插入到表test_dtdoctype中
insert into session.testdtdoctype(id,parentid,name)values(id,parentid,sname);
END FOR;
--查询临时表中的数据
select count(*) into snum from session.testdtdoctype;
set result = concat(result,strip(DIGITS(snum)));
set result = concat(result,'条数据');
drop table session.testdtdoctype;
end;