Question

oracle存储过程转换成sql存储过程 加游标

CREATE OR REPLACE PROCEDURE USP_VASCULAR_ACCESS_SITE IS
BEGIN
DECLARE
V_VASCULAR_ACCESS VARCHAR2(50);
V_ACCESS_SITE VARCHAR2(50);
BEGIN
FOR BRESULT IN (SELECT A.PATIENT_ID FROM BPIS_PATIENT_INFO A) LOOP
BEGIN
SELECT A.VASCULAR_ACCESS, A.ACCESS_SITE
INTO V_VASCULAR_ACCESS, V_ACCESS_SITE
FROM BPIS_TREAT_PROGRAM_LIST A
WHERE A.PATIENT_ID = BRESULT.PATIENT_ID
AND ROWNUM = 1;
UPDATE BPIS_PATIENT_INFO A
SET A.VASCULAR_ACCESS = V_VASCULAR_ACCESS,
A.ACCESS_SITE = V_ACCESS_SITE
WHERE A.PATIENT_ID = BRESULT.PATIENT_ID;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
END USP_VASCULAR_ACCESS_SITE;

Answer 1

CREATE proc jiagongzi
as
declare @name nvarchar(20)
declare @xueli nvarchar(20)
declare @time datetime
declare @gongzi intdeclare zengjia cursor scroll
for
select * from 职员表
open zengjia
fetch next from zengjia into @name,@xueli,@time,@gongzi
while @@FETCH_STATUS=0
begin
if (@xueli='本科' or @xueli='硕士') and DATEDIFF(year, @time, getdate())>=3
begin
update 职员表 set 工资=@gongzi+@gongzi*0.1 where 姓名=@name
end
if (@xueli<>'本科' and @xueli<>'硕士') and DATEDIFF(year, @time, getdate())>=10
begin
update 职员表 set 工资=@gongzi+@gongzi*0.08 where 姓名=@name
end
fetch next from zengjia into @name,@xueli,@time,@gongzi
end
close zengjia
deallocate zengjia goexec jiagongzi