8 동적 SQL 문을 코딩

동적 SQL 은 런타임에 동적으로 SQL 문을 구축 할 수있는 프로그래밍 기술입니다. 컴파일시 SQL 문의 전체 텍스트를 알 수 없으므로 동적 SQL 을 사용하여보다 일반적인 목적의 유연한 응용 프로그램을 만들 수 있습니다. 예를 들어 dynamic SQL 을 사용하면 런타임까지 이름을 알 수없는 테이블에서 작동하는 프로 시저를 만들 수 있습니다.,

Oracle 포함한 두 가지 방법으로 구현하는 dynamic SQL 에 PL/SQL 응용 프로그램

• 네이티브 dynamic SQL,당신은 장소는 동적 SQL 문으로 직접 PL/SQL 블록입니다.
• DBMS_SQL패키지의 호출 절차.

이 장에서는 다음 주제를 다룹니다.

• “동적 SQL 이란 무엇입니까?”
• “동적 SQL 을 사용하는 이유는 무엇입니까?,”
• “Dynamic SQL 사용하여 시나리오 기본 Dynamic SQL”
• “선택 사 Native Dynamic SQL 및 DBMS_SQL 패키지”
• 사용”Dynamic SQL 에서 이외의 언어 PL/SQL”
• 사용”PL/SQL 레코드에서 SQL 삽입 업데이트 문”

자세한 내용을 확인하실 수 있습니다에 대해DBMS_SQL패키지에서 Oracle9i 공급 PL/SQL 패키지 및 유형을 참조합니다.

동적 SQL 은 무엇입니까?

Dynamic SQL 을 사용하면 런타임까지 전체 텍스트를 알 수없는 SQL 문을 참조하는 프로그램을 작성할 수 있습니다., 동적 SQL 에 대해 자세히 논의하기 전에 정적 SQL 의 명확한 정의는 동적 SQL 을 이해하기위한 좋은 출발점을 제공 할 수 있습니다. 정적 SQL 문은 실행에서 실행으로 변경되지 않습니다. 의 전체 텍스트 static SQL 문을 알려져 있에서 컴파일하는 다음과 같은 이점을 제공합니다.

• 성공적으로 컴파일한지 확인합 SQL 문을 참조가 올바른 데이터베이스 객체입니다.
• 성공적인 컴파일은 데이터베이스 개체에 액세스하는 데 필요한 권한이 있는지 확인합니다.,
• 정적 SQL 의 성능은 일반적으로 동적 SQL 보다 우수합니다.

이러한 장점 때문에,당신은 사용해야 하는 dynamic SQL 사용할 수 없는 경우에만 static SQL 신의 목표를 달성 또는 사용하는 경우 정적 SQL 복잡해 dynamic SQL. 그러나 정적 SQL 에는 동적 SQL 로 극복 할 수있는 한계가 있습니다. PL/SQL 프로 시저에서 실행해야하는 SQL 문의 전체 텍스트를 항상 알지 못할 수도 있습니다., 귀하의 프로그램이 사용자 입력을 받을 정의하는 SQL 문을 실행,또는 당신의 프로그램이 필요할 수 있을 완료 처리 작업을 결정이 올바른 작업입니다. 이러한 경우 동적 SQL 을 사용해야합니다.

예를 들어,보고에서 응용 프로그램 데이터 웨어하우스 환경지 않을 수도 있습 알고 정확한 테이블에 이름을 때까지합니다., 이러한 테이블 수에 따라 이름이 시작 연월의 분기에,예를 들어INV_01_1997,INV_04_1997,INV_07_1997,INV_10_1997,INV_01_1998니다. 보고 응용 프로그램에서 동적 SQL 을 사용하여 런타임에 테이블 이름을 지정할 수 있습니다.

사용자가 선택할 수있는 정렬 순서로 복잡한 쿼리를 실행하고 싶을 수도 있습니다., 코드 쿼리에 두 번,서로 다른ORDER BY절을 작성할 수 있습니다 쿼리를 동적으로 포함하는 지정한ORDER BYclause.

