--用case when else判断是否插入随机生成的考勤记录
create or replace procedure mytest_RestoreKq as
kssj date;
jssj date;
tmpsj date;
amnum int;
pmnum int;
cursor cs is SELECT sfz,zwbh FROM ncldlpx where xxid='0001' and pxqb='2013001';
begin
select pxkssj into kssj from kbqb where xxid='0001' and qb='2013001';
select pxjssj into jssj from kbqb where xxid='0001' and qb='2013001';
for c in cs loop--for循环嵌套while loop循环需插入临时变量。
tmpsj:=kssj;
while tmpsj<=jssj loop
select count(*) into amnum from zw_kq where ksj between to_date(to_char(tmpsj,'yyyy-MM-dd')||' '||'07:00:00','yyyy-MM-dd hh24:mi:ss') and
to_date(to_char(tmpsj,'yyyy-MM-dd')||' '||'09:00:00','yyyy-MM-dd hh24:mi:ss') and zwbh=c.zwbh and sfz=c.sfz;
select count(*) into pmn
create or replace procedure mytest_RestoreKq as
kssj date;
jssj date;
tmpsj date;
amnum int;
pmnum int;
cursor cs is SELECT sfz,zwbh FROM ncldlpx where xxid='0001' and pxqb='2013001';
begin
select pxkssj into kssj from kbqb where xxid='0001' and qb='2013001';
select pxjssj into jssj from kbqb where xxid='0001' and qb='2013001';
for c in cs loop--for循环嵌套while loop循环需插入临时变量。
tmpsj:=kssj;
while tmpsj<=jssj loop
select count(*) into amnum from zw_kq where ksj between to_date(to_char(tmpsj,'yyyy-MM-dd')||' '||'07:00:00','yyyy-MM-dd hh24:mi:ss') and
to_date(to_char(tmpsj,'yyyy-MM-dd')||' '||'09:00:00','yyyy-MM-dd hh24:mi:ss') and zwbh=c.zwbh and sfz=c.sfz;
select count(*) into pmn