-
Все о разработке в СУБД "Oracle" Теория, практика, SQL, PL/SQL, сертификация и многое другое ————————————————— Сотрудничество, вопросы, обучение - @denis_dbd Реклама - @reklama_dbd
Ревью функции с курсором
Постановка: смотрите пост вторника.
Анализ
Кратко: я бы такой код не пропустил на ревью.
см код выше
1️⃣ Стр 2. Возвращается тип без привязки к колонки таблицы. Нет причин не использовать жесткую привязку к колонке text.
2️⃣ Стр 4. Избыточное объявление курсора. Можно использовать системный тип sys_refcursor.
3️⃣ Стр 13 и 16. Закрывать курсор надо 100%. В штатном режиме он закроется. А вот в случае возникновения исключения - нет закрытия. Ошибка неопытного курсоро-пользователя. Добавляется блок exception с проверкой на открытость курсора и его закрытием if v_curs%is_open then close v_curs; end if;
4️⃣ Но это частности. Основной вопрос к коду: зачем использовать явный курсор? Ответ: не имеет никакого смысла. Код можно переписать, например, так:
function get_text_new(p_id t1.id%type)▫️В данном варианте, не важно id уникально или нет (как в первом так и во втором будет рендомно отдавать какую-то строку).
return t1.text%type
is
v_text t1.text%type;
begin
select max(t1.text)
into v_text
from t1 where t1.id = p_id;
return v_text;
end;
/
Задача
Вам на ревью поступил следующий PL/SQL-код:
create or replace function get_text(p_id t1.id%type)Что делает данный код? Корректный ли он?
return varchar2
is
type my_type_cur is ref cursor;
v_curs my_type_cur;
v_text t1.text%type;
begin
open v_curs for
select text from t1 where id = p_id;
fetch v_curs into v_text;
close v_curs;
return v_text;
end;
/
Задача на переменные пакета и его состояние
Постановка в посте вторника
Решение
Для опытных разработчиков, задача не составит никакой сложности. Однако, очень часто у новичков возникают трудности с пониманием сути пакетов, их состояний и хранящихся в них объектов.
В нашем случае создается пакет my_pack. В теле пакета объявлена внутренняя глобальная переменной g_hello и задан блок инициализации.
Глобальная внутренняя переменная видна во всем теле пакета в любой его части.
Блок инициализации выполняется, при первом обращении к пакету для инициализации его состояния. Так же может выполниться повторно, если состояние пакета было сброшено (например, перекомпиляция).
Порядок инициализации в пакете: значения в глобальных объектах, затем блок инициализации.
На момент выполнения процедуры say_hello значение в переменной будет ‘Hello’.
Правильный ответ: А
Это самые основы. Эти и другие темы будем разбирать на курсе PL/SQL.Основы старт 9.09 🎓
#решениезадачи #package
Ревью триггера
Постановка в посте вторника.
Исходный код триггера:
create or replace trigger my_tab_seq_tgНачнем-с.
before insert on my_tab
for each row
begin
if inserting then
if :new.seq_id is null then
select some_seq.nextval into :new.seq_id from dual;
end if;
end if;
end;
create or replace trigger my_tab_seq_tgЕсть еще интересная холиварная тема: а зачем нам такой триггер в принципе? Коллеги в чатике отчасти это уже обсудили.
before insert on my_tab
for each row
when (new.seq_id is null)
begin
:new.seq_id := some_seq.nextval;
end;
/
Друзья, всем привет!
Рад сообщить, о старте пятого потока курса “Oracle PL/SQL.Основы”.
Он будет полезен: QA-инженерам, разработчикам, аналитикам.
Основная цель: научить вас читать чужой и писать свой PL/SQL-код, достаточный для современной разработки и закрытия рабочих задач.
🔹22 видео с теорией;
🔹18 практик = единый проект;
🔹13 онлайн встреч в Zoom с разбором вопросов;
🔹закрытый телеграм-канал;
Практика построена таким образом, чтобы вы с нуля, шаг за шагом, создали API на PL/SQL для прототипа платежной системы. Начав с анонимных блоков вы закончите своим мини-фреймворком для Unit-тестирования. Пример.
Подробности и программа - здесь. Отзывы - здесь.
Для тех кто заинтересован, но пока не уверен, я создал промо-группу курса, в которой буду размещать подробности, отрывки с занятий, отвечать на вопросы (да вы можете их сразу задавать) и даже встретимся онлайн. Залетайте!
⚠️ Группа 10 человек, 5 мест уже забронировано.
#plsql_basic
Задача о трансформации запроса
Постановку смотрите в посте вторника.
Немного теоретической части
Любой запрос при hard parse проходит через стадию “оптимизации” для построения плана запроса. Одним из этапов оптимизации является трансформация запроса.
Например, запрос
select * from tab1 where col1 = … or col2 = …при определенных условиях будет преобразован в запрос вида:
select * from tab1 where col1 = …Первоначальный запрос, в конечном итоге, может отличаться от того, что будет выполняться. В документации отражены некоторые возможные трансформации.
union all
select * from tab1 where col2 = … and col1 not in ...
alter session set events '10053 trace name context forever';В результате выполнения в каталоге на сервере будет создан trc-файл с меткой PLAN_TRC_EXAMPLE1.
alter session set tracefile_identifier='PLAN_TRC_EXAMPLE1';
select t.first_name
from hr.employees t
join hr.departments d on d.department_id = t.department_id
where t.first_name like 'Alex%';
alter session set events '10053 trace name context off';
Очередная задачка на оптимизацию. Если вы периодически смотрите планы запросов, то могли столкнуться с подобным случаем.
Итак. Текст запроса
select t.first_nameПлан запроса на скриншоте ⬆️
from hr.employees t
join hr.departments d on d.department_id = t.department_id
where t.first_name like 'Alex%';
Задача по оптимизации
Полную постановку смотрите в посте вторника.
Объяснение
Типичная проблема при неправильном использовании механизмов СУБД. DBA очень часто негодуют по этому поводу.
Итак, поехали разбирать.
1️⃣ Представление v$sqlarea показывает все выполнявшиеся запросы в БД (parent-курсоры).
2️⃣ Каждый запрос имеет свой sql_id (уникальный номер), hash_value (хэш от текста запроса) и др.
3️⃣ Как только СУБД начинает выполнять запрос, происходит его парсинг. В том числе, берется hash от текста запроса и сравнивается с тем, что уже есть в SGA в кэше запросов. Если запрос не найден, то производится жесткий разбор (hard parse). В итоге, разобранный запрос помещается в кэш запросов.
Поменяете регистр одной буквы в запросе - это будет новый запрос и новый hard parse со всеми сопутствующими расходами.
Следующие запросы, с точки зрения БД, разные.
select * from tab1 where id = 1;Для каждого будет сгенерирован свой sql_id, свой план(возможно), будут помещены в кэш-запросов и т.п. множество накладных расходов.
select * from tab1 where id = 2;
select * from tab1 where id = :var;теперь в var можно подставлять значения 1, 2…и т.д.
select ... where t1… = :idбудет выполнен 1 раз hard parse и всё.
Всем привет!
Я сейчас активно пишу курс по оптимизации, поэтому следующая задачка будет из этой области, так сказать, по горячим следам.
Был выполнен такой запрос:
select parsing_schema_nameРезультаты на скриншоте.
,substr(t.sql_text, 1, 100)
,count(*) cnt
from v$sqlarea t
group by t.parsing_schema_name, substr(t.sql_text, 1, 100)
order by cnt desc;
Как просто хранить сложные объекты
Автор: Филипп Дельгядо
Еще один доклад с Podlodka.
Мне всегда интересно посмотреть как разные компании/команды проектируют свои приложения. Тема бездонная. Нет какой-то серебряной пули - единого подхода, который можно использовать везде и всегда.
Соответственно, видео/доклады на тему проектирования приложений особенно привлекают внимание.
В этом видосе Филипп рассказал про подход хранения сложных объектов в РСУБД простыми способами.
Совсем кратко: сложные объекты условно делятся на две части. Одна - это обычные поля, через которые поддерживается целостность данных, вторая часть - json, который хранится в одном поле. При этом каждая строка объекта помечается версией, ведь формат json'a может меняться. Средний слой учитывает версию при сериализации/десереализации объекта.
Подробней в его докладе.
Кстати, было бы интересно узнать мнение коллег, кто сталкивался с продуктами компании OpenWay - Way4 и, возможно, с такой архитектурой.
Всем приятного просмотра 🎥
Обсудить в чатике
#видео
Задача. Английский алфавит
Задача
Вывести одним запросом заглавные/прописные/большие буквы латинского алфавита, каждая буква - отдельная строка, без использования union.
Уровень сложности: легкий
Было бы круто посмотреть на различные способы решения.
Как всегда, разбор в четверг 🎓
Обсудить в чатике
#задача
Задача "Расписание"
Постановка в посте вторника.
Задача с тестового задания в IT “Магнит” (сейчас уже не дают).
Решить можно двумя способами
1️⃣ “В лоб” - написав парсер переданной строки на уровни и проходы по уровням с разной степени упоротости в реализации. Получится очень много кода.
2️⃣ “Хитрый” - используя пакет dbms_scheduler (пакет для управления фоновыми задачами). Гуглится на раз-два. Элегантное решение - парсится строка, преобразовывается в формат подходящий для задания расписания и используется процедура dbms_scheduler.evaluate_calendar_string. Процедура возвращает следующую дату запуска джоба от заданной даты в соответствии с расписанием срабатывания.
Подробное решение.
В далеком 2015м я решал её первым способом. Кода получилось многовато. С одной стороны это хорошо, можно посмотреть, что вообще может написать человек используя PL/SQL или SQL. Мои студенты "рождали" такие портянки, что становилось страшно 🤓
Если хотите попрактиковаться в написании кода на PL/SQL - рекомендую реализовать первым способом.
#решениезадачи #расписания
Отчет для отдела маркетинга
Задача с тестового задания с одной конторы в РФ. Довольно часто встречается в реальной работе.
Отделу маркетинга требуется сводная выгрузка по клиентам, с гранулярностью до клиента, при этом для каждого клиента в выборке должны быть «лучшие» адрес, телефон и адрес электронной почты. То есть, в результирующей выборке по каждому клиенту есть только одна строка.
При этом:
🔹Лучший адрес отбирается по приоритету фактический > регистрации > домашний, при наличии нескольких адресов одного приоритета выбирается наиболее полный (заполнено больше из перечня атрибутов city-street-house-flat, при равенстве по заполненности выбирается последний по дате внесения в базу.
🔹Лучший телефон это последний по дате внесения в базу.
🔹Лучший email это первый по дате внесения в базу.
🔹Данные по контактам и адресам – не архивные.
Полная постановка с примерами таблиц
Разбор решения в четверг 🎓
#задача
Всем хорошей пятницы и выходных 🎊
#юмор
Oracle Developer
Задача
Вопрос из теста в курсе PL/SQL.Основы. Рассчитан на изучающих PL/SQL.
На скриншоте представлен код пакета. Вызывается процедура пакета:
beginЧто будет выведено в буфер вывода? Варианты ниже ⬇️
my_pack.say_hello();
end;
/
Задача
Дан триггер:
create or replace trigger my_tab_seq_tgВопросы: все ли в порядке? можно ли его как то улучшить/изменить?
before insert on my_tab
for each row
begin
if inserting then
if :new.seq_id is null then
select some_seq.nextval into :new.seq_id from dual;
end if;
end if;
end;
/
Шутка в тему оптимизации 😉
Всем хорошей пятницы и выходных 🎊
#юмор
Oracle Developer
Пока не запретили английский 😉
Всем хорошей пятницы и выходных 🎊
#юмор
Классификация современных баз данных
Автор: Николай Голов
Этот доклад публиковали примерно год назад на Podlodka. Он вполне актуален на текущий момент.
Довольно интересный обзор на разного рода системы и СУБД.
Основная суть: как организовать выбор баз(ы) для своей системы, за который через пару лет не станут мучительно стыдно перед коллегами?
Из всего прочего, понравилась мысль "это нормально, когда на проекте используется более чем одна СУБД для своих узкоспециализированных задача".
Вроде бы простая мысль, но не сразу очевидная аля "всё пилим в Оракле и точка".
Конечно, речь идет про приложения среднего слоя.
Если у вас вся логика на Oracle PL/SQL, вы дергаете веб-сервисы из БД - эта история не совсем про вас.
Однако, для расширения кругозора рекомендую заценить. Вдруг вы уже начал процесс импортозамещения и потихоньку распиливаете ваш Oracle-монолит 😉
Всем приятного просмотра 🎥
Обсудить в чатике
#видео
Задача. Английский алфавит
Задача
Вывести одним запросом заглавные/прописные/большие буквы латинского алфавита, каждая буква - отдельная строка, без использования union.
Решение
На текущий момент, коллеги в чатике нагенерили около 20 способов решить данную задачку от совсем простых до монструозных чисто развлечься.
Приведу буквально одно:
select chr(ascii('A') + level - 1)
from dual
connect by level <= ascii('Z') - ascii('A') + 1;
Всем неравнодушным респект, было интересно посмотреть на такое разнообразие 🔥
Расписание
Есть матрица расписания запусков (см. скрин)
Первая строка – 15-и минутные интервалы, вторая - часовые интервалы, третья - дни недели, четвертая - дни месяца, пятая - месяцы года. С помощью данной матрицы задается периодичность запусков.
Требуется написать функцию на PL/SQL, которая бы возвращала дату следующего запуска от двух входных параметров:
1️⃣ дата, от которой ведется отчет;
2️⃣ это текстовая переменная, в которой перечислены все выбранные ячейки. Ячейки разделены «,» (запятой), а строки разделены «;» (точкой с запятой),
Например, для данного рисунка расписание будет выглядеть следующим образом: 0,45;0,4,8,12,17,22;2,6;1,2,3,4,5,11,18,24;1,2,3,9,11;
Контрольный пример
Дата отсчета: 09.07.2010 23:36
Строка: 0,45;12;1,2,6;3,6,14,18,21,24,28;1,2,3,4,5,6,7,8,9,10,11,12;
Результат: 18.07.2010 12:00
Примечание. В данном примере, используется американский календарь, в котором 1 – это воскресенье, 2 – понедельник и т.д.
Тестовое из Магнита (сейчас не дают).
#задача
Отчет для отдела маркетинга
Постановка в посте вторника.
Итоговый запрос
select c.idПояснения
,c.name
,max(a.city || ', ' || a.street || ', ' || a.house || ' fl. ' || a.flat)
keep(dense_rank first order by a.a_type desc, nvl2(a.city, 1, 0)
+ nvl2(a.street, 1, 0) + nvl2(a.house, 1, 0) + nvl2(a.flat, 1, 0) desc, a.created desc) address
,max(ph.c_info) keep(dense_rank first order by ph.created desc) phone
,max(em.c_info) keep(dense_rank first order by em.created asc) email
from client c
left join address a on a.client_id = c.id and a.active = 'Y'
left join contact ph on ph.client_id = c.id and ph.c_type = 1 and ph.active = 'Y'
left join contact em on em.client_id = c.id and em.c_type = 2 and em.active = 'Y'
group by c.id, c.name;
Задача с курсами валют
Постановку задачи смотрите в посте вторника.
Довольно баянная задача. Периодически встречается на собесах.
1. Для решения используем конструкцию keep dense rank в режиме группировки по name (group by name).
2. Сортировка внутри групп по колонке date по убыванию (desc). Если есть null-значения в колонке date, то они уйдут в самый верх отсортированных значений (nulls last).
3. first - берем первый результат ранжирования внутри группы ибо desc-сортировка.
4. Для каждого требуемого столбца результата выбираем соответствующие столбцы - date/rate. min - получает минимальное значение из первого результата. В данном случае, без разницы min/max.
Итого:
select t.name
,min(ddate) keep(dense_rank first order by ddate desc nulls last) ddate
,min(rate) keep(dense_rank first order by ddate desc nulls last) rate
from FxRates t
group by t.name;
Запрос можно переписать на такой:
select t.name
,min(ddate) keep(dense_rank last order by ddate asc nulls first) ddate
,min(rate) keep(dense_rank last order by ddate asc nulls first) rate
from FxRates t
group by t.name;
Объяснения может не хватить, особенно, если вы никогда не сталкивались с этим. Рекомендую воссоздать тестовый пример и поиграться с конструкцией keep dense_rank first/last.
У меня в загашнике есть еще одна очень интересная задачка на эту тему 😉
#решениезадачи #яндекс