By aPOST in SQL —

18. [SQL기초] 스토어드 프로시저

스토어드 프로시저 기초

용어가 조금 생소하지만 쉽게 프로그래밍 언어의 함수(function)와 같은 기능을 SQL로 구현한 것입니다. 스토어드 프로시저는 저장 프로시저라고도 합니다. 스토어드 프로시저는 특정 쿼리 작업들을 하나의 단위로 묶어서 사용할 수 있도록 모듈로 만들어진 데이터베이스의 객체입니다. 스토어드 프로시저에는 간단하지만 프로그래밍 기능을 사용할 수 있으며, 변수도 사용할 수 있습니다.

앞에서 다양한 내장 함수와 집계 함수를 배웠지만, 이들 함수들도 기본적으로는 스토어드 프로시저와 유사한 것입니다. 다른 점이 있다면 DBMS에 기본 내장되어 있어서 언제나 호출해서 사용할 수 있다는 점 정도입니다.

쉽게 말하면 스토어드 프로시저는 사용자가 정의한 SQL 함수라고 할 수 있습니다.

작성한 스토어드 프로시저는 데이터베이스에 객체로 저장되며, 언제나 가져다 사용할 수 있습니다. 스키마 레벨의 객체이기 때문에 데이터베이스 안에서 유일한 스토어드 프로시저 이름을 가져야 합니다.

데이터베이스 안에 별도의 객체로 관리되기 때문에 데이터베이스를 백업할 때 함께 백업도 가능합니다.

일반 프로그래밍 언어의 함수 작성 방법과 유사하게, 스토어드 프로시저를 선언하고 그 안에 내부 기능과 쿼리문을 작성한 후 실행하면 스토어드 프로시저가 생성됩니다.

스토어드 프로시저는 다음과 같은 장단점을 가지고 있습니다.

장점

  • 보안성이 높다.
  • 해킹에서 조금 더 안전하다.
  • 실행 속도가 더 빠르다.
  • 복잡한 데이터베이스 처리를 모듈화 할 수 있다.

단점

  • 초보자에게는 구현이 어렵다.
  • 중간에 에러가 발생하면 원인 파악이 번거롭다.
  • 전용 문법을 추가로 배워야 한다.

스토어드 프로시저는 시작과 끝을 표시하는 문장으로 스토어드 프로시저 블록을 구분합니다. 상품 테이블에서 품절된 상품 목록을 반환하는 스토어드 프로시저를 만들어보겠습니다.

SQL 쿼리문 컴파일과 스토어드 프로시저

컴파일은 사람이 이해할 수 있는 언어에 가까운 문법으로 작성된, 프로그램, 또는 코드를 컴퓨터(기계)가 이해할 수 있는 코드(보통은 바이트, 또는 바이너리 실행 코드)로 변환하는 과정을 말합니다.

거의 모든 언어들이 컴파일이라는 과정을 거처 실행 가능한 코드(윈도우에서는 .exe 파일로 생성)를 생성하며, 우리는 이 실행 가능한 코드, 즉  프로그램/앱 이라고 부르는 실행 파일을 실행해서 프로그램이 지원하는 기능을 사용하게 됩니다.

쿼리문도 컴파일을 합니다.

우리가 알고 있는 SQL 쿼리문은 사람이 이해할 수 있게 일정한 문법을 갖춰 작성하는 일종의 단순한 언어입니다.

쿼리문은 사람은 이해할 수 있지만 컴퓨터, 또는 데이터베이스가 이해할 수 있는 실행 코드가 아닙니다.

따라서 쿼리문을 실행해서 데이터베이스에서 원하는 데이터를 얻으려면, 반드시 컴파일이라는 과정을 거친 후 쿼리코드가 실행되고 결과를 반환하게 됩니다.

