[MYSQL] 📚 스토어드 프로시저 & 스토어드 함수 사용법

스토어드 프로시저

프로시저는 일련의 쿼리를 모아 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

​

[ 프로시저 장점 ]

• 하나의 요청으로 여러 SQL문을 실행할 수 있다.(네트워크에 대한 부하를 줄일 수 있다.)
• 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
• 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조무결성 유지가 가능하게 된다.
  간단히 말하면 응용 프로그램 측 로직을 가지지 않고도 데이터베이스의 데이터 앞뒤가 맞게 될 수 있다.

​

[ 프로시저 단점 ]

• 코드 자산으로서의 재사용성이 나쁘다.(이부분 때문에 실무에서 거의 안쓰는 편이다)
• 업무의 사양 변경 시 외부 응용 프로그램과 할께 프로시저의 정의를 변경할 필요가 있다.

---

스토어드 프로시저 사용법

 

프로시저들 목록 확인

show procedure status;

​

프로시저 정의 (생성)

-- DELIMITER는 프로시저 앞,뒤의 위치하여 안에 있는 부분은 한번에 실행될 수 있게 하는 역할을 한다.
DELIMITER $$

-- 프로시저 정의
CREATE PROCEDURE search_user(IN txtValue CHAR(10), OUT outValue int) #IN은 매개변수 입력값, out은 RETURN 같은 개념이다. 출력값							

-- 프로시저 실행 내용 정의
BEGIN
	SELECT USER, HOST FROM MYSQL.USER;
END $$

DELIMITER;

 

프로시저 호출

CALL 프로시저명(매개변수들);

​

프로시저 내용 조회

show create procedure 프로시저명;

​

---

 프로시저 문법 구성

DROP PROCEDURE IF EXISTS calculate_grade;

DELIMITER $$
CREATE PROCEDURE calculate_grade(
  IN in_mid DOUBLE, -- 매개변수는 IN으로 가져온다.
  IN in_end DOUBLE,
  IN in_att INT,
  IN in_rep INT,
  IN in_class_num INT,
  IN in_student_num INT )

BEGIN
  -- 지역 변수 선언
  DECLARE total DOUBLE DEFAULT 0; -- double total = 0; 과 같다고 보면 된다.
  DECLARE grade VARCHAR(2);

  -- 변수 초기화
  SET total = in_mid + in_end + in_att + in_rep; -- 인자를 다 더한다.

  -- 분기
  IF total >= 95 AND total <= 100 THEN
  	SET grade = 'A+';

  ELSEIF total >=90 AND total < 95 THEN
  	SET grade = 'A';

  ELSEIF total >=85 AND total < 90 THEN
  	SET grade = 'B+';

  ELSEIF total >=80 AND total < 85 THEN
  	SET grade = 'B';

  ELSEIF total >=75 AND total < 80 THEN
  	SET grade = 'C+';

  ELSEIF total >=70 AND total < 75 THEN
  	SET grade = 'C';

  ELSEIF total >=65 AND total < 70 THEN
  	SET grade = 'D+';

  ELSEIF total >=60 AND total < 65 THEN
  	SET grade = 'D';

  ELSEIF total >=0 AND total < 60 THEN
  	SET grade = 'F';
  END IF;

  -- 쿼리문
  UPDATE university.course
  SET
    course_mid = in_mid,
    course_end = in_end,
    course_report = in_rep,
    course_attendance = in_att,
    course_total = total,
    course_grade = grade
  WHERE course_student_num = in_student_num AND course_class_num = in_class_num AND course_num >= 1;

END $$
DELIMITER ;

-- 프로시저 실행 (호출)
CALL calculate_grade(40.0, 30.0, 9,9,1,2019160160);

​

DELIMITER

