MSSQL Stored Procedure

박제이·2023년 12월 28일
RDBMSStored Proceduremssql데이터베이스저장 프로시저

데이터베이스

목록 보기
1/1

0. 개요

MSSQL(Server)의 Stored Procedure(저장 프로시저)는 데이터베이스에서 실행할 수 있는 미리 정의된 쿼리와 로직의 집합입니다. 이는 T-SQL(Transact-SQL)이라 불리는 SQL 언어를 사용하여 작성되며, 데이터베이스에 저장되어 나중에 필요할 때 실행할 수 있습니다. 여러 SQL 문과 제어 구조를 포함할 수 있으며, 프로시저는 일반적으로 데이터베이스에서 빈번하게 사용되는 작업을 수행하도록 설계됩니다.

Stored Procedure의 주요 특징과 기능은 다음과 같습니다:

  • 재사용성: 프로시저는 데이터베이스에 저장되어 있으므로 여러 응용 프로그램이나 쿼리에서 재사용할 수 있습니다. 이는 코드의 중복을 피하고 유지보수를 용이하게 합니다.

  • 파라미터: 프로시저는 입력 파라미터를 받아들일 수 있으며, 실행 시에 다양한 값을 전달하여 동적인 쿼리를 수행할 수 있습니다.

  • 트랜잭션 관리: 프로시저 내에서 트랜잭션을 시작하고 커밋 또는 롤백할 수 있습니다. 이를 통해 데이터 일관성을 유지하고 에러 발생 시 롤백하는 등의 트랜잭션 관리가 가능합니다.

  • 보안: 사용자에 대한 특정 권한을 가진 프로시저를 생성하여 데이터베이스 보안을 강화할 수 있습니다. 다른 사용자가 직접 테이블에 접근하는 것을 허용하지 않고 프로시저를 통해서만 데이터에 접근할 수 있도록 설계할 수 있습니다.

  • 조건부 로직: 프로시저 내에서 조건문, 반복문과 같은 제어 구조를 사용하여 복잡한 로직을 구현할 수 있습니다.

MSSQL의 Stored Procedure 기능은 다른 RDBMS (관계형 데이터베이스 관리 시스템)에서도 유사한 형태로 지원됩니다. 즉, MSSQL만의 독자적인 특징은 아니며, 이러한 기능은 일반적으로 대부분의 관계형 데이터베이스 시스템에서 지원하는 표준적인 기능입니다.

1. Stored Procedure를 만들고 사용하는 과정

1.1 Stored Procedure 생성

Stored Procedure를 생성하기 위해서는 CREATE PROCEDURE 문을 사용합니다. 아래는 간단한 예제입니다:

CREATE PROCEDURE GetEmployee
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

이 예제에서 GetEmployee라는 Stored Procedure를 생성하였습니다. 이 프로시저는 @EmployeeID라는 입력 파라미터를 받아들이고, 해당 ID에 해당하는 직원 정보를 조회하는 쿼리를 포함하고 있습니다.

1.2 Stored Procedure 실행

프로시저를 실행하기 위해서는 EXEC 키워드를 사용합니다:

EXEC GetEmployee @EmployeeID = 123;

위의 예제에서 GetEmployee 프로시저를 실행하고, @EmployeeID 파라미터에 123을 전달하고 있습니다.

1.3 파라미터를 사용한 Stored Procedure

Stored Procedure에 파라미터를 추가하여 동적인 동작을 수행할 수 있습니다. 예를 들어, 다음은 입력 파라미터와 출력 파라미터를 사용하는 Stored Procedure의 예입니다:

CREATE PROCEDURE CalculateSum
    @Num1 INT,
    @Num2 INT,
    @Sum INT OUTPUT
AS
BEGIN
    SET @Sum = @Num1 + @Num2;
END;

이 프로시저는 @Num1과 @Num2를 입력으로 받아들이고, @Sum을 출력으로 반환합니다.

1.4 트랜잭션 관리

프로시저 내에서 트랜잭션을 관리할 수 있습니다. 예를 들어, 다음은 트랜잭션을 시작하고 롤백하는 Stored Procedure의 예입니다:

CREATE PROCEDURE ProcessOrder
    @OrderID INT
