Dev Record

1. PL/SQL

PL/SQL은 Procedural Language/Structured Query Language의 약자로, 데이터베이스에서 SQL을 확장하여 절차적 프로그래밍 기능을 제공하는 언어이다.

DECLARE 선언부, BEGIN 시작부, EXCEPTION 예외부, END 끝 등의 구조로 이루어져있다.

1.1 주요 특징

• 절차적 프로그래밍: 조건문(IF, CASE), 반복문(LOOP, WHILE), 예외 처리 등 절차적 요소를 포함하여 논리적인 흐름을 제어할 수 있다.
• 블록 구조: PL/SQL은 선언부(DECLARE), 실행부(BEGIN-END), 예외 처리부(EXCEPTION)로 구성된 블록 구조를 사용한다.
  • 익명 블록(Anonymous Block)
  • 저장 프로시저(Stored Procedure)
  • 함수(Function)
  • 패키지(Package)
• SQL 통합: SQL 문과 함께 동작하며, 프로시저, 함수, 트리거 등을 정의하고 실행할 수 있다.
• 모듈화 및 재사용성: 패키지를 통해 관련 함수와 프로시저를 묶어 관리할 수 있어 코드의 재사용성과 유지보수성을 높일 수 있다.
• 파라미터 지원: IN, OUT, INOUT 파라미터를 사용하여 외부에서 값을 전달받거나, 실행 결과를 반환할 수 있다.
  • IN : 읽기 전용 입력 값 (기본값)
  • OUT : 프로시저 실행 후 결과 반환
  • INOUT : 입력 값도 받고 결과 값도 반환

1.2 CURSOR

Oracle이 SQL 문을 실행할 때 생성하는 메모리 공간인 Context Area에서 쿼리 실행 상태 정보, 결과집합, 처리 행 수 등의 정보가 저장되고 Cursor는 이 Context Area를 참조하여 데이터를 가져오는 도구이다.

Context Area : SQL 실행 결과(결과집합)와 실행 상태 정보를 저장하는 메모리 영역

Cursor : Context Area를 가리키고 데이터를 가져오는 도구

암시적 커서

sql

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    DBMS_OUTPUT.PUT_LINE('총 직원 수: ' || v_count);
END;

• SELECT COUNT(*) 실행 → Oracle이 자동으로 Context Area 생성
• Context Area에 결과값(COUNT(*)) 저장
• v_count 변수에 저장 후 Context Area 자동 해제

⚡ 암시적 커서의 특징

• Context Area를 자동 관리
• SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT 같은 속성으로 상태 확인 가능

명시적 커서

sql

DECLARE
    CURSOR emp_cursor IS SELECT emp_name FROM employees;
    v_emp_name employees.emp_name%TYPE;
BEGIN
    OPEN emp_cursor;  -- Context Area 생성 및 결과 저장
    LOOP
        FETCH emp_cursor INTO v_emp_name;  -- Context Area에서 한 행 가져오기
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_name);
    END LOOP;
    CLOSE emp_cursor;  -- Context Area 해제
END;

• OPEN emp_cursor → Context Area 생성
• FETCH emp_cursor → Context Area에서 한 행씩 가져옴
• CLOSE emp_cursor → Context Area 해제

⚡ 명시적 커서의 특징

• Context Area를 직접 관리해야 함
• OPEN, FETCH, CLOSE 등을 사용

1.3 장단점

✅ 장점

• 성능 향상: SQL 문을 미리 컴파일하고 저장하여 실행 속도가 빠름
• 네트워크 트래픽 감소: DB 내에서 로직을 처리하여 애플리케이션과의 통신량 감소
• 보안 강화: 데이터베이스 내부에서 실행되므로 직접 SQL을 실행하는 것보다 보안이 강화됨
• 트랜잭션 제어 가능: COMMIT, ROLLBACK을 활용해 안정적인 트랜잭션 관리 가능

❌ 단점

• 유지보수 어려움: 비즈니스 로직이 DB에 집중되면 코드 관리가 어려워짐
• 디버깅 불편: 일반 애플리케이션 코드보다 디버깅 및 로깅이 어려움
• 이식성 부족: DBMS에 종속적이므로 다른 데이터베이스로 이전 시 코드 변경 필요
• 병목 발생 가능: DB 서버에서 많은 로직을 처리하면 서버 부하 증가

1.4 Example

sql

