[Real MySQL] 14-1. 스토어드 프로그램: 기본

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

 

• 스토어드 프로그램은 스토어드 루틴이라 하며, 절차적인 처리를 위해 제공됨
• 스토어드 프로그램은 스토어드 프로시저, 스토어드 함수, 트리거, 이벤트 등을 모두 아우르는 명칭임
  • 모두 똑같은 문법으로 작성할 수 있음

1. 장단점

장점

보안 향상

• 입력값의 유효성을 체크한 후 동적인 SQL을 생성
• 단위별로 실행 권한 부여 가능
  • 특정 테이블의 읽기, 쓰기
  • 특정 컬럼에 대해 권한 설정

 

기능의 추상화

• 개발 언어나 도구와 관계없이 생성규칙을 알지 못해도 호출하여 발급받을 수 있음
• 각각 다른 프로그래밍 언어로 생성규칙 적용하여 생성된 값을 각각의 소스코드에서 구현하지 않아도 됨

 

네트워크 소요 시간 절감

• 애플리케이션에서 데이터베이스에 여러번 질의해야 할 경우, 스토어드 프로그램으로 한번에 요청하여 응답받을 수 있음

 

절차적 기능 구현

• 스토어드 프로그램은 DBMS 서버어서 절차적인 기능을 실행할 수 있는 제어 기능을 제공
• SQL에는 절차적인 기능을 제공하지 않음
  • IF, WHILE 과 같은 제어 문장 사용 불가

 

개발 업무의 구분

• 애플리케이션 조직과 SQL 조직이 구분되어 작업이 가능함
• 트랜잭션 단위로 데이터베이스 관련 처리를 하도록 스토어드 프로그램을 API 같이 제공하여 역할 구분이 가능함

 

단점

낮은 처리 성능

• MySQL 서버는 처리 성능이 다른 프로그램 언어보다 떨어짐 (스토어드 프로그램 처리가 주목적이 아님)
• 스토어드 프로그램을 읽기 위한 파싱 작업이 추가로 들어감

 

애플리케이션 코드의 조각화

• 프로그램 코드와 MySQL 스토어드 프로그램이 서로 분리되면 설치나 배포가 더 복잡해짐

 

2. 문법

스토어드 프로시저

• 서로 데이터를 주고받아야 하는 여러 쿼리를 하나의 그룹으로 묶어서 독립적으로 실행하기 위해 사용함

 

생성

CREATE PROCEDURE sp_sum (IN param1 INTEGER, IN param2 INTEGER, OUT param3 INTEGER)
BEGIN 
   SET param3 = param1 + param2;
END;

• 반환값 없음
• IN: 입력 전용 파라미터
• OUT: 출력 전용 파라미터

 

수정

alter procedure sp_sum sql security definer;

 

삭제

drop procedure sp_sum;

 

실행

CALL sp_sum(1, 2, @result);
SELECT @result;

• CALL 명령어로 스토어드 프로그램을 호출함
• 세션 변수를 사용하여 출력 파라미터를 전달함

 

커서 반환

CREATE PROCEDURE sp_selectEmployees (IN in_empno INTEGER)
BEGIN 
   SELECT * FROM employees WHERE emp_no=in_empno;
END //

CALL sp_selectEmployees(10001);;

• OUT 변수가 있지도 않았는데 쿼리의 결과셋이 출력되거나 클라이언트로 전송됨
• 디버깅 용으로 사용됨
  • 스토어드 프로시저는 로깅 기능이 없음

 

딕셔너리

• 스토어드 프로시저는 시스템 테이블로 저장됨 (사용자에게 보이지 않음)
• information_schema.ROUTINES 뷰로 확인 가능

select routine_schema, routine_name, routine_body
 from information_schema.ROUTINES
  where routine_schema='employees' and routine_type='PROCEDURE';

 

스토어드 함수

• 하나의 SQL 문장으로 작성이 불가능한 기능을 하나의 SQL 문장으로 구현해야 할 때 사용됨

 

생성 및 삭제

CREATE FUNCTION sf_sum(param1 INTEGER, param2 INTEGER)
RETURNS INTEGER  
READS SQL DATA  -- 또는 DETERMINISTIC 추가 가능
BEGIN
  DECLARE param3 INTEGER DEFAULT 0;
  SET param3 = param1 + param2;
  RETURN param3;
END //

• RETURN 부 필수 (타입, 변수)
• PREPARE 스테이먼트 사용 불가 (PREPARE, EXECUTE)
• COMMIT / ROLLBACK 사용 불가
• 재귀 호출 사용 불가
• 프로시저 호출 사용 불가
• 결과 셋을 반환하는 SQL 사용 불가

 

실행

select sf_sum(1, 2);

 

트리거

