mysql, 스토어드 프로그램(프로시저, 함수, 이벤트, 트리거 등)

2024-04-19 • infoqoch •

mysql

•

mysql

스토어드 프로그램(stored program)

스토어드 프로그램(스토어드 루틴)은 절차적인 처리를 위한 MySQL의 기능.
스토어드 프로그램은 다음을 포함함.
- 스토어드 프로시저
- 스토어드 함수
- 트리거
- 이벤트

장점

데이터베이스의 보안 향상
- 스토어드 프로그램 단위로 실행 권한을 부여
- SQL 인젝션 같은 보안 사고 방어
기능의 추상화: 스토어드 프로그램을 사용하여 개발 언어나 도구와 관련 없는 특정 기능을 구현할 수 있음
절차적 기능 구현과 네트워크 소요시간 절감
- SQL의 실행 결과는 빠르더라도 네트워크 경유 시간은 많은 자원을 사용.
- 프로그램에서 여러 번의 쿼리를 통해 데이터를 호출하는 것보다, 스토어드 프로그램으로 로직을 구현하여 한 번의 네트워크를 사용하는 것이 나음.
- 절차적 기능 구현을 위한 IF, WHILE 등 기능을 제공
개발 업무의 구분: SQL 개발조직이 존재할 경우, 해당 조직에서 스토어드 프로그램을 통한 API 제공 가능

단점

낮은 처리 성능
- 절차적 코드 처리가 다른 프로그램 언어에 비하여 상대적으로 느림
- 특히 MySQL은 실행 시마다 스토어드 프로그램을 파싱하는 문제 존재
애플리케이션 코드의 조각화: 애플리케이션 로직과 DBMS 로직의 분산으로 인한 복잡도 상승

문법

헤더 부문과 본문 부분으로 나뉨
- 헤더: 스토어드 프로그램의 이름, 입출력 값의 정의, 보안과 작동 관련한 옵션
- 본문(바디): 실행하는 내용
MySQL 8.0 이후 시스템 내부에서 스토어드 프로그램을 관리. 헤더는 변경(ALTER) 가능하지만 바디는 DROP 후 CREATE 해야 함.

사용 시 주의사항

IGNORE_SPACE: MySQL은 함수의 이름과 괄호 사이의 공백에 엄격하다. 해당 규칙을 지키거나, 공백에 엄격하지 않도록 IGNORE_SPACE 모드를 설정한다.
thread_stack: 32비트 운영체제의 경우 스택의 크기를 512K 이상으로 늘릴 필요가 있다.
만약 프로그램 생성 과정에서 에러가 발생할 경우 다음 링크를 참고하여 시스템 변수를 설정하자.
- SET GLOBAL log_bin_trust_function_creators = 1;
- https://www.leafcats.com/271

스토어드 프로시저

스토어드 프로시저는 서로 데이터를 주고받아야 하는 여러 쿼리를 하나의 그룹으로 묶어서 독립적으로 실행하기 위해 사용한다.
스토어드 프로시저는 반드시 독립적으로 호출돼야 하며, SELECT, UPDATE 등 SQL 문장에서 참조할 수 없다.
CALL storedProcedure();와 같이 CALL 키워드와 함께 사용한다.

스토어드 프로시저 생성 및 삭제

BEGIN부터 END까지를 바디라 하며 그 앞의 내용은 헤더이다.
스토어드 프로시저는 반환값이 없다. 즉 RETURN 명령이 없으며, 리턴 대신 OUT 타입의 파라미터로 대체한다.
- IN: 입력 전용 파라미터. 스토어드 프로시저 내부에서는 읽기 전용으로 사용된다.
- OUT: 출력 전용 파라미터. 프로시저 외부에서 내부로 값을 전달할 때 사용할 수 없다. 프로시저 실행 완료 후 외부 호출자로 값을 전달하는 용도로 사용된다.
일반 쿼리는 ;으로 그 끝을 구분한다. 스토어프 프로시저 내부에서는 여러 쿼리를 함께 사용하기 때문에 ;가 반복되어 사용될 수 있다. 프로시저 생성 쿼리의 종료를 구분하기 위하여, 구분자를 DELIMITER ;; 형태로 정의한 후 해당 프로시저의 끝에 ;;를 사용한다. 그 후 다시 DELIMITER ;로 재정의하여 구분자가 기본값인 ;가 될 수 있도록 변경한다.
프로시저의 생성과 삭제는 [CREATE|DROP] PROCEDURE의 형태로 수행한다. 프로시저의 보안 및 작동 방식을 변경할 때는 ALTER PROCEDURE를 사용하여 변경할 수 있다. 하지만 바디의 수정은 불가능하므로 DROP 후 CREATE로 재작성한다.

