Don't Panic
in DB ☕ 5분 읽기

[DB] SQL 스토어드 프로시저

#DB
목차

스토어드 프로시저

SQL을 자동화하지 않고 반복적으로 쓰기에는 상당한 불편함과 한계가 있다. 스토어드 프로시저를 사용하면 MySQL 안에서도 다른 프로그래밍 언어처럼 프로그램 로직을 코딩할 수 있다.

SQL은 데이터베이스에서 사용되는 언어(language)이다. 그런데 SQL을 쓰다 보면 다른 프로그래밍 언어의 기능이 필요할 때가 있다. 조건문이나 반복문을 사용하면 더 편리하고 빠른 결과를 낼 수 있는 것과 비슷하다.

MySQL의 스토어드 프로시저(Stored Procedure)는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.

스토어드 프로시저 개요

스토어드 프로시저 기본

스토어드 프로시저의 완전한 형식은 어렵게 보일 수도 있지만, 사용하는 형식은 간단하다.

스토어드 프로시저의 개념과 형식

  • 스토어드 프로시저(저장 프로시저)란 MySQL에서 제공하는 프로그래밍 기능이다.
  • C, 자바, 파이썬 등 일반 프로그래밍과는 차이가 있지만, MySQL 내부에서 사용할 때 적절한 프로그래밍 기능을 제공해 준다.
  • 스토어드 프로시저는 쿼리문의 집합으로 볼 수 있으며, 여러 동작을 일괄 처리하기 위한 용도로도 사용한다.
  • 자주 사용하는 일반 쿼리를 반복하는 것보다, 스토어드 프로시저로 묶어 놓고 필요할 때마다 간단히 호출하면 훨씬 편리하게 MySQL을 운영할 수 있다.
  • 스토어드 프로시저도 데이터베이스 개체 중 하나다. 즉, 테이블처럼 DB 내부에 저장된다.

스토어드 프로시저 형식

1은 필수 항목으로, 프로시저를 묶어 주는 기능을 한다.

DELIMITER의 의미는 “구분자”이다.

MySQL에서 구분자는 기본적으로 세미콜론(;)을 쓰는데, 스토어드 프로시저 안의 많은 SQL 끝에도 세미콜론을 쓴다. 문제는 세미콜론이 나왔을 때 이것이 SQL의 끝인지 스토어드 프로시저의 끝인지 모호할 수 있다는 점이다. 그래서 구분자를 $$로 지정해서, $$가 나오기 전까지는 스토어드 프로시저가 끝난 것이 아니라는 걸 표현한다.

2는 스토어드 프로시저의 이름을 정해 준다.

스토어드 프로시저를 만든 것은 자판기를 만든 것과 같다.

스토어드 프로시저 호출

호출 방법은 다음과 같다.

CALL 스토어드_프로시저_이름()

생성할 때는 CREATE로 스토어드 프로시저를 만들고, 호출은 CALL로 실행(호출)한다.

USE market_db;
DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
    SELECT * FROM member; -- 스토어드 프로시저 내용
END $$
DELIMITER ;

CALL user_proc();

스토어드 프로시저 실습

매개변수 사용

스토어드 프로시저는 입력 매개변수를 지정할 수 있다. 자판기에 동전을 넣는 것이라 생각하면 된다.

IN 입력_매개변수_이름 데이터_형식

입력 매개변수가 있는 스토어드 프로시저를 실행하려면, 다음과 같이 괄호 안에 값을 전달하면 된다.

CALL 프로시저_이름(전달 값)

마찬가지로 출력 매개변수를 통해 결과를 얻을 수도 있다.

OUT 출력_매개변수_이름 데이터_형식

출력 매개변수가 있는 스토어드 프로시저는 다음과 같이 실행한다.

CALL 프로시저_이름(@변수명);
SELECT @변수명;

다음은 입력 매개변수를 사용하는 예제다.

USE market_db;
DROP PROCEDURE IF EXISTS user_proc1;
DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
  SELECT * FROM member WHERE mem_name = userName;
END $$
DELIMITER ;

CALL user_proc1('에이핑크');

정리

  • 스토어드 프로시저는 MySQL에서 제공되는 프로그래밍 기능이다.
  • 스토어드 프로시저는 BEGIN ~ END 사이에 코드를 구현한다.
  • 스토어드 프로시저를 작성한 후, CALL 문을 통해서 호출한다.
  • 입력 매개변수는 스토어드 프로시저에 값을 전달한다. 형식은 앞에 IN을 붙인다.
  • 출력 매개변수는 스토어드 프로시저에서 계산된 결과를 돌려받는다. 형식은 앞에 OUT을 붙인다.
  • 동적 SQL은 다이나믹하게 SQL을 생성한 후 실행한다.

스토어드 함수와 커서

