PROCEDURE (프로시저)

프로시저란 자주 사용되는 쿼리를 모듈화시켜 객체로 저장하고, 필요한 시점에 불러서 사용하는 것이다. 함수와 달리 독자적인 실행이 가능하다.

프로시저는 일종의 저장된 프로그램으로, 복잡한 SQL 문장들을 하나의 논리적 단위로 묶어서 관리할 수 있게 해준다. 이를 통해 애플리케이션 코드의 복잡도를 줄이고, 데이터베이스 레벨에서 데이터 처리 로직을 중앙화할 수 있다. (애플리케이션을 가볍게 만드는것이 항상 정답은 아니다. 프로그래밍적으로 강점이 있는 팀에서 일한다면 DB를 가볍게 서버에 무게를 둘 수도 있다.)

만약 6개 이상의 테이블이 조인된 상황이라면, 프로시저를 활용하면 네크워크 트래픽을 줄여 성능을 높일 수 있으며 쿼리가 탈취당하더라도 프로시져 이름만 노출되므로 보안상으로 이점을 얻을 수 있다.

프로시저 장점

위에서 말한 장점외에도 실무에서 관리 비용 측면으로 훨씬 강력한 이점이 있다.

업무 규칙 변경

프로시저의 장점이 진짜 체감되는 순간은 업무 규칙이 바뀔 때다.
예를 들어 30번 부서는 2025년부터 급여 산정 방식 변경이라는 요구사항이 나왔다고 가정해보자.

일반 쿼리 기반 애플리케이션:

• 최소 3~7개의 서비스/DAO/쿼리 파일을 찾아서 변경해야 한다
• 각 파일마다 동일한 로직이 중복되어 있을 가능성이 높다
• 코드 변경 후 빌드, 테스트, QA 검증 과정을 거쳐야 한다
• 운영 서버에 배포하는 절차가 필요하다
• 배포 중 서비스 중단 시간이 발생할 수 있다

프로시저 기반 구조:

• 프로시저 내부 CASE 로직 한 줄만 바꾸면 끝난다
• 다른 시스템 변경 없이 운영에 바로 반영된다
• 애플리케이션 재배포가 필요 없다
• 서비스 중단 없이 즉시 적용 가능하다

-- 프로시저 내부 로직 수정 예시
CREATE OR REPLACE PROCEDURE usp_calculate_salary
(v_deptno IN NUMBER, v_base_sal IN NUMBER)
IS
    v_result NUMBER;
BEGIN
    IF v_deptno = 30 AND TO_CHAR(SYSDATE, 'YYYY') >= '2025' THEN
        -- 2025년부터 30번 부서는 새로운 계산 방식 적용
        v_result := v_base_sal * 1.2 + 50000;  -- 이 한 줄만 수정
    ELSE
        v_result := v_base_sal * 1.1;
    END IF;
    
    -- 결과 저장 로직...
END;

프로시저가 표준인 업종

금융/보험/통신/카드사/공공기관처럼 규정 변경이 연 20~200번 이상 발생하는 조직에서는 프로시저 기반이 거의 표준이다. 이런 업종의 특징은 다음과 같다:

• 법률/규정 변경이 잦다: 정부 정책, 금융 규제, 약관 변경 등이 수시로 발생한다
• 변경 적용이 급하다: "다음 달 1일부터 적용" 같은 촉박한 일정이 많다
• 배포 리스크가 크다: 24시간 운영 시스템이라 함부로 재배포하기 어렵다
• 정확성이 중요하다: 돈이나 개인정보를 다루므로 실수가 용납되지 않는다

이런 조직에서는 애플리케이션 배포보다 데이터베이스 레벨 수정을 선호한다. 프로시저 하나만 바꾸면 전체 시스템에 즉시 반영되고, 문제가 생기면 이전 버전으로 즉시 롤백할 수 있기 때문이다.

네트워크 트래픽 감소

쿼리문 대신 프로시저 이름만 전송하므로 시간이 단축된다. 예를 들어 100줄짜리 복잡한 쿼리문을 매번 전송하는 대신, 단순히 프로시저 이름 하나만 보내면 되기 때문에 네트워크 부하가 현저히 줄어든다. 특히 원격지에서 접속하거나 네트워크 속도가 느린 환경에서는 이러한 차이가 체감될 정도로 크다.

기존 방식

APP(emp.java > select ... 구문) -> 네트워크 -> DB연결 -> select... -> DB

프로시저 방식

APP(emp.java > usp_emplist) -> 네트워크 -> DB연결 -> usp_emplist -> DB

보안 강화

네트워크 상에서 쿼리가 노출되지 않는다. 해커가 네트워크 패킷을 가로채더라도 실제 SQL 구문을 볼 수 없고, 단지 프로시저 이름만 확인할 수 있다. 또한 프로시저에 대한 실행 권한만 부여하면 되므로, 테이블에 대한 직접적인 접근 권한을 주지 않아도 된다는 보안상의 이점이 있다.

비즈니스 로직 중앙화

급여 계산, 포인트 적립, 등급 산정 같은 핵심 비즈니스 로직을 프로시저로 만들어두면 여러 시스템에서 일관되게 사용할 수 있다. 웹 애플리케이션, 모바일 앱, 배치 프로그램, 관리자 도구 등 어디서 호출하든 동일한 결과를 보장한다.

프로시저 단점

파라미터 받기, 리턴문 처리가 다소 복잡하다 (불가능하지는 않다). 특히 OUTPUT 파라미터를 사용하거나 여러 개의 결과셋을 반환해야 할 때는 일반 SQL 쿼리보다 코드가 복잡해질 수 있다. 또한 디버깅이 애플리케이션 레벨에서 하는 것보다 어렵고, 데이터베이스가 변경될 경우 프로시저를 다시 작성해야 하는 이식성 문제도 존재한다.

DB가 코드 저장소가 된다

단점에서 진짜 문제되는 부분은 "DB가 코드 저장소가 된다" 문제다. 즉, 유지보수 인력이 바뀌었을 때 로직이 소스코드에 없고 DB 내부에 있으므로 개발자가 스키마를 까보지 않으면 흐름을 이해하기 어렵다.

일반적인 애플리케이션 개발에서는 Git 저장소를 클론받고 IDE로 코드를 열면 전체 비즈니스 로직을 파악할 수 있다. 하지만 프로시저 기반 시스템에서는:

• DB에 접속해서 프로시저 목록을 확인해야 한다
• 각 프로시저가 어떤 다른 프로시저를 호출하는지 추적해야 한다
• 프로시저 간 의존관계를 파악하기 어렵다
• 코드 검색 도구(Grep, IDE 검색)를 사용할 수 없다

예를 들어 신입 개발자가 입사해서 "급여 계산 로직이 어디 있나요?" 라고 물었을 때:

• 일반 프로젝트: "SalaryService.java 파일 보세요"
• 프로시저 기반: "DB 접속해서 USP_로 시작하는 프로시저들 찾아보세요. 아마 서로 호출하고 있을 거예요."

필수적인 개발 프로세스

그래서 프로시저 기반 설계를 채택하는 팀은 반드시 이런 규칙을 내부적으로 만든다:

1. 프로시저 변경 시 반드시 형상관리 도구(Git)에 버전 보관

/database
  /procedures
    usp_calculate_salary.sql
    usp_process_order.sql
  /functions
    f_get_discount_rate.sql
  /migration
    2024-01-15_update_salary_proc.sql

