MSSQL PL/SQL 시스템 함수 정리(@@TRANCOUNT, @@ROWCOUNT 등)

프로젝트에서 개발기간 다 지나고 나니 

이제서야 보이는 내 비효율적인 프로시저 코드를 리팩터링하면서 정리하는

MSSQL 문법(시스템 함수 정리)

 

시스템 함수

SQL Server 인스턴스에서 작업을 수행하고 값, 개체 및 설정에 대한 정보를 반환하는 함수.

 

 

시스템 함수 종류

$PARTITION	ERROR_PROCEDURE
@@ERROR	ERROR_SEVERITY
@@IDENTITY	ERROR_STATE
@@PACK_RECEIVED	FORMATMESSAGE
@@ROWCOUNT	GET_FILESTREAM_TRANSACTION_CONTEXT
@@TRANCOUNT	GETANSINULL
BINARY_CHECKSUM	HOST_ID
CHECKSUM	HOST_NAME
COMPRESS	ISNULL
CONNECTIONPROPERTY	ISNUMERIC
CONTEXT_INFO	MIN_ACTIVE_ROWVERSION
CURRENT_REQUEST_ID	NEWID
CURRENT_TRANSACTION_ID	NEWSEQUENTIALID
DECOMPRESS	ROWCOUNT_BIG
ERROR_LINE	SESSION_CONTEXT
ERROR_MESSAGE	SESSION_ID
ERROR_NUMBER	XACT_STATE
ISDATE

 

 

일반 쿼리문에서 자주 사용하는 함수

함수	기능	comment
ISNULL	NULL일 경우 대체	WHERE 절에서 사용하지 말것
ISNUMERIC	입력한 식이 유효한 숫자라면 1 반환, 아니면 0 반환
ISDATE	유효한 날짜/시간이라면 1 반환, 아니면 0 반환

 

 

PL/SQL에서 자주 사용하는 함수

함수	기능	comment
@@ERROR	최근에 실행된 Transact-SQL 문의 오류 번호 반환	integer 반환 오류가 없으면 0 (cf. SELECT message_id FROM sys.messages)
@@ROWCOUNT	최근 실행된 쿼리의 영향을 받은 행 수 반환	int 반환 행 > 20억개 일 경우 ROWCOUNT_BIG 사용
@@TRANCOUNT	현재 연결에서의 BEGIN TRANSACTION 수 반환	BEGIN TRANSACTION : +1  ROLLBACK TRANSACTION : 0 COMMIT TRANSACTION : -1 COMMIT WORK : -1
FORMATMESSAGE	1. sys.messages에 있는 메시지 사용 2. 사용자 정의 문자열 서식 지정	RAISERROR와 유사한 기능

 

1. @@ERROR

1. 특정 오류 처리
--CHECK 제약 조건 위반 발생(오류 #547)
UPDATE HR.EmployeePayHistory  
SET PayFrequency = 4  
WHERE BusinessEntityID = 1;  

IF @@ERROR = 547
    BEGIN
    PRINT N'A check constraint violation occurred.';
    END
GO

2. 조건부로 프로시저 종료
CREATE PROCEDURE HR.usp_DeleteCandidate   
(  
    @CandidateID INT  
)  
AS   
DELETE FROM HR.JobCandidate  
WHERE JobCandidateID = @CandidateID;  

-- Test the error value.  
IF @@ERROR <> 0   	#에러 발생
    BEGIN  
        PRINT N'An error occurred deleting the candidate information.';  
        RETURN 99; #실패  
    END  
ELSE  			#정상처리
    BEGIN  
        PRINT N'The job candidate has been deleted.';  
        RETURN 0;  #정상
    END;  
GO

2. @@ROWCOUNT

UPDATE HR.Employee
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789

IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO

 

3. @@TRANCOUNT

1. BEGIN, COMMIT 문
PRINT @@TRANCOUNT  #=> 0 
BEGIN TRAN  
    PRINT @@TRANCOUNT  #=> 1
    BEGIN TRAN  
        PRINT @@TRANCOUNT  #=> 2
    COMMIT  
    PRINT @@TRANCOUNT  #=> 1
COMMIT  
PRINT @@TRANCOUNT  #=> 0

2. BEGIN, ROLLBACK 문
PRINT @@TRANCOUNT  #=> 0
BEGIN TRAN  
    PRINT @@TRANCOUNT  #=> 1
    BEGIN TRAN  
        PRINT @@TRANCOUNT  #=> 2
ROLLBACK  
PRINT @@TRANCOUNT  #=> 0

4.  FORMATMESSAGE

1. SELECT FORMATMESSAGE(20009, '첫번째 문자열', '두번째 문자열');
=> 'The article '첫번째 문자열' could not be added to the publication '두번째 문자열'.
(cf. SELECT text FROM sys.messages)

2. SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS Result;
=> This is the first variable and this is the second variable.

 

사용 안해봄 ;;

함수	기능	comment
ERROR_PROCEDURE	오류가 발생한 프로시저의 이름을 반환	다른 에러 함수들과 같이 CATCH문에서 사용하면 좋다. ERROR_NUMBER() ERROR_SEVERITY() : 오류 심각도 ERROR_STATE() ERROR_MESSAGE() ERROR_LINE()
ERROR_SEVERITY	오류 심각도 반환
@@IDENTITY	마지막으로 삽입된 ID 값 반환	INSERT, SELECT INTO 후 생성된 마지막 ID값 테이블에서 데이터가 입력될 때 자동으로 증가값이 부여되는 제약조건 자동증가값 설정하여 사용할 수 있다. (CREATE TABLE test ( idx int identity(초기값, 증가값))
@@PACK_RECEIVED	마지막으로 시작한 이후 SQL Server가 네트워크에서 읽은 입력 패킷 수 반환

 

1. ERROR_PROCEDURE

만약 사용하게 된다면

A 프로시저에서 다른 프로시저(B, C, D...)들을 호출하는 경우, 

어떤 프로시저에서 에러가 발생했는지 로깅 처리 할때 사용할 수 있을 듯 하다.

CREATE PROCEDURE usp_ExampleProc  
AS  
    SELECT 1/0;  
GO  
  
BEGIN TRY  
    -- Execute the stored procedure inside the TRY block.  
    EXECUTE usp_ExampleProc;  
END TRY  
BEGIN CATCH  
    SELECT ERROR_PROCEDURE() AS ErrorProcedure;  
END CATCH;  
GO

 

 

다른 함수들 찾아보기

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/functions?view=sql-server-ver16

Microsoft SQL 데이터베이스 함수란? - SQL Server