MySQL (13) - 프로시저(Procedure)와 함수(Function)

간단하게 CRUD만 사용하는 경우에는 SQL을 깊게 사용하지 않지만, 전문적으로 DB를 운영하기 위해서는 결국 함수형으로 관리하는 것이 필수입니다. SQL에는 함수만 있지 않고 프로시저가 있습니다. 이번에는 해당 개념에 대하여 알아보려고 합니다.

Database - pixabay

함수 (Function)

데이터베이스에서 함수는 일반적인 프로그래밍언어에서 함수의 역할과 동일합니다. 입력값을 받아 특정한 작업을 수행하고 결과값을 출력하는 구조로 되어있습니다. 함수는 특정 작업을 수행하고 그 결과를 반환하여 DML 및 SELECT를 사용하는 쿼리문 안에서 표현식으로 사용될 수 있습니다. 

함수는 다음과 같은 특징을 가지고 있습니다. 

1. 함수에서 입력값을 받아 처리하고 해당 결과값을 반환합니다.
2. 함수 호출 문에서 반환된 값으로 사용할 수 있습니다. (복잡한 쿼리의 count 결과를 꺼내는 경우 등)

 

제작 예시

//생성
CREATE FUNCTION myfunc
(
	str1 VARCHAR(32)
) 
RETURNS VARCHAR(32)

BEGIN
  DECLARE result VARCHAR(32);
  SET result = REVERSE(str1);
  RETURN result;
END;

//삭제
DROP FUNCTION myfunc;

위의 함수는 문자열을 인식 받아 반대로 출력해 주는 기능을 합니다. mysql에서는 오라클의 create or replace 기능을 제공하지 않기 때문에 수정할 때는 삭제후 생성하면 수정할 수 있습니다.

 

사용하는 방법

위에서와 같이 DML과 SELECT 쿼리문에 사용할 수 있습니다. 각각의 경우에 대한 쿼리문 예시입니다.

--생성 - name 컬럼 안에 '달서비'라는 글자를 역순으로 바꾼 결과를 저장한다 
INSERT INTO mytable (name) VALUES (myfunc('달서비'));

--조회 - name 컬럼의 값을 받아 역순으로 출력한다
SELECT myfunc(name) FROM mytable;

--수정 - name 컬럼의 값을 역순으로 수정한다
UPDATE mytable SET name = myfunc(name);

--삭제 - name 컬럼안에 '달서비'라는 결과를 역순으로 바꾼 결과를 삭제한다
DELETE FROM mytable WHERE name = myfunc('달서비');

 

프로시저 (Procedure)

프로시저는 SQL 쿼리문을 하나의 단위로 묵어서 실행하는 쿼리의 집합입니다. 거의 함수와 사용이 비슷하지만, DML과 SELECT 문에 붙어서 사용할 수 있는 함수 다르게 직접 실행시켜야 합니다. 입력과 출력을 둘 다 여러 개 둘 수 있습니다. 하지만 호출 문에서 결과값으로 사용할수 없습니다. 프로시저의 특징은 다음과 같습니다.

1. 프로시저는 데이터베이스 내부에서 작업을 수행합니다.
2. 프로시저는 호출문에서 결과값으로 사용할 수 없습니다. (함수와 가장 큰 차이점)

 

 

제작 예시

--생성
CREATE PROCEDURE my_order_process
(
	IN in_id INT, 
	IN in_status VARCHAR(20)
)

BEGIN
	-- Create: 새 주문 추가
  	INSERT INTO orders (id, status) VALUES (in_id, in_status);

  	-- Read: 주문 목록 조회
  	SELECT * FROM orders;

  	-- Update: 상태 업데이트
  	UPDATE orders 
    SET status = CONCAT('new_', status) 
    WHERE id = in_id;

  	-- Delete: 완료된 주문 삭제
  	DELETE FROM orders WHERE id = in_id;
END;

--삭제
DROP PROCEDURE my_order_process

해당 프로시저 예제는 전체적인 주문 프로세스를 프로시저 형식으로 정리한 함수입니다. 수정하는 방법은 위의 방식과 동일하게 삭제 후 생성으로 수정하면 됩니다.

 

사용하는 방법

CALL my_order_process(1, 'pending');

위 실행문에서 첫 번째 인자는 새 주문의 ID 값이 되고, 두 번째 인자는 새 주문의 상태 값이 됩니다. 이 값을 프로시저 내부로 전달하여 프로시저에서 처리할 수 있도록 합니다. 해당 프로시저가 실행이 되면 새로운 주문이 추가되고, 추가된 주문 목록을 출력합니다. 그리고 해당 주문을 업데이트 하고 마지막으로 완료된 주문을 삭제합니다.

 

프로시저와 함수 역할의 역할과 차이점

프로시저와 함수는 둘 다 데이터베이스에서 재사용할 수 있는 코드를 만들어 중복을 방지하고 이를 통해 코드를 효율적으로 관리할 수 있습니다. 또한 데이터베이스에 정의되어 저장됩니다. 위의 공통점 말고 두 개가 사용하는 방식에는 차이점이 있습니다. 해당 방식을 표로 정리해 보았습니다.

	함수(Function)	프로시저(Procedure)
매개변수	입력만 받을수 있다	입력, 출력 둘다 받으수 있다.
반환값	반환값을 가져야 한다.	반환값이 필수가 아니다.
쿼리문 안에서 사용여부	사용가능	불가능
처리 장소	1) 클라이언트 2) 서버(값 전달) 3) 클라이언트(처리)	1) 클라이언트 2) 서버(값전달 및 처리) 3) 클라이언트