DB에만 프로시저가 존재하면 누가 언제 왜 수정했는지 알 수 없다. 반드시 소스 파일로 관리하고 변경 이력을 Git에 남겨야 한다.

2. 프로시저 이름 규칙 엄격하게 준수

• usp_ : 사용자 정의 프로시저
• usp_batch_ : 배치 작업용 프로시저
• usp_api_ : API 호출용 프로시저
• f_ : 사용자 정의 함수

이름만 봐도 용도를 알 수 있도록 체계를 만들어야 한다.

3. 프로시저에서 호출하는 하위 로직도 프로시저 or 함수로 모듈화

하나의 거대한 프로시저에 모든 로직을 때려 넣으면 유지보수가 불가능하다. 작은 단위로 쪼개서 각각을 프로시저나 함수로 만들어야 한다.

-- 나쁜 예: 1000줄짜리 거대 프로시저
CREATE PROCEDURE usp_process_all IS
BEGIN
    -- 급여 계산 로직 500줄
    -- 포인트 계산 로직 300줄
    -- 등급 산정 로직 200줄
END;

-- 좋은 예: 모듈화된 구조
CREATE PROCEDURE usp_process_all IS
BEGIN
    usp_calculate_salary();
    usp_calculate_point();
    usp_update_grade();
END;

4. 프로시저 의존관계 문서화

어떤 프로시저가 어떤 프로시저를 호출하는지 다이어그램이나 문서로 정리한다. 복잡한 시스템에서는 이것이 없으면 전체 흐름을 파악하기 불가능하다.

DB 지옥을 피하는 방법

반대로 이런 프로세스가 없는 팀은 프로시저를 쓰는 순간 "누구도 전체 로직 구조를 파악 못하는 DB 지옥" 이 된다. 다음과 같은 상황이 발생한다:

• 퇴사한 개발자가 만든 프로시저를 아무도 건드리지 못한다
• 프로시저를 수정했다가 다른 시스템이 망가진다
• 동일한 로직이 여러 프로시저에 중복되어 있다
• 어떤 프로시저가 실제로 사용되는지 아무도 모른다
• 레거시 프로시저를 지우면 안 될 것 같아서 계속 쌓인다

결론: 프로세스와 함께 가야 한다

그래서 실무에서는 "프로시저를 쓰는 조직 = 반드시 표준 개발 프로세스 / 문서화 체계가 있는 조직" 이라는 전제가 같이 붙는다.

프로시저는 강력한 도구지만, 그만큼 규율 있게 사용해야 한다. 작은 팀이나 스타트업에서 무분별하게 프로시저를 남발하면 나중에 큰 기술 부채가 된다. 하지만 체계적인 개발 프로세스를 갖춘 조직에서는 프로시저가 생산성과 안정성을 크게 향상시킨다.

기본 프로시저 생성

가장 기본적인 형태의 프로시저는 파라미터 없이 단순히 정해진 작업만 수행하는 것이다. 아래 예제는 30번 부서의 모든 직원의 직무를 'TTT'로 변경하는 프로시저다.

create or replace procedure usp_emplist
is
BEGIN
    update emp
    set job = 'TTT'
    where deptno=30;
END;

실행 방법

execute usp_emplist;
-- 또는
exec usp_emplist;

INPUT 파라미터를 사용하는 프로시저

실무에서는 고정된 값보다는 외부에서 값을 받아서 처리하는 경우가 훨씬 많다. INPUT 파라미터를 사용하면 프로시저 호출 시 필요한 값을 전달할 수 있다. 아래 예제는 특정 사원번호를 받아서 해당 사원의 급여를 0으로 만드는 프로시저다.

create or replace procedure usp_update_emp
(vempno emp.empno%TYPE)
is
BEGIN
    update emp
    set sal = 0
    where empno = vempno;
END;

실행

exec usp_update_emp(7788);

SELECT 결과를 출력하는 프로시저

데이터를 조회해서 화면에 출력하는 프로시저도 만들 수 있다. DBMS_OUTPUT.put_line을 사용하면 오라클 콘솔에 결과를 출력할 수 있다. 이는 주로 테스트나 디버깅 용도로 사용된다.

create or replace procedure usp_getemplist
(vempno emp.empno%TYPE)
is
  vname emp.ename%TYPE;
  vsal  emp.sal%TYPE;
BEGIN
    select ename, sal
      into vname, vsal
    from emp
    where empno=vempno;
    
    DBMS_OUTPUT.put_line('이름은 : ' || vname);
    DBMS_OUTPUT.put_line('급여는 : ' || vsal);
END;

실행

exec usp_getemplist(7902);

OUTPUT 파라미터

OUTPUT 파라미터는 프로시저 실행 결과를 외부로 반환할 때 사용한다. 함수처럼 RETURN 문을 사용하지 않고도 여러 개의 값을 반환할 수 있다는 점이 특징이다.

파라미터 종류

INPUT 파라미터 (IN): 사용 시 반드시 입력 (생략 시 default). 프로시저로 값을 전달하는 용도로만 사용되며, 프로시저 내부에서 값을 변경해도 외부에는 영향을 주지 않는다.

OUTPUT 파라미터 (OUT): 입력값을 받지 않고, 결과를 외부로 반환한다. 프로시저 실행 후 처리된 결과를 호출한 쪽으로 전달하는 용도다. 여러 개의 OUT 파라미터를 선언하면 여러 값을 동시에 반환할 수 있다.

OUTPUT 파라미터 사용 예제

create or replace procedure app_get_emplist
(
  vempno IN emp.empno%TYPE,
  vename OUT emp.ename%TYPE,
  vsal   OUT emp.sal%TYPE
)
is
BEGIN
    select ename, sal
      into vename, vsal
    from emp
    where empno=vempno;
END;

오라클에서 실행 테스트

DECLARE
  out_ename emp.ename%TYPE;
  out_sal   emp.sal%TYPE;
BEGIN
   app_get_emplist(7902, out_ename, out_sal);
   DBMS_OUTPUT.put_line('출력값 : ' || out_ename || '-' || out_sal);
END;

SYS_REFCURSOR (자바 연동용)

자바와 같은 애플리케이션에서 프로시저 결과를 받으려면 SYS_REFCURSOR 타입을 사용해야 한다. 일반 OUTPUT 파라미터는 단일 값이나 몇 개의 값만 반환할 수 있지만, SYS_REFCURSOR는 여러 행의 결과셋을 반환할 수 있어서 실무에서 매우 자주 사용된다. 자바의 ResultSet과 유사한 개념이라고 보면 된다.

