이메일 존재 컬럼 탐색 PL/SQL 스크립트

SET SERVEROUTPUT ON;
SET TIMING ON; -- 실행 시간 확인용 (선택사항)

DECLARE
    v_found_count NUMBER;
    v_table_name  VARCHAR2(100) := 'TABLE_A'; -- 조회할 테이블명 (대문자 필수)
    -- 이메일 정규식 패턴
    v_pattern     VARCHAR2(200) := '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
    v_sql         VARCHAR2(1000);
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- [' || v_table_name || '] 테이블 이메일 컬럼 탐색 시작 ---');

    -- 1. 해당 테이블의 문자형 컬럼들만 추출하여 루프 수행
    FOR rec IN (
        SELECT column_name 
        FROM user_tab_columns 
        WHERE table_name = UPPER(v_table_name)
          AND data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'CLOB')
    ) LOOP
        
        -- 2. 동적 쿼리 생성: ROWNUM = 1을 사용하여 존재 여부만 체크
        -- EXISTS나 ROWNUM = 1을 사용하면 첫 번째 매칭 시점에서 스캔 중단 (Stop Key)
        v_sql := 'SELECT COUNT(*) FROM ' || v_table_name || 
                 ' WHERE REGEXP_LIKE("' || rec.column_name || '", :1) AND ROWNUM = 1';

        EXECUTE IMMEDIATE v_sql INTO v_found_count USING v_pattern;

        -- 3. 발견된 경우 출력
        IF v_found_count > 0 THEN
            DBMS_OUTPUT.PUT_LINE('[발견] 컬럼명: ' || rec.column_name);
        END IF;
        
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('--- 탐색 종료 ---');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('에러 발생: ' || SQLERRM);
END;
/

