New

USE sqlDB;

GO

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

GO

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 userCur  -- 커서열기

            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 -- 커서 해제

CREATE PROC usp_error

 @userid nvarchar(10),

 @name nvarchar(10),

 @birthYear INT = 1900,

 @addr NCHAR(4) = '서울',

 @mobile1 NCHAR(3) = NULL,

 @mobile2 NCHAR(8) = NULL,

 @height smallInt = 180

AS

 DECLARE @err INT;

 INSERT INTO userTbl(userID,name,birthYear,addr,mobile1,mobile2,height)

   VALUES (@userid,@name,@birthYear,@addr,@mobile1,@mobile2,@height);

 SELECT @err = @@ERROR;

 IF @err != 0

 BEGIN

  PRINT  '###' + @name + '을(를) INSERT에실패했습니다. ###'

 END;

 RETURN @err; -- 오류번호를 돌려줌.

------------------------------------------------------------

TRY...CATCH문 이용으로 변경

==>

CREATE PROC usp_tryCatch

 @userid nvarchar(10),

 @name nvarchar(10),

 @birthYear INT = 1900,

 @addr NCHAR(4) = '서울',

 @mobile1 NCHAR(3) = NULL,

 @mobile2 NCHAR(8) = NULL,

 @height smallInt = 180

AS

 DECLARE @err INT;

 BEGIN TRY 

   INSERT INTO 

userTbl(userID,name,birthYear,addr,mobile1,mobile2,height)

   VALUES (@userid, @name, @birthYear, @addr, @mobile1, 

@mobile2, @height)

 END TRY

 BEGIN CATCH

  SELECT ERROR_NUMBER()

  SELECT ERROR_MESSAGE()

 END CATCH

ERROR_NUMBER()  : 오류번호

ERROR_MESSAGE() : 오류메시지

ERROR_SEVERITY() : 오류심각도

ERROR_STATE() : 오류 상태 번호

ERROR_LINE() : 오류를 발생시킨 행 번호

ERROR_PROCEDURE() : 오류가 발생한 저장프로서져 또는 트리거 이름

----------------------------------------------------------------

사용예)

BEGIN TRY

  INSERT INTO userTbl VALUES('AJH', '우주희', 1988, '서울', NULL, NULL, 170)

  PRINT N'정상적으로입력되었다.'

END TRY

BEGIN CATCH

   PRINT N'***오류가발생했다***'

   PRINT N'오류번호: ' 

   PRINT ERROR_NUMBER()

   PRINT N'오류메시지: '

   PRINT ERROR_MESSAGE() 

   PRINT N'오류상태코드: '

   PRINT ERROR_STATE() 

   PRINT N'오류심각도: '

   PRINT ERROR_SEVERITY() 

   PRINT N'오류발생행번호: '

   PRINT ERROR_LINE() 

   PRINT N'오류발생프로시저/트리거: '

   PRINT ERROR_PROCEDURE() 

END CATCH

-------------------------------------

결과

***오류가발생했다***

오류번호: 

2627

오류메시지: 

PRIMARY KEY 제약 조건 'PK__userTbl__7C8480AE'을(를) 위반했습니다. 개체 'dbo.userTbl'에 중복 키를 삽입할 수 없습니다.

오류상태코드: 

1

오류심각도: 

14

오류발생행번호: 

2

오류발생프로시저/트리거:

 GETDATE        현재 날짜와 시간

                     SELECT GETDATE()

 DATEDIFF       두날짜 차이 리턴.

                      SELECT DATEDIFF(week, GETDATE(), '2009/09/01')

                        --> 현재부터 2009.9.1까지 남은 주

 DATENAME     날짜의 지정한 부분만 리턴

                       --> SELECT DATENAME(weekday, '2009/06/06' )

                       --> 2009.6.6 무슨요일인지 리턴.

 DAY                지정된 날짜의 일   SELECT DAY('2009/09/01')

 MONTH           지정된 날짜의 월

 YEAR              지정된 날짜의 년

 DATEADD        날짜에 더한 결과 리턴.

                      SELECT DATEADD(day, 100, '2006/10/10' )

                          --> 2006년 10월 10일 부터 100일 후의 날짜

                          --> day , year, month, week, hour, minute, second

ROUND( 1234.5678, 2)      --> 1234.5700

ROUND( 1234.5678, -2)     --> 1200.0000