Открыто о СУБД Oracle на русском : Использование PL/SQL-таблиц

Выпуск 23

Использование PL/SQL-таблиц

Уважаемые подписчики! Этот выпуск посвящен различным вариантам и проблемам использования PL/SQL-таблиц, в том числе, в новейшей версии сервера Oracle 9i Release 2. По-прежнему продолжаем изучать наработки Тома Кайта...

Мы рассмотрим три вопроса:

1. Ассоциативные массивы в Oracle 9.2
2. Индексирование массивов
3. Возврат значений PL/SQL таблицы через ref cursor

Ассоциативные массивы в Oracle 9.2

Том,

Я бы хотел задать пару вопросов "когда же...", причем, первый из них весьма теоретический...

1) Ассоциативные массивы
-----------------------

Хорошо, что, наконец, появились PL/SQL-таблицы с индексами типа varchar2. Они очень пригодятся при реализации справочников, как и классические PL/SQL-таблицы при реализации справочников с числовыми ключами. Однако, для загрузки массивов приходится организовывать цикл по таким справочным таблицам. Было бы здорово, если бы можно было выполнять следующее (псевдокод)?

SELECT pk_col, description_col
BULK COLLECT INTO l_lookup
USING pk_col AS INDEX
FROM lookup_table;

...где l_lookup - таблица типа description_col%type indexed by pk_col%type...

Я не знаю, возможно ли это, или разрабатывается ли что-либо подобно для следующей версии? Но в псевдокоде смотрится красиво ;)

2) FORALL для таблицы записей
-----------------------------

В версии 9.2 мы по-прежнему не можем ссылаться на отдельные элементы таблицы записей в операторе FORALL. Я знаю, что можно вставлять всю запись, что хорошо, и изменять данные, используя всю запись (но это плохо, поскольку будут изменяться и первичные ключи, и нам придется использовать дополнительные массивы в конструкции FORALL WHERE, чтобы изменение касалось только соответствующих строк). Однако, хорошо бы иметь возможность делать следующее:

FORALL i IN myCompositeArray.FIRST .. myCompositeArray.LAST
   UPDATE my_table
   SET    my_col = myCompositeArray(i).my_col
   WHERE  my_pk  = myCompositeArray(i).my_pk; 

Не знаешь ли ты, когда это будет возможно, если будет? Пока я для этого использую TABLE (и TABLE(CAST( в версии 8i)...

Большое спасибо.

Ответ Тома Кайта

1) Да, синтаксически это было бы красиво, но пока этого нет, - в обсуждении

можно узнать, как это делать за два шага.

Комментарий В.К.

Перевод этого обсуждения можно найти ниже

Конец комментария

2) Придется использовать запись с элементами-массивами, а не массив записей. Проблема связана с физической организацией данных (по строкам или по столбцам) в массиве. Операции с массивами применимы только к непрерывным массивам.

Например:

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int, y int ); 

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2      type foo is record
  3      ( x int,
  4        y int
  5      );
  6 
  7      type foo_array is table of foo index by binary_integer;
  8 
  9      l_data foo_array;
 10  begin
 11      for i in 1 .. 2
 12      loop
 13          l_data(i).x := i;
 14          l_data(i).y := i;
 15      end loop;
 16 
 17      forall i in 1 .. 2
 18          insert into t values l_data(i);
 19  end;
 20  / 

PL/SQL procedure successfully completed. 

Так что, в некоторых случаях, использовать массивы записей можно, - когда интерфейс предполагает получение записей по одной... (используя новую возможность вставки/изменения данных на основе ЗАПИСИ в 9ir2).

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2      type foo is record
  3      ( x int,
  4        y int
  5      );
  6 
  7      type foo_array is table of foo index by binary_integer;
  8 
  9      l_data foo_array;
 10  begin
 11      for i in 1 .. 2
 12      loop
 13          l_data(i).x := i;
 14          l_data(i).y := i;
 15      end loop;
 16 
 17      forall i in 1 .. 2
 18          update t set y = l_data(i).y where x = l_data(i).x;
 19  end;
 20  /
        update t set y = l_data(i).y where x = l_data(i).x;
                         *
