DB2.Store Procedure - Sample

[ 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;