저장 프로시저 자세한 개념 : https://velog.io/@fasongsong/%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Stored-PROCEDURE
📌 저장 프로시저(stored procedure)란?
- 일련의 SQL 문장을 선언해서 MYSQL에 저장하고, 해당 SQL문을 함수처럼 사용하는것
- 만들어두기만 하면 함수처럼 호출하여 편하게 사용할 수 있음
📌 왜 사용하는가?
- 저장 프로시저는 사용자들에게 데이터에 대한 제한적인 접근을 허용케하는 전통적인 수단
- 쿼리 분석기와 같은 도구를 이용할 경우 원하는 어떠한 작업도 수행가능하므로, 사용자들은 SELECT, INSERT, UPDATE 혹은 DELETE 같은 문장을 직접 실행할 수 있는 권한을 가져서는 안됨
- 성능을 향상시키기 위해 사용함
저장 프로시저가 최초로 실행되면, SQL 서버는 해당 프로시저에 대한 실행계획을 생성하는데 이 실행계획이 캐쉬에 저장됨
- 해당 저장프로시저가 재실행 요청을 받으면, SQL 서버는 저장된 실행계획을 재사용함
실행계획이 만료되거나 혹은 SQL 서버가 새로운 실행계획을 생성시켜야 할 이유가 생길 때까지 해당 실행계획은 캐쉬에 유지됨
- 50줄 이상의 복잡한 Select 문장이 있으며, 각 실행시마다 Where 절 조건문이 조금씩 변한다고 가정해보자
이 문장을 저장 프로시저에 넣을 경우, 네트워크를 통해 전달되는 데이터 소통량이 상당히 감소하겨 되며 해당 프로시저가 자주 실행될수록 성능향상 효과가 증대됨
- 단일 행을 반환하는 SQL문장을 실행시키고 싶을 경우에, SQL 문만 사용하면 결과 집합(record set)을 레코드셋으로 받아야 함
하지만, 저장 프로시저를 사용할 경우에는 성능이 월등한 출력매개변수의 사용이 가능함
- 단일 쿼리에 대한 실행시간의 차이는 무시할 수 있겠지만, 신규 사용자 등록 같은 단순 insert 작업을 SQL 서버에 수만번 해야 한다면, 결과 집합으로 값을 받는 것에 비해 @key를 출력 매개변수로 반환받는 경우의 이점은 엄청나게 커지게 됨
- 수백개의 테이블이 존재하는 복잡한 시스템에서, 간혹 어디에서 어떤 테이블 혹은 칼럼이 참조되었는지 알고 싶을 때
만약 모든 코드가 저장 프로시저에 보관되어있다면, 참조된 객체를 찾기 위해 저장 프로시저의 코드만 살펴보면 됨
📌 구현
코드
-- 기존에 있는거 날리기
drop table user;
drop procedure `proc_user_insert`;
CREATE Table user(
`id` varchar(20),
`name` varchar(20),
`campus` varchar(20),
`class` varchar(20),
`gi` varchar(20)
);
-- 저장 프로시저
DELIMITER //
CREATE PROCEDURE `proc_user_insert` (
IN `p_id` varchar(20),
IN `p_name` VARCHAR(20),
IN `p_campus` VARCHAR(20),
IN `p_class` varchar(20),
IN `p_gi` varchar(20)
)
BEGIN
INSERT INTO `user` (id, name, campus, class, gi)
VALUES (p_id, p_name, p_campus, p_class, p_gi);
END
//
DELIMITER ;
//구분자 변경안했을 경우 (에러)
-- CREATE PROCEDURE `proc_user_insert` (
-- IN `p_id` INT,
-- IN `p_name` VARCHAR(20),
-- IN `p_campus` VARCHAR(20),
-- IN `p_class` INT,
-- IN `p_gi` INT
-- )
-- BEGIN
-- INSERT INTO `user` (id, name, campus, class, gi)
-- VALUES (p_id, p_name, p_campus, p_class, p_gi);
-- END;
CALL `proc_user_insert`('hello', '홍길동', '서울', '1반', '1기');
select * from user;
주의할 점
- MySQL에서는 DELIMITER 명령어를 사용하여 프로시저를 작성할 때 구분자를 변경해야 함
- 그렇지 않으면 기본 구분자인 세미콜론(;)이 프로시저 정의의 끝으로 인식되어 에러가 발생할 수 있음
📌 IN, OUT, INOUT
MySQL 저장 프로시저에서 사용되는 IN, OUT, INOUT 매개변수는 프로시저 호출 시 입력값을 받고 결과값을 반환하는 방법을 정의함
1. IN 매개변수
역할: 프로시저 호출 시 호출자가 입력하는 값을 받습니다.
특징: 프로시저 내에서 값을 변경할 수 있지만, 호출자에게는 변경된 값이 반영되지 않습니다. 입력 전용입니다.
예제:
CREATE PROCEDURE example_in(IN p_value INT)
BEGIN
-- 입력된 p_value를 사용하여 로직 수행
END;2. OUT 매개변수
역할: 프로시저가 실행된 후 결과값을 호출자에게 반환합니다.
특징: 프로시저 내에서 값을 설정해야 하며, 호출자에게 반환됩니다. 출력 전용입니다.
예제:
CREATE PROCEDURE example_out(OUT p_result INT)
BEGIN
-- 결과값을 p_result에 설정
SET p_result = 42;
END;3. INOUT 매개변수
역할: 호출자가 입력한 값을 받고, 그 값을 수정한 후 호출자에게 반환합니다.
특징: 입력과 출력이 모두 가능합니다. 프로시저 내에서 값을 변경하면, 변경된 값이 호출자에게 반영됩니다.
예제:
CREATE PROCEDURE example_inout(INOUT p_value INT)
BEGIN
-- p_value 값을 수정
SET p_value = p_value + 10;
END;예제: IN, OUT, INOUT 사용
아래 예제는 IN, OUT, INOUT 매개변수를 모두 사용하는 저장 프로시저
DELIMITER //
CREATE PROCEDURE example_all(
IN p_input INT,
OUT p_output INT,
INOUT p_inout INT
)
BEGIN
-- p_input은 입력 값으로 사용
DECLARE temp INT;
SET temp = p_input * 2;
-- p_output에 결과값 설정
SET p_output = temp;
-- p_inout 값을 수정
SET p_inout = p_inout + temp;
END
//
DELIMITER ;프로시저를 호출하는 예제
-- 변수 선언
SET @input = 5;
SET @output = 0;
SET @inout = 3;
-- 프로시저 호출
CALL example_all(@input, @output, @inout);
-- 결과 확인
SELECT @output; -- 결과: 10 (5 * 2)
SELECT @inout; -- 결과: 13 (3 + 10)- @output에 초기 값을 설정하더라도, 프로시저 호출 후에는 프로시저 내부에서 설정한 값으로 덮어쓰여짐. 따라서 SET @output = 1;으로 초기 값을 설정하더라도 최종 결과는 프로시저에서 설정한 값이 됨
요약
- IN: 입력용 매개변수로, 호출자가 값을 제공하고 프로시저가 값을 사용
- OUT: 출력용 매개변수로, 프로시저가 값을 설정하여 호출자에게 반환
- INOUT: 입력 및 출력용 매개변수로, 호출자가 값을 제공하고 프로시저가 그 값을 수정하여 호출자에게 반환
- 이러한 매개변수를 사용하여 복잡한 로직을 수행하고 필요한 값을 주고받을 수 있음
📌 참고
🤔 @ 기호
@ 기호란?
- MySQL에서 @ 기호는 세션 변수 또는 사용자 정의 변수를 나타내는 데 사용됨
- 이러한 변수는 SQL 세션 동안 사용되며, 특정 쿼리나 저장 프로시저 호출에서 값을 저장하고 공유하는 데 유용
세션 변수의 특징
- 세션 범위: 세션 변수는 MySQL 세션 동안 지속됨. 세션이 종료되면 변수도 사라짐
- 초기화 필요 없음: 변수를 선언할 때 별도의 초기화 과정이 필요 없음
- 동적 타입: 세션 변수는 다양한 데이터 타입을 가질 수 있으며, 값을 할당할 때 데이터 타입이 결정됨
세션 변수 사용 방법
- 변수 선언 및 값 할당:
SET @variable_name = value;- 변수 사용:
SELECT @variable_name;세션 변수 사용 예제
- 세션 변수 선언 및 값 할당:
SET @number1 = 10;
SET @number2 = 20;- 변수 사용하여 연산 수행:
SET @sum = @number1 + @number2;- 결과 출력:
SELECT @sum; -- 결과: 30저장 프로시저와 세션 변수
저장 프로시저를 호출할 때 세션 변수를 사용하여 결과를 저장하고, 호출 후에 결과를 확인할 수 있음
🤔 레코드셋과 출력 매개변수
레코드셋 (Result Set)
- 레코드셋은 데이터베이스에서 쿼리 실행 결과로 반환되는 데이터의 집합
- 이는 주로 SELECT 문을 실행했을 때 반환되는 결과를 의미함
- 레코드셋은 여러 행(row)과 여러 열(column)로 구성되어 있으며, SQL 쿼리의 결과를 나타냄
예제: 레코드셋 사용
아래 예제는 ssafy_user 테이블에서 모든 사용자를 선택하는 쿼리와 그 결과로 반환되는 레코드셋을 보여줌
SELECT * FROM user;결과 레코드셋:
id name campus class gi
1 홍길동 서울 1반 5
2 김철수 부산 2반 3이 쿼리는 ssafy_user 테이블의 모든 행을 반환하며, 각 행은 사용자의 정보를 포함하고 있음
출력 매개변수 (OUT Parameters)
- 출력 매개변수는 저장 프로시저가 호출자에게 결과를 반환하는 데 사용됨
- 출력 매개변수는 OUT 키워드를 사용하여 정의되며, 프로시저 내부에서 값을 설정한 후 호출자에게 반환
예제: 위 예시 참고
정리
- 레코드셋은 쿼리 결과로 반환되는 데이터 집합을 의미하며, 출력 매개변수는 저장 프로시저가 호출자에게 값을 반환하는 데 사용됨
파송송의 개발 기록