AS
BEGIN
    BEGIN TRANSACTION;

    -- 수행할 작업들
    DELETE FROM Orders WHERE OrderID = @OrderID;

    -- 예외 상황인 경우 롤백
    ROLLBACK TRANSACTION; -- 또는 COMMIT TRANSACTION; (성공 시)

END;

1.5 보안 관리

프로시저를 사용하여 특정 권한을 가진 사용자만이 특정 작업을 수행할 수 있도록 할 수 있습니다.

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL
WITH EXECUTE AS OWNER
AS
BEGIN
    -- 보안 검사 및 작업 수행
    UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
END;

'WITH EXECUTE AS OWNER'는 프로시저가 소유자의 보안 주체로 실행되도록 하는 구문입니다.

이렇게 MSSQL에서 Stored Procedure를 생성하고 사용하는 기본적인 단계들은 위와 같습니다. 저장 프로시저를 사용하면 코드의 재사용성을 높이고 유지보수를 용이하게 할 수 있습니다.

2. 쿼리문을 직접 사용하는 방식과 Stored Procedure를 사용하는 방식 간에는 몇 가지 차이점

2.1 쿼리문을 직접 사용하는 방식:

  • 직접적인 쿼리 제어: 개발자가 직접 SQL 쿼리를 작성하고 실행합니다.

  • 유연성: 동적인 쿼리를 더 쉽게 작성할 수 있습니다. 조건이나 정렬과 같은 부분이 동적으로 바뀌어야 하는 경우에 유용합니다.

  • ORM 미사용: 만약 ORM을 사용하지 않는다면, 쿼리문을 직접 작성하는 것이 일반적입니다.
    ->     ORM 프레임워크(MyBatis, Hibernate)는 Java 객체와 데이터베이스 테이블 간의 매핑을 자동으로 처리해주는 도구입니다.

  • 디버깅 용이: 개발자는 직접 작성한 쿼리를 디버깅할 수 있어 오류를 빠르게 찾을 수 있습니다.

2.2 Stored Procedure를 사용하는 방식:

  • 로직의 중앙 집중화: 비즈니스 로직을 데이터베이스 내에 중앙 집중화할 수 있습니다. 저장 프로시저는 데이터베이스 서버에서 실행되므로, 네트워크 트래픽을 줄일 수 있고, 트랜잭션을 효과적으로 관리할 수 있습니다.

  • 보안 및 권한 관리: 특정 사용자에게 프로시저의 실행 권한을 부여하고, 테이블에 대한 직접적인 접근을 제한할 수 있습니다.

  • 최적화: 일부 데이터베이스 시스템은 Stored Procedure를 컴파일하여 실행 계획을 최적화하는데, 이는 일부 상황에서 성능 향상을 가져올 수 있습니다.

  • 재사용성: 동일한 로직을 여러 곳에서 사용해야 할 때, 저장 프로시저를 만들어서 재사용할 수 있습니다.

  • 버전 관리: 프로시저는 데이터베이스 내에 저장되므로, 데이터베이스 스키마의 변경에 대한 버전 관리가 용이합니다.

그러나 프로젝트 팀이나 개발자의 기술 스택, 경험, 특정 데이터베이스 시스템의 특성 등에 의해 결정될 수 있습니다. Stored Procedure를 사용하는 것이 더 적절한지는 프로젝트 요구사항과 개발자의 선호도에 따라 다를 수 있습니다.

3. ERP와 그룹웨어 시스템 사이의 연계에서 Stored Procedure를 사용하는 경우에는 몇 가지 장점들

3.1 데이터 일관성과 효율성:

  • 트랜잭션 관리: ERP 시스템에서 그룹웨어로 데이터를 전송하는 경우, 여러 테이블 간의 복잡한 작업이 수반될 수 있습니다. 이런 작업을 하나의 트랜잭션 내에서 처리할 때, Stored Procedure를 사용하여 트랜잭션의 일관성을 유지할 수 있습니다. 트랜잭션 내에서 여러 단계의 작업을 수행하고, 성공 또는 실패에 따라 롤백이나 커밋을 관리할 수 있습니다.

  • 복잡한 로직 처리: 데이터를 가공하거나 특정 비즈니스 규칙을 적용해야 하는 경우, Stored Procedure를 사용하여 데이터 처리 로직을 중앙에서 관리하고, 프로시저 호출을 통해 효율적으로 처리할 수 있습니다.