동적 SQL 프로그램은 다시 컴파일 할 필요없이 데이터 정의의 변경 사항을 처리 할 수 있습니다. 이렇게하면 정적 SQL 보다 동적 SQL 이 훨씬 유연 해집니다. Dynamic SQL 을 사용하면 sql 을 다른 환경에 쉽게 적용 할 수 있으므로 재사용 가능한 코드를 작성할 수 있습니다..,

Dynamic SQL 을 사용하면 순수 정적 sql 프로그램에서 지원되지 않는 DDL(data definition language)문 및 기타 SQL 문을 실행할 수도 있습니다.

동적 SQL 을 사용하는 이유는 무엇입니까?

사용해야 합니다 dynamic SQL 경우에는 static SQL 을 지원하지 않을 수행할 작업 또는 경우에 당신은하지 않는 정확하게 알고 있는 SQL 문을 실행해야 합니다에 의해 PL/SQL 절차입니다. 이러한 SQL 문은 사용자 입력에 따라 달라 지거나 프로그램에서 수행 한 처리 작업에 따라 달라질 수 있습니다.,DL and SCL Statements in PL/SQL

In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:

• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
• Session control language (SCL) statements, such as ALTER SESSION and SET ROLE
  
  See Also:

  Oracle9i SQL Reference for information about DDL and SCL statements.,

또한,만 사용할 수 있습니다TABLE절SELECT문을 통해 dynamic SQL. 예를 들어,다음 PL/SQL 블록에 포함SELECT문을 사용하는TABLE절하고 네이티브 dynamic SQL:

실행하는 동적 쿼리

사용할 수 있는 dynamic SQL 응용 프로그램을 만들 수 있는 실행하는 동적 쿼리,그의 전체 텍스트까지도 알려지지 않 runtime., 다양한 유형의 응용 프로그램을 사용할 필요가 동적 쿼리를 포함하여:

• 할 수 있는 응용 프로그램 사용자가 입력 또는 선택한 쿼리를 검색 또는 정렬 기준에 런타임
• 할 수 있는 응용 프로그램 사용자가 입력 또는 선택 최적화 프로그램에서 힌트를 실행할 시간
• 응용 프로그램를 쿼리하는 데이터베이스는 데이터의 정의 테이블은 지속적으로 변경
• 응용 프로그램를 쿼리하는 데이터베이스는 새로운 테이블을 만들이 자주

예를 들어, 보”쿼리를 사용하여 동적 SQL:”예를 들어,보 쿼리의 예에서는”A Dynamic SQL 사용하여 시나리오 기본 Dynamic SQL”.,

참조 데이터베이스를 개체를 존재하지 않는 편집

다양한 유형의 응용 프로그램과 상호 작용해야 합 생성되는 데이터는 정기적으로 실시합니다. 예를 들어 컴파일 타임에 테이블 정의를 알 수 있지만 테이블의 이름은 알 수 없습니다.

동적 SQL 은 테이블 이름을 지정하기 위해 런타임까지 기다릴 수 있기 때문에이 문제를 해결할 수 있습니다. 예를 들어,”동적 SQL 은 무엇입니까?”에서 설명한 샘플 데이터웨어 하우스 응용 프로그램에서”매 분기마다 새 테이블이 생성되며이 테이블은 항상 동일한 정의를가집니다., 할 수 있는 사용자의 이름을 지정 테이블에서 런타임으로 역동적 SQL 쿼리를 유사한 다음과 같다:

최적화하는 동적으로 실행

사용할 수 있는 dynamic SQL 을 구축하는 SQL 문을에서 방법을 최적화하여 실행을 연결하는 힌트로서 SQL 문을 동적으로. 이를 통해 재 컴파일을 필요로하지 않고 현재 데이터베이스 통계를 기반으로 힌트를 변경할 수 있습니다.,

예를 들어,다음과 같은 절차를 사용하여 변수라는a_hint사용자가를 전달하는 힌트 옵션을SELECT성명:

이 예제에서는 사용자가 전달할 수 있 다음과 같은 값을a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'또는 다른 모든 유효한 힌트 옵션입니다.

실행하는 동적 PL/SQL 블록