ERROR at line 18:
ORA-06550: line 18, column 26:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND
table of records
ORA-06550: line 18, column 35:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 18, column 9:
PL/SQL: SQL Statement ignored 

Но поскольку здесь массив организован по строкам, элементы Y идут не подряд, поэтому операции с массивами по Y или X использовать нельзя.

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2      type intArray is table of int index by binary_integer;
  3      type foo is record
  4      ( x intArray,
  5        y intArray
  6      );
  7 
  8      l_data foo;
  9  begin
 10      for i in 1 .. 2
 11      loop
 12          l_data.x(i) := i;
 13          l_data.y(i) := i;
 14      end loop;
 15 
 16      forall i in 1 .. 2
 17          update t set y = l_data.y(i) where x = l_data.x(i);
 18  end;
 19  / 

PL/SQL procedure successfully completed. 

А вот так - можно.

Комментарий автора вопроса от 14 августа 2002 года

Том,

Спасибо за способ решения, который ты предложил для пункта 1). Я об этом не подумал - просто организовывал цикл по целевой таблице и непосредственно присваивал значения ассоциативному массиву построчно.

Что касается использования записей с элементами-массивами, тут ничего не изменилось. Но поскольку теперь 9.2 поддерживает множественный выбор (bulk collecting) в массив записей, было бы полезно обеспечить и выполнение по нему оператора forall, не так ли?

Возможность вставки в таблицу записей, появившаяся в 9.2, это тоже прекрасно, но что касается измнения, имеет смысл упомянуть о парочке проблем.

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

Во-вторых, если я все правильно понимаю, оператор update, использующий запись, не будет избирательным, если только не поддерживается отдельный массив значений первичных ключей, чтобы можно было гарантировать измнение только одной соответствующей строки:

Например:

   FORALL i in array.FIRST .. array.LAST
      UPDATE my_table
      SET ROW = array(i);    

...этот оператор изменит каждую строку таблицы, последовательно устанавливая ей значения из очередной строки массива.

Тогда как:

   FORALL i in array.FIRST .. array.LAST
      UPDATE my_table
      SET ROW = array(i)
      WHERE my_pk_column = array(i).pk_col; 

...просто не поддерживается, как было сказано в исходном вопросе.

Поэтому приходится делать следующее:

   FORALL i in array.FIRST .. array.LAST
      UPDATE my_table
      SET ROW = array(i)
      WHERE my_pk_column = separate_array_of_pk(i); 

Правильно?

Или идея изменять строки таблицы с помощью записей PL/SQL-таблицы вообще далека от совершенства?

Ответ Тома Кайта

Ваши наблюдения в отношении UPDATE SET ROW, в основном, верны.

Комментарий читателя от 19 ноября 2002 года

Привет, Том!

У меня есть таблица PL/SQL-записей, которую мне нужно передать в java-приложение. Но среди полей PL/SQL-записи должна быть таблица чисел.

Я попытался сделать следующее:

declare
TYPE prev_membr_id IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
TYPE prgm_rec_type IS RECORD
(
    program_id           NUMBER
   ,l_prev_membr_id      prev_membr_id
);
TYPE programDtails IS TABLE OF prgm_rec_type INDEX BY BINARY_INTEGER;
x_dtail_tbl programDtails;
begin
null;
end; 

Но получил сообщение об ошибке

PLS-00507: a PLSQL Table may not contain a table or a record with composite fields 

Как мне получить желаемое в PL/SQL?. Я слышал, что наборы Oracle позволяют это сделать. Кроме того, надо ли это каким-то специальным образом обрабатывать в java, если я буду использовать наборы?

Ответ Тома Кайта

Передавать ТАБЛИЦУ ЗАПИСЕЙ из PLSQL в какой бы то ни было язык не нужно. Никогда.

