if를 검색하다가 우연히 프로시저에 대한 내용을 보게 되었다.
쿼리를 함수처럼 사용한다는 걸 들어본 적은 없었기에.. 어떤 건지 알아보고자 정리를 시작했다!
✨ 프로시저란?!
여러 개의 SQL 문장을 하나의 단위로 실행하는 코드 블록이라고 할 수 있다.
일단 함수와 비교를 해보자면,
| 구분 | 프로시저 | 함수 |
|---|---|---|
| 반환 값 | 단일 값 반환은 불가능하나, OUT/INOUT으로 여러 값을 반환 가능 |
단일 값을 반환하며, RETURN을 사용 |
| 호출 방식 | CALL로 호출 | SQL 문 내에서 직접 호출 가능 (SELECT, WHERE, 등) |
| 목적 | 여러 작업(쿼리 수행, 로직 처리) 수행 | 특정 계산 또는 단일 값을 반환하는 작업 수행 |
| 입출력 매개변수 | IN, OUT, INOUT 매개변수 사용 가능 | 입력 매개변수만 사용 가능 |
| 사용 위치 | 독립적으로 호출 | 다른 SQL 문장 내에서 호출 가능 |
| SQL문 실행 | 데이터 조작(INSERT, UPDATE, DELETE) 가능 | 주로 읽기 또는 계산용으로 사용 |
| 사용 제한 | 다른 SQL 문 내에서 사용할 수 없음 | 다른 SQL 문에서 사용 가능 (SELECT, JOIN 등) |
🙄프로시저는 보통 어떤 경우에 사용하게 될까??
- 코드 재사용 : 한 번 작성 후 필요할 때마다 호출할 때 사용한다.
- 코드 추상화, 단순화 : 프로시저는 복잡한 로직을 단순화하고 추상화된 형태로 표현이 가능하다.
- 보안과 접근 제어 : 프로시저는 DB 시스템에서도 사용이 가능하다.
한 번에 수행한다는 게 단순히 생각하면 더 좋아보이지만 처리성능과 재사용면에서는 그닥이라고 한다.
- 특정 작업을 수행하기 위해 작성된 코드 블록으로, 한 가지 목적을 담고 있기에 다른 작업에 사용하기 어렵다.
- 재사용을 위해 여러 곳에서 복사, 붙여넣기로 사용할 경우 결국 코드 중복만 이루어진다. 이는 추후 유지보수를 하거나 일관성을 유지하기에 어려워진다.
✨ 권한 부여
일단 root 계정이 아닌 다른 사용자의 계정에서 프로시저를 생성하려면 권한이 부여되어야 한다..
1. 프로시저 생성 및 수정 권한 부여
GRANT CREATE, ALTER ROUTINE ON DB명.* TO '일반 계정';
FLUSH PRIVILEGES;
2. 특정 프로시저 실행 권한 부여
GRANT EXECUTE ON PROCEDURE 'DB명'.'프로시저명' TO '일반 계정'@'host';
FLUSH PRIVILEGES;
✨ 프로시저 생성 및 사용
SHOW CREATE PROCEDURE '프로시저명'; -- 프로시저 목록 확인
DROP PROCEDURE IF EXISTS '프로시저명'; -- 동일한 명칭의 프로시저가 있는 경우 DROP 처리
DELIMITER $$ -- 프로시저 작성이 완료되지 않은 상태에서 실행되는 것을 방지하지 위해
CREATE PROCEDURE '프로시저명' (
-- 파라미터 선언
이름1 타입1,
이름2 타입2
)
BEGIN
-- 변수 선언
DECLARE 이름2 타입2
-- 쿼리문1
-- 쿼리문2
END
$$
DELIMITER ;대략 위의 형식으로 작성한다고 볼 수 있다.
예를 들어 PK인 ID값에 대해 자동 증가가 없는 경우,
이를 설정해주려면 DB에서 기존 데이터의 수를 가져와서 +1을 더한 숫자를 PK로 두어 INSERT 하는 방법을 생각할 수 있다.
한 프로시저로 실행하기 위해 아래와 같이 작성하면 된다.
DELIMITER $$
CREATE PROCEDURE INSERT_USER (
IN USER_NAME VARCHAR(30),
IN USER_AGE INT
)
BEGIN
DECLARE USER_ID INT;
-- user_id 계산
SELECT COUNT(*) + 1
INTO USER_ID
FROM test_user;
-- 사용자 정보 삽입
INSERT INTO test_user (user_id, user_name, user_age)
VALUES (USER_ID, USER_NAME, USER_AGE);
END$$
DELIMITER ;여기서 DELIMITER는 구문 문자로, 문법의 끝을 나타낸다고 한다.
BEGIN과 END 사이의 SQL 문에도 세미콜론이 있다보니, 문장의 끝을 구분하기 어려워 사용한다고 한다.
이렇게 작성한 프로시저는 아래와 같이 호출할 수 있다.
CALL INSERT_USER ("1번 유저", 25);
위와 같이 데이터가 들어간 것을 볼 수 있다!
✨ IN, OUT, INOUT
프로시저를 활용해서 데이터 값을 입력 받고(IN), 내보내고(OUT), 둘 다 하는(INOUT) 기능을 만들기도 한다.
CREATE TABLE teacher_student (
id INT AUTO_INCREMENT PRIMARY KEY,
teacher_id VARCHAR(15) NOT NULL,
student_list VARCHAR(300)
);
선생님과 학생 목록을 리스트 형태처럼 저장하는 테이블이 있다고 하자!
선생님과 학생 한 명을 매칭하여 넣으면, 학생 리스트가 출력되도록 하는 프로시저를 만들었다.
DELIMITER $$
DROP PROCEDURE MAKE_LIST;
CREATE PROCEDURE MAKE_LIST(
IN teacher_id VARCHAR(15), -- 입력받는 선생님 ID
IN student_id VARCHAR(15), -- 입력받는 학생 ID
OUT updated_list VARCHAR(300) -- 반환할 업데이트된 학생 리스트
)
BEGIN
DECLARE EXIST_TEACHER INT DEFAULT 0; -- 선생님 존재 여부 확인 변수
DECLARE CURRENT_LIST VARCHAR(300); -- 현재 학생 리스트
-- 선생님이 테이블에 있는지 확인
SELECT COUNT(1) INTO EXIST_TEACHER
FROM teacher_student
WHERE teacher_id = teacher_id;
IF EXIST_TEACHER = 0 THEN
-- 선생님이 없는 경우, 새로운 레코드 삽입
INSERT INTO teacher_student (teacher_id, student_list)
VALUES (teacher_id, student_id);
SET updated_list = CONCAT(teacher_id, " / ", student_id); -- 새로운 리스트 반환
ELSE
-- 선생님이 있는 경우, 현재 리스트 가져오기
SELECT student_list INTO CURRENT_LIST
FROM teacher_student
WHERE teacher_id = teacher_id;
-- 기존 리스트에 새 학생 ID 추가
SET CURRENT_LIST = CONCAT(teacher_id, " / ", CURRENT_LIST, ', ', student_id);
-- 업데이트된 리스트를 테이블에 저장
UPDATE teacher_student
SET student_list = CURRENT_LIST
WHERE teacher_id = teacher_id;
SET updated_list = CONCAT(teacher_id, " / ", CURRENT_LIST); -- 업데이트된 리스트 반환
END IF;
END$$
DELIMITER ;
이후에 OUT으로 설정된 출력 변수를 선언해주고 프로시저를 호출했다.
-- 출력 변수 선언
SET @updated_list = '';
-- 프로시저 호출
CALL MAKE_LIST('T001', 'S001', @updated_list);
-- 결과 출력
SELECT @updated_list AS updated_student_list;
🚨 호출하기 전에 OUT과 INOUT에 대한 변수는 반드시 선언이 필요하다!!!
OUT이나 INOUT은 호출 후 값을 저장할 메모리 공간이 필요하기 때문에, 애초에 변수로 제공을 해야한다.
처음으로 호출된 프로시저의 결과를 뽑아보면,
선생님 / 학생 형태로 출력됨을 볼 수 있다.
CALL MAKE_LIST('T001', 'S002', @updated_list);
SELECT @updated_list AS updated_student_list;
새로운 데이터를 넣고 다시 뽑아보면...
이렇게 이어서 출력됨을 볼 수 있다.
TABLE에는 어떻게 들어갔냐?!?
잘 분리가 되어 들어가고 수정되었음을 알 수 있다!
복잡하긴 하지만... 예시를 들어서 보기 위해 실행해보았다!
INOUT도 OUT처럼 변수를 설정해서 쓰면 되는데... 차이는 초기값이 사용가능한지 여부이다.
OUT 예시
DELIMITER $$
CREATE PROCEDURE TEST_OUT(
OUT result INT
)
BEGIN
-- 새로운 값을 설정
SET result = 42;
END$$
DELIMITER ;
-- 호출
SET @out_value = 100; -- 초기값 설정했지만 사용되지 않음
CALL TEST_OUT(@out_value);
-- 결과 확인
SELECT @out_value; -- 출력: 42
INOUT 예시
DELIMITER $$
CREATE PROCEDURE TEST_INOUT(
INOUT number INT
)
BEGIN
-- 호출 시 전달받은 값을 사용하여 연산
SET number = number + 42;
END$$
DELIMITER ;
-- 호출
SET @inout_value = 100; -- 초기값 설정
CALL TEST_INOUT(@inout_value);
-- 결과 확인
SELECT @inout_value; -- 출력: 142
OUT은 '집어넣는 값'이라는 의미가 없으므로...
초기에 설정한 값을 이용할 수 없다!!
number가 OUT인 경우 number = numbser + 42를 프로시저 안에서 활용할 수 없다는 의미이다~