엄밀히 말하면 컴파일은 실행 가능한 바이너리(실행파일)를 만드는 것을 말합니다. 쿼리문 컴파일은 엄밀한 의미로는 컴파일이 아니며, DBMS가 읽을 수 있는 바이트코드 형태로 인터프리팅(Interpreting)을 한  쿼리 커맨드 리스트를 생성하는 것입니다.
쿼리문을 인터프리팅(Interpreting) 해서 빠르게 실행 가능한 DBMS 고유의 커맨드 리스트로 변환하는 것이기 때문에, DBMS에서 지원을 한다면 SQL 쿼리문이 아닌 다른 언어(자바나 파이썬 등)로도 데이터를 쿼리할 수 있습니다.
오랫동안 관행적으로 SQL을 컴파일 한다고 써왔기 때문에 그렇게 사용하는 것일 뿐입니다.

프로그램 코드 안에 들어있는 쿼리문은, 프로그램을 수정할 때는 쿼리문 자체를 볼 수 있으므로 어떤 데이터를 가져올 것이라는 것을 사람이 쉽게 알아볼 수 있습니다. 단, 이 쿼리문이 데이터베이스로 전달되어 실행될 때는 매번 컴파일을 하게 됩니다.

당연히 약간의 쿼리문 컴파일 시간 지연이 추가로 생기게 됩니다.

현실 세계에서는 빈번하게 호출되는 쿼리문은 DBMS가 캐싱이나 기타 최적화, 또는 속도 향상 기법을 사용해 쿼리 컴파일 및 결과를 얻는 속도를 올리는 다양한 방법을 사용합니다.
무조건 매번 쿼리문을 컴파일해서 실행하는 것은 아닙니다.

반면에 스토어드 프로시저로 작성한 쿼리문은 미리 컴파일되어 저장됩니다.

따라서 쿼리가 실행되는 시간에는 컴파일이 필요 없고, 즉시 실행해서 쿼리 결과를 얻게 됩니다.

스토어드 프로시저는 다르게는 프리 컴파일드 쿼리문(PreCompiled Query)이라고 할 수 있습니다.

데이터베이스를 이용하는 프로그램에서 데이터 쿼리 속도를 올리는 가장 기본적이면서 쉬운 방법은 빈번한 쿼리문들을 최우선으로 스토어드프로시저로 미리 생성해 두는 것입니다.

딜리미터(Delimiter) 변경

스토어드 프로시저를 작성하기 전에 딜리미터(delimiter)에 대해서 알아야 합니다.

딜리미터는 쿼리 실행문의 끝에 붙이는 문의 종료 표시, 또는 구분자입니다. 표준 SQL의 딜리미터 기호는 세미콜론(;)입니다. 딜리미터는 고정된 기호가 아니며 필요에 따라 변경할 수 있습니다. 딜리미터를 변경해야 하는 경우는 여러 개의 쿼리문을 하나의 덩어리로 저장했다 가져와서 실행해야 하는 경우입니다.

대표적인 경우가 스토어드 프로시저입니다. 스토어드 프로시저는 정의한 스토어드 프로시저 블록 안의 전체 코드들과 쿼리문을 그대로 저장해야 합니다. 블록 안에 코드에는 세미콜론이 여러 번 나오게 되고 스토어드 프로시저를 저장하기 위해 정의한 스토어드 프로시저 블록을 실행하면 세미콜론으로 구분한 쿼리문들이 각각 실행됩니다.

정의한 스토어드 프로시저를 저장할 수 없게 됩니다.

그래서 기본 딜리미터를 다른 것으로 변경해서 스토어드 프로시저 블록 안의 쿼리문 들이 각각 실행되지 않고 코드로 그대로 저장되도록 해야 합니다.

구분하기 쉽고, 혼동되지 않도록 “$$”나 “//” 정도로 딜리미터를 변경해서 사용합니다.

DELIMITER $$

그리고 딜리미터를 변경해서 사용한 용도가 끝나면 반드시 원래의 기본 딜리미터로 복구해야 합니다.

DELIMITER ;

딜리미터 변경은 MySQL에서만 적용되는 내용이며, 다른 데이터베이스에서는 스토어드 프로시저 등의 기능 사용을 위해 딜리미터 변경을 할 필요가 없습니다.

