[MySQL] 저장 루틴(Stored Routines) 프로시저와 함수

여러 데이터베이스(MySQL, Oracle, PostgreSQL 등)에서는 저장 루틴(Stored Routines)을 지원한다.

 

저장 루틴은 자주 사용하는 SQL 문장을 루틴처럼 저장해놓고 필요할 때마다 실행할 수 있는 기능이다.
저장 루틴의 형태는 프로시저(Procedure)와 함수(Function) 두 가지로 나뉜다.

프로시저는 여러 쿼리를 한번에 수행하고, 함수는 쿼리를 수행한 후 값을 가져오는 차이가 있다.

 

구문과 특징은 DB마다 약간 다르니 사용하는 DBMS에 맞춰 작성해야 한다.

아래 내용은 MySQL 공식 문서를 참고하여 정리했다.

 

 

 

프로시저(Stored Procedure)

프로시저는 데이터베이스 안에 저장된 SQL 코드 블록이다.
특정 작업을 반복해서 수행할 때 매번 같은 SQL을 입력하지 않고 미리 정의된 프로시저를 실행할 수 있다.

 

 

🧱 프로시저 생성

DELIMITER //

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT 
    SET @x = @x + 1; 
  UNTIL @x > p1 
  END REPEAT;
END //

DELIMITER ;

 

• DELIMITER // : MySQL에서는 기본적으로 세미콜론(;)을 문장의 끝으로 인식하는데, 프로시저나 함수처럼 여러 SQL 문장을 작성할 때는 중간에 있는 세미콜론 때문에 문장이 끝났다고 오해할 수 있다.
  DELIMITER // 를 사용하면 전체 블록을 하나의 문장으로 인식시킨다. 작성 후에는 다시 DELIMITER ;로 되돌린다.
• p1 INT : 프로시저 호출 시 전달받는 입력 파라미터 (IN 키워드를 생략하면 입력 파라미터로 처리됨)
• BEGIN ~ END : 여러 SQL 문장을 하나의 블록으로 묶음

 

 

🧱 실행

CALL dorepeat(1000);

 

 

 

 

함수(Function)

함수는 하나의 값을 반환하는 저장 루틴이다.
주로 계산, 포맷 변환, 조건 처리 등에 사용되며, 일반 SQL 쿼리 내에서도 사용할 수 있다.

 

 

🧩 함수 생성

CREATE FUNCTION hello (s CHAR(20))

RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');

• hello : 함수 이름
• (s CHAR(20)) : 이 함수에 넘겨줄 입력값(=파라미터), 그 값의 데이터 타입은 고정 길이 문자열 20자
• RETURNS : 반환 타입을 지정
• DETERMINISTIC : 같은 입력에 대해 항상 같은 결과를 반환
• RETURN : 결과 값 반환

 

 

🧩 실행

SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+

 

 

 

 

 

참고

https://dev.mysql.com/doc/refman/8.0/en/stored-routines.html

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html