DELIMITER ;;

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

DROP PROCEDURE sp_sum;;

DELIMITER ;

스토어드 프로시저 실행

스토어드 프로시저는 SELECT이 아닌 CALL 명령어로 실행한다.
IN의 경우 상수값을 직접 입력할 수 있다. 하지만 OUT의 경우 MySQL의 사용자 변수를 사용해야 한다.

select @result; -- null

CALL sp_sum(1, 2, @result);

select @result; -- 3

스토어드 프로시저의 커서 반환

스토어드 프로그램은 명시적으로 커서를 파라미터로 전달받거나 반환할 수 없다. 프로시저 내에서 커서를 오픈하지 않거나 SELECT 쿼리의 결과 셋을 페치(fetch)하지 않으면, 해당 쿼리의 결과 셋은 클라이언트로 바로 전송된다.
아래의 sp_selectEmployees를 실행하면, select 쿼리의 결과가 클라이언트에 바로 전달된다. 이런 특성으로 인해 스토어드 프로시저 개발 과정에서 디버깅 용도로 사용한다.

CREATE PROCEDURE sp_selectEmployees (IN in_empno INTEGER )
BEGIN
SELECT * FROM employees WHERE emp_no=in_empno;
END;;
  
call sp_selectEmployees(10001); -- out과 관계 없이 select의 값이 클라이언트에 전달된다.

스토어드 프로시저 딕셔너리

MySQL 8.0 이후로 스토어드 프로시저는 proc테이블에서 시스템 테이블로 저장 위치가 변경되었으며, 아래와 같은 방식으로 조회만 가능하다.

SELECT routine_schema, routine_name, routine_type, ROUTINE_DEFINITION, routine_body
FROM information_schema.ROUTINES
WHERE routine_schema ='your_schema' and routine_type='PROCEDURE' ;

스토어드 함수

스토어드 함수는 하나의 SQL 문장으로 작성이 불가능한 기능을 하나의 SQL 문장으로 구현해야 할 때 사용한다.
스토어드 함수는 SELECT 쿼리와 함께 사용하는 것을 제외하고는 스토어드 프로시저에 대비하여 제약사항이 많다.
MySQL 8.0 이후 LETERAL과 윈도우 함수 등이 있어서 스토어드 함수의 필요성이 이전 버전보다 낮아졌다.

스토어드 함수 생성 및 삭제

[CREATE|DROP] FUNCTION 의 형태로 생성하거나 삭제한다.
모든 입력 파라미터는 읽기 전용이다. 리턴 값은 RETURNS 키워드로 반환 타입을 정의한다. RETURN은 RETURNS에 정의한 데이터 타입으로 정의한다.

CREATE FUNCTION sf_sum(param1 INTEGER, param2 INTEGER)
  RETURNS INTEGER
BEGIN
  DECLARE param3 INTEGER DEFAULT 0;
  SET param3 = param1 + param2;
  RETURN param3;
END;;

select sf_sum(1,2); -- 3

스토어드 함수는 스토어드 프로시저와 달리 다양한 제약사항이 존재한다.
- PREPARE와 EXECUTE 명령을 이용한 프리페어 스테이트먼트를 사용할 수 없다.
- 명시적 또는 묵시적인 ROLLBACK/COMMIT을 유발하는 SQL 문장을 사용할 수 없다.
- 재귀 호출을 사용할 수 없다.
- 스토어드 함수 내에서 프로시저를 호출할 수 없다.
- 결과 셋을 반환하는 SQL 문장을 사용할 수 없다.

스토어드 함수 실행