스토어드 프로시저 정의

먼저 딜리미터를 변경해주고 CREATE PROCEDURE 명령어로 스토어드 정의 블록을 시작합니다. 공백의 띄워서 스토어드 프로시저 이름을 표기해주고 괄호를 붙여 파라미터 넣는 영역을 위치를 표시합니다.

함수를 작성하는 것과 유사합니다. 스토어드 프로시저 이름은 SQL의 객체 이름을 정하는 규칙을 그대로 따라갑니다. 그리고 스토어드 프로시저 이름 앞에는 스토어드 프로시저임을 이름만으로 구분할 수 있는 식별자를 붙여주는 것이 좋습니다.

예를 들어 “sp_”, “p_” 와 같은 접두어를 일관되게 붙여서 관리하면 스토어드 프로시저를 유지보수하기가 편합니다.

DELIMITER $$

create procedure sp_getOutOfStock()

실제로 스토어드 프로시저 코드를 넣을 블록을 선언해야 합니다. BEGIN 과 END로 블록을 선언합니다. 그리고 END뒤에는 쿼리문의 끝을 표시하는 딜리미터를 꼭 붙여야 합니다. 앞에서 딜리미터를 “$$”로 바꿨으므로 “$$”를 붙입니다.

begin

end$$

스토어드 프로시저 블록이 끝난 후에는 딜리미터를 원래대로 복구해야 하므로 DELIMITER 명령어로 기본 딜리미터 기호로 변경합니다.

DELIMITER ;

스토어드 프로세저의 기본 구조가 완성되었습니다. 실제 스토어드 프로시저의 블록 안에는 실행되는 코드가 아무 것도 없지만, 스토어드 프로시저는 동작합니다. 완성된 스토어드 프로시저는 다음과 같습니다. 딜리미터를 포함해 쿼리문 전체를 실행하면 스토어드 프로시저가 저장됩니다.

DELIMITER $$
create procedure sp_getOutOfStock()
begin
end$$
DELIMITER ;

 

스토어드 프로시저 실행 코드 추가

BEGIN과 END 사이에 실행 코드를 넣어 스토어드 프로시저가 반환하는 내용이 있도록 만들어야 합니다. 품절 상품 목록을 얻는 쿼리문을 블록 안에 추가합니다.

DELIMITER $$
create procedure sp_getOutOfStock()
    begin
        select a.id, a.name, b.stock from product a left join inventory b on a.id = b.p_code where b.stock = 0;
    end$$
DELIMITER ;

스토어드 프로시저 실행

저장된 스토어드 프로시저를 실행하려면 CALL 명령어로 스토어드 프로시저를 함수처럼 호출하면 됩니다.

call sp_getOutOfStock();

스토어드 프로시저가 실행되고 품절된 상품 목록이 표시됩니다.

스토어드 프로시저 삭제

DROP PROCEDURE 명령어로 등록한 스토어드 프로시저를 삭제할 수 있습니다. 앞서 만들었던 스토어드 프로시저를 삭제하려면 다음과 같이 쿼리문을 작성합니다. 삭제할 때는 스토어드 프로시저를 저장하거나 호출할 때와 다르게 끝에 괄호 없이 이름만 표기합니다.

drop procedure sp_getOutOfStock;

조금 더 세련되게 스토어드 프로시저가 등록되었을 때가 삭제가 되도록 하려면 다음과 같이 IF문으로 먼저 체크를 해서 삭제할 수 있습니다.

drop procedure if exists sp_getOutOfStock;

저장된 스토어드 프로시저는 수정을 할 수 없기 때문에 저장된 스토어드 프로시저를 수정하려면 삭제 후 새 스토어드 프로시저를 등록해야 합니다.

 

스토어드 프로시저 파라메터 사용

