728x90
USP : User-Defined Stored Procedure
UFN 또는 UDF : User-Defined Function
사용자가 필요에 의해 정의(User-Defined)하여 사용하는 쿼리 집합이자 절차.
파라미터와 리턴값을 지정할 수 있고 둘 다 구조는 같다.
프로시저는 서버(DB)상에서 처리되고 함수는 클라이언트에서 처리되는데 서버상에서 처리되는 프로시저가 속도가 더 빠르다.
차이점
| Procedure | Function |
| 서버(DB) 상에서 처리 | Client 상에서 처리 |
| 트랜잭션 가능 (TRY-CATCH, CUD, 프로시저 호출 가능) |
트랜잭션 불가능 (TRY-CATCH, CUD, 프로시저 호출 불가능) |
| 리턴 필수 아님 | 리턴 필수 |
생성
-- USP
CREATE PROCEDURE DBO.USP명 (
@InParameter NVARCHAR(30), -- 파라미터타입
@OutParameter NVARCHAR(20) OUTPUT,
@ERRMSG NVARCHAR(200) OUTPUT
) AS
BEGIN
SET NOCOUNT ON; -- 'n개 행이 적용됨' 메시지 표시X(성능 향상됨)
-- DECLARE @Variable NVARCHAR(10) --필요 시 변수 선언
BEGIN TRY -- 트랜잭션 필요한 경우 TRY CATCH
BEGIN TRANSACTION
-- 원하는 CRUD작업 추가
UPDATE ...
SELECT @OutParameter = Target_Col FROM TABLE_A WHERE Search_Col = @InParameter
COMMIT TRANSACTION
RETURN 1
END TRY
BEGIN CATCH
-- 원하는 내용 추가
SET @ERRMSG = ERROR_MESSAGE()
ROLLBACK TRANSACTION
RETURN -1
END CATCH
SET NOCOUNT OFF;
END
GO
-- UFN
CREATE FUNCTION DBO.UFN명 (
@InParameter NVARCHAR(30) -- 파라미터타입
)
RETURNS NVARCHAR(5) -- 리턴타입, RETURNS TABLE 테이블도 가능
AS
-- RETURN ( SELECT문 ) -- 테이블 리턴일 경우
BEGIN
DECLARE @OutParameter NVARCHAR(10)
SELECT @OutParameter = Target_Col FROM TABLE_A WHERE Search_Col = @InParameter
RETURN @OutParameter
END
GO수정/삭제
수정은 생성문에서 CREATE 를 ALTER 로 바꾸고 수정하고 싶은 부분을 수정한 후 실행하면 된다.
CUD는 트랜잭션 잊지 말자
-- 수정
ALTER PROCEDURE ...
ALTER FUNCTION ...
-- 삭제
DROP PROCEDURE USP명
DROP FUNCTION UFN명
조회 & 실행
-- USP 조회
SP_HELPTEXT USP명
-- 조회
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' -- 또는 FUNCTION
AND ROUTINE_NAME LIKE '%USP명%' -- 또는 UFN명
-------------------------------------------------------------
-- USP 실행
DECLARE @OutParameter VARCHAR(20), @ERRMSG VARCHAR(200) -- OUTPUT 값 저장할 변수
EXEC USP명 'InParameter값', @OutParameter OUTPUT, @ERRMSG OUTPUT
SELECT @OutParameter -- 값 찍어보기
-- UFN 실행
SELECT DBO.UFN명('InParameter값') 리턴별칭728x90
'DataBase > MSSQL' 카테고리의 다른 글
| [MSSQL] 이중 커서/중첩 커서(Nested Cursor)로 반복처리 예시 (0) | 2022.09.04 |
|---|---|
| [MSSQL] 특정문자 포함여부에 따라 다른 값 출력(IF, CASE) (0) | 2022.04.29 |
| [MSSQL] 컬럼 합치기 STUFF + FOR XML PATH (0) | 2022.04.25 |
| [MSSQL] dbo.테이블 (0) | 2022.04.06 |
| [MSSQL] ANSI(안시) Query (0) | 2022.03.25 |
