pl sql примеры кода
Основы языка PL/SQL
Хотя язык SQL и является легким в изучении и обладает массой мощных функциональных возможностей, он не позволяет создавать такие процедурные конструкции, которые возможны в языках третьего поколения вроде C. Язык PL/SQL является собственным расширением языка SQL от Oracle и предлагает функциональность серьезного языка программирования. Одно из главных его преимуществ состоит в том, что он позволяет использовать в базе данных такие программные единицы, как процедуры и пакеты, и тем самым увеличивать возможность повторного использования кода и его производительность.
Базовый блок PL/SQL
Блоком в PL/SQL называется исполняемая программа. Блок кода PL/SQL, независимо от того, инкапсулируется он внутри какой-то программной единицы наподобие процедуры или задается в виде анонимного блока в свободной форме, состоит из следующих структур, которые представляют собой четыре ключевых оператора, только два из которых являются обязательными.
Ниже приведен пример простого блока кода PL/SQL:
Объявление переменных в PL/SQL
В операторе DECLARE можно объявлять как переменные, так и константы. Прежде чем использовать какую-либо переменную ее нужно обязательно объявить. Переменная в PL/SQL может представлять собой как переменную встроенного типа, такого как DATE, NUMBER, VARCHAR2 или CHAR, так и составного вроде VARRAY. Помимо этого, в PL/SQL еще применяются такие типы данных, как BINARY_INTEGER и BOOLEAN.
Ниже приведены некоторые типичные примеры объявления переменной в PL/SQL:
Помимо переменных также можно объявлять и константы, как показано в следующем примере:
Еще можно использовать атрибут %TYPE и с его помощью указывать при объявлении переменной, что ее тип данных должен совпадать с типом данных определенного столбца таблицы:
Посредством атрибута %ROWTYPE можно указывать, что тип данных записи (строки) должен совпадать с типом данных определенной таблицы базы данных. Например, в следующем коде указано, что запись DeptRecord должна содержать все те же столбцы, что и таблица department, а типы данных и длина этих столбцов в ней должны выглядеть абсолютно идентично:
Написание исполняемых операторов PL/SQL
После оператора BEGIN можно начинать вводить все свои желаемые SQL-операторы. Выглядеть эти операторы должны точно так же, как обычные операторы SQL. При использовании операторов SELECT и INSERT в PL/SQL, правда, необходимо помнить об особенностях, о которых более подробно речь пойдет в следующих разделах.
Использование оператора SELECT в PL/SQL
При использовании оператора SELECT в PL/SQL нужно сохранять извлекаемые значения в переменных, как показано ниже:
Использование DML-операторов в PL/SQL
Любые операторы INSERT, DELETE или UPDATE работают в PL/SQL точно так же, как в обычном SQL. Однако в PL/SQL после каждого из них можно также применять оператор COMMIT, как показано ниже:
Обработка ошибок
В PL/SQL любая ошибка или предупреждение называется исключением (exception). В PL/SQL есть кое-какие определенные внутренне ошибки, но также допускается определять и свои собственные. При возникновении любой ошибки инициируется исключение, и управление переходит в отвечающий за обработку исключений раздел программы PL/SQL. В случае определения своих собственных ошибочных ситуаций необходимо обеспечивать инициирование исключений за счет применения специального оператора RAISE.
Ниже приведен пример использования оператора RAISE для обработки исключений:
Управляющие структуры в PL/SQL
В PL/SQL предлагается несколько видов управляющих структур (control structures), которые позволяют обеспечивать итерацию кода или условное выполнение определенных операторов. Все они кратко описаны в последующих разделах моего блога.
Условное управление
Главной разновидностью условной управляющей структуры в PL/SQL является оператор IF, который обеспечивает условное выполнение операторов. Он может применяться в одной из трех следующих форм: IF-THEN, IF-THEN-ELSE и IF-THEN-ELSEIF. Ниже приведен пример простого оператора IF-THEN-ELSEIF:
Конструкции циклов в PL/SQL
Конструкции циклов в PL/SQL позволяют обеспечивать итеративное выполнение кода либо заданное количество раз, либо до тех пор, пока определенное условие не станет истинным или ложным. В следующих подразделах описываются основные виды этих конструкций.
Простой цикл
Конструкция простого цикла подразумевает помещение набора SQL-операторов между ключевыми словами LOOP и END LOOP. Оператор EXIT завершает цикл. Конструкция простого цикла применяется тогда, когда точно неизвестно, сколько раз должен выполняться цикл. В случае ее применения решение о том, когда цикл должен завершаться, принимается на основании содержащейся между операторами LOOP и END LOOP логики.
В следующем примере цикл будет выполняться до тех пор, пока значение quality_grade не достигнет 6:
Еще один простой вид цикла позволяет выполнять конструкция LOOP. EXIT. WHEN, в которой длительность цикла регулируется оператором WHEN. Внутри WHEN указывается условие, и когда это условие становится истинным, цикл завершается. Ниже показан простой пример:
Цикл WHILE
Цикл WHILE указывает, что определенный оператор должен выполняться до тех пор, пока определенное условие остается истинным. Обратите внимание на то, что условие вычисляется за пределами цикла, и вычисляется оно всякий раз, когда выполняются операторы, указанные между операторами LOOP и END LOOP. Когда условие перестает быть истинным, происходит выход из цикла. Ниже приведен пример цикла WHILE:
Цикл FOR
Цикл FOR применяется тогда, когда требуется, чтобы оператор выполнялся определенное количество раз. Он имитирует классический цикл do, который существует в большинстве языков программирования. Ниже приведен пример цикла FOR:
Записи в PL/SQL
Записи (records) в PL/SQL позволяют воспринимать взаимосвязанные данные как одно целое. Они могут содержать поля, каждое из которых может представлять отдельный элемент. Можно использовать атрибут ROW%TYPE и с его помощью объявлять записью столбцы определенной таблицы, что подразумевает применение таблицы в качестве шаблона курсора, а можно создавать и свои собственные записи. Ниже приведен простой пример записи:
Для ссылки на отдельное поле внутри записи применяется точечное обозначение, как показано ниже:
Использование курсоров
Курсором (cursor) в Oracle называется указатель на область в памяти, в которой содержится результирующий набор SQL-запроса, позволяющий индивидуально обрабатывать содержащиеся в результирующем наборе строки. Курсоры, которые используются Oracle при выполнении DML-операторов, называются неявными, а курсоры, которые создают и используют разработчики приложений — явными.
Неявные курсоры
Неявные курсоры автоматически применяются Oracle всякий раз, когда в коде PL/SQL используется оператор SELECT. Они могут использоваться лишь в тех операторах, которые возвращают одну строку. В случае если SQL-оператор возвращает более одной строки, будет выдаваться сообщение об ошибке.
В приведенном ниже блоке кода PL/SQL оператор SELECT, например, предусматривает применение неявного курсора:
Явные курсоры
Явные курсоры создаются разработчиком приложения и облегчают операции с набором строк, которые могут обрабатываться друг за другом. Они применяются всегда, когда известно, что SQL-оператор будет возвращать более одной строки. Обратите внимание, что явный курсор необходимо всегда объявлять в начале блока PL/SQL внутри раздела DECLARE, в отличие от неявного курсора, на который никогда не нужно ссылаться в коде.
После объявления явного курсора он будет проходить через следующие этапы обработки.
В листинге А.4 показан пример создания курсора и затем его использования внутри цикла.
Атрибуты курсоров
В примере, приведенном в листинге А.4, для указания того, когда цикл должен завершаться, используется специальный атрибут курсора %NOTFOUND. Атрибуты курсоров очень полезны при работе с явными курсорами. Наиболее важные из них перечислены ниже.
Курсорный цикл FOR
Обычно при использовании явных курсоров требуется открывать курсор, извлекать данные и по завершении закрывать курсор. Курсорный цикл FOR позволяет выполнять эти процедуры по открытию, извлечению и закрытию автоматически, чем очень сильно упрощает дело. В листинге А.5 показан пример применения конструкции курсорного цикла FOR.
Курсорные переменные
Курсорные переменные указывают на текущую строку в многострочном результирующем наборе. В отличие от обычного курсора, однако, курсорная переменная является динамической, что позволяет присваивать ей новые значения и передавать ее другим процедурами и функциям. Создаются курсорные переменные в PL/SQL следующим образом.
Сначала определяется тип REF CURSOR, как показано ниже:
Затем объявляются сами курсорные переменные типа EmpCurType в анонимном блоке кода PL/SQL либо в процедуре (или функции):
Процедуры, функции и пакеты
Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:
В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:
Пакеты (packages) в Oracle представляют собой объекты, которые обычно состоят из нескольких взаимосвязанных процедур и функций и, как правило, применяются для выполнения какой-нибудь функции приложения путем вызова всех находящихся внутри пакета взаимосвязанных процедур и функций. Пакеты являются чрезвычайно мощным средством, поскольку могут содержать большие объемы функционального кода и многократно выполняться несколькими пользователями.
Каждый пакет обычно состоит из двух частей: спецификации и тела. В спецификации пакета объявляются все входящие в его состав переменные, курсоры и подпрограммы (процедуры и функции), а в теле пакета содержится фактический код этих курсоров и подпрограмм.
В листинге А.6 приведен пример простого пакета Oracle.
При желании использовать пакет emp_pkg для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду:
Некоторые советы при работе с PLSQL
Я решил написать цикл небольших заметок благодаря посту Хорошие привычки в PL/SQL, советы которого и рассмотрю.
1. Нюансы %TYPE/%ROWTYPE
Cделаем тест-кейс — создадим табличку с полем типа Integer и внесем в нее тестовое значение:
create table test_integer(a integer );
create type integer_table as table of integer ;
/
insert into test_integer values (1E125);
commit ;
select * from test_integer;
Как видите, значение прекрасно вставилось. А теперь давайте попробуем его получить в pl/sql блоке:
declare
l_a test_integer.a%type;
begin
select a into l_a from test_integer;
dbms_output.put_line( ‘Все ок, значение=’ || l_a);
exception
when others then
dbms_output.put_line( ‘Все плохо! ‘ ||sqlerrm);
end ;
/
Причина ошибки кроется в том, что INTEGER в SQL это number(*,0) — floating-point number, у которого scale = 0, а INTEGER в PL/SQL это «SUBTYPE INTEGER IS NUMBER(38,0);».
Что же насчет типа из той статьи «CREATE TYPE id_list IS TABLE OF INTEGER;«?
Естественно, это коллекция pl/sql integer’ов:
Тип id_list я создал как integer_table — это просто моя прихоть создавать типы коллекций с постфиксом _TABLE. Кроме того, я обычно не создаю по типу на каждую таблицу table of table_name%rowtype, а только на часто используемые или если эти типы используются в параметрах процедур и функций. Кстати говоря, если у вас oracle SELECT e.employee_id,e.salary,e.hire_date
FROM employee e
WHERE department_id = dept_in
FOR UPDATE OF e.salary;
The columns in the OF clause only indicate which table or view rows are locked.
И запустим в параллельных сессиях:
1:
select * from test_integer where id in (1,3,4,8) for update ;
select id
,row_number() over ( order by id) rn
,rownum
from test_integer t
where id order by id
for update skip locked
3. Работа с коллекциями
Проведем пример снова с test_ab, только сначала снова установите b=1 для всех столбцов.
declare
type number_table is table of number;
update test_ab set b=10 where a=7;
PS. Продолжение следует…
Диалект Oracle SQL: Model в примерах. Часть 1
Любой разработчик, имеющий дело с генерацией отчётности из баз данных, регулярно сталкивается с построением громоздких запросов. Часто это бывает связано с ошибками проектирования БД, и, ещё чаще, со сложностями преобразования извлекаемых данных. К последним можно отнести применение итерационных методов вычисления, подсчёт промежуточных итогов по подгруппам, расчёты, в которых используются значения соседних строк выборки, сложное форматирование строк и подобные задачи. Такие преобразования часто выносятся с уровня БД на уровень сервера приложений или клиента, что сказывается на производительности и удобстве сопровождения кода. Для решения этих задач SQL СУБД Oracle предоставляет аналитические функции и оператор MODEL — о нём и пойдёт речь в этой статье.
Это расширение конструкции SELECT стало доступно с СУБД версии 10g. MODEL позволяет обращаться к данным выборки как к многомерным массивам, изменять и добавлять элементы, проводить сложную агрегацию, а также решать ряд задач, которые до этого требовали использования PL/SQL. При этом языковые конструкции остаются читабельными и декларативными. Одним словом — Excel-like, плюс вся нагрузка ложится на плечи сервера БД.
Синтаксис
Оператор MODEL обрабатывается в числе последних, после него только DISTINCT и ORDER BY. В результате применения столбцы выборки отображаются в массивы measured_column_* c измерениями dimension_column_*. Необязательный параметр PARTITION BY определяет паритиции, аналогичные используемым в аналитических функциях (каждая из них обрабатывается правилами rule_* как независимый массив). Правила применяются в порядке перечисления.
Простейшие примеры
Для начала смоделируем выборку чисел 1, 2 и 3:
SELECT *
FROM dual
MODEL DIMENSION BY (0 dimension)
MEASURES (dummy)
RULES (
dummy[5] = 1,
dummy[6] = 2,
dummy[7] = 3
);
В данном случае, по трём правилам заполняется массив dummy с измерением dimension. Алиас 0 dimension определяется для того, чтобы добавить новый столбец. Разберём преобразование подробнее. Первым делом происходит определение и отображение столбцов выборки (0 dimension в DIMENSION, dummy в MEASURES), затем по этим столбцам происходит выборка (возвращается строка dummy = X, dimension = 0) и только после этого выполняются правила. Сначала ищется строка с dimension = 5, но т.к. она не находится, создаётся новая и заполняется dummy = 1, аналогично для оставшихся двух правил. Если необходимо, с помощью директивы RETURN UPDATED ROWS можно вывести только обновлённые строки:
SELECT result, dummy
FROM dual
MODEL RETURN UPDATED ROWS
DIMENSION BY (dummy)
MEASURES (0 result)
RULES (
result[5] = 1,
result[6] = 2,
result[7] = 3
);
Применять наборы правил можно и в циклах. Следующий запрос рассчитывает несколько элементов последовательности Фибоначчи:
ITERATE задаёт количество итераций цикла (начиная с 0), а необязательная директива UNTIL — условие выхода из него (которое сработало, судя по тому что вы ещё не нажали Ctrl + End). Доступ к счётчику осуществляется через переменную iteration_number.
Диапазоны и агрегирование
Рассмотрим таблицу, хранящую информацию о выпитом в течение недели кофе:
Предположим, мы хотим вывести отчёт о том сколько всего чашек кофе выпито в каждый из дней и в общем, заодно учтём, что в четверг порции чёрного кофе были двойными. Итак, запрос:
Разберём правила подробнее. Первое удваивает количество выпитого в четверг кофе. Функция cv(dimension_name) возвращает текущее значение индекса по измерению dimension_name для обрабатываемого элемента (т.е., в данном случае, вместо cv(day) можно было указать day = 4 или, при желании, сослаться на предыдущий день как day = cv(day) — 1). Второе правило вычисляет подытоги с понедельника по четверг. Кроме цикла (который и так нагляден), здесь используются ссылки на диапазоны элементов в правых частях равенств. Указывать диапазоны можно теми же способами, что и проверки в конструкции WHERE, дополнительное ключевое слово ANY служит для выбора любых значений индекса. Ссылки на диапазоны в правой части равенства необходимо агрегировать, в данном случае используется функция sum. И, наконец, третье правило считает сумму подытогов.
Движемся дальше. Рассмотрим запрос, выводящий подытоги выпитого по видам кофе, количество латте, выпитого во вторник, а также сообщение о том, достигнута ли цель недели — выпить эспрессо в среду:
В первом правиле используется цикл с итерациями по значениям выборки. Вложенные запросы, используемые внутри MODEL должны быль некореллированными. Следующее правило вычисляет итоговую сумму. Обратите внимание на строку «drank cups of latte on 2 day». Т.к. элемент cnt[2, ‘latte’] не был найден, мы получили NULL по ссылке. Это поведение можно изменить директивой IGNORE NAV (добавляется после слова MODEL), тогда вместо ненайденных элементов и NULL, в расчёты будут подставляться: 0 для чисел, 1 января 2001 года для дат, пустая строка для строковых типов и NULL для всего остального. И, наконец, четвёртое правило демонстрирует использование выражения IS PRESENT, оно возвращает истину в случае, если заданный элемент существует, но, увы, эспрессо в среду не было выпито.
На этом вводная завершена, надеюсь вы получили общее представление и привыкли к синтаксису. Во второй части будет рассказано об управлении обновлением и созданием элементов, контроле вывода MODEL, вопросах применимости метода и его производительности. И, конечно же, будут разобраны более сложные примеры.
Часть 2. Идентификация событий происходящих в Oracle PL/SQL
Предисловие
Убедительная просьба, рассматривать данный текст только как продолжение к статье о «Событийной модели логирования». Эта статья будет полезна тем, у кого уже реализовано логирование событий в БД и кто хотел бы осуществлять сбор статистики и начать проводить аналитику этих событий. Только представьте, что ваша БД сможет информировать вас о критичных сбоях системы, накапливать информацию о событиях в БД (кол-во повторений, период повторений и т.д.). И всё это без использования стороннего ПО силами одного PL/SQL.
Введение
Модель логирования позволяет реализовать:
Единый подход в обработке и хранении событий (статья)
Собственную нумерацию и идентификацию событий происходящих в БД
Единый мониторинг событий (статья в разработке)
Анализ событий происходящих в БД (статья в разработке)
Описанные выше характеристики указаны в порядке нумерации и каждый следующий пункт (шаг) есть улучшение и усложнение существующей модели. Описание этой модели будет сложно выполнить в рамках одной статьи, поэтому опишем их последовательно. В этой (второй) статье создадим собственную нумерацию кодов для событий, а также создадим функционал идентификации событий происходящих в БД.
Для чего это нужно?
Для начала давайте рассмотрим пример. Вы реализовали логирование ошибок в вашей БД. С течением времени в ваш лог «прилетают» самые разнообразные ошибки. Предположим, имеются две ошибки вида «no_data_found» возникшие в двух разных процедурах при двух разных запросах (select). Первая ошибка возникла при попытке найти «email» клиента, что в принципе не является критичной ошибкой. Вторая ошибка возникла при попытке найти номер лицевого счета клиента, что вполне может являться критичной ошибкой. При этом если мы посмотрим в таблицу лога (из статьи), то увидим, что указанные ошибки будут храниться с одинаковым кодом 1403 (ORA-01403) в столбце msgcode. Более того, текст указанных ошибок будет практически аналогичным (текст полученный с помощью функции SQLERRM) за исключением имен объектов, на которых произошла ошибка. Для того чтобы понять является ли критичной конкретная ошибка, разработчику необходимо вникать в текст ошибки, смотреть в каком объекте возникла ошибка и на основе этой информации сделать вывод о срочности исправления. При этом, если мы сможем задать более четкое описание ошибки отличное от текста Oracle (SQLERRM), то это позволит упростить понимание причин возникновения и способов решения ошибки.
Как должно быть (в идеале)
Не найдена запись в таблице содержащей адреса электронной почты клиентов
ORA-01403: данные не найдены
USR0001: Не найден адрес электронной почты клиента (идентификатор клиента)
Не найдена запись в таблице содержащей лицевые счета клиентов
ORA-01403: данные не найдены
USR0002: Не найден лицевой счет клиента (идентификатор клиента)
Из этого примера видно, что одна и та же ошибка «no_data_found» (ORA-01403: данные не найдены) может иметь совершенно разное значение с точки зрения бизнес логики, а значит нам необходимо разработать механизм, который позволит идентифицировать каждое событие происходящее в БД как отдельное событие с нашим внутренним уникальным кодом и текстом события (отличную от Oracle). Таким образом мы решаем две проблемы:
1) В месте возникновения ошибки мы устанавливаем уникальный код ошибки. В будущем это позволяет достаточно быстро найти место возникновения ошибки. Также, наличие уникальных кодов позволяет нам произвести точечный подсчет повторений и на основании этой информации принять решение об устранении данной ошибки.
2) Дополнительный «читаемый» текст позволяет сильно упростить понимание ошибки. В таблице выше показано, как одна и та же ошибка может запутать или разъяснить пользователю сведения об ошибке.
Надеюсь мне удалось объяснить зачем необходимо кодировать события в таблице логов. Далее по тексту, будут введены термины «Архитектурный лог» и «Пользовательский лог». На примере процедуры поиска активного номера телефона клиента будет показано как и зачем создано разделение на архитектурный и пользовательский лог.
Архитектурное логирование событий
Давайте рассмотрим пример, имеется процедура поиска активного номера телефона принадлежащего конкретному клиенту (для примера его Предположим, что при постановке задачи для разработчика не было описания каких-либо особых условий т.е. по условиям задачи предполагалось, что для конкретного пользователя (id = 43, идентификатор передается в качестве параметра) в таблице client_telnumbers всегда будет хотя бы одна запись с номером телефона клиента и признаком «активный» (значение поля enddate равно дате 31.12.5999 23:59:59, что означает что номер используется клиентом. В случае, любой другой даты в указанном поле означает, что номер перестал быть активным и более не используется), поэтому наша процедура будет выглядеть примерно так:
Исходный код демонстрационной процедуры
Важно! Представленный код является примерным (примитивным) и служит только для демонстрации логирования в рамках данной статьи. В своих статьях я не выкладываю текст кода из реально действующих БД. Надеюсь, вы понимаете, что в реальности указанная процедура написана гораздо сложнее.
*Исходный код других используемых объектов смотрите в Git
Если мы будем использовать логирование ошибок как показано в предыдущей статье, то с течением времени обнаружим, что идентифицировать ошибки из данной процедуры будет сложно. Поэтому для всех ошибок попадающих в обработку исключения «WHEN OTHERS» реализована процедура pkg_msglog.p_log_archerr, которая при первом возникновении ошибки автоматически присваивает ей уникальный код и сохраняет ошибку в таблице лога. В дальнейшем, при повторении данной ошибки процедура найдет ранее созданный код и использует его при логировании в таблице лога.
В итоге, после добавления блока «архитектурного» логирования (строки с 18 по 24), наша процедура будет выглядеть следующим образом:
Исходный код демонстрационной процедуры
*Исходный код других используемых объектов смотрите в Git
Исходный код таблицы
Ограничение в таблице на комбинацию (Имя объекта, код ошибки SQLCODE). При первом появлении ошибки создается запись в таблице и генерируется код ошибки «SYS0000» + счетчик ошибок. При повторном появлении указанной ошибки будет взят уже сгенерированный ранее код ошибки.
рис. Пример содержимого таблицы messagecodes_arch
*Исходный код других используемых объектов смотрите в Git
Обратите внимание, что при использовании описанной модели «архитектурного» логирования у вас появляется функционал позволяющий максимально быстро реагировать на первое появление ошибки (в конкретной функции/процедуре). Для этого необходимо реализовать отдельный мониторинг архитектурных ошибок, который постараюсь продемонстрировать в следующей (третьей) статье. Использование процедуры pkg_msglog.p_log_archerr не требует каких-либо действий кроме описания входных параметров.
Таким образом мы можем создать базовый шаблон процедуры (функции), использование которого позволит вам гарантированно отлавливать все архитектурные ошибки в вашем коде.
Шаблон процедуры/функции с архитектурным логированием
Рекомендую использовать данный шаблон для построения «Событийной модели логирования».
*Исходный код других используемых объектов смотрите в Git
В рамках событийной модели логирования, предполагается, что все архитектурные ошибки будут исправляться отдельной задачей т.е. основная цель это устранить повторное появление ошибок с кодом «SYS****» в таблице лога. В указанной задаче вам необходимо либо устранить причины возникновения данной ошибки, либо добавить отдельную обработку ошибки отличную от «when others», которую в дальнейшем будем назвать «пользовательское» логирование (в рамках данного цикла статей).
Пользовательское логирование событий
Предположим, что однажды в нашей процедуре get_telnumber произошла «архитектурная ошибка». В частности, для конкретного пользователя в таблице client_telnumbers хранится два номера телефона с признаком «активный». В таком случае, процедура «упадёт» с ошибкой «ORA-01422: too_many_rows». При этом, наш функционал архитектурного логирования сгенерировал новый код ошибки «SYS0061» и создал запись в таблице лога.
рис. Код архитектурной ошибки SYS0061
Самое важно в такой ситуации это не откладывать «на потом» исправление архитектурных ошибок. В идеале, необходимо создать отдельную задачу (баг) и в рамках неё устранить ошибку.
Для этого в процедуре get_telnumber добавлено исключение (exception) «too_many_rows» пользовательского логирования. Также, был создан справочник пользовательских ошибок отличный от архитектурного справочника, тем что в него все записи добавляются разработчиком «вручную». Наверное это самое слабое место во всей архитектуре логирования. Предполагается, что разработчик должен описать исключение (exception) и создать для него уникальный код ошибки. Также, желательно к указанной ошибке сформулировать читаемый текст ошибки (для своих коллег, пользователя, техподдержки и т.д.), что бывает иногда очень сложным (из личного опыта).
Таблица пользовательских ошибок и процедура их «регистрации» будет выглядеть следующим образом:
Исходный код таблицы пользовательских ошибок и процедуры регистрации
Регистрация пользовательских ошибок производится процедурой p_insert_msgcode. На вход подается код и текст ошибки. В случае, если по указанному коду нет записей в справочнике messagecodes, то создается новая запись (производится регистрация). В случае, если по коду ошибки найдена запись, то производится сравнение текстов ошибки, в случае расхождений производится обновление текста, иначе работа процедуры завершается без изменений. Таким образом мы всегда можем корректировать текст ошибок.
Итого, содержимое справочника пользовательских ошибок будет выглядеть следующим образом:
рис. Пример содержимого справочника пользовательских ошибок
*Исходный код других используемых объектов смотрите в Git
После того, как мы «зарегистрировали» пользовательскую ошибку «USR0003» и добавив отдельную обработку пользовательского логирования (строки с 19 по 28), наша процедура get_telnumber будет выглядеть следующим образом:
Исходный код демонстрационной процедуры
*Исходный код других используемых объектов смотрите в Git
Давайте рассмотрим еще один пример (кейс из реального случая), в момент когда процедура get_telnumber по id клиента находит один «активный» номер телефона иногда возникает ситуация, что номер телефона не принадлежит мобильному оператору. Ситуации бывают разные иногда указанный номер мог быть номером городской телефонной сети, иногда номером международного оператора, а иногда вообще набор из нескольких цифр и т.д. Основным требованием от бизнес-заказчика было использование номера телефона российских операторов мобильной связи. Поэтому было решено добавить проверку соответствия найденного номера некому «корректному» шаблону (строки с 18 по 29). В случае обнаружения некорректного номера, логировать данное событие отдельным кодом «USR0004» и типом «WRN». Добавим функцию проверки корректности номера телефона, если номер соответствует шаблону (требованиям), то вернем номер телефона, иначе пустое значение.
Исходный код демонстрационной процедуры
*Исходный код других используемых объектов смотрите в Git
После сбора статистических данных по конкретной ошибке с кодом «USR0004», руководству стало понятно, что ошибка актуальна и количество ошибок с течением времени не только не уменьшается, а наоборот растет с линейной прогрессией. В дальнейшем, были выявлены источники «кривых» данных и были установлены внутренние требования по первичной обработке номера телефона клиентов. В итоге, со временем количество ошибок уменьшилось до нуля. И этого нельзя было добиться до тех пор, пока у всех участвующих лиц не возникло понимание о масштабе проблемы.
Выполняя самый банальный запрос в таблицу лога с группировкой по типу сообщения (msgtype), имени объекта (objname) и вашему внутреннему коду ошибки (msgcode) за отдельный квартал, вы сможете увидеть реальную картинку частоты возникновения той или иной ошибки. Как только в вашей БД появляется ошибка с большим количеством повторений вы всегда сможете выявить это событие и принять решение об устранении.
Исходный код запроса
рис. Пример результата запроса с группировкой
*Исходный код других используемых объектов смотрите в Git
Заключение
В заключении наверное скажу банальную вещь, о том что ваша БД является сложным механизмом ежесекундно выполняющая рутинные операции. Прямо сейчас в БД могут происходить различные ошибки. Критичные, которые вы исправляете практически сразу или некритичные, о которых вы можете вообще не знать. И если у вас нет информации о подобных ошибках, то возникает вопрос: «Нужно ли их вообще исправлять? Или можно подождать до тех пор, пока проблема не всплывёт?». Вопрос наверное «риторический».
Я же данной статьёй хотел показать один из способов ведения логирования с кодированием отдельных событий. Данный метод требует некоторых «обязательств» от разработчика и в нынешнее время этого тяжело добиться. В следующей статье постараюсь показать один из способов мониторинга ошибок основанный напрямую по кодам ошибок созданных в текущей статье.