농부지기

[ DB2.Store Procedure - Sample ]

SET SCHEMA MISADM  ;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","MISADM";

CREATE PROCEDURE PRD.SQLSAMP

 (IN PRDDIAY_NO VARCHAR(10),

  IN PRDT_BGN_DD VARCHAR(8),

  IN PRDT_END_DD VARCHAR(8)

 )

  SPECIFIC PRD.SQLSAMP

  DYNAMIC RESULT SETS 1

  LANGUAGE SQL

  NOT DETERMINISTIC

  CALLED ON NULL INPUT

  MODIFIES SQL DATA

  INHERIT SPECIAL REGISTERS

  ------------------------------------------------------------------------

-- SQL Stored Procedure sqlsamp

------------------------------------------------------------------------

P1: BEGIN

    -- Declare cursor

    DECLARE cursor1 CURSOR WITH RETURN FOR

        SELECT

              f_diarycsmmst_tbl.prddiay_no,

              f_diarycsmmst_tbl.psn_cd,

              f_diarycsmepn_tbl.BRD1_EPN,

              f_diarycsmepn_tbl.BRD2_EPN ,

              f_diarycsmepn_tbl.BRD3_EPN

        FROM

           f_diarycsmmst_tbl,

           f_diarycsmepn_tbl

        WHERE

           (

              (

                f_diarycsmmst_tbl.prddiay_no = f_diarycsmepn_tbl.prddiay_no

              )

           );

    -- Cursor left open for client application

    OPEN cursor1;

END P1;

GRANT EXECUTE ON PROCEDURE PRD.SQLSAMP

 (VARCHAR(10),

  VARCHAR(8),

  VARCHAR(8)

 )

  TO USER PRD WITH GRANT OPTION;

[DB2.Store Procedure - Syntax ]

1. IF 문
     IF .. THEN
        ...
    ELSEIF  ... THEN
        ...
    ELSE
        ...
    END IF;
 

   IF   IS NULL THEN
         ....
   END IF;
 

2. WHILE 문
      WHILE 조건
           ....
       END;
 

3. goto 문
    Main_state:
           WHILE 조건
                  ....
                 LEAVE  Main_state:
           END;