사용할 수 있습니다EXECUTEIMMEDIATE문을 실행하는 익명 PL/SQL 블록입니다. 런타임에 블록 내용을 구성하여 유연성을 추가할 수 있습니다.

예를 들어,ythroughthroughou 원하는 응용 프로그램을 작성하는 이벤트 번호를 전달합을 처리기 위해 이벤트입니다. 처리기의 이름은EVENT_HANDLER_event_num 형식이며,여기서 event_num 은 이벤트의 번호입니다., 중 하나를 구현하는 디스패처로 문 스위치,코드 핸들에 의해 각 이벤트를 만드는 정적화하여 적절한 처리기입니다. 이 코드는 새 이벤트에 대한 핸들러가 추가 될 때마다 디스패처 코드를 업데이트해야하기 때문에 매우 확장 가능하지 않습니다.,

네이티브를 사용하 dynamic SQL 작성할 수 있습니다 작고,더 가동 가능한 이벤트 dispatcher 은 다음과 같습니다.

수행하는 동적 작업을 호출자-권

를 사용하여 호출자 권한 기능을 가진 동적 SQL,할 수 있는 응용 프로그램을 구축하는 문제 dynamic SQL 문서 권한과의 스키마 호출자. 이러한 두 가지 기능,호출자-권리 및 동적 SQL 을 사용하면 다시 사용할 수 있는 응용 프로그램을 구축 하위 사용할 수 있는 액세스 호출자의 데이터 모듈입니다.,

Dynamic SQL 사용하여 시나리오 기본 Dynamic SQL

이 시나리오는 방법을 보여줍니다 다음 작업을 수행하도록 사용하여 기본 dynamic SQL:

• DDL 실행 및 DML 운영
• 실행하는 단 하나 줄이고 여러 행 쿼리

데이터베이스에서 이 시나리오는 회사의 인적자원 데이터베이스(라는hr)다음과 같은 데이터 모델:

은 마스터 테이블의 명명된offices목록을 포함합의 모든 회사다., offices테이블은 다음과 같은 정의:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

는 여러emp_위치 테이블이 포함되어 있는 직원의 정보로,어디에 위치는 이름의 도시는 곳에 사무실이 위치합니다. 예를 들어,테이블 라는emp_houston포함 직원을 위한 정보를 회사의 Houston office 면,테이블 라는emp_boston포함 직원을 위한 정보를 회사의 보스턴 사무실입니다.,

각emp_위치 테이블은 다음과 같은 정의:

다음 섹션에서 설명한 다양한 기본 dynamic SQL 작업을 수행할 수 있는 데이터는hr데이터베이스입니다.

샘플 DML 작업을 사용하여 기본 Dynamic SQL

다음과 같은 기본 dynamic SQL 절차은 인상을 모든 직원으로 특정 작업 제목:

샘플 DDL 작업을 사용하여 기본 Dynamic SQL

EXECUTE IMMEDIATE문을 수행할 수 있습 DDL 작업입니다., 예를 들어,다음 절차에 따라 추가 사무실 위치:

다음과 같은 절차를 삭제합 사무실 위치:

샘플 단 하나 줄 쿼리를 이용하여 Dynamic SQL

EXECUTEIMMEDIATE문을 수행할 수 있는 동적인 단 하나 줄 쿼리를 처리합니다. 지정할 수 있습니다 바인딩에서 변수를USING절하고 가져 오기 결과 행으로 대상에서 지정한INTO절의 문입니다.,

는 다음의 함수를 검색합니다수의 직원이 특정 위치에 수행된 작업:

샘플 여러 행 쿼리를 이용하여 Dynamic SQL

OPEN-FOR,FETCH,andCLOSE문을 수행할 수 있는 동적인 여러 행 쿼리를 처리합니다., 예를 들어,다음과 같은 절차의 목록은 모든 직원으로 특정 작업에 지정된 위치:

사이의 선택 기본 Dynamic SQL 및 DBMS_SQL 패키지