Если вы используете наборы (определив новый тип данных SQL), достаточно просто возвращать клиенту курсорную переменную (REF CURSOR, результирующее множество).

Пример того, что я имею ввиду, можно найти в обсуждении:

Комментарий В.К.

Перевод этого обсуждения можно найти ниже

Конец комментария

А если вы никогда не использовали курсорные переменные, прочитайте сначала следующую статью:

Оригинал обсуждения этого вопроса можно найти здесь .

Индексирование массивов

Привет, Том!

Я загружаю 500000 строк в массив и ищу в нем данные, как в таблице, т.е.:

select * into value from TABLE(cast(tabls as mytable))
where column_name = ...

Это работает очень медленно.

Можно ли создать индекс по массиву в памяти и ускорить работу?

ОЗУ у нас достаточно, так что, память - не проблема.

Ответ Тома Кайта

Зачем вы это делаете?

Не делайте этого. Используйте РЕАЛЬНУЮ таблицу (не загружайте данные в массив вообще). Если "реальная" таблица - слишком большая для постоянных запросов, используйте проиндексированную глобальную временную таблицу.

В версии 9iR2 появились ассоциативные массивы - индексом может быть строка, а не только число.

Если же у вас есть моя книга, в ней я продемонстрировал, как легко создать хеш-таблицу в любой версии, начиная с Oracle 8.0. Если вы выбираете данные по условию "where column_name = :value", то вместо массива с последовательными индексами достаточно будет создать хеш-таблицу.

Я думаю, вас также может весьма удивить объем ОЗУ, занимаемый вашим массивом.

Комментарий читателя от 6 августа 2002 года

Привет, Том!

Наконец-то мы установили в компании Oracle 9i. У нас 9i R2.

Я пытаюсь совместно использовать ассоциативные массивы и возможности BULK COLLECT.

ORGANIZATION_NAME                         NOT NULL VARCHAR2(110)  

Я получаю сообщения об ошибках

  1   declare
  2   type t_org  is table of varchar2(110) index by varchar2(110);
  3   t_org_arr     t_org;
  4   BEGIN
  5      select organization_name BULK COLLECT INTO t_org_arr
  6        from pubs.olv@renpqa1.world@pubs_link
  7      where delete_indicator = 0
  8         and main_point_flag ='Y';
  9       dbms_output.put_line('Oraganization :'||t_org_arr('Baker Rost'));
 10       dbms_output.put_line('Total :'||t_org_arr.count);
 11*  end;
SQL> /
    select organization_name BULK COLLECT INTO t_org_arr
                                               *
ERROR at line 5:
ORA-06550: line 5, column 48:
PLS-00657: Implementation restriction: bulk SQL with associative arrays with
VARCHAR2 key is not sup
ORA-06550: line 5, column 48:
PLS-00597: expression 'T_ORG_ARR' in the INTO list is of wrong type
ORA-06550: line 6, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored 

Не мог бы ты сказать, что тут неверно?

В Oracle 8i выполнение BULK COLLECT по связи базы данных не поддерживалось. Поддерживается ли такая возможность в Oracle 9i?

В представленном выше сообщении об ошибке говорится, что BULK COLLECT для ассоциативных массивов не поддерживается. Не мог бы ты объяснить, почему?.

Если мы множественная обработка поддерживалась, то заполение массива и поиск по нему были бы явно ЛУЧШЕ.

Спасибо.

Ответ Тома Кайта

Я считаю, что сообщение об ошибке достаточно красноречиво само по себе:

PLS-00657: Implementation restriction: bulk SQL with associative arrays with
VARCHAR2 key is not supported 

не так ли?

Подумайте об этом - взгляните на свой SQL: что, по-вашему, должно произойти при выполнении такой множественной выборки? Что будет "индексом" массива? При использовании конструкции index by binary_integer логично, что первая строка получает индекс 1, вторая строка - индекс 2 и т.д. При использовании index by varchar2, когда вы извлекаете название организации, какой ИНДЕКС должен использоваться?????

