Oracle - функция вычисления контрольного разряда EAN-13

Функция для вычисления контрольного разряда штрихкода EAN13 по первым двенадцати цифрам.
На входе подаем строку (12-значное число).
На выходе получаем цифру контрольного разряда.
function GetControlNumber(p_code varchar2) return number
  is
   v_chet number;
   v_nechet number;
   v_itog number;
   v_res number;
  begin
     
     v_chet := to_number(substr(p_code,2,1))+to_number(substr(p_code,4,1))+to_number(substr(p_code,6,1))+to_number(substr(p_code,8,1))+to_number(substr(p_code,10,1))+to_number(substr(p_code,12,1));
     v_nechet := to_number(substr(p_code,1,1))+to_number(substr(p_code,3,1))+to_number(substr(p_code,5,1))+to_number(substr(p_code,7,1))+to_number(substr(p_code,9,1))+to_number(substr(p_code,11,1));
     v_itog := mod(((v_chet*3)+v_nechet),10);

     if v_itog>0 then 
       v_itog := 10 -v_itog;
     end if; 
     v_res:=v_itog;
     
   return v_res;
  end;


Пользуйтесь на здоровье.

Про алгоритм вычисления контрольного разряда можно прочитать здесь.

Oracle - Фичи и полезности (Часть №1)

Здесь буду выкладывать всякие полезные мелочи и запросы, которые могут пригодиться с их описанием.

1. Получаем список всех полей интересующей нас таблицы.
select  column_name 
  from user_tab_columns 
  where upper(table_name) = upper('название_таблицы')


2. Меняем формат даты по умолчанию в сессии.
alter session set NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi:ss';
— Пока сессия не закрыта, будет действовать установленный таким образом формат.

Читать дальше →

Oracle - Функции RANK() и DENSE_RANK() (Или учимся выбирать необходимые значения внутри множественной выборки)

Начнем с функции RANK(). С объяснения для чего она и в каких случаях она нам может пригодиться.

Функция RANK() — это очень полезная функция, она позволяет нам пронумеровать набор по некоторому группирующему значению внутри всего выбранного набора данных. Проще всего показать это на примере. Для этого создадим небольшую таблицу:
ID	PERSON	DT			SM

1	Роман	01.10.2016 11:51:31	545.3
2	Роман	01.10.2016 11:51:31	445.2
3	Роман	01.10.2016 11:51:31	145.3
4	Кирил	01.05.2016 16:51:31	99.5
5	Алена	01.07.2016 12:51:31	445.3
6	Роман	01.12.2016 16:51:31	876.1
7	Кирил	01.06.2016 16:51:31	237.22
8	Алена	01.12.2016 16:51:31	145.3
9	Алена	01.02.2016 14:51:31	534.7
10	Роман	01.03.2016 16:51:31	165.3
11	Кирил	01.04.2016 16:51:31	345.2


Пусть в этой таблице будет содержать некоторые выставленные счета разным пользователям PERSON на разные суммы SM в разное время DT.

Читать дальше →

Полезные мелочи при администрировании и работе с Oracle

Здесь буду публиковать полезные мелочи для БД Oracle, которые пригождаются в быту.

Узнаем все пути к каталогам Oracle
SELECT * FROM V$DIAG_INFO


Узнаем версию сервера Oracle
SELECT * FROM V$VERSION

Размер Таблиц и Tablespace в Oracle

Узнаем сколько занимает каждый TableSpace:
(Размер указан в мегабайтах)
select a.tablespace_name, totalspace, nvl(freespace,0) freespace,
   (totalspace-nvl(freespace,0)) used,
  ((totalspace-nvl(freespace,0))/totalspace)*100 "%USED"
from
  (select tablespace_name, sum(bytes)/1048576 totalspace
   from dba_data_files
   group by tablespace_name) a,
  (select tablespace_name, sum(Bytes)/1048576 freespace
   from dba_free_space
   group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
  and ((totalspace-nvl(freespace,0))/totalspace)*100 > 90
 order by 5 desc
 


Теперь узнаем сколько занимают таблицы в указанном TableSpace:
(Размер в мегабайтах, имя нужного TableSpace — берем из результатов предыдущего запроса, для всех при больших базах может выполняться долго)

select de.owner, de.segment_name, sum(de.bytes)/1048576, de.segment_type from dba_extents de
 where de.owner in ('нужный tablespace')
group by de.owner, de.segment_name, de.segment_type


Вот и всё.

Работа с деревьями в Oracle (PL/SQL)

В этой публикации объясняется, как работать со специфическими методами, которые присутствуют в базе данных Oracle, для работы с Деревьями. Итак, сразу к делу…

--Создаем таблицу для экспериментов с деревьями.
create table my_tree (
  id number,
  id_parent number,
  nm varchar2(100)
  );


--Добавляем нашей тестовой таблице индекс (для порядка).
create index idx_my_tree on my_tree(id,id_parent);

--Заполняем нашу тестовую таблицу классификатора (категориями продуктов).
insert into MY_TREE values (1,null, 'Продукты');
insert into MY_TREE values (2,1, 'Колбасы');
insert into MY_TREE values (3,1, 'Полуфабоикаты');
insert into MY_TREE values (4,1, 'Фрукты');
insert into MY_TREE values (5,3, 'Котлеты');
insert into MY_TREE values (6,3, 'Пельмени');
insert into MY_TREE values (7,3, 'Манты');
insert into MY_TREE values (8,3, 'Бифштексы');
insert into MY_TREE values (9,2, 'Копченая');
insert into MY_TREE values (10,2, 'Вареная');
insert into MY_TREE values (11,4, 'Помидоры');
insert into MY_TREE values (12,4, 'Огурцы');
insert into MY_TREE values (13,4, 'Бананы');
insert into MY_TREE values (14,2, 'Салями');


Читать дальше →

Проверка адреса электронной почты на корректность средствами PL/SQL

Простейшая проверка адреса электронной почты выглядит так:
select * from EMailsTable et where REGEXP_LIKE (et.Email, '[A-z0-9._%-]+@[A-z0-9._%-]+\.[A-z]{2,4}')

Скрипт выбирает из таблицы с E-Mail адресами только те адреса которых попадают под заданное регулярное выражение.

Пример создания временных таблиц на PL/SQL как в MSSQL

Для того, кто перешёл с MSSQL на Oracle, как например для меня, очень не привычно отсутствие такой удобной конструкции как:
select * into #tmp from table1

Где #tmp — временная таблица, с которой мы в рамках сессии можем далее обращаться как с обычной.
Однако в Oracle такая конструкция не работает. Мои попытки сходу найти какой либо аналог, успехом не увенчались. Однако свершилось чудо, и спустя некоторое время более опытный в этом вопросе коллега подсказал таки выход, который по сути реализует аналогичную вещь как и в MSSQL.

И так, вот как оказывается можно:
with

--Задаем временные таблицы
t_view1 as (select * from table1 where id = 1),

t_view2 as (select * from table2 where id = 1)

--Производим действия с использованием временных таблиц
select * from table3 t where
exists (select * from t_view1 v where v.id = t.id)
and exists (select * from t_view2 v where v.id = t.id)


И всё!..
(Кусочек кода не большой и думаю понятен, без излишнего разъяснения).