有关游标的问题

Question

--定义游标
declare 游标名 cursor for select name from sysobjects--打开游标
open 游标名--读取
fetch next from 游标名 into 变量名--判断读取是否成功
while @@fetch_status=0
begin
  --循环处理,
  --你的处理语句  --读取下一条
  fetch next from 游标名 into 变量名
end--关闭游标
close 游标名--释放游标资源(   楼主写这句了吗?    )
deallocate 游标名

Answer 1

不会的，一定是你的T-SQL有缺陷，你将SOURCE CODE贴出来看看。

Answer 2

CREATE procedure p_Test AS if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#tmpRibao]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#tmpRibao]declare @cardate varchar(255)create table #tmpRibao(
carway char(20),
[1hour] float,
[2hour] float,
[3hour] float,
[4hour] float,
[5hour] float,
[6hour] float,
[7hour] float,
[8hour] float,
[9hour] float,
[10hour] float,
[11hour] float,
[12hour] float,
[13hour] float,
[14hour] float,
[15hour] float,
[16hour] float,
[17hour] float,
[18hour] float,
[19hour] float,
[20hour] float,
[21hour] float,
[22hour] float,
[23hour] float,
[24hour] float,
[cardate] varchar(255))declare cardate_cursor CURSOR FOR select distinct [caryear]+[carmonth]+[carday] from ribao
open cardate_cursorFETCH NEXT FROM cardate_cursor
INTO @cardate
BEGINinsert into #tmpRibao
select carway,[1hour],[2hour],[3hour],[4hour],[5hour],[6hour],[7hour],
[8hour],[9hour],[10hour],[11hour],[12hour],[13hour],[14hour],[15hour],
[16hour],[17hour],[18hour],[19hour],[20hour],[21hour],[22hour],[23hour],[24hour],
[cardate]= [caryear] + '年' + [carmonth] + '月' + [carday] + '日'
from ribao where [caryear] + [carmonth]+[carday]=@cardateinsert into #tmpRibao
SELECT distinct carway='上行',
[1hour]=(select [1hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [1hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[2hour]=(select [2hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [2hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[3hour]=(select [3hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [3hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[4hour]=(select [4hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [4hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[5hour]=(select [5hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [5hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[6hour]=(select [6hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [6hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[7hour]=(select [7hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [7hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[8hour]=(select [8hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [8hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[9hour]=(select [9hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [9hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[10hour]=(select [10hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [10hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[11hour]=(select [11hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [11hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[12hour]=(select [12hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [12hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[13hour]=(select [13hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [13hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[14hour]=(select [14hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [14hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[15hour]=(select [15hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [15hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[16hour]=(select [16hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [16hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[17hour]=(select [17hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [17hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[18hour]=(select [18hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [18hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[19hour]=(select [19hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [19hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[20hour]=(select [20hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [20hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[21hour]=(select [21hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [21hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[22hour]=(select [22hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [22hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[23hour]=(select [23hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [23hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[24hour]=(select [24hour] from ribao where carway='1车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [24hour] from ribao where carway='2车道' and [caryear] + [carmonth]+[carday]=@cardate),
[cardate]=caryear + '年' + carmonth + '月' + carday + '日'
FROM dbo.ribao  where [caryear] + [carmonth]+[carday]=@cardate

Answer 3

insert into #tmpRibao
SELECT distinct carway='下行',
[1hour]=(select [1hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [1hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[2hour]=(select [2hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [2hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[3hour]=(select [3hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [3hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[4hour]=(select [4hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [4hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[5hour]=(select [5hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [5hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[6hour]=(select [6hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [6hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[7hour]=(select [7hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [7hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[8hour]=(select [8hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [8hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[9hour]=(select [9hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [9hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[10hour]=(select [10hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [10hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[11hour]=(select [11hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [11hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[12hour]=(select [12hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [12hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[13hour]=(select [13hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [13hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[14hour]=(select [14hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [14hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[15hour]=(select [15hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [15hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[16hour]=(select [16hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [16hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[17hour]=(select [17hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [17hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[18hour]=(select [18hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [18hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[19hour]=(select [19hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [19hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[20hour]=(select [20hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [20hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[21hour]=(select [21hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [21hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[22hour]=(select [22hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [22hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[23hour]=(select [23hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [23hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[24hour]=(select [24hour] from ribao where carway='3车道' and [caryear] + [carmonth]+[carday]=@cardate)+
(select [24hour] from ribao where carway='4车道' and [caryear] + [carmonth]+[carday]=@cardate),
[cardate]= [caryear] + '年' + [carmonth] + '月' + [carday] + '日'
FROM dbo.ribao  where [caryear] + [carmonth]+[carday]=@cardateinsert into #tmpRibao
SELECT distinct carway='总路况',
[1hour]=(select sum([1hour]) from ribao where [caryear] + [carmonth]+[carday]=@cardate),
[2hour]=(select sum([2hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[3hour]=(select sum([3hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[4hour]=(select sum([4hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[5hour]=(select sum([5hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[6hour]=(select sum([6hour]) from ribao where [caryear] + [carmonth]+[carday]=@cardate),
[7hour]=(select sum([7hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[8hour]=(select sum([8hour]) from ribao where [caryear] + [carmonth]+[carday]=@cardate ),
[9hour]=(select sum([9hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[10hour]=(select sum([10hour]) from ribao where [caryear] + [carmonth]+[carday]=@cardate ),
[11hour]=(select sum([11hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[12hour]=(select sum([12hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[13hour]=(select sum([13hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[14hour]=(select sum([14hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[15hour]=(select sum([15hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[16hour]=(select sum([16hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[17hour]=(select sum([17hour]) from ribao where [caryear] + [carmonth]+[carday]=@cardate ),
[18hour]=(select sum([18hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[19hour]=(select sum([19hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[20hour]=(select sum([20hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[21hour]=(select sum([21hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[22hour]=(select sum([22hour]) from ribao  where [caryear] + [carmonth]+[carday]=@cardate),
[23hour]=(select sum([23hour]) from ribao where [caryear] + [carmonth]+[carday]=@cardate ),
[24hour]=(select sum([24hour]) from ribao where [caryear] + [carmonth]+[carday]=@cardate ),
[cardate]= caryear + '年' + carmonth + '月' + carday + '日'
from ribao where [caryear] + [carmonth]+[carday]=@cardate  FETCH NEXT FROM cardate_cursor
ENDCLOSE cardate_cursor
DEALLOCATE cardate_cursorselect * from #tmpRibao
GO
我觉得不应该吧，程序会有缺陷么？我按照标准格式写的啊，总是会释放不了，下次执行就总是取出以前的数据，然后就很多！

Answer 4

楼主的语句好像是少了这句话阿
while @@fetch_status=0这句话很重要啊