Oracle PL/SQL 동적 SQL 자습서: 즉시 실행 및 DBMS_SQL

동적 SQL이란 무엇입니까?

동적 SQL SQL은 런타임에 SQL 문을 생성하고 실행하는 프로그래밍 방법론입니다. 주로 범용적이고 유연한 프로그램을 작성하는 데 사용되며, 테이블 이름, 열 목록 또는 WHERE 절의 조건이 프로그램 실행 시점까지 알려지지 않은 경우와 같이 요구 사항에 따라 SQL 문을 생성하고 실행합니다.

동적 SQL 작성 방법

PL/SQL은 동적 SQL을 작성하는 두 가지 방법을 제공합니다.

1. NDS – 네이티브 동적 SQL (EXECUTE IMMEDIATE 및 OPEN-FOR 문)
2. DBMS_SQL (제공된 패키지)

일반적인 규칙은 간단합니다. 입력 및 출력 변수의 개수와 데이터 형식을 컴파일 시점에 알 수 있다면, 더 빠르고 코드도 적게 필요한 네이티브 동적 SQL을 사용하십시오. 해당 정보를 런타임에만 알 수 있는 경우에는 DBMS_SQL 패키지를 사용하십시오.

NDS(네이티브 동적 SQL) – 즉시 실행

네이티브 동적 SQL은 동적 SQL을 작성하는 더 쉬운 방법입니다. 실행 시점에 EXECUTE IMMEDIATE 명령어를 사용하여 SQL을 생성하고 실행합니다. 이 방식을 사용하려면 실행 시점에 사용될 변수의 데이터 형식과 개수를 미리 알아야 합니다. 또한 DBMS_SQL에 비해 성능이 우수하고 코드 복잡성이 낮습니다.

통사론

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• dynamic_sql_string: 단일 SQL 문 또는 PL/SQL 블록을 담는 문자열 표현식(VARCHAR2 또는 CHAR, NVARCHAR2/NCHAR는 아님).
• INTO 절: 선택 사항입니다. 동적 SQL이 단일 행 SELECT인 경우에만 사용되며, 반환된 값을 변수 또는 레코드에 저장합니다. 선택된 각 열에는 형식이 호환되는 변수가 필요합니다.
• 사용 절: 선택 사항입니다. 바인딩 변수를 제공합니다. 기본 모드는 IN이며, OUT 및 IN OUT은 값을 반환받는 데 사용됩니다.
• RETURNING INTO 절로 돌아가기: RETURNING 절을 포함하는 DML 문과 함께 사용하여 영향을 받는 행 값을 바인드 인수에 캡처합니다.

예 1 : 이 예제에서는 바인드 변수를 사용하는 NDS 문을 이용하여 emp 테이블에서 emp_no '1001'에 해당하는 데이터를 가져옵니다.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

산출

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code 설명 :

• 2-6행: 변수를 선언합니다.
• 8 라인 : 실행 시점에 SQL 문을 구성합니다. SQL 문에는 WHERE 절에 바인드 변수 ':empno'가 포함되어 있습니다.
• 9-11행: EXECUTE IMMEDIATE를 사용하여 작성된 SQL 문을 실행합니다. INTO 절의 변수에는 가져온 값이 저장되고, USING 절은 바인드 변수 :empno에 대한 값을 제공합니다.
• 12-15행: 가져온 값을 표시합니다.

DDL에 동적 SQL 사용

정적 PL/SQL은 CREATE, ALTER, DROP과 같은 DDL을 직접 실행할 수 없습니다. EXECUTE IMMEDIATE는 이러한 문제를 해결하기 위해 문을 문자열로 구성하며, 이는 런타임에 객체 이름이 제공되는 경우에도 유용합니다.

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

객체 이름(테이블, 컬럼, 스키마)은 바인드 변수로 전달할 수 없으므로 문자열로 연결해야 합니다. SQL 인젝션을 방지하기 위해 DBMS_ASSERT.SIMPLE_SQL_NAME 등을 사용하여 입력값을 항상 검증하십시오.

동적 SQL용 DBMS_SQL

PL/SQL은 실행 시점까지 문장의 구조를 알 수 없는 동적 SQL 작업을 위해 DBMS_SQL 패키지를 제공합니다. 동적 SQL을 생성하고 실행하는 과정은 다음과 같은 단계를 포함합니다.

• 커서 열기: 동적 SQL은 다음과 같이 실행됩니다. 커서SQL 문을 실행하려면 먼저 커서를 열어야 합니다.
• SQL 구문 분석: 동적 SQL을 파싱합니다. 이렇게 하면 구문을 확인하고 쿼리를 바로 실행할 수 있도록 준비합니다.
• 바인딩 변수 값: 바인딩 변수가 있는 경우 해당 변수에 값을 할당합니다.
• 열 정의: SELECT 문에서의 상대적 위치를 사용하여 각 열을 정의합니다.
• 실행하다: 파싱된 쿼리를 실행합니다.
• 값 가져오기: 실행된 값을 가져옵니다.
• 커서 닫기: 결과를 불러오면 커서를 닫으세요.

예 1 : 이 예제에서는 DBMS_SQL 문을 사용하여 emp 테이블에서 emp_no '1001'에 해당하는 데이터를 가져옵니다. EXCEPTION 블록은 오류가 발생하더라도 커서를 닫습니다.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

산출

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code 설명 :

• 1-8행: 변수 선언.
• 10 라인 : SQL 문을 구성하는 방법.
• 11 라인 : DBMS_SQL.OPEN_CURSOR를 사용하여 커서를 열면 열린 커서의 ID가 반환됩니다.
• 12 라인 : 커서가 열리면 SQL 구문 분석이 시작됩니다.
• 13 라인 : 바인딩 값 '1001'이 ':empno' 대신 할당됩니다.
• 14-17행: 상대적 위치에 따른 열 정의: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
• 18 라인 : DBMS_SQL.EXECUTE를 사용하여 쿼리를 실행하면 처리된 레코드 수가 반환됩니다.
• 19-32행: 반복문을 사용하여 레코드를 가져옵니다. FETCH_ROWS는 더 이상 행이 없으면 0을 반환하여 반복문을 종료합니다.
• 예외 블록: 오류 발생 시 열려 있는 커서로 인해 메모리 누수가 발생하지 않도록 커서가 닫히도록 합니다.

NDS와 DBMS_SQL: 언제 어떤 것을 사용해야 할까요?

두 접근 방식 모두 런타임에 SQL을 실행하지만, 적합한 상황이 다릅니다.

• 네이티브 동적 SQL(EXECUTE IMMEDIATE / OPEN-FOR)을 사용하십시오. 컴파일 시점에 입력 및 출력의 개수와 데이터 형식을 알 수 있는 경우, 더 빠르고 가독성이 좋으며 코드 양도 줄어듭니다.
• DBMS_SQL을 사용하세요 실행 시점까지 구조를 알 수 없는 경우, 예를 들어 선택된 열 또는 바인드 변수의 수가 변하는 쿼리(메서드 4 동적 SQL이라고 함) 또는 단일 32K VARCHAR2 변수에 담기에는 너무 큰 문장이 있는 경우에 해당합니다.

자주 묻는 질문