19장 저장 프로시저 사용하기

저장 프로시저 이해하기

지금까지 사용한 SQL 문은 한 개 이상의 테이블에 단일한 구문을 사용하는 간단한 구문이다. 하지만 종종 복잡한 작업을 수행하기 위해 다수의 구문을 사용할 필요가 있다. 이때 여러 테이블과 다양한 SQL 문이 필요하지만 수행할 정확한 SQL 명령문과 순서는 고정된 것이 아니다. 언제든지 변경될 수 있다.

그렇다면 코드를 어떻게 작성해야 할까?

일단, SQL 문을 따로 작성한 후 결과에 따라 조건에 맞게 명령문을 수동으로 실행할 수 있다. 또 다른 방법은 저장 프로시저를 사용하는 것이다. 저장 프로시저란 하나 이상의 SQL 명령문 집합으로 일종의 배치 파일이다.

저장 프로시저를 사용하는 이유

• 여러 단계로 이루어진 과정을 사용하기 쉬운 하나의 단위로 캡슐화해 복잡한 과정을 단순화한다.

• 여러 단계를 반복해서 만들 필요가 없어 데이터 일관성을 보장한다.

• 오류 방지에 도움을 준다.

• 변경 관리를 단순화한다.

• 보안성을 높인다.

• 명령을 처리하기 위해 DBMS가 해야 하는 일이 줄고, 성능이 향상된다.

• SQL 언어 요소와 기능 중 하나의 요청 안에서 사용하는 것이 있는데, 저장 프로시저는 이러한 언어 요소와 기능을 사용해 더 강력하고 유연한 코드 작성이 가능하다.

등등 많은 이유가 있지만 세 가지 주된 이점은 단순성, 보안성, 성능이다.

저장 프로시저의 단점은 다음과 같다.

• DBMS마다 저장 프로시저 문법이 달라 다른 DBMS로 이식하기 어렵다.

• 기본 SQL 문 작성보다 복잡해 고수준의 기술과 경험이 필요하다.

이러한 단점에도 저장 프로시저는 매우 유용하다.

저장 프로시저 실행하기

저장 프로시저를 실행하는 SQL 문은 EXECUTE 이다. 저장 프로시저 이름과 매개변수를 전달한다.

EXECUTE AddNewProduct ('JTS01', 'Stuffed Eiffel Tower', 6.49, 'asdfadf');
-- AddNewProduct라는 이름의 저장 프로시저를 실행한다.
-- 이는 Products 테이블에 새로운 제품을 추가한다.
-- 전달한 네 개의 매개변수는 각각 판매처ID, 제품명, 가격, 설명이다.
-- 이 저장 프로시저는 Products 테이블에 새로운 행을 하나 추가하고, 적절한 열에 매개변수를 전달한다.

Products 테이블에는 기본 키인 prod_id가 필요한데 위의 예시에서는 매개변수로 전달하지 않았다.

왜?

ID를 제대로 생성하려면, 사용자에게 의존하는 것보다 프로세스가 자동으로 처리하는 것이 더 안전하다.

AddNewProduct는 다음의 역할을 수행한다.

• 네 개의 매개변수에 값이 있는지 확인하고, 데이터를 전달한다.

• 기본 키로 사용하는 고유한 ID를 생성한다.

• Products 테이블에 새로운 행을 추가하고, 생성한 기본 키와 전달한 데이터를 적절한 열에 삽입한다.

위 단계는 기본적인 저장 프로시저의 실행 형식이다. DBMS에 따라 추가적인 실행 옵션을 사용 가능하다.

저장 프로시저 생성하기

저장 프로시저 작성은 쉬운 일이 아니다. 예시를 보자.

-- Oracle ver.
-- 이메일 주소를 가진 고객의 수를 세는 저장 프로시저
CREATE PROCEDURE MailingListCount (
    ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
    SELECT count(*) INTO v_rows
    FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := v_rows;
END;

MailingListCount는 ListCount라는 이름의 매개변수를 갖는다. 이 매개변수는 값을 전달하는게 아니라 결괏값을 가져온다. OUT 키워드는 이런 행동을 명시하기 위해 사용한다. ORACLE은 IN, OUT, INOUT 형의 매개변수를 지원하는데 IN은 저장 프로시저로 값을 전달하기 위해, OUT은 저장 프로시저에서 값을 반환하기 위해, INOUT은 두 용도 모두 쓰인다. 저장 프로시저 코드는 BEGIN과 END 문으로 묶여있고, 그 사이에 이메일 주소를 가진 고객을 가져오기 위해 SELECT 문을 수행한다. 그런 다음 ListCount가 가져온 행의 수로 설정된다.

이 코드는 다음과 같이 호출한다.

var ReturnValue NUMBER -- 변수 선언
EXEC MailingListCount(:ReturnVALUE); -- 저장 프로시저에 매개변수로 전달
SELECT ReturnValue; -- SELECT 문으로 가져온 값 출력

이 외에도 SQL Server, Microsoft SQL Server 등 DBMS마다 수행하는 방법이 다르다.

정리하기

• 저장 프로시저란

• 저장 프로시저의 실행과 작성 문법