Коллекции

Коллекция - структура данных, сходная со списком или одномерным массивом.

Существует три вида коллекций:
1. Ассоциативный массив (assoc arrays) - неограниченные разряженные коллекции. Индекс может быть строка/число. Редко используется.
2. Вложенная таблица (nested table) - неограниченные индексированные несвязные коллекции. Индекс число. Могут иметь “дырки”. Используется очень часто.
3. Массив с фиксированной длинной (varray) - размер всегда ограничен. Не может быть “дырок” (разряженности). Практически не используется.

Все коллекции одномерные и однородные (все элементы имеют один тип). Элементами могут быть как примитивы так и объекты. Индексация начинается с “1”.

Применение:
1. Манипуляция списком данных в программе.
2. Ускорение многострочных SQL-операций.
3. Кэширование данных.

Одна из горячих тем на собеседованиях 🎓

#теория #plsql #коллекции

Ассоциативный массив (associative array/index by)

Ассоциативный массив - множество пар ключ-значение.

Ключом может быть:
1) PLS_INTEGER (от −2 147 483 648 до 2 147 483 647).
2) VARCHAR2 (до 32К)

При обращении к несуществующему ключу будет ошибка. Выбирать ключи стоит аккуратно. Для строк используется функция хэширования - может на больших объемах работать чуть дольше.

Синтаксис:
type имя_типа is table of тип_объекта index by varchar2(N char);


type имя_типа is table of тип_объекта index by pls_integer;

index by - ассоциативный массив
varchar2, pls_integer - типа ключа в массиве.

Примеры:

-- Ассоциативный массив с ключом строкой и элементами типа number.
type t_my_type2 is table of number(30,0) index by varchar2(30000 char);
v_my_list   t_my_type;

-- Ассоциативный массив с ключом числом и элементами типом boolean.
type t_my_type2 is table of boolean index by pls_integer;
v_my_list2   t_my_type2;

#теория #plsql #коллекции

Вложенные таблицы (nested tables)

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

Синтаксис:

type имя_типа is table of тип_объекта;

Примеры:

-- Коллекция состоящая из элементов типа столбца tab.first_name.
type t_arr   is table of tab.first_name%type;
v_arr t_arr := t_arr(); -- обязательная инициализация

-- Коллекция состоящая из элементов типа record
type t_rec is record(
  field1 number, ...
);
type t_arr   is table of   t_rec;
v_arr t_arr := t_arr(); -- обязательная инициализация

Хотите, что бы я записал видео про коллекции и операции с ними? 🎥
Тогда жмите палец вверх 👍

#теория #plsql #коллекции

Инициализация/создание коллекций

Заполнить коллекцию элементами можно тремя способами:
1. Инициализация при создании.
2. Заполнение в run-time вручную.
3. Заполнение через SQL-команды.

Пример заполнения коллекции через SQL:

declare
  type t_rec     is record( n1 number, n2 number);
  type t_tab_rec is table of t_rec;  -- тип коллекции
  v_tab1          t_tab_rec;  -- переменная коллекции
begin
  -- результаты помещаются в коллекцию
  select * 
     bulk collect into v_tab
    from dual connect by level <= 10;
end;

Остальные примеры заполнения я покажу в видео про коллекции 🎥

#теория #plsql #коллекции

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

Например: scEfxmqTsVTuSBNF

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Функции с кэшированием результатов - result_cache

Всем привет!
Я решил сделать видео на тему, занявшую второе место в голосовании, которое было три недели назад.

В нем, я расскажу, что такое функции с кэшированием результатов - result_cache, рассмотрим концепцию и разберем подробней на простеньких примерах. Материал относится к advanced-уровню и я надеюсь, вы уже знаете, что такое функции в Oracle 😉

🎥 Смотреть видео - 6 мин

#видео #plsql #функции #result_cache

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

Принцип решения:
Для генерации случайных значений используется пакет dbms_random.
В нем есть ряд функций для генерации строковых и числовых значений.

Для генерации строки используем фунцию string. У неё есть два параметра:
1) Какие значения генерировать.
- 'u','U' : upper case alpha characters only
- 'l','L' : lower case alpha characters only
- 'a','A' : alpha characters only (mixed case)
- 'x','X' : any alpha-numeric characters (upper)
- 'p','P' : any printable characters
2) Какой длины нужна строка

Для получения случайной длины используем функцию value с заданием диапазона.

Запрос:

select dbms_random.string(opt => 'a',
                          len => dbms_random.value(10, 20)) rnd_str
  from dual
connect by level <= 3;

Надеюсь, вам понравилась задачка 😉

#sql #решениезадачи #dbms_random

WHILE (пока выполняется условие)

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

Пример:

declare
  v_index pls_integer := 1;
begin  
  -- от 1 до 3х
  while v_index <= 3
  loop    
     dbms_output.put_line('simple while: '||v_index);
     v_index := v_index + 1;
  end loop;
end;
/

#plsql #теория #циклы

Задача: Есть таблица, в которой две колонки имеют значение по умолчанию. Обе они не допускают вставки NULL-значений.
Необходимо вставить новую строку в эту таблицу, так чтобы эти две колонки заполнились значениями по умолчанию.
В insert нельзя использовать конкретные значения из определения таблицы.

Структура таблицы:
create table tab1
(
id number(30) not null,
ttype number(1) default 0 not null ,
tname varchar2(20 char) default 'none' not null
);

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Всем привет! Как и обещал, я записал видео про коллекции 😉

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

На всякий случай, скажу, что весь контент на этом канале создается лично мной, в том числе видео. Приятного просмотра 👍🏻

