글
Technical Article/펌 2004. 4. 5. 20:36SP(Stored Procedure)를 사용해보자!
USE pubs
GO
--프로시져 생성구문
CREATE PROC up_konan1
AS
SELECT * FROM titles
GO
--프로시져 수행 구문
EXEC up_konan1
[일반 쿼리와 장단점 비교]
일반 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. 캐시에 실행 계획이 없을 경우 - 처음 실행 과정을 반복 합니다.
저장 프로시져 생성 문법
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
일반적인 프로시져의 생성
USE pubs
GO
--프로시져 생성구문
CREATE PROC up_konan2
AS
SELECT pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값
FROM titles
GROUP BY pub_id, type, royalty, ytd_sales
WITH CUBE
GO
--프로시져 수행 구문
EXEC up_konan2
[매개변수를 사용한 저장 프로시져]
--프로시져 생성
CREATE PROC up_konan_test7
@v_price int
AS
SELECT * FROM titles WHERE price > @v_price
--프로시져 수행
EXEC up_konan_test7 30
EXEC up_konan_test7 50
--테스트 테이블 생성
CREATE TABLE konan_test9(
c1 int
, c2 varchar(10)
)
--프로시져 생성
CREATE PROC up_konan_test9
@v_c1 int
, @v_c2 varchar(10)
AS
INSERT INTO konan_test9(c1, c2) VALUES(@v_c1, @v_c2)
--프로시져 수행
EXEC up_konan_test9 1, 'Hi~'
EXEC up_konan_test9 2, '테스트'
--데이터 조회
SELECT * FROM konan_test9
--프로시져 생성
CREATE PROC up_konan_test10
@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 up_konan_test10 'titles', 'BU1032'
EXEC up_konan_test10 'titles', 'BU1111'
EXEC up_konan_test10 'titleauthor', 'BU1111'
EXEC up_konan_test10 'titleauthor', 'BU1032'
자 뭔가 갑자기 어려워 진듯 하시지용? ^_^
자 제가 이번에 보여드린 프로시져만 이해 하시면 프로시져의 80%는 이해 하신 겁니다.
천천히 설명을 드리지요.
CREATE PROC up_konan_test10
@v_tblname varchar(20)
, @v_title_id varchar(20)
자 저는 매개변수를 두개 받았습니다.
이름에서 대강 느끼시겠지만.. 테이블을 받을 변수 @v_tblname,
title_id를 받을 변수 @v_title_id 를 두었습니다. ^_^
--저장할 변수 선언
DECLARE @v_strSQL VARCHAR(200)
--변수 @v_strSQL에 생성된 문자열 저장
SET @v_strSQL = 'SELECT * FROM ' + @v_tblname +
' WHERE title_id = ''' + @v_title_id + ''''
프로시져 내부에서 사용할.. 문자열 연결로 SQL구문을 생성할 변수인
@v_strSQL 변수를 생성 했습니다. 아울러 VARCHAR(200) 이라는 조금 긴 문자열도
저장이 가능하게 생성 했습니다.
이어서 SET 구문을 이용해 @v_strSQL 변수에 문자열을 구성합니다.
작은따옴표 의 갯수가 상당히 중요 합니다.
작은 따옴표 하나를 문자열 내에서(문자열은 작은 따옴표로 묶이지요) 표현 하려면?
작은 따옴표 두개를 사용해야 합니다.
아울러 쿼리의 띄어쓰기도 주의 하셔야 합니다. FROM 절의 앞과 뒤에서 공백을 하나씩
주어서 문제없이 SQL구문이 생성되도록 주의해 주셔야 합니다.
끝으로...
EXEC(@v_strSQL)
--SELECT @v_strSQL
두줄 입니다.
해당하는 쿼리를 디버깅 하는 것은 상당히 귀찮은 작업 입니다.
--EXEC(@v_strSQL)
SELECT @v_strSQL 처음 수행시는 이처럼..
수행을 하는 구문인 EXEC 구문대신에.. 아래처럼 SQL구문이 SELECT되게 하는
프로시져를 생성 합니다.
그러면 위에서 약간 복잡한 해당하는 SQL구문이 구성된 녀석이 프로시져 수행시
리턴 될겁니다. 그런후 결과창의 구성된 SQL구문을 질의 분석기(쿼리 어낼라이져)로
옮기신후 여기서 수행해 보신후 잘 생성이 되었나 안되었나를 알아 보신후
테스트 해 보시면 되겠지요. 그런후 ALTER PROCEDURE 구문으로
해당 프로시져를
EXEC(@v_strSQL)
--SELECT @v_strSQL
이러한 식으로 EXEC - 수행되게 변경을 하신후.. 프로시져를 수행 하시면?
결과가 잘 나오는 것을 보실 겁니다. ^_^
그렇다면!!! 왜 저런 복잡한듯한... 문자열 생성 방식을 사용해야 하는 것인가!!!
간단합니다. 테이블명과 같은 객체형 값(Object Value)는 프로시져의 매개변수로
사용이 불가 합니다.
--프로시져 생성 - 수행 안됨
CREATE PROC up_konan_test11
@v_tblname varchar(20)
AS
SELECT * FROM @v_tblname
--일반 비교값으로 생성하는 프로시져
CREATE PROC up_konan_test11
@v_title_id varchar(20)
AS
SELECT * FROM titles WHERE title_id = @v_title_id
--프로시져 수행
EXEC up_konan_test11 'BU1032'
다음으로 TOP 구문을 이용할 경우 입니다.
SELECT TOP 10 * FROM titles 이런 식으로 사용을 하시지요?
이때 10 과 같은 TOP의 값을 수행하고 싶을 경우 입니다.
--프로시져 생성 - 수행 안됨
CREATE PROC up_konan_test12
@v_topN int
AS
SELECT top @v_topN * FROM titles
--프로시져 생성 - 프로시져 생성은 가능. 수행은 불가
CREATE PROC up_konan_test12
@v_topN int
AS
DECLARE @v_strSQL VARCHAR(200)
SET @v_strSQL = 'SELECT top ' + @v_topN + ' * FROM titles'
EXEC(@v_strSQL)
--프로시져 수행. 수행 불가
EXEC up_konan_test12 '10'
--프로시져 생성
CREATE PROC up_konan_test13
@v_topN varchar(5) --여기가 틀림
AS
DECLARE @v_strSQL VARCHAR(200)
SET @v_strSQL = 'SELECT top ' + @v_topN + ' * FROM titles'
EXEC(@v_strSQL)
--프로시져 수행
EXEC up_konan_test13 '10'
GO
--프로시져 생성구문
CREATE PROC up_konan1
AS
SELECT * FROM titles
GO
--프로시져 수행 구문
EXEC up_konan1
[일반 쿼리와 장단점 비교]
일반 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. 캐시에 실행 계획이 없을 경우 - 처음 실행 과정을 반복 합니다.
저장 프로시져 생성 문법
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
일반적인 프로시져의 생성
USE pubs
GO
--프로시져 생성구문
CREATE PROC up_konan2
AS
SELECT pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값
FROM titles
GROUP BY pub_id, type, royalty, ytd_sales
WITH CUBE
GO
--프로시져 수행 구문
EXEC up_konan2
[매개변수를 사용한 저장 프로시져]
--프로시져 생성
CREATE PROC up_konan_test7
@v_price int
AS
SELECT * FROM titles WHERE price > @v_price
--프로시져 수행
EXEC up_konan_test7 30
EXEC up_konan_test7 50
--테스트 테이블 생성
CREATE TABLE konan_test9(
c1 int
, c2 varchar(10)
)
--프로시져 생성
CREATE PROC up_konan_test9
@v_c1 int
, @v_c2 varchar(10)
AS
INSERT INTO konan_test9(c1, c2) VALUES(@v_c1, @v_c2)
--프로시져 수행
EXEC up_konan_test9 1, 'Hi~'
EXEC up_konan_test9 2, '테스트'
--데이터 조회
SELECT * FROM konan_test9
--프로시져 생성
CREATE PROC up_konan_test10
@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 up_konan_test10 'titles', 'BU1032'
EXEC up_konan_test10 'titles', 'BU1111'
EXEC up_konan_test10 'titleauthor', 'BU1111'
EXEC up_konan_test10 'titleauthor', 'BU1032'
자 뭔가 갑자기 어려워 진듯 하시지용? ^_^
자 제가 이번에 보여드린 프로시져만 이해 하시면 프로시져의 80%는 이해 하신 겁니다.
천천히 설명을 드리지요.
CREATE PROC up_konan_test10
@v_tblname varchar(20)
, @v_title_id varchar(20)
자 저는 매개변수를 두개 받았습니다.
이름에서 대강 느끼시겠지만.. 테이블을 받을 변수 @v_tblname,
title_id를 받을 변수 @v_title_id 를 두었습니다. ^_^
--저장할 변수 선언
DECLARE @v_strSQL VARCHAR(200)
--변수 @v_strSQL에 생성된 문자열 저장
SET @v_strSQL = 'SELECT * FROM ' + @v_tblname +
' WHERE title_id = ''' + @v_title_id + ''''
프로시져 내부에서 사용할.. 문자열 연결로 SQL구문을 생성할 변수인
@v_strSQL 변수를 생성 했습니다. 아울러 VARCHAR(200) 이라는 조금 긴 문자열도
저장이 가능하게 생성 했습니다.
이어서 SET 구문을 이용해 @v_strSQL 변수에 문자열을 구성합니다.
작은따옴표 의 갯수가 상당히 중요 합니다.
작은 따옴표 하나를 문자열 내에서(문자열은 작은 따옴표로 묶이지요) 표현 하려면?
작은 따옴표 두개를 사용해야 합니다.
아울러 쿼리의 띄어쓰기도 주의 하셔야 합니다. FROM 절의 앞과 뒤에서 공백을 하나씩
주어서 문제없이 SQL구문이 생성되도록 주의해 주셔야 합니다.
끝으로...
EXEC(@v_strSQL)
--SELECT @v_strSQL
두줄 입니다.
해당하는 쿼리를 디버깅 하는 것은 상당히 귀찮은 작업 입니다.
--EXEC(@v_strSQL)
SELECT @v_strSQL 처음 수행시는 이처럼..
수행을 하는 구문인 EXEC 구문대신에.. 아래처럼 SQL구문이 SELECT되게 하는
프로시져를 생성 합니다.
그러면 위에서 약간 복잡한 해당하는 SQL구문이 구성된 녀석이 프로시져 수행시
리턴 될겁니다. 그런후 결과창의 구성된 SQL구문을 질의 분석기(쿼리 어낼라이져)로
옮기신후 여기서 수행해 보신후 잘 생성이 되었나 안되었나를 알아 보신후
테스트 해 보시면 되겠지요. 그런후 ALTER PROCEDURE 구문으로
해당 프로시져를
EXEC(@v_strSQL)
--SELECT @v_strSQL
이러한 식으로 EXEC - 수행되게 변경을 하신후.. 프로시져를 수행 하시면?
결과가 잘 나오는 것을 보실 겁니다. ^_^
그렇다면!!! 왜 저런 복잡한듯한... 문자열 생성 방식을 사용해야 하는 것인가!!!
간단합니다. 테이블명과 같은 객체형 값(Object Value)는 프로시져의 매개변수로
사용이 불가 합니다.
--프로시져 생성 - 수행 안됨
CREATE PROC up_konan_test11
@v_tblname varchar(20)
AS
SELECT * FROM @v_tblname
--일반 비교값으로 생성하는 프로시져
CREATE PROC up_konan_test11
@v_title_id varchar(20)
AS
SELECT * FROM titles WHERE title_id = @v_title_id
--프로시져 수행
EXEC up_konan_test11 'BU1032'
다음으로 TOP 구문을 이용할 경우 입니다.
SELECT TOP 10 * FROM titles 이런 식으로 사용을 하시지요?
이때 10 과 같은 TOP의 값을 수행하고 싶을 경우 입니다.
--프로시져 생성 - 수행 안됨
CREATE PROC up_konan_test12
@v_topN int
AS
SELECT top @v_topN * FROM titles
--프로시져 생성 - 프로시져 생성은 가능. 수행은 불가
CREATE PROC up_konan_test12
@v_topN int
AS
DECLARE @v_strSQL VARCHAR(200)
SET @v_strSQL = 'SELECT top ' + @v_topN + ' * FROM titles'
EXEC(@v_strSQL)
--프로시져 수행. 수행 불가
EXEC up_konan_test12 '10'
--프로시져 생성
CREATE PROC up_konan_test13
@v_topN varchar(5) --여기가 틀림
AS
DECLARE @v_strSQL VARCHAR(200)
SET @v_strSQL = 'SELECT top ' + @v_topN + ' * FROM titles'
EXEC(@v_strSQL)
--프로시져 수행
EXEC up_konan_test13 '10'
RECENT COMMENT