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

작성자 : 피오나 브라운 피오나 브라운
Hours 업데이트

동적 SQL이란 무엇입니까?

동적 SQL 런타임에 명령문을 생성하고 실행하기 위한 프로그래밍 방법론입니다. 이는 요구 사항에 따라 런타임에 SQL 문이 생성되고 실행되는 범용 및 유연한 프로그램을 작성하는 데 주로 사용됩니다.

동적 SQL을 작성하는 방법

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

  1. NDS – 네이티브 동적 SQL
  2. DBMS_SQL

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

네이티브 동적 SQL은 동적 SQL을 작성하는 더 쉬운 방법입니다. 'EXECUTE IMMEDIATE' 명령을 사용하여 런타임에 SQL을 만들고 실행합니다. 하지만 이 방법을 사용하려면 런타임에 사용할 변수의 데이터 유형과 개수를 미리 알아야 합니다. 또한 DBMS_SQL과 비교할 때 더 나은 성능과 덜 복잡한 기능을 제공합니다.

통사론 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • 위 구문은 EXECUTE IMMEDIATE 명령을 보여줍니다.
  • INTO 절은 선택 사항이며 동적 SQL에 값을 가져오는 select 문이 포함된 경우에만 사용됩니다. 변수 유형은 select 문의 변수 유형과 일치해야 합니다.
  • USING 절은 선택 사항이며 동적 SQL에 바인드 변수가 포함된 경우에만 사용됩니다.

예제 1: 이 예에서는 NDS 문을 사용하여 emp_no '1001'에 대한 emp 테이블에서 데이터를 가져오겠습니다.

NDS - 즉시 실행

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
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_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

산출

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

코드 설명 :

  • 코드 라인 2-6: 변수를 선언합니다.
  • 코드 라인 8: 런타임에 SQL을 프레이밍합니다. SQL에는 where 조건 ':empno'에 바인드 변수가 포함되어 있습니다.
  • 코드 라인 9: NDS 명령 'EXECUTE IMMEDIATE'를 사용하여 프레임된 SQL 텍스트 실행(코드 라인 8에서 수행됨)
  • 'INTO' 절의 변수(lv_emp_name, ln_emp_no, ln_salary, ln_manager)는 SQL 쿼리(emp_name, emp_no, 급여, 관리자)에서 가져온 값을 보유하는 데 사용됩니다.
  • 'USING' 절은 SQL 쿼리(:emp_no)의 바인드 변수에 값을 제공합니다.
  • 코드 라인 10-13: 가져온 값을 표시합니다.

동적 SQL용 DBMS_SQL

PL/SQL은 동적 SQL을 사용할 수 있는 DBMS_SQL 패키지를 제공합니다. 동적 SQL을 만들고 실행하는 프로세스에는 다음 프로세스가 포함됩니다.

  • 커서 열기: 동적 SQL은 SQL과 동일한 방식으로 실행됩니다. 커서. 따라서 SQL 문을 실행하려면 커서를 열어야 합니다.
  • 구문 분석 SQL: 다음 단계는 동적 SQL을 구문 분석하는 것입니다. 이 프로세스에서는 구문을 확인하고 쿼리를 실행할 준비를 유지합니다.
  • 바인드 변수 값: 다음 단계는 바인드 변수에 대한 값이 있는 경우 할당하는 것입니다.
  • 열 정의: 다음 단계는 select 문에서 상대 위치를 사용하여 열을 정의하는 것입니다.
  • 실행: 다음 단계는 구문 분석된 쿼리를 실행하는 것입니다.
  • 값 가져오기: 다음 단계는 실행된 값을 가져오는 것입니다.
  • 커서 닫기: 결과를 가져오면 커서를 닫아야 합니다.

예제 1: 이 예에서는 DBMS_SQL 문을 사용하여 emp_no '1001'에 대한 emp 테이블에서 데이터를 가져오겠습니다.

동적 SQL용 DBMS_SQL

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;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
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);

동적 SQL용 DBMS_SQL

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,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||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_ClIRSOR(ln_cursor_id);

END:
/

산출

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

코드 설명 :

  • 코드 라인 1-9: 변수 선언.
  • 코드 라인 10: SQL 문을 프레이밍합니다.
  • 코드 라인 11: DBMS_SQL.OPEN_CURSOR를 사용하여 커서를 엽니다. 열린 커서 ID를 반환합니다.
  • 코드 라인 12: 커서가 열린 후 SQL이 구문 분석됩니다.
  • 코드 라인 13: 바인드 변수 '1001'이 ':empno' 대신 커서 ID에 할당됩니다.
  • 코드 라인 14-17: SQL 문에서의 상대 위치를 기반으로 열 이름을 정의합니다. 우리의 경우 상대 위치는 (1) emp_name, (2) emp_no (3) 급여 (4) 관리자입니다. 따라서 이 위치를 기반으로 목표 변수를 정의합니다.
  • 코드 라인 18: DBMS_SQL.EXECUTE를 사용하여 쿼리를 실행합니다. 처리된 레코드 수를 반환합니다.
  • 코드 라인 19-33: 루프를 사용하여 레코드를 가져오고 이를 표시합니다.
  • 코드 라인 20: DBMS_SQL.FETCH_ROWS는 처리된 행에서 하나의 레코드를 가져옵니다. 반복적으로 호출하여 모든 행을 가져올 수 있습니다. 행을 가져올 수 없으면 0을 반환하고 루프를 종료합니다.

제품 개요

이번 섹션에서는 동적 SQL과 DYNAMIC SQL을 실행하는 방법에 대해 논의했습니다. 또한 두 가지 방법으로 동적 SQL을 실행하는 다양한 단계를 살펴보았습니다. 또한 런타임에 실행을 수행하기 위해 NDS와 DBMS_SQL 방식 모두에서 동일한 시나리오를 처리하는 예도 살펴보았습니다.

이 게시물을 요약하면 다음과 같습니다.