Архив

Публикации с меткой ‘коллекция’

Ассоциативный массив: цикл по коллекции с ключом varchar2

27 Июль 2017 1 комментарий

Пример заполнения ассоциативного массива и цикл для вывода данных.
Ключом является строка varchar2(1):

declare
  -- объявление типа
  TYPE resultRec is record
  (
      ready  varchar2(1),
      cnt    number
  );
  type resultTab is table of resultRec INDEX BY VARCHAR2(1);
  --
  p_iter varchar2(1); -- итератор
  p_validation_res_tbl resultTab; -- экземпляр коллекции
  --
  -- запись данных в коллекцию
  procedure put_result(p_ready varchar2)
  is
  begin
    if (p_validation_res_tbl.exists(p_ready)) then
      p_validation_res_tbl(p_ready).cnt := p_validation_res_tbl(p_ready).cnt + 1;
    else
      p_validation_res_tbl(p_ready).ready := p_ready;
      p_validation_res_tbl(p_ready).cnt   := 1;
    end if;
  end;  
begin
  
  -- запись данных в коллекцию через вспомогательную процедуру
  put_result('E');
  put_result('E');
  put_result('S');
  put_result('W');
  
  -- первичное выставление итератора коллекции  
  p_iter := p_validation_res_tbl.FIRST;
  -- проверка, на то, что коллекция не пустая
  if (p_iter is null) then
    dbms_output.put_line('p_validation_res_tbl is null');
  else  
    -- цикл по коллекции
    WHILE p_iter IS NOT NULL
    LOOP
      -- Пример оращения к данным p_validation_res_tbl(p_iter).cnt, где
      --   p_validation_res_tbl: экземпляр коллекции
      --   p_iter : текущий итератор
      --   cnt : поле из record resultRec
      dbms_output.put_line(p_validation_res_tbl(p_iter).ready 
                           ||':'
                           ||p_validation_res_tbl(p_iter).cnt
                           ||' row(s)');
      -- получение следующей записи
      p_iter := p_validation_res_tbl.NEXT(p_iter);
    END LOOP; 
  end if; 
  
end;
>
E:2 row(s)
S:1 row(s)
W:1 row(s)

Функции для работы с коллекциями

19 Сентябрь 2011 Нет комментариев

Рассмотрим функции для работы с nested tables и varrays (11G)

Сделаем nested tables для примеров:

create or replace type test_number_table as table of number(20);

CARDINALITY

CARDINALITY возвращает число элеметов в nested table. Тип возращаемого значения NUMBER.
Если вложенная таблица пустая или is null, тогда CARDINALITY вернет NULL.

SQL> select CARDINALITY(test_number_table(1,2,3,4,5,1,2)) as t_cardinality from dual;

T_CARDINALITY
-------------
            7

Читать дальше про “Функции для работы с коллекциями” »

Ассоциативные массивы (Associative Arrays)

15 Февраль 2011 Нет комментариев

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

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

Позволяет работать со столбцами как с единой переменной — массивом.

Является временной формой коллекции, существует в течении сессии.

Другое название ассоциативных массивов — индексированные таблицы или pl\sql таблицы.

Ассоциативный массив это наиболее часто используемый вид коллекции. Его не надо инициализировать или расширять. В Oracle9i Database Release 2 и более поздних версиях ассоциативные массивы можно индексировать не только по числам, но и по строкам.

Пример  1: INDEX BY PLS_INTEGER
SQL> set serveroutput on size unlimited;
SQL> DECLARE
  2   TYPE XX_COST_TYPE IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  3   cost XX_COST_TYPE;
  4   l_res NUMBER;
  5  BEGIN
  6   cost(1) := 5 ;
  7   cost(20) := 10 ;
  8   cost(12) := 15 ;
  9   l_res := cost(1) + cost(20) + cost(12);
 10   DBMS_OUTPUT.PUT_LINE(l_res);
 11  END;
 12  /

30

