Procedure Sample

CREATE OR REPLACE PROCEDURE ABNDMC.UPS_CUST_CLOSE_COUNSEL
(
   av_yymm         in varchar,
   av_dt_start     in varchar,
   av_dt_end       in varchar,
   o_err             OUT NUMBER,          /*에러코드   */
   o_errMsg          OUT varchar2
) 
IS
    v_csno          varchar2(8);

    ls_custid       varchar2(8);
    
    CURSOR cust_dm( p_dt_start char, p_dt_end char ) IS
        select custid 
          from scs_customer a
         where ...;
   

BEGIN

        OPEN cust_dm( av_dt_start, av_dt_end );
            LOOP

                FETCH cust_dm
                INTO  ls_custid ;
                EXIT WHEN cust_dm%NOTFOUND;

               

                실행....               

              

 

        END LOOP;
        CLOSE cust_dm;

 

   o_err := 0;
   o_errMsg := '';

   RETURN;

EXCEPTION
     WHEN OTHERS THEN
       o_err    := SQLCODE;
       o_errMsg := SQLERRM;
     RETURN;

END UPS_CUST_CLOSE_COUNSEL;
/

 

--******************************************************************************

--******************************************************************************

 

declare
    result1 varchar2(10);
    result2 varchar2(10);
 
begin
    UPS_CUST_CLOSE_COUNSEL('201202','20120101','20120101',result1,result2);
  dbms_output.put_line('dddd = ' || result1);
  dbms_output.put_line('dddd = ' || result2);
end;

/

[출처] [oracle] 프로시저 실행하기|작성자 아무개