Программирование >>  Oracle 

1 ... 266 267 268 [ 269 ] 270 271 272 ... 469


Динамический SQL 1219

DEPTNO ENAME

2 0 ADAMS

CLERK

30 ALLEN

SALESMAN

3 0 BLAKE

MANAGER

10 CLARK

MANAGER

30 JAMES

CLERK

30 MARTIN

SALESMAN

30 WARD

SALESMAN

7 rows selected.

scott@TKYTE816> exec my pkg.get emps(p deptno=> 10, p cursor => :C) PL/SQL procedure successfully completed.

DEPTNO ENAME JOB

10 CLARK MANAGER 10 KING PRESIDENT 10 MILLER CLERK

Если созданный динамически запрос возвращает более одной строки, надо использовать представленный выше метод, а не оператор EXECUTE IMMEDIATE.

Итак, по сравнению с представленными выше подпрограммами пакета DBMS SQL, использование операторов EXECUTE IMMEDIATE и OPEN FOR существенно упрощает написание программ. Значит ли это, что пакет DBMS SQL больше использовать не придется? Определенно, - не значит. Представленные выше примеры показывают, насколько простым может быть использование динамического SQL, если количество связываемых переменных известно во время компиляции. Если бы мы этого не знали, то не смогли бы использовать оператор EXECUTE IMMEDIATE так просто, как в представленных примерах. Для этого оператора количество связываемых переменных надо знать заранее. Пакет DBMS SQL в этом отношении обеспечивает большую гибкость. Помимо количества связываемых переменных необходимо знать еще и столбцы, входящие в результат выполнения SQL-оператора SELECT. Если количество и типы этих столбцов неизвестны, использовать оператор EXECUTE IMMEDIATE тоже не удастся. Можно будет использовать оператор OPEN FOR, если клиент, получающий курсорную переменную, не является другой подпрограммой на языке PL/SQL.

Оператор EXECUTE IMMEDIATE обеспечит более высокую производительность по сравнению с пакетом DBMS SQL для всех операторов, анализируемых и выполняемых однократно (все наши примеры пока были именно такими). Для выполнения подпрограмм пакета DBMS SQL в этом отношении требуется больше ресурсов, потому что нужно вызвать пять или шесть процедур там, где достаточно одного выполнения оператора EXECUTE IMMEDIATE.

Однако пакет DBMS SQL обеспечивает более высокую производительность, если его процедуры используются для многократного выполнения одного и того же проанализированного оператора. Оператор EXECUTE IMMEDIATE не позволяет повторно использовать проанализированные операторы. Он всегда разбирает оператор, и расходы ресурсов на повторные выполнения этой операции со временем превышают расхо-



1220 Глава 16

ды на дополнительные вызовы процедур. Особое значение это приобретает в многопользовательской среде. Наконец, операторы EXECUTE IMMEDIATE и OPEN не позволяют обрабатывать массивы так же просто, как подпрограммы пакета DBMS SQL и, как будет продемонстрировано, одно это может принципиально повлиять на производительность.

Сравнение пакета DBMS SQL и встроенного динамического SQL

Рассмотрев способы реализации подпрограмм с помощью пакета DBMSSQL И встроенного динамического SQL, поговорим о том, когда следует использовать каждый из способов. Решение зависит от следующих факторов.

Известно ли на этапе компиляции, какие связываемые переменные придется пользовать? Если - нет, надо выбрать пакет DBMS SQL.

Известны ли на этапе компиляции все выходные данные? Если - нет, нужно отдать предпочтение пакету DBMS SQL.

Надо ли использовать курсорную переменную для возврата результирующего множества из хранимой процедуры? Если - да, придется использовать оператор OPEN FOR.

Будет ли формируемый динамически оператор выполняться в сеансе один раз многократно. Если один и тот же динамически формируемый оператор выполняется несколько раз, пакет DBMS SQL обеспечит более высокую производительность.

Надо ли использовать обработку массивов для динамически формируемых операторов.

Три из этих факторов мы рассмотрим ниже (на самом деле - даже четыре, поскольку многократное выполнение оператора мы рассмотрим как в случае обработки массивов, так и без оной).

Связываемые переменные

Связываемые переменные существенно влияют на производительность системы. Если они не используются, производительность недопустимо низка. Метод автоматической подстановки связываемых переменных (auto binding) путем установки соответствующего значения параметра CURSOR SHARING был рассмотрен в главе 10. Это несколько улучшает ситуацию, но приводит к избыточному расходованию ресурсов, поскольку сервер вынужден переписывать запрос и удалять информацию, существенную для оптимизатора, которую можно было бы оставить при явном включении связываемых переменных в код.

Предположим, необходимо создать процедуру, динамически создающую запрос на основе введенных пользователем данных. Запрос всегда будет возвращать однотипнге результаты (тот же список выбора), но конструкция WHERE будет меняться в зависимости от входных данных. Из соображений производительности необходимо использо-



Динамический SQL

1221

вать связываемые переменные. Как это сделать с помощью встроенного динамического и средств пакета DBMS SQL? Чтобы представить методы, начну со спецификации подпрограммы. Разрабатываемая процедура будет иметь следующий вид:

replace package dyndemo

scott@TKYTE816>

2 as

create or

3 4 5

7 8 9 10 11 12 13 14 15

17 18 19 20 21

23 24 25

type array is table of varchar2(2000);

DO QUERY будет динамически запрашивать таблицу и обрабатывать результаты. Ее можно вызвать следующим образом:

dyn demo.do query( array(ename, job), arrayClike, =

arrayC%A%, CLERK));

* для выполнения запроса:

* select * from emp where ename like %A% and job

* например.

procedure do query(p cnames in array,

p operators in array,

p values in array);

CLERK

end;

Package created.

Вполне естественно реализовать ее с помощью DBMSSQL - для таких ситуаций и создавался этот пакет. Можно организовать цикл по массивам столбцов и значений и построить конструкцию WHERE. Затем проанализировать запрос и выполнить еще один цикл по массивам для связывания значений переменных. После этого выполнить оператор, выбрать строки и обработать их. Это можно записать следующим образом:

scott@TKYTE816> create or replace package body dyn demo

3 4 5

7 8 9 10

11 12 13

Реализация диначеского запроса с неизвестные связываем переменные средствам DBMS SQL

g cursor int default dbms sql.open cursor;

procedure do query(p cnames in array,

p operators in array, p values in array)

к

к

к



1 ... 266 267 268 [ 269 ] 270 271 272 ... 469

© 2006 - 2025 pmbk.ru. Генерация страницы: 0
При копировании материалов приветствуются ссылки.
Яндекс.Метрика