Полезные мелочи и запросы, которые могут пригодиться с их описанием.
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';— Пока сессия не закрыта, будет действовать установленный таким образом формат.
Функция для вычисления контрольного разряда штрихкода 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;Начнем с функции 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.
Узнаем сколько занимает каждый 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_typecreate 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, 'Салями');Простейшая проверка адреса электронной почты выглядит так:
select * from EMailsTable et where REGEXP_LIKE (et.Email, '[A-z0-9._%-]+@[A-z0-9._%-]+\.[A-z]{2,4}')
Скрипт выбирает из таблицы с E-Mail адресами только те адреса которых попадают под заданное регулярное выражение.
Для того, кто перешёл с MSSQL на Oracle, как например для меня, очень не привычно отсутствие такой удобной конструкции как:
select * into #tmp from table1Где #tmp - временная таблица, с которой мы в рамках сессии можем далее обращаться как с обычной.
Однако в Oracle такая конструкция не работает. Мои попытки сходу найти какой либо аналог, успехом не увенчались. Однако свершилось чудо, и спустя некоторое время более опытный в этом вопросе коллега подсказал таки выход, который по сути реализует аналогичную вещь как и в MSSQL.
Ни для кого не секрет что при использовании DISTINCT вместе с SELECT мы получаем набор уникальных записей, но оказывается в Oracle, есть ещё один оператор для этих целей UNIQUE - причем из проведенных опытов, похоже дает большую производительность.
Получить версию Oracle
SELECT * FROM V$VERSION