MariaDB / Procedure

MariaDB 저장 프로시저(Stored procedure)는 이름을 붙여 데이터베이스에 저장한 SQL 작업이며 CALL로 실행합니다. 입력값을 받고 여러 문장을 실행하거나 결과 집합·출력 매개변수를 돌려줄 수 있습니다. 프로시저를 만들었다는 사실만으로 작업 전체가 자동 트랜잭션이 되거나 더 빠르고 안전해지는 것은 아니므로 권한과 실행 범위를 함께 설계해야 합니다.

준비 사항과 매개변수 없는 프로시저

실습용 learning 데이터베이스에서 CREATE·INSERT·SELECT·UPDATE와 CREATE ROUTINE·EXECUTE·ALTER ROUTINE 권한이 필요합니다. 같은 실습 이름이 있으면 별도 이름을 사용합니다. 아래 SQL은 일반 MariaDB 모드의 mariadb 명령행 클라이언트를 기준으로 하며, 모든 예제를 같은 접속에서 차례로 실행합니다.

USE learning;
CREATE TABLE procedure_item_demo (
    id INT PRIMARY KEY,
    category CHAR(1) NOT NULL,
    name VARCHAR(30) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO procedure_item_demo VALUES
    (1,'A','Notebook',10.00,2),
    (2,'A','Pencil',2.00,5),
    (3,'B','Ruler',5.00,3);

DELIMITER //
CREATE PROCEDURE learning.p_list_demo()
SQL SECURITY INVOKER
READS SQL DATA
BEGIN
    SELECT id, name, quantity FROM learning.procedure_item_demo ORDER BY id;
END//
DELIMITER ;
CALL learning.p_list_demo();

결과는 (1,Notebook,2), (2,Pencil,5), (3,Ruler,3)입니다. 매개변수가 없어도 CREATE PROCEDURE의 이름 뒤에는 ()를 적습니다. BEGIN ... END 안의 세미콜론은 내부 문장을 끝내고, 여기서 //는 프로시저 정의 전체의 입력을 끝냅니다. DELIMITER ;로 클라이언트 구분자를 복구한 뒤 CALL합니다.

DELIMITER는 SQL 서버 문법이 아니라 클라이언트가 긴 정의를 나누어 보내지 않도록 하는 명령입니다. PHP·드라이버 API에는 DELIMITER 줄을 보내지 않고 CREATE PROCEDURE 전체를 하나의 SQL 문자열로 전달합니다. GUI 도구도 단일 문장 실행·스크립트 실행 방식에 따라 구분자 처리가 다르므로 “GUI에서는 항상 필요 없다”라고 일반화하지 않습니다. CREATE PROCEDURE 공식 문서의 본문·매개변수 규칙을 기준으로 사용합니다.

IN으로 조건을 받고 OUT으로 값 반환

DELIMITER //
CREATE PROCEDURE learning.p_category_summary_demo(
    IN p_category CHAR(1),
    OUT p_count INT,
    OUT p_total DECIMAL(12,2)
)
SQL SECURITY INVOKER
READS SQL DATA
BEGIN
    IF p_category IS NULL OR TRIM(p_category) = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Category is required';
    END IF;
    SELECT COUNT(*), COALESCE(SUM(price * quantity), 0)
      INTO p_count, p_total
    FROM learning.procedure_item_demo
    WHERE category = p_category;
END//
DELIMITER ;
CALL learning.p_category_summary_demo('A', @item_count, @total_value);
SELECT @item_count AS item_count, @total_value AS total_value;
CALL learning.p_category_summary_demo('C', @item_count, @total_value);
SELECT @item_count AS item_count, @total_value AS total_value;

A의 결과는 2·30.00이고 해당 행이 없는 C는 0·0.00입니다. SELECT ... INTO는 조회 값을 매개변수에 넣고, 결과를 표 형태로 직접 반환하는 앞의 SELECT와 구분됩니다. OUT을 받는 @item_count·@total_value는 접속별 사용자 변수이므로 CALL과 SELECT를 같은 접속에서 실행합니다. OUT 값은 프로시저 진입 시 NULL에서 시작하며, 이전 값을 보존하는 인수는 INOUT입니다. IN 매개변수 값을 내부에서 바꿔도 호출자의 입력 변수를 자동으로 수정하지는 않습니다.

NULL·빈 분류는 SIGNAL로 거부합니다. 입력값 검사와 SQL 값 전달을 구분하며, 애플리케이션에서 CALL에 사용자 입력을 넣을 때는 드라이버의 매개변수 바인딩을 사용합니다. 문자열을 이어 붙여 동적인 SQL을 만들 필요가 없는 조회라면 위처럼 열과 매개변수의 이름을 명확히 구분해 비교합니다.

INOUT으로 받은 값을 바꾸기

DELIMITER //
CREATE PROCEDURE learning.p_bump_demo(INOUT p_counter INT)
SQL SECURITY INVOKER
BEGIN
    SET p_counter = COALESCE(p_counter, 0) + 1;
END//
DELIMITER ;
SET @visits = 10;
CALL learning.p_bump_demo(@visits);
SELECT @visits;
SET @visits = NULL;
CALL learning.p_bump_demo(@visits);
SELECT @visits;

첫 결과는 11, 둘째는 1입니다. NULL을 0으로 보는 것은 이 예제에서 정한 규칙이며 모든 누적값의 기본 정책은 아닙니다. OUT·INOUT 자리에는 결과를 받을 변수가 필요하므로 CALL p_bump_demo(10)처럼 리터럴을 전달하지 않습니다. 프로시저 내부에서 다른 루틴을 호출할 때는 해당 문맥에 맞는 지역 변수·매개변수를 사용할 수도 있습니다. 호출 규칙은 CALL 문서에서 확인할 수 있습니다.

데이터 변경과 트랜잭션 경계

다음 프로시저는 한 행의 수량을 바꾸되 내부에서 COMMIT하지 않습니다. 0은 허용하고 NULL·음수는 거부하는 실습 규칙입니다. 대상 ID가 없으면 UPDATE는 0행에 적용되므로, 필요에 따라 존재 여부와 업무상 실패를 구분하는 출력값을 추가할 수 있습니다.

DELIMITER //
CREATE PROCEDURE learning.p_set_quantity_demo(IN p_id INT, IN p_quantity INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
    IF p_quantity IS NULL OR p_quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Quantity must be nonnegative';
    END IF;
    UPDATE learning.procedure_item_demo
    SET quantity = p_quantity WHERE id = p_id;
END//
DELIMITER ;
START TRANSACTION;
CALL learning.p_set_quantity_demo(1, 9);
SELECT id, quantity FROM learning.procedure_item_demo WHERE id = 1;
ROLLBACK;
SELECT id, quantity FROM learning.procedure_item_demo WHERE id = 1;

조회는 (1,9) 뒤에 (1,2)를 반환합니다. InnoDB의 커밋 전 변경이므로 호출자가 ROLLBACK으로 되돌릴 수 있습니다. CALL 자체가 여러 변경을 모두 원자적으로 처리한다는 뜻은 아닙니다. 프로시저가 내부에서 COMMIT하거나 DDL을 실행하면 호출자가 기대한 경계를 깨뜨릴 수 있으므로, 트랜잭션을 누가 시작하고 끝낼지 인터페이스로 정합니다. BEGIN ... END는 문장 묶음이며 START TRANSACTION의 대체가 아닙니다.

프로시저 생성 시의 SQL 모드·문자셋 등의 조건도 저장됩니다. 개발·운영 환경의 차이와 예외 처리 정책을 검토하고, 루틴 안의 오류가 생겼다는 사실만으로 앞선 모든 변경이 자동 복구되었다고 판단하지 않습니다. 애플리케이션에서 결과 집합이 여러 개 반환되는 CALL을 사용하면 다음 SQL 실행 전에 드라이버 규칙에 따라 결과를 모두 처리해야 합니다.

목록·정의·권한 확인과 변경

SHOW PROCEDURE STATUS WHERE Db = 'learning';
SHOW CREATE PROCEDURE learning.p_category_summary_demo;
SELECT ROUTINE_NAME, SECURITY_TYPE, DEFINER
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'learning' AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;

SHOW CREATE PROCEDURE로 매개변수와 본문·SQL 모드를 확인해 변경 전에 백업합니다. 정의가 NULL로 보이거나 조회되지 않으면 루틴 소유 여부와 정의 조회 권한을 확인합니다. SQL SECURITY INVOKER를 명시한 이 예제는 호출자의 권한으로 테이블을 읽고 바꾸므로 EXECUTE 권한만 있다고 모든 테이블 작업이 허용되지는 않습니다. 기본인 DEFINER 방식은 정의자의 권한을 사용하므로 정의 계정·최소 권한·호출 범위를 별도로 관리해야 합니다.

저장 루틴 권한 문서를 참고해 실행 권한과 데이터 권한을 구분합니다. SQL SECURITY를 편의상 높은 권한으로 바꾸기보다 해당 루틴이 제공해야 할 동작을 좁힙니다.

ALTER PROCEDURE learning.p_bump_demo COMMENT 'Counter increment example';
SHOW CREATE PROCEDURE learning.p_bump_demo;

ALTER PROCEDURE로 주석·보안 방식 같은 특성을 바꿀 수 있지만 매개변수나 본문은 바꿀 수 없습니다. 본문을 바꾸려면 백업·의존 코드·권한을 확인한 뒤 다시 정의합니다. 다음은 이 실습의 p_bump_demo만 증가량 2로 교체하는 예입니다. 기존 운영 이름에 무조건 적용하지 않습니다.

DELIMITER //
CREATE OR REPLACE PROCEDURE learning.p_bump_demo(INOUT p_counter INT)
SQL SECURITY INVOKER
BEGIN
    SET p_counter = COALESCE(p_counter, 0) + 2;
END//
DELIMITER ;
SET @visits = 10;
CALL learning.p_bump_demo(@visits);
SELECT @visits;

교체 뒤 결과는 12입니다. ALTER PROCEDURE의 변경 범위와 CREATE OR REPLACE의 권한 보존 동작을 구분하세요. 단순 DROP 뒤 CREATE는 루틴별 권한과 의존 코드에 영향을 줄 수 있으므로 정의 백업만으로 배포 검토를 끝내지 않습니다.

실습 프로시저 삭제

DROP PROCEDURE learning.p_list_demo;
DROP PROCEDURE learning.p_category_summary_demo;
DROP PROCEDURE learning.p_bump_demo;
DROP PROCEDURE learning.p_set_quantity_demo;
SELECT COUNT(*) AS remaining_demo_procedures
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'learning'
  AND ROUTINE_NAME IN ('p_list_demo','p_category_summary_demo',
                       'p_bump_demo','p_set_quantity_demo');
SELECT id, quantity FROM learning.procedure_item_demo ORDER BY id;

DROP에서는 이름 뒤에 ()를 붙이지 않습니다. 남은 실습 루틴 수는 0이지만 테이블과 데이터는 남아 수량 2·5·3을 확인할 수 있습니다. 프로시저를 삭제해도 과거 호출로 커밋된 데이터 변경이 취소되는 것은 아니므로, 정의와 데이터 복원을 구분해서 계획합니다.