티스토리 뷰
제 1 장 Stored Procedure 사용환경 조성
Stored Procedure(저장프로시저)를 사용하기 앞서서 본 문서에서는 Visual Studio 2010환경에서 서버탐색기를 통하여 데이터베이스 연동함을 알립니다.
1. 위의 그림[1-1]과 같이 보기창에서 다른창으로 간 뒤 서버 탐색기를 켜줍니다.
2. 서버 탐색기를 켰으면 SharePoint연결, 데이터베이스, 서버 항목이 보일건데 데이터베이스부분에서 우클릭을 합니다.
3. 우클릭을 하면 그림[1-2]처럼 새 SQL Server 데이터베이스 만들기 창이 뜰텐데
서버 이름 항목을 클릭하고 난 뒤에 서버를 골라줍니다. 그리고 Windows사용자 계정으로 로그인할건지 SQL Server로 로그인할 건지 골라준 뒤에 데이터베이스 이름을 정해줍니다.
서버를 고르기 전에, SQL Server가 깔려있지 않다면 먼저 설치 후에 사용환경을 조성하시길 바랍니다.
물론, 본 문서에서는 SQL Server 설치 및 설치방법에 대해서는 논하지 않습니다.
4.여기까지 다 하셨으면 저장프로시저의 사용환경의 조성이 완료되었습니다.
만약에 SQL Server까지 설치했는데 서버 이름 목록에서 해당 서버가 나타나지 않는다면 SQL Browser가 off상태인지 확인하고 꺼져있다면 on상태로 바꾼 뒤 해보시길 바랍니다. SQL Browser는 SQL Server 구성관리자에서 확인할 수 있습니다.
그림 [1-3]참고
[그림 1-3]
제 2 장 Stored Procedure란?
Stored Procedure(저장프로시저)는 DB내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다.
저장 프로시저는 다음과 같은 장점이 있습니다.
1. 하나의 요청으로 여러 SQL문을 실행 할 수 있습니다. (네트워크에 대한 부하를 줄일 수 있습니다.)
2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어듭니다.
3. 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조무결성 유지가 가능합니다. 간단히 말하면 응용 프로그램 측 로직을 가지지 않고도 데이터베이스의 데이터 앞뒤가 맞게 할 수 있습니다.
4. JAVA 등의 호스트 언어와 SQL 문장이 확실하게 분리된 소스 코드의 전망이 좋아지는 것, 또한 웹사이트 등 운용 중에도 저장프로시저의 교체에 의한 수정이 가능하기 때문에 보수성이 뛰어납니다.
저장프로시저를 많이 사용하면 다음과 같은 단점이 있습니다.
1. 데이터베이스 제품에 대해 설명하는 구문 규칙이 SQL / PSM 표준과의 호환성이 낮기 때문에 코드 자산으로의 재사용성이 나쁩니다.
2. 비즈니스 로직의 일부로 사용하는 경우 업무의 사양 변경 시 외부 응용 프로그램과 함께 저장프로시저의 정의를 변경할 필요가 있습니다. 이때 불필요한 수고와 변경 실수에 의한 장애를 발생시킬 가능성이 있습니다
제 1 절 Stored Procedure 사용 예
다음은 저장프로시저의 기본 형식입니다.
CREATE PROCEDURE dbo.StoredProcedure1 /*프로시저 생성 [스키마이름].[프로시저이름]*/
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT /*값을 리턴해주는 OUTPUT*/
)
AS
/* 저장 프로시저 내 실행될 SQL문 */
RETURN
저장 프로시저는 create procedure 쿼리문을 통해 만들 수 있으며,
@가 붙어있는 parameter1과 parameter2는 매개변수입니다.
매개변수 이름 뒤에 타입을 지정해줍니다.
Output 키워드를 쓰면 값을 리턴해서 수행결과를 알 수 있습니다.
프로시저 설명을 위한 테이블을 하나 만들겠습니다.
CREATE table dbo.Product/*테이블 생성 [스키마이름].[테이블 이름]*/
(
[NAME] [varchar](200) not null, /*[이름] 문자열 200글자 공백 X*/
[PRICE] [int] not null /*[가격] 정수형 공백X*/
)
상품 이름과 가격을 추가하는 저장프로시저를 작성해보겠습니다.
CREATE PROCEDURE dbo.AddProduct
(
@NAME varchar(50),
@PRICE int
)
AS
insert into Product (NAME,PRICE) values(@NAME, @PRICE)
RETURN
위의 프로시저에서는 varchar(50)타입의 NAME 매개변수와 int 타입의 PRICE매개변수를 명시하였고, Product테이블의 NAME열에는 @NAME값을 넣고, PRICE열에는 @PRICE값을 넣는다는 SQL구문입니다.
다음은 상품 이름으로 ID를 검색하는 저장프로시저를 작성해보겠습니다.
CREATE PROCEDURE dbo.FindIDByName
(
@NAME varchar(50),
@ID int output
)
AS
set @ID = -1
select ID
from Product
where NAME = @NAME
RETURN
위의 프로시저에서는 varchar(50)타입의 NAME매개변수와 int타입의 output유형의 매개변수를 명시하였습니다.
set부분은 검색한 이름이 없을 때 ID값을 -1로 지정하였습니다.
Select 부분은 필드명(열 이름)을 적어주고
from에는 테이블명을 적어줍니다.
where문에는 조건을 적어주면 해당 조건이 충족할 경우 select하는데 여기서는
Product테이블의 NAME필드가 매개변수@NAME과 일치할 때 ID를 추출하는 코드입니다.
다음은 상품 이름으로 상품을 제거하는 저장프로시저를 작성해보겠습니다.
CREATE PROCEDURE dbo.RemoveProduct
(
@NAME varchar(50)
)
AS
declare @ID int
Exec FindIDByName @NAME,@ID output
delete from Product
where ID = @ID
RETURN
상품이름으로 검색할 수 있게 매개변수로 @NAME을 하나 두었습니다.
Exec 구문은 기본형이 Exec [저장프로시저 이름][인자리스트]이고,
저장 프로시저에서 다른 저장프로시저를 호출할 때 사용합니다.
Exec 구문에 인자 리스트를 열거할 때는 출력 유형의 매개 변수는 output키워드를 명시해야 합니다. 그리고 저장 프로시저 내부에서 변수를 선언할 때는 declare구문을 이용하여 변수명과 형식을 명시합니다.
위의 코드에서는 FindIDByName 저장 프로시저를 호출하여 ID값을 비교하여 같을 경우 삭제하는 코드입니다.
제 3 장 Stored Procedure응용
이제 어느정도 기본적인 저장프로시저를 몸에 익혔으니, 좀 더 응용해서 동적으로 테이블을 만드는 저장프로시저를 작성해봅시다.
CREATE PROCEDURE dbo.CreateInvertedFile
(
@Index int
)
AS
declare @query nvarchar(200)
declare @Name varchar(50)
set @Name = CAST(@Index as varchar(50))
set @query = 'Create Table File_' + @Name + ' (Url varchar(50) not null) '
exec (@query)
RETURN
int타입으로 매개변수로 @Index를 두었습니다.
테이블을 동적으로 만들기 위한 변수를 선언합니다. 테이블 이름은 File_를 고정으로 하고 뒤에 Index(번호)가 붙게 할 것 입니다. Index번호를 문자열로 변환하는 코드가 set @Name = CAST(@Index as varchar(50))입니다. 변환 뒤에 Name 변수에 설정합니다.
테이블을 생성하는 쿼리문을 @query변수에 설정합니다.
exec (@query)로 설정한 쿼리문을 실행합니다.