저장 프로시저 (Stroed Procedure)

▤ 목차

✔ Procedure

쿼리문들의 집합으로, 어떤 동작을 여러쿼리를 거쳐서 일괄적으로 처리할 때 사용한다.

절차적으로 진행된다. 즉, 특정 로직의 쿼리를 묶어 놓은 것이다.

 

저장 프로시저:

일련의 작업을 처리하는 절차이다. 서버에서 실행되기 때문에 속도가 빠르다.

함수:

여러 작업을 위한 기능이다. 리턴값을 갖고있다.

클라이언트 단에서 실행되기 때문에 프로시저보다 느리다.

 

⌨ 형식

delimiter //  -- //대신 $$ 사용가능
BEGIN -- 저장 프로시저의 본체 (본문 시작)

END;-- 저장 프로시저의 본체 (본문 끝)
//
delimiter ;

💻 코드로 보기

delimiter //  -- //대신 $$ 사용가능
CREATE OR replace PROCEDURE sp_1(a INT, b INT)
BEGIN -- 저장 프로시저의 본체 (본문 시작)
DECLARE X,Y INT DEFAULT 0; -- 변수 선언, 대소문자 구분 없다.
SET X =10;
SELECT X,Y; -- 콘솔 출력
SELECT X+Y; -- 두 변수의 합을 반환
END;-- 저장 프로시저의 본체 (본문 끝)
//
delimiter ;

CALL sp_1(1,3);

 

👏 중요

실행할때는 전체 범위를 설정하고 ctrl + f9을 눌러 실행해준다.

 

✏️ 프로시저 상태보기

SHOW PROCEDURE STATUS;

⌨ PROCEDURE 생성하기

SHOW CREATE PROCEDURE sp_1;

💻 프로시저 삭제하기

DROP PROCEDURE sp_1;

✔ 프로시저 안 IF, WHILE-DO, REPEAT

💻IF

delimiter //
CREATE OR replace PROCEDURE sp_4(IN jik VARCHAR(20) CHARSET utf8, num int)
BEGIN
SELECT jik;
SELECT * from jikwon WHERE jikwon_jik= jik;

if(num = 10) then
	SELECT * from jikwon WHERE buser_num=10;
elseif(num = 20) then
	SELECT * from jikwon WHERE buser_num=20;
ELSE
	SELECT * from jikwon WHERE buser_num not IN (10,20);
END if;

END;
//
delimiter ;

CALL sp_4('대리',20);

💻WHILE-DO

delimiter //
DROP PROCEDURE if EXISTS sp_5 //
CREATE PROCEDURE sp_5()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET X=1;
SET str = '';

while X <= 5 DO
	SET str=CONCAT(str,X,',');
	SET X = X + 1;
END while;

SELECT str;
END;
//
delimiter ;

CALL sp_5;

 

💻REPEAT

delimiter //
DROP PROCEDURE if EXISTS sp_6 //
CREATE PROCEDURE sp_6()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET X=1;
SET str = '';

repeat
	SET str=CONCAT(str,X,',');
	SET X = X + 2;
until X >5
END repeat;

SELECT str;
END;
//
delimiter ;

CALL sp_6;

✔일반 쿼리문과 저장 프로시저의 작동 순서

⌨ 일반 쿼리문 

개발자 ( SQL문 작성 )

1. 구문분석 : 구문 오류 확인
2. 개체 이름 확인 : 데이터(이름으로) 존재 유무확인,
3. 사용권한 확인 : 접근중인 사용자가 권한 확
4. 최적화 : 쿼리문의 최적의 경로를 결정. (인덱스 사용 여부로 결정)
5. 컴파일 및 실행계획 등록 > 계획 (캐시)메모리 저장
6. 실행

💻 저장 프로시저

[정의 단계]

1. 프로시저 만들기
2. 구문분석 : 구문 오류 파악
3. 지연된 이름 확인 : 저장 프로시저를 정하는 시점에서 해당 테이블이 존재하지 않아도된다. 프로시저 실행 당시 테이블 존재 여부 확인
4. 생성권한 확인 : 현재 사용자가 저장 프로시저를 생성 권한 확인
5. 시스템 테이블에 등록 : 저장 프로시저의 이름 또는 코드가 시스템 테이블에 등록된다.

테이블이 존재하면 확인하기때문에 테이블의 열이나 이름이 블리면 오류를 발생시킨다.

 

👏 중요

프로시저를 처음 실행할때만 일반 쿼리문 작동 방식과 동일한 방식으로 진행된다. (구문분석 X)

개제 존재 유무를 개체 이름 확인을 통해 수행된다.

두번째부터 저장 프로시저 실행은 메모리(캐시) 확인하고 실행을 한다.

😊정리

여러개의 쿼리를 한번에 실행할 수 있으며 두번째 사용부터는 캐시에 저장되어 있기에 실행 속도가 빨라진다.

쿼리를 쓸 때마다 옵티마이저가 구문을 분석하는 비용이 사라진다.

개발 언어에 의존적이지 않기에 유지보수에 효과적이다.

또한 사용자에게 테이블 자체 권한을 주지 않고 프로시저에 접근 권한을 주는 방식으로 보안을 강화할 수 있다.

쿼리문은 적게 전송할수록 좋다. 클라이언트에서 서버로 많은 텍스트가 전송될 경우 네트워크에 큰 부하를 발생시킨다.

저장 프로시저를 사용하면 프로시저의 이름, 매개변수 등 적은 데이터만 전송하면 되기 때문에 모든 텍스트를 전송하는 것보다 부하를 줄일 수 있다.

 

저장 프로시저 또한 적절하게 사용해야하는데,

개발된 프로시저가 여러 곳에서 사용되는 경우 수정했을 때 영향의 분석이 어렵다.

배포, 버전 관리 등에 이력이 힘들다.

서버의 수를 늘려야 할때, DB의 수를 늘리는 것이 더 어렵다.

문자, 숫자열 연산에 저장 프로시저를 사용하면 개발언어단보다 느린 성능을 보일 수 있다.