CREATE OR REPLACE PROCEDURE EXAMPLE."PEXC_EXAMPLE_Q" 
(
      p_example1  VARCHAR2  DEFAULT 'Q'   
     ,p_example2  VARCHAR2                                                                                           
     ,p_example3  VARCHAR2                                                                                        
     ,RTN_CURSOR  OUT SYS_REFCURSOR                                                                               
) 
IS
    ---------------------------------------------------------------------------------------------------------------------------
    -- ▩▩ [ 1. 변수선언 ]
    ---------------------------------------------------------------------------------------------------------------------------
    ERR_CODE  NUMBER := -20009; -- ERROR 코드
    ERR_MSG   VARCHAR2(200);    -- ERROR Message
    USER_EXCEPTION EXCEPTION;

    v_sql_query VARCHAR2(1000); -- 동적 SQL 저장 변수
    v_table_name VARCHAR2(50);  -- 테이블명 동적 결정 변수

    v_count NUMBER; -- 데이터 존재 여부 체크용
BEGIN
    ---------------------------------------------------------------------------------------------------------------------------
    -- ▩▩ [ 2. 수행전 Check Logic ]
    ---------------------------------------------------------------------------------------------------------------------------
    -- 테이블 존재 여부 체크 (p_example1 기준)
    SELECT COUNT(*) INTO v_count 
    FROM all_tables 
    WHERE table_name = UPPER('TABLE_EXAMPLE1');

    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'TABLE_EXAMPLE1 테이블이 존재하지 않습니다.');
    END IF;

    ---------------------------------------------------------------------------------------------------------------------------
    -- ▩▩ [ 3. Business Logic 처리 ]
    ---------------------------------------------------------------------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('example');    

    -- 동적 테이블명 설정
    IF p_example1 = 'Q' THEN
        v_table_name := 'TABLE_EXAMPLE1';
    ELSE
        v_table_name := 'TABLE_EXAMPLE3';
    END IF;

    -- 동적 SQL 생성
    v_sql_query := 'SELECT * FROM ' || v_table_name;

    -- 반복문을 활용한 디버깅 메시지 출력
    FOR i IN 1..3 LOOP
        DBMS_OUTPUT.PUT_LINE('Loop iteration: ' || i);
    END LOOP;

    -- 동적 SQL 실행
    OPEN RTN_CURSOR FOR v_sql_query;

    ---------------------------------------------------------------------------------------------------------------------------
    -- ▩▩ [ 4. Exception 처리 ]
    ---------------------------------------------------------------------------------------------------------------------------
EXCEPTION
    WHEN USER_EXCEPTION THEN
        RAISE_APPLICATION_ERROR(ERR_CODE, ERR_MSG);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(ERR_CODE, SQLERRM);
END;

예시 구조를 보면 PL/SQL 코드가, Business Logic을 처리하는 Service Layer와 데이터 접근을 담당하는 Repository의 역할을 함께 수행하며, 하나의 Method처럼 활용한다.

그럼 Java단에서는 ? 추가적인 전처리나 후처리 작업이 없다면 파라미터만 넘겨 리턴 받으면 끝이다.

PL/SQL 같은 프로시저 기반 개발 방식에서는 계층형 아키텍처나 객체지향 설계를 쓰지 않는다.

2. Procedure vs Java

먼저 간략히 말하자면 우리 회사는 Stored Procedure를 사용한다.

입사 후 첫 프로젝트를 마치며 프로시저의 단점을 너무 많이 느꼈고, 왜 객체지향 개발을 하지않을까 의문이 생겼다.

그렇게 프로시저 개발을 채택하는 이유와 Java 프로그래밍에 대한 차이를 찾아보던 중 도움되고 공감되는 글이있었고, 두 개발 방식의 차이를 잘 정리해놓아 가져와보았다.

🔹 기술적 차이

   Java : 컴파일된 소스를 어플리케이션 서버에 배포하여 실행하는 객체지향 프로그래밍. 비교적 간단한 쿼리로 데이터를 조회한 후 자바 객체를 이용해 로직을 구현

   Procedure : DB 서버에서 소스를 컴파일 후 실행하는 절차지향 프로그래밍. 쿼리 집합. 순차적으로 쿼리를 수행하여 로직 구현. 문법이 DBMS 마다 다르다.

🔹 성능/부하 관련

   Java :

• 어플리케이션 서버의 사양에 영향이 크고 DB 서버 사양에 비교적 영향을 덜 받는다.
• 쿼리 성능 외에 네트워크 IO 횟수에도 영향을 받는다.
• 객체지향다운 구현과 단순하고 효율적인 쿼리를 사용하여 DB 접속 네트워크 I/O 횟수를 줄이는 것이 좋다.
• 수행 쿼리 로그를 콘솔과 파일로 남길 경우 그를 위한 추가 자원과 시간이 필요하다. 네트워크 I/O 보다 로깅이 더 많은 시간을 잡아먹는다. 최신 로깅 라이브러리가 지원하는 비동기 로깅을 고려해볼 수 있다.
• 캐싱 기법으로 동일 쿼리 재수행을 최소화할 수 있다.
• 멀티 쓰레드를 활용하여 성능 향상을 기대할 수 있다. 단, 데드락에 주의해야 함. 또한 스프링 트랜잭션 전파는 멀티 쓰레드에 적용되지 않는다는 것도 주의.
• 성능 이슈가 발생할 경우 로깅 파일 살펴보면 응답이 느린 쿼리를 찾아내기 쉽다.

   Procedure :

