[Oracle] PL/SQL PROCEDURE Sample

CREATE OR REPLACE PROCEDURE NOSUN.CREATE_BAEHAD
(V_LSTSEQ VARCHAR2, V_BAENUM VARCHAR2) AS

  C_JBDATE INVLOG.JBDATE%TYPE;
  C_MAINNB INVLOG.MAINNB%TYPE;

  S_CNT NUMBER;
  S_DLVGUB CHAR(1);

  CURSOR C_LIST IS
    SELECT A1.MAINNB, A1.BSTSEQ, A1.DLVAMT
       FROM INVLOG A1
     WHERE A1.MAINNB = V_LSTSEQ;   
BEGIN
  S_CNT := 0;

  UPDATE INVLOG
       SET BAENUM = V_BAENUM
   WHERE BSTSEQ = V_LSTSEQ;            

  OPEN C_LIST;
  LOOP
    <<start_point>>
       
    FETCH C_LIST INTO C_MAINNB, C_BSTSEQ, C_DLVAMT;
    EXIT WHEN C_LIST%NOTFOUND;

    IF C_DLVAMT <> 0 THEN
      S_DLVGUB := '1';
    ELSE
      S_DLVGUB := '2';
    END IF;          

    BEGIN
      SELECT APPCOD, RERAT1, RERAT2
           INTO S_APPCOD, S_RERAT1, S_RERAT2
         FROM RATTRF
       WHERE APPCOD = (CASE WHEN C_BTOCHK = 'Y' THEN 'BTOC' ELSE 'ZZ00' END);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_OUTPUT := '운송장번호 : '||C_MAINNB||'에 대한 운송요율이 등록되있지 않습니다.';
        EXIT;                               
    END;
 
    GOTO START_POINT;

  END LOOP;
  CLOSE C_LIST;
 
  UPDATE BEHEAD
     SET JNGCHK = 'Y'
   WHERE BAENUM = V_BAENUM;      

END CREATE_BAEHAD;
/