[MySQL] MySQL create Function(함수) 만들기

실행환경
Desktop	조립식
CPU	Intel(R) Core(TM) i7-2600K CPU @ 3.40GHz 3.40GHz
Memory	8.00 GB
OS	Windows 7 Professional K 64bit
Java	1.8.0_05
MySQL	Ver 14.14 Distrib 5.6.19, for Win64

함수 만들기

DELIMITER $$
DROP FUNCTION IF EXISTS `db_name.function_name`$$
CREATE  FUNCTION `db_name.function_name`(stID int, endID int, line int) RETURNS int
    DETERMINISTIC
BEGIN

Declare stOrd int;
declare endOrd int;
declare result int;

select ord into stOrd 
from busstoplinelist 
where BusLineID = line and BusStopID = stID;

select ord into endOrd 
from busstoplinelist 
where BusLineID = line and BusStopID = endID;

RETURN endOrd - stOrd;
    END$$
DELIMITER ;

select function_name(421, 423, 1525);

DELIMITER : $$ 문장의 끝을 $$로 바꾸어준다는 뜻
DETERMINISTIC : Link

예외 처리

delimiter $$

DROP PROCEDURE IF EXISTS db_name.procedure_name$$

CREATE PROCEDURE db_name.procedure_name(IN num INT, IN ch VARCHAR(2))

BEGIN

     DECLARE err INT DEFAULT '0'; 
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET err = -1; 

     START TRANSACTION;

     INSERT INTO table_name VALUES (num, ch);     // 성공
     INSERT INTO table_name VALUES (ch, num);     // 실패

     IF err < 0 THEN 
          ROLLBACK; 
     ELSE 
          COMMIT; 

     END IF; 

END $$

delimiter ;

참고사이트 Link