• DB 성능이 매우 중요하다. 그러므로 DBMS 선택도 중요하고 DB 서버 사양도 중요함.
• 로직이 복잡할 경우 복잡한 쿼리를 사용하게 되어 쿼리 작성 실력과 튜닝이 더욱 중요해짐.
• 트랜잭션 격리 수준에 따라 다르지만, 동일 쿼리 반복 수행시 실제로 데이터를 조회하므로 비효율적인 쿼리 수행이 많음.
• 프로시저 자체에서 멀티쓰레드를 지원하진 않지만, 어플리케이션에서 멀티 쓰레드로 프로시저를 실행할 수는 있다.
• 성능 이슈가 발생할 경우 프로시저 내에서 속도가 느린 쿼리를 찾아내기 어렵다. DB 관리자의 지원이 절대적으로 필요.

🔹 형상관리 (버전관리)

   Java : IDE 툴을 통해 모든 소스가 SVN, GIT 등으로 형상 관리가 되고 그것이 그대로 서버에 배포되므로 변경 추적과 원복이 용이하다. 동시에 같은 소스를 다른 개발자가 따로 수정할 경우 conflict 오류가 발생하여 서로 알아채고 merge 하기 쉽다.

   Procedure : 별도로 소스 변경 내역을 수동으로 백업하며 관리하여야 하고 그것이 DB 서버에 실제로 반영되었음을 보장하지 않으므로 변경 추적과 원복이 어렵다. 동시에 같은 소스를 다른 개발자가 따로 수정할 경우 아무런 오류 없이 나중에 컴파일된 소스가 반영되므로 서로 알아채기 어렵다.

🔹 배포관리

   Java : 변경 소스를 반영하기 위해 어플리케이션 서버를 무중단 서버로 구축하거나 재시작 해야한다.

   Procedure : DB 에서 프로시저 컴파일만 하면 되므로 권한이 있는 사람이면 아무 때나 수정 배포할 수 있다. 단, 어플리케이션 서버에서 콜하는 프로시저이고 파라미터 갯수나 타입 등이 바뀔 때에는 어플리케이션 소스도 변경 배포되어야 하므로 두 작업이 되도록 동시에 이루어지도록 타이밍을 잘 맞추어야 한다.

🔹 디버깅/테스트

   Java : 개발자들이 개발시 사용하는 IDE 툴로 쉽게 디버깅이 가능하다. 로깅 설정으로 로그 파일을 남기면 실제 수행된 쿼리와 바인딩된 변수 확인이 가능하므로 운영 환경에서도 문제 추적이 용이하다. 소스 일부 테스트와 테스트 자동화가 용이하다.

   Procedure : 디버깅 지원하는 DB 툴이 있어야 하며, 파라미터를 수동으로 입력하고 실행하여 디버깅을 해야 한다. 잘못된 파라미터 입력으로 버그나 오류가 발생했는데 애꿎은 프로시저만 디버깅하느라 삽질하는 경우가 종종 있다. DB 서버 자체에 쿼리 수행 로그가 남지만 개발자에게 접근 권한을 주는 경우는 거의 없으므로 운영 환경에서 문제 추적이 어렵다. 프로시저 내에 특정 부분만 테스트할 수 없고 프로시저 단위로 수행해야 하므로 테스트 자동화가 까다롭다.

🔹 에러 처리

   Java : 다양한 추상화 기법으로 일관된 에러 메시지 생성과 처리가 용이함.

   Procedure : 발생한 에러 메시지를 어플리케이션에 OUT 변수로 전달해야 한다. 경우에 따라 어플리케이션에서는 이를 다시 특정 Exception 으로 포장하거나 분기 처리해야 하므로 비교적 다루기 복잡함.

🔹트랜잭션 관리

   Java : 스프링 등의 선언적 트랜잭션 관리 기법으로 코딩이 아닌 설정을 통한 다양한 트랜잭션 전략 활용 용이하고, 그에 따라 트랜잭션을 조건에 따라 다양하게 옵션화할 때도 동일한 소스를 수정없이 재사용할 수 있다. 단, 일부 특수한 트랜잭션 설정은 DBMS 에서 지원해주는 것만 가능하다.

   Procedure : 기본적으로 어플리케이션에서 트랜잭션을 관리하지만, 프로시저에서 수동 커밋 롤백이 가능함. 프로그래밍적으로 직접 커밋 롤백 명령문을 작성해야 하므로 트랜잭션 분리 전략을 다양하게 활용하기 어렵다.

