oracle存储过程实例详解_oracle数据库存储过程

Create Or Replace procedure loop_while_xlxxb
(
   start_value in date,
   end_value in date
)
is
   current_value date:= start_value;
   current_djz number:=1;
   current_xqj number:=0;
   current_sfjq varchar2(20):='';
   current_num number:=1;
   current_z_y number:=0;
  
begin
  while current_value <=end_value
    LOOP 
      begin
       
         select case to_char(current_value,'day')
            when '星期一' then 1
            when '星期二' then 2
            when '星期三' then 3
            when '星期四' then 4
            when '星期五' then 5
            when '星期六' then 6
            when '星期日' then 7
            else 0 end
              into current_xqj
                 from dual;
      
      select  case current_xqj
                when 7 then '是'
                when 6 then '是'
                  else '否' end
                    into current_sfjq
                    from dual;
                    
      select (to_char(current_value,'iw')-to_char(start_value,'iw'))+1 into current_z_y from dual;
                    
      select ceil(current_num/7) into current_djz from dual;
      
      current_djz :=current_z_y;
      insert into xlxxb(xn,xq,nyr,djz,xqj,sfjq)
                 values('2015-2016','2'
                            ,to_char(current_value,'yyyy-mm-dd')
                            ,current_djz
                            ,current_xqj
                            ,current_sfjq
          );
          
           current_value:=current_value+1;
           current_num :=current_num+1;
      end;
    end LOOP;        
end loop_while_xlxxb;

begin
  loop_while_xlxxb(to_date('2015-09-01','yyyy-mm-dd'),to_date('2015-12-31','yyyy-mm-dd'));
  commit;
  end;