스토어드 프로시저
프로시저는 일련의 쿼리를 모아 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.
[ 프로시저 장점 ]
- 하나의 요청으로 여러 SQL문을 실행할 수 있다.(네트워크에 대한 부하를 줄일 수 있다.)
- 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
- 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조무결성 유지가 가능하게 된다.
간단히 말하면 응용 프로그램 측 로직을 가지지 않고도 데이터베이스의 데이터 앞뒤가 맞게 될 수 있다.
[ 프로시저 단점 ]
- 코드 자산으로서의 재사용성이 나쁘다.(이부분 때문에 실무에서 거의 안쓰는 편이다)
- 업무의 사양 변경 시 외부 응용 프로그램과 할께 프로시저의 정의를 변경할 필요가 있다.
스토어드 프로시저 사용법
프로시저들 목록 확인
show procedure status;
프로시저 정의 (생성)
-- DELIMITER는 프로시저 앞,뒤의 위치하여 안에 있는 부분은 한번에 실행될 수 있게 하는 역할을 한다.
DELIMITER $$
-- 프로시저 정의
#IN은 매개변수 입력값, out은 RETURN 같은 개념이다. 출력값
CREATE PROCEDURE search_user(IN txtValue CHAR(10), OUT outValue int)
-- 프로시저 실행 내용 정의
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 스토어드 함수명;
참고