스토어드 프로시저

MySQL에서 제공하는 프로그래밍 기능이다.

• 특징

  • 성능을 향상 시킬 수 있다.
  • 유지관리가 편하다.
  • 모듈식 프로그래밍이 가능하다.
  • 보안을 강화할 수 있다.

프로시저 정의 문법

DELIMITER $$
CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] parameter_name data_type, ...)
BEGIN
   -- 여기에 쿼리 작성 
END $$
DELIMITER ;
CALL procedure_name();

• CREATE PROCEDURE - 스토어드 프로시저 생성

• procedure_name - 스토어드 프로시저의 이름

• [IN|OUT|INOUT] parameter_name data_type - 스토어드 프로시저에서 사용되는 매개변수

• IN - 매개변수는 스토어드 프로시저 내부에서 값을 읽을 때 사용

• OUT - 매개변수는 스토어드 프로시저 내부에서 값을 반환할 때 사용

• INOUT - 매개변수는 값을 읽고 반환할 수 있는 매개변수

• DELIMITER $$ - 쿼리문의 끝나는 부분을 구분할 때 ;(세미콜론)으로 구분하는데 이것을 $으로 바꾼다는 문법이다. 그 이유는 BEGIN~END안에 쿼리문들이 ;으로 구분되어 작성하는데 문제가 생기지 않게 하려고 한다고 한다. 작성을 마친 후 `DELIMITER ;` 을 하는 것은 끝나는 구분자(?)를 $ → ; 다시 원래대로 변경하는 것이다.

• CALL - 프로시저를 호출한다.

삭제

drop PROCEDURE 프로시저_이름;

---

실습 - 입력1개

use sqldb;
DELIMITER $$ 
CREATE PROCEDURE userProc1(IN userName VARCHAR(10))
BEGIN
	SELECT * FROM userTbl WHERE name = userName; -- 이부분에 조관우가 들어간다.
END $$ 
DELIMITER ; 

CALL userProc1('조관우');

---

실습 - 입력이 2개

DROP PROCEDURE IF EXISTS userProc2;
DELIMITER $$
CREATE PROCEDURE userProc2(
	IN userBirth INT, -- 년도과 키가 입력으로 받음
    IN userHeight INT
    )
BEGIN
	SELECT * FROM userTbl
    WHERE birthYear > userBirth and height> userHeight;
END $$ 
DELIMITER ;

CALL userProc2(1970,178);

---

실습 - in,out

out 파라미터 위치에 변수를 넘겨준다.

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 ;
call userProc3 ('테스트값', @Val);
select concat('현재 입력되는 값==>',@Val); -- 조회할 때마다 값이 증가한다.

---

실습 - if문

-- IF
DELIMITER $$
CREATE PROCEDURE ifelseProc(
	IN username VARCHAR(10))
BEGIN
	DECLARE bYear INT; -- 저장프로시저 내에서 변수 선언하는 방법
	-- 아래 조회결과를 into로 bYear에 값을 할당시킨다.
    SELECT birthYear INTO bYear FROM userTbl
		WHERE name=username;
		
	IF (bYear >=1980) THEN
		SELECT '젊음';
	ELSE	
		SELECT '안 젊음';
	END IF;
END $$ 
DELIMITER ;
CALL ifelseProc('조요필');

---

실습 - 에러 핸들링

-- error handling
DELIMITER $$
CREATE PROCEDURE errorProc()
BEGIN
	DECLARE i INT;
    DECLARE hap INT;
    DECLARE saveHap INT;
    
    DECLARE EXIT HANDLER FOR 1264 -- INT형 오버플로우 발생하면 이 부분이 수행
    BEGIN
		SELECT CONCAT('INT 오버플로우 직전의 합계 -->', saveHap);
        SELECT CONCAT('1+2+3+4+...+',i,'=오버플로우');
	END;
    
    SET i = 1 ; -- 1부터 증가
    SET hap = 0; -- 합계누적
    
    WHILE (TRUE) DO
		SET saveHap = hap;
        SET hap = hap+i;
        SET i = i + 1;
	END WHILE;
END $$
DELIMITER ;

CALL errorProc();

저장된 프로시저 확인

SELECT routine_name, routine_definition FROM INFORMATION_SCHEMA.ROUTINES
	WHERE routine_schema = 'DB이름' AND routine_type = 'PROCEDURE';

저장된 프로시저의 파라미터 확인

SELECT parameter_mode, parameter_name, dtd_identifier
	FROM INFORMATION_SCHEMA.PARAMETERS
    WHERE specific_name = '프로시저이름';

프로시저가 어떻게 만들어졌는지 확인할 수 있다.

SHOW CREATE PROCEDURE sqldb.프로시저이름;

open value in VIewer => text