DB2.Funciton - 분 구하기

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