스토어드 함수는 스토어드 프로시저와 달리 CALL 명령으로 실행할 수 없다. SELECT 문장으로 실행한다.

select sf_sum(1,2);

트리거

트러기는 어떤 테이블의 레코드가 저장되거나 변경될 때, 미리 정의해둔 작업을 자동으로 실행하는 스토어드 프로그램이다.
테이블 레코드가 INSERT, UPDATE, DELETE될 때 시작하도록 설정할 수 있다.
칼럼의 유효성 체크, 다른 테이블로의 복사나 백업, 계산된 결과를 다른 테이블에 함께 업데이트하는 등, 전후처리 작업을 트리거로 처리한다.
트리거의 필요성은 다소 낮다.
- 애플리케이션에서 해당 기능을 개발하더라도 성능 상 큰 차이를 가지지 않는다.
- 오히려 트리거가 생성돼 있는 테이블의 칼럼을 추가하거나 삭제할 때 추가적인 성능을 요구한다.
복제를 ROW로 할 경우, 트리거의 결과가 바이너리 로그로 기록되기 때문에 레플리카 서버의 데이터가 소스 서버와 동일하다. STATEMENT의 경우, 레플리카에서 트리거가 실행되므로 결과는 같더라도 실행 위치가 다를 수도 있다.

트리거 생성

CREATE TRIGGER 명령어로 생성한다.
실행될 이벤트(INSERT,UPDATE,DELETE)와 시점(BEFORE, AFTER)을 명시할 수 있다.
FOR EACH ROW 키워드를 사용하여 개별 레코드 단위로 트리거가 실행되게 한다.
NEW OLD를 사용하여 테이블이 변경전 레코드/변경될 레코드를 가리킬 수 있다.

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

트리거 실행

트리거의 동작을 확인하는 방법은 직접 영향을 받는 테이블의 레코드를 조작해야 한다.
위의 트리거가 동작함을 아래 쿼리를 통해 확인 가능하다. employees의 레코드를 삭제할 때 삭제한 레코드의 emp_no가 가진 salaries의 레코드가 삭제된다.

-- 1건
select *
from employees
where emp_no = 10010;

-- 6건
select *
from salaries
where emp_no = 10010;
  
delete from employees where emp_no = 10010; -- salaries의 6건이 삭제된다.

트리거 딕셔너리

MySQL 8.0 이전에는 *.TRG라는 파일로 저장되었으나, 현재는 시스템 테이블에 통합 저장된다. 아래 쿼리를 통해 조회만 가능하다.

SELECT trigger_schema, trigger_name, event_manipulation, action_timing, action_statement
FROM information_schema.TRIGGERS
WHERE trigger_schema='employees';

이벤트

특정한 시간에 스토어드 프로그램을 실행할 수 있는 스케줄러 기능을 이벤트라고 한다.
MySQL에는 스케줄링을 전담하는 스레드가 있으며, 해당 스레드가 활성화된 경우에만 이벤트가 실행된다.
해당 스레드는 event_scheduler 시스템 변수가 ON이나 1로 설정될 때 활성화 된다.

SHOW GLOBAL VARIABLES LIKE 'event_scheduler'; -- on이 되어야 함.
SHOW PROCESSLIST; -- User:event_scheduler가 있어야 함.

이벤트 생성

이벤트는 반복 실행 여부에 따라 크게 일회성 이벤트와 반복성 이벤트로 나눌 수 있다.
일회성 이벤트는 아래와 같이 SCHEDULE AT으로 정의한다. 해당 이벤트 생성 후 1초 뒤 하나의 레코드를 insert 한다.

create table hello(world varchar(100) primary key);

select * from hello; -- 0 row

CREATE EVENT onetime_job
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
DO
    INSERT INTO hello VALUES ('world') ;

select * from hello; -- 1 row

아래와 같이 SCHEDULE EVERY를 사용하여 여러 차례 수행하도록 이벤트를 생성할 수 있다.

CREATE EVENT several_times_job
    ON SCHEDULE EVERY 1 SECOND STARTS now() ENDS date_add(now(), interval 10 second )
DO
    INSERT INTO hello VALUES (concat('world', now())) ;

