[SQL] 19장. 저장 프로시저 사용하기

김상현·2022년 10월 11일

sql

SQL

목록 보기

19/22

[손에 잡히는 10분 SQL - 벤 포터 지음, 박남혜 옮김] 책의 학습 후 정리자료입니다.

📍 저장 프로시저 이해하기

저장 프로시저 는 나중에 사용하기 위해 만들어둔 하나 이상의 SQL 명령문 집합을 의미하는데, 일종의 배치 파일로 생각해도 된다.

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

💡 저장 프로시저를 사용하는 주요 목적

여러 단계로 이루어진 과정을 사용하기 쉬운 하나의 단위로 캡슐화하여 복잡한 작업을 단순화한다.
여러 단계를 반복해서 만들 필요가 없어서 데이터 일관성을 보장한다. 모든 개발자와 프로그램이 같은 저장 프로시저를 사용한다면, 모두 똑같은 코드를 사용할 것이다.
오류 방지에 도움을 준다. 수행해야 할 단계가 많아질수록, 오류는 더욱 발생하기 쉽다. 오류를 방지하는 것은 데이터 일관성을 보장한다.
변경 관리를 단순화한다. 테이블, 열 이름, 비즈니스 로직이 변경되면, 저장 프로시저 코드만 바꾸고, 다른 것은 수정할 필요가 없기 때문에 다른 사람이 변경사항을 알 필요가 없다.
보안성을 높인다. 저장 프로시저를 사용하면 기본 데이터에 대한 접근을 제한할 수 있는데, 이 제한은 데이터 손상 가능성을 줄여준다.
저장 프로시저는 대개 컴파일된 형태로 저장되기 때문에, 명령을 처리하기 위해 DBMS가 해야 하는 일이 줄어들고, 그 결과 성능이 향상된다.
SQL 언어 요소와 기능 중에 하나의 요청 안에서만 사용해야 하는 것이 있다. 저장 프로시저는 좀 더 강력하고 유연한 코드를 작성하기 위해 이런 언어 요소와 기능을 사용할 수 있다.
저장 프로시저를 사용하면 얻는 세 가지 이점이 있는데, 이는 단순성, 보안성, 성능이다.

❗️ 저장 프로시저의 단점

저장 프로시저 문법은 DBMS마다 매우 달라서, 다른 DBMS로 이식할 수 있는 저장 프로시저를 작성하는 것은 거의 불가능에 가깝다. 그렇다고는 해도, 자신을 호출하는 저장 프로시저는 상대적으로 이식이 가능하다. 따라서 다른 DBMS로 바꿔야 하는 경우, 적어도 클라이언트 프로그램 코드는 변경하지 않을 수 있다.
저장 프로시저는 기본 SQL 문을 작성하는 것보다는 좀 더 복잡해서, 저장 프로시저를 작성하려면 고수준의 기술과 경험이 필요하다. 그래서 많은 데이터베이스 관리자는 보안 조치 중 하나로 저장 프로시저 생성권한을 제한한다.

📍 저장 프로시저 실행하기

🧷 예제 1

저장 프로시저를 실행하는 SQL 문은 EXCUTE 인데, 저장 프로시저의 이름과 전달할 필요가 있는 매개변수를 갖는다.

EXECUTE AddNewProduct('JTS01',
                      'Stuffed Eiffel Tower',
                      6.49,
                      'Plush stuffed toy with the text La Tour Eiffel
                      in red white and blue');

AddNewProduct 프로시저
- 네 개의 매개변수 모두에 값이 있는지 확인하고, 데이터를 전달한다.
- 기본 키로 사용하는 고유한 ID를 생성한다.
- Products 테이블에 새로운 행을 추가하고, 생성한 기본 키와 전달한 데이터를 적절한 열에 삽입한다.

사용하는 DBMS에 따라 다음에 나오는 실행 옵션을 추가로 사용할 수도 있다.
- 매개변수가 없으면, 기본값으로 설정할 것인지를 결정하는 선택적 매개변수
- '매개변수 = 값' 형식으로 지정하는 비순차적 매개 변수
- 저장 프로시저에서 프로그램을 실행할 때 사용하는 매개변수를 업데이트 할 수 있는 출력 매개변수
- SELECT 문을 이용한 데이터 검색
- 저장 프로시저에서 결과값을 실행 프로그램으로 전달하는 데 사용하는 반환 코드

📍 저장 프로시저 생성하기

🧷 예제 2

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

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;