농부지기

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

[ DB2.Funciton - 윤달일경우날수 ]

CREATE FUNCTION daysinyear(yr INT)

RETURNS INT

RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE

CASE (mod(yr, 4)) WHEN 0 THEN

CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END

ELSE 365 END

END)@

[ DB2.Funciton - 나이구하기 ]

* 사원의 나이 구하기 [현재일자,생일]*

SET SCHEMA SBSINST;

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

CREATE FUNCTION CMB.GET_AGES

 (ASCURRDATE VARCHAR(8),

  ASBIDD VARCHAR(8)

 )

  RETURNS VARCHAR(3)

  SPECIFIC CMB.GET_AGES

  LANGUAGE SQL

  NOT DETERMINISTIC

  READS SQL DATA

  STATIC DISPATCH

  CALLED ON NULL INPUT

  EXTERNAL ACTION

  INHERIT SPECIAL REGISTERS

  BEGIN ATOMIC

    DECLARE vAGE varchar(3);

    DECLARE I_CUR_YEAR INTEGER ;

    DECLARE I_OLD_YEAR INTEGER ;

    SET I_CUR_YEAR = INT(SUBSTR(asCURRDATE,1,4));

    SET I_OLD_YEAR = INT(SUBSTR(asBIDD,1,4));

    SET vAGE = CHAR(I_CUR_YEAR - I_OLD_YEAR);

    IF vAGE IS NULL THEN

      SET vAGE = '';

    END IF;

    RETURN vAGE;

  END;

[ DB2.Funciton - 금액반올림.내림_하기 ]

SET SCHEMA MISADM;

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

CREATE FUNCTION PER.GET_ROUND_AMT

 (ASGUBUN VARCHAR(1),

  ASAMT DECIMAL(13, 0),

  ASBASAMT INTEGER

 )

  RETURNS DECIMAL(13, 0)

  SPECIFIC PER.GET_ROUND_AMT

  LANGUAGE SQL

  NOT DETERMINISTIC

  READS SQL DATA

  STATIC DISPATCH

  CALLED ON NULL INPUT

  EXTERNAL ACTION

  INHERIT SPECIAL REGISTERS

  BEGIN ATOMIC

    DECLARE vRET decimal(13,0);

    IF asGUBUN = 'U' THEN

      SET vRET = (

        SELECT INT((asAMT+(asBASAMT-1)))/INT(asBASAMT)*INT(asBASAMT)

          FROM SYSIBM.SYSDUMMY1 );

    ELSEIF asGUBUN = 'D' THEN

      SET vRET = (

        SELECT INT(asAMT)/INT(asBASAMT)*INT(asBASAMT)

          FROM SYSIBM.SYSDUMMY1 );

    END IF;

    IF vRET IS NULL THEN

      SET vRET = 0;

    END IF;

    RETURN vRET;

  END;

COMMENT ON FUNCTION PER.GET_ROUND_AMT

 (VARCHAR(1),

  DECIMAL(13, 0),

  INTEGER

 )

  IS '구분자(asGUBUN)가 U면 올림, D면 내림이고, asBASAMT는 올림/내림 단위, asAMT는 대상금액';

[ DB2.Funciton - 시간.분_구하기 ]

SET SCHEMA PSO;

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

CREATE FUNCTION PSO.GET_MIN_MIN

 (SRC_TIME VARCHAR(4),

  MIN_TIME VARCHAR(4)

 )

  RETURNS VARCHAR(100)

  SPECIFIC PSO.GET_MIN_MIN

  LANGUAGE SQL

  NOT DETERMINISTIC

  READS SQL DATA

  STATIC DISPATCH

  CALLED ON NULL INPUT

  EXTERNAL ACTION

  INHERIT SPECIAL REGISTERS

  BEGIN ATOMIC

    DECLARE ret varchar(100);

    DECLARE len int;

    DECLARE tmp int;

    SET tmp = INTEGER(MIN_TIME) ;

    SET ret = SRC_TIME;

    SET tmp = INTEGER(SUBSTR(ret, 1, 2)) * 60 + INTEGER(SUBSTR(ret, 3, 2)) -

      tmp;

    SET ret = REPLACE(CHAR(tmp/60), ' ', '');

    IF LENGTH(ret) = 1 THEN

      SET ret = '0' || ret;

    END IF;

    SET tmp = MOD(tmp, 60);

    SET ret = REPLACE(ret || CHAR(tmp), ' ', '');

    IF LENGTH(ret) = 3 THEN

      SET ret = SUBSTR(ret, 1, 2) || '0' || SUBSTR(ret, 3, 1) ;

    END IF;

    IF ret IS NULL THEN

      SET ret = '';

    END IF;

    RETURN ret;

  END;

[ DB2.Funciton - 분 구하기 ]

SET SCHEMA SBSINST;

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