Oracle 에서 제공하는 두 가지 방법을 사용하여 동적 SQL 내 PL/SQL:원 dynamic SQLDBMS_SQL패키지입니다. 네이티브 동적 SQL 은 PL/SQL 코드에 직접 동적 SQL 문을 배치 할 수 있습니다. 이러한 동적문을 포함 DML 문(쿼리 포함),PL/SQL 익명의 블록,DDL 문을,트랜잭션 컨트롤 진술,그리고 세션 제어의 문이 있습니다.,

To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.

DBMS_SQL패키지는 sql 문을 동적으로 실행하는 API 를 제공하는 PL/SQL 라이브러리입니다. DBMS_SQL패키지에는 커서를 열고 커서를 구문 분석하고 공급 바인딩 등의 절차가 있습니다. DBMS_SQL패키지를 사용하는 프로그램은 동적 SQL 작업을 수행하기 위해 이 패키지를 호출합니다.

다음 섹션에서는 두 가지 방법의 장점에 대한 자세한 정보를 제공합니다.,

의 이점은 기본 Dynamic SQL

네이티브 dynamic SQL 통해 다음과 같은 이점을 제공합DBMS_SQL패키지:

네이티브 Dynamic SQL 사용하기 쉬운

기 때문에 기본 dynamic SQL 통합과 SQL,당신은 그것을 사용할 수 있는 방법과 동일하게 사용할 static SQL 내 PL/SQL 코드입니다. 네이티브 동적 SQL 코드는 일반적으로DBMS_SQL패키지를 사용하는 동등한 코드보다 더 작고 읽기 쉽습니다.,

와DBMS_SQL패키지를 호출해야 합니다 많은 절차와 함께 엄격한 시퀀스를 만들고,간단한 작업을 많이 필요로의 코드입니다. 대신 네이티브 동적 SQL 을 사용하여 이러한 복잡성을 피할 수 있습니다.

표 8-1 은DBMS_SQL패키지 및 기본 동적 SQL 을 사용하여 동일한 작업을 수행하는 데 필요한 코드 양의 차이를 보여줍니다.,

Table8-1 코드의 비교 DBMS_SQL 및 네이티브 패키지는 Dynamic SQL

네이티브 Dynamic SQL 이보다 더 빨리 DBMS_SQL

네이티브 dynamic SQL PL/SQL 수행 비교적의 성능 static SQL 기 때문에,PL/SQL 통역 지원 기능이 내장되어 있습니다. 네이티브 동적 SQL 을 사용하는 프로그램은DBMS_SQL패키지를 사용하는 프로그램보다 훨씬 빠릅니다. 일반적으로 네이티브 동적 SQL 문은 동등한DBMS_SQL호출보다 1.5~3 배 더 우수한 성능을 발휘합니다. (응용 프로그램에 따라 성능 향상이 달라질 수 있습니다.,)

네이티브 dynamic SQL 뭉치 문 준비,바인딩 및 실행 단계로 작업을 최소화할 수 있는 데이터 복사 및 절차를 호출 오버헤드 및 성능이 향상됩니다.

DBMS_SQL패키지를 기반으로는 절차적 API 고 발생하는 높은 절차는 전화 및 데이터 복사 오버헤드가 발생합니다. 매번 당신은 bind 변수의DBMS_SQL패키지 복사본 PL/SQL bind 변수로 사용할 공간 동안 실행됩니다., 각 시간을 가져,데이터 복사 공간으로 관리하는DBMS_SQL패키지한 후 인출된 데이터를 복사,한 번에 하나의 열,적절한 PL/SQL 변수의 결과로,상당한 오버헤드가 발생합니다.

성능 팁:바인딩을 사용 변수를

중 하나를 사용할 경우 기본 dynamic SQL 또는DBMS_SQL패키지를 사용하여 성능을 향상시킬 변수를 바인딩하기 때문에,bind 변수의 허용을 오라클을 공유하는 하나의 커서 위한 여러 SQL 문을 수행합니다.,

예를 들어,다음과 같은 기본 dynamic SQL 코드를 사용하지 않는 bind 변수:

에 대한 각각의 고유한my_deptnovariable,새로운 커서 만든 원인,리소스 경쟁과 가난한 성과입니다. 대신,bindmy_deptno로 bind 변수:

