MSSQL 저장 프로시져

 

 

 

 

=================================

=================================

=================================

 

 

 

 

 

 

출처: http://blog.daum.net/lords001/582394

** STORED PROCEDURE **

일반 SQL구문을 수행할 경우
- 처음 수행시
1. 구문 분석단계 - SQL구문의 문법을 검사합니다.
2. 표준화 - DB의 개체들(테이블 등)에 대해서 검사합니다.
3. 보안 점검 - 해당하는 DB개체들에 대한 사용자의 사용 권한을 검사합니다.
4. 최적화 - 최적의 성능을 내기위한 사항을 적용합니다.
5. 컴파일
　
- 반복 수행시
1. 처음 수행한 일반 SQL구문의 실행 계획이 캐싱되어 있는지 확인후 이를 수행합니다
2. 캐싱되어 있지 않을 경우 처음수행시의 5단계를 다시 수행함.


저장 프로시져를 수행할 경우
- 생성시
1. 구문 분석 단계 - 프로시져 생성 구문의 SQL구문들을 검사합니다.
2. 표준화 - DB의 개체들(테이블 등)에 대해서 검사합니다.
3. 보안 점검 - 프로시져에 대해서 생성이 가능한지 검사합니다.
4. 해당 프로시져의 구문과 생성 정보를 저장합니다.
　
- 첫 실행시
1. 보안 점검 - 해당하는 DB개체들에 대한 사용자의 사용 권한을 검사합니다.
2. 최적화 - 최적의 성능을 내기위한 사항을 적용합니다.
3. 컴파일후 수행 계획을 생성후 캐시에 저장후 실행합니다.
　
- 반복 수행시
1. 실행 계획이 캐싱이 된지 확인후 실행 합니다.
2. 캐시에 실행 계획이 없을 경우 - 처음 실행 과정을 반복 합니다.


USE pubs
GO

--프로시져 생성구문
CREATE PROC proc_test
AS
SELECT * FROM titles
GO
--프로시져 수행 구문
EXEC proc_test



CREATE PROC proc_test1
AS
SELECT pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값
FROM titles
GROUP BY pub_id, type, royalty, ytd_sales
WITH CUBE
GO

EXEC proc_test1


--프로시져 수정구문
ALTER PROC proc_test1
AS
SELECT pub_id
FROM titles
GO

EXEC proc_test1


--프로시져 삭제
DROP Proc proc_test1


CREATE PROC proc_test3
AS
SELECT pub_id, title
FROM titles
GO

EXEC proc_test3

--프로시져 생성구문 조회
sp_helptext proc_test3


--저장 프로시져 생성구문 암호화
ALTER PROC proc_test3
WITH ENCRYPTION
AS
SELECT pub_id, title
FROM titles
GO

EXEC proc_test3

sp_helptext proc_test3


** 저장 프로시져의 종류
1. 사용자 정의 저장 프로시져
예) proc_test3
2. 시스템 저장 프로시져
예) sp_help, sp_helptext 와 같이 sp_로 시작하는 프로시져.
3. 확장 시스템 저장 프로시져 - xp_ 로 시작하는 프로시져를 의미
예) EXEC master..xp_cmdshell 'dir'



** 매개변수를 사용한 저장 프로시져 **
CREATE PROC proc_test4
AS
SELECT * FROM titles

EXEC proc_test4


CREATE PROC proc_test5
@v_price int
AS
SELECT * FROM titles WHERE price > @v_price

EXEC proc_test5 30
EXEC proc_test5 50


CREATE PROC proc_test6
@v_title varchar(10)
AS
SELECT * FROM titles WHERE type = @v_title

EXEC proc_test6 'business'
EXEC proc_test6 business
EXEC proc_test6 'mod_cook'



CREATE TABLE TB_test7(
c1 int
, c2 varchar(10)
)

CREATE PROC proc_test7
@v_c1 int
, @v_c2 varchar(10)
AS
INSERT INTO TB_test7(c1, c2) VALUES(@v_c1, @v_c2)

EXEC proc_test7 1, 'Hi~'
EXEC proc_test7 2, '테스트'

SELECT * FROM TB_test7

