MySQL 5.X - FUNCTION,PROCEDURE 만들기예제

delimiter $$

 

DROP FUNCTION IF EXISTS dbname.string_print$$

 

CREATE FUNCTION dbname.string_print( str VARCHAR(20) ) RETURNS VARCHAR(20)

     BEGIN

          DECLARE copy_str VARCHAR(20);

          SET copy_str = str;

          RETURN copy_str;

     END $$

 

delimiter ;

 

SELECT dbname.string_print('Hello world');

delimiter $$

 

DROP PROCEDURE IF EXISTS dbname.string_print$$

 

CREATE PROCEDURE dbname.string_print( str VARCHAR(20) )

     BEGIN

          DECLARE copy_str VARCHAR(20);

          SET copy_str = str;

          SELECT copy_str;

     END $$

 

delimiter ;

 

CALL dbname.string_print('Hello world');

이번엔 예외처리...

 

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 ;

 

      : IN -> input, OUT -> output <가독성을 위한 표시 기능>

      : 예외 핸들링을 위한 변수 선언. 쿼리 실패시 err값은 '-1'

이번엔 루프문을...

 

delimiter $$

 

DROP PROCEDURE IF EXISTS db_name.procedure_name$$

CREATE PROCEDURE db_name.procedure_name(IN ch VARCHAR(2))

BEGIN

 

     DECLARE val INT DEFAULT '0'; 
    

     WHILE val < 5 DO
          INSERT INTO table_name VALUES (val, ch);

          SET val = val + 1;

     END WHILE;
    
END $$

 

delimiter ;

 

이번엔 커서를...

 

delimiter $$

 

DROP PROCEDURE IF EXISTS proc_name$$

CREATE PROCEDURE proc_name()
BEGIN 

DECLARE cur_state INT DEFAULT '0'; 
DECLARE copy_column1 INT DEFAULT '0'; 
DECLARE copy_column2 INT DEFAULT '0'; 
DECLARE result_count INT DEFAULT '0';

 

DECLARE cur CURSOR FOR SELECT column1, column2 FROM table1;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cur_state = 1;

 

OPEN cur;

REPEAT

FETCH cur INTO copy_column1, copy_column2; →커서 이동 및 결과 매칭

IF NOT cur_state THEN →커서 상태 체크

UPDATE ...;
SET result_count = result_count + 1;

END IF;

UNTIL cur_state END REPEAT;

CLOSE cur;

 

IF result_count > 0 THEN

SELECT result_count;

ELSE

SELECT 0;

END IF;

END $$

 

delimiter ;

 

      : 커서 정의 - cur은 'SELECT column1, column2 FROM table1;'의 커서이다.

      : 커서 상태 핸들러 지정(※'02000'의 의미는 잘모르겠다.)

      : 커서 열고 닫기.

      : do while문과 비슷한 문법.

 

이번엔 SELECT 결과를 변수에...

 

delimiter $$

 

DROP PROCEDURE IF EXISTS proc_name$$

CREATE PROCEDURE proc_name()
BEGIN 

DECLARE result INT DEFAULT '0'; 

 

SELECT column1 INTO result FROM table1 WHERE ...;

SELECT reslut;

END $$

 

delimiter ;

 

※ MySQL에선 'result = column1'이 안된다.