[SQL SERVER] 저장 프로시저와 사용자 정의 함수

1. 저장 프로시저 : 쿼리문의 집합 -> 어떤 동작을 일괄 처리할때 사용

장점

1) SQL Server의 성능을 향상 시킬 수 있다

2) 유지 관리가 간편하다

3) 모듈식 프로그래밍이 가능하다

4) 보안을 강화할 수 있다

ex) 사용자에게 테이블 권한을 주지 않고 프로시저에 대한 권한을 준다 

5) 네트워크 전송량의 감소

: 쿼리문이 네트워크를 통해서 전송되는데 쿼리가 많을 수록 많은 부하를 준다  

과정

구문 분석 -> 개체 이름 확인 -> 사용 권한 확인 -> 최적화 (대부분 indexing) -> 컴파일 및 실행계획 등록 => 메모리에 저장 -> 실행

같은 과정을 한번 더 실행하면

메모리 확인 -> 실행 (단 한글자라도 다르다면 다르게 취급한다.)

EXEC usp_ID 5000 WITH RECOMPILE;

:저장 프로시저 option -> recompile

DBCC FREEPROCCACHE;

: CACHE 비움

SET STATISTICS TIME ON;

: 시간 확인

CREATE PROCEDURE usp_users

AS

SELECT * FROM userTbl;

GO

: 프로시저 생성하기

EXEC usp_users;

: 프로시저 실행하기

ALTER PROCEDURE

: 프로시저 변경하기

DROP PROCEDURE

: 프로시저 삭제하기

ex1) 

CREATE PROCEDURE usp_users1

@userName NVARCHAR(10)

AS

SELECT * FROM userTbl WHERE name=@userName;

GO

EXEC usp_users1 'name';

ex2)

CREATE PROCEDURE usp_users4

@txtValue NCHAR(10),

@outValue INT OUTPUT --출력

AS

INSERT INTO testTbl VALUES(@txtValue);

SELECT @outValue = IDENT_CURRENT('testTbl');

GO

: 저장 프로시저를 생성할 때 꼭 테이블이나 뷰가 있어야 하는 것은 아니다

DECLARE @myValue INT;

EXEC usp_users4 '테스트값1', @myValue OUTPUT;

PRINT '현재 입력된 값 => '+CAST(@myValue AS CHAR(5));

: 실행할 때!

CREATE PROC usp_ifElse

@userName NVARCHAR(10)

AS

DECLARE @bYear INT

SELECT @bYear = birthYear FROM userTbl

WHERE name = @userName

IF (@bYear >=1980 )

BEGIN

PRINT '젊다'

END

ELSE

BEGIN

PRINT '늙었다'

END

GO

EXEC usp_ifElse '이름'

: procedure if else!

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(50)

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

WHILE(@@FECTCH_STATUS=0)  -- 행이 없을 때까지 반복 

BEGIN

SET @userGrade = 

CASE

WHEN (@sum >= 1500) THEN '고객'

ELSE '유령 고객'

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 char(8)

AS

DECLARE @err INT;

INSERT INTO userTbl VALUES(@userid);

// error 가 있는 것을  if문으로 확인

SELECT @err = @@ERROR;

IF @err != 0

BEGIN

PRINT 'INSERT 실패'

END;

RETURN @err;

// error를 try catch 구문으로 해결

CREATE PROC usp_error

 @userid char(8)

AS

DECLARE @err INT;

BEGIN TRY

INSERT INTO table VALUES (@userid)

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER()

SELECT ERROR_MESSAGE()

END CATCH

: 쿼리 실행시 오류가 발생했을 때

DECLARE @userID char(8);

EXEC @retVal = usp_error 'userid';

SELECT @retVal;

: procedure 실행시 return 값을 출력

USE sqlDB;

SELECT o.name, n.definition

FROM sys.sql_modules m

JOIN sys.object o

ON m.object_id = o.object_id AND o.TYPE = 'P';

: 생성한 모듈의 이름과 definition을 확인

EXECUTE sp_helptxt usp_error;

: module 하나의 정의를 보여줌 

EXEC sp_executesql N'SELECT * FROM userTbl';

CREATE TYPE userTblType AS TABLE(

userID char(8),

name NVARCHAR(10),

birthYear int,

addr NCHAR(2)

)

: table이라는 type을 생성

CREATE PROCEDURE usp_tableTypeParameter

@tblPara userTblType READONLY -- 테이즐 형식의 매개변수는 READONLY를 꼭 써줘야합니다.

AS

BEGIN

SELECT * FROM @tblPara

END

2. 사용자 정의 함수

조건

1) Try Catch를 사용할 수 없다

2) CREATE / ALTER / DROP 문을 사용할 수 없다

3) 오류가 발생하면 즉시 함수를 멈추고 오류값을 반환한다.

1) 사용자 정의 스칼라 함수 : return 문에 의해서 단일값을 돌려줌

CREATE FUNCTION ufn_getAge(@byear INT)

RETURN INT

AS

BEGIN

DECLARE @age INT

SET @age = YEAR(GETDATE()) - @byear

RETURN(@age)

END

GO

SELECT dbo.ufn_getAge 1979;

ALTER FUNCTION ufn_getAge(@byear INT) ~~~
: 함수 수정

DROP FUNCTION ufn_getAge;

: 함수 삭제

2) 사용자 정의 테이블 반환 함수

 

 2-1) 인라인 테이블 반환 함수

CREATE FUNCTION 함수이름(매개 변수)

RETURN TABLE

AS

RETURN(

SELECT * FROM user;

)

GO

 2-2) 다중문 테이블 반환 함수

CREATE FUNCTION 함수이름(매개변수)

RETURNS @테이블 변수 TABLE

(열 이름과 데이터 형식 정의...)

AS

BEGIN

위에서 정의한 TABLE에 INSERT 하는 함수

RETURN;

END;

3) 스키마 바운드 함수

: 함수에서 참조하는 테이블, 뷰 등이 수정되지 못하도록 설정한 함수

WITH SCHEMABINDING