执行这个存储过程,OA_TAB_PHONECHARGE表就锁住了,是X锁,谁能帮我看看,解决了就给分. CREATE procedure Proc_updatePhoneCharge
as
if datepart(day,getdate()) =31
begin declare @usercode char(10)
declare @monthcharge decimal
declare @sparecharge decimal
begin transaction
declare phonecharge cursor
for
SELECT pc_user_code,pc_month_charge,pc_spare_charge FROM DBO.OA_TAB_PHONECHARGE
where pc_id in
(
SELECT max(pc_id) FROM DBO.OA_TAB_PHONECHARGE where PC_month_CHARGE>0 GROUP BY PC_USER_CODE
)
open phonecharge
fetch phonecharge into @usercode,@monthcharge,@sparecharge
declare @err int
select @err = 0
begin transaction t1
while (@@fetch_status=0)
begin
if datepart(month,getdate()) = 1
begin
insert into DBO.OA_TAB_PHONECHARGE(PC_USER_CODE,PC_MONTH_CHARGE,PC_SPARE_CHARGE,PC_CREATOR,PC_CREATEDATE)
values(@usercode,@monthcharge,@monthcharge,'200718',getdate())
select @err = @err + @@error
end
else
begin
insert into DBO.OA_TAB_PHONECHARGE(PC_USER_CODE,PC_MONTH_CHARGE,PC_SPARE_CHARGE,PC_CREATOR,PC_CREATEDATE)
values(@usercode,@monthcharge,@sparecharge+@monthcharge,'200718',getdate())
select @err = @err + @@error
fetch next from phonecharge into @usercode,@monthcharge,@sparecharge
end
end
if @err = 0
commit transaction t1
else
rollback transaction t1
close phonecharge
deallocate phonecharge
end
GO
as
if datepart(day,getdate()) =31
begin declare @usercode char(10)
declare @monthcharge decimal
declare @sparecharge decimal
begin transaction
declare phonecharge cursor
for
SELECT pc_user_code,pc_month_charge,pc_spare_charge FROM DBO.OA_TAB_PHONECHARGE
where pc_id in
(
SELECT max(pc_id) FROM DBO.OA_TAB_PHONECHARGE where PC_month_CHARGE>0 GROUP BY PC_USER_CODE
)
open phonecharge
fetch phonecharge into @usercode,@monthcharge,@sparecharge
declare @err int
select @err = 0
begin transaction t1
while (@@fetch_status=0)
begin
if datepart(month,getdate()) = 1
begin
insert into DBO.OA_TAB_PHONECHARGE(PC_USER_CODE,PC_MONTH_CHARGE,PC_SPARE_CHARGE,PC_CREATOR,PC_CREATEDATE)
values(@usercode,@monthcharge,@monthcharge,'200718',getdate())
select @err = @err + @@error
end
else
begin
insert into DBO.OA_TAB_PHONECHARGE(PC_USER_CODE,PC_MONTH_CHARGE,PC_SPARE_CHARGE,PC_CREATOR,PC_CREATEDATE)
values(@usercode,@monthcharge,@sparecharge+@monthcharge,'200718',getdate())
select @err = @err + @@error
fetch next from phonecharge into @usercode,@monthcharge,@sparecharge
end
end
if @err = 0
commit transaction t1
else
rollback transaction t1
close phonecharge
deallocate phonecharge
end
GO
解决方案 »
- 我的贴子打开怎么变成 '发生错误' 了?? 就是" 求一个关于应付帐款的Select语句"这个贴子,你们能打开吗?
- 自定义函数里面无法进行插入操作?
- 如果监视存储过程的执行过程?
- 循环处理字段类型
- 请问如何得知当前某个数据库正在被几个人使用?
- 求日期的最大值、最小值。
- 这样的硬件能装WINDOWS 2003 企业版或者Windows Server 2003 简体中文数据中心版么?我XP系统装不上SQL2005啊?
- 关于登陆sql 2000的问题!!!
- 我也曾经自认为是sqlserver高手,但现在确不行了,有求于大家,分数一定送上,是我两个月来未解决的问题。
- SQL SERVER中,两个不在同一个库中的表(结构相同)能否通过 insert ... select 的方法到数据?
- excel导入问题
- 基于SQL数据库中某一表进行修改,开发小工具,请指点思路!
declare @monthcharge decimal
declare @sparecharge decimal
begin transaction----这个事务的提交和回滚处理呢?因为事务没有提交
所以表被锁定了
commit transaction t1
else
rollback transaction t1这不是回滚和提交吗?begin transaction 肯定是要的,我要保证insert全部成功或者全部失败。
这样改一下:
while (@@fetch_status=0)
begin
if datepart(month,getdate()) = 1
begin
insert into ...
select @err = @err + @@error
end
else
begin
insert into ...
select @err = @err + @@error
end
----修改此处,将fetch next写到if..else..外面
fetch next from phonecharge into @usercode,@monthcharge,@sparecharge
end
fetch next from phonecharge into @usercode,@monthcharge,@sparecharge
这行有问题,我试试
commit transaction t1
else
rollback transaction t1这不是回滚和提交吗?
这是提交,但是是对下面一个事务的提交啊,一开始那个呢?
fetch phonecharge into @usercode,@monthcharge,@sparecharge
declare @err int
select @err = 0
begin transaction t1
as
if datepart(day,getdate()) =31
begin declare @usercode char(10)
declare @monthcharge decimal
declare @sparecharge decimal
begin transaction
declare phonecharge cursor
for
SELECT pc_user_code,pc_month_charge,pc_spare_charge FROM DBO.OA_TAB_PHONECHARGE
where pc_id in
(
SELECT max(pc_id) FROM DBO.OA_TAB_PHONECHARGE where PC_month_CHARGE>0 GROUP BY PC_USER_CODE
)
open phonecharge
fetch phonecharge into @usercode,@monthcharge,@sparecharge
declare @err int
select @err = 0
begin transaction t1
while (@@fetch_status=0)
begin
if datepart(month,getdate()) = 1
begin
insert into DBO.OA_TAB_PHONECHARGE(PC_USER_CODE,PC_MONTH_CHARGE,PC_SPARE_CHARGE,PC_CREATOR,PC_CREATEDATE)
values(@usercode,@monthcharge,@monthcharge,'200718',getdate())
select @err = @err + @@error
end
else
begin
insert into DBO.OA_TAB_PHONECHARGE(PC_USER_CODE,PC_MONTH_CHARGE,PC_SPARE_CHARGE,PC_CREATOR,PC_CREATEDATE)
values(@usercode,@monthcharge,@sparecharge+@monthcharge,'200718',getdate())
select @err = @err + @@error
end
fetch next from phonecharge into @usercode,@monthcharge,@sparecharge
end
if @err = 0
commit transaction t1
else
rollback transaction t1
close phonecharge
deallocate phonecharge
end
execute Proc_updatePhoneCharge
to hhhdyj()
我不太会写存储过程,对你说的意思也不是很明白,你能帮我改改吗?
我就是想开始一个事务,如果出错就回滚,如果执行到最后就提交整个事务。能帮帮我吗?谢谢了!
as
if datepart(day,getdate()) =31
begin declare @usercode char(10)
declare @monthcharge decimal
declare @sparecharge decimal
------- begin transaction----没有用,要是加上的话,还要多一个提交事务的语句
declare phonecharge cursor
for
SELECT pc_user_code,pc_month_charge,pc_spare_charge FROM DBO.OA_TAB_PHONECHARGE
where pc_id in
(
SELECT max(pc_id) FROM DBO.OA_TAB_PHONECHARGE where PC_month_CHARGE>0 GROUP BY PC_USER_CODE
)
open phonecharge
fetch phonecharge into @usercode,@monthcharge,@sparecharge
declare @err int
select @err = 0
begin transaction t1
while (@@fetch_status=0)
begin
if datepart(month,getdate()) = 1
begin
insert into DBO.OA_TAB_PHONECHARGE(PC_USER_CODE,PC_MONTH_CHARGE,PC_SPARE_CHARGE,PC_CREATOR,PC_CREATEDATE)
values(@usercode,@monthcharge,@monthcharge,'200718',getdate())
select @err = @err + @@error
end
else
begin
insert into DBO.OA_TAB_PHONECHARGE(PC_USER_CODE,PC_MONTH_CHARGE,PC_SPARE_CHARGE,PC_CREATOR,PC_CREATEDATE)
values(@usercode,@monthcharge,@sparecharge+@monthcharge,'200718',getdate())
select @err = @err + @@error
end
fetch next from phonecharge into @usercode,@monthcharge,@sparecharge
end
if @err = 0
commit transaction t1
else
rollback transaction t1
close phonecharge
deallocate phonecharge
end
GO