PL/SQL procedure successfully completed
Пример  2: INDEX BY VARCHAR2
SQL> DECLARE
  2   TYPE XX_COST_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
  3   cost XX_COST_TYPE;
  4   L_SELECTED VARCHAR2(100) := 'CHAIR';
  5  BEGIN
  6   cost('TABLE') := 5000;
  7   cost('CHAIR') := 84020 ;
  8   cost('LAMP')  := 8300;
  9   cost('PENCIL'):= 110;
 10   DBMS_OUTPUT.PUT_LINE(cost(L_SELECTED));
 11  END;
 12  /

84020

PL/SQL procedure successfully completed

Ограничения:


Нельзя создать тип ассоциативного массива на уровне схемы:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE TYPE xx_test_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER
  2  /

Warning: Type created with compilation errors
Предупреждение: Тип создан с ошибками компиляции.

SQL> show errors
Ошибки для TYPE XX_TEST_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/22     PLS-00355: использование таблицы pl/sql не разрешается в этом
         контексте
Categories: oracle, SQL Tags: , ,

Вложенные таблицы (Nested Tables)

15 Февраль 2011 Нет комментариев

 

Вложенные таблицы

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

Максимальное число строк вложенной таблицы составляет 2 гигабайта.

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

Свойства вложенных таблиц

  • Могут использоваться как в SQL (тип столбца в таблице), так и в PL\SQL коде
  • Содержат однородные данные, т.е. все строки имеют одинаковую структуру данных
  • Требуется инициализация, при использовании в PL\SQL
  • Порядок элементов не зафиксирован
  • Используется память PGA (для всех 3 типов коллекций)
  • При попытке чтения элемента с несуществующим индексом -> исключение NO_DATA_FOUND

Читать дальше про “Вложенные таблицы (Nested Tables)” »

Categories: oracle, SQL Tags: , ,

Коллекции в PL/SQL

15 Февраль 2011 Нет комментариев

Коллекции

Коллекцией называется упорядоченная группа элементов одного типа.

Язык PL/SQL поддерживает три вида коллекций

Плюсы использования коллекций

  • Кеширование статичной информации
  • Отслеживание элементов данных для спец.обработки
  • Хранение списков непосредственно в столбцах таблицы

 

Какой тип коллекций лучше использовать

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

 


  • Если в PL/SQL-приложении требуется отрицательная индексация, необходимо использовать ассоциативные массивы.

  • Если используется Oracle Database 10g и считается полезным выполнять высокоуровневый набор операций с коллекциями, выберите вложенные таблицы вместо ассоциативных массивов.

  • Если надо потребовать ограничения количества строк коллекции, используйте VARRAY.

  • Если предполагается хранить большое количество постоянных данных в столбце таблицы в виде коллекции, вариант только один – это вложенная таблица. Тогда Oracle Database будет физически использовать отдельную таблицу для хранения коллекции, поэтому её рост ничем не ограничен.

  • Если требуется сохранить порядок элементов, хранимых в столбце коллекции, а набор данных предполагается небольшим, используйте VARRAY. Что значит «небольшой»? Это объём данных, размещаемых в одном блоке базы данных.

  • Вот несколько других параметров, когда VARRAY — массив переменной длины более предпочтителен: не надо беспокоиться об удалении данных из середины набора данных; данные имеют верхнюю границу индексирования; а также, если предполагается, главным образом, извлекать всю коллекцию целиком.

 

Псевдофункции коллекций

Читать дальше про “Коллекции в PL/SQL” »

Categories: oracle, SQL Tags: ,

Select from PL/SQL table

28 Июнь 2010 1 комментарий

Пример запроса из коллекции.

-- создаем глобальный тип данных, локальный нельзя использовать
create or replace type xx_test_type is object ( id number, descr varchar2(20));
create or replace type xx_test_tab is table of xx_test_type;

Пример заполнения и вывода данных

declare
 vcollect xx_test_tab := xx_test_tab(); -- инициализация
begin
 -- заполняем данными
 vcollect.extend;
 vcollect(vcollect.count) := xx_test_type(1,'test1');

 vcollect.extend;
 vcollect(vcollect.count) := xx_test_type(2,'test2');
 -- запрос из коллекции
 for i in (select * from Table(vcollect) order by id )
 loop
   dbms_output.put_line(i.descr);
 end loop;
end;