[MS-SQL2011강좌] 18. 저장 프로시저에 대한 이해, 생성, 수정, 삭제 by 강동운님

이 내용은 2011년 6월 현재 SQL2011(코드명 Denali) Beta를 기준으로 작성 되었으며 SQL2011(코드명 Denali) 공식버전(RTM) 발표까지 꾸준히 업데이트 예정입니다.
아울러, 현재 Beta버전이지만 강좌를 따라해 보시는데 아무 문제 없으실거에요. 도움 되시길 바랍니다.
==================================================================================================

안녕하세요. 이스트럭(강동운) 입니다.

 

오늘은 저장 프로시저에 대해서 알아볼 차례 인데요~!

 

원문: http://www.sqler.com/127313

원문: http://www.sqler.com/127315

 

이번에 소개해 드릴 내용은 저장 프로시져의 사용입니다.

사실 저장 프로시져는 매우~!!!  아주 자주!!!!  그리고 조금 잘 구축된 시스템이라면?

일반 쿼리(Ad - Hoc Query)는 사용하지 않고 전부 이 저장 프로시져로 생성합니다.

"왜??  여태까지 일반 쿼리를 열시미 배우고 간신히 조금 쓸만한데 왜!!!!  저장 프로시져를

또 만들어 쓰는 겁니까아~~~~"  

대부분 저장 프로시져를 처음 접하시는 분들께 듣는 불만 사항입니다.

이에 대한 답변은 간단합니다. - 

　

 

저장 프로시져의 장점 1. 매우 좋은 성능 2. 보안성을 높일 수 있음. 3. 다양한 처리가 가능 4. 넷웍의 부하를 줄일 수 있음.

 

언뜻 보아서는 무쟈게 좋아 보임다..

　

아울러 코난이가 생각하는 단점으로는

 

 

저장 프로시져의 단점 1. 접하기가 어려움 2. VB나 ASP에서 호출 방식이 틀려 처음 저장 프로시져를 접할경우 거부감이 생김 3. 새로운 언어를 배우는 정도의 부담이 있음.

 

이라고 생각 한답니다. – 저 역시 처음 배울 때 저런 생각 이었습니다. - 전부 마음가짐에

대한 단점 이지요...

사실 부끄럽지만.. SQL7 기본강좌의 저장 프로시져 부분을 봐 오신 분이라면..

얼마나 부실한지 아실 겁니다.. 사실 저역시 잘 몰랐습니다. 하지만 실제 업무를 접하시면

전부 저장 프로시져 입니다. 반드시 넘어야 할 녀석이니 우선 마음 푹 놓으시고

샘플을 많이 봐 보세요. 

코난이도 매일 저거랑 싸웁니다. SQLER 님덜 조금만 힘내서 보십시다요~~

　

저장프로시져란?

보통 프로시져, 저장 프로시져, SP 라고도 불립니다. 영어로는 Stored Procedure라고 함당.

코난이는 늘 그랬던 것처럼 혼용해서 쓸 겁니다. -_-;;

뷰를 보셨지요..

뷰는 특정 SELECT 구문등을 단순화가 가능합니다. - 하지만 뷰는 성능에 영향을 주지는

못합니다. 프로시져는 성능에 영향을 많이 줍니다. 

우선 샘플을 봐 봅시다.

 

USE pubs GO IF OBJECT_ID('up_konan1','P') IS NOT NULL         DROP PROC up_konan1 GO --프로시저 생성 구문 CREATE PROC dbo.up_konan1 AS SELECT * FROM titles GO   --프로시저 수행 구문(프로시저 실행 시에는 반드시 스키마 명을 붙여주세요) EXEC dbo.up_konan1

 

 

AS 라는 프로시져 생성 구문을 보니 뷰랑 비스 하지요? 

PROC 라는 말만 틀리지 거의 같고 아울러 눈에 많이 익은 SELECT ~~ 로 시작 됩니다.

일반 SQL구문인 SELECT, ~~

등등의 구문으로 이루어지는데 어떻게 더 빠를 수 있나요?

자 이제 프로시져의 비밀을 풀어 봅시다.

　

 

일반 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. 캐시에 실행 계획이 없을 경우 - 처음 실행 과정을 반복 합니다.

 

 

자 어떠신지요? 프로시져 수행과 일반 쿼리 수행시 별로 다른점은 없는 듯 하지요?

하지만 해당 작업중 SQL수행시의 과정은 오로지 캐싱에만 의존해야 합니다.

따라서 이는 실행 계획의 재사용으로 인해 파라미터 스니핑이라는 문제가 발생할 수 있습니다.

이는 SQL Server 가 CBO(Cost Based Optimazation) 기반 설계라 이런 문제가 발생합니다.

 

