[C# PL/SQL] 9.오라클 프로시저에서 동적 SQL 사용하기, 프로시저 내에서 동적 SQL을 작성하고 C#에서 이를 호출하는 방법을 소개

9. 오라클 프로시저에서 동적 SQL 사용하기

본 글에서는 오라클 데이터베이스에서 동적 SQL을 사용하는 프로시저를 작성하고, 그 프로시저를 C#에서 호출하는 방법에 대해 자세히 설명하겠습니다. 동적 SQL은 런타임에 SQL 문을 구성할 수 있는 강력한 기능으로, 다양한 유연성과 시나리오를 처리하는 데 있어 큰 장점을 제공합니다. 특히, 데이터베이스 테이블 구조가 변경되거나, 쿼리가 사용자 입력에 따라 다를 때 유용하게 사용할 수 있습니다.

1. 동적 SQL이란?

동적 SQL은 SQL 문이 실행 시점에 동적으로 생성되는 SQL 문을 의미합니다. 이는 정적 SQL과 대비되는 개념으로, 정적 SQL은 컴파일 타임에 정해진 SQL 문을 의미합니다. 동적 SQL을 사용하면 다음과 같은 장점을 누릴 수 있습니다:

  • 복잡한 조건부 로직 처리
  • 쿼리 작성의 유연성
  • 동적으로 테이블 이름이나 컬럼 이름 변경 가능
  • 대량 데이터 처리 시 성능 개선

2. Oracle PL/SQL에서 동적 SQL 사용하기

오라클에서는 동적 SQL을 구현하기 위해 EXECUTE IMMEDIATE 문과 DBMS_SQL 패키지를 사용할 수 있습니다. 일반적으로 EXECUTE IMMEDIATE 구문이 더 간단하기 때문에 주로 사용됩니다.

2.1 EXECUTE IMMEDIATE 사용 예

단일 SQL 문장을 실행하는 간단한 예를 살펴보겠습니다. 다음은 특정 테이블에 행을 삽입하는 프로시저입니다: 


CREATE OR REPLACE PROCEDURE insert_dynamic_sql(
    p_table_name IN VARCHAR2,
    p_column_name IN VARCHAR2,
    p_value IN VARCHAR2
) AS
    sql_stmt VARCHAR2(1000);
BEGIN
    sql_stmt := 'INSERT INTO ' || p_table_name || ' (' || p_column_name || ') VALUES (:1)';
    EXECUTE IMMEDIATE sql_stmt USING p_value;
END;

위의 프로시는 테이블명, 컬럼명, 값을 파라미터로 받아 해당 테이블에 동적으로 데이터를 삽입합니다.

2.2 COMMIT과 ROLLBACK

동적 SQL을 사용할 때는 데이터 변경 작업 후 반드시 COMMIT을 호출하여 변경 사항을 확정해야 합니다. 예를 들어: 


-- 위 프로시저에 commit 추가
CREATE OR REPLACE PROCEDURE insert_dynamic_sql(
    p_table_name IN VARCHAR2,
    p_column_name IN VARCHAR2,
    p_value IN VARCHAR2
) AS
    sql_stmt VARCHAR2(1000);
BEGIN
    sql_stmt := 'INSERT INTO ' || p_table_name || ' (' || p_column_name || ') VALUES (:1)';
    EXECUTE IMMEDIATE sql_stmt USING p_value;

    COMMIT; -- 데이터베이스에 변경 사항을 확정
END;

3. C#에서 오라클 동적 SQL 프로시저 호출하기

C#에서는 Oracle.ManagedDataAccess.Client 네임스페이스를 통해 오라클 데이터베이스에 연결하고, 앞서 작성한 프로시저를 호출할 수 있습니다. 다음 예제를 통해 설명하겠습니다.

3.1 C# 프로젝트 설정

  • NuGet 패키지 매니저를 통해 Oracle.ManagedDataAccess 패키지를 설치합니다.

3.2 C# 코드 예제


using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connString = "User Id=;Password=;Data Source=";
        
        using (OracleConnection conn = new OracleConnection(connString))
        {
            conn.Open();
            using (OracleCommand cmd = new OracleCommand("insert_dynamic_sql", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add("p_table_name", OracleDbType.Varchar2).Value = "your_table";
                cmd.Parameters.Add("p_column_name", OracleDbType.Varchar2).Value = "your_column";
                cmd.Parameters.Add("p_value", OracleDbType.Varchar2).Value = "sample_value";

                cmd.ExecuteNonQuery();
                Console.WriteLine("데이터가 성공적으로 삽입되었습니다.");
            }
        }
    }
}

위의 C# 코드는 데이터베이스에 연결하고, 동적 SQL을 사용한 오라클 프로시저를 호출하여 데이터를 삽입하는 작업을 수행합니다.

4. 동적 SQL 사용 시 주의사항

동적 SQL을 사용할 때는 몇 가지 주의할 사항이 있습니다:

  • SQL 인젝션(SQInjection): 사용자 입력을 기반으로 SQL 문을 생성할 때, SQL 인젝션 공격에 취약할 수 있습니다. 반드시 바인드 변수 또는 정규 표현식을 사용하여 안전성을 확보해야 합니다.
  • 성능: 작성하는 SQL 문이 복잡해질수록 성능이 저하될 수 있습니다. 실행 계획이 캐싱되지 않을 수 있기 때문에 통계 정보를 최적화하고, 자주 사용되는 쿼리는 정적인 구문으로 작성하는 것을 고려해야 합니다.
  • 디버깅: 동적 SQL은 런타임에 SQL 문이 생성되므로, 에러 발생 시 쿼리 내용을 쉽게 디버깅하기 어렵습니다. 철저한 로깅이 필요합니다.

5. 결론

동적 SQL은 유연한 데이터베이스 조작이 필요한 경우 매우 유용한 도구입니다. 그러나 그 사용에 대해 충분한 이해와 주의가 필요합니다. 앞서 소개한 프로시저 및 C# 호출 예제를 통해 여러분은 오라클 프로시저에서 동적 SQL을 활용하는 기초를 익히셨기를 바랍니다. 동적 SQL을 통해 다양한 비즈니스 요구사항에 유연하게 대응하며, 더욱 발전된 데이터베이스 어플리케이션을 개발할 수 있습니다.