스토어드 프로시저와 함께 SQL 프로그래밍 기능으로 사용되는 데이터베이스 개체로는 스토어드 함수와 커서가 있다. 잘 쓰면 SQL 기능을 더 강력하게 확장할 수 있다.

스토어드 함수

  • MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공한다.
  • 스토어드 함수는 스토어드 프로시저와 모양이 비슷하지만 세부적으로 다르다. 용도가 다르며, RETURNS 예약어로 하나의 값을 반환해야 한다.

커서

  • 커서는 스토어드 프로시저 안에서 한 행씩 처리할 때 쓰는 프로그래밍 방식이다.
  • 문법은 복잡해 보여도 형태가 대부분 비슷하게 고정되어 있어, 한번 익히면 다음에는 쉽게 사용이 가능하다.

다음은 스토어드 함수 예제다.

SET GLOBAL log_bin_trust_function_creators = 1;	-- 변수 설정

USE market_db;
DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
    RETURNS INT
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;

SELECT sumFunc(100, 200) AS '합계';

커서는 첫 행부터 마지막 행까지 하나씩 실행해 간다.

커서 동작 1

커서 동작 2

정리

  • MySQL은 다양한 내장 함수로 제공되지 않는 기능을 스토어드 함수로 만들어 사용할 수 있다.
  • 스토어드 함수는 RETURNS 예약어를 통해 반환될 데이터의 형식을 미리 지정해 둬야 한다.
  • 스토어드 함수의 매개변수는 모두 스토어드 함수로 값이 들어오는 입력 매개변수이다.
  • 커서는 한 행씩 처리되도록 하는 기능이다. DECLARE로 선언할 수 있으며, 그 내용이 SELECT 문이다.
  • 커서는 행이 끝날 때까지 반복한다. 행의 끝을 판단하기 위해 변수 endOfRow를 준비하고, TRUE인지 체크하는 방식을 사용한다.

자동 실행되는 트리거

트리거는 작업을 자동으로 수행해서, 사용자가 추가 작업을 잊어버리는 실수를 방지해 준다.

예를 들어 회사원이 퇴사하면 그 퇴사한 직원 내용을 백업하고 퇴사자 테이블로 옮겨야 하는데, 이를 수동으로 하면 백업하지 않고 삭제할 수도 있다. 트리거는 이런 실수를 방지해 준다. 직원 테이블에서 사원 삭제 시 해당 데이터를 자동으로 퇴사자 테이블에 들어가도록 설정할 수 있다.

트리거를 쓰면 데이터에 오류가 발생하는 것을 막을 수 있다. 이를 데이터의 무결성이라고 한다.

트리거의 개요

  • 트리거란 테이블에 INSERT, UPDATE, 또는 DELETE가 발생할 때 실행되는 코드다.
  • 트리거는 테이블에서 DML문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다.
  • 테이블에 미리 부착(attach)되는 프로그램 코드라 생각하면 된다.
  • 보통 AFTER 트리거를 많이 쓴다. (AFTER, BEFORE 트리거 2개가 있다.)

트리거 개요

트리거 활용

트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 쓸 수 있다.

트리거가 사용하는 임시 테이블

테이블에 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 쓰는 테이블이 2개 있는데, 그것이 NEW와 OLD이다. 이 테이블은 MySQL이 알아서 생성하고 관리한다.

NEW 테이블은 INSERT 문이 실행될 때 다음과 같이 작동한다.

NEW 테이블 동작

INSERT(새 값)로 테이블에 새 값이 들어간다. 하지만 사실 새 값은 테이블로 가기 전 NEW에 잠깐 들어간다.

(사실 NEW 테이블은 많이 쓰지 않는다. 어차피 NEW 테이블을 거친 값은 테이블에 들어가 있기 때문이다.)

OLD 테이블은 DELETE(예전 값)와 연관이 있는데, DELETE 문이 실행될 때 다음과 같이 작동한다.

OLD 테이블 동작

예전 값이 삭제되기 전에 OLD 테이블에 잠시 머문다. 그래서 AFTER DELETE 트리거를 만들어도 삭제 후에 OLD.열이름 형식으로 예전 값에 접근할 수 있다.

마지막으로 UPDATE(새 값, 예전 값)를 사용하면 다음과 같이 NEW와 OLD 테이블을 모두 사용한다.

UPDATE 시 NEW/OLD 동작

정리

  • 트리거는 테이블에서 DML문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다.
  • AFTER DELETE는 DELETE 문이 발생한 후 트리거를 작동하라는 의미다.
  • 트리거는 행 데이터가 삭제·수정되면 기존 데이터를 백업 테이블에 저장하도록 할 수 있다.
  • 트리거에서 기존 데이터는 OLD 테이블에, 새 데이터는 NEW 테이블에 잠시 저장된다. 두 테이블은 MySQL이 내부적으로 관리한다.

관련 글