sql 游标

关于游标的详细信息可参见http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html博文

本文只是针对sql2008下函数使用游标的完整方法的展示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
USE [hbsp]
GO
/****** Object:  UserDefinedFunction [dbo].[Func_GetTeamLeader]    Script Date: 06/06/2014 09:31:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER  function [dbo].[Func_GetTeamLeader]( @SPT_ID char(13)) returns varchar(50)
as
begin
  
 
   declare vLeader cursor for select r.name
from 
       dbo.t_spmember as m,
       dbo.renyzl as r
where 
       r.per_num =m.spm_num
   and m.spt_id=rtrim(@SPT_ID) and m.SPM_ORDER=1 ;
    
 declare @sResult varchar(50)
 declare @sTmp  varchar(50)
 declare @name varchar(50)
 set @sResult =‘‘
  
   
  -- 打开游标
 OPEN vLeader
  
 -- 遍历游标开始
 FETCH NEXT FROM vLeader INTO @name
  
 WHILE @@FETCH_STATUS = 0
  BEGIN
   
 if (rtrim( @name) is not null)
 begin
       set  @sTmp = rtrim(@name);
        
        if (len(rtrim(@sResult + @sTmp)) <= 2000 )
            begin
               if (rtrim( @sResult) is not null )
               BEGIN
               set @sResult = @sResult + @sTmp+‘,‘ ;
               END
              end
   end
    -- 下一个
    FETCH NEXT FROM vLeader INTO @name
  END
 -- 关闭游标
 CLOSE vLeader
 -- 释放游标
 DEALLOCATE vLeader
 if(rtrim( @sResult) is not null)
  begin
  set @sResult=SUBSTRING(@sResult,1,len(@sResult)-1);
  end
  return @sResult
end;

 sql2008下游标中的参数必须全部定义参数的长度,否则会查不出结果

 

oracle 中函数使用游标实例与上面sql的方法相同

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
create or replace function Func_GetTeamLeader(tnum char ) return char
is
cursor vLeader(v char) is 
     select r.name
from 
       t_spmember m,
       renyzl r
where 
       r.per_num =m.spm_num
   and m.spt_id=trim(v) and SPM_ORDER=1 ;
  
rwf vLeader%rowtype;
sResult varchar2(2000);
sTmp    varchar2(2000);
begin
  sResult :=‘‘;
  
  for rwf in vLeader(tnum) loop
     if rtrim( rwf.name) is not null then
        sTmp := rtrim(rwf.name);
        if length(rtrim(sResult || sTmp)) <= 2000 then
           sResult := sResult || sTmp || ‘,‘;
        else
           exit;
        end if;
     end if;
  end loop;
    
  if rtrim( sResult) is not null then
     sResult:= substr( sResult,1, length(sResult)-1);
  end if;
  
  
 return sResult;
end;

 

 

 

sql 游标,布布扣,bubuko.com

sql 游标

上一篇:解决VM虚拟机安装centos7无法联网


下一篇:2021-04-29