Посмотрите на свой пример и скажите мне, что будет/должно возвращаться при обращении

    t_org_arr( 'Baker Rost' ) 

Как мы узнаем, что надо использовать строку 'Baker Rost' в качестве индекса массива?

Вот как это надо делать:

scott@ORA920.LOCALHOST> declare
  2     type temp   is table of varchar2(110) index by binary_integer;
  3
  4     l_key       temp;
  5     l_val       temp;
  6
  7     type t_org  is table of varchar2(110) index by varchar2(110);
  8     t_org_arr     t_org;
  9  BEGIN
 10     select username, created BULK COLLECT INTO l_key, l_val
 11       from all_users@ora920.us.oracle.com;
 12
 13     for i in 1 .. l_key.count
 14     loop
 15         t_org_arr( l_key(i) ) := l_val(i);
 16     end loop;
 17
 18     dbms_output.put_line('Created : '||t_org_arr('SCOTT'));
 19     dbms_output.put_line('Total   : '||t_org_arr.count);
 20  end;
 21  /
Created : 13-MAY-02
Total   : 34 

PL/SQL procedure successfully completed. 

Выполняется множественная выборка КЛЮЧЕЙ (индексов массива) и ЗНАЧЕНИЙ. Как я показал, это можно делать по связи базы данных. После этого их можно поместить в ассоциативный массив.

Оригинал обсуждения этого вопроса можно найти здесь .

Возврат значений PL/SQL таблицы через ref cursor

Как возвращать значения из PL/SQL-таблицы (indexed by BINARY_INTEGER) через ref cursor?

Содержимое PL/SQL-таблицы НЕЛЬЗЯ вернуть одним SQL-оператором. Поскольку это PL/SQL-таблица, я не могу выполнить стандартный оператор:

open TunnelCrs for 'select gw1, gw2 from a'

Сейчас для создания курсорной переменной я делаю следующее:

   open TunnelCrs for
   'select '||to_char(a(1).gw1)||','||
   to_char(a(1).gw1)||' from dual'; 

Если есть несколько строк, я использую union all.

Вот мои определения типа и PL/SQL-таблицы:

TYPE gw_ttn is record (
        gw_id1 INTEGER,
        gw_id2 INTEGER
);
TYPE gw_tn is table of gw_ttn index by binary_integer;
TYPE TunnelCursor IS REF CURSOR;
a       gw_tn; 

Нет ли более элегантного решения?

Ответ Тома Кайта

Правильно в данном случае будет использовать НЕ PL/SQL-таблицу, а объектный тип. Примерно так:

tkyte@OSI1.WORLD> create or replace type myScalarType as object
  2  (   x     int,
  3      y    date,
  4      z    varchar2(25)
  5  )
  6  /

Type created. 

tkyte@OSI1.WORLD> create or replace type myTableType as table of myScalarType;
  2  /

Type created. 

tkyte@OSI1.WORLD> create or replace
  2  function demo_proc( p_start_row in number,
  3                      p_end_row in number )
  4  return myTableType
  5  as
  6      l_data             myTableType := myTableType();
  7      l_cnt              number default 0;
  8  begin
  9      for x in ( select * from emp order by sal desc )
 10      loop
 11          l_cnt := l_cnt + 1;
 12          if ( l_cnt >= p_start_row )
 13          then
 14              l_data.extend;
 15              l_data(l_data.count) :=
 16                      myScalarType( x.empno,
 17                                    x.hiredate,
 18                                    x.ename );
 19          end if;
 20          exit when l_cnt = p_end_row;
 21      end loop;
 22 
 23      return l_data;
 24  end;
 25  /

Function created. 

tkyte@OSI1.WORLD> select *
  2  from the ( select cast( demo_proc(2,6) as mytableType )
  3               from dual ) a
  4  /

         X Y         Z
