|
1. 저장 프로시저
일반적으로 클라이언트 서버 환경의 데이터 베이스를 조작하는 경우, 클라이언트의 컴퓨터에서 입력된 SQL
명령을 네트워크를 통해 데이터 베이스를 관리하고 있는 RDBMS 서버로 전송합니다. 그러나 데이터 베이스
의 조작을 중심으로 하는 로직의 경우, 매번 클라이언트로부터 SQL 명령을 전송받지 않고도 복수의 SQL문
집합을 하나의 프로그램처럼 데이터베이스에 등록할 수 있습니다. 이렇게 함으로써 속도개선과 편리성향상
이라는 두가지 이득을 얻을수 있는데 이러한 SQL문들을 데이터베이스에 저장한 프로그램이라는 의미에서
저장 프로시져라고 부릅니다.
2. 저장 프로시저의 장점
1) 데이터베이스 내에서 SQL명령을 컴파일할 때 캐시를 이용할 수 있으므로 처리가 매우 빠르다.
2) 반복적으로 SQL을 실행하는 경우, 매회 명령마다 네트워크를 경유할 필요가 없으므로 속도가 빨라진다.
3) 어플리케이션마다 복수의 SQL문을 기술할 필요없이 이미 만들어진 저장 프로시저를 반복 사용하여 다른
어플리케이션에서 재사용 할 수 있다.
4) 데이터베이스를 조작하는 로직을 수정하는 경우, 저장 프로시저는 서버측에 있으므로 어플리케이션을
수정하여 컴파일 할 필요가 없다.
3.저장 프로시저의 기본적인 작성방법 | ||
|
형식) CREATE PROC [프로시저명] AS [SQL구문] | ||
|
|
그러면 titles 테이블을 대상으로 price를 2배로 만드는 간단한 저장 프로시저를 작성해봅시다. -> create proc pupdateprice as update titles set price=price*2 | |
 <그림 1> | ||
|
우선 pupdateprice라는 저장 프로시저를 실행하기 전에 먼저 기존의 titles 테이블의 price 데이터를 확인해봅시다. -> select top 3 title, price from titles | ||
 <그림 2> | ||
| 이제 pupdateprice 를 실행하고 그 결과를 살펴봅시다. -> EXEC pupdateprice select top 3 title, price from titles | ||
 <그림 3> | ||
|
데이터를 살펴 보면 price가 모두 기존의 데이터의 2배가 되었음을 확인할 수 있습니다. | ||
|
|
4.인수를 가지는 저장 프로시저 | |
|
형식) create proc [프로시저명] 변수 선언부 as [SQL구문] 위와 같이 Create Proc 구문 아래에 저장 프로시저에서 사용할 인수를 선언한다. -> | ||
 <그림 4> | ||
|
위에 보면 Mul변수앞에 @가 붙어있음을 볼 수 있습니다. @가 붙으면 변수라는 뜻입니다.
그럼 이제 인수값을 넣어서 pupdateprice2를 실행해 보겠습니다. 실행시에 인수값을 넣어주는 방법은 아래와 같습니다. -> | ||
 <그림 5> | ||
|
위 그림에서 pupdateprice2가 실행되어서 titles 테이블의 price가 다시 절반으로 줄어든 모습을 볼 수 있습니다.
위와 같이 디폴트값을 사용하거나 혹은 인수를 순서대로 넘기지 않을 경우에는 아래의 예처럼 인수값을 일일이 지정해 주면 됩니다. exec pupdateprice2 @I=2, @j=3 그러면 여러 개의 인수를 가진 프로시저를 직접 만들어 봅시다. -> | ||
 <그림 6> | ||
|
pupdateprice3 는 @a,@b,@c 세개의 인수를 가진 저장 프로시저입니다. @a에는 값을 지정하지 않았을 때 디폴트 값이 2를 갖도록 했습니다. 나머지 @b와 @c는 디폴트 값이 없으므로 꼭 인수값을 넣어야 합니다.
-> select top 3 title, price from titles | ||
 <그림 7> | ||
