MySQL 스토어드 프로시저

스토어드 프로시저

• MySQL에서 제공되는 프로그래밍 기능
• 쿼리문의 집합으로 어떤 동작을 일괄 처리하기 위한 용도로 사용
• 스토어드 함수는 반환하는 값이 반드시 나온다.
• 쿼리 모듈화
  • 필요할 때마다 호출만 하면 훨씬 편리하게 MySQL 운영
  • CALL 프로시저_이름() 으로 호출

#기본형식
형식 : 
DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저이름(IN 또는 OUT 파라미터)
BEGIN

	SQL 프로그램 코딩

END $$
DELIMITER;
CALL 스토어드 프로시저이름();

#예시
DELIMITER $$
CREATE PROCEDURE userProc()
BEGIN
	SELECT * FROM userTbl; -- 스토어드 프로시저 내용
END $$
DELIMITER;

CALL userProc();

• 스토어드 프로시저의 수정과 삭제
  • 수정 : ALTER PROCEDURE
  • 삭제 : DROP PROCEDURE
• 매개변수의 사용
  • 입력 매개변수를 지정하는 형식
    • IN 입렵매개변수이름 데이터_형식
  • 입력 매개변수가 있는 스토어드 프로시저 실행 방법
    • CALL 프로시저_이름(전달 값);
  • 출력 매개 변수 지정 방법
    • OUT 출력매개변수이름 데이터_형식
  • 출력 매개 변수에 값 대입하기 위해 주로 SELECT INTO문 사용
  • 출력 매개 변수가 있는 스토어드 프로시저 실행 방법
  • CALL 프로시저_이름(@변수명); SELECT @변수명;
    

#출력매개변수 설정
-- 스토어드 프로시저 생성 및 테스트로 사용할 테이블 생성
DELiMITER $$
CREATE PRocedure userProc3(
	In txtValue Char(10),
    out outvalue int
)
Begin
	insert into testTbl values(null, txtvalue);
    select max(id) into outvalue from testTbl;
End $$
DELIMITER ;

-- 테스트 테이블 생성 후
CREATE table if not exists testTbl(
	id int Auto_increment primary key,
    txt char(10)
);

-- 매개변수로 호출하여 실행한다.
call userProc3('테스트값', @myValue);

-- concat을 이용하여 이어 붙인다.
select concat('현재 입력된 ID 값 ==>', @myValue);

#출력매개변수 설정
-- 스토어드 프로시저 생성 및 테스트로 사용할 테이블 생성
DELiMITER $$
CREATE PRocedure userProc3(
	In txtValue Char(10),
    out outvalue int
)
Begin
	insert into testTbl values(null, txtvalue);
    select max(id) into outvalue from testTbl;
End $$
DELIMITER ;

-- 테스트 테이블 생성 후
CREATE table if not exists testTbl(
	id int Auto_increment primary key,
    txt char(10)
);

-- 매개변수로 호출하여 실행한다.
call userProc3('테스트값', @myValue);

-- concat을 이용하여 이어 붙인다.
select concat('현재 입력된 ID 값 ==>', @myValue);

• IF.. ELSE문 사용

DELIMITER $$
CREATE PROCEDURE ifelseProc(
	IN userName VARCHAR(10)
)
BEGIN
	DECLARE bYear INT; -- 변수 선언
	SELECT birthYear into bYear FROM userTBL
		WHERE name = userName;
	IF (byear >= 1980 ) THEN
			SELECT '아직 젊군요..';
	ELSE
			SELECT '나이가 많네';
	END IF;
END $$
DELIMITER ;

CALL ifelseProc('조용필');

• CASE문 사용

DELIMITER $$
CREATE PROCEDURE caseProc(
	IN userName VARCHAR(10)
)
BEGIN
	DECLARE bYear INT;
    DECLARE tti CHAR(3); -- 띠
    SELECT birthYear INTO byear from usertbl
		where name = userName;
	CASE
		WHEN (byear%12 = 0) THEN SET tti = '원숭이';
        WHEN (byear%12 = 1) THEN SET tti = '닭';
        WHEN (byear%12 = 2) THEN SET tti = '개';
        WHEN (byear%12 = 3) THEN SET tti = '돼지';
        WHEN (byear%12 = 4) THEN SET tti = '쥐';
        WHEN (byear%12 = 5) THEN SET tti = '소';
        WHEN (byear%12 = 6) THEN SET tti = '호랑이';
        WHEN (byear%12 = 7) THEN SET tti = '토끼';
        WHEN (byear%12 = 8) THEN SET tti = '용';
        WHEN (byear%12 = 9) THEN SET tti = '뱀';
        WHEN (byear%12 = 10) THEN SET tti = '말';
        ELSE SET tti = '양';
	END CASE;
    SELECT CONCAT(username, '의 띄 ==>', tti);
