[DB] 스토어드 프로시저(SP)와 스토어드 함수 / MySQL 사용법

스토어드 프로시저(SP) 정의

SQL 쿼리문들을 하나로 묶어서 편리하게 사용하는 프로그래밍 기능을 뜻함.

 

SQL 묶는 개념 외에 다른 프로그래밍 언어와 같은 기능(함수)을 담당할수도 있음.

 

실무에선 SQL문을 매번 하나하나 수행하기보다는 스토어드 프로시저로 만들어 놓은 후 스토어드 프로시저를 호출하는 방식을 많이 사용

 

스토어드 프로시저 명령어

전체형식

# 전체형식

CREATE
	[DEFINER - user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
proc_parameter:
	[ IN : OUT : INOUT ] param_name type
    
type:
	Any valid MYSQL data type
    
characteristic:
	CMMENT 'string'
    : LANGUAGE SQL
    : [NOT] DETERMINISTIC
    : { CONTAINS SQL : NO SQL : READS SQL DATA : MODIFIES SQL DATA }
    : SQL SECURITY { DEFINER : INVOKER }
    
routine_body:
	Valid SQL routine statement

요약 명령어

# 요약 형식

# 생성
DELIMITER $$  # $$는 내부 SQL문 종료문자와 구분짓기 위해 다른것으로 둔것. //이든 다른것도 가능
CREATE PROCEDURE SP이름(IN 또는 OUT 파라미터)
BEGIN
	SQL 프로그래밍 코딩...
END $$
DELIMITER ;

// 호출
CALL userProc();

// 수정
ALTER PROCEDURE 프로시저명


// 삭제 
DROP PROCEDURE

매개변수

# 매개변수 처리

# SP에선 입력,출력 매개변수를 두어서 사용 가능

# 입력 매개변수 지정 형식
IN 입력_매개변수_이름 데이터_형식

# 호출
CALL 프로시저_이름(매개변수 전달 값);

# 출력 지정 형식
OUT 출력_매개변수_이름 데이터_형식

#출력 매개변수 있는 sp 실행 형식
CALL 프로시저_이름(@변수명);
SELECT @변수명;

# 예시
DROP PROCEDURE IF EXISTS proc;

DELIMITER $$

CREATE PROCEDURE proc(
	IN invalue CHAR(2),
	OUT outvalue INT
)
BEGIN
	INSERT INTO dept VALUES(invalue,'본사','영업소');
	SELECT MAX(deptno) INTO outvalue FROM dept;
END $$

CALL proc('70',@myValue);
SELECT CONCAT('MAX 부서번호 값 -->',@myValue);

select 가져올 컬럼 into 변수명 from 테이블;

• SELECT ~ INTO ~
• 반환 값을 변수명에 넣어줌.

오류처리

# SP 내부에 오류 발생 처리

DECLARE 액션 HANDLER FOR 오류조건 처리할_문장

# 예시

DELIMITER $$
CREATE PROCEDURE errorProc()
BEGIN
	DECLARE i INT; # 1씩 증가하는 값
    DECLARE hap INT; # 합계(정수형). 오버플로 발생시킬 예정
    DECLARE saveHap INT; # 합계(정수형). 오버플로 직전의 값 저장
    
    DECLARE EXIT HANDLER FOR 1264; # 오류번호 1264 INT형 오버플로가 발생하면 이부분 수행
    BEGIN
    	SELECT CONCAT('INT 오버플로 직전의 합계 --> ', saveHap);
        SELECT CONCAT(i,'더할 시 오버플로');
    END;
    
    SET i = 1; # 1부터 증가
    SET hap = 0; # 합계 누적
    
    WHILE (TRUE) D0 # 무한 루프
    	SET saveHap = hap;
        SET hap = hap +i;
        SET i = i+1;
    END WHILE;
END $$

CALL errorProc();

관련 링크 : https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html

MySQL :: MySQL 8.0 Reference Manual :: 13.6.7.2 DECLARE ... HANDLER Statement

 

스토어드 프로시저의 특징

1. 하나의 요청으로 여러 SQL문 실행 가능( 네트워크 부하 줄임)
2. 유지관리 간편
   • 클라이언트 응용 프로그램에서 직접 SQL문 작성 안해도됨.
   • 데이터베이스에서 스토어드 프로시저의 내용을 일관되게 수정/유지보수 등의 작업 할 수 있음
3. 모듈식 프로그래밍 가능
   
   • 스토어드 프로시저를 생성해놓으면, 언제든지 실행 가능
4. 보안 강화
   
   • 사용자 별로 테이블에 접근 권한을 주지 않고, 스토어드 프로시저에게만 접근 권한을 줌.

 

스토어드 함수

사용자가 직접 만들어서 사용하는 함수를 뜻함.

 

# 권한 설정 필수
SET GLOBAL log_bin_trust_function_creators = 1;

# 생성
DELIMITER $$
CREATE FUNCTION 스토어드 함수이름(파라미터)
	RETURN 반환형식
BEGIN
	프로그래밍 코딩..
    RETURN 반환값;
END $$

# 호출
SELECT 스토어드_함수이름();

# 삭제
DROP FUNCTION 스토어드_함수이름;

# 함수 내용 확인
SHOW CREATE FUNCTION 스토어드_함수이름;

 

스토어드 프로시저 VS 스토어드 함수

스토어드 프로시저	스토어드 함수
여러 SQL문, 숫자 계산 등 다양한 용도로 사용(업무 맡기기)	계산을 통해 하나의 값을 반환하는데 사용(로직 도와주기)
0,1,N개의 반환 값 가능	1개의 반환 값 필수
입출력 파라미터 사용 가능	입력 파라미터만 가능
CALL 로 호출	SELECT로 호출
프로시저 안에서 SELECT문 사용 가능	함수 안에 집합 결과 반환 SELECT문 사용불가

 

참조

'이것이 MySQL이다' 책 참조