[MySQL] 10. 스토어드 프로그램

스토어드 프로시저

쿼리문의 집합, 동작을 일괄 처리하기 위한 용도로 사용된다.

 

스토어드 프로시저 개요

• 정의 형식

DELIMITER $$
CREATE PROCEDURE [스토어드 프로시저이름 (IN 또는 OUT 파라미터)]
BEGIN
    [SQL 프로그래밍 코딩]
END $$
DELIMITER ;
CALL [스토어드 프로시저이름();]

• 수정과 삭제
  • 수정은 ALTER PROCEDURE를 사용
  • 삭제는 DROP PROCEDURE를 사용
• 매개변수의 사용
  • 입력 매개변수 지정
    
    • IN [입력_매개변수_이름] [데이터_형식]
  • 매개변수가 있는 스토어드프로시저 사용
    • CALL [프로시저이름]([전달 값]);
  • 출력 매개변수 지정
    • OUT [출력_매개변수_이름] [데이터_형식]
    • 출력 매개변수에 값을 대입할 땐 SELECT ... INTO문
• 스토어드 프로시저 내의 오류 처리
  • DECLARE 액션 HANDLER FOR [오류조건] [처리할_문장]

 

스토어드 프로시저의 특징

• MySQL 성능 향상가능
  • 스토어드 프로시저 이름 및 매개변수로 긴 쿼리코드를 대체할 수 있기에 네트워크의 부하를 줄일 수 있다
• 유지가 간편하다
  • 데이터베이스에서 관련된 스토어드 프로시저의 내용을 일관되게 수정/유지보수 등의 작업을 할 수 있다.
• 모듈식 프로그래밍이 가능하다
  • 한번 작성해두면 언제든지 실행이 가능하다
  • 다른 모듈식 프로그래밍 언어와 동일한 장점을 갖는다.
• 보안을 강화할 수 있다.
  • 사용자에게 테이블이 아닌 프로시저에 접근 권한을 줌으로써 보안을 강화할 수 있다.

 

스토어드 함수

사용자가 직접 만들어 사용하는 함수

 

스토어드 함수와 스토어드 프로시저의 차이

• IN, OUT을 사용할 수 없다. (스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다)
• RETURNS문으로 반환할 값의 데이터 형식을 지정하고 본문 안에서는 RETURN문으로 하나의 값을 반환해야한다.
  (프로시저는 반환하는 구문이 없었음. OUT파라미터를 사용해서 반환)
• SELECT문장 안에서 호출된다 (프로시저는 CALL로 호출)
• SELECT를 사용할 수 없다. (프로시저는 가능)
• 어떤 계산을 통해 하나의 값을 반환하는데 주로 사용됨 (프로시저는 여러 SQL문, 숫자 계산 등 다양한 용도로 사용됨)

예시

USE sqlDB;
DROP FUNCTION IF EXISTS userFunc;
DELIMITER $$
CREATE FUNCTION userFunc(value1 INT, value2 INT)
    RETURNS INT
BEGIN
    RETURN value1 + value2;
END $$
DELIMITER;

SELECT userFunc(100, 200);

 

커서

커서 개요

• 테이블에서 여러 개의 행을 쿼리한 후 쿼리의 결과(행의 집합)을 한 행씩 처리하는 방식
• 파일 처리 시 파일 포인터와 비슷한 동작을 한다

커서의 처리 순서

트리거

트리거 개요

• DML문(Insert, Update, Delete)이벤트 발생 시 작동하는 데이터베이스 개체
• 테이블에 부착되어있고, 이벤트 발생 시 자동으로 실행된다.
• 스토어드 프로시저와 달리 IN, OUT 매개변수를 사용할 수 없다.

트리거 문법

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    |trigger_order|
    trigger_body
    
trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name -- 여러 개의 트리거가 부착되어 있을 때, 다른 트리거보다 먼저 또는 나중에 수행되도록

• 삭제는 DROP TRIGGER
• ALTER TRIGGER문은 사용 불가

 

DML 트리거 종류

• AFTER 트리거
  • DML 작업 후에 작동
• BEFORE 트리거
  • DML 이벤트 발생하기 전에 작동

 

트리거가 생성하는 임시 테이블

• NEW 테이블
  • INSERT, UPDATE 작업 시에 변경할 새로운 데이터를 잠깐 저장해놓는다
  • 명령 수행 시 NEW테이블에 우선 저장되었다가 입력/변경 된다.
  • 변경 될 데이터 저장
• OLD 테이블
  • DELETE, UPDATE 작업이 수행되면 변경 이전 값을 저장한다.
  • 변경 전 데이터 저장

 

기타 트리거

• 다중 트리거
  • 하나의 테이블에 동일한 트리거가 여러 개 부착되어있는 것
  • ex) AFTER INSERT 트리거가 한 개 테이블에 2개 이상 있을 수 있다.
• 중첩 트리거
  • 트리거가 또 다른 트리거를 작동
  • 성능에 좋지 않은 영향을 미칠 수 있다.(첫 트랜잭션은 마지막 트리거가 끝나야 닫히기에 rollback시 부하가 크다)
• 트리거의 작동 순서
  • 하나의 테이블에 여러 개의 트리거가 있다면 작동 순서를 지정할 수 있다.
  • 대부분 순서는 큰 의미가 없다
  • 'FOLLOWS [트리거이름]' : 지정한 트리거 다음 현재 트리거가 작동
  • 'PRECEDES [트리거이름]' : 지정한 트리거가 작동하기 이전에 현재 트리거가 작동

 

실행계획

DB가 테이터를 찾아가는 일련의 과정을 사람이 알아보기 쉽게 DB 결과 셋으로 보여주는 것.

MySQL Explain을 활용하여 기존의 쿼리를 튜닝할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수립 등과 같이 성능 최적화에 대한 전반적인 업무를 처리할 수 있다.

 

사용방법

EXPLAIN [EXTENDED] SELECT ... FROM ... WHERE ...

EXPLAIN을 붙이고 실행을 하면 실행계획에 대한 결과를 보여준다.

 

 

예제

https://ibks-platform.tistory.com/374

[MySQL] Query Plan 보는 법

 

Explain 결과 표 항목 정리

https://nomadlee.com/mysql-explain-sql/