小弟对数据库知识水平甚缺,请教各位高手,这个游标会不会在多用户使用的时候,造成死锁,如果发生死锁,是怎么处理游标中的数据呢?
declare curn cursor for
select ActionId,ItemId,GroupID
,Medicare,MedicareRate,BeginTime
,[Name],[Type],Category
,Code,Usage,TotalDosage
,TotalDosageUnit,OneDosageUnit,Frequency
,PackQty,OneDosage,BaseQty
,Drugstore,AdviceItem,ChildrenRate
,CurDoctorId,CurDoctorName,Re,AdviceId
from emrPrescribe
where PatientNo=@PatientNo
and ExecuteTime=@ExecuteTime
open curn
fetch next from curn into @Actionid,@ItemId,@GroupID
,@Medicare,@MedicareRate,@BeginTime
,@Name,@Type,@Category
,@Code,@Usage,@TotalDosage
,@TotalDosageUnit,@OneDosageUnit,@Frequency
,@PackQty,@OneDosage,@BaseQty
,@Drugstore,@AdviceItem,@ChildrenRate
,@CurDoctorId,@CurDoctorName,@Re,@AdviceId
WHILE (@@fetch_status = 0)
begin
exec submitToLis --@Actionid
@lisid
,@Code
,@AdviceItem
,@BaseQty
,@AdviceCount
,@zd
exec submitDrugsToHis @PatientNo
,@Actionid
,@tmpGroupid
,@DeptName
,@Name
,@Code
,@Quantity
,@Unit
,@DrugstoreType
,@DCAType
end fetch next from curn into @Actionid,@ItemId,@GroupID
,@Medicare,@MedicareRate,@BeginTime
,@Name,@Type,@Category
,@Code,@Usage,@TotalDosage
,@TotalDosageUnit,@OneDosageUnit,@Frequency
,@PackQty,@OneDosage,@BaseQty
,@Drugstore,@AdviceItem,@ChildrenRate
,@CurDoctorId,@CurDoctorName,@Re,@AdviceId
end
close curn
deallocate curn
declare curn cursor for
select ActionId,ItemId,GroupID
,Medicare,MedicareRate,BeginTime
,[Name],[Type],Category
,Code,Usage,TotalDosage
,TotalDosageUnit,OneDosageUnit,Frequency
,PackQty,OneDosage,BaseQty
,Drugstore,AdviceItem,ChildrenRate
,CurDoctorId,CurDoctorName,Re,AdviceId
from emrPrescribe
where PatientNo=@PatientNo
and ExecuteTime=@ExecuteTime
open curn
fetch next from curn into @Actionid,@ItemId,@GroupID
,@Medicare,@MedicareRate,@BeginTime
,@Name,@Type,@Category
,@Code,@Usage,@TotalDosage
,@TotalDosageUnit,@OneDosageUnit,@Frequency
,@PackQty,@OneDosage,@BaseQty
,@Drugstore,@AdviceItem,@ChildrenRate
,@CurDoctorId,@CurDoctorName,@Re,@AdviceId
WHILE (@@fetch_status = 0)
begin
exec submitToLis --@Actionid
@lisid
,@Code
,@AdviceItem
,@BaseQty
,@AdviceCount
,@zd
exec submitDrugsToHis @PatientNo
,@Actionid
,@tmpGroupid
,@DeptName
,@Name
,@Code
,@Quantity
,@Unit
,@DrugstoreType
,@DCAType
end fetch next from curn into @Actionid,@ItemId,@GroupID
,@Medicare,@MedicareRate,@BeginTime
,@Name,@Type,@Category
,@Code,@Usage,@TotalDosage
,@TotalDosageUnit,@OneDosageUnit,@Frequency
,@PackQty,@OneDosage,@BaseQty
,@Drugstore,@AdviceItem,@ChildrenRate
,@CurDoctorId,@CurDoctorName,@Re,@AdviceId
end
close curn
deallocate curn
where PatientNo=@PatientNo查询时可用脏读或用快照隔离处理,看看调用处理的逻辑
可以通過SQL Server Profiler跟蹤