• 테이블의 레코드가 저장되거나 변경될 때, 미리 정의해둔 작업을 자동으로 실행해주는 스토어드 프로그램
• 테이블에 대해서만 생성할 수 있음

 

예

• 칼럼의 유효성 체크
• 다른 테이블로의 복사나 백업
• 계산된 결과를 다른 테이블에 함께 업데이트

 

생성

CREATE TRIGGER on_delete BEFORE DELETE ON employees
  FOR EACH ROW
  BEGIN
    DELETE FROM salaries WHERE emp_no=OLD.emp_no;
END;;

• 트리거를 실행할 이벤트
  • BEFORE, AFTER
  • INSERT, UPDATE, DELETE
• OLD: employees 테이블의 변경되기 전 레코드
• 사용하지 못하는 유형
  • 외래키 관계에 의해 자동으로 변경되는 경우 호출되지 않음
  • 복제에 의해 레플리카 서버에 업데이트되는 레코드 기반의 복제는 레플리카 서버의 트리거를 기동시키지 않음

 

트리거 딕셔너리

• information_schema의 TRIGGERS 뷰를 통해 트리거 정보 확인 가능

 

이벤트

• 특정한 시간에 스토어드 프로그램을 실행할 수 있는 스케줄러 기능
• 스케줄링을 전담하는 스레드가 활성화되는 경우에만 이벤트가 실행됨
  • event_scheduler 시스템 변수를 활성화해야 함

 

확인

SHOW PROCESSLIST;

 

 

생성

• DO 절
  • 스토어드 프로시저 호출 가능
  • 쿼리 호출 가능 (BEGIN ... END)

• ON COMPLETE 절
  • 완전히 종료된 이벤트를 삭제할 지 유지할지 결정할 수 있음
  • PRESERVE: 이벤트 실행이 완료되도 삭제되지 않음

 

CREATE EVENT onetime_job
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO
  INSERT INTO daily_rank_log VALUES (NOW(), 'DONE');

• ON SCHEDULE AT: 단 한번 실행되는 일회성

 

CREATE EVENT daily_ranking
  ON SCHEDULE EVERY 1 DAY STARTS '2020-09-07 01:00:00' ENDS '2021-09-07 01:00:00'
DO
  INSERT INTO daily_rank_log VALUES (NOW(), 'DONE');

• ON SCHEDULE EVERY 1 DAY STARTS .. ENDS: 반복성 이벤트

 

상태

• ENABLE
• DISABLE
• DISABLE ON SLAVE (SLAVESIDE_DISABLED)
  
  • 소스 서버에서 실행된 이벤트가 만들어낸 데이터 변경 사항은 자동으로 레플리카 서버로 복제됨
  • 레플리카 서버에서는 DISABLE 됨 (중복 방지)
  • 단, 레플리카 서버가 소스 서버로 승격되면 수동으로 ENABLE로 변경해야 함

 

확인

• information_schema의 EVENTS 뷰를 통해 조회할 수 있음
  
  • 항상 마지막 실행 로그만 가짐

 

딕셔너리

• information_schema의 EVENTS 뷰를 통해 조회할 수 있음
  • 이벤트 코드
  • 반복인지 일회성인지
  • 언제 마지막으로 실행되었는지
  • 언제 등록되었는지

 

스토어드 프로그램 본문 작성

트랜잭션 (BEGIN...END 블록)

• 스토어드 프로시저 or 이벤트에만 사용 가능
• START TRANSACTION: 트랜잭션 시작
• COMMIT: 트랜잭션 종료

 

변수

• 변수는 BEGIN ... END 구문에서만 사용 가능

DECLARE v_name VARCHAR(50) DEFAULT 'Matt';

SET v_name = 'KIM';

SELECT last_name INTO v_name
FROM employees
WHERE emp_no=10001
LIMIT 1;

 

제어문

CREATE FUNCTION sf_greatest(p_value1 INT, p_value2 INT)
RETURNS INT
BEGIN
  IF p_value1 IS NULL THEN RETURN p_value2;
  ELSEIF p_value1 IS NULL THEN RETURN p_value2;
  ELSEIF p_value1 >= p_value2 THEN RETURN p_value2;
  ELSE RETURN p_value2;
  END IF;
END;;

CREATE FUNCTION sf_greatest(p_value1 INT, p_value2 INT)
RETURNS INT
BEGIN
  CASE    
	WHEN p_value1 IS NULL THEN RETURN p_value2;
	WHEN p_value1 IS NULL THEN RETURN p_value2;
	WHEN p_value1 >= p_value2 THEN RETURN p_value2;
	ELSE RETURN p_value2;
  END CASE;
END;;