CREATE FUNCTION PSO.GET_MIN_ADD

 (SRC_TIME VARCHAR(6),

  ADD_TIME VARCHAR(6)

 )

  RETURNS VARCHAR(100)

  SPECIFIC PSO.GET_MIN_ADD

  LANGUAGE SQL

  NOT DETERMINISTIC

  READS SQL DATA

  STATIC DISPATCH

  CALLED ON NULL INPUT

  EXTERNAL ACTION

  INHERIT SPECIAL REGISTERS

  BEGIN ATOMIC

    DECLARE ret varchar(100);

    DECLARE len int;

    DECLARE tmp int;

    SET tmp = INTEGER(ADD_TIME) * 60 ;

    SET ret = SRC_TIME;

    SET tmp = tmp + INTEGER(SUBSTR(ret, 1, 2)) * 3600 + INTEGER(SUBSTR(ret, 3,

      2)) * 60 + INTEGER(SUBSTR(ret, 5, 2));

    SET ret = REPLACE(CHAR(tmp/3600), ' ', '');

    IF LENGTH(ret) = 1 THEN

      SET ret = '0' || ret;

    END IF;

    SET tmp = MOD(tmp, 3600);

    SET ret = REPLACE(ret || CHAR(tmp/60), ' ', '');

    IF LENGTH(ret) = 3 THEN

      SET ret = SUBSTR(ret, 1, 2) || '0' || SUBSTR(ret, 3, 1) ;

    END IF;

    SET tmp = MOD(tmp, 60);

    SET ret = REPLACE(ret || CHAR(tmp), ' ', '');

    IF LENGTH(ret) = 5 THEN

      SET ret = SUBSTR(ret, 1, 4) || '0' || SUBSTR(ret, 5, 1) ;

    END IF;

    IF ret IS NULL THEN

      SET ret = '';

    END IF;

    RETURN ret;

  END;

[ DB2.Funciton - 일자와일자_사이의_간격구하기 ]

* From일자에서 To일자를 뺀 일수를 반환됩니다 *

SET SCHEMA MISADM;

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

CREATE FUNCTION COM.GET_DATEDIFF

 (ASFRDATE VARCHAR(8),

  ASTODATE VARCHAR(8)

 )

  RETURNS INTEGER

  SPECIFIC COM.GET_DATEDIFF

  LANGUAGE SQL

  DETERMINISTIC

  READS SQL DATA

  STATIC DISPATCH

  CALLED ON NULL INPUT

  EXTERNAL ACTION

  INHERIT SPECIAL REGISTERS

  BEGIN ATOMIC

    DECLARE vReturn INTEGER;

    SET vReturn = (

      SELECT DAYS(DATE(SUBSTR(asFrDate,1,4) ||'-'|| SUBSTR(asFrDate,5,2) ||'-'

          || SUBSTR(asFrDate,7,2))) - DAYS(DATE(SUBSTR(asToDate,1,4) ||'-'||

          SUBSTR(asToDate,5,2) ||'-'|| SUBSTR(asToDate,7,2)))

        FROM COM.Z_COMCDCOD_TBL

        FETCH FIRST 1 ROWS ONLY ) ;

    IF vReturn IS NULL THEN

      SET vReturn = 0 ;

    END IF ;

    RETURN vReturn;

  END;

COMMENT ON FUNCTION COM.GET_DATEDIFF

 (VARCHAR(8),

  VARCHAR(8)

 )

  IS 'From일자에서 To일자를 뺀 일수를 반환됩니다.';

[ DB2.Funciton - 특정일자에_format지정하기 ]

* 해당일자를 DATE 일자로 반환됩니다.(YYYYMMDD ==> YYYY-MM-DD)' *

SET SCHEMA MISADM;

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

CREATE FUNCTION COM.GET_DATE_DD

 (ASDATE VARCHAR(8)

 )

  RETURNS CHARACTER(10)

  SPECIFIC COM.GET_DATE_DD

  LANGUAGE SQL

  DETERMINISTIC

  READS SQL DATA

  STATIC DISPATCH

  CALLED ON NULL INPUT

  EXTERNAL ACTION

  INHERIT SPECIAL REGISTERS

  BEGIN ATOMIC

    DECLARE vReturn CHARACTER(10);

    SET vReturn = (

      SELECT CHAR(DATE(SUBSTR(asDate,1,4) ||'-'|| SUBSTR(asDate,5,2) ||'-'||

          SUBSTR(asDate,7,2)))

        FROM COM.Z_COMCDCOD_TBL

        FETCH FIRST 1 ROWS ONLY ) ;

    IF vReturn IS NULL THEN

      SET vReturn = '';

    END IF;

    RETURN vReturn;

  END;

COMMENT ON FUNCTION COM.GET_DATE_DD

 (VARCHAR(8)

 )

  IS '해당일자를 DATE 일자로 반환됩니다.(YYYYMMDD ==> YYYY-MM-DD)';

[ DB2.Funciton - 특정일자에_일수더하기 ]

* 해당날자에 일수를 더한 일자 반환 함수 *

SET SCHEMA MISADM;

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

CREATE FUNCTION COM.GET_ADDDAYS

 (ASDATE VARCHAR(8),

  ASDAYS INTEGER

 )

  RETURNS CHARACTER(8)

  SPECIFIC COM.GET_ADDDAYS

  LANGUAGE SQL

  DETERMINISTIC

  READS SQL DATA

  STATIC DISPATCH

  CALLED ON NULL INPUT

  EXTERNAL ACTION

  INHERIT SPECIAL REGISTERS

  BEGIN ATOMIC

    DECLARE vReturn CHARACTER(8);

    SET vReturn = (

      SELECT SUBSTR(REPLACE(CHAR(DATE(SUBSTR(asDate,1,4) ||'-'|| SUBSTR(asDate,5,2) ||'-'|| SUBSTR(asDate,7,2)) + asDays DAYS ),'-',''),1,8)

        FROM COM.Z_COMCDCOD_TBL

        FETCH FIRST 1 ROWS ONLY ) ;

    IF vReturn IS NULL THEN

      SET vReturn = '';

    END IF;

    RETURN vReturn;

  END;

COMMENT ON FUNCTION COM.GET_ADDDAYS

 (VARCHAR(8),

  INTEGER

 )

  IS '해당일자에 일수를 더한 일자를 반환됩니다.';