스토어드 프로시저는 프로그래밍 언어의 함수 기능처럼 파라미터 변수들을 사용해 스토어드 프로시저로 값을 넘겨줄 수 있습니다. 금액 숫자를 파라미터로 받아서 해당 금액 이상인 주문 목록을 가져오는 스토어드 프로시저를 만들어보겠습니다.

DELIMITER $$
create procedure sp_getOrderPrice (IN price int)
    begin
                 select * from p_order where p_sell_price >= price;
    end$$
DELIMITER ;

스토어드 프로시저로 넘기는 파라메터는 다음과 같이 정의합니다.

CREATURE PROCEDURE 프로시져이름(파라메터타입 변수이름 데이터 형)

파라미터 타입은 “IN”, “OUT”, “INOUT” 중 하나입니다. “IN”은 스토어드 프로시저 안에서 사용할 변수 값을 파라미터로 받는 것이고, “OUT”은 스토어드 프로시저에서 생성한 값을 스토어드 프로시저 밖으로 전달할 대 사용합니다. “INOUT”은 입출력에 모두 사용하는 변수에 사용합니다. 파라미터 타입이 없으면 “IN” 타입으로 정의됩니다.

변수명과 데이터 형은 테이블 열을 정의하는 것과 같은 방법으로 정의합니다.

“INOUT” 파라메터 타입의 파라미터로 값을 받아서 계산 결과를 반환하는 스토어드 프로시저를 만들어보겠습니다.

set @num = 5;
DELIMITER $$
CREATE PROCEDURE NextNumber( INOUT  num INT )
BEGIN
                 SET num = num + 1;
END$$
DELIMITER ;
CALL NextNumber(@num);
CALL NextNumber(@num);
CALL NextNumber(@num);
select @num;

5인 변수 값은 스토어드 프로시저 3번 실행 후 8로 변경됩니다.

값이 5인 변수를 스토어드 프로시저 파라메터로 넘겨서 변수 값에 1을 더해서 반환합니다. “OUT”, “INOUT” 파라미터 속성으로 정의된 변수는 변수의 참조를 받기 때문에 파라미터로 받은 변수의 값이 외부 변수에 그대로 적용됩니다.

스토어드 프로시저 안에 변수 사용

스토어드 프로시저 안에 변수를 정의해 계산식 결과를 저장할 수 있습니다. 그리고 쿼리문 결과를 가공해서 새로운 결과를 테이블에 다시 저장하는 복잡한 처리도 가능합니다.

기초 구문

Declare 변수명 데이터 형 default 기본값;

테이블 데이터를 페이지 단위로 나누어서 가져오는 페이징 기능을 스토어드 프로시저로 만들어 보겠습니다. 페이지 크기와 페이지 위치를 자유롭게 변경할 수 있도록 파라메터로 페이지 크기와 페이지 위치를 받아서 오프셋 할 행 수를 저장하는 변수에 오프셋 계산 결과를 저장합니다.

DELIMITER $$
create procedure sp_getOutOfStock (IN pagesize int, IN page int)
begin
                 declare offset int default (page-1)*pagesize;
                 select * from sms limit offset, pagesize;
end$$
DELIMITER ;

스토어드 프로시져 호환성

스토어드 프로시저는 기본 구조는 비슷하지만 스토어드 프로시저를 정의하는 문법이 데이터베이스마다 약간씩 다릅니다. 오라클은 다음과 같이 스토어드 프로시저를 생성합니다.

Create procedure sp_getOutOfStock AS
Result SYS_REFCURSOR;
Begin
    open result for
    select a.id, a.name, b.stock from product a left join inventory b on a.id = b.p_code where b.stock = 0;
End;

오라클은 스토어드 프로시저 수정을 위한 ALTER PROCEDURE 명령어 구문을 지원합니다.

SQL Server는 다음과 같이 스토어드 프로시저를 작성해야 합니다.

Create procedure sp_getOutOfStock AS
    select a.id, a.name, b.stock from product a left join inventory b on a.id = b.p_code where b.stock = 0;
go
SQL 관련글
글 36 개 모두보기 →