CREATE FUNCTION sf_factorial(p_max INT)
RETURNS INT
BEGIN
  DECLARE v_factorial INT DEFAULT 1;
  
  factorial_loop : LOOP
    SET v_factorial = v_factorial * p_max;
    SET p_max = p_max - 1;
    
    IF p_max <= 1 THEN LEAVE factorial_loop; END IF;
    
  END LOOP;
  
  RETURN v_factorial;
END;;

 

SQLSTATE와 에러 번호

• ERROR 1446 (42506) : Table 'test.not_found_table' doesn't exist
• ERROR ERROR-NO (SQL-STATE)
• ERROR-NO: MySQL에서만 식별 가능한 에러번호
• SQL-STATE: ANSI SQL 표준 DBMS의 상태 코드

 

컨디션

DECLARE dup_key CONDITION FOR 1062;

DECLARE dup_key CONDITION FOR SQLSTATE 42000;

• 핸들러가 처리할 상황을 정의하는 객체
• 기본적으로 ERROR-NO를 매칭
• SQLSTATE를 명시하는 경우 SQLSTATE 키워드 붙이고 뒤에 번호 붙이기

 

핸들러

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_flag=1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
  ROLLBACK;
  SELECT 'Error occured';
END ;;

DECLARE EXIT HANDLER FOR 1022, 1062 SELECT 'Error occured';

• 특정 컨디션 상황에서 어떻게 처리할지 정의하는 기능
• type
  • CONTINUE: 에러가 발생하면 처리하고 다시 돌아옴
  • EXIT: 에러가 발생하면 처리하고 실행 블록을 벗어남

 

시그널

• 예외를 직접 발생시키는 키워드
• BEGIN ... END 안에서 SIGNAL 사용

CREATE FUNCTION sf_divide (p_dividend INT, p_divisor INT)
  RETURN INT
BEGIN
  DECLARE null_divisor CONDITION FOR SQLSTATE '45000';
  
  IF p_divisor IS NULL THEN
    SIGNAL null_divisor
      SET MESSAGE_TEXT = 'Divisor can not be null', MYSQL_ERRNO=9999;
  ELSEIF p_divisor=0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Divisor can not be 0', MYSQL_ERRNO=9998;
  ELSEIF p_dividend IS NULL THEN
    SIGNAL SQLSTATE '01000'
      SET MESSAGE_TEXT = 'Dividend is null', MYSQL_ERRNO=9997;  
    RETURN 0;
  
  END IF;
  
  RETURN FLOOR(p_dividend / p_divisor);
END;;

 

• 핸들러 코드에서 SIGNAL 사용 

CREATE PROCEDURE sp_remove_user (IN p_userid INT) 
BEGIN 
  DECLARE v_affectedrowcount INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION;
    BEGIN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT='Can not remove user information', MYSQL_ERRNO=9999
    END;
    
  DELETE FROM tb_user WHERE user_id=p_userid;
  SELECT ROW_COUNT() INTO v_affectedrowcount;
  IF v_affectedrowcount <> 1 THEN SIGNAL SQLSTATE '45000'; END IF;
   
END;;

 

커서

• 데이터 결과 셋
  
  • 전 방향 읽기만 가능
  • 커서의 컬럼을 바로 업데이트하는 것이 불가
• 센서티브 커서
  • 레코드에 대한 정보를 실제 레코드의 포인터만으로 유지하는 형태
  • 별도 임시 테이블로 레코드를 복사하지 않으므로 커서의 오픈이 빠름
  • 칼럼의 데이터 변경 및 삭제 가능
• 인센서티브 커서
  • 일치하는 레코드를 별도의 임시 테이블로 복사해서 가지고 있는 형태
  • 조회에 부합되는 결과를 우선적으로 임시 테이블로 복사해야 되므로 느림
  • 컬럼 조회만 가능

 

CREATE FUNCTION sf_emp_count(p_dept_no VARCHAR(10))
  RETURNS BIGINT
BEGIN
  DECLARE v_total_count INT DEFAULT 0;
  DECLARE v_no_more_data TINYINT DEFAULT 0;
  DECLARE v_emp_no INTEGER;
  DECLARE v_from_date DATE;
  DECLARE v_emp_list CURSOR FOR
  
  SELECT emp_no, from_date FROM dept_emp WHERE dept_no=p_dept_no;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_date = 1;
  
  OPEN v_emp_list;
  REPEAT
    FETCH v_emp_list INTO v_emp_no, v_from_date;
    IF v_emp_no > 20000 THEN
      SET v_total_count = v_total_count + 1;
    END IF;
  UNTIL v_no_more_data END REPEAT;  
    
  CLOSE v_emp_list;
  
  RETURN v_total_count;
END ;;

• 정의된 커서를 오픈하여 실제 쿼리의 결과를 가져옴
• 오픈된 커서는 페치 명령으로 레코드 단위로 읽어서 사용
• 사용이 완료된 후에 CLOSE 명령으로 커서 및 관련 자원을 해제