Архив

Архив раздела ‘oracle’

Ассоциативный массив: цикл по коллекции с ключом 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)

Замена N-ного символа в строке

28 Октябрь 2011 2 comments

Для замены N-ного символа в строке, можно обойтись и без SUBSTR — использовать REGEXP_REPLACE.

select regexp_replace('208010100000','.','X', 5, 1) as res from dual
union all
select regexp_replace('208010100000','.','X', 2, 1) as res from dual;

RES
------------
2080X0100000
2X8010100000

Функция LISTAGG (объединение строк)

20 Сентябрь 2011 20 comments

LISTAGG упорядочивает данные, объединенные в группы конструкцией ORDER BY, затем соединяет указаный столбец measure_expr.

1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.

2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.

3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.

Пример агрегатной функции

SQL> select deptno, LISTAGG(t.ename, ', ') WITHIN GROUP (order by t.empno) as ename_list
  2  from scott.emp t
  3  group by deptno;

DEPTNO ENAME_LIST
------ --------------------------------------------------------------------------------
    10 CLARK, KING, MILLER
    20 SMITH, JONES, SCOTT, ADAMS, FORD, SMIT2
    30 ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES

Пример аналитической функции

SQL> select deptno,
  2         ename,
  3         job,
  4         hiredate, listagg(t.ename, ', ') 
  6           WITHIN GROUP(order by t.empno) over(partition by deptno) as ename_list
  7  from scott.emp t
  8  where hiredate > to_date('01-01-1982','dd-mm-yyyy');

DEPTNO ENAME      JOB       HIREDATE    ENAME_LIST
------ ---------- --------- ----------- -----------------------
    10 MILLER     CLERK     23.01.1982  MILLER
    20 SCOTT      ANALYST   09.12.1982  SCOTT, ADAMS, SMIT2
    20 ADAMS      CLERK     12.01.1983  SCOTT, ADAMS, SMIT2
    20 SMIT2      CLERK     25.07.2011  SCOTT, ADAMS, SMIT2

Oracle Database SQL Language Reference 11g Release 2 : LISTAGG

Oracle Project (Проекты)

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

Информация об Oracle Project (Проекты) — описание основных таблиц, полезные запросы и примеры использования API.
По мере появления данных, буду добавлять информацию.

Основные таблицы Oracle Project, модуль PA.
Таблица Описание
PA_PROJECTS_ALL Список проектов
PA_PROJECT_ASSETS_ALL Активы для проектов капитального строительства
PA_PROJECT_CLASSES Классификация проектов
PA_CLASS_CATEGORIES Категории классификаций
PA_CLASS_CODES Коды классификаций
PA_PROJECT_ROLE_TYPES_B
view pa_project_role_types
Список ролей для назначения на проект
PA_PROJECT_STATUSES Список используемых статусов.
Для PROJECT, DELIVERABLE, TASK, PROGRESS, STRUCTURE и т.д.
PA_PROJECT_TYPES_ALL Тип проекта
PA_TRANSACTION_INTERFACE_ALL Интерфейсная таблица для импорта транзакций из внешних источников
PA_IMPLEMENTATIONS_ALL Информация о конфигурации установки
PA_BUDGET_LINES Строки бюджета
PA_BUDGET_VERSIONS Версии бюджета
PA_BUDGET_TYPES Тип бюджета
PA_EXPENDITURES_ALL Группы статей расходов
PA_EXPENDITURE_CATEGORIES Категории затрат
PA_EXPENDITURE_TYPES Типы затрат
PA_EXPENDITURE_ITEMS_ALL Информация о расходах для проектов и задач
PA_PERIODS_ALL Периоды
PA_RESOURCES Список ресурсов
PA_PROJ_ELEMENTS Объекты рабочего плана — задачи, позиции поставки, структура
PA_PROJ_ELEMENT_VERSIONS Версии объектов рабочего плана
PA_STRUCTURE_TYPES Тип структуры
PA_PROJ_ELEM_VER_STRUCTURE Версия структуры
PA_CONVERSION_TYPES_V Тип курса
PA_COST_DISTRIBUTION_LINES_ALL Распределение затрат расходных статей
Categories: oracle, Oracle e-Business Suite Tags: , , ,

