高分在线等待oracle存储过程翻译成sql server存储过程

[cxp19871102]

CREATE OR REPLACE
PROCEDURE INS_DUTY_TIME_REC_SEVEN IS
CURSOR C1 IS
select no
from PERSONNEL_BASIC--人事基本数据table
where area_code like :P_AREA
and resign_date is null
and dl_idl = 'D'
order by no;


v_num NUMBER:= 0;
v_num_2 NUMBER:= 0;
v_day NUMBER:= 0;
a_date date;
p_date varchar(15);
p_date_end varchar(15);

begin
--目前日期往前1天
p_date := to_char(:P_RUN_DATE-1,'yyyymmdd') ;
p_date_end := to_char(:P_RUN_DATE,'yyyymmdd') ;

FOR c1_rec in c1 LOOP
select count(*)
into v_num
from DUTY_TIME_REC--刷卡资料table
where IN_OUT_TIME >=to_date(P_date||'06:00:00','yyyymmddhh24:mi:ss')--刷卡的时间范围
and IN_OUT_TIME <= to_date(P_date_end||'05:59:59','yyyymmddhh24:mi:ss')
and IN_OUT ='01'--上班卡
and BADGE_NO = c1_rec.no
and rownum=1;
begin
select count(DAYS),DAYS
into v_num_2 ,v_day
from DUTY_TIME_REC_SEVEN--连续记录table
where BADGE_NO= c1_rec.no
and FLAG='Y'
group by DAYS;
exception
when others then
v_num_2:=0;
v_day:=0;
end;
if v_num>0 then
if v_num_2>0 then
update DUTY_TIME_REC_SEVEN
set DAYS=DAYS+1,
END_DATE=to_date(P_date,'yyyymmdd')
where BADGE_NO= c1_rec.no
and FLAG='Y' ;
end if;
if v_num_2 =0 then
insert into DUTY_TIME_REC_SEVEN(BADGE_NO,DAYS,END_DATE,RUN_DATE,FLAG)
values (c1_rec.no,1,to_date(P_date,'yyyymmdd'),:P_RUN_DATE,'Y');
end if;
end if;
if v_num =0 then
if v_day>=6 then
update DUTY_TIME_REC_SEVEN
set FLAG='N'
where BADGE_NO= c1_rec.no
and FLAG='Y';
insert into DUTY_TIME_REC_SEVEN(BADGE_NO,DAYS,END_DATE,RUN_DATE,FLAG)
values (c1_rec.no,0,to_date(P_date,'yyyymmdd'),:P_RUN_DATE,'Y');
end if;
if v_day<6 then
update DUTY_TIME_REC_SEVEN
set DAYS=0,
FLAG='Y',
RUN_DATE=:P_RUN_DATE
where BADGE_NO= c1_rec.no
and FLAG='Y' ;
end if;
end if;
END LOOP;
update DUTY_TIME_REC_SEVEN
set RUN_DATE=:P_RUN_DATE;
commit;
end;

[suiziguo]

去SQL SERVER区问问看。