CREATE OR REPLACE PROCEDURE usp_EmpList
(
  p_sal IN number,
  p_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
     OPEN p_cursor
     FOR SELECT empno, ename, sal FROM EMP WHERE sal > p_sal;
END;

오라클에서 테스트

var out_cursor REFCURSOR
exec usp_EmpList(2000, :out_cursor)
print out_cursor;

메시지 반환 프로시저

실무에서는 프로시저 실행이 성공했는지, 실패했다면 어떤 에러가 발생했는지를 알아야 한다. 이를 위해 메시지를 반환하는 패턴을 자주 사용한다.

테이블 생성 및 제약조건

create table usp_emp
as
    select * from emp;

alter table usp_emp
add constraint pk_usp_emp_empno primary key(empno);

INSERT와 예외 처리

CREATE OR REPLACE PROCEDURE usp_insert_emp
(
 vempno IN emp.empno%TYPE,
 vename IN emp.ename%TYPE,
 vjob   IN emp.job%TYPE,
 p_outmsg OUT VARCHAR2
)
IS
BEGIN
    INSERT INTO USP_EMP(empno, ename, job) VALUES(vempno, vename, vjob);
    COMMIT;
    p_outmsg := 'success';
    
EXCEPTION 
    WHEN OTHERS THEN
        p_outmsg := SQLERRM;
        ROLLBACK;
END;

실행

DECLARE
  out_msg varchar2(200);
BEGIN
   usp_insert_emp(2000,'홍길동','IT', out_msg);
   DBMS_OUTPUT.put_line('출력값 : ' || out_msg);
END;

첫 실행: 출력값 : success
두 번째 실행: 출력값 : ORA-00001: unique constraint violated (PRIMARY KEY 중복)

급여 계산 프로시저 (실무 예제)

실무에서 프로시저가 가장 많이 활용되는 케이스 중 하나가 바로 배치 작업이다. 예를 들어 매월 급여를 계산해서 결과 테이블에 저장하는 작업은 프로시저로 구현하기에 적합하다. 커서를 활용해서 전체 직원을 순회하면서 직종별로 다른 급여 계산 로직을 적용할 수 있다.

급여 결과 테이블 생성

create table salary_result(
    empno number,
    ename varchar2(50),
    job_type varchar2(20),
    pay number,
    paid_date date default sysdate
);

급여 계산 및 저장 프로시저

create or replace procedure proc_calculate_salary
is
   cursor sal_cursor is  
   select empno, ename, job_type, monthly, hours, hourly, meal 
   from salary_info;
   
   v_empno salary_info.empno%TYPE;
   v_ename salary_info.ename%TYPE;
   v_job_type salary_info.job_type%TYPE;
   v_monthly salary_info.monthly%TYPE;
   v_hours salary_info.hours%TYPE;
   v_hourly salary_info.hourly%TYPE;
   v_meal salary_info.meal%TYPE;
   v_pay  number;
begin
    open sal_cursor;
    
    loop
        fetch sal_cursor into v_empno, v_ename, v_job_type, v_monthly, v_hours, v_hourly, v_meal;
        exit when sal_cursor%NOTFOUND;
        
        -- 직종별 급여 계산
        if v_job_type = '정규직' then
             v_pay := nvl(v_monthly,0);
        elsif v_job_type = '시간직' then
             v_pay := nvl(v_hours,0) * nvl(v_hourly,0);
        elsif v_job_type = '일용직' then
             v_pay := nvl(v_hourly,0) + nvl(v_meal,0);
        else 
             v_pay := 0;
        end if;
        
        -- 급여 결과 테이블에 insert
        insert into salary_result(empno, ename, job_type, pay)
        values(v_empno, v_ename, v_job_type, v_pay);
    end loop;
    
    commit;
end;

실행 방법

BEGIN
  proc_calculate_salary;
END;

이 프로시저는 매월 마지막 주 금요일 12시에 자동 실행되도록 스케줄링할 수 있다. 오라클의 DBMS_SCHEDULER를 사용하면 이런 정기 작업을 자동화할 수 있어서, 한 번만 설정해두면 매월 자동으로 급여가 계산되고 저장된다.

FUNCTION (사용자 정의 함수)

함수의 개념

오라클에서 제공하는 to_char(), sum() 등의 함수 외에 사용자가 직접 필요한 함수를 만들어 사용할 수 있다. 사용방법은 다른 함수 사용법과 동일하다.

함수의 가장 큰 특징은 SELECT 문 안에서 직접 사용할 수 있다는 점이다. 프로시저는 단독으로 실행해야 하지만, 함수는 일반 오라클 내장 함수처럼 쿼리문 안에서 자연스럽게 호출할 수 있다. 이를 통해 복잡한 계산 로직이나 변환 작업을 재사용 가능한 함수로 만들어두면, 여러 쿼리에서 쉽게 활용할 수 있다.

기본 함수 생성 (최대 급여 구하기)

create or replace function f_max_sal
(s_deptno emp.deptno%TYPE)
return number
is
  max_sal emp.sal%TYPE;
BEGIN
    select max(sal)
        into max_sal
    from emp
    where deptno = s_deptno;
    
    return max_sal;
END;

사용 예제

-- 특정 부서의 최대 급여를 받는 사원 조회
select * from emp where sal = f_max_sal(10);

-- 전체 최대 급여와 30번 부서 최대 급여 비교
select max(sal), f_max_sal(30) from emp;

문자열 반환 함수 (이름 뒤에 '님' 붙이기)

create or replace function f_callname
(vempno emp.empno%TYPE)
return varchar2
is
  v_name emp.ename%TYPE;
BEGIN
    select ename || '님'
      into v_name
    from emp
    where empno=vempno;
    
    return v_name;
END;

사용 예제

-- 단독 사용
select f_callname(7788) from dual;

-- SELECT 문에서 활용
select empno, ename, f_callname(7788), sal
from emp
where empno=7788;

-- 각 행마다 적용
select empno, ename, f_callname(empno), sal
from emp
where empno=7788;

실습: 부서명 반환 함수

사번을 입력받아 해당 사원의 부서이름을 리턴하는 함수를 만든다. 이런 함수는 조인을 사용하지 않고도 부서명을 간편하게 조회할 수 있게 해준다.

create or replace function f_get_dname
(vempno emp.empno%TYPE)
return varchar2
is
    v_dname dept.dname%TYPE;
BEGIN
    select dname
      into v_dname
    from dept
    where deptno = (select deptno from emp where empno=vempno);
    
    return v_dname;
END;

사용 예제

select empno, ename, deptno, f_get_dname(empno)
from emp 
where empno=7788;

프로시저와 함수의 차이점

구분	PROCEDURE	FUNCTION
반환값	OUT 파라미터로 여러 값 반환 가능	RETURN으로 하나의 값 반환
호출 방법	단독 실행 가능 (EXEC, BEGIN-END)	SELECT 문 내에서 호출 가능
용도	DML 작업, 복잡한 비즈니스 로직	계산, 변환 등 값을 반환하는 작업
SQL 사용	SELECT 문에서 직접 사용 불가	SELECT 문에서 직접 사용 가능

프로시저는 주로 데이터를 변경하거나 복잡한 처리 로직을 수행할 때 사용하고, 함수는 계산이나 조회 결과를 반환할 때 사용한다고 구분하면 된다. 프로시저는 작업의 수행 자체가 목적이고, 함수는 값을 얻는 것이 목적이라는 차이가 있다.

커서 활용 과제 아이디어

커서를 실무나 프로젝트에 반영한다면 다음과 같은 상황에 사용하면 좋다:

1조: 통신/금융

핸드폰 요금 처리, 카드 요금 계산에 커서를 활용할 수 있다. 각 고객의 사용 내역을 커서로 순회하면서 요금을 계산하고, 할인 정책을 적용하며, 최종 청구 금액을 산출하는 배치 작업을 구현할 수 있다. 예를 들어 통화 시간, 데이터 사용량, SMS 발송 건수 등을 집계해서 정해진 요금제에 따라 청구서를 생성하는 작업이 가능하다.

2조: 배달 서비스

배달의민족 같은 경우 고객의 구매 이력을 취합하여 고객 등급 갱신 작업을 할 수 있다. 지난 3개월간 주문 건수와 총 구매 금액을 계산해서, 일반/실버/골드/VIP 등의 등급을 자동으로 부여하는 프로시저를 만들 수 있다. 이런 작업은 매일 새벽에 실행되도록 스케줄링하면 효율적이다.

3조: 회원 관리

회원의 접속 정보를 커서로 확인하여 별도의 처리를 수행할 수 있다. 예를 들어 최근 6개월간 접속 기록이 없는 회원을 자동으로 휴면 회원으로 전환하거나, 월별 접속 통계를 생성해서 리포트 테이블에 저장하는 작업이 가능하다. 또한 회원 가입일로부터 1년이 지난 회원에게 기념 포인트를 지급하는 배치 작업도 구현할 수 있다.

4조: 쇼핑몰

할인율 적용 테이블과 상품 테이블을 연동해서 자동으로 할인가를 계산할 수 있다. 매주 금요일 세일 기간에는 특정 카테고리의 상품들에 대해 정해진 할인율을 자동으로 적용하고, 세일이 끝나면 원래 가격으로 복구하는 프로시저를 만들 수 있다. 또한 재고가 일정 수량 이하로 떨어지면 자동으로 발주 요청 데이터를 생성하는 작업도 가능하다.

기타 활용 예시

매일 새벽 3시에 사용자의 리포지토리가 최신 커밋으로 되어있는지 체크하는 작업은 Spring Batch와 연동해서 구현할 수 있다. 매일 정오마다 사용자의 문제 풀이 건수 및 점수를 집계하여 랭킹을 갱신하는 작업도 프로시저로 효율적으로 처리할 수 있다.

매일 결제 금액을 정산하는 작업에서는 포인트 결제와 실제 카드 결제를 분리해서 집계하고, 순이익을 계산하기 위한 각종 수수료와 비용을 차감하는 복잡한 로직을 프로시저로 구현하면 유지보수가 쉬워진다.

PL/SQL 트랜잭션 및 예외 처리

사용자 정의 예외 생성

DB에서 에러가 발생하면 웹에서 에러 페이지를 만나게 되는데, 사용자 정의 예외 에러로 DB에서 미리 처리할 수 있다. 예외 번호는 -20000번대를 사용한다.

오라클에서는 -20000부터 -20999까지의 에러 번호를 사용자 정의 예외용으로 예약해두었다. 이를 활용하면 비즈니스 로직에 맞는 의미 있는 에러 메시지를 만들 수 있고, 애플리케이션에서도 에러 번호를 보고 어떤 문제인지 쉽게 파악할 수 있다.

DECLARE
    v_ename emp.ename%TYPE := '&p_ename';
    v_err_code NUMBER;
    v_err_msg VARCHAR2(255);
BEGIN
    DELETE emp WHERE ename = v_ename;
    
    IF SQL%NOTFOUND THEN
        -- 사용자가 예외 만들기 (강제 예외 던지기)
        RAISE_APPLICATION_ERROR(-20002,'my no data found');
    END IF;
    
EXCEPTION 
    WHEN OTHERS THEN
        ROLLBACK;
        v_err_code := SQLCODE;
        v_err_msg := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));
        DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg);
