oracle truncate 프로시져 사용 방법

oracle truncate 프러시저 에러

얼마 전에 oracle truncate 사용 시 에러가 발생

먼저 truncate 설명입니다.

 

오라클에서 TRUNCATE는 테이블의 모든 데이터를 삭제하는 명령어입니다. DELETE와는 몇 가지 중요한 차이점이 있습니다.

TRUNCATE의 특징

• DML이 아닌 DDL: TRUNCATE는 데이터 정의 언어(DDL)에 속합니다. 따라서 ROLLBACK이 불가능합니다.
• 빠른 속도: DELETE와 달리 테이블의 모든 행을 하나씩 삭제하는 것이 아니라, 데이터 영역을 통째로 초기화합니다. 이로 인해 대량의 데이터를 삭제할 때 매우 빠릅니다.
• 테이블 구조는 유지: TRUNCATE를 실행해도 테이블 자체의 구조(컬럼, 제약조건 등)는 그대로 유지됩니다.
• AUTO_INCREMENT 초기화: 오라클에서는 시퀀스를 사용하기 때문에 직접적인 AUTO_INCREMENT 개념은 없지만, TRUNCATE를 사용하면 시퀀스 값은 초기화되지 않습니다. 시퀀스 값을 초기화하려면 별도의 명령어를 사용해야 합니다.

1. 기본 TRUNCATE 사용 방법

TRUNCATE TABLE my_table;

2. 제약 조건(CASCADE)을 이용한 TRUNCATE

다른 테이블에서 외래 키(Foreign Key)로 참조하고 있는 테이블을 TRUNCATE 할 경우, 오류가 발생할 수 있습니다. 이럴 때는 CASCADE 옵션을 사용해 종속된 제약 조건들을 함께 처리할 수 있습니다.

TRUNCATE TABLE 테이블명 CASCADE;

3. TRUNCATE와 DELETE의 주요 차이점

구분	TRUNCATE	DELETE
명령어 종류	DDL (Data Definition Language)	DML (Data Manipulation Language)
롤백 가능 여부	불가능	가능
속도	매우 빠름 (테이블 초기화 방식)	느림 (행 단위 삭제 방식)
삭제 방식	테이블의 데이터 블록 전체를 재설정	행 단위로 데이터 삭제
WHERE 절	사용 불가능 (전체 데이터 삭제)	사용 가능 (특정 데이터 선택 삭제)
트리거	트리거를 활성화시키지 않음	트리거를 활성화시킴

4. TRUNCATE 사용 시 주의사항

• 롤백 불가: TRUNCATE를 실행하면 되돌릴 수 없으므로, 실행 전에 반드시 신중해야 합니다.
• 권한: TRUNCATE 명령을 실행하려면 해당 테이블에 대한 DROP ANY TABLE 또는 TRUNCATE ANY TABLE 권한이 필요합니다.
• 트리거 비활성화: TRUNCATE는 DELETE 트리거를 활성화시키지 않습니다. 만약 DELETE 트리거가 중요한 로직을 수행하고 있다면, DELETE를 사용해야 합니다.

※ TRUNCATE는 대량의 데이터 초기화에 매우 효과적인 명령어이지만, 복구할 수 없다는 점을 항상 염두에 두고 사용해야 합니다.

TRUNCATE 프로시저 내 사용방법

프로시저 내에서 TRUNCATE를 사용하는 방법은 동적 SQL(Dynamic SQL)을 이용해야 합니다. TRUNCATE는 DDL(Data Definition Language)이기 때문에 일반적인 SQL 구문처럼 프로시저 내에 직접 사용할 수 없습니다.

동적 SQL 사용 방법

CREATE OR REPLACE PROCEDURE truncate_table_proc (
    p_table_name IN VARCHAR2
)
IS
    v_sql_stmt VARCHAR2(200);
BEGIN
    v_sql_stmt := 'TRUNCATE TABLE ' || p_table_name;
    
    EXECUTE IMMEDIATE v_sql_stmt;
    
    -- 프로시저가 성공적으로 실행되었음을 알리는 메시지
    DBMS_OUTPUT.PUT_LINE('테이블 ' || p_table_name || '이(가) TRUNCATE되었습니다.');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        -- 오류 발생 시 롤백 (EXECUTE IMMEDIATE는 자동으로 커밋되므로, 필요 시 ROLLBACK을 명시적으로 처리)
        RAISE;
END;

프로시저 실행

BEGIN
    truncate_table_proc('employees');
END;

주의사항

• 보안 문제: 동적 SQL은 SQL 인젝션 공격에 취약할 수 있으므로, 외부 입력값을 사용할 때는 입력값 검증을 철저히 해야 합니다.
• 권한: 프로시저를 실행하는 사용자에게 TRUNCATE 하려는 테이블에 대한 적절한 권한(DROP ANY TABLE 또는 TRUNCATE ANY TABLE)이 부여되어야 합니다.
• 롤백 불가: TRUNCATE는 DDL이므로, 일단 실행되면 롤백이 불가능합니다. 프로시저 내에서 오류가 발생하더라도 TRUNCATE 작업 자체는 커밋된 상태로 유지됩니다.
• 트리거 미작동: TRUNCATE는 DELETE 트리거를 활성화시키지 않습니다. 만약 트리거 로직이 중요하다면 TRUNCATE 대신 DELETE를 고려해야 합니다.

※ 프로시저 내에서 TRUNCATE TABLE my_table와 같이 직접적으로 DDL(Data Definition Language) 명령어를 사용하면 오류가 발생합니다. 이 현상은 Oracle 데이터베이스의 PL/SQL 엔진이 DDL 문을 직접 처리하지 못하기 때문입니다.

2025.09.13 - [db/oracle] - oracle DDL, DML 차이점

oracle DDL, DML 차이점