关于游标的详细信息可参见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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER function [dbo].[Func_GetTeamLeader]( @SPT_ID char(13)) returns varchar(50)
asbegin 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
iscursor 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; |