3.2 보안과 접근 제어:

  • 접근 권한 제어: Stored Procedure를 사용하여 ERP 시스템에서 그룹웨어로의 데이터 이전 작업을 수행하면, 그룹웨어에서 직접적인 테이블 접근이 아닌 프로시저 호출을 통해 특정 권한을 가진 사용자만이 데이터에 접근할 수 있도록 제어할 수 있습니다. 이는 데이터의 보안을 강화할 수 있습니다.

  • 데이터 마스킹: 필요한 경우, Stored Procedure를 사용하여 민감한 데이터를 마스킹하거나 필요한 정보만을 추출하여 전송할 수 있습니다.

3.3 유지보수 및 버전 관리:

  • 중앙화된 로직: 프로시저를 사용하면 데이터 이전 또는 연계 로직을 데이터베이스 내에서 중앙화할 수 있습니다. 이는 유지보수를 간편하게 만들고, 변경이 필요한 경우 프로시저만 수정하면 되므로 버전 관리에 용이합니다.

  • 쿼리 최적화: 프로시저는 데이터베이스 엔진에 의해 컴파일되므로 성능 최적화가 가능합니다.

3.4 비동기 처리:

  • 비동기 작업: Stored Procedure를 사용하면 비동기적으로 백그라운드에서 작업을 수행할 수 있습니다. 데이터 전송 작업이 시간이 소요되는 경우, 프로시저를 통해 요청을 받아들이고 나중에 비동기적으로 처리할 수 있습니다.

이러한 이유들로 Stored Procedure를 활용하여 ERP와 그룹웨어 시스템 간의 연계를 구현하면, 데이터 일관성과 효율성을 높일 수 있으며, 보안 및 접근 제어를 강화하며, 유지보수 및 버전 관리를 용이하게 할 수 있습니다.

4. Stored Procedure의 정의를 확인

Stored Procedure가 어떻게 구성되었는지 확인하는 방법은 데이터베이스 관리 시스템(DBMS)에 따라 다를 수 있습니다. 일반적으로 Stored Procedure의 정의를 확인하는 방법은 다음과 같습니다.

4.1 SQL Server (MSSQL):

Stored Procedure의 정의를 확인하려면 SQL Server Management Studio(SSMS) 또는 쿼리를 실행할 수 있는 다른 도구를 사용합니다.

-- Stored Procedure의 정의 확인
sp_helptext 'YourStoredProcedureName';

위의 쿼리에서 'YourStoredProcedureName'에는 확인하고 싶은 Stored Procedure의 이름을 넣어주어야 합니다.

4.2 MySQL 또는 MariaDB:

-- Stored Procedure의 정의 확인
SHOW CREATE PROCEDURE YourStoredProcedureName;

MySQL과 MariaDB에서는 SHOW CREATE PROCEDURE 명령을 사용하여 Stored Procedure의 정의를 확인할 수 있습니다.

4.3 Oracle Database:

-- Stored Procedure의 정의 확인
SELECT text
FROM user_source
WHERE name = 'YourStoredProcedureName' AND type = 'PROCEDURE';

Oracle Database에서는 user_source 테이블을 사용하여 Stored Procedure의 정의를 확인할 수 있습니다.

4.4 PostgreSQL:

-- Stored Procedure의 정의 확인
SELECT pg_get_functiondef('YourStoredProcedureName'::regproc);

PostgreSQL에서는 pg_get_functiondef 함수를 사용하여 Stored Procedure의 정의를 확인할 수 있습니다.

주의:
몇몇 데이터베이스 관리 시스템에서는 사용자가 Stored Procedure를 조회할 수 있는 권한이 필요할 수 있습니다. 관리자 또는 해당 프로시저를 생성한 사용자에게 문의하거나 권한을 확인해야 합니다.

명령어나 함수가 데이터베이스 관리 시스템에 따라 다를 수 있으므로, 사용 중인 DBMS의 문서를 참고하여 적절한 명령을 확인해야 합니다.

profile
박제이
백엔드 개발자입니다.

0개의 댓글