END;

실행 결과

에러 번호 : -20002
에러 내용 : ORA-20002: my no data found

객체 영속화 개념

영속적 저장의 필요성

지금까지 만들었던 작업들은 영속적으로 저장되지 않았다. CREATE 키워드가 있어야 영속적으로 저장된다.

영속화란 데이터나 객체를 메모리가 아닌 디스크에 저장해서, 프로그램이 종료되거나 세션이 끊어져도 계속 유지되도록 하는 것을 의미한다. 일반 PL/SQL 블록은 실행이 끝나면 사라지지만, CREATE로 만든 객체는 데이터베이스에 저장되어 언제든 다시 사용할 수 있다.

영속적 객체 예시

• CREATE TABLE
• CREATE VIEW
• CREATE PROCEDURE
• CREATE FUNCTION

프로시저 객체화의 장점

내가 만든 커서를 영속적으로 저장(객체화)해두면, 다음번에 코딩하지 않고 불러서 사용할 수 있다. 한 번 작성한 프로시저는 계속해서 재사용할 수 있고, 여러 개발자가 공유해서 사용할 수도 있다. 또한 비즈니스 로직이 변경되면 프로시저만 수정하면 되므로, 애플리케이션 코드를 건드리지 않아도 되는 장점이 있다.

프로시저 명명 규칙

Oracle vs MS-SQL

Oracle: Subprogram (Procedure)
MS-SQL: Procedure

오라클에서는 프로시저를 서브프로그램의 일종으로 분류하지만, MS-SQL에서는 그냥 프로시저라고 부른다. 개념은 거의 동일하지만 문법에서 약간의 차이가 있다.

명명 관례

• sp_로 시작: 시스템 프로시저. 오라클이나 MS-SQL에서 기본적으로 제공하는 프로시저들이 주로 이런 prefix를 사용한다.
• usp_로 시작: 사용자 정의 프로시저 (User Stored Procedure). 개발자가 직접 만든 프로시저는 이렇게 명명하는 것이 관례다.

이런 명명 규칙을 따르면 시스템 프로시저와 사용자 프로시저를 쉽게 구분할 수 있고, 프로시저 목록을 볼 때도 한눈에 파악하기 쉽다.

MS 개발 환경에서의 프로시저

MS 계열 개발 특징

