DB | Way23

Вывод простых чисел на Oracle

Задача с Hackerrank:

Напишите запрос выводящий простые числа меньшие или равные 1000.

Пример вывода постых чисел меньших или равных 10:

2&3&5&7

2&3&5&7

Решение

Разложим задачу на подзадачи:

Вывести список чисел от 2 до 1000
Отфильтровать из списка только простые числа
Объединить результат в одну строку

Для вывода списка чисел воспользуемся connect by

select 
  level + 1 n 
from 
  dual 
connect by 
  level <= 999

select

level + 1 n

from

dual

connect by

level <= 999

В результате появляется список строк с числами от 2 до 1000. Теперь нужно оставить только простые числа. Простые числа делятся только на единицу и на самих себя. Проверим это с помощью подзапроса. Так как в подзапросе понадобится тот же список чисел то вынесем его в with:

with nums as
(select 
  level + 1 n 
from 
  dual 
connect by 
  level <= 999)

select * from nums

with nums as

(select

level + 1 n

from

dual

connect by

level <= 999)

select * from nums

И добавим подзапрос:

with nums as
(select 
  level + 1 n 
from 
  dual 
connect by 
  level <= 999)

select 
  * 
from 
  nums n1
where 
  not exists (select 
                * 
              from 
                nums n2 
              where 
                n2.n <> n1.n 
                and mod(n1.n, n2.n) = 0
              )

with nums as

(select

level + 1 n

from

dual

connect by

level <= 999)

select

from

nums n1

where

not exists (select

from

nums n2

where

n2.n <> n1.n

and mod(n1.n, n2.n) = 0

)

Теперь осталось только соединить строки в одну. Для этого воспользуемся функцией listagg:

with nums as
(select 
  level + 1 n 
from 
  dual 
connect by 
  level <= 999)

select 
  listagg(n, '&') within group (order by n) n 
from 
  nums n1
where 
  not exists (select 
                * 
              from 
                nums n2 
              where 
                n2.n <> n1.n 
                and mod(n1.n, n2.n) = 0
              )

with nums as

(select

level + 1 n

from

dual

connect by

level <= 999)

select

listagg(n, '&') within group (order by n) n

from

nums n1

where

not exists (select

from

nums n2

where

n2.n <> n1.n

and mod(n1.n, n2.n) = 0

)

В результате получаем:

2&3&5&7&11&13&17&19&23&29&31&37&41&43&47&53&59&61&67&71&73&79&83&89&97&101&103&107&109&113&127&131&137&139&149&151&157&163&167&173&179&181&191&193&197&199&211&223&227&229&233&239&241&251&257&263&269&271&277&281&283&293&307&311&313&317&331&337&347&349&353&359&367&373&379&383&389&397&401&409&419&421&431&433&439&443&449&457&461&463&467&479&487&491&499&503&509&521&523&541&547&557&563&569&571&577&587&593&599&601&607&613&617&619&631&641&643&647&653&659&661&673&677&683&691&701&709&719&727&733&739&743&751&757&761&769&773&787&797&809&811&821&823&827&829&839&853&857&859&863&877&881&883&887&907&911&919&929&937&941&947&953&967&971&977&983&991&997

2&3&5&7&11&13&17&19&23&29&31&37&41&43&47&53&59&61&67&71&73&79&83&89&97&101&103&107&109&113&127&131&137&139&149&151&157&163&167&173&179&181&191&193&197&199&211&223&227&229&233&239&241&251&257&263&269&271&277&281&283&293&307&311&313&317&331&337&347&349&353&359&367&373&379&383&389&397&401&409&419&421&431&433&439&443&449&457&461&463&467&479&487&491&499&503&509&521&523&541&547&557&563&569&571&577&587&593&599&601&607&613&617&619&631&641&643&647&653&659&661&673&677&683&691&701&709&719&727&733&739&743&751&757&761&769&773&787&797&809&811&821&823&827&829&839&853&857&859&863&877&881&883&887&907&911&919&929&937&941&947&953&967&971&977&983&991&997

Oracle. Как не нужно генерировать первичный ключ

Идея поста возникал при прочтении Получение в программе значения первичного ключа после INSERT.

В Oracle младше 12 версии нет автоинкрементного поля. Стандратный способ генерации первичных ключей — последовательности. Но иногда разработчики генерируют первичный ключ таким способом:

select max(rid) + 1 from table

1	select max(rid) + 1 from table

Это приводит к проблемам при многопользовательской работе. Типичная ситуация — пользователи одновременно получают новое значение для ПК, а потом вставляют записи:

Пользователь1	Пользователь2
select max(rid) + 1 from table
	select max(rid) + 1 from table
insert
	insert

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

Пользователь1	Пользователь2
select max(rid) + 1 from table
	select max(rid) + 1 from table
insert
	insert
commit
	ORA-00001: нарушено ограничение уникальности

Для генерации ПК следует использовать последовательности или специальные механизмы вроде GUID. Иначе будут возникать ошибки.

Пример взаимоблокировки в Oracle

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

drop table test.emp;

create table test.emp
(
  rid  integer not null,
  name varchar2(100)
);

alter table test.emp
  add constraint table_pk primary key (rid);

insert into test.emp (rid, name) values (1, 'запись 1');
insert into test.emp (rid, name) values (2, 'запись 2');

commit;

drop table test.emp;

create table test.emp

(

rid integer not null,

name varchar2(100)

);

alter table test.emp

add constraint table_pk primary key (rid);

insert into test.emp (rid, name) values (1, 'запись 1');

insert into test.emp (rid, name) values (2, 'запись 2');

commit;

Следующая последовательность запросов update приводит к взаимной блокировке.

Сессия 1	Сессия 2
`update test.emp set name = '11' where rid = 1;`
	`update test.emp set name = '22' where rid = 2;`
`update test.emp set name = '22' where rid = 2;`
`Сеанс зависает`
	`update test.emp set name = '11' where rid = 1;`
	`ORA-00060: взаимная блокировка при ожидании ресурса`

Ошибка ORA-00060, как написано в документации, может появиться в случайной из этих сессий.

Ссылки

Подробная статья про взаимоблокировки

Аудит данных в Oracle на основе пакетных переменных и триггеров

В пакеты Oracle можно добавлять переменные. Эти переменные имеют свои значения для каждой сессии. Такое свойство позволяет организовать аудит — лог записей о том кто и когда менял данные в таблицах. Общий алгоритм такой:

Создаётся пакет с переменными.
После соединения программа записывает в эти переменные данные о пользователе: идентификатор пользователя, открытый модуль и всё что может понадобится для аудита.
На таблицы помещаются триггеры которые используют переменные из пакета аудита и записывают информацию о модификации данных в отдельную таблицу.

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

Читать далее Аудит данных в Oracle на основе пакетных переменных и триггеров

Oracle. not in и null

Если в колонке внутри подзапроса not in возможно появление null то это может привести к неверному поведению.

Рассмотрим пример, сделаем две таблицы:

create table test.table1 
  as select value v 
     from table(sys.odcinumberlist(1, 2, 3));

create table test.table2 
  as select value v 
     from table(sys.odcinumberlist(1, 3, null));

create table test.table1

as select value v

from table(sys.odcinumberlist(1, 2, 3));

create table test.table2

as select value v

from table(sys.odcinumberlist(1, 3, null));

Запрос с in возвращает то что ожидается

select 
  t.value 
from 
  test.table1 t 
where 
  t.value in (select t.value from test.table2);

select

t.value

from

test.table1 t

where

t.value in (select t.value from test.table2);

1
3

А вот запрос с not in не возвращает вообще ничего

select 
  t.value 
from 
  test.table1 t 
where 
  t.value not in (select t.value from test.table2);

select

t.value

from

test.table1 t

where

t.value not in (select t.value from test.table2);

И так будет всегда если в результате подзапроса присутствует null. Таким образом, если делается запрос с not in, всегда нужно проверить что колонка в подзапросе является not null. Или заменить условие, например на not exists.

Подробная статья про поведение null в Oracle, в том числе разобрана ситуация с not in.

Установка Oracle Instant Client в Windows

Instant Client — сокращенная версия клиента Oracle.

Установка состоит из трех этапов:

Загрузка файлов Instant Client
Установка системных переменных
Настройка подключения - файл tnsnames.ora

Читать далее Установка Oracle Instant Client в Windows

Установка PostgreSQL в Dokku

Внутри Dokku можно установить PostgreSQL с помощью плагина. После установки появится возможность создавать базы данных, подключатся к ним, связывать БД с приложениями.

Запускаем команду установки

sudo dokku plugin:install https://github.com/dokku/dokku-postgres.git postgres

1	sudo dokku plugin:install https://github.com/dokku/dokku-postgres.git postgres

Читать далее Установка PostgreSQL в Dokku

Июнь 2020
Пн	Вт	Ср	Чт	Пт	Сб	Вс
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30