[이론] PL/SQL - Procedure, 프로시저란?

들어가기에 앞서

최근 업무에서 사용하는 Oracle 기반으로 작성되었습니다.

프로시저란?

• 특정 로직을 처리하고 RETURN 값이 없는 서브 프로그램
• 특정 작업을 수행하기 위한 명령어 집합
• 미리 정의된 로직을 가진 이름 있는 코드 블록
• TABLE에 대해 데이터를 추출(조작)해 이를 다른 TABLE에 저장, 갱신할 때 사용

즉, DB 단에서 특정 비즈니스 로직을 미리 지정해
호출을 통해 언제든 재사용할 수 있는 비즈니스 로직 단위

특징

• 매개변수 전달 및 반복 실행 가능
• IF에 따른 조건 분기 가능
• EXCEPTION에 따른 예외 처리 가능

형식

CREATE OR REPLACE PROCEDURE 프로시저명(파라미터명 타입)
IS
  -- 선언부
BEGIN
  -- 로직 실행
EXCEPTION
  -- 예외 처리
END 프로시저명;

1. IN 매개변수

• 호출 시 프로시저로 입력값을 전달
• READ ONLY로, 프로시저 내부에서 값 변경 불가능
• DEFAULT이므로 생략가능

2. OUT 매개변수

• 프로시저 실행 후 결과를 반환할 때 사용
  • 프로시저는 RETURN 값이 없다며?
    → OUT 매개변수를 통해 호출자에게 데이터를 전달 가능

---

장점

• 하나의 요청으로 여러 SQL문 실행 가능
• 네트워크 소요 시간 감소, 트래픽 감소를 통한 성능 개선
• 여러 애플리케이션과의 로직 공유
• 기능 변경의 편리성 = 특정 기능 변경 시, 프로시저만 변경

단점

• 문자열, 숫자 연산에 있어 백엔드단에서 처리하는 것보다 성능이 느리다.
• 프로시저가 정확히 서비스의 어느 부분에서 사용되는 지 확인이 어렵다.

---

언제 사용할까?

1. 반복적인 작업에 대한 자동화
2. 데이터 처리의 복잡한 비즈니스 로직을 DB 내부에서 관리하고자 할 때
3. 특정 데이터에 대한 접근 방식을 일관적으로 보장하고자 할 때
4. 데이터 무결성, 보안 관리가 필수적인 로직을 구성할 때

왜 사용할까?

1. 코드의 재사용성 증가
2. DB 계층에서 비즈니스 로직을 처리해, 애플리케이션 계층의 부담을 감소
   → 백엔드 단에서 데이터 조회, 처리를 할 함수가 줄어듬
3. 직접적인 테이블 접근 대신 프로시저를 호출해 데이터 무결성을 향상하고, 보안성을 높임
4. 최초 실행될 때 Compile 되고, 이후 재실행 시에는 Compile 되지 않는다.
   → 특정 모듈을 캡슐화해 DBMS에 캐시 저장해 사용 가능
5. WAS를 구성하는 프로그래밍 언어에 의존하지 않아
   MSA와 같은 다양한 환경에서 동일한 로직을 사용 가능하다.
   

---

백엔드 단에서 ORM등으로 DB를 조작하는 것과의 차이점

구분	Procedure 중심 접근법	ORM(API 중심) 접근법
위치	데이터베이스 내부	애플리케이션 계층
성능	빠름 (미리 컴파일된 형태, 데이터베이스에서 직접 실행)	비교적 느림 (ORM 변환 및 DB 연결 오버헤드 존재)
유지보수	로직 변경 시 DB 레벨 수정 필요	로직 변경 시 애플리케이션 코드 수정
이식성	DB 종속성 높음 (Oracle 종속적)	DB 종속성 낮음 (ORM을 사용하여 DB를 쉽게 전환 가능)
복잡도	복잡한 로직을 DB 내에 캡슐화	애플리케이션에서 로직 관리
보안	직접 테이블 접근 최소화 가능 (접근 통제 용이)	애플리케이션에서 별도의 접근 제어 필요

• 즉, 프로시저 방식은 성능과 보안 측면에서 백엔드 단에서 DB를 처리하는 것보다 효과적
• 다만, 이식성 및 유연성이 좋지 않고 DB 종속성이 높다.

---

프로시저 튜닝과 성능 향상

1. 불필요한 반복적 쿼리 제거

• 한 번에 많은 데이터를 처리할 수록 성능이 향상된다.

2. 인덱스 최적화

• 프로시저가 실행되면서 인덱스를 적절히 탐색해야 성능이 향상된다.
  • 단일 인덱스 컬럼을 절차적으로 탐색하는 경우, 성능이 하락됨

3. CURSOR의 효율적 관리

• CURSOR 사용 시, BULK COLLECT 등을 통해 성능 향상
  • 다음 공부 글을 통해 작성하겠다.

4. 예외 처리 최적화

• EXCEPTION 처리를 효과적으로 진행한다.
  • OTHERS를 바로 사용하기 보다는
    NO_DATA_FOUND → VALUE_ERROR → OTHERS 순서 등

5. 불필요 트랜잭션 최적화

• COMMIT, ROLLBACK의 호출 빈도를 최소한으로 트랜잭션을 설계한다.

6. 데이터 처리 방식 최적화

• SQL 연산이 PL/SQL 연산보다 빠르므로, SQL연산을 최대한 활용한다.

---

[참고자료]

• https://scoring.tistory.com/entry/%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80%EB%9E%80
• https://velog.io/@gillog/Oracle-PLSQL-Procedure
• ChatGPT 4.5