END $$
DELIMITER ;

CALL caseProc('김범수');

• WHILE 문 활용

DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE str VARCHAR(100); -- 각 단을 문자열로 저장
    DECLARE i INT; -- 구구단 앞자리
    DECLARE k INT; -- 구구단 뒷자리
    SET i = 2; -- 2단부터 계산
    
    WHILE (i < 10) DO  -- 바깥 반복문. 2단~9단까지.
        SET str = ''; -- 각 단의 결과를 저장할 문자열 초기화
        SET k = 1; -- 구구단 뒷자리는 항상 1부터 9까지.
        WHILE (k < 10) DO
            SET str = CONCAT(str, '  ', i, 'x', k, '=', i*k); -- 문자열 만들기
            SET k = k + 1; -- 뒷자리 증가
        END WHILE;
        SET i = i + 1; -- 앞자리 증가
        INSERT INTO guguTBL VALUES(str); -- 각 단의 결과를 테이블에 입력.
    END WHILE;
END $$
DELIMITER ;

CALL whileProc();
SELECT * FROM guguTBL;

• 저장된 프로시저의 이름 및 내용 확인

-- 현재 저장된 프로시저의 이름 및 내용 확인
SELECT routine_name, routine_definition FROM INFORMATION_SCHEMA.ROUTINES
    WHERE routine_schema = 'tabledb' AND routine_type = 'PROCEDURE';

-- 파라미터 확인
SELECT parameter_mode, parameter_name, dtd_identifier
	FROM INFORMATION_SCHEMA.PARAMETERS
	WHERE specific_name = 'userProc3';

스토어드 프로시저의 특징

• MySQL의 성능 향상
• 유지관리 간편
  • 스토어드 프로시저로 저장해 놓은 쿼리의 수정, 삭제 등의 관리 수월
• 모듈식프로그래밍 가능
  • 언제든지 실행 가능
  • 모듈식 프로그래밍 언어와 동일한 장점 갖음
• 보안강화
  • 사용자 별로 테이블 접근 권한 주지 않고 스토어드 프로시저에만 접근 권한을 주어 보안 강화
    • 뷰 또한 스토어드 프로시저와 같이 보안 강화 가능

스토어드 함수

• 사용자가 직접 만들어서 사용하는 함수

• 스토어드 프로시저와 유사 (형태와 사용 용도에 있어 차이 있음)

• 2개의 숫자의 합계를 계산하는 스토어드 함수

SET global log_bin_trust_function_creators = 1; -- 함수 생성 권한 허용

USE sqlDB;
DROP FUNCTION IF EXISTS userFunc;
DELIMITER $$
CREATE FUNCTION userFunc(value1 INT, value2 INT)
    RETURNS INT
BEGIN
    RETURN value1 + value2;
END $$
DELIMITER ;

SELECT userFunc(100, 200);

• 출생년도를 입력하면 나이가 출력되는 함수

DROP FUNCTION IF EXISTS getAgeFunc;
DELIMITER $$
CREATE FUNCTION getAgeFunc(bYear INT)
    RETURNS INT
BEGIN
    DECLARE age INT;
    SET age = YEAR(CURDATE()) - bYear; -- 현재 년도에서 입력받은 년도를 빼준것을 age에 초기화
    RETURN age; -- age 리턴
END $$
DELIMITER ;

SELECT getAgeFunc(1979);

스토어드 함수와 스토어드 프로시저의 차이점

• 스토어드 함수
  • 파라미터에 IN, OUT 등을 사용할 수 없음
    • 모두 입력 파라미터로 사용
  • RETURNS문으로 반환할 값의 데이터 형식 지정
    • 본문 안에서는 RETURN문으로 하나의 값 반환
  • SELECT 문장 안에서 호출
  • 함수 안에서 집합 결과 반환하는 SELECT 사용 불가
    • SELECT INTO 는 집합 결과 반환하는 것이 아니므로 예외적으로 스토어드 함수에서 사용 가능
  • 어떤 계산 통해서 하나의 값 반환하는데 주로 사용
• 스토어드 프로시저
  • 파라미터에 IN, OUT 등을 사용 가능
  • 별도의 반환하는 구문이 없음’
    • 필요하다면 여러 개의 OUT 파라미터 사용해서 값 반환 가능
  • CALL로 호출
  • 스토어드 프로시저 안에 SELECT문 사용 가능
  • 여러 SQL문이나 숫자 계산 등의 다양한 용도로 사용