Функция ANY_VALUE
Функция ANY_VALUE позволяет не включать поле в список GROUP BY, по которым проводится группировку. Значение в колонке ANY_VALUE выбирается первым со значением NOT NULL. Ключевое слово DISTINCT допускается использовать в ANY_VALUE, но оно не функционирует.
select
d.deptno,
any_value(DISTINCT d.dname) AS deptname,
any_value(e.ename) AS ename,
count(e.empno) AS employee_count
from
dept d
left outer join
emp e
on d.deptno = e.deptno
group by
d.deptno order by1;
Улучшенный синтаксис аналитических функций
WINDOW
В предыдущих версиях оконная рамка определялась непосредственно в вызове аналитической функции. В Oracle 21c оконная рамка может быть определена отдельным предложением, что позволяет использовать её одновременно в несколькох аналитических функциях. Также можно определить несколько оконных рамок. Оконную рамку можно расширять по разному в кажом месте использования.
select
empno,
deptno,
sal,
first_value(sal) over (w1 rows between unbounded preceding and current row) as lowest_in_dept,
first_value(sal) over w2 as highest_in_dept,
rank() over w1 as sal_rank_in_dept
from
emp
window w1 as (partition by deptno order by sal),
w2 as (partition by deptno order by sal desc);
GROUPS
Дополнительно к ROWS и RANGE было добавлено ещё одно - GROUPS. При использовании GROUPS данные делятся на группы на основе упорядоченных значений. Текущая строка является частью текущей группы, поэтому ссылки на предыдущую или следующую относятся к предыдущей или следующей группе, а не к конкретным строкам. Подобна RANGE количество строк в окне не известно до тех пор, пока данные не будут упорядочены.
select
row_number () over (
order by
sal) as row_order,
sal,
avg(sal) over w1 as avg_rows,
avg(sal) over w2 as avg_groups,
avg(sal) over w3 as avg_range
from
emp window w1 as (order by sal rows between 1 preceding and current row),
w2 as (order by sal groups between 1 preceding and current row),
w3 as (order by sal range between 1 preceding and current row);
EXCLUDE
Ключевое слово EXCLUDE имеет несколько опций, позволяющих исключить различные строки из оконных рамок.
EXCLUDE NO OTHERS: ничего не исключает. Равносильно отсутствию слова EXCLUDE.
EXCLUDE CURRENT ROW: исключает только текущую строку.
EXCLUDE GROUP: исключает текущую группу, текущую строку и все остальные строки с таким же значением.
EXCLUDE TIES: удаляет все строки с таким же значением, как у текущей строки.
EXCLUDE может использоваться с ROWS, RANGE и GROUPS.
select
row_number () over w1 as row_order,
value,
avg(value) over (w1 rows between 1 preceding and 1 following exclude current row) as ex_current_row,
avg(value) over (w1 rows between 1 preceding and 1 following exclude group) as ex_group,
avg(value) over (w1 range between unbounded preceding and current row exclude ties) as ex_ties
from
t1 window w1 as (order by value);
Таблица Blockchain
В Oracle Database 21c появился новый тип таблицы - блокчейн таблица - это защищенная от несанкционированного доступа таблица, предназначенная только для вставки, с соответствующим периодом хранения строк и самой таблицы. Строки организованы в цепочки, где каждая строка содержит хэш-сумму данных, содержащихся в строке, и хэш-сумму данных предыдущих строк.
Пример создания таблицы:
create blockchain table bct_t1 (
id number,
fruit varchar2(20),
quantity number,
created_date date,
constraint bct_t1_pk primary key (id) )
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v1";
NO DROP : таблица не может быть удалена (только если в ней уже есть строки). Таблица удалится при удалении схемы.
NO DROP UNTIL number DAYS IDLE : таблица не может быть удалена пока не пройдет указанное количество дней, в течении который не будет вставлена ни одна строка.
NO DELETE определяеть срок хранения строк - как долго каждая строка будет защищена от удаления.
Тип данных JSON
В таблице можно создавать колонки с типом данных JSON.
create table t1 (
id number generated always as identity,
json_data json,
constraint ta_pk primary key (id)
);
SQL Macros
Использование вызова функци в SQL-запросах замедляет выполнение запроса из-за переключения контекстов SQL и PL/SQL. Для решения этой проблемы было придумано ключевое слово sql_macro, которое заставляет опитизатор выполнять подстановку во время оптимизации запроса.
create or replace function calculate_tax(p_value number)
return varchar2 sql_macro(scalar) is
begin
return q'{ p_value * 0.2 }';
end;
/
... Продолжение следует ... Статья не завершена ...
