1222

Глава 16

14 is

15 l ery long;

16 l sep varchar2(20) default where ;;

17 l comma varchar2(1) default ;

18 l status int;

19 l colValue varchar2(4000);

20 begin

21 /*

22 * Это наш постоянный список выбора - мы всегда 2 3 * выбаем эти три столбца. Изменяются

2 4 * условия выбора.

25 */

26 l query := select ename, empno, job from emp; 27

28 /*

2 9 * Мы стро условие, сначала

3 0 * помещая в заос конструкцию:

31 *

32 * ename operator :bvX

33 *

34 */

35 for i in 1 .. p cnames.count loop

3 6 l query := l query l sep p cnames(i) И М

3 7 p operators(i) И М

38 :bv i;

3 9 l sep : = and ;

40 end loop;

42 /*

43 * Теперь можно анализировать заос

44 */

45 dbms sql.parse(g cursor, l ery, dbms sql.native);

47 /*

48 * и определять столбцы результата. Все три столбца

49 * выбираются в переменные типа VARCHAR2.

50 */

51 for i in 1 .. 3 loop

52 dbms sql.define column(g cursor, i, l colValue, 4000);

53 end loop;

55 /*

5 6 * Теперь можно связать входные переменные запроса

57 */

58 for i in 1 .. p cnames.count loop

59 dbms sql.bind variable(g cursor, :bvMi, p values(i), 4000);

60 end loop;

62 /*

63 * и выполнить его. Так формируется результующее множество

64 */

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

65 l status := dbms sql.execute(g cursor);

67 /*

68 * теперь проходим в цикле по строкам и выдаем результаты.

69 */

70 while (dbms sql.fetch rows(g cursor) > 0)

71 loop

72 l comma := ;

73 for i in 1 .. 3 loop

74 dbms sql.column value(g cursor, i, l colValue);

75 dbms output.put(l comma l colValue);

76 l comma := , ;

77 end loop;

78 dbms output.new line;

79 end loop;

80 end; 81

82 end dyn demo;

83 /

Package body created.

scott@TKYTE816> set serveroutput on scott@TKYTE816> begin

2 dyn demo.do ery(dyn demo.array(ename, job) ,

3 dyn demo.array(like, = ) ,

4 dyn demo.array(%A%, CLERK));

5 end;

ADAMS,787 6,CLERK JAMES,7 900,CLERK

PL/SQL procedure successfully completed.

Как видите, все просто и в рамках действий, предусмотренных для использования пакета DBMS SQL. Теперь реализуем то же самое с помощью встроенного динамического SQL Здесь мы сталкиваемся с проблемой. Для динамического выполнения запроса со связываемыми переменными во встроенном динамическом SQL используется следующий синтаксис:

OEEN курсорнаяпеременная FOR select

USING переиенная1, переменная2, переменнаяЗ, ...;

Проблема в том, что на этапе компиляции мы не знаем размера списка USING - будет ли в нем одна переменная, две или вообще ни одной? Поэтому необходимо па-раметризировать запрос, но использовать обычные связываемые переменные нельзя. Можно, однако, использовать средство, предназначавшееся совсем для других целей. В главе 21, при изучении средств тщательного контроля доступа, мы рассмотрим контекст приложения (application context) и его использование. Контекст приложения, по сути, позволяет поместить в пространство имен (namespace) пару переменная/значение. К этой паре переменная/значение можно обращаться в SQL-операторах с помощью встроенной функции SYS CONTEXT. Контекст приложения, таким образом, можно исполь-

1224

Глава 16

create or replace package body dyn demo

зовать для параметризации запроса, помещая связываемые значения в пространство имени и выбирая их в запросе с помощью встроенной функции SYSCONTEXT. Итак, вместо запроса следующего вида:

select ename, empno, job from emp where ename like :bvl and job = :bv2;

создаем такой запрос:

select ename, empno, job from emp

where ename like SYS CONTEXT(namespace,ename) and job = SYS CONTEXT(namespace,job);

Код, реализующий этот метод, может выглядеть так:

scott@TKYTE816> REM Пользователь SCOTT должен иметь привилегию CREATE ANY ->CONTEXT

scott@TKYTE816> REM или роль с такой привилегий, иначе код не сработает scott@TKYTE816> create or replace context bv context using dyn demo

2 /

Context created.

scott@TKYTE816> 2 as

3 4 5 6

9 10

13 14 15 16 17 18 19 20 21

25 26 27

2 8 29

procedure do query(p cnames

p operators

p values type rc is ref cursor;

in in in

array, array, array)

long; varchar2(20) rc;

emp.ename%type; emp.empno%type; emp.job%type;

where ;

l query

l sep varchar2(20) default

l cursor l ename l empno

l job

begin /*

* Это наш постоянный список выбора - мы

* выбираем эти три столбца. Изменяются

* условия выбора.

всегда

l query : =

for i in 1 l query

select ename, empno, job from emp;

p cnames.count loop l query l sep p cnames(i) M M p operators(i) M M sys context(BV CONTEXT,

p cnames(i)