---------- --------- -------------------------
      7788 09-DEC-82 SCOTT
      7902 03-DEC-81 FORD
      7566 02-APR-81 JONES
      7698 01-MAY-81 BLAKE
      7782 09-JUN-81 CLARK 

tkyte@OSI1.WORLD> 

Так что, я рекомендую вам использовать тип SQL, а не тип pl/sql-таблицы (они работают во многом одинаково, за одним существенным исключением - вложенные таблицы SQL требуют использования метода .EXTEND для выделения пространства, тогда как в pl/sql-таблице "место" добавляется при необходимости автоматически.

Используя тип SQL, вы сможете легко выбирать данные из таблицы. Ваш пример с курсорной переменной можно реализовать так:

tkyte@OSI1.WORLD> create or replace package my_pkg
  2  as
  3      type rc is ref cursor;
  4 
  5      procedure p( p_cursor in out rc );
  6  end;
  7  / 

Package created. 

tkyte@OSI1.WORLD> create or replace package body my_pkg
  2  as
  3 
  4  procedure p( P_cursor in out rc )
  5  is
  6      l_data  myTableType := myTableType();
  7  begin
  8      for i in 1 .. 3 loop
  9          l_data.extend;
 10          l_data(i) :=
 11             myScalarType( i, sysdate+i, i || ' data');
 12      end loop;
 13 
 14      open p_cursor for
 15      select *
 16        from TABLE ( cast ( l_data as myTableType) );
 17  end;
 18 
 19  end;
 20  / 

Package body created. 

tkyte@OSI1.WORLD> set autoprint on
tkyte@OSI1.WORLD> variable x refcursor
tkyte@OSI1.WORLD> exec my_pkg.p(:x) 

PL/SQL procedure successfully completed.

         X Y         Z
---------- --------- -------------------------
         1 27-MAY-00 1 data
         2 28-MAY-00 2 data
         3 29-MAY-00 3 data 

tkyte@OSI1.WORLD>

Комментарий читателя от 30 августа 2001 года

Имеет ли смысл с точки зрения производительности возвращать курсорную переменную (ref cursor), а не саму таблицу (и обращаться к ней из JDBC как к типу ARRAY или CustomDatum)?

Ответ Тома Кайта

Да, лучше возвращать курсорную переменную.

Если возвращать массив, то придется извлечь все строки на стороне PL/SQL. При использовании курсорной переменной клиент сразу получает данные (не надо извлекать последнюю строку, прежде чем можно будет увидеть первую). Приложение будет работать быстрее с точки зрения конечного пользователя.

Кроме того, если возвращать массив, придется извлечь все строки, выделить под них память в plsql, скопировать туда все данные, а затем послать их по сети одним большим куском клиенту, который должен выделить память для хранения всего набора. При использовании курсорной перменной, - я возвращаю указатель (не требется расходовать время и ресурсы на выделение памяти в plsql и копирование в нее данных). Я посылаю строки по частям, а не все сразу (по умолчанию - по 10 строк за раз, но этим можно управлять). Мне не нужно выделять память для размещения всего набора данных на клиенте, - только для части.

Поэтому курсорная переменная - намного более масштабируемое решение при постоянных пересылках данных.

Более того, java-код для доступа к результрующему множеству - тривиален по сравнению с обращением к типам ARRAY или CustomDatum.

Комментарий В.К.

Обсуждение продолжалось и дальше, но ничего интересного с точки зрения использования курсорных переменных и работы с табличным типом как с реальной таблицей я там не нашел... Если хотите, прочитайте сами.

Конец комментария

Оригинал обсуждения этого вопроса можно найти здесь .

Copyright © 2002 Oracle Corporation

В следующем выпуске

Следующий выпуск будет авторским. О синтаксисе операторов CREATE INDEX, CREATE INDEXTYPE, CREATE JAVA и CREATE LIBRARY. А, может быть, и о закреплении таблиц в памяти (о пуле KEEP в буферном кэше) - посмотрим, что быстрее получится. Выпуск выйдет на следующей неделе. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.