저장 프로시저, 트리거

저장 프로시저(Stored PROCEDURE)

SQL Server에서 제공되는 프로그래밍 기능이고, 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용된다.

 

설명만 보면 함수랑 비슷해 보이지만 차이점이 있다.

1. 프로시저는 작업을 처리한 절차이지만, 함수는 여러 작업을 위한 기능이다.
2. 프로시저는 서버에서 실행되기 때문에 빠르지만, 함수는 클라이언트에서 실행되기 때문에 프로시저보다 느리다.
3. 프로시저는 리턴값이 없거나 많을 수도 있지만, 함수는 리턴값이 필수이다.

 

 

프로시저 장점과 단점

장점

• SQL Server의 성능을 향상 시킬 수 있다.
  • 하나의 쿼리문으로 원하는 결과를 얻을 수 없을 때가 생기는데,
    그 때 여러줄의 쿼리문을 사용하는 것도 비효율적이며
    인자 값만 상황에 따라 바뀌고 동일한 로직의 쿼리를 필요할 때마다 작성하는 거도 비효율적인데
    이 때 사용하면 성능이 크게 향상될 수 있다.
• 모듈식 프로그래밍이 가능하여 유지보수에 좋다.
  • 저장 프로시저를 생성해 놓으면, 언제든지 실행이 가능하다.
• 보안이 강화된다.
  • 사용자 별로 테이블에 권한을 주지 않고, 프로시저에만 접근 권한을 주는 방식을 사용하기 때문이다.
• 네트워크 부하를 줄일 수 있다.
  • 클라이언트가 직접 SQL문을 작성하지 않고, 프로지서명에 매개변수만 담아 전달하면
    SQL문이 서버에 저장되어 있기 때문에 클라이언트와 서버 간 네트워크 트래픽이 감소된다.

단점

• DB확장이 어렵다.
  • DB 서버 내에서 실행되므로,
    프로시저를 사용하는 경우 새로운 서버를 추가하더라도 프로시저를 재작성하거나다시 배포해야 할 수 있기 때문에
    서버의 성능 한계에 도달할 경우 서버의 수를 늘리는 것보다 DB 수를 늘리는 것이 더 어려울 수 있다.
  • 프로시저는 DB와 강하게 결합되어 있기 때문에, 다른 DB로의 교체비용이 많이 들거나 불가능할 수 있다.

 

 

프로시저 사용 흐름

 

1. 저장 프로시저 정의 단계

1. 구문분석 : 구문의 오류 파악.
2.  지연된 이름 확인 : 저장 프로시저를 정하는 시점에서 해당 개체가 존재하지 않아도 상관없다.
   프로시저 실행 당시에 개체 존재 여부 확인한다.
3. 생성권한 확인 : 현재 사용자가 저장 프로시저를 생성할 권한이 있는지 확인한다.
4. 시스템 테이블 등록 : 저장 프로시저의 이름 및 코드가 시스템 테이블에 등록한다.

 

2. 처음 저장 프로시저 실행

1. 구문 분석 : 구문 자체에 오류가 없는지 분석, 오타가 있으면 에러메시지 출력
2. 개체 이름 확인 : 사용자가 호출한 프로시저가 있는지 확인
3. 사용권한 확인 : 현재 접근중인 사용자가 권한이 있는지 확인
4. 최적화 : 해당 프로시저가 가장 좋은 성능을 낼 수 있는 경로를 결정
5. 컴파일 및 실행 계획 등록 : 해당 실행계획 결과를 메모리에 등록
6. 컴파일된 결과 실행

 

트리거(Trigger)

어떤 트랜잭션이 일어나면 거기에 반응해서 다른 명령을 실행하게 하는 기능이며,

테이블이 insert, update, delete될 때 발생한다.

 

 

트리거의 유형

• 행 트리거 : 테이블 안의 영향을 받은 행 각각에 대해 실행된다.
  변경 전 또는 변경 후의 행은 OLD, NEW라는 가상 줄 변수를 사용하여 읽을 수 있다.
• 문장 트리거 : INSERT, UPDATE, DELETE문에 대해 한 번만 실행된다.

 

트리거 장단점

장점

• 데이터 무결성 강화 : 특정 조건을 충족하지 않는 INSERT 또는 UPDATE 를 방지하거나,
  자동으로 관련 데이터를 업데이트할 수 있습니다.
• 리소스 절약 : 반복적인 작업을 자동화할 수 있으므로, 시간과 노력을 절약할 수 있습니다.
• 자동화된 작업 수행 : 이벤트가 발생할 때 자동으로 실행되므로, 일정한 작업을 자동화하여 클라이언트의 개입을 줄일 수 있습니다.

단점

• 복잡성 : 여러 트리거가 복합적으로 작동하는 경우에는 예측하기 어려울 수 있다.
• 디버깅의 어려움 : 트리거가 자동으로 작동하므로, 디버깅하기 어려울 수 있다.
• 성능 영향 : 이벤트 발생 시마다 실행되므로, 과도한 사용은 성능에 부정적인 영향을 미칠 수 있다.
• 결합도 증가: 과도하게 사용하면 DB 객체 간의 결합도가 증가할 수 있으므로, 유지 관리 및 확장에 어려움을 겪을 수 있다.