高分在线等待oracle存储过程翻译成sql server存储过程
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;


------解决方案--------------------
去SQL SERVER区问问看。