sql server
在函数中使用游标 获取子表中的 数据
create FUNCTION [dbo].[wuping]
(
@userID varchar(50)
)
RETURNS varchar(max)
AS
BEGIN
declare @aa varchar(max),@bb varchar(max)
set @aa=''
set @bb=''
if not exists(select 1 from wuping_detail where subcolumn = @userID)
return ''
declare @c cursor
set @c = cursor for select shebei_name+' '+ shebei_num+' ' + shebei_num+' '+unit from wuping_detail where subcolumn=@userID
open @c
fetch next from @c into @bb
while @@fetch_status=0
begin
if @aa = ''
begin
set @aa=@bb
end
else
begin
set @aa = @aa + '
'+@bb
end
fetch next from @c into @bb
end
close @c
return @aa
END
oracle
-- Created on 2011-3-22 by ADMINISTRATOR declare
-- Local variables here i integer;
aresult varchar2(4000):='';
cursor cur_xiaoqu is
SELECT tablename FROM total p ;
people cur_xiaoqu%ROWTYPE;
begin
open cur_xiaoqu;
loop
fetch cur_xiaoqu into PEOPLE;
exit when cur_xiaoqu%notfound;
aresult := 'insert into aaa select '''|| PEOPLE.tablename ||''' ,count(*) from '|| PEOPLE.tablename ;
EXECUTE IMMEDIATE aresult ;
dbms_output.put_line(aresult);
end loop;
close cur_xiaoqu;
commit;
end;