Поиск значения по всем таблицам

7 Сентябрь 2011 19 comments

Поиск значения по всем таблицам

Вариант1:

DECLARE
  match_count   INTEGER;
  query_str     VARCHAR2(400);
  l_owner       VARCHAR2(10)  := 'SCOTT';
  l_search_text VARCHAR2(100) := '10';
BEGIN
  FOR t IN (select table_name, column_name FROM all_tab_columns a
            where owner = l_owner
                  and a.data_type in ('NUMBER', 'VARCHAR2')
            )
  LOOP
    begin
      match_count := 0;
      query_str   := 'SELECT COUNT(*) FROM ' ||l_owner||'.'|| t.table_name ||
                     ' WHERE to_char(' || t.column_name || ') = :1';
      EXECUTE IMMEDIATE query_str
        INTO match_count
        USING l_search_text;
      IF match_count > 0 THEN
        dbms_output.put_line(l_owner||'.'||t.table_name || ' ' || t.column_name || ' ' || match_count);
        dbms_output.put_line('  SELECT t.'|| t.column_name || ', t.* from '||l_owner||'.'|| t.table_name||' t where to_char(' || t.column_name || ') = '''||l_search_text||'''');
        dbms_output.put_line('');
      END IF;
    exception
     when others then
       dbms_output.put_line(t.table_name||'.'||t.column_name||' : '||SQLERRM||' : '||SQLCODE);
       dbms_output.put_line(query_str);
       dbms_output.put_line('');
       /*raise*/
    end;
  END LOOP;
END;

Вариант2:

--SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE tab_columns_rec_type IS RECORD (
    table_name   all_tab_columns.table_name%type
   ,column_name  all_tab_columns.column_name%type
   ,data_type    all_tab_columns.data_type%type
  );
  type tab_columns_tab_type is table of tab_columns_rec_type index by binary_integer;
  l_tab_columns_rec tab_columns_tab_type;
 
  match_count   INTEGER;
  query_str     VARCHAR2(400);
  l_owner       VARCHAR2(10)  := 'SCOTT';
  l_search_text VARCHAR2(100) := '10'; -- % добавляем здесь, если ищем не по точному вхождению
  l_number      NUMBER;
  is_number     NUMBER(1);
  l_str_length  NUMBER;
  /*
  l_mode:
  1 - NUMBER и VARCHAR2
  2 - only NUMBER
  3 - only VARCHAR2
  */
  l_mode NUMBER := 1;
  -- При каком количестве найденные совпадений
  -- прекращать поиск
  l_found_count  NUMBER := 1; 
  l_exists_count NUMBER;
BEGIN
 
  l_str_length := length(l_search_text);
 
  begin
    l_number := to_number(l_search_text);
    is_number := 1;
  exception
    when VALUE_ERROR then
      is_number := 0;
  end;
 
  if (l_mode = 1 and is_number = 1) then
    select table_name, column_name, data_type
      bulk collect into l_tab_columns_rec
    FROM all_tab_columns a
    where owner = l_owner
          and a.data_length >= l_str_length
          and ( a.data_type = 'VARCHAR2' or a.data_type = 'NUMBER' );
  elsif (l_mode = 2 and is_number = 1) then
    select table_name, column_name, data_type
      bulk collect into l_tab_columns_rec
    FROM all_tab_columns a
    where owner = l_owner
          and a.data_length >= l_str_length
          and a.data_type = 'NUMBER';
  else
    select table_name, column_name, data_type
      bulk collect into l_tab_columns_rec
    FROM all_tab_columns a
    where owner = l_owner
          and a.data_length >= l_str_length
          and a.data_type = 'VARCHAR2';
  end if;
  l_exists_count := 0;
  if NOT (l_tab_columns_rec is null or l_tab_columns_rec.count = 0) then
    for j in l_tab_columns_rec.first..l_tab_columns_rec.last
    LOOP
        begin
          match_count := 0;
          if (l_tab_columns_rec(j).data_type = 'VARCHAR2') then
            query_str   := 'SELECT count(*) FROM ' ||l_owner||'.'|| l_tab_columns_rec(j).table_name ||
                           ' WHERE rownum = 1 and to_char(' || l_tab_columns_rec(j).column_name || ') = :1';
            EXECUTE IMMEDIATE query_str
              INTO match_count
              USING l_search_text;
          elsif (l_tab_columns_rec(j).data_type = 'NUMBER') then
            query_str   := 'SELECT count(*) FROM ' ||l_owner||'.'|| l_tab_columns_rec(j).table_name ||
                           ' WHERE rownum = 1 and ' || l_tab_columns_rec(j).column_name || ' = :1';
            EXECUTE IMMEDIATE query_str
              INTO match_count
              USING l_number;
          end if;
 
          IF match_count > 0 THEN
            l_exists_count := l_exists_count + 1;
            dbms_output.put_line(l_tab_columns_rec(j).data_type||' '||l_owner||'.'||l_tab_columns_rec(j).table_name || ' ' || l_tab_columns_rec(j).column_name );
            dbms_output.put_line('  SELECT t.'|| l_tab_columns_rec(j).column_name || ', t.* from '||l_owner||'.'|| l_tab_columns_rec(j).table_name||' t where to_char(' || l_tab_columns_rec(j).column_name || ') like '''||l_search_text||'''');
            dbms_output.put_line('');
            if (l_exists_count = l_found_count) then
              exit;
            end if;
          END IF;
        exception
         when others then
           dbms_output.put_line(l_tab_columns_rec(j).table_name||'.'||l_tab_columns_rec(j).column_name||' : '||SQLERRM||' : '||SQLCODE);
           dbms_output.put_line(query_str);
           dbms_output.put_line('');
           /*raise*/
        end;
      END LOOP;
  end if;
 
END;
Categories: oracle, SQL Tags: , ,

Массив переменной длины (VARRAY)

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

Массив переменной длины

Подобно двум другим типам коллекций, массивы переменной длины являются одномерными коллекциями, состоящими из однородных элементов.

  • Размер всегда ограничен и массив не может быть разреженным.
  • Максимальное количество элементов указывается при определении типа.
  • Могут использоваться как в PL\SQL так и в таблицах.
  • Порядок элементов при сохранение и извлечении, в отличии от вложенных таблиц, сохраняется.
  • Индекс — положительное число от 1 до 2,147,483,647
  • Нельзя удалить произвольный элемент массива

 

Использование массива переменной длины в SQL

Создаем тип

CREATE TYPE XX_TYPE_ORDERS IS VARRAY(5) OF VARCHAR2(100);

Создаем таблицу

CREATE TABLE XX_ORDERS (ORDER_ID NUMBER,ORDER_NAME XX_TYPE_ORDERS);

Вставляем строки в таблицу

INSERT INTO XX_ORDERS VALUES (1, XX_TYPE_ORDERS('Order 1','Order 2','Order 3'));
INSERT INTO XX_ORDERS VALUES (2, XX_TYPE_ORDERS('Order 21','Order 22','Order 23'));

Читать дальше про “Массив переменной длины (VARRAY)” »

Categories: oracle, SQL Tags:

Ассоциативные массивы (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 1 комментарий

 

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

Вложенную таблицу можно рассматривать как одномерный массив, в котором индексами служат значения целочисленного типа. Вложенная таблица может иметь пустые элементы, которые появляются после их удаления встроенной процедурой 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: ,