一个Oracle存储过程



create or replace procedure sp_ProfitSharing(v_date date,v_oparatorId NUMBER) as
--定义变量
v_revenue NUMBER;
v_toll NUMBER;
v_settlementFactor NUMBER(5);
v_recordGenTime DATE:=sysdate;
v_recordStatus NUMBER(2);
v_count NUMBER;

begin

--判断当日期是否已结算，如果是将status变为1，即为废弃状态
   select count(*) into v_count from dd_profit_sharing d
   where d.statistic_date = v_date;
   if v_count>0 then
   update dd_profit_sharing d set d.record_status=1 where d.statistic_date = v_date;
   end if;

   Declare
   Cursor tollCursor Is
   select d.toll,d.station_code,d.statistic_date from dd_toll_tracffic_daily d
   where d.statistic_date = v_date and d.record_status=0;

   varT tollCursor%rowType;

   Cursor factorCursor Is
   select t.settlement_object_code,t.settlement_factor from sd_settlement_factor t;

   varE factorCursor%rowType;

   Begin
    if tollCursor%isopen = false then
        open tollCursor;
       dbms_output.put_line('Opening...');
     end if;
     loop
     fetch tollCursor into varT;
        exit when tollCursor%notfound;

      Begin
     if factorCursor%isopen = false then
        open factorCursor;
       dbms_output.put_line('Opening...');
     end if;
     loop
        fetch factorCursor into varE;
        exit when factorCursor%notfound;
        v_revenue:=varE.settlement_factor * varT.toll * 0.0001;
        v_toll:=varT.toll;
        v_settlementFactor:=varE.settlement_factor;
        v_recordStatus:=0;
        insert into dd_profit_sharing values(dd_profit_sharing_seq.nextval,v_date,
        varT.station_code,varE.settlement_object_code,v_revenue,v_toll,v_settlementFactor,v_oparatorId,
        v_recordGenTime,v_recordStatus);

     end loop;
     if factorCursor%isopen then
        Close factorCursor;
        dbms_output.put_line('Closing...');
     end if;
  End;

     end loop;
     if tollCursor%isopen then
        Close tollCursor;
        dbms_output.put_line('Closing...');
     end if;
   End;

end;