• 프로시저나 트리거에서 사용된다.
• 프로시저를 생성할 때 프로시저 안에 있는 쿼리들이 ;으로 인해 실행되면 안되기 때문에 이를 막기 위해 DELIMITER를 이용하여 DELIMITER를 이용하여 지정된 문자가 나타나기 전까지는 ;을 만나도 실행되지 않게 막아준다.

​

IN / OUT

• in : 프로시저를 호출하기 위해 필요한 정보들로 함수의 매개변수(인자)에 해당한다.
• out : 함수의 반환값으로 이해하면 된다.

​

DECLARE

• 프로시저 내부에서 사용하는 지역 변수를 선언할 때 사용한다.

​

SET

• 변수의 값을 설정할 때 사용한다.

​

IF 조건식 THEN 실행문 END IF

DELIMITER $$
 
CREATE PROCEDURE GetCustomerLevel(
    IN  p_customerNumber int(11), 
    OUT p_customerLevel  varchar(10))
BEGIN
    DECLARE creditlim double;
 
    SELECT creditlimit INTO creditlim -- select한 결과 creditlimit 필드 값을 위에서 선언한 지역변수 creditlim에 넣는다 
    FROM customers
    WHERE customerNumber = p_customerNumber;
 
    IF creditlim > 50000 THEN
        SET p_customerLevel = 'PLATINUM';
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = 'SILVER';
    END IF;
 
END$$
 
DELIMITER ;

​

while문

DELIMITER $$

create procedure myProc()

BEGIN
  declare i int;
  declare hap int;

  set i = 1;
  set hap = 0;

  myWhile: while (i<1000) do
    if (i % 3 = 0) then
      set i = i + 1;
      iterate myWhile; -- iterate == continue 같은 개념
    end if;
  
    set i = i + 1;
    set hap = hap + i;

    if (hap==900) then
    	leave myWhile; -- leave == break 같은 개념
  
  end while;
  
  select hap;
END $$

DELIMITER ;

---

스토어드 함수

MySQL이 사용자가 원하는 함수를 전부 제공하지 않으므로, 필요에 따라 직접 만들어서 사용하는 함수를 스토어드 함수라 한다.

프로시저와 비슷하게 보이지만 사용처가 확연히 다르다.

---

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

• 프로시저의 파라미터 IN, OUT을 사용할 수 없다.
• 스토어드 함수 파라미터는 모두 입력 파라미터로 사용된다.
• 스토어드 함수는 return문으로 반환 할 값의 데이터 타입을 지정하고, 본문 안에서 return문으로 하나의 값을 반환해야 한다.
  반면에 스토어드 프로시저는 별도의 반환하는 구문이 없으며, 여러개의 OUT파라미터를 사용해서 값을 반환할 수 있다.
• 프로시저는 call로 호출되지만,
  스토어드 함수는 select로 호출된다.
• 프로시저 안에서는 select문을 사용할 수 있지만,
  스토어드 함수 안에서는 집합 결과를 반환하는 select문을 사용 할 수 없다.

​스토어드 프로시저는 여러 쿼리문들을 묶고 고급언어 처럼 분기처리를 위해 사용하는 묶음이라고 보면, 
스토어드 함수는 간단하고 짧은 값을 얻기위해 사용하는 묶음이라고 보면 된다.

---

스토어드 함수 사용법

 

스토어드 함수 생성

DROP FUNCTION IF EXISTS getAge;

DELIMITER $$
	CREATE FUNCTION getAgeFunc(birthYear INT) -- 입력 파라미터 지정
	RETURNS INT -- 반환 타입 지정
    
BEGIN
	DECLARE age INT
	SET age = YEAR(CURDATE()) - bYear;

	RETURN age; -- 값 반환
END $$
DELIMITER ;

스토어드 함수 실행

SELECT getAgeFunc(1979);
​
SELECT userName, getAgeFunc(birthYear) AS '나이' FROM userTable

​

스토어드 함수 조회 / 삭제

SHOW CREATE FUNCTION 스토어드 함수명;
​
DROP FUNCTION 스토어드 함수명;