저장프로시저

저장프로시저(Stoered Procedure) : 테이블처럼 데이터베이스 내부에 저장된다는 의미이다.

 

저장프로시저의 정의 형식

[구문형식]

create proc [스키마 이름]        [@첫번째 매개변수이름] [데이터형식],        [@두번째 매개변수이름] [데이터형식],          .....        AS          SELECT 문

 

[실습]

1-1. 1개의 입력 매개변수가 있는 저장프로시저를 생성하자.
create procedure usp_users1
   @username nvarchar(10)
as
   select * from usertbl where name = @username;
1-2. 저장프로시저를 실행하자.

exec usp_users1 '이천수';

userID   name   birthYear   addr mobile1 mobile2  height
------- ------ ----------- ---- ------- -------- ------
LCS      이천수     1983       인천   011     4444444     179

(1개 행 적용됨)

 

2-1. 2개의 입력변수가 있는 저장프로시저를 생성하자.

create procedure usp_users2
  @userBirth int,
  @userHeight int
as
  SELECT * FROM  userTbl
  where birthYear > @userBirth AND height > @userHeight;

2-2. 저장프로시저를 실행하자.

exec usp_users2 1980, 180;

userID   name       birthYear   addr mobile1 mobile2  height
-------- ---------- ----------- ---- ------- -------- ------
PJS      박지성        1983        서울   011     1111111  181

(1개 행 적용됨)
2-3. 직접 매개변수명을 지정하여 저장프로시저를 실행하자.

exec usp_users2 @userHeight=180, @userBirth=1980;

 

3-1. 디폴트 매개변수(매개변수를 기본값 설정)를 지정해 보자.

 create procedure usp_users3
   @userBirth INT = 1980,
   @userHeight INT = 180
AS
   SELECT * FROM userTbl
   WHERE birthYear > @userBirth AND height > @userHeight;

3-2. 저장프로시저를 실행하자.

EXEC usp_users3

userID   name       birthYear   addr mobile1 mobile2  height
-------- ---------- ----------- ---- ------- -------- ------
PJS      박지성        1983        서울   011     1111111  181

(1개 행 적용됨)

 

출력 매개변수를 설정해서 사용해 보자.

4-1.  저장프로시저를 생성한 후에 테스트로 사용할 테이블을 생성하자.

create procedure usp_users4
@txtValue NCHAR(10),
@outValue INT OUTPUT
AS
INSERT INTO testTbl VALUES(@txtValue);
SELECT @outValue = IDENT_CURRENT('testTbl');  -- usp_users4 프로시저 생성 시에 testTbl이라는 테이블은 존재하지 않았지만 저장프로시저가 생성됨(=지연된 이름 확인) 

CREATE TABLE testTbl (id INT IDENTITY, txt NCHAR(10));  -- 테이블 생성
# 지연된 이름 확인(deferred name resolution) : 저장프로시저의 특징으로 저장프로시저를 정의하는 시점에서는 해당 개체(주로 테이블)가 존재하지 않아도 관계가 없다는 의미이다. 즉, 테이블의 존재 여부는 이 프로시저의 실행 시에 확인하게 된다.

  

4-2. @myValue라는 매개변수를 선언하자.

DECLARE @myValue INT;
EXEC usp_users4 '테스트값1', @myValue OUTPUT;
PRINT '현재 입력된 ID 값 ==> ' + CAST(@myValue AS CHAR(5));

(1개 행 적용됨)
현재 입력된 ID 값 ==> 1   

 

5-1. 저장프로시저 안에 IF...ELSE 문을 사용해 보자.

CREATE PROC usp_ifElse
    @userName nvarchar(10)
AS
    DECLARE @bYear INT
    SELECT @bYear = birthYear FROM userTbl
       WHERE name = @userName;
    IF (@bYear >= 1980)
       BEGIN
          PRINT N'아직 젊군요..';
       END
     ELSE
        BEGIN
          PRINT N'나이가 지긋하네요..';
        END

5-2. 저장프로시저를 실행하자. 

EXEC  usp_ifElse '박주영';
아직 젊군요..

 

6-1. CASE문을 사용해보자.

CREATE PROC usp_case
    @userName nvarchar(10)
AS
    DECLARE @bYear INT
    DECLARE @tti NCHAR(3)
    SELECT @bYear = birthYear FROM userTbl
        WHERE name = @userName;

 

SET @tti =
     CASE
          WHEN ( @bYear%12 = 0) THEN '원숭이'
          WHEN ( @bYear%12 = 1) THEN '닭'
          WHEN ( @bYear%12 = 2) THEN '개'
          WHEN ( @bYear%12 = 3) THEN '돼지'
          WHEN ( @bYear%12 = 4) THEN '쥐'
          WHEN ( @bYear%12 = 5) THEN '소'
          WHEN ( @bYear%12 = 6) THEN '호랑이'
          WHEN ( @bYear%12 = 7) THEN '토끼'
          WHEN ( @bYear%12 = 8) THEN '용'
          WHEN ( @bYear%12 = 9) THEN '뱀'
          WHEN ( @bYear%12 = 10) THEN '말'
          ELSE '양'
     END;