2. . 코드 핵심 포인트 설명
UPPER(v_table_name): Oracle의 메타데이터 테이블(user_tab_columns)은 테이블명을 기본적으로 대문자로 저장합니다. 변수에 소문자를 써도 대문자로 인식하게끔 처리했습니다.
EXECUTE IMMEDIATE: 쿼리문 안에 변수(rec.column_name)가 들어가야 하므로 동적 SQL을 사용했습니다.
ROWNUM = 1: 이 구문이 들어감으로써 Oracle 옵티마이저는 'First Rows' 최적화를 수행합니다. 이메일 데이터가 1억 건 중 딱 1건만 있어도, 그 1건을 찾는 순간 해당 컬럼 조사를 즉시 멈추고 다음 컬럼으로 넘어갑니다.
쌍따옴표(") 처리: 컬럼명에 예약어나 특수문자가 섞여 있을 경우를 대비해 "' || rec.column_name || '" 형태로 감싸주는 것이 안전합니다.



더 정교한 이메일 체크가 필요하다면?
만약 단순한 형식이 아니라 조금 더 엄격한 이메일 체크를 원하신다면 위 스크립트의 v_pattern 부분만 아래와 같이 수정해서 사용해 보세요.
대소문자 무시 버전:
REGEXP_LIKE(컬럼, 패턴, 'i') 를 사용하거나 패턴 자체를 수정합니다.
엄격한 버전:
^[a-zA-Z0-9!#$%&''*+/=?^_{|}~-]+(?:.[a-zA-Z0-9!#$%&''*+/=?^_{|}~-]+)*@(?:[a-zA-Z0-9](?:[a-zA-Z0-9-]*[a-zA-Z0-9])?\.)+[a-zA-Z0-9](?:[a-zA-Z0-9-]*[a-zA-Z0-9])?$



간단하게 수동으로 확인하고 싶을 때

SELECT *
FROM TABLE_A
WHERE REGEXP_LIKE(컬럼1, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
   OR REGEXP_LIKE(컬럼2, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
   OR REGEXP_LIKE(컬럼3, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

대소문자 구분: REGEXP_LIKE는 기본적으로 대소문자를 구분합니다. 구분 없이 찾고 싶다면 세 번째 인자에 'i'를 추가하세요.
예: REGEXP_LIKE(column_name, pattern, 'i')
성능: 테이블의 데이터 양이 방대할 경우 전체 컬럼을 정규표현식으로 검색하면 부하가 생길 수 있습니다. 가능하면 ROWNUM = 1 조건을 활용해 데이터 존재 여부만 빠르게 파악하는 것이 좋습니다.



기본 구문

SET SERVEROUTPUT ON;

DECLARE
    v_count NUMBER;
    v_table_name VARCHAR2(30) := 'TABLE_A'; -- 찾고자 하는 테이블명 (대문자 주의)
    v_pattern VARCHAR2(100) := '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
BEGIN
    FOR rec IN (SELECT column_name 
                FROM user_tab_columns 
                WHERE table_name = v_table_name 
                AND data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2')) 
    LOOP
        -- 동적 쿼리로 해당 컬럼에 패턴이 존재하는지 확인
        EXECUTE IMMEDIATE 
            'SELECT COUNT(*) FROM ' || v_table_name || 
            ' WHERE REGEXP_LIKE(' || rec.column_name || ', :1) AND ROWNUM = 1'
            INTO v_count
            USING v_pattern;

        IF v_count > 0 THEN
            DBMS_OUTPUT.PUT_LINE('이메일 데이터 발견 컬럼: ' || rec.column_name);
        END IF;
    END LOOP;
END;
/

여러 개의 테이블명을 콤마(,)로 구분된 문자열로 받아서, 이를 배열(Collection)에 담고 순차적으로 처리하는 방식의 PL/SQL 스크립트

SET SERVEROUTPUT ON;

DECLARE
    -- 1. 입력 설정
    v_table_input  VARCHAR2(4000) := 'TABLE1, TABLE2, TABLE3'; -- 처리할 테이블 목록
    v_pattern      VARCHAR2(200)  := '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
    
    -- 2. 배열(Collection) 타입 정의 및 변수 선언
    TYPE t_table_names IS TABLE OF VARCHAR2(100);
    v_tables       t_table_names;
    
    v_found_count  NUMBER;
    v_sql          VARCHAR2(1000);
BEGIN
    -- 3. 콤마로 구분된 문자열을 자르고 공백을 제거하여 배열에 할당
    SELECT TRIM(REGEXP_SUBSTR(v_table_input, '[^,]+', 1, LEVEL))
    BULK COLLECT INTO v_tables
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(v_table_input, '[^,]+', 1, LEVEL) IS NOT NULL;

    DBMS_OUTPUT.PUT_LINE('--- 총 ' || v_tables.COUNT || '개의 테이블 탐색 시작 ---');

    -- 4. 테이블 배열 루프 (Outer Loop)
    FOR i IN 1 .. v_tables.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('>> 테이블 확인 중: ' || v_tables(i));

        -- 5. 해당 테이블의 문자열 타입 컬럼 루프 (Inner Loop)
        FOR rec IN (
            SELECT column_name 
            FROM user_tab_columns 
            WHERE table_name = UPPER(v_tables(i)) -- 대문자 변환 처리
              AND data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'CLOB')
        ) LOOP
            
            -- ROWNUM = 1을 사용하여 존재 여부만 빠르게 확인
            v_sql := 'SELECT COUNT(*) FROM ' || v_tables(i) || 
                     ' WHERE REGEXP_LIKE("' || rec.column_name || '", :1) AND ROWNUM = 1';

            BEGIN
                EXECUTE IMMEDIATE v_sql INTO v_found_count USING v_pattern;

                IF v_found_count > 0 THEN
                    DBMS_OUTPUT.PUT_LINE('   [발견] ' || v_tables(i) || ' -> ' || rec.column_name);
                END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    -- 테이블이 없거나 권한이 없는 경우 등 예외 처리
                    DBMS_OUTPUT.PUT_LINE('   [오류] ' || v_tables(i) || '의 ' || rec.column_name || ' 처리 중 에러');
            END;
            
        END LOOP;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('--- 모든 탐색 종료 ---');
END;
/