여기에,같은 커서가 재사용을 위한 다른 값을 바인딩my_deptno,성능을 개선하고 scalabilty.,

네이티브 Dynamic SQL 을 지원하는 사용자 정의 형식

네이티브 dynamic SQL 을 지원하는 모든 종류의 지원에 의해 정적에서 SQL PL/SQL 포함하여,사용자 정의 형식과 같은 사용자 정의 개체,컬렉션,그리고REFs. DBMS_SQL패키지는 이러한 사용자 정의 유형을 지원하지 않습니다.

네이티브 Dynamic SQL 지원 가져오기로 기록

네이티브 dynamic SQL 고 및 정 SQL 모두 지원 가져오기로 기록하지만,DBMS_SQL패키지지 않습니다. 네이티브 동적 SQL 을 사용하면 쿼리로 인한 행을 PL/SQL 레코드로 직접 가져올 수 있습니다.,

다음 예에서 행 쿼리에서 가져온 있으로emp_rec기록:

의 장점 DBMS_SQL 패키지

DBMS_SQL패키지를 통해 다음과 같은 이점을 제공합니 native dynamic SQL:

DBMS_SQL 이에서 지원되는 클라이언트 측 프로그램

DBMS_SQL패키지에서 지원되는 클라이언트 측 프로그램,그러나 기본 dynamic SQL 하지 않습니다., 모든 통화DBMS_SQL패키지에서 클라이언트 측 프로그램으로 변환하 PL/SQL 원격 절차를 통화(RPC);이러한 호출이 발생해야하는 경우 bind 변수를 정의한 변수는,또는 실행하는 문입니다.

DBMS_SQL 지원하는지 설명

DESCRIBE_COLUMNS절차에서DBMS_SQL패키지를 설명하는 데 사용할 수 있는 열의를 위해 커서를 열고 분석을 통해DBMS_SQL. 이 기능은 SQL*Plus 의DESCRIBE명령과 유사합니다., 기본 동적 SQL 에는DESCRIBE시설이 없습니다.

DBMS_SQL 지원하는 여러 행 업데이트 및 삭제와 함께 돌아온 절

DBMS_SQL패키지 지원하는 문RETURNING절을 업데이트하거나 삭제하는 여러 개의 행이 있습니다. 기본 동적 SQL 은 단일 행이 반환되는 경우RETURNING절만 지원합니다.,

DBMS_SQL Supports SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

DBMS_SQL Lets You Reuse SQL Statements

The PARSE procedure in the DBMS_SQL package parses a SQL statement once., 초기 구문 분석 후 다른 바인드 인수 세트와 함께 문을 여러 번 사용할 수 있습니다.

Native dynamic SQL 은 명령문을 사용할 때마다 SQL 문을 준비하며 일반적으로 구문 분석,최적화 및 계획 생성이 포함됩니다. 추가 준비 작업에는 작은 성능 저하가 발생하지만 일반적으로 속도 저하는 기본 동적 SQL 의 성능 이점보다 큽니다.,

의 예 DBMS_SQL 패키지 코드 및 네이티브 Dynamic SQL 코드

다음 예제를 설명하는 차이점에 코드를 완료하는 데 필요한 작업으로는DBMS_SQL포장 및 네이티브 dynamic SQL. 특히,다음과 같은 유형의 예를 표시됩니다:

• 쿼리
• DML 운영
• DML 반환 운영

에서 일반적으로,네이티브 dynamic SQL 코드를 더 읽기 쉽고 컴팩트한할 수 있는 개발자의 생산성을 향상.,

쿼리를 사용하여 동적 SQL:예

다음 예에서는 동 질의문으로 하나 bind 변수(:jobname)및 두 개의 열을 선택하(ename및sal):

stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname';

이 예는 쿼리를 위해 직원과 작업을 설명SALESMANjobcolumnemp테이블. 표 8-2 는DBMS_SQL패키지 및 기본 동적 SQL 을 사용하여이 쿼리를 수행하는 샘플 코드를 보여줍니다.,

테이블 8-2 쿼리를 사용하 DBMS_SQL 및 네이티브 패키지는 Dynamic SQL