🔹 DB 의존도

   Java : 특정 DBMS 특화 문법이나 함수 사용을 지양하고 표준 ANSI SQL 위주로 개발하면 DB 의존도를 최소화하여 변경 비용을 크게 줄일 수 있다.

   Procedure : DBMS 마다 프로시저 문법이 다르므로 의존도가 매우 높다. 양이 많고 복잡하다면 한 번 프로시저로 개발하면 DBMS 변경이 현실적으로 거의 불가능하다.

🔹 소스 보안 (안티 리버싱)

   Java : 자바 특성상 디컴파일이 쉽기 때문에 완벽한 비공개가 어렵다. 전문 상용 난독화 툴을 적용하면 분석이 거의 불가능하게 만들 수 있기는 하다.

   Procedure : DBMS 에 따라 자체 제공하는 소스 암호화 방법이 있다. 그 외 접근 제한을 통해 소스를 보호할 수도 있다.

🔹 개발/수정 생산성

   Java : 객체지향 개발 특성상 개발 초기에는 자바 소스를 여럿 작성해야 해서 시간이 걸리지만, 일단 개발이 완료되고 나면 수정 변경에 유연하고 견고한 시스템을 구축할 수 있다.

   Procedure : 개발 초기에는 프로시저만 작성하면 되어 금방 만들 수 있지만, 절차 지향 특성상 로직이 복잡해지고 소스가 길어질수록 점점 수정 변경이 어려워지고 리스크가 커진다.

Spring Boot부터 시작해서 OOP, JPA로 개발을 배웠던 나로서는 PROCEDURE의 단점이 너무 많다고 느낀다.

동적 SQL이나 프로시저 내 임시 테이블을 이용한 로직, 새로운 프로젝트마다 덕지덕지 붙어서 돌아가는 SP들은 가독성도 좋지 않고, 디버깅이 힘들어 유지 보수 측면에서 너무 불편하다.

하지만 한편으론, 진짜 이런 방식이 ‘무조건 나쁜 걸까?’ 하는 생각도 든다.

과연 Java로 모든 로직을 옮기면 정말 더 깔끔하고 유지 보수하기 쉬운 구조가 될까?

10년 넘는 도메인 지식을 가진 설계자들이 반드시 Spring과 JPA를 잘 알아야 할까?

결국, 기술 선택에는 비용과 리소스, 그리고 누가 그것을 다룰 수 있느냐가 전부 포함되어 있다.

비즈니스 적으로도 모두 다 해당하는 개발자의 인건비와 빠른 개발 속도를 포기하고 Java를 택하기엔 수지 타산이 맞지 않다.

결국 중요한 건 "무엇이 더 낫냐"가 아니라, "이 상황에서 어떤 선택이 더 효율적인가?"라는 질문이다.

현재로서 내가 할 수 있는 일은 지금 쓰고 있는 구조 안에서 최대한 문제를 줄이는 것뿐.

이런 고민을 하다 보면, 나는 도대체 어떤 개발자가 되고 싶은 걸까 하는 질문에 자꾸 멈춰 서게 된다.

옛 방식을 고수하면 도태될까 두렵고, 그렇다고 무작정 신기술만 밀어붙이는 마이웨이 개발자가 되는 것도 싫다.

나는 어디쯤에 서야 할까? 아직은 나도 내 스탠스를 정확히 정하지 못했다.

Mybatis에서 프로시저를 사용할 때는 statementType을 CALLABLE로 설정해야 한다. 반대로, 일반적인 SQL 쿼리를 실행하는 경우에는 statementType을 PREPARED로 설정해야 한다.

MyBatis에서 statementType은 SQL 실행 방식에 따라 세 가지 옵션을 제공한다.

• STATEMENT: 일반적인 SQL 쿼리를 실행하며, 매개변수를 전달할 수 없다. 실행 시마다 컴파일되므로 성능이 떨어질 수 있지만, DDL 문(create, alter, drop 등)에서 주로 사용된다.
• PREPARED: 사전 컴파일된 SQL을 실행하며 매개변수를 전달할 수 있다. 반복 실행 시 성능이 뛰어나고 SQL 인젝션을 예방할 수 있습니다. 일반적인 SELECT, INSERT, UPDATE, DELETE와 같은 동적 쿼리에 적합하다.
• CALLABLE: 저장 프로시저를 호출하는 데 사용된다. 프로시저의 입력 매개변수와 출력 값을 설정할 수 있으며, 미리 컴파일되어 있어 성능상 이점이 있다. Oracle이나 SQL Server에서 저장 프로시저를 활용할 때 적합하다.

Select 속성

Insert, Update, Delete 속성

기타 참고