🎥 Смотреть видео - 9 мин

#видео #plsql #коллекции #nestedtables

Задача: Есть таблица, в которой две колонки имеют значение по умолчанию. Обе они не допускают вставки NULL-значений.
Необходимо вставить новую строку в эту таблицу, так чтобы эти две колонки заполнились значениями по умолчанию. В insert НЕ использовать конкретные значения из определения таблицы.

Принцип решения:
Ключевое слово DEFAULT в списке значений обеспечивает вставку значения, которое было определено как применяемое по умолчанию во время создания конкретного столбца. Это ключевое слово используется во всех СУБД.

Запрос:

insert into tab1
  (id
  ,ttype
  ,tname)
values
  (1
  ,default
  ,default);

Надеюсь, вам понравилась задачка 😉

#sql #решениезадачи #default

FOR (цикл со счетчиком)

Классический цикл FOR с начальным и конечным значениями. Нач и конечн значения вычисляются один раз. Переменную индекса объявлять не надо. Конечное значение включается в диапазон.
Синтаксис приведен на скриншоте.
Reverse - обратный проход, причем “начальное значение” - не стартовое, а конечное, “конечное значение” - является стартовым, а не конечным :)

Примеры:

-- Цикл от 1 до 3-х (вкл). Шаг: +1
for i in 1..3 loop
   dbms_output.put_line('simple loop: '||i);
end loop;

-- Цикл от с 3-х до 1-го (вкл). Шаг: -1
for i in reverse 1..3 loop
   dbms_output.put_line('reverse loop: '||i);
end loop;

-- Цикл не выполнится
for i in reverse 3..1 loop
   dbms_output.put_line('reverse loop: '||i);
end loop;

#plsql #теория #циклы

WHILE (пока выполняется условие)

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

Пример:

declare
  v_index pls_integer := 1;
begin  
  -- от 1 до 3х
  while v_index <= 3
  loop    
     dbms_output.put_line('simple while: '||v_index);
     v_index := v_index + 1;
  end loop;
end;
/

#plsql #теория #циклы

Задача: Требуется возвратить разницу между двумя датами в секундах, минутах, часах.

Запрос:

select ... diff_in_sec
       ... diff_in_min
       ... diff_in_hours
  from (select to_date('01.01.2020 12:00', 'dd.mm.YYYY hh24:mi') date2
              ,date '2020-01-01' date1
          from dual);

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Oracle Live SQL - онлайн инструмент от Oracle для выполнения SQL-запросов, написания PL/SQL-кода.

Если у вас нет под рукой БД, а вам необходимо где-то выполнить запрос, то этот инструмент вполне подойдет. Например, задачки из моего канала, можно выполнять в нем💡

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

Вход осуществляется под Oracle-учеткой. Регистрация проста и бесплатна.

Live SQL работает в Oracle Database 19c Enterprise Edition - 19.5.0.0.0.

Если вам интересно могу записать видео про этот сервис и как его использовать👍🏻

#ide

Задача: Требуется возвратить разницу между двумя датами в секундах, минутах, часах.

Принцип решения:
В Oracle разница между двумя датами - количество часов. Следовательно, полученное значение можно выразить в секундах, минутах, используя умножение.

Запрос:

select (date2 - date1) * 24 * 60 * 60 diff_in_sec
      ,(date2 - date1) * 24 * 60 diff_in_min
      ,(date2 - date1) diff_in_hours
  from (select to_date('01.01.2020 12:00', 'dd.mm.YYYY hh24:mi') date2
              ,date '2020-01-01' date1
          from dual);

Надеюсь, вам понравилась задачка 😉

#sql #решениезадачи #даты

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

Назначение меток:
1. Для понимания кода - циклы в циклах в циклах.
2. Для уточнения имени переменных.
3. Для использования в exit/continue для указания конкретного цикла.

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

Примеры:

{{main_loop}}
for i in 1..10 loop
   
   {{inner_loop}}
   for j in 1..10 loop
      if (...) then
          continue main_loop;
      elsif(...) then
         exit main_loop;
      end if; 
   end loop;

end loop;
/

К сожалению, телеграм-разметка никак не хочет пропускать угловые скобки 😒 Я заменил их на фигурные. Смотрите скриншот, как правильно использовать синтаксис.

#plsql #теория #циклы

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

Документация по исключениям. - тут (18с).

Раздел исключений - раздел в котором обрабатываются исключения, возникшие в исполняемом разделе.

#plsql #теория #исключения

Задача: Требуется, используя стандартный механизм СУБД, остановить (заснуть) выполнение PL/SQL программы.

PL/SQL-блок:

begin
   -- здесь спать 10 секунд  
end;
/

Смотрите готовый PL/SQL-код в посте в четверг 🎓

#plsql #задача

Из чего состоят исключения

Исключение в общем виде состоит из двух частей:
- код ошибки (строка-число)
- сообщение об ошибки

Код состоит из двух частей: строка - число. Строка - определяет к какой части/функционалу/компонентам СУБД относится исключение. Отрицательное число - номер ошибки внутри части определенной строкой.
Сообщение - краткое информативное сообщение о сути проблемы. Может содержать шаблон, в котором в run-time подставляется значения.

В документации подробно разжевано: причины и как можно исправить.

Примеры:
-- ORA - часть отвечающая за ядро СУБД, “-00001” - уникальный номер ошибки внутри ядра.
1) ORA-00001: unique constraint (string.string) violated

-- Ошибка в утилите экспорта. “-00002” - уникальный номер ошибки внутри этой утилиты
2) EXP-00002: error in writing to export file

#plsql #теория #исключения