--문자열 생성 방식
CREATE PROC proc_test8
@v_tblname varchar(20)
, @v_title_id varchar(20)
AS
--저장할 변수 선언
DECLARE @v_strSQL VARCHAR(200)
--변수 @v_strSQL에 생성된 문자열 저장
SET @v_strSQL = 'SELECT * FROM ' + @v_tblname + ' WHERE title_id = ''' + @v_title_id + ''''
EXEC(@v_strSQL)
--SELECT @v_strSQL

EXEC proc_test8 'titles', 'BU1032'
EXEC proc_test8 'titles', 'BU1111'
EXEC proc_test8 'titleauthor', 'BU1111'
EXEC proc_test8 'titleauthor', 'BU1032'
　
/*
--EXEC(@v_strSQL)
SELECT @v_strSQL 처음 수행시는 이처럼..
수행을 하는 구문인 EXEC 구문대신에.. 아래처럼 SQL구문이 SELECT되게 하는
프로시져를 생성 합니다.
그러면 위에서 약간 복잡한 해당하는 SQL구문이 구성된 녀석이 프로시져 수행시
리턴 될겁니다. 그런후 결과창의 구성된 SQL구문을 질의 분석기(쿼리 어낼라이져)로
옮기신후 여기서 수행해 보신후 잘 생성이 되었나 안되었나를 알아 보신후
테스트 해 보시면 되겠지요. 그런후 ALTER PROCEDURE 구문으로
해당 프로시져를
EXEC(@v_strSQL)
--SELECT @v_strSQL
이러한 식으로 EXEC - 수행되게 변경을 하신후.. 프로시져를 수행 하시면?
결과가 잘 나오는 것을 보실 겁니다. */


-- 문자열 생성 방식을 사용해야 하는 것인가?
테이블명과 같은 객체형 값(Object Value)는 프로시져의 매개변수로
사용이 불가 합니다.
CREATE PROC proc_test9
@v_tblname varchar(20)
AS
SELECT * FROM @v_tblname

CREATE PROC proc_test9
@v_title_id varchar(20)
AS
SELECT * FROM titles WHERE title_id = @v_title_id

EXEC proc_test9 'BU1032'



** TOP 구문을 이용할 경우
--프로시져 생성 - 수행 안됨
CREATE PROC proc_test10
@v_topN int
AS
SELECT top @v_topN * FROM titles

--프로시져 생성 - 프로시져 생성은 가능. 수행은 불가
CREATE PROC proc_test10-1
@v_topN int
AS
DECLARE @v_strSQL VARCHAR(200)
SET @v_strSQL = 'SELECT top ' + @v_topN + ' * FROM titles'
EXEC(@v_strSQL)

--프로시져 수행. 수행 불가
EXEC proc_test10-1 '10'


--프로시져 생성
CREATE PROC proc_test10-2
@v_topN varchar(5) --여기가 틀림
AS
DECLARE @v_strSQL VARCHAR(200)
SET @v_strSQL = 'SELECT top ' + @v_topN + ' * FROM titles'
EXEC(@v_strSQL)

--프로시져 수행
EXEC proc_test10-2 '10'




** 저장 프로시져 옵션 **

1) OUTPUT

--OUTPUT을 사용한 저장 프로시져
CREATE PROC proc_test11
@v_title_id varchar(10)
, @v_output int OUTPUT
AS
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
SET @v_output = (SELECT @@ROWCOUNT)
--@@ROWCOUNT : 테이블에서 영향을 받은(수정, 삭제, 삽입 등) 행의 수를 리턴

--프로시져 수행
DECLARE @v_effected_rows int
EXEC proc_test11 'BU1032', @v_effected_rows OUTPUT
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC proc_test11 'BU103X', @v_effected_rows OUTPUT
SELECT @v_effected_rows


* SELECT가 포함된 프로시져의 OUTPUT 매개변수 사용
--OUTPUT을 사용한 저장 프로시져 SELECT 포함
CREATE PROC proc_test12
@v_title_id varchar(10)
, @v_output int OUTPUT
AS
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
SELECT * FROM titles
SET @v_output = (SELECT @@ROWCOUNT)

--프로시져 수행
DECLARE @v_effected_rows int
EXEC proc_test12 'BU1032', @v_effected_rows OUTPUT
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC proc_test12 'BU103X', @v_effected_rows OUTPUT
SELECT @v_effected_rows

위의 결과 - OUTPUT 값을 ASP에서 받아서 사용하실 수 없습니다.
이유인 즉슨.. 결과셋이 3개이기 때문입니다.
1. UPDATE 결과셋
2. SELECT 결과셋
3. OUTPUT 결과셋


편법을 이용해 2개의 결과셋으로 줄이는 방법
--OUTPUT을 사용한 저장 프로시져 SELECT 포함
CREATE PROC proc_test13
@v_title_id varchar(10)
, @v_output int OUTPUT
AS
SET NOCOUNT ON
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
SELECT * FROM titles
SET @v_output = (SELECT @@ROWCOUNT)

--프로시져 수행
DECLARE @v_effected_rows int
EXEC proc_test13 'BU1032', @v_effected_rows OUTPUT
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC proc_test13 'BU103X', @v_effected_rows OUTPUT
SELECT @v_effected_rows

/*
바로 SET NOCOUNT ON 이라는 녀석 입니다.
결과셋중 UPDATE 결과셋은 리턴 값이 없는.. 오로지 영향받은 행의 수만 리턴 합니다.
이럴 경우 SET NOCOUNT ON을 이용해 결과 값이 없을 경우 결과 셋으로 처리 안하실
수 있습니다. - 이러면 UPDATE 결과셋이 안넘어 오니 두개의 결과셋이 오지요.
COMPUTE의 결과셋 - (여러개의 결과셋)을 이용할 수 없는 이유와 같다고 보시면 됩니다.
ASP에서는 이를 받아서 처리하실 수 없습니다.
하지만.. VB + DB 나.. ASP + 컴퍼넌트 + DB 를 이용할 경우 사용이 가능합니다.
ADO의 Recordset 메서드 중에서 NextRecoedset을 이용하면 사용이 가능합니다.
하지만. ASP + DB일 경우 사용이 불가 합니다. 이점 주의하세요. */



2) RETURN

--RETURN을 사용한 저장 프로시져
CREATE PROC proc_test14
@v_title_id varchar(10)
AS
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
RETURN @@ROWCOUNT

--프로시져 수행
DECLARE @v_effected_rows int
EXEC @v_effected_rows = proc_test14 'BU1032'
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC @v_effected_rows = proc_test14 'BU103X'
SELECT @v_effected_rows


* SELECT가 포함된 RETURN을 이용하는 방식
--RETURN을 사용한 저장 프로시져 SELECT 포함
CREATE PROC proc_test15
@v_title_id varchar(10)
AS
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
SELECT * FROM titles
RETURN @@ROWCOUNT

--프로시져 수행
DECLARE @v_effected_rows int
EXEC @v_effected_rows = proc_test15 'BU1032'
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC @v_effected_rows = proc_test15 'BU103X'
SELECT @v_effected_rows


3) 지연된 이름 확인
프로시져 내부에서 다른 저장 프로시져를 호출할 수 있다.
32개 까지 중첩이 가능합니다

--up_konan99라는 존재하지 않는 프로시져 수행
CREATE PROC proc_test16
AS
SELECT TOP 1 * FROM titles
EXEC proc_test50

--오류 메세지
존재하지 않는 개체 'proc_test50'에 의존하고 있으므로
현재 저장 프로시저의 sysdepends에 행을 추가할 수 없습니다.
저장 프로시저는 만들어집니다.

--수행결과는?
EXEC proc_test16

--proc_test50 프로시져 생성
CREATE PROC proc_test50
AS
SELECT TOP 10 * FROM titles

--수행결과는?
EXEC proc_test16
EXEC proc_test50



* 생성된 저장 프로시져의 정보보기
--sysobjects 테이블에서 타입은 프로시져이고
--이름이 proc_test16 인 프로시져의 name과 id 조회
SELECT name, id FROM sysobjects WHERE xtype = 'P'
and name = 'proc_test16'

--해당 ID값으로 syscomments 테이블에서 데이터 조회
SELECT id, text FROM syscomments where id = 434100587

--참고로 sysdepends 테이블에서 의존성을 확인해 보자.
SELECT id, depid FROM sysdepends where id = 434100587

--sysdepends 테이블에서 조회된 id로 어떤 DB개체인지 알아보자.
SELECT name, id FROM sysobjects WHERE id = '2121058592'

 

 

 

 

=================================

=================================

=================================