OracleSQL 핵심정리12 - 저장프로시저, 저장함수, 커서, 트리거

저장프로시저

• PL/SQL문을 데이터베이스에 저장하여 재사용 가능하게 만든 데이터베이스 오브젝트
• 데이터베이스에 저장할 수 있도록 저장 프로시저를 제공
  • ex) CREATE [OR REPLACE] PROCEDURE 프로시저명 (매개변수) IS 지역변수선언 BEGIN 실행할 문장 END;
• USER_SOURCE : 프로시저 생성 확인
• 매개변수 유형
  • IN : 데이터 전달 받을 때 사용
  • OUT : 수행된 결과를 받아갈 때 사용
  • INOUT : 매개변수 선언시 사용
• 실행 방법
  • ex) EXCUTE 프로시저명(매개변수) : 프로시저를 실행
  • 매개변수에 값을 전달하고 결과를 받기 위해 바인드 변수 사용 가능
• 바인드 변수 : 프로시저 수행 후 구해진 결과를 받아오기 위해서 사용하는 변수
  • ex) VARIABLE 변수명 변수타입
  • OUT 매개변수 사용: 실행 시 바인드 변수 앞에 : 을 붙여 값을 저장
  • 바인드 변수 출력 : PRINT 변수명;
• 저장프로시저의 장점
  • 클라이언트와 서버 간의 네트워크 트래픽 감소 : 필요한 데이터 처리를 서버 측에서 완료하므로 네트워크 부하 감소
  • 처리 속도 향상 : 데이터베이스 서버에서 직접 실행되므로 데이터 처리가 빠름

저장함수

• 저장프로시저와 유사한 용도로 사용하나 실행 결과를 되돌려 받을 수 있음
• 데이터를 조회하거나 계산된 값을 반환할 필요가 있을 때 유용
• 생성 방법
  • ex) CREATE [OR REPLACE] FUNCTION 함수명 (매개변수) IS 지역변수선언; BEGIN 실행할 문장 RETURN 값; END 함수명;
    • OR REPLACE : 기존에 같은 이름의 함수가 있을 경우 이를 대체하여 새로운 함수를 생성
    • 매개변수 : 함수에 전달할 입력값의 타입을 정의
    • RETURN : 함수가 실행 후 되돌려줄 값의 데이터 타입을 명시합니다.
• 실행 방법
  • ex) DECLARE 결과변수 타입; BEGIN 결과변수 := 함수명(매개변수); END;
  • 바인드 변수를 사용하여 함수의 반환 값을 저장

CURSOR

• CURSOR : SQL 쿼리로부터 반환된 여러 행의 결과를 순차적으로 처리하기 위한 PL/SQL 구조
• 선언 방법
  • DECLARE CURSOR 커서명 IS 셀렉트문; BEGIN OPEN 커서명; FETCH 커서명 INTO 변수; CLOSE 커서명; END;
    • DECLARE : DECLARE CURSOR 커서명 IS 셀렉트문;을 사용하여 커서를 선언. 커서에 실행할 SQL 쿼리를 지정
    • OPEN : OPEN 커서명;을 통해 커서가 지정한 SELECT 문을 실행하고 결과 세트를 메모리에 저장
    • FETCH : FETCH 커서명 INTO 변수;를 통해 커서로부터 데이터를 한 행씩 가져와서 데이터를 지정한 변수에 저장
    • CLOSE : CLOSE 커서명;으로 커서를 닫고, 메모리에 할당된 자원을 해제
• 커서 상태
  • %FOUND: 마지막 FETCH 연산이 성공적으로 데이터를 반환했을 때 TRUE <-> %NOTFOUND
  • %ISOPEN: 커서가 현재 열려있을 때 TRUE
  • %ROWCOUNT: 현재까지 FETCH된 행의 총 수를 반환

TRIGGER

• 데이터베이스에서 특정 이벤트(INSERT, UPDATE, DELETE)가 발생했을 때 자동으로 실행되는 프로시저
• 데이터의 무결성 유지, 감사 로그 기록, 자동 계산 수행 등 다양한 용도로 활용
• 생성 방법
  • ex) CREATE TRIGGER 트리거명 TIMING[BEFORE | AFTER] EVENT[INSERT | UPDATE | DELETE] ON 테이블명 BEGIN 실행할문장 END
    • TIMING : 트리거가 실행될 시점을 정의. BEFORE는 DML문이 실행되기 전, AFTER는 실행된 후를 의미
    • EVENT : 트리거를 발생시키는 DML문을 지정
  • 문장 레벨 트리거 : DML문이 실행될 때 단 한 번만 트리거. 전체 작업에 대한 일괄 처리에 적합
  • 행 레벨 트리거 : DML문에 의해 영향을 받는 각 행에 대해 개별적으로 실행. 개별 행의 데이터를 검사하거나 변경할 때 사용
• 트리거 생성 시 고려 사항
  • 데이터베이스 성능에 영향을 줄 수 있으므로, 필요한 경우에만 사용
  • 복잡한 비즈니스 로직을 트리거 내에 너무 많이 포함시키면 유지 관리가 어려워짐