|
저장 프로시저 확인 방법 형식) sp_helptext [저장 프로시저] -> sp_helptext pupdateprice | ||
 <그림 8> | ||
|
5. |
반환값을 가지는 저장 프로시저 |
|
저장 프로시저는 값이 리턴 된다고 해서 저장함수라고도 합니다. 이러한 저장 프로시저는 SQL명령 내에 MAX, SUM이나 Count 함수와 같이 사용할 수 있습니다.
형식) create proc 함수명 인수1 데이터형1, 인수2 데이터형2 [output] as {SQL 구문} return 리턴값 위와 같이 저장 프로시저가 리턴값을 선언할 때 [OUTPUT]이란 낱말을 뒤에 붙이면 그 인수의 값을 리턴할 수 있습니다. OUTPUT은 여러 인수에 복수로 설정할 수도 있다. 리턴값을 돌려주는 저장 프로시저의 예를 한가지 살펴보겠습니다. GETDATE라고 하는 현재 날짜와 시간을 표시하는 함수가 있는데 GETDATE 함수가 돌려주는 현재시간에서 날짜 부분만을 표시하는 pTODAY 라는 저장함수를 작성해보겠습니다. -> | |
 <그림 9> | |
|
pTODAY 저장 프로시저는 @today를 값을 반환하는 변수로 지정하였습니다. Select 문에서는 오늘의 날짜를 구하기 위해 datepart 날짜 함수와 convert 변환 함수를 사용하였습니다.
그러면 pTODAY 저장 프로시저를 실행해 보겠습니다. pTODAY를 실행시키기 전에 프로시저의 리턴값을 받아 올 @answer 변수를 먼저 선언하고 아래 처럼 저장 프로시저를 실행하는 명령에서 인수에 반환값이라는 표시로 OUTPUT을 지정해야 합니다. | |
 <그림 10> | |
|
@answer 매개변수가 pTODAY 저장 프로시저의 리턴값을 받아 오고 받아온 @answer 값을 select문으로 화면에 표시했습니다. Print 문을 사용하여 print @answer를 해도 되지만 Print 문을 사용하게 되면 위의 결과 화면처럼 컬럼명 ‘오늘날짜’ 를 붙이지 못합니다. | |
|
6. |
저장 프로시저의 삭제 |
|
형식) drop procedure 프로시저명,… -> drop procedure pupdateprice, pupdateprice2 | |
|
| |
|
7. |
엔터프라이즈 관리자를 이용한 저장 프로시저의 작성 |
|
엔터프라이즈 관리자를 이용해서 저장프로시저를 만드는 방법에 대해서 살펴보자. | |
 <그림 12> | |
|
[pubs]데이터베이스의 저장프로시저 화면
위 그림은 엔터프라이즈 관리자에서 pubs 데이터베이스의 저장 프로시저를 마우스 클릭했을 때 나타나는 저장 프로시저 목록입니다. 우리가 만들었던 pTODAY에 마우스를 위치시키고 마우스 오른쪽 버튼을 클릭하면 아래와 같은 팝업메뉴가 나타납니다. | |
 <그림 13> | |
|
위 그림의 팝업메뉴를 보면 복사, 삭제, 이름 바꾸기 등의 작업을 할 수 있는 것을 볼 수 있습니다. 팝업 메뉴에서 등록정보를 선택하면 저장 프로시저의 소스코드 화면이 다음과 같이 나타나고 이 화면에서 저장 프로시저의 소스 코드를 수정할 수도 있습니다. | |
 <그림 14> | |
|
엔터프라이즈 관리자를 이용하여 새로운 저장 프로시저를 만들어 봅시다. 팝업메뉴에서 맨 위에 있는 새 저장 프로시저를 선택하면 아래와 같은 화면이 나옵니다. | |
 <그림 15> | |
|
위 그림의 텍스트 부분에 SQL쿼리 분석기에서 했던 것처럼 저장 프로시저를 만드는 SQL문을 입력한 후에 확인버튼을 누르면 됩니다. |
|
