oracl储存过程例子

CREATE OR REPLACE PROCEDURE proST_WAS_R
 
    (startDate date,
     endDate date,
     returnValue out int )
is
countL number;

   stcd varchar(16);
   i number;--判断循环次数
   j number;--判断总循环次数
   k number;--控制列的值在某范围内
   insertDate date;--插入的日期
   UPZ number;
 DWZ number;
 TGTQ number;
 SWCHRCD char(1);
 SUPWPTN char(1);
 SDWWPTN char(1);
   COLM_GD char(2);
  REM_GD varchar(30);
  ATID char(18);
  SDFL varchar(100);
  RMA varchar(256);
  MDPS varchar(30);
  MDDT date;
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- numbererfering with SELECT statements.

    -- Insert statements for procedure here

 SELECT   endDate - startDate into countL FROM  dual;

 i := 0;
  j := 0;
  k := 0;
insertDate := startDate;
   UPZ :=1;
 DWZ :=1;
 TGTQ :=1;
 SWCHRCD :=1;
 SUPWPTN :=1;
 SDWWPTN :=1;
  COLM_GD :=1;
  REM_GD:=1;
  ATID:=1;
  SDFL:=1;
  RMA:=1;
  MDPS:=1;
  MDDT:=startDate;
    returnValue :=1;--输出参数

DECLARE  CURSOR authors_cursor IS
select stcd from st_stbprp_b;

 

begin
  open authors_cursor;
  loop
 fetch authors_cursor into stcd; exit when authors_cursor%NOTFOUND;
     i :=0;
     insertDate :=startDate;
    
       while (i <= countL) loop
          begin
        
           insert   into   ST_WAS_R(STCD,TM,UPZ,DWZ,TGTQ,SWCHRCD,SUPWPTN,SDWWPTN, COLM_GD,REM_GD,ATID,SDFL,RMA,MDPS,MDDT)
                 values(stcd,insertDate,UPZ,DWZ,TGTQ,SWCHRCD,SUPWPTN,SDWWPTN,COLM_GD,REM_GD,ATID,SDFL,RMA,MDPS,MDDT);
             exception
                    when DUP_VAL_ON_INDEX THEN
                    returnValue := 2;
                    raise_application_error(-20015,'系统中已存在该记录,不能重复！');

                    return;
               commit;
                
                 SELECT  insertDate+1 into insertDate
                 FROM    dual;
     
     
                i :=i + 1;
              j :=j + 1;
                if(j mod 10=0) then
                  UPZ :=UPZ+1;
                  DWZ :=DWZ+1;
                  TGTQ :=TGTQ+1;
                  k:= k+1;
                end if;
               
                if(k mod 100=0) then
                  UPZ :=0;
                  DWZ :=0;
                  TGTQ :=0;                
                end if;
               
               
          end;
           end loop;

 
  end loop;
 close authors_cursor;
 end;

END;