파라미터 스니핑은 조금 만 검색하셔도 많은 자료를 얻으실 수 있을 것이므로 여기서는 자세히 설명해드리지는 않겠습니다.

 

 

이제 저장 프로시저를 생성하는 방법에 대해서 알아보도록 하겠습니다.

 

CREATE PROC [ EDURE ] procedure_name [ ; number ]      [ { @parameter data_type }          [ VARYING ] [ = default ] [ OUTPUT ]      ] [ ,...n ] [ WITH      { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]

CREATE PROCEDURE(Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187926(v=SQL.110).aspx

 

 

저장 프로시져는 위와 같은 어려워 보이는 방식으로 생성합니다.

하지만 코난이도 몇가지 방식 사용 안합니다. 관심 있으시거나 방바닥의 머리칼 하나

가만 두지 못하시는 분들은 온라인 도움말(북스 온라인)을 참고 하세요.. 

　

일반적인 프로시져의 생성

 

 

USE pubs GO   --프로시져 생성구문 CREATE PROC dbo.up_konan2 AS SELECT pub_id 저자 , type 책종류, royalty 로열티 , ytd_sales 팔린수, AVG(price) 평균값 FROM dbo.titles GROUP BY pub_id, type, royalty, ytd_sales WITH CUBE GO   --프로시져 수행 구문 EXEC dbo.up_konan2

 

 

프로시져 수정

대부분의 수정 구문처럼 ALTER 구문을 사용 합니다.

간단히 약간 수정을 가해 보지요.

 

 

 

--프로시저 수정 구문 ALTER PROC dbo.up_konan2 AS SELECT pub_id FROM dbo.titles GO   --프로시져 수행 구만 EXEC dbo.up_konan2

 

 

약간이 아니라 많이 틀린가용 ^_^;;

ALTER 명령의 장점 간단히 언급해 드렸지용?

DROP을 할 경우 여러 묶여 있는(sysobjects 테이블의 id로 묶여 있는) 여러 개체들과

비호환의 문제 , 또는 유저에 대한 권한 문제가 있을 수 있으므로..

가능한 저렇게 ALTER 구문을 쓰시면 좋습니다.

물론 CREATE -> ALTER로 바낀것 뿐이니 어렵지 않으실 겁니당 ^_^

 

프로시져 삭제

저장 프로시져의 삭제 역시 만만치 않게 쉽습니다.

DROP이라는 구문 기억 나시죠?

 

--프로시저 삭제 DROP Proc dbo.up_konan2

 

 

어떠세요? 간단하지요. ^_^

　

프로시져 생성 구문 조회

저장 프로시져를 생성후 생성 구문을 추후에 조회하실 수 있습니다.

 

 

if OBJECT_ID('up_konan3','P') IS NOT NULL         DROP PROC up_konan3 GO --프로시져 생성 구문 CREATE PROC dbo.up_konan3 AS SELECT pub_id, title FROM dbo.titles GO   --프로시저 수행구문 EXEC dbo.up_konan3 go   --생성된 프로시저의 내용 조회 sp_helptext up_konan3 GO

 

간단히 보실 수 있지용? ^_^

컨트롤 + T 키를 누르신후 결과를 Text모드로 보시면 더 잘 보입니다. ^_^

프로시져의 장점으로 보안성을 높일 수 있다고 말씀 드렸지요?

하지만 저렇게 프로시져 생성 구문을 아무나 볼 수 있다면 문제지용? ^_^

프로시져 생성 구문을 암호화 시켜 보지요. ^_^

　

저장 프로시져 생성구문 암호화

자 어떠신지용 - 암호화 되어서 보실 수 없을 겁니다. ^_^  어렵지 않으시죵?

　

저장 프로시져의 종류

저장 프로시져의 종류는 3가지 라고 보시면 됩니다.

1. 사용자 정의 저장 프로시져 - 여지껏 저희가 생성하고 수행해본 것을 말하지용 ^_^

2. 시스템 저장 프로시져 - sp_help, sp_helptext 와 같이 sp_로 시작하는 프로시져.

3. 확장 시스템 저장 프로시져 - xp_ 로 시작하는 프로시져를 의미 합니다.

예를 들면..

 

EXEC master..xp_cmdshell 'dir'

와 같은 시스템 저장 프로시져를 의미 합니다. ^_^

 

이 정도면 어느 정도 생성 방법은 설명을 드린 듯 하네요.

또한 프로시저를 생성 시에는 반드시 sp_로 만들지 마시길 바랍니다. SQL Server 내부적으로 프로시저 수행시 sp로 시작하는 단어면 우선 시스템 프로시저인지 검사를 하게 되는데.. 이는 불필요한 작업이 될 수 있습니다. 대부분의 많은 사람들은 user sp라고 해서.. usp_ 라고 많이들 사용하십니다. 참고가 되셨으면 좋겠네요 ^^;

[출처] SQLER.COM http://www.sqler.com/392656


SQL2011(코드명 Denali) 시리즈 강좌 리스트

[SQL2011강좌] 1. 코드명 Denali 설치 방법
[SQL2011강좌] 2. SSMS 접속 및 간단한 쿼리 실행
[SQL2011강좌] 3. SQL Server 의 MDF, LDF 그리고 데이터베이스 생성하기
[SQL2011강좌] 4. DDL 1탄: 테이블 생성
[SQL2011강좌] 5. DDL 2탄: 쿼리를 이용한 테이블 컬럼 추가 및 삭제
[SQL2011강좌] 6. Primary key와 Unique 제약조건
[SQL2011강좌] 7. DML 1탄 INSERT
[SQL2011강좌] 8. DML 1탄 INSERT(identity 속성)
[SQL2011강좌] 9. Sequence
[SQL2011강좌] 10. pubs, northwind Database 예제 설치
[SQL2011강좌] 11. SELECT(단일 테이블)
[SQL2011강좌] 12. SELECT(JOIN)
[SQL2011강좌] 13. UPDATE, DELETE 절
[SQL2011강좌] 14. DISTINCT, UNION, UNION ALL 키워드
[SQL2011강좌] 15. ORDER BY, GROUP BY, HAVING
[SQL2011강좌] 16. SubQuery(서브쿼리)
[SQL2011강좌] 17. 뷰에 대한 이해 및 생성, 수정, 삭제
[SQL2011강좌] 18. 저장 프로시저에 대한 이해, 생성, 수정, 삭제
[SQL2011강좌] 19. 인덱스에 대한 이해
[SQL2011강좌] 20. 클러스터드 인덱스와 넌 클러스터드 인덱스
[SQL2011강좌] 21. 트랜잭션에 대한이해
[SQL2011강좌] 22. 트랜잭션의 격리수준 4가지
[SQL2011강좌] 23. 잠금에 대한 이해
[SQL2011강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업)
[SQL2011강좌] 25. 백업과 복구 전략
[SQL2011강좌] 26. 커서란?

SQL2011(코드명 Denali) 시리즈 동영상 강좌 리스트
[SQL2011 동영상 강좌] 1. 코드명 Denali 설치 방법
[SQL2011 동영상 강좌] 2. SSMS 접속 및 간단한 쿼리 실행
[SQL2011 동영상 강좌] 3. SQL Server 의 MDF, LDF 그리고 데이터베이스 생성하기
[SQL2011 동영상 강좌] 4. DDL 1탄: 테이블 생성
[SQL2011 동영상 강좌] 5. DDL 2탄: 쿼리를 이용한 테이블 컬럼 추가 및 삭제
[SQL2011 동영상 강좌] 6. Primary key와 Unique 제약조건
[SQL2011 동영상 강좌] 7. DML 1탄 INSERT
[SQL2011 동영상 강좌] 8. DML 1탄 INSERT(identity 속성)
[SQL2011 동영상 강좌] 9. Sequence
[SQL2011 동영상 강좌] 10. pubs, northwind Database 예제 설치
[SQL2011 동영상 강좌] 11. SELECT(단일 테이블)
[SQL2011 동영상 강좌] 12. SELECT(JOIN)
[SQL2011 동영상 강좌] 13. UPDATE, DELETE 절
[SQL2011 동영상 강좌] 14. DISTINCT, UNION, UNION ALL 키워드
[SQL2011 동영상 강좌] 15. ORDER BY, GROUP BY, HAVING
[SQL2011 동영상 강좌] 16. SubQuery(서브쿼리)
[SQL2011 동영상 강좌] 17. 뷰에 대한 이해 및 생성, 수정, 삭제
[SQL2011 동영상 강좌] 18. 저장 프로시저에 대한 이해, 생성, 수정, 삭제
[SQL2011 동영상 강좌] 19. 인덱스에 대한 이해
[SQL2011 동영상 강좌] 20. 클러스터드 인덱스와 넌 클러스터드 인덱스
[SQL2011 동영상 강좌] 21. 트랜잭션에 대한이해
[SQL2011 동영상 강좌] 22. 트랜잭션의 격리수준 4가지
[SQL2011 동영상 강좌] 23. 잠금에 대한 이해
[SQL2011 동영상 강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업)
[SQL2011 동영상 강좌] 25. 백업과 복구 전략
[SQL2011 동영상 강좌] 26. 커서란?