함수만들기

함수만들기 : http://sourcenote.tistory.com/99

 

delimiter $$
  
DROP FUNCTION IF EXISTS localtax.FUN_INTDF$$
  
CREATE FUNCTION localtax.FUN_INTDF(vGubun VARCHAR(10), vYear SMALLINT, vQuarter TINYINT, vBank VARCHAR(50), vBizgubun VARCHAR(1)) RETURNS BIGINT(20) unsigned
     BEGIN
          DECLARE vRtn BIGINT(20) unsigned;
          set vRtn = 0;
          if vGubun <> '' and vYear > 0 and vQuarter > 0 and vBank <> '' then
            if vGubun = 'CNT' then
              SELECT IFNULL(COUNT(INTDF_SEQ),0) INTO vRtn FROM TB_INTEREST_DIFF 
	            WHERE INTDF_YEAR = vYear AND INTDF_QUARTER = vQuarter AND BANK_NAME = vBank AND BIZ_GUBUN LIKE CONCAT('%',vBizgubun,'%');
            elseif vGubun = 'LN' then 
              SELECT IFNULL(SUM(LN_PRICE),0) INTO vRtn FROM TB_INTEREST_DIFF 
              WHERE INTDF_YEAR = vYear AND INTDF_QUARTER = vQuarter AND BANK_NAME = vBank AND BIZ_GUBUN LIKE CONCAT('%',vBizgubun,'%');
            elseif vGubun = 'INTDF' then 
              SELECT IFNULL(SUM(INTDF_PRICE),0) INTO vRtn FROM TB_INTEREST_DIFF 
              WHERE INTDF_YEAR = vYear AND INTDF_QUARTER = vQuarter AND BANK_NAME = vBank AND BIZ_GUBUN LIKE CONCAT('%',vBizgubun,'%');
            end if;  
          end if;
          RETURN vRtn;
     END $$
  
delimiter ;

결과물 보여주기...
select FUN_INTDF('INTDF',2015,1,'경남');