我使用SQLServer 2005,我想建立一个表值函数,
表值函数的作用是统计飞机航段对 及 其频率
其中航段对:北京-广州 与 广州-北京 属于同一个航段对。(起飞-降落)
所以,我想将算出频率后,将其中一个数据剔除。
CREATE FUNCTION getAirportCoupleYearStat
(
-- Add the parameters for the function here
@registerdept varchar(20),
@year varchar(4)
)
RETURNS
@temp_tbl_airportCouple TABLE
(
-- Add the column definitions for the TABLE variable here
takeoffID varchar(50),
arrivedID varchar(50),
airportCouple varchar(100),
num int
)
AS
BEGIN
declare @outTakeoffID varchar(50)
set @outTakeoffID = ''
declare @outArrivedID varchar(50)
set @outArrivedID = ''
declare @outAirportCouple varchar(100)
set @outAirportCouple = ''
declare @outNum int
set @outNum = 0
declare @matchNum int
set @matchNum = 0
--创建临时结果集(这是已经统计出各航段的内容,频率,但是还没有合并的结果集,
--即存在 北京-广州 5 ; 广州-北京 4; 等类似记录)
--后续的工作是希望往表插数据 : 北京-广州 9,并将 广州-北京 4 这条记录删除
with temple_airportCoupleYear(takeoffID, arrivedID, airportCouple,num)
as
(
select takeoffID, arrivedID, airportCouple,num
from view_airportCoupleYear
where registerDept = @registerdept and dateYear = @year
)
/*
insert @temp_tbl_airportCouple select temple_airportCoupleYear
*/ --为结果集创建游标 ::: 但是在这里出现系统报错
declare outCur cursor
for select takeoffID, arrivedID, airportCouple,num
from temple_airportCoupleYear
--循环结果集,把数据放入要返回的表中
open outCur
fetch Next from outCur
into @outTakeoffID,@outArrivedID,@outAirportCouple,@outNum
insert into @temp_tbl_airportCouple values
(@outTakeoffID,@outArrivedID,@outAirportCouple,@outNum)
while @@Fetch_status=0
begin
--1.存储匹配城市对的航段的数量
select @matchNum=num
from temple_airportCoupleYear
where takeoffID = @outArrivedID and arrivedID = @outTakeoffID
--2.将匹配城市对的数量相加,并将一条数据加入到返回的表中
set @outNum = @outNum + isnull(matchNum,0)
insert into @temp_tbl_airportCouple
values(@outTakeoffID,@outArrivedID,@outAirportCouple,@outNum)
--3.为避免重复,将匹配的信息剔除
delete from temple_airportCoupleYear
where takeoffID=@outArrivedID and arrivedID=@outTakeoffID
--取下一条数据
fetch Next from outCur
into @outTakeoffID,@outArrivedID,@outAirportCouple,@outNum
end
close outCur
DEALLOCATE outCur
return
END
GO执行该表值函数, 系统提示
消息 156,级别 15,状态 1,过程 getAirportCoupleYearStat,第 45 行
关键字 'declare' 附近有语法错误。请问各位高手,这是什么原因呢?
游标不能使用在临时结果集吗?但是看到一篇文章,里面有内容如下:
DECLARE favCounts CURSOR LOCAL SCROLL FOR
select substring(p_start_time,1,10) as strRq_s_tmp from wcdj where sp_end='完成'
他的语句在存储过程中。
请问各位大侠,有何不同啊?
谢谢
表值函数的作用是统计飞机航段对 及 其频率
其中航段对:北京-广州 与 广州-北京 属于同一个航段对。(起飞-降落)
所以,我想将算出频率后,将其中一个数据剔除。
CREATE FUNCTION getAirportCoupleYearStat
(
-- Add the parameters for the function here
@registerdept varchar(20),
@year varchar(4)
)
RETURNS
@temp_tbl_airportCouple TABLE
(
-- Add the column definitions for the TABLE variable here
takeoffID varchar(50),
arrivedID varchar(50),
airportCouple varchar(100),
num int
)
AS
BEGIN
declare @outTakeoffID varchar(50)
set @outTakeoffID = ''
declare @outArrivedID varchar(50)
set @outArrivedID = ''
declare @outAirportCouple varchar(100)
set @outAirportCouple = ''
declare @outNum int
set @outNum = 0
declare @matchNum int
set @matchNum = 0
--创建临时结果集(这是已经统计出各航段的内容,频率,但是还没有合并的结果集,
--即存在 北京-广州 5 ; 广州-北京 4; 等类似记录)
--后续的工作是希望往表插数据 : 北京-广州 9,并将 广州-北京 4 这条记录删除
with temple_airportCoupleYear(takeoffID, arrivedID, airportCouple,num)
as
(
select takeoffID, arrivedID, airportCouple,num
from view_airportCoupleYear
where registerDept = @registerdept and dateYear = @year
)
/*
insert @temp_tbl_airportCouple select temple_airportCoupleYear
*/ --为结果集创建游标 ::: 但是在这里出现系统报错
declare outCur cursor
for select takeoffID, arrivedID, airportCouple,num
from temple_airportCoupleYear
--循环结果集,把数据放入要返回的表中
open outCur
fetch Next from outCur
into @outTakeoffID,@outArrivedID,@outAirportCouple,@outNum
insert into @temp_tbl_airportCouple values
(@outTakeoffID,@outArrivedID,@outAirportCouple,@outNum)
while @@Fetch_status=0
begin
--1.存储匹配城市对的航段的数量
select @matchNum=num
from temple_airportCoupleYear
where takeoffID = @outArrivedID and arrivedID = @outTakeoffID
--2.将匹配城市对的数量相加,并将一条数据加入到返回的表中
set @outNum = @outNum + isnull(matchNum,0)
insert into @temp_tbl_airportCouple
values(@outTakeoffID,@outArrivedID,@outAirportCouple,@outNum)
--3.为避免重复,将匹配的信息剔除
delete from temple_airportCoupleYear
where takeoffID=@outArrivedID and arrivedID=@outTakeoffID
--取下一条数据
fetch Next from outCur
into @outTakeoffID,@outArrivedID,@outAirportCouple,@outNum
end
close outCur
DEALLOCATE outCur
return
END
GO执行该表值函数, 系统提示
消息 156,级别 15,状态 1,过程 getAirportCoupleYearStat,第 45 行
关键字 'declare' 附近有语法错误。请问各位高手,这是什么原因呢?
游标不能使用在临时结果集吗?但是看到一篇文章,里面有内容如下:
DECLARE favCounts CURSOR LOCAL SCROLL FOR
select substring(p_start_time,1,10) as strRq_s_tmp from wcdj where sp_end='完成'
他的语句在存储过程中。
请问各位大侠,有何不同啊?
谢谢
这里错误因为CTE定义好厚
下面第一句就要执行它 就是要用到temple_airportCoupleYear
但是你是后面第二句才用到
把 declare outCur cursor 放到下面这句之前with temple_airportCoupleYear(takeoffID, arrivedID, airportCouple,num)
as
(
select takeoffID, arrivedID, airportCouple,num
from view_airportCoupleYear
where registerDept = @registerdept and dateYear = @year
)
select takeoffID, arrivedID, airportCouple,num
into #temple_airportCoupleYear
from view_airportCoupleYear
where registerDept = @registerdept and dateYear = @year /*
insert @temp_tbl_airportCouple select temple_airportCoupleYear
*/ --为结果集创建游标 ::: 但是在这里出现系统报错
declare outCur cursor
for select takeoffID, arrivedID, airportCouple,num
from #temple_airportCoupleYear
--循环结果集,把数据放入要返回的表中
open outCur
fetch Next from outCur
into @outTakeoffID,@outArrivedID,@outAirportCouple,@outNum
insert into @temp_tbl_airportCouple values
(@outTakeoffID,@outArrivedID,@outAirportCouple,@outNum)
while @@Fetch_status=0
begin
--1.存储匹配城市对的航段的数量
select @matchNum=num
from temple_airportCoupleYear
where takeoffID = @outArrivedID and arrivedID = @outTakeoffID
--2.将匹配城市对的数量相加,并将一条数据加入到返回的表中
set @outNum = @outNum + isnull(matchNum,0)
insert into @temp_tbl_airportCouple
values(@outTakeoffID,@outArrivedID,@outAirportCouple,@outNum)
--3.为避免重复,将匹配的信息剔除
delete from temple_airportCoupleYear
where takeoffID=@outArrivedID and arrivedID=@outTakeoffID --取下一条数据
fetch Next from outCur
into @outTakeoffID,@outArrivedID,@outAirportCouple,@outNum
end
close outCur
DEALLOCATE outCur
return
END
GO
我在创建临时结果集后,立刻加了这一语句,
insert @temp_tbl_airportCouple select * from temple_airportCoupleYear是成功了。
但是,我就是不希望它立刻往要返回的表里插数据,而是在游标的循环中。我现在尝试改临时结果集为临时表。。
谢谢大侠。
代码如下:
alter FUNCTION getAirportCoupleYearStat
(
@deptID varchar(50),
@year varchar(4)
)
RETURNS @temp_tbl_airportCouple TABLE
(
-- Add the column definitions for the TABLE variable here
takeoffID varchar(50),
arrivedID varchar(50),
airportCouple varchar(100),
num int
)
AS
BEGIN
declare @outTakeoffID varchar(50)
set @outTakeoffID = ''
declare @outArrivedID varchar(50)
set @outArrivedID = ''
declare @outAirportCouple varchar(100)
set @outAirportCouple = ''
declare @outNum int
set @outNum = 0
declare @matchNum int
set @matchNum = 0
--因为:无法从函数内访问临时表
--所以创建表表变量
declare @myTabVar table
(
takeoffID varchar(50),
arrivedID varchar(50),
airportCouple varchar(100),
num int
) insert @myTabVar
select takeoffID, arrivedID, airportCouple,num
from view_airportCoupleYear
where registerDept = @deptID and dateYear = @year
--为表变量创建游标
declare outCur cursor
for select takeoffID, arrivedID, airportCouple,num
from @myTabVar --循环结果集,把数据放入要返回的表中
open outCur
fetch Next from outCur
into @outTakeoffID,@outArrivedID,@outAirportCouple,@outNum
while @@Fetch_status=0
begin
--1.存储匹配城市对的航段的数量
select @matchNum=num
from @myTabVar
where takeoffID = @outArrivedID and arrivedID = @outTakeoffID
--2.将匹配城市对的数量相加,并将一条数据加入到返回的表中
set @outNum = @outNum + isnull(@matchNum,0)
insert into @temp_tbl_airportCouple
values(@outTakeoffID,@outArrivedID,@outAirportCouple,@outNum)
--3.为避免重复,将匹配的信息剔除
delete from @myTabVar
where takeoffID=@outArrivedID and arrivedID=@outTakeoffID
--4.清零
set @outNum = 0
set @matchNum = 0
--取下一条数据
fetch Next from outCur
into @outTakeoffID,@outArrivedID,@outAirportCouple,@outNum
end
close outCur
DEALLOCATE outCur
/*insert @temp_tbl_airportCouple
select takeoffID, arrivedID, airportCouple,num
from @myTabVar*/
return
END