很多业务系统将取数逻辑写成一个返回游标的存储过程,这样在业务代码中通过循环遍历游标,即可取出所有的记录。可惜在有些场合,不一定方便写这种循环遍历的代码,那就需要将这种游标转换成视图性质的表状数据集。
如果数据库是SQL Server的话,转换很方便,另写一个存储过程就行了,示例脚本如下:
/*
-- 返回游标的存储过程
alter proc GetCursor1 (@Cursor1 cursor varying output) as
begin
set @Cursor1 = cursor for select * from T1 where ID in (1,2,5)
open @Cursor1
end
*/
create proc sp_2 as
begin
declare @f1 int,@f2 nvarchar(50)
declare @t table(ID int,Name nvarchar(50))
declare @Cursor1 cursor
-- 调用存储过程
exec GetCursor1 @Cursor1 output
fetch next from @Cursor1 into @f1,@f2
while @@FETCH_STATUS=0
begin
insert into @t(ID,Name)Values(@f1,@f2)
fetch next from @Cursor1 into @f1,@f2
end
close @Cursor1
deallocate @Cursor1
select * from @t
end
如果数据库是Oracle,由于Oracle不支持从存储过程返回select的结果,只能靠Oracle函数来解决。
示例脚本如下:
/*
create or replace procedure proc1 (Cursor1 out sys_refcursor) as
begin
open Cursor1 for 'select * from hr.jobs';
end;
*/
/*
create or replace type job_object as object
(
id varchar2(50),
name varchar2(50)
)
*/
/*
create or replace type job_table is table of job_object;
*/
/*
create or replace function f_cursor
return job_table pipelined
is
cur1 SYS_REFCURSOR;
in_rec Jobs%ROWTYPE;
oneJob job_object;
begin
-- call procedure
proc1(Cursor1 => cur1);
loop
fetch cur1 into in_rec;
exit when cur1%notfound;
oneJob := job_object(in_rec.job_id, in_rec.job_title);
pipe row(oneJob);
end loop;
close cur1;
return;
end f_cursor;
*/
-- 调用Oracle函数的查询语句这样写:
select * from table(f_cursor())