오라클의 스토어 프로시져나 함수의 내용을 주석을 제외해서 내용중에 사용되는 테이블의 명들을 추출

실무에서 사용되는 SQL 쿼리는 보통 더 복잡하고 다양한 기능을 포함합니다. 예를 들어, WITH 절, 서브쿼리, 복잡한 JOIN, UNION, CASE 문 등이 포함될 수 있습니다. 이와 같은 복잡한 SQL 쿼리에서 테이블 이름을 추출할 수 있도록 코드를 확장할 수 있습니다.

아래는 보다 복잡한 SQL 쿼리 예시와 이를 처리할 수 있는 파이썬 코드입니다. 쿼리에서 주석을 제거하고, 다양한 형태의 SQL 구문에서 테이블 이름을 추출하는 로직을 보강한 코드입니다.

예시 SQL 코드 (실무에 가깝게 복잡한 쿼리)

sql_code = """
-- 이 부분은 주석입니다
/* 멀티라인 주석 
여러 줄에 걸쳐서 작성된 주석입니다 
여기까지 주석 */

WITH temp_dept AS (
    SELECT dept_id, COUNT(*) AS emp_count 
    FROM employees 
    GROUP BY dept_id
)
SELECT e.emp_name, e.emp_id, t.emp_count 
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN temp_dept t ON e.dept_id = t.dept_id
WHERE e.salary > 50000
  AND d.dept_name IN ('HR', 'Finance')
  AND t.emp_count > 10
UNION
SELECT emp_name, emp_id, NULL AS emp_count
FROM employees 
WHERE salary > 100000;

UPDATE employees 
SET salary = salary * 1.1 
WHERE emp_id IN (SELECT emp_id FROM employees WHERE dept_id = 10);

INSERT INTO departments (dept_id, dept_name) 
SELECT 101, 'Legal' FROM dual;

DELETE FROM employees 
WHERE emp_id = (SELECT emp_id FROM employees WHERE dept_id = 20 AND salary < 40000);
"""

파이썬 코드:

728x90

복잡한 쿼리에서도 테이블 이름을 정확하게 추출할 수 있도록 여러 SQL 패턴을 반영한 코드입니다.

import sqlparse
import re

# SQL 쿼리에서 주석을 제거하는 함수
def remove_comments(sql):
    # -- 뒤의 주석을 제거 (한 줄 주석)
    sql = re.sub(r'--.*$', '', sql, flags=re.MULTILINE)
    # /*와 */ 사이의 주석을 제거 (블록 주석)
    sql = re.sub(r'/\*.*?\*/', '', sql, flags=re.DOTALL)
    return sql

# SQL 쿼리에서 테이블 이름을 추출하는 함수
def extract_table_names(sql):
    sql = remove_comments(sql)  # 주석 제거

    # SQL을 파싱
    parsed = sqlparse.parse(sql)

    tables = set()

    # SQL 명령문에서 테이블을 찾기 위한 정규 표현식
    table_patterns = [
        r'\bFROM\s+(\S+)',      # SELECT 쿼리에서 테이블 이름 추출
        r'\bJOIN\s+(\S+)',      # JOIN에서 테이블 이름 추출
        r'\bUPDATE\s+(\S+)',    # UPDATE 쿼리에서 테이블 이름 추출
        r'\bINSERT\s+INTO\s+(\S+)',  # INSERT INTO에서 테이블 이름 추출
        r'\bDELETE\s+FROM\s+(\S+)',  # DELETE FROM에서 테이블 이름 추출
        r'\bWITH\s+(\S+)',      # WITH 절에서 테이블(또는 CTE) 이름 추출
        r'\bSELECT\s+.*?\bFROM\s+(\S+)',  # SELECT 쿼리의 FROM 절에서 테이블 추출
        r'\bUNION\s+.*?\bFROM\s+(\S+)',   # UNION에서 사용된 FROM 테이블 추출
        r'\bINTO\s+(\S+)',      # INSERT INTO 절에서 테이블 이름 추출
        r'\bFROM\s+DUAL'        # DUAL 테이블은 Oracle에서 종종 사용
    ]

    for stmt in parsed:
        # 각 SQL 명령문을 문자열로 변환
        sql_text = stmt.value.upper()

        for pattern in table_patterns:
            matches = re.findall(pattern, sql_text)
            for match in matches:
                # 테이블 이름에서 'AS' 별칭을 제거하고, 테이블만 저장
                table_name = match.split()[0]  # 테이블 이름 앞에 붙은 별칭 제거
                tables.add(table_name.strip())

    return tables

# 예시 SQL 코드
sql_code = """
-- 이 부분은 주석입니다
/* 멀티라인 주석 
여러 줄에 걸쳐서 작성된 주석입니다 
여기까지 주석 */

WITH temp_dept AS (
    SELECT dept_id, COUNT(*) AS emp_count 
    FROM employees 
    GROUP BY dept_id
)
SELECT e.emp_name, e.emp_id, t.emp_count 
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN temp_dept t ON e.dept_id = t.dept_id
WHERE e.salary > 50000
  AND d.dept_name IN ('HR', 'Finance')
  AND t.emp_count > 10
UNION
SELECT emp_name, emp_id, NULL AS emp_count
FROM employees 
WHERE salary > 100000;

UPDATE employees 
SET salary = salary * 1.1 
WHERE emp_id IN (SELECT emp_id FROM employees WHERE dept_id = 10);

INSERT INTO departments (dept_id, dept_name) 
SELECT 101, 'Legal' FROM dual;

DELETE FROM employees 
WHERE emp_id = (SELECT emp_id FROM employees WHERE dept_id = 20 AND salary < 40000);
"""

# 테이블 이름 추출
tables = extract_table_names(sql_code)
print("사용된 테이블 이름들:", tables)

개선된 주요 기능:

728x90

1. WITH 절 처리:
   • r'\bWITH\s+(\S+)': WITH 절에 정의된 CTE(Common Table Expressions)에서 사용되는 테이블 이름을 추출합니다. 이 예시에서는 temp_dept가 추출됩니다.
2. 서브쿼리 및 UNION 처리:
   • r'\bSELECT\s+.*?\bFROM\s+(\S+)': 서브쿼리에서 FROM 절에 등장하는 테이블 이름을 추출합니다.
   • r'\bUNION\s+.*?\bFROM\s+(\S+)': UNION 절을 포함하는 쿼리에서 FROM 절의 테이블 이름을 추출합니다.
3. DUAL 테이블 처리:
   • r'\bFROM\s+DUAL': Oracle에서 종종 사용되는 DUAL 테이블도 추출할 수 있습니다.

출력 예시:

사용된 테이블 이름들: {'TEMP_DEPT', 'DEPARTMENTS', 'EMPLOYEES', 'DUAL'}

설명:

• 이 코드에서는 주석을 정확히 제거한 후, 다양한 SQL 구문에서 테이블 이름을 추출합니다.
• 서브쿼리, WITH 절, UNION, DUAL 테이블까지 고려하여 실무에서 자주 사용되는 복잡한 SQL 쿼리에서 테이블 이름을 추출할 수 있습니다.
• 테이블 이름 앞에 별칭이 있을 경우, split()을 사용하여 별칭을 제거하고 실제 테이블 이름만 추출합니다.

이 코드로 실무에서 발생할 수 있는 다양한 복잡한 SQL 쿼리에서도 테이블 이름을 정확히 추출할 수 있습니다.

 

2024.11.24 - [오라클 데이터베이스 강좌] - Oracle의 스토어 프로시저를 주석을 제외한 순수 소스 코드로 확인하려면