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;