[SQL] 저장 프로시저(Procedure)

저장 프로시저

저장 프로시저 또는 스토어드 프로시저(stored procedure)는 하나 이상의 SQL 문을 나중에 사용하기 편리하게 저장해 둔 것으로, 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합, 간단하게 배치 파일이라고 볼 수도 있다.

※ 만들 순 없어도 사용할 순 있다.

저장 프로시저의 생성 권한과 실행 권한은 대부분의 DBMS에서 분리되어 있다. 즉 만들 수는 없어도 사용할 순 있다는 뜻이다.

저장 프로시저의 장점

단순성, 보안, 성능

• 복잡한 작업을 사용하기 쉽게 하나의 단위로 묶어 단순화
• 여러 단계를 반복적으로 만들 필요 없이 데이터의 일관성 유지 -> 코드의 재활용, 오류 방지, 오류 방지를 통한 데이터 일관성 유지
• 변경 내용 관리 단순화 -> 테이블, 열 이름 또는 비즈니스 로직 등이 변경될 경우, 저장 프로시저만 업데이트하면 됨 -> 보안에 도움 (저장 프로시저만 사용하고 기반 데이터에는 액세스할 수 없도록 제한하면 데이터가 훼손되는 것을 방지할 수 있다.)
• 성능 개선 -> 저장 프로시저는 컴파일된 형식으로 저장되기 때문에 DBMS가 이 명령을 실행하기 위해 수행하는 작업이 적다.
• 강령하고 유연한 코드 작성 가능 -> 단순한 요청에서는 사용할 수 없는 SQL 언어 요소와 기능을 저장 프로시저에서는 사용 가능

저장 프로시저의 단점

• 저장 프로시저의 성능은 DBMS에 따라 다름 -> 모든 DBMS에서 작동하는 저장 프로시저를 만들기는 거의 불가능
• 높은 수준의 기술과 경험 필요(기본적인 SQL 문을 사용하는 것보다 복잡) -> 많은 데이터베이스 관리자가 저장 프로시저 생성 권한에 대한 보안을 위해 아무에게나 허용하지 않도록 하고 있다.

저장 프로시저 만들기

저장 프로시저를 생성할 때는 CREATE PROCEDURE 문을 사용한다. Oracle과 같은 경우 아래와 같이 사용한다.

CREATE PROCEDURE 저장_프로시저_이름

  IN argument

  OUT argument

  IN OUT argument

IS [변수의 선언]

BEGIN

SQL 문1

SQL 문2

END

CREATE PROCEDURE MailingListcount

(ListCount OUT NUMBER)

IS

BEGIN

SELECT * FROM Customers

WHERE NOT cust_email IS NULL;

ListCount := SQL%ROWCOUNT;

END;

매개변수

• IN 매개변수는 참조만 가능하며 값을 할당할 수 없다.
• OUT 매개변수에 값을 전달할 수는 있지만 의미는 없다.
• OUT, IN OUT 매개변수에는 디폴트 값을 설정할 수 없다.
• IN 매개변수에는 변수나 상수, 각 데이터 유형에 따른 값을 전달할 수 있지만, OUT, IN OUT 매개변수를 전달할 때는 반드시 변수 형태로 값을 넘겨줘야 한다.

저장 프로시저 실행

저장 프로시저는 한 번 이상 실행될 내용을 묶은 것이기 때문에 자주 실행된다. 저장 프로시저는 EXECUTE 문으로 실행시킬 수 있다.

EXECUTE AddNewProduct ('JTS01', 

'Stuffed Eiffel Tower', 

6.49,

'Plush stuffed toy with the text La Tour Eiffel in red white and blue')