을 수행하 DML 를 사용하여 동적 SQL:예

다음 예에서 동적인INSERT문 테이블을 가진 세 개의 열이:

stmt_str := 'INSERT INTO dept_new VALUES (:deptno, :dname, :loc)';

이 예에서 새로운 행는 열에 있는 값 PL/SQL 변수를deptnumber,deptname,andlocation. 표 8-3 은DBMS_SQL패키지 및 기본 동적 SQL 을 사용하여이 DML 작업을 수행하는 샘플 코드를 보여줍니다.,

테이블 8-3DML 를 사용하여 작업 DBMS_SQL 및 네이티브 패키지는 Dynamic SQL

을 수행하 DML 으로 돌아온 절을 사용하여 동적 SQL:예

다음 예제를 사용하여 동적UPDATE문의 위치를 업데이트 부서,다음의 이름을 반환합니다 department:

stmt_str := 'UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname';

테이블 8-4 쇼 샘플을 수행하는 코드를 사용하여 이 작업을 모두DBMS_SQL포장 및 네이티브 dynamic SQL.,

테이블 8-4DML 반환를 사용하여 작업 DBMS_SQL 및 네이티브 패키지는 Dynamic SQL

를 사용하여 동적 SQL 이외의 언어로 PL/SQL

지만 이 장에서 설명 PL/SQL 지원을 위한 동적 SQL,당신을 호출 할 수 있습 dynamic SQL 에서 다른 언어:

• 사용할 경우에는 C/C++,당신을 호출 할 수 있습 dynamic SQL Oracle 전화 인터페이스(OCI), 또는 사용할 수 있습니다 Pro*C/C++리컴파일러를 추가하는 dynamic SQL 확장하여 C 코드입니다.
• COBOL 을 사용하는 경우 Pro*COBOL 프리 컴파일러를 사용하여 cobol 코드에 동적 SQL 확장을 추가 할 수 있습니다.,
• Java 를 사용하는 경우 jdbc 와 함께 동적 SQL 을 사용하는 응용 프로그램을 개발할 수 있습니다.

응용 프로그램이 있는 경우 사용하는 OCI,Pro*C/C++,또는 프로*COBOL 실행하는 dynamic SQL,당신은 로의 전환을 고려해야 native dynamic SQL 내부 PL/SQL 저장된 절차와 기능이 있습니다. 클라이언트 측 응용 프로그램에서 동적 SQL 작업을 수행하는 데 필요한 네트워크 왕복으로 인해 성능이 저하 될 수 있습니다. 저장 프로 시저가 서버에 상주 할 수 있으므로 네트워크 오버 헤드가 제거됩니다., OCI,Pro*C/C++또는 Pro*COBOL 응용 프로그램에서 pl/SQL 저장 프로 시저 및 저장된 함수를 호출 할 수 있습니다.,br>see

정보를 위해 호출에 대한 오라클 저장된 절차 및 저장 기능이 다양한 언어에서 사이트를 참조하시기 바랍니다:

• Oracle 전화 인터페이스 프로그래머의 안내
• Pro*C/C++리컴파일러한 프로그래머의 안내
• 프로*COBOL 리컴파일러한 프로그래머의 안내
• Oracle9i Java 저장된 절차 개발자 안내

를 사용하여 PL/SQL 레코드에서 SQL 삽입 업데이트 문

할 수 있지만 열거하고 각 분야의 PL/SQL 기록을 삽입하거나 업데이트하는 경우에 행 테이블과 코드 없는 특히 읽거나 유지할 수 있습니다., 대신 이러한 명령문에서 PL/SQL 레코드를 직접 사용할 수 있습니다. 가장 편리한 기술은%ROWTYPE 특성을 사용하여 레코드를 선언하여 SQL 테이블과 정확히 동일한 필드를 갖도록하는 것입니다.

이 있지만 기술하는 데 도움이 통합 PL/SQL 변수와 형식과 더 밀접한 SQL DML 문을 사용할 수 없습니다 PL/SQL 기록으로 bind 변수에 dynamic SQL 문을 수행합니다.피>