SP 작성 및 사용

SP(Stored Procedure)란?

스토어드 프로시저란 DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합

스토어드 프로시져는 뷰와 같이 SQL문 덩어리로 되어 있고 비슷하지만 활용도는 차이가 있다.

우선 매개변수의 사용으로 사용자에게서 변수값을 받을 수도 있고 또 그 값을 output으로 밖으로 되돌려줄수도 있다.

또한 일반적인 쿼리와는 좀 다른 처리 방식을 가진다.

프로시져 생성과 반복

1) 만들 때

  1. 구문분석(Parsing) : 키워드를 분리하고 문법검사를 한다.

  2. 표준화 : 각 object의 이름을 확인한다.

  3. 보안점검 : 저장 프로시져를 만들 수 있는 권한이 있는지 검사한다.

  4. 결과는 syscomments와 sysobjects에 저장한다.

 

2) 첫 실행할 때

  1. 보안점검 : 실행할 수 있는 권한을 검사한다.

  2. 최적화 : 가장 빠른 성능을 낼 수 있도록, 색인, 조인, 잠금 등에 대한 것을 결정한다.

  3. 컴파일하고 이에 따른 실행계획(어떻게 실행할 것인지에 대한 계획서)을 만들어 캐쉬에 저장하고 실행한다.

 

3) 반복해서 실행할 때

  -캐쉬에 실행계획이 있을 때 : 그것을 그대로 이용하여 실행한다. (더 이상의 컴파일 작업이 없다.)

  -캐쉬에 실행계획이 없을 때 : 처음 실행할 때의 과정을 반복한다.

 

얻는 이점

-매번 컴파일을 하지 않아서 빠르다.

-네트웍에서 오고 가는 긴 sql문의 네트웍 트래픽도 줄일 수 있다.

-보안관리를 쉽게 한다.

-매개변수를 사용할 수 있다.

-output 매개변수를 사용할 수 있다.

-return 값을 사용할 수 있다.

-원격프로시져를 이용하면 원격 서버의 데이터를 처리 할 수 있다.

 

예제1

---

기본 데이터 생성

CREATE TABLE SAMPLE_TEST1
(
  CODE VARCHAR(4) NOT NULL PRIMARY KEY,
  CODE_DES NVARCHAR(10)
);
INSERT INTO SAMPLE_TEST1 (CODE,CODE_DES)VALUES('1000','일반코드1000');

간단한 테이블 조회 쿼리 작성

SELECT *
FROM SAMPLE_TEST1;

create proc 구문을 더하면, Stored Procedure를 생성하는 구문을 작성할 수 있습니다.

CREATE PROC ESP_TEST1
AS
SELECT *
FROM SAMPLE_TEST1;

실행은

EXEC ESP_TEST1

 

예제2

---

일반 insert 쿼리

INSERT INTO SAMPLE_TEST1 (CODE,CODE_DES)VALUES('1001','일반코드1001');

파라미터를 받을 수 있게 추가

CREATE PROC ESP_TEST2(
 @CODE VARCHAR(4),
 @CODE_DES NVARCHAR(10)
)
AS
INSERT INTO SAMPLE_TEST1 (CODE,CODE_DES)VALUES(@CODE,@CODE_DES);

실행시 파라미터 값 추가

EXEC ESP_TEST2 @CODE='1001',@CODE_DES='일반코드1001';

 

예제3

---

예제2를 동적 쿼리로 구현하기

CREATE PROC ESP_TEST3(
 @CODE VARCHAR(4),
 @CODE_DES NVARCHAR(10)
)
AS
BEGIN
       DECLARE @SQLSTR NVARCHAR(MAX),@PARMDEFINITION NVARCHAR(MAX);
       SET @PARMDEFINITION = N' @CODE VARCHAR(4),@CODE_DES NVARCHAR(10)';
       SET @SQLSTR = N'INSERT INTO SAMPLE_TEST1 (CODE,CODE_DES)VALUES(@CODE,@CODE_DES); ';
       EXEC sp_executesql @SQLSTR, @PARMDEFINITION, @CODE,@CODE_DES;
END

실행

EXEC ESP_TEST3 @CODE='1002',@CODE_DES='일반코드1002';