PRINT @userName + '의 띠==> ' + @tti;

6-2. 저장프로시저를 실행하자.

EXEC usp_case '송종국';
송종국의 띠==> 양

 

7-1. WHILE 문을 사용해 보자.

ALTER TABLE userTbl
    ADD grade NVARCHAR(5);   -- 고객등급 열 추가

 

CREATE PROCEDURE usp_while
AS
     DECLARE userCur CURSOR FOR    -- 커서 선언
          SELECT U.userid, sum(price*amount)
          FROM buyTbl B
              RIGHT OUTER JOIN userTbl U
              ON B.userid = U.userid
          GROUP BY U.userid, U.name

# 커서는 테이블에서 여러 개의 행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식이다. 

OPEN useCur  -- 커서 열기

 

DECLARE @id NVARCHAR(10)  -- 사용자 아이디를 저장할 변수 선언
DECLARE @sum BIGINT -- 총구매액을 저장할 변수 선언
DECLARE @userGrade NCHAR(5) -- 고객등급 변수 선언

 

FETCH NEXT FROM userCur INTO @id, @sum -- 첫행 값을 대입

 

WHILE (@@FETCH_STATUS=0) -- 행이 없을 때까지 반복(즉, 모든 행 처리)
BEGIN
    SET @userGrade =
       CASE
          WHEN (@sum >= 1500) THEN N'최우수고객'
          WHEN (@sum >= 1000) THEN N'우수고객'
          WHEN (@sum >= 1) THEN N'일반고객'
          ELSE N'유령고객'
        END
    UPDATE userTbl SET grade = @userGrade WHERE userID = @id
    FETCH NEXT FROM userCur INTO @id, @sum  -- 다음행 값을 대입
END

 

CLOSE userCur  -- 커서 닫기
DEALLOCATE userCur  -- 커서 해제

7-2. 저장프로시저를 실행하기 전에 userTbl을 확인해 보자.

SELECT * FROM userTbl

userID   name       birthYear   addr mobile1 mobile2  height grade
-------- ---------- ----------- ---- ------- -------- ------ -----
AJH      안정환        1979        강원   NULL    NULL     182    NULL
CJC      최진철        1975        제주   019     0000000  185    NULL
JJJ      조재진        1986        충북   019     3333333  179    NULL
KNI      김남일        1977        경북   016     6666666  183    NULL
LCS      이천수        1983        인천   011     4444444  179    NULL
LYP      이영표        1983        전북   NULL    NULL     178    NULL
PJS      박지성        1983        서울   011     1111111  181    NULL
PJY      박주영        1986        경기   011     2222222  178    NULL
SJK      송종국        1979        경기   018     9999999  178    NULL
SKH      설기현        1978        서울   011     8888888  182    NULL

7-3. 저장프로시저를 실행하고 다시 userTbl을 확인해 보자.

EXEC usp_while;
SELECT * FROM userTbl

userID    name   birthYear      addr mobile1 mobile2  height grade
------ -------  -----------   ---- ------- -------- ------ --------
AJH      안정환        1979        강원   NULL    NULL   182     일반고객
CJC      최진철        1975        제주   019     0000000  185     최우수고객
JJJ        조재진        1986       충북   019     3333333  179      유령고객
KNI      김남일         1977        경북   016     6666666  183     유령고객
LCS      이천수        1983        인천   011     4444444  179     일반고객
LYP      이영표        1983        전북   NULL    NULL   178     유령고객
PJS      박지성        1983        서울   011     1111111  181      유령고객
PJY      박주영        1986        경기   011     2222222  178      우수고객
SJK      송종국        1979        경기   018     9999999  178      유령고객
SKH      설기현        1978        서울   011     8888888  182      일반고객

 

8. RETURN 문을 이용해서 저장프로시저의 성공 여부를 확인하라.

CREATE PROC usp_return
     @userName nvarchar(10)
AS
     DECLARE @userGrade NVARCHAR(5);
     SELECT @userGrade =  grade FROM userTbl
                 WHERE name = @userName;
     IF (@userGrade <> '')
       RETURN 0;  -- 성공일 경우, 그냥 return만 써도 0을 돌려줌
     ELSE
       RETURN -1;  -- 실패일 경우(즉, 해당 이름의 ID가 없을 경우)

 

DECLARE @retVal INT;

EXEC @retVal=usp_return '안정환';

SELECT @retVal;

0

(1개 행 적용됨)

 

DECLARE @retVal INT;
EXEC @retVal=usp_return '우재남';
SELECT @retVal;

-1

(1개 행 적용됨)

 

오류 처리를 위해서 @@ERROR 함수를 사용해 보자.

CREATE PROC usp_error

    @userid nvarchar(10),

    @name nvarchar(10),

    @birthYear INT = 1900,

    @addr NCHAR(3) 

출처 : http://tortoise05.springnote.com/pages/3977643.xhtml