1.创建被调用的储存过程,该储存过程返回一结果集:
CREATE
PROCEDURE
call_Proc1()
LANGUAGE SQL
result sets 1
-- ----------------------------------------------------------------------
-- SQL 存储过程
-- ----------------------------------------------------------------------
P1: BEGIN
declare c1 cursor with return to caller for
select SCORE
from T_USER;
open c1;
END P1
LANGUAGE SQL
result sets 1
-- ----------------------------------------------------------------------
-- SQL 存储过程
-- ----------------------------------------------------------------------
P1: BEGIN
declare c1 cursor with return to caller for
select SCORE
from T_USER;
open c1;
END P1
2.测试储存过程:
drop
PROCEDURE
Proc2;
CREATE PROCEDURE Proc2 (
out out_market_code integer
)
LANGUAGE SQL
-- ----------------------------------------------------------------------
-- SQL 存储过程
-- ----------------------------------------------------------------------
P1: BEGIN
-- 建立一个结果集数组
declare loc1,loc2 result_set_locator varying ;
declare i integer ;
declare temp_ch integer ;
-- 调用该SP返回结果集。
call call_Proc1;
-- 将返回结果集和结果集数组关联
associate result set locator(loc1) with procedure call_Proc1;
-- 将结果集数组分配给cursor
allocate cursor1 cursor for result set loc1; set i = 2 ;
-- 取结果集中的数据,这里假定表 T_USER中至少有两条数据;用于调试,可以查看 call_Proc1中返回的结果集中的数据
fetch cursor1 into temp_ch;
WHILE i > 0 DO
set out_market_code = temp_ch;
set i = i - 1 ;
fetch cursor1 into temp_ch;
end while ;
close cursor1;
END P1
CREATE PROCEDURE Proc2 (
out out_market_code integer
)
LANGUAGE SQL
-- ----------------------------------------------------------------------
-- SQL 存储过程
-- ----------------------------------------------------------------------
P1: BEGIN
-- 建立一个结果集数组
declare loc1,loc2 result_set_locator varying ;
declare i integer ;
declare temp_ch integer ;
-- 调用该SP返回结果集。
call call_Proc1;
-- 将返回结果集和结果集数组关联
associate result set locator(loc1) with procedure call_Proc1;
-- 将结果集数组分配给cursor
allocate cursor1 cursor for result set loc1; set i = 2 ;
-- 取结果集中的数据,这里假定表 T_USER中至少有两条数据;用于调试,可以查看 call_Proc1中返回的结果集中的数据
fetch cursor1 into temp_ch;
WHILE i > 0 DO
set out_market_code = temp_ch;
set i = i - 1 ;
fetch cursor1 into temp_ch;
end while ;
close cursor1;
END P1