실행환경

 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

+ Recent posts