create or replace package PAK_AMMERTERDUTYis
CURSOR CUR;
PROCEDURE PR_AMMERTERDUTY();
end;create or replace package body PAK_AMMERTERDUTYis
begin
PROCEDURE PR_AMMERTERDUTY()
as
CURSOR CUR as
select * from ammerterlog where readdate>sysdate-2 and readdate<=sysdate-1;
pod AmmerterDuty.Positiveokvalue1%TYPE;
nod AmmerterDuty.Negativeokvalue1%TYPE;
ids AmmerterDuty.Ammerterid%TYPE;
querydate AmmerterDuty.Readdate%TYPE;
tmp integer;
begin
FOR C IN CUR LOOP
tmp:= mod(Ceil((trunc(to_date(C.Readdate,'yy-mm-dd')-to_date('2008-01-01','yy-mm-dd'))+1)/2+1),5);
CASE tmp
when 0 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 1 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 2 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 3 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 4 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
end;
if tmp==0 then tmp=tmp+1;end if;
if tmp>=0 and pod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate) or nod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate)
then update AmmerterDuty set PositiveOKValue||tmp=pod,NegativeOKValue||tmp=nod where ammerterid=ids and readdate=querydate;
else insert into AmmerterDuty(adid,ammerterid,PositiveOKValue||tmp,NegativeOKValue||tmp) values(adid.nextval,ids,pod,nod);
end if;
tmp:=mod(ceil((trunc(to_date(C.Readdate,'yy-mm-dd')-to_date('2008-01-02','yy-mm-dd'))+1)/2+3),5);
CASE tmp
when 0 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 1 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 2 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 3 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 4 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
end;
if tmp==0 then tmp=tmp+1;end if;
if tmp>=0 and pod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate) or nod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate)
then update AmmerterDuty set PositiveOKValue||tmp=pod,NegativeOKValue||tmp=nod where ammerterid=ids and readdate=querydate;
else insert into AmmerterDuty(adid,ammerterid,PositiveOKValue||tmp,NegativeOKValue||tmp) values(adid.nextval,ids,pod,nod);
end if;
tmp:=mod(ceil((trunc(to_date(C.Readdate,'yy-mm-dd')-to_date('2008-01-01','yy-mm-dd'))+1)/2),5)
CASE tmp
when 0 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 1 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 2 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 3 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 4 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
end;
if tmp==0 then tmp=tmp+1;end if;
if tmp>=0 pod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate) or nod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate)
then update AmmerterDuty set PositiveOKValue||tmp=pod,NegativeOKValue||tmp=nod where ammerterid=ids and readdate=querydate;
else insert into AmmerterDuty(adid,ammerterid,PositiveOKValue||tmp,NegativeOKValue||tmp) values(adid.nextval,ids,pod,nod);
end if;
end loop;
COMMIT;
end PR_AMMERTERDUTY;
end;
CURSOR CUR;
PROCEDURE PR_AMMERTERDUTY();
end;create or replace package body PAK_AMMERTERDUTYis
begin
PROCEDURE PR_AMMERTERDUTY()
as
CURSOR CUR as
select * from ammerterlog where readdate>sysdate-2 and readdate<=sysdate-1;
pod AmmerterDuty.Positiveokvalue1%TYPE;
nod AmmerterDuty.Negativeokvalue1%TYPE;
ids AmmerterDuty.Ammerterid%TYPE;
querydate AmmerterDuty.Readdate%TYPE;
tmp integer;
begin
FOR C IN CUR LOOP
tmp:= mod(Ceil((trunc(to_date(C.Readdate,'yy-mm-dd')-to_date('2008-01-01','yy-mm-dd'))+1)/2+1),5);
CASE tmp
when 0 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 1 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 2 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 3 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 4 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
end;
if tmp==0 then tmp=tmp+1;end if;
if tmp>=0 and pod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate) or nod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate)
then update AmmerterDuty set PositiveOKValue||tmp=pod,NegativeOKValue||tmp=nod where ammerterid=ids and readdate=querydate;
else insert into AmmerterDuty(adid,ammerterid,PositiveOKValue||tmp,NegativeOKValue||tmp) values(adid.nextval,ids,pod,nod);
end if;
tmp:=mod(ceil((trunc(to_date(C.Readdate,'yy-mm-dd')-to_date('2008-01-02','yy-mm-dd'))+1)/2+3),5);
CASE tmp
when 0 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 1 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 2 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 3 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 4 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
end;
if tmp==0 then tmp=tmp+1;end if;
if tmp>=0 and pod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate) or nod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate)
then update AmmerterDuty set PositiveOKValue||tmp=pod,NegativeOKValue||tmp=nod where ammerterid=ids and readdate=querydate;
else insert into AmmerterDuty(adid,ammerterid,PositiveOKValue||tmp,NegativeOKValue||tmp) values(adid.nextval,ids,pod,nod);
end if;
tmp:=mod(ceil((trunc(to_date(C.Readdate,'yy-mm-dd')-to_date('2008-01-01','yy-mm-dd'))+1)/2),5)
CASE tmp
when 0 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 1 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 2 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 3 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
when 4 then pod:=C.PositiveOKValue;nod:=C.NegativeOKValue;ids=C.Ammerterid;querydate=C.Readdate;
end;
if tmp==0 then tmp=tmp+1;end if;
if tmp>=0 pod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate) or nod in (select * from AmmerterDuty where ammerterid=ids and readdate=querydate)
then update AmmerterDuty set PositiveOKValue||tmp=pod,NegativeOKValue||tmp=nod where ammerterid=ids and readdate=querydate;
else insert into AmmerterDuty(adid,ammerterid,PositiveOKValue||tmp,NegativeOKValue||tmp) values(adid.nextval,ids,pod,nod);
end if;
end loop;
COMMIT;
end PR_AMMERTERDUTY;
end;
什么问题都没说,你在浪费大家的时间