스토어드 프로시저
스토어드 프로시저(이하 프로시저)는 여러개의 명령을 묶어서 실행할 수 있는 기능이다.
복잡한 비즈니스 로직을 구현하고, 트랜잭션을 처리하는 목적을 가진다.
그렇기 때문에 스토어드 함수와는 달리 비결정성을 허용한다.
프로시저 목적
- 복잡한 비즈니스 로직 구현
- 데이터 변경
- 자동화된 작업 수행
CREATE PROCEDURE 프로시저 생성
DELIMITER //
CREATE PROCEDURE 프로시저명()
BEGIN
DECLARE var1 INT;
SET var1 = 100;
SELECT * FROM 테이블명1;
SELECT * FROM 테이블명2;
END //
DELIMITER ;프로시저명 뒤에 오는 괄호 내부에 파라미터를 줄 수도 있다.
파라미터 종류
- 입력 파라미터
IN 파라미터명 데이터형식- 출력 파라미터
OUT 파라미터명 데이터형식CALL 프로시저 호출
CALL myProc();프로시저에 파라미터가 존재해 인자를 넘겨주는 경우에는 다음과 같이 호출
CALL myProc(인자1, 인자2, ...);만약 출력 파라미터가 존재하는 경우에는, 인자로 변수명을 준다.
출력 인자에 값 넣기
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 ;SELECT ~ INTO 변수명 명령으로 출력 인자에 값을 넣는다.
변수 선언
프로시저 내부에서 변수 선언 시 DECLARE 변수명 데이터타입으로 선언
기본적으로 사용자 정의 변수에 대해서 @을 붙여서 선언하지만, 프로시저 내부에서는 @을 붙이지 않음
할당 시 SET 사용은 똑같음
IF문 사용
IF 조건 THEN
실행문;
ELSEIF 조건2 THEN
실행문;
ELSE
실행문;
END IF;일반 프로그래밍 언어와 비슷하지만, IF(조건) 뒤에 THEN을 붙여야하고 프로시저 내부에서 사용하는 경우 END IF;로 구문을 끝내야 한다.
CASE문 사용
CASE
WHEN(조건) THEN
실행문;
WHEN(조건2) THEN
실행문;
ELSE
실행문;
END CASE;역시 프로시저 내부에서 사용하는 경우 WHEN(조건) 뒤에 THEN을 붙여야하고, END CASE;로 구문을 끝내야 한다.
프로그래밍 언어의 switch와 유사한 기능을 하지만, 처음으로 참인 조건에서 THEN 이하의 실행문을 실행하고 멈추게 되므로 break는 사용하지 않는다.
delimiter //
create procedure userGrade()
begin
select u.userid, u.name, sum(price * amount) '총구매액',
case
when (sum(price*amount) >= 10000) then '최우수고객'
when (sum(price*amount) >= 5000) then '우수고객'
when (sum(price*amount) >= 1000) then '일반고객'
else '유령고객'
end as '고객등급'
from buytbl b
right outer join usertbl u
on b.userid = u.userid
group by u.userid
order by sum(price * amount) desc;
end //
delimiter ;
call userGrade();만약 SELECT에서 CASE문을 사용한다면, END AS 컬럼명으로 컬럼을 생성한다.
WHILE문 사용
WHILE(조건) DO
실행문
END WHILE;WHILE(조건) 뒤에 DO가 붙고, END WHILE로 구문을 끝낸다.
label 지정
delimiter //
create procedure whileProc()
begin
declare i int;
declare sum_of int;
set i = 1;
set sum_of = 0;
myWhile :
while (i <= 100) do -- myWhile: => while문에 label을 지정
if(i % 7 = 0) then
set i = i + 1;
iterate myWhile; -- iterate => 지정한 label문으로 가서 계속 진행
end if;
set sum_of = sum_of + i;
if(sum_of > 1000) then
leave myWhile;
end if;
set i = i + 1;
end while;
select sum_of;
end //
delimiter ;
call whileProc(); -- 호출while문에 대한 label 지정 시 :을 사용하여 while문 label을 지정할 수 있다.
ITERATE label명: 프로그래밍 언어의 while문에서 continue와 같은 기능LEAVE label명: 프로그래밍 언어의 while문에서 break와 같은 기능
중첩 WHILE문
DELIMITER //
CREATE PROCEDURE GUGUPROC()
BEGIN
DECLARE i INT;
DECLARE k INT;
DECLARE str VARCHAR(100);
SET k = 2;
SET str = '';
WHILE(k < 10) DO
SET i = 1;
WHILE (i < 10) DO
SET str = CONCAT(k, ' * ', i, ' = ', k * i);
INSERT INTO guguTBL VALUES(str);
SET i = i + 1;
END WHILE;
SET k = k + 1;
END WHILE;
END //
DELIMITER ;일반 프로그래밍 언어와 똑같이 중첩 WHILE문을 만들 수 있다.
각각의 WHILE문마다 END WHILE;로 구문을 끝내야 한다.
오류 핸들링
DECLARE 액션 HANDLER FOR 오류조건 처리할문장;- 액션 : 오류 발생 시의 행동.
CONTINUE -> 처리할문장을 실행
EXIT -> 종료 - 오류조건 : 어떤 오류를 처리할 것인지.
오류코드 숫자, SQLEXCEPTION, SQLWARNING, NOT FOUND 등이 오게 됨 - 처리할문장 : 처리할 문장이 하나라면 한 문장이 나오면 되고, 여러 문장이라면 BEGIN END로 묶음
커서
프로시저 내부에서 커서를 사용할 수 있다.
커서는 일반 프로그래밍 언어의 파일 처리와 방법이 비슷하기 때문에 행의 집합을 다루기에 편리한 기능을 많이 제공해준다.
일반 파일 처리에서 파일을 연 후에 한 행씩 읽거나 쓰는 작업을 처리한다. 파일 포인터가 한 행을 읽을 때마다 자동으로 다음 행을 가리킨다.
커서도 이와 비슷한 동작을 한다.
- 커서의 선언 (DECLARE CURSOR)
- 반복 조건 선언 (DECLARE CONTINUE HANDLER)
더 이상 읽을 행이 없을 경우에 실행할 내용 설정 - 커서 열기 (OPEN)
- 커서에서 데이터 가져오기 (FETCH)
- 데이터 처리
(4 ~ 5번은 LOOP ~ END LOOP 문으로 반복 구간을 지정) - 커서 닫기 (CLOSE)
고객 키 평균 프로시저
DELIMITER //
CREATE PROCEDURE cursorProc()
BEGIN
DECLARE userHeight INT; -- 고객의 키
DECLARE cnt INT DEFAULT 0; -- 고객의 인원 수 (= 읽은 행의 수)
DECLARE totalHeight INT DEFAULT 0; -- 키의 합계
DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부
DECLARE userCursor CURSOR FOR
SELECT height FROM userTbl;
DECLARE CONTINUE HANDLER FOR -- 행의 끝이면 TRUE 대입
NOT FOUND SET endOfRow = TRUE;
OPEN userCursor;
cursor_loop : LOOP
FETCH userCursor INTO userHeight; -- 고객의 키 1개를 대입
IF endOfRow THEN
LEAVE cursor_loop;
END IF;
IF userHeight IS NOT NULL THEN
set cnt = cnt + 1;
set totalHeight = totalHeight + userHeight;
END IF;
END LOOP;
-- 고객 키의 평균을 출력
SELECT CONCAT('고객 키의 평균 ===>', (totalHeight / cnt));
CLOSE userCursor;
END //
DELIMITER ;고객 등급 추가 프로시저
ALTER TABLE userTbl ADD grade varchar(5);
DELIMITER //
CREATE PROCEDURE gradeProc()
BEGIN
DECLARE id VARCHAR(10); -- 현재 고객 아이디
DECLARE hap BIGINT; -- 총 구매액
DECLARE userGrade VARCHAR(5); -- 현재 고객 등급
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
DECLARE userCursor CURSOR FOR
SELECT U.userId, sum(price * amount)
FROM buyTbl B
RIGHT OUTER JOIN userTbl U
ON U.userId = B.userId
GROUP BY U.userId, U.name;
DECLARE CONTINUE HANDLER FOR
NOT FOUND SET endOfRow = TRUE;
OPEN userCursor;
grade_loop: LOOP
FETCH userCursor INTO id, hap; -- 행에서 읽은 값 대입
IF endOfRow THEN
LEAVE grade_loop;
END IF;
CASE
WHEN (hap >= 10000) THEN
SET userGrade = '최우수고객';
WHEN (hap >= 5000) THEN
SET userGrade = '우수고객';
WHEN (hap >= 1000) THEN
SET userGrade = '일반고객';
ELSE SET userGrade = '유령고객';
END CASE;
UPDATE userTbl
SET grade = userGrade
WHERE userId = id;
END LOOP;
CLOSE userCursor; -- 커서 닫기
END //
DELIMITER ;PREPARE문
동적 쿼리는 SQL의 일부 값이 런타임에 결정되는 경우 사용된다.
이 때 SQL문을 CONCAT으로 이어서 실행하는 경우에는 SQL Injection 공격에 취약할 수 있다.
이를 피하기 위해 PREPARED STATEMENT를 사용한다.
SET @bYear = 1998;
SET @height = 176;
set @myQuery = 'SELECT * FROM userTbl WHERE birthYear >= ? AND height >= ?';
PREPARE stmt FROM @myQuery;
-- prepared statement 생성
EXECUTE stmt USING @bYear, @height;
-- using으로 ?에 값 넣기. 순서를 지켜야 한다.
DEALLOCATE PREPARE stmt;PREPARED STATEMENT 생성 시에는 FROM SQL_템플릿_문자열를 사용해 생성한다.
SQL 템플릿 문자열에는 값 자리에만 플레이스홀더(?)를 포함할 수 있다.
또한 변수에 SQL 템플릿 문자열을 담아서 FROM 뒤에 변수명을 써서 PREPARED STATEMENT를 만들 수도 있다.
주의점
MySQL에서 동적 쿼리를 위한 PREPARED STATEMENT를 사용할 때의 주의할 점은 다음과 같다.
- 테이블명이나 컬럼명과 같은 구조적인 부분은 플레이스홀더(
?)로 사용할 수 없다.
즉, 테이블이나 컬럼명을 동적으로 결정하는 경우에는 CONCAT을 사용하여 만든 SQL 템플릿 문자열로 PREPARED STATEMENT를 만들어야 한다. - 공백에 주의
- 실행 후 할당 해제하기
프로시저의 테이블명 파라미터와 동적 쿼리
다음과 같이 프로시저에서 테이블명을 파라미터로 넘겨서 조회를 하는 경우 조회가 되지 않는다.
DELIMITER //
CREATE PROCEDURE nameProc(
IN tableName VARCHAR(20)
)
BEGIN
SELECT * FROM tableName;
END //
DELIMITER ;tableName이라는 인자를 받았지만, 넘겨받은 인자가 아닌 tableName이라는 이름 자체로 테이블 조회를 시도한다.
그래서 이러한 경우에는 동적 쿼리를 사용해야 한다.
DELIMITER //
CREATE PROCEDURE nameProc(
IN tableName VARCHAR(20)
)
BEGIN
SET @myQuery = CONCAT('SELECT * FROM ', tableName);
PREPARE stmt FROM @myQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT * FROM tableName;
END //
DELIMITER ;테이블명은 플레이스홀더(?)를 사용할 수 없으므로, CONCAT을 사용해 동적으로 SQL 템플릿 문자열을 만들고 이를 사용해 PREPARED STATEMENT를 생성 및 실행했다.
파라미터 조회
SELECT parameter_mode, parameter_name, dtd_identifier
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE specific_name = '프로시저명';위 명령어를 사용해 해당 프로시저의 파라미터들을 조회할 수 있다.
모든 프로시저 조회
SELECT routine_name, routine_definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_schema = 'DB명' and routine_type = 'PROCEDURE';위 명령어를 사용해 해당 DB에 존재하는 모든 프로시저를 조회할 수 있다.
프로시저의 장점
-
성능 향상
긴 코드로 구현된 쿼리 실행 시 클라이언트에서 서버로 쿼리의 모든 텍스트가 전송되어야 한다. 하지만 미리 정의된 프로시저를 사용하면 프로시저명과 파라미터 등의 몇 글자만 전송하면 되므로 네트워크 부하를 어느 정도 줄일 수 있으며 결과적으로 성능을 향상시킨다.+) 다른 DBMS에서는 프로시저 초기 호출 1회에만 컴파일된 후 메모리에 로딩. MySQL에서는 매 호출마다 컴파일 되기 때문에 컴파일과 관련해 성능 향상은 불가능 하지만, 네트워크 부하를 줄인다는 점에서 성능 향상을 가져올 수 있음.
-
유지 관리가 간편하다
응용 프로그램에서 직접 SQL문을 작성하지 않고 프로시저 이름만 호출하게 설정함으로써 DB에서 관련된 프로시저 내용을 일관되게 수정/유지보수 할 수 있다. -
모듈식 프로그래밍 가능
-
보안을 강화할 수 있다
사용자별로 테이블에 접근 권한을 주지 않고 스토어드 프로시저에만 접근 권한을 줄 수 있다. 이로써 보안을 강화할 수 있다.
(뷰와 비슷하게 제한된 접근 권한을 주어 보안을 강화시킴)