MS 계열 개발(C#, VB.NET - MS-SQL)에서는 90% 이상을 프로시저로 만들라고 권장한다. 마이크로소프트 진영에서는 비즈니스 로직의 대부분을 데이터베이스 레벨에서 처리하는 것을 선호하는 경향이 있다. 이는 .NET 프레임워크와 MS-SQL의 통합성이 뛰어나고, 프로시저를 통한 성능 최적화가 잘 되어 있기 때문이다.

Java 개발 환경에서의 프로시저

Java는 활용할 수 있는 프레임워크(MyBatis 등)가 있어서 선택적으로 사용한다.

MyBatis: 프로시저 사용 가능. MyBatis는 프로시저 호출을 잘 지원하며, XML이나 어노테이션을 통해 쉽게 연동할 수 있다.

JPA: 프로시저 기능 없음. JPA는 객체 지향적인 접근 방식을 지향하기 때문에 프로시저와는 맞지 않는 부분이 있다. 하지만 네이티브 쿼리를 사용하면 우회적으로 프로시저를 호출할 수는 있다.

하지만 프로시저의 장점이 너무 크기 때문에 적절히 활용하는 것이 좋다. 특히 대용량 배치 작업이나 복잡한 집계 작업은 프로시저로 처리하는 것이 훨씬 효율적이다.

프로시저 vs 일반 쿼리

주체의 차이

• 일반 쿼리: SELECT 구문이 주체. 애플리케이션에서 SQL을 직접 작성하고 전송한다.
• 프로시저: DB가 주체. 데이터베이스 내부에서 로직이 실행되므로 애플리케이션은 단순히 프로시저 이름만 호출하면 된다.

쿼리 조인이 많을 경우
네트워크 트래픽이 크다. 여러 테이블을 조인하는 복잡한 쿼리는 길이가 길어지고, 매번 이를 전송하면 네트워크 부하가 증가한다. 프로시저를 사용하면 복잡한 쿼리를 데이터베이스에 저장해두고 이름만 호출하면 되므로 트래픽을 크게 줄일 수 있다.

보안 측면
쿼리 구문을 가로채도 해커는 프로시저 내용을 알아볼 수 없다. 네트워크 패킷을 스니핑해도 프로시저 이름만 보이고 실제 SQL 로직은 노출되지 않는다. 또한 테이블에 대한 직접 권한 없이 프로시저 실행 권한만 주면 되므로, 권한 관리 측면에서도 유리하다.

프로시저의 장단점 정리

장점

1. 네트워크 트래픽 감소 (시간 단축)

기존 방식:
APP(emp.java > select ... 긴 쿼리문) -> 네트워크 -> DB연결 -> select... -> DB

프로시저 방식:
APP(emp.java > usp_emplist) -> 네트워크 -> DB연결 -> usp_emplist -> DB

100줄짜리 복잡한 쿼리문을 매번 전송하는 것과 프로시저 이름 하나만 보내는 것의 차이는 네트워크 속도가 느린 환경에서 특히 크게 체감된다. 원격지에서 접속하거나 모바일 환경에서는 이러한 차이가 사용자 경험에 직접적인 영향을 미친다.

2. 보안 강화
네트워크 상에서 쿼리 내용이 노출되지 않는다. 해커가 네트워크 패킷을 가로채더라도 프로시저 이름만 보일 뿐, 실제 테이블 구조나 비즈니스 로직을 파악할 수 없다. 프로시저를 통한 SQL Injection 등의 공격에 안전하다. 또한 민감한 데이터 처리 로직을 프로시저 내부에 캡슐화하면, 애플리케이션 코드가 유출되더라도 핵심 로직은 보호할 수 있다.

3. 재사용성
한 번 작성한 로직을 여러 곳에서 재사용할 수 있다. 여러 애플리케이션이나 여러 모듈에서 동일한 비즈니스 로직이 필요할 때, 프로시저를 호출하기만 하면 된다. 중복 코드를 제거하고 일관성을 유지할 수 있다.

4. 유지보수 용이
비즈니스 로직 변경 시 프로시저만 수정하면 된다. 예를 들어 급여 계산 방식이 바뀌었을 때, 애플리케이션 코드를 여러 곳에서 수정할 필요 없이 프로시저 하나만 수정하면 모든 곳에 적용된다. 애플리케이션 재배포가 필요 없다는 것도 큰 장점이다. 특히 대규모 시스템에서는 애플리케이션 재배포가 쉽지 않은 경우가 많은데, 프로시저만 수정하면 바로 적용할 수 있어서 매우 편리하다.

단점

1. 파라미터 처리
INPUT, OUTPUT 파라미터 사용이 다소 복잡하다. 특히 여러 개의 OUTPUT 파라미터를 사용하거나, IN OUT 파라미터를 사용할 때는 코드가 복잡해질 수 있다. 초보 개발자에게는 진입 장벽이 다소 높게 느껴질 수 있다.

2. 리턴 처리
여러 건의 데이터를 반환하려면 SYS_REFCURSOR 등을 사용해야 한다. 단순 SELECT 쿼리에 비해 코드가 복잡하고, 커서를 사용하는 방법을 이해해야 한다. 특히 자바 같은 애플리케이션에서 커서를 받아서 처리하는 부분이 처음에는 어렵게 느껴질 수 있다.

3. 디버깅
애플리케이션 레벨보다 디버깅이 어렵다. 프로시저 내부에서 에러가 발생하면 정확한 위치를 찾기가 쉽지 않고, 브레이크포인트를 걸어서 단계별로 확인하는 것도 일반 애플리케이션 코드보다 번거롭다. 오라클에서는 DBMS_OUTPUT이나 로그 테이블을 활용해서 디버깅하는 방법을 익혀야 한다.

4. 이식성
DB 종류가 바뀌면 프로시저를 다시 작성해야 한다. 오라클에서 작성한 프로시저를 MySQL이나 PostgreSQL로 옮기려면 문법 차이 때문에 상당 부분을 수정해야 한다. 멀티 DB를 지원해야 하는 상용 제품의 경우 이것이 큰 단점이 될 수 있다.

종합 정리

PL/SQL 기본 구조

DECLARE
    -- 변수 선언
BEGIN
    -- 실행부
EXCEPTION
    -- 예외 처리
END;

PL/SQL은 SQL을 절차적으로 확장한 언어다. DECLARE 섹션에서 필요한 변수를 선언하고, BEGIN~END 사이에 실행할 로직을 작성하며, EXCEPTION 섹션에서 에러 처리를 한다. 이 구조는 프로시저, 함수, 트리거 등 모든 PL/SQL 객체에 공통적으로 적용된다.

핵심 문법 요약

분류	문법	설명
변수 할당	:=	v_name := 'kglim' - 변수에 값을 대입할 때 사용
조건문	IF-ELSIF-ELSE	IF (조건) THEN ... END IF; - 조건에 따라 다른 로직 수행
CASE문	CASE WHEN	조건에 따른 값 할당, 여러 조건을 깔끔하게 처리
반복문	LOOP, WHILE, FOR	다양한 반복 처리, 상황에 맞게 선택 사용
커서	CURSOR ... IS	행 단위 데이터 처리, 여러 행을 순차적으로 처리할 때 필수
프로시저	CREATE PROCEDURE	쿼리 모듈화, 비즈니스 로직 캡슐화
함수	CREATE FUNCTION	값 반환, SELECT 문에서 직접 사용 가능

타입 지정 방법

v_empno number(10);              -- 일반 타입: 직접 데이터 타입 지정
v_empno emp.empno%TYPE;          -- 컬럼 타입 참조: 테이블 컬럼과 동일한 타입 자동 적용
v_row emp%ROWTYPE;               -- 전체 행 타입 참조: 테이블 전체 구조 참조

%TYPE과 %ROWTYPE을 사용하면 테이블 구조가 변경되어도 프로시저를 수정할 필요가 없다. 예를 들어 empno 컬럼의 크기가 number(4)에서 number(10)으로 변경되어도, %TYPE을 사용한 변수는 자동으로 새 타입을 따라간다.

커서의 핵심

DECLARE
    CURSOR c1 IS SELECT ...;
BEGIN
    FOR rec IN c1 LOOP
        -- 행 단위 처리
        EXIT WHEN c1%NOTFOUND;
    END LOOP;
    CLOSE c1;
END;

커서는 여러 행의 데이터를 순차적으로 처리할 때 사용한다. FOR 루프와 함께 사용하면 OPEN, FETCH, CLOSE를 자동으로 처리해주므로 코드가 간결해진다. 배치 작업이나 대량 데이터 처리에서 필수적인 기능이다.

프로시저와 함수의 선택

프로시저를 사용할 때
DML 작업 (INSERT, UPDATE, DELETE)을 수행할 때 프로시저를 사용한다. 데이터를 변경하는 작업은 프로시저의 주 용도다. 복잡한 비즈니스 로직을 구현할 때도 프로시저가 적합하다. 여러 단계의 처리가 필요하거나, 조건에 따라 다른 작업을 수행해야 할 때 프로시저로 구현하면 로직을 체계적으로 관리할 수 있다. 여러 값을 반환해야 할 때도 프로시저가 유리하다. OUT 파라미터를 여러 개 선언하면 한 번의 호출로 여러 결과를 받을 수 있다.

함수를 사용할 때
SELECT 문에서 직접 사용해야 할 때는 함수를 사용한다. 조회 쿼리의 일부로 계산이나 변환 작업이 필요할 때 함수가 적합하다. 단일 값을 반환할 때도 함수가 적절하다. RETURN 문으로 하나의 값을 깔끔하게 반환할 수 있다. 계산, 변환 작업에도 함수가 적합하다. 예를 들어 금액을 포맷팅하거나, 날짜를 특정 형식으로 변환하거나, 복잡한 수식을 계산하는 등의 작업은 함수로 만들어두면 여러 곳에서 재사용하기 편리하다.

실무 활용 팁

1. 배치 작업: 커서 + 프로시저로 대량 데이터 처리
매일 밤 실행되는 집계 작업이나 정산 작업은 프로시저로 구현하는 것이 일반적이다. 커서를 사용해서 전체 데이터를 순회하면서 필요한 계산을 수행하고 결과를 저장한다. 예를 들어 일별 매출 집계, 회원 등급 갱신, 포인트 소멸 처리 등이 여기에 해당한다.

2. 자동화: 스케줄러와 연동하여 정기 작업 수행
오라클의 DBMS_SCHEDULER나 크론탭을 사용해서 프로시저를 정기적으로 실행할 수 있다. 매일, 매주, 매월 특정 시간에 자동으로 실행되도록 설정하면 사람이 직접 개입하지 않아도 된다. 야간 배치 작업, 월말 정산, 주간 리포트 생성 등에 활용한다.

3. 성능 최적화: 반복적인 복잡한 쿼리는 프로시저로 작성
동일한 복잡한 쿼리를 여러 번 실행해야 하는 경우, 프로시저로 만들어두면 파싱 시간을 절약할 수 있다. 오라클은 프로시저를 컴파일해서 저장하므로 실행 계획이 캐시되어 성능이 향상된다.

4. 보안: 민감한 로직은 프로시저로 캡슐화
급여 계산, 개인정보 처리, 권한 검증 등 민감한 로직은 프로시저 내부에 숨겨두고, 애플리케이션에서는 프로시저만 호출하도록 한다. 이렇게 하면 애플리케이션 코드가 유출되어도 핵심 로직은 보호할 수 있다.

5. 트랜잭션 관리: COMMIT, ROLLBACK을 프로시저 내에서 제어
복잡한 트랜잭션 처리는 프로시저 내부에서 관리하는 것이 안전하다. 여러 단계의 DML 작업을 수행하다가 중간에 에러가 발생하면 ROLLBACK으로 전체를 취소하고, 모두 성공했을 때만 COMMIT으로 확정한다. 예외 처리 섹션에서 ROLLBACK을 하도록 구현하면 데이터 일관성을 보장할 수 있다.

다음 학습 내용

TRIGGER (트리거): 특정 이벤트 발생 시 자동 실행
트리거는 INSERT, UPDATE, DELETE 같은 DML 작업이 발생했을 때 자동으로 실행되는 특수한 프로시저다. 예를 들어 급여가 변경되면 자동으로 이력 테이블에 기록하거나, 재고가 변경되면 로그를 남기는 등의 작업을 트리거로 구현할 수 있다. 데이터 무결성 유지와 감사 추적에 유용하다.

PACKAGE: 관련된 프로시저와 함수를 그룹화
패키지는 연관된 프로시저와 함수를 하나의 논리적 단위로 묶는 기능이다. 예를 들어 직원 관리와 관련된 모든 프로시저와 함수를 EMP_PKG라는 패키지로 만들 수 있다. 패키지를 사용하면 네임스페이스를 관리할 수 있고, 전역 변수를 공유할 수도 있어서 대규모 프로젝트에서 유용하다.

동적 SQL: 실행 시점에 SQL 생성
EXECUTE IMMEDIATE를 사용하면 런타임에 SQL 문을 동적으로 생성해서 실행할 수 있다. 테이블 이름이나 컬럼 이름을 변수로 받아서 처리해야 할 때, 또는 조건에 따라 완전히 다른 쿼리를 실행해야 할 때 동적 SQL이 필요하다. 단, SQL Injection에 취약할 수 있으므로 주의해서 사용해야 한다.

고급 커서 기법: 파라미터화된 커서, 커서 변수
커서에 파라미터를 전달해서 조건을 동적으로 변경할 수 있다. 또한 SYS_REFCURSOR 타입의 커서 변수를 사용하면 프로시저 간에 커서를 전달하거나, 애플리케이션으로 결과셋을 반환할 수 있다. 이는 자바 같은 외부 프로그램과 연동할 때 필수적인 기술이다.

BULK COLLECT: 대량 데이터 처리 최적화
일반 커서는 한 번에 한 행씩 처리하지만, BULK COLLECT를 사용하면 여러 행을 한 번에 배열로 가져올 수 있다. 이렇게 하면 컨텍스트 스위칭이 줄어들어서 성능이 크게 향상된다. 수십만 건 이상의 대량 데이터를 처리할 때는 BULK COLLECT와 FORALL을 함께 사용하는 것이 일반적이다.

예외 처리 고급: 사용자 정의 예외 활용
비즈니스 로직에서 발생하는 특정 상황을 예외로 정의해서 처리할 수 있다. 예를 들어 "재고 부족"이나 "권한 없음" 같은 상황을 별도의 예외로 선언하고, 해당 상황이 발생하면 RAISE로 예외를 던질 수 있다. 이렇게 하면 에러 처리 로직이 명확해지고 유지보수가 쉬워진다.

스케줄링: DBMS_SCHEDULER를 이용한 자동화
DBMS_SCHEDULER 패키지를 사용하면 프로시저를 정해진 시간에 자동으로 실행할 수 있다. 매일 새벽 3시, 매주 월요일 오전 9시, 매월 말일 등 다양한 스케줄을 설정할 수 있다. 또한 선행 작업이 완료된 후에 실행되도록 하는 체인 스케줄도 가능하다. 배치 작업 자동화에 필수적인 기능이다.

부록: 주요 예외 처리

예외명	발생 상황
NO_DATA_FOUND	SELECT 결과가 없을 때 - SELECT INTO 구문에서 조회 결과가 0건일 때 발생
TOO_MANY_ROWS	SELECT 결과가 2개 이상일 때 - SELECT INTO는 단일 행만 받을 수 있는데 여러 행이 반환될 때 발생
DUP_VAL_ON_INDEX	UNIQUE/PK 제약 위반 - 중복된 값을 INSERT하거나 UPDATE할 때 발생
INVALID_CURSOR	잘못된 커서 작업 - OPEN되지 않은 커서를 FETCH하거나, 이미 CLOSE된 커서를 사용할 때 발생
ZERO_DIVIDE	0으로 나누기 - 나눗셈 연산에서 분모가 0일 때 발생
OTHERS	모든 예외 - 위에 명시되지 않은 모든 예외를 포괄적으로 처리

예외 정보 확인 함수

• SQLCODE: 에러 코드를 반환한다. 정상 실행 시 0, 예외 발생 시 음수를 반환한다.
• SQLERRM: 에러 메시지를 반환한다. 어떤 에러가 발생했는지 설명하는 텍스트를 제공한다.

예외 처리는 안정적인 프로시저를 만드는 데 필수적이다. 모든 프로시저에는 EXCEPTION 섹션을 두어서 예상치 못한 에러에 대비해야 한다. 특히 DML 작업을 수행하는 프로시저는 에러 발생 시 ROLLBACK을 해서 데이터 일관성을 유지해야 한다.

실무 프로시저 작성 가이드

표준 템플릿

실무에서 프로시저를 작성할 때는 일관된 구조를 따르는 것이 중요하다. 다음은 권장되는 표준 템플릿이다.

CREATE OR REPLACE PROCEDURE usp_procedure_name
(
    -- INPUT 파라미터
    p_param1 IN table.column%TYPE,
    p_param2 IN VARCHAR2,
    -- OUTPUT 파라미터
    p_result OUT VARCHAR2,
    p_error_code OUT NUMBER
)
IS
    -- 변수 선언
    v_count NUMBER := 0;
    v_temp VARCHAR2(100);
    
    -- 커서 선언 (필요시)
    CURSOR cur_data IS
        SELECT column1, column2 FROM table_name WHERE condition;
    
BEGIN
    -- 초기화
    p_error_code := 0;
    p_result := 'SUCCESS';
    
    -- 비즈니스 로직
    -- 1. 유효성 검증
    -- 2. 데이터 처리
    -- 3. 결과 저장
    
    COMMIT;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_error_code := -20001;
        p_result := 'NO_DATA_FOUND: ' || SQLERRM;
        ROLLBACK;
        
    WHEN OTHERS THEN
        p_error_code := SQLCODE;
        p_result := 'ERROR: ' || SQLERRM;
        ROLLBACK;
END;
/

프로시저 작성 원칙

1. 명확한 네이밍
프로시저 이름은 그 기능을 명확히 표현해야 한다. usp_insert_employee, usp_calculate_salary, usp_update_inventory 같은 식으로 동사+명사 형태로 작성하면 이해하기 쉽다. 팀 내에서 네이밍 규칙을 정해서 일관성을 유지하는 것이 중요하다.

2. 파라미터 prefix 사용
파라미터는 p, 변수는 v, 커서는 cur_ 같은 접두사를 붙여서 구분하면 코드 가독성이 높아진다. 이렇게 하면 코드를 읽을 때 어떤 것이 파라미터이고 어떤 것이 지역 변수인지 한눈에 파악할 수 있다.

3. 주석 작성
프로시저 상단에는 작성자, 작성일, 목적, 파라미터 설명, 수정 이력 등을 주석으로 남긴다. 복잡한 로직 부분에도 주석을 달아서 다른 개발자가 이해하기 쉽게 만든다.

/*
 * 프로시저명: usp_calculate_monthly_salary
 * 작성자: 홍길동
 * 작성일: 2024-01-15
 * 목적: 월별 급여 계산 및 결과 저장
 * 파라미터:
 *   - p_year_month: 대상 년월 (YYYYMM)
 *   - p_result: 처리 결과 메시지
 * 수정이력:
 *   2024-02-01 김철수 - 시간외수당 계산 로직 추가
 */

4. 트랜잭션 관리
DML 작업 후에는 반드시 COMMIT 또는 ROLLBACK을 명시해야 한다. 예외가 발생하면 EXCEPTION 섹션에서 ROLLBACK을 수행해서 데이터 일관성을 보장한다. 특히 여러 테이블을 동시에 수정하는 경우, 하나라도 실패하면 전체를 되돌려야 한다.

5. 에러 처리 전략
모든 예외를 WHEN OTHERS로만 처리하지 말고, 발생 가능한 특정 예외들을 먼저 처리한 후 마지막에 OTHERS를 두는 것이 좋다. 각 예외마다 적절한 에러 코드와 메시지를 반환하면 디버깅이 쉬워진다.

성능 최적화 팁

1. 불필요한 커밋 피하기
루프 안에서 매번 COMMIT을 하면 성능이 크게 저하된다. 가능하면 모든 처리가 끝난 후 한 번만 COMMIT하는 것이 좋다. 단, 대량 데이터 처리 시에는 일정 건수마다 중간 COMMIT을 해서 트랜잭션 크기를 조절할 필요가 있다.

-- 나쁜 예
FOR rec IN cur_data LOOP
    INSERT INTO table VALUES (...);
    COMMIT;  -- 매번 COMMIT은 느리다
END LOOP;

-- 좋은 예
FOR rec IN cur_data LOOP
    INSERT INTO table VALUES (...);
END LOOP;
COMMIT;  -- 한 번만 COMMIT

2. 커서 FOR LOOP 활용
명시적으로 OPEN, FETCH, CLOSE를 하는 것보다 FOR LOOP를 사용하면 코드가 간결하고 에러 가능성도 줄어든다. 오라클이 자동으로 커서 관리를 해주기 때문에 개발자가 신경 쓸 부분이 줄어든다.

3. BULK COLLECT 고려
대량 데이터를 처리할 때는 BULK COLLECT와 FORALL을 사용하면 성능이 10배 이상 향상될 수 있다. 단, 메모리 사용량이 증가하므로 LIMIT 절을 사용해서 한 번에 가져오는 행 수를 제한하는 것이 안전하다.

-- 일반 커서 방식
FOR rec IN cur_data LOOP
    UPDATE table SET col = rec.val WHERE id = rec.id;
END LOOP;

-- BULK COLLECT 방식 (훨씬 빠름)
DECLARE
    TYPE id_array IS TABLE OF table.id%TYPE;
    TYPE val_array IS TABLE OF table.col%TYPE;
    v_ids id_array;
    v_vals val_array;
BEGIN
    SELECT id, val BULK COLLECT INTO v_ids, v_vals FROM table;
    FORALL i IN 1..v_ids.COUNT
        UPDATE table SET col = v_vals(i) WHERE id = v_ids(i);
END;

4. 불필요한 SELECT 제거
같은 데이터를 여러 번 조회하지 않도록 변수에 저장해서 재사용한다. 특히 루프 안에서 반복적으로 같은 쿼리를 실행하는 것은 피해야 한다.

5. 인덱스 활용
프로시저에서 자주 조회하는 컬럼에는 인덱스를 생성한다. WHERE 절에 사용되는 컬럼, JOIN 조건에 사용되는 컬럼은 인덱스가 있어야 성능이 좋다. 실행 계획을 확인해서 Full Table Scan이 발생하는지 체크한다.

디버깅 방법

1. DBMS_OUTPUT 활용
개발 단계에서는 DBMS_OUTPUT.PUT_LINE으로 중간 결과를 출력해서 로직이 제대로 동작하는지 확인한다. 단, 운영 환경에서는 주석 처리하거나 제거해야 성능에 영향을 주지 않는다.

BEGIN
    DBMS_OUTPUT.PUT_LINE('시작: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    -- 로직 수행
    DBMS_OUTPUT.PUT_LINE('종료: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE('처리 건수: ' || v_count);
END;

2. 로그 테이블 사용
운영 환경에서는 별도의 로그 테이블을 만들어서 프로시저 실행 이력을 기록한다. 시작 시간, 종료 시간, 처리 건수, 에러 메시지 등을 저장하면 문제 발생 시 추적이 용이하다.

CREATE TABLE procedure_log (
    log_id NUMBER,
    proc_name VARCHAR2(100),
    start_time DATE,
    end_time DATE,
    row_count NUMBER,
    status VARCHAR2(20),
    error_msg VARCHAR2(500)
);

-- 프로시저 내에서 로그 기록
INSERT INTO procedure_log VALUES (
    seq_log.NEXTVAL,
    'usp_calculate_salary',
    v_start_time,
    SYSDATE,
    v_count,
    'SUCCESS',
    NULL
);

3. 단계별 테스트
복잡한 프로시저는 한 번에 전체를 작성하지 말고, 단계별로 나눠서 테스트한다. 각 단계가 정상 동작하는 것을 확인한 후 다음 단계를 추가하는 방식으로 개발하면 문제가 발생했을 때 원인을 찾기 쉽다.

4. 예외 상황 테스트
정상적인 케이스뿐만 아니라 예외 상황도 반드시 테스트한다. 데이터가 없는 경우, 중복 데이터가 있는 경우, NULL 값이 들어오는 경우 등 다양한 시나리오를 검증해야 안정적인 프로시저가 된다.

보안 고려사항

1. SQL Injection 방지
동적 SQL을 사용할 때는 EXECUTE IMMEDIATE에 직접 문자열을 연결하지 말고, USING 절을 사용해서 파라미터를 바인딩한다. 이렇게 하면 SQL Injection 공격을 방지할 수 있다.

-- 위험한 방식 (SQL Injection 취약)
v_sql := 'SELECT * FROM emp WHERE empno = ' || p_empno;
EXECUTE IMMEDIATE v_sql;

-- 안전한 방식 (파라미터 바인딩)
v_sql := 'SELECT * FROM emp WHERE empno = :1';
EXECUTE IMMEDIATE v_sql INTO v_result USING p_empno;

2. 권한 관리
프로시저에는 EXECUTE 권한만 부여하고, 테이블에 대한 직접 접근 권한은 회수한다. 사용자는 프로시저를 통해서만 데이터를 조작할 수 있으므로 보안이 강화된다.

-- 테이블 직접 접근 권한 회수
REVOKE SELECT, INSERT, UPDATE, DELETE ON emp FROM user_app;

-- 프로시저 실행 권한만 부여
GRANT EXECUTE ON usp_manage_emp TO user_app;

3. 민감 정보 처리
주민번호, 비밀번호 같은 민감한 정보를 처리할 때는 로그에 남기지 않도록 주의한다. 디버깅용 출력문에도 민감 정보가 포함되지 않도록 한다.

유지보수 전략

1. 버전 관리
프로시저를 수정할 때는 주석에 수정 이력을 남기고, 가능하면 Git 같은 버전 관리 시스템에서 관리한다. 문제가 발생했을 때 이전 버전으로 롤백할 수 있어야 한다.

2. 의존성 관리
프로시저가 다른 프로시저나 함수를 호출하는 경우, 의존 관계를 문서화한다. 한 프로시저를 수정하면 영향받는 다른 프로시저들도 확인해야 한다.

3. 테스트 자동화
중요한 프로시저는 테스트 케이스를 만들어서 정기적으로 실행한다. 입력값과 예상 출력값을 정의해두고, 프로시저 수정 후 자동으로 테스트를 실행해서 회귀 버그를 방지한다.

4. 성능 모니터링
운영 환경에서 프로시저 실행 시간을 주기적으로 체크한다. 갑자기 실행 시간이 증가하면 데이터 증가로 인한 성능 저하일 수 있으므로, 인덱스 추가나 쿼리 튜닝을 검토해야 한다.

실전 예제: 전자상거래 주문 처리

실무에서 자주 마주치는 복잡한 비즈니스 로직을 프로시저로 구현하는 예제를 살펴보자. 전자상거래 사이트에서 주문이 들어왔을 때 처리하는 프로시저다.

CREATE OR REPLACE PROCEDURE usp_process_order
(
    p_customer_id IN NUMBER,
    p_product_id IN NUMBER,
    p_quantity IN NUMBER,
    p_use_point IN NUMBER DEFAULT 0,
    p_order_id OUT NUMBER,
    p_result OUT VARCHAR2
)
IS
    v_stock NUMBER;
    v_price NUMBER;
    v_customer_point NUMBER;
    v_total_amount NUMBER;
    v_final_amount NUMBER;
    
    e_insufficient_stock EXCEPTION;
    e_insufficient_point EXCEPTION;
    
BEGIN
    -- 1. 재고 확인
    SELECT stock, price 
      INTO v_stock, v_price
      FROM products
     WHERE product_id = p_product_id
       FOR UPDATE;  -- 동시성 제어를 위한 락
    
    IF v_stock < p_quantity THEN
        RAISE e_insufficient_stock;
    END IF;
    
    -- 2. 고객 포인트 확인
    SELECT point INTO v_customer_point
      FROM customers
     WHERE customer_id = p_customer_id
       FOR UPDATE;
    
    IF v_customer_point < p_use_point THEN
        RAISE e_insufficient_point;
    END IF;
    
    -- 3. 금액 계산
    v_total_amount := v_price * p_quantity;
    v_final_amount := v_total_amount - p_use_point;
    
    -- 4. 주문 생성
    INSERT INTO orders (order_id, customer_id, product_id, quantity, 
                        total_amount, used_point, final_amount, order_date)
    VALUES (seq_order.NEXTVAL, p_customer_id, p_product_id, p_quantity,
            v_total_amount, p_use_point, v_final_amount, SYSDATE)
    RETURNING order_id INTO p_order_id;
    
    -- 5. 재고 차감
    UPDATE products
       SET stock = stock - p_quantity
     WHERE product_id = p_product_id;
    
    -- 6. 포인트 차감
    UPDATE customers
       SET point = point - p_use_point
     WHERE customer_id = p_customer_id;
    
    -- 7. 포인트 적립 (구매 금액의 1%)
    UPDATE customers
       SET point = point + TRUNC(v_final_amount * 0.01)
     WHERE customer_id = p_customer_id;
    
    COMMIT;
    p_result := 'SUCCESS';
    
EXCEPTION
    WHEN e_insufficient_stock THEN
        ROLLBACK;
        p_result := 'ERROR: 재고가 부족합니다';
        
    WHEN e_insufficient_point THEN
        ROLLBACK;
        p_result := 'ERROR: 포인트가 부족합니다';
        
    WHEN OTHERS THEN
        ROLLBACK;
        p_result := 'ERROR: ' || SQLERRM;
        
END;
/

이 프로시저는 다음과 같은 실무적 요소들을 포함하고 있다:

• FOR UPDATE를 통한 동시성 제어: 여러 사용자가 동시에 주문할 때 재고가 음수가 되는 것을 방지
• 사용자 정의 예외: 비즈니스 규칙 위반을 명확한 예외로 처리
• 원자적 트랜잭션: 주문, 재고 차감, 포인트 처리가 모두 성공하거나 모두 실패
• RETURNING 절: INSERT 후 생성된 ID를 바로 반환
• 다단계 검증: 재고와 포인트를 순차적으로 확인

프로시저 호출 예제

DECLARE
    v_order_id NUMBER;
    v_result VARCHAR2(200);
BEGIN
    usp_process_order(
        p_customer_id => 1001,
        p_product_id => 5001,
        p_quantity => 2,
        p_use_point => 1000,
        p_order_id => v_order_id,
        p_result => v_result
    );
    
    DBMS_OUTPUT.PUT_LINE('주문번호: ' || v_order_id);
    DBMS_OUTPUT.PUT_LINE('결과: ' || v_result);
END;
/

---

초보 개발자는 모든 것을 프로시저로 만들거나, 반대로 프로시저를 전혀 사용하지 않는 극단적인 선택을 하기 쉽다. 하지만 경험이 쌓이면 상황에 맞게 적절히 활용할 수 있게 된다. 복잡한 배치 작업, 트랜잭션 처리, 보안이 중요한 로직은 프로시저로, 단순한 조회나 계산은 함수나 일반 쿼리로 처리하는 균형감각이 필요하다.