DO 절에서는 아래와 같이 BEGIN - END를 사용하여 여러 개의 SQL을 사용하거나 스토어드 프로시저를 호출할 수 있다.

CREATE EVENT several_times_job
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
  DO BEGIN
    INSERT INTO hello VALUES ('my world');
    INSERT INTO hello VALUES ('your world');
  END;;

이벤트는 완전하게 종료된 경우 자동으로 삭제된다. ON COMPLETION PRESERVE 옵션을 사용할 경우 완전하게 종료되더라도 삭제하지 않는다.
이벤트는 ENABLE, DISABLE, DISABLE ON SLAVE의 상태를 가진다. 이벤트가 생성되면, 해당 이벤트는 레플리카 서버에 자동으로 복제되며 DISABLE ON SLAVE 상태를 가진다. 만약 레플리카 서버가 소스 서버로 승격(PROMOTION)될 경우, 수동으로 이벤트의 상태를 ENABLE로 변경해야 해당 이벤트가 실행된다.

SELECT event_schema, event_name
FROM information_schema.EVENTS
WHERE STATUS = 'SLAVESIDE_DISABLED';

ALTER EVENT myevent ENABLE; -- enable 상태로 변경

이벤트 실행 및 결과 확인

아래와 같이 이벤트를 조회할 수 있다.
아래는 ON COMPLETION PRESERVE을 옵션으로 설정하였으므로 실행 후에도 해당 이벤트가 삭제되지 않고 조회할 수 있다.

CREATE EVENT onetime_job 
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
  ON COMPLETION PRESERVE 
DO
  INSERT INTO hello VALUES ('world') ;

SELECT *
FROM information_schema.EVENTS;

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

모든 스토어드 프로그램은 동일한 문법의 본문부(BEGIN…END)를 사용한다.

BEGIN END 블록과 트랜잭션

스토어드 프로시저와 이벤트에서 트랜잭션을 사용할 수 있다.

CREATE TABLE tb_hello (name VARCHAR(100), message VARCHAR (100));

CREATE PROCEDURE sp_hello (IN name VARCHAR (50))
BEGIN
  -- START TRANSACTION;
  INSERT INTO tb_hello VALUES (name, CONCAT('Hello', name));
  -- COMMIT;
END;;

위에서 주석을 살릴 경우, 프로시저 내부에서 트랜잭션 로직을 완료할 수 있다. 외부에서는 해당 트랜잭션을 통제할 수 없다.
위에서 주석을 유지할 경우, 해당 로직이 외부의 트랜잭션에 의존한다.
위에서 주석을 유지한 경우, 아래 쿼리 묶음을 실행할 경우 데이터가 insert 되지 않고 롤백된다.

START TRANSACTION;
CALL sp_hello ('First');
ROLLBACK;
SELECT * FROM tb_hello; -- 없음

반대로 스토어드 함수나 트리거는 프로그램 본문 내에서 트랜잭션을 완료(커밋하거나 롤백)할 수 없다. 프로시저 외부에서의 커밋 및 롤백 여부에 영향을 받는다.

변수

BEGIN … END 블록 사이의 변수는 해당 블록에서만 사용 가능하다. 로컬 변수라 한다.
사용자 변수(@my_var)는 전역에서 사용되므로, 사용 과정에서 사이드 이펙트를 만들 수 있다. 더불어 성능 상 불리하다. 가능하면 로컬 변수를 주로 사용한다.
로컬 변수는 DECLARE 명령으로 정의하고 반드시 타입을 명시한다.
로컬 변수에 값을 할당하기 위해서는 SET, SELECT INTO 키워드를 사용한다.
- SELECT INTO의 레코드 결과는 반드시 하나여야 한다.
- 이를 위해서 LIMIT 1을 사용하거나 커서를 사용한다.
변수는 중복되어 사용할 수 있으며 그것의 우선순위는 다음과 같다.
- DECLARE로 정의한 로컬 변수
- 스토어드 프로그램의 입력 파라미터
- 테이블의 칼럼
아래는 employees 테이블의 first_name과 파라미터 IN first_name, DECLARE의 first_name이 혼용되었다.
어떤 값을 넣더라도 우선순위인 DECLARE first_name에 따라 ‘Kim’을 리턴한다.

CREATE PROCEDURE sp_hello (IN first_name VARCHAR (50))
BEGIN
    DECLARE first_name VARCHAR (50) DEFAULT 'Kim' ;
    SELECT CONCAT('Hello', first_name) FROM employees LIMIT 1;
END ;;

call sp_hello(null); -- HelloKim
call sp_hello('lee'); -- HelloKim
SELECT first_name FROM employees LIMIT 1; -- Aamer
SELECT CONCAT('Hello', first_name) FROM employees LIMIT 1; -- HelloAamer

위와 같은 문제를 우회하기 위하여 변수의 이름을 다르게 만든다. 보통은 파라미터는 p_{name}, declare는 v_{name}로 이름을 짓는다.

CREATE PROCEDURE sp_hello2 (IN p_first_name VARCHAR (50))
BEGIN
    DECLARE v_first_name VARCHAR (50) DEFAULT 'Kim' ;
    SELECT CONCAT('Hello', first_name) FROM employees LIMIT 1;
END ;;

제어문

IF문은 다음 두 가지 방식으로 사용할 수 있다.
- IF, ELSEIF, ELSE, END IF
- CASE WHEN, THEN, ELSE, END CASE

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

select
    sf_greatest(null, 1)
    , sf_greatest(2,    null)
    , sf_greatest(3, 4)
    , sf_greatest(6, 5)
;

반복루프를 두 가지 방식으로 사용할 수 있다.
- LOOP, LEAVE, END LOOP
- REPEAT, UNTIL END REPEAT

CREATE FUNCTION sf_factorial1(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;

select sf_factorial1(5); -- 120

핸들러와 컨디션을 이용한 에러 핸들링

MySQL은 아래와 같은 구조로 에러 메시지를 제공한다.
ERROR 1146 (42S02): Table 'test.not_found_table' doesn't exist
- ERROR-NO: MySQL이 제공하는 에러 코드로, ERROR 1146처럼 제공한다.
- SQL-STATE: ANSI표준으로서 모든 DBMS는 동일한 값에 대해서 동일한 의미를 가진다. 다섯 개의 숫자와 알파뱃으로 구성되며 42S02와 같이 제공한다.
- ERROR-MESSAGE: 사람이 읽을 수 있는 형태로 에러 메시지를 제공한다.
바디에서는 이러한 에러를 제어할 수 있도록 핸들러를 제공한다.
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_flag = 1;
- 위와 같이 핸들러를 정의한 경우, 다음과 같은 의미를 가진다.
- SQLEXCEPTION(SQL-STATE가 00, 01, 02로 시작하는 에러)가 발생할 경우,
  - error_flag 변수의 값을 1로 할당하고
  - 마지막에 실행한 스토어드 프로그램 코드로 돌아가 계속 실행(CONTINUE)한다.
SQLEXCEPTION은 컨디션으로서 00, 01, 02로 시작하는 SQL-STATE 전체를 대표한다. 여기서 컨디션이란, 핸들러가 어떤 조건이 발생할 때 실행할지를 정의하는 방법 중 하나로서, SQL-STATE를 외우지 않고 사람이 읽기 쉽도록 미리 정의한 조건을 의미한다. SQLEXCEPTION 외에 SQLWARNING, NOT FOUND 등이 있으며 이 세개의 컨디션은 MySQL에서 이미 정의한 컨디션이다.
아래는 컨디션을 활용한 스토어드 함수 예제이다. 다음과 같이 사용자 정의 컨디션을 정의했다.
- DECLARE dup_key CONDITION FOR 1062;
  - dup_key란 컨디션이 정의되었다.
  - 1062은 중복에 대한 에러 코드이다.
- 핸들러는 CONTINUE가 아닌 EXIT이 옵션을 사용하였다. 해당 핸들러가 발생할 경우 로직을 바로 종료한다. -1을 리턴하고 종료한다.

CREATE FUNCTION sf_testfunc()
    RETURNS BIGINT
BEGIN
    DECLARE dup_key CONDITION FOR 1062;
    DECLARE EXIT HANDLER FOR dup_key
        BEGIN
            RETURN -1;
        END;
    INSERT INTO hello VALUES ('world');
    RETURN 1;
END;;

select sf_testfunc(); -- 결과: 1
select sf_testfunc(); -- 결과: -1

반대로 예외를 던질 수 있다. 이를 시그널(SIGNAL)이라 한다. 자바의 throw에 대응한다.
45000 SQL-STATE는 ‘정의되지 않은 사용자 오류’를 의미 한다.
SIGNAL nullpointex;의 형태로 45000 에러가 발생하는 것을 유도하였다.

CREATE FUNCTION sf_greatest3 (p_value1 INT, p_value2 INT)
    RETURNS INT
BEGIN
    DECLARE nullpointex CONDITION FOR SQLSTATE '45000'; -- asni 에러코드
    IF p_value1 IS NULL OR p_value2 IS NULL THEN SIGNAL nullpointex;
    ELSEIF p_value1 >= p_value2 THEN RETURN p_value1;
    ELSE RETURN p_value2;
    END IF;
END;;

select sf_greatest3(null, 1); -- 45000 에러 발생

아래는 SQLEXCPTION이 발생할 경우 아래 핸들러가 동작하도록 유도한다. 이를 통하여 다영한 조건을 유연하게 수용하는 프로시저를 구현할 수 있다.

DROP PROCEDURE sp_remove_hello_world;
CREATE PROCEDURE sp_remove_hello_world (IN p_value VARCHAR(100))
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 hello WHERE world = p_value;
    SELECT ROW_COUNT() INTO v_affectedrowcount;
    IF v_affectedrowcount <> 1 THEN
        SIGNAL SQLSTATE '45000';
    END IF;
END;;

drop table hello;
call sp_remove_hello_world('hello'); -- SQLEXCEPTION이 발생하며, 정의한 에러가 발생

create table hello(world varchar(100) primary key);
call sp_remove_hello_world('world'); -- v_affectedrowcount가 0이며, 정의한 에러가 발생

insert into hello(world) values ('world');
call sp_remove_hello_world('world'); -- 정상 처리

커서

스토어드 프로그램은 커서를 지원한다. 다만, JDBC ResultSet과 달리 제한적으로 사용 가능하다.
- 커서는 전 방향(전진) 읽기만 가능하다.
- 커서의 칼럼을 바로 업데이트하는 것이 불가능하다.
DBMS의 커서는 센서티브 커서와 인센서티브 커서로 구분할 수 있다.
- 센서티브(Sensitive) 커서: 커서는 일치하는 레코드에 대한 정보를 실제 레코드의 포인터만으로 유지하는 형태. 커서를 사용하여 데이터 변경 및 삭제가 가능. 칼럼 값이 변경돼더라도 커서에 바로 반영.
- 인센서티브(Insensitive) 커서: 커서에 일치하는 레코드를 별도의 임시 테이블에 복사하는 형태. 임시 테이블에 복사하므로 속도가 느리고 커서를 사용해 해당 레코드에 데이터 변경할 수 없음.
- MySQL은 두 개의 커서 방식을 혼합하는 어센서티브(Asensitive) 방식을 사용한다. 임시테이블의 사용 여부를 알 수 없으며, 커서를 활용한 칼럼의 삭제 변경이 불가능하다.
스토어드 프로그램에서 커서는 OPEN과 CLOSE로 열고 닫으며, 오픈된 커서를 FETCH 명령어로 레코드 단위로 읽는다.

CREATE FUNCTION sf_emp_count(p_dept_no VARCHAR(10))
  RETURNS BIGINT
  DETERMINISTIC
  SQL SECURITY INVOKER
BEGIN
  /* 사원 번호가 20000보다 큰 사원의 수를 누적하기 위한 변수 */
  DECLARE v_total_count INT DEFAULT 0;
  /* 커서에 더 읽어야 할 레코드가 남아 있는지 여부를 위한 플래그 변수 */
  DECLARE v_no_more_data TINYINT DEFAULT 0;
  /* 커서를 통해 SELECT된 사원 번호를 임시로 담아 둘 변수 */
  DECLARE v_emp_no INTEGER;
  /* 커서를 통해 SELECT된 사원의 입사 일자를 임시로 담아 둘 변수 */
  DECLARE v_from_date DATE;
  /* v_emp_list라는 이름으로 커서 정의 */
  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_data = 1;

  /* 정의된 v_emp_list 커서를 오픈 */
  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;

  /* v_emp_list 커서를 닫고 관련 자원을 반납 */
  CLOSE v_emp_list;

  RETURN v_total_count;
END;;

select sf_emp_count('d002');

스토어드 프로그램의 보안 옵션

MySQL 8.0 이후부터는 스토어드 프로그램의 생성 및 변경 권한이 세분화 되었다.

DEFINER와 SQL SECURITY 옵션

DEFINER: 스토어드 프로그램의 소유권과 실행 권한.
SQL SECURITY: 스토어드 프로그램을 실행할 때, 누구의 권한으로 실행할지 결정하는 옵션.
- DEFINER: 생성한 사용자
- INVOKER: 호출한 사용자
예를 들면 다음과 같다.
- 어떤 함수를 user1@%가 생성하였고, user2@%가 실행한다.
- SQL SECURITY=DEFINER: user1이 스토어드 프로그램에 대한 실행 권한, 각 테이블에 대한 권한을 가져야 한다.
- SQL SECURITY=INVOKER: user2이 스토어드 프로그램에 대한 실행 권한, 각 테이블에 대한 권한을 가져야 한다.
DEFINER는 모든 스토어드 프로그램이 가지는 옵션. SQL SECURITY는 스토어드 프로시저, 스토어드 함수, 뷰만 가지며, 해당 옵션을 설정하지 않을 경우 DEFINER를 기본값으로 한다.
일반 사용자가 사용할 수 없는 테이블에 대한 조회 및 변경에 대하여, 스토어드 프로그램을 SQL SECURITY=DEFINER로 설정하여 관리자 권한을 제공할 경우 해당 작업을 수행할 수 있다. 이를 통한 복잡한 권한 문제를 해결할 수 있다.
생성한 프로그램의 DEFINER를 다른 사용자로 설정할 수 있다. 이때는 SET_USER_ID(SUPER) 권한이 필요하다. 해당 프로그램을 SYSTEM_USER 권한을 가진 사용자로 설정하려면 SYSTEM_USER 권한이 필요하다.

현업에서 이에 대한 문제가 발생한 적이 있었습니다. 보안 관련 시스템 관리 과정에서 사용하지 않는 사용자가 삭제하였습니다. 다만 어떤 프로시저 함수가 SQL SECURITY가 DEFINER로 설정되었고 삭제한 사용자로 DEFINER가 할당되어 있었습니다. 이로 인하여 해당 함수는 권한을 확인할 방법이 없었고, 사용할 때 에러를 반환하였습니다. 이 문제는 DEFINER를 존재하는 유저(root)로 변경하여 해결하였습니다. 유저를 삭제할 때는 꼭 프로시저 프로그램의 DEFINER를 확인하시거나 절대 삭제되지 않을 유저로 설정하세요~

DETERMINISTIC과 NOT DETERMINISTIC 옵션

DETERMINISTIC은 입력이 같다면 시점이나 상황에 관계 없이 결과가 항상 같다.
NOT DETERMINISTIC은 입력이 같더라도 결과가 다를 수 있다.

CREATE FUNCTION sf_getdate1()
RETURNS DATETIME
NOT DETERMINISTIC
BEGIN
  RETURN NOW () ;
END ;;

select * from tb where from_date > sf_getdate1();

위 퀴리를 실행할 경우 sf_getdate1()이 쿼리 호출 시점에서 한 번의 평가로 상수를 만들지 못하고, 레코드 마다 재평가를 한다. 이로 인하여 해당 쿼리는 테이블 풀 스캔을 수행한다.
풀 스캔을 유도하는 NOT DETERMINISTIC이 스토어드 함수의 기본값이다. 그러므로 어떤 함수를 이용하더라도 DETERMINISTIC를 설정한다.

RealMySQL 8.0, 14장을 참고하여 작성하였습니다.