Функции oracle


Содержание

Функции oracle

Создание функций Oracle PL/SQL, команда CREATE FUNCTION, вызов функции, возврат информации из функции

Задание:

Создайте функцию в схеме hr в базе данных Oracle со следующими параметрами:

  • функция должна называться fSalary ;
  • она должна принимать в качестве входящего параметра номер сотрудника;
  • она должна возвращать размер заработной платы для данного сотрудника на основе информации из таблицы hr . employees .

Напишите код PL / SQL , в котором бы производился вызов этой функции и в окно приложения выводилась бы информация, возвращаемая этой функцией.

Решение:

Код для создания соответствующей функции может выглядеть так:

create function fSalary(emp_id IN number) RETURN number

is nSalary number(10,2);

SELECT salary into nSalary from employees where employee_ >

Код для применения этой функции может быть таким:

Встроенные функции Oracle

дата 22.05.2010
автор armitage
голосов 7

Функции для работы со строками в Oracle.

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

1) Функция определения длины строки LENGTH(строка), возвращает количество символов в строке, включая концевые пробелы.

2) Функции преобразования регистров символов UPPER(строка), LOWER(строка), INITCAP(строка). Для преобразования символов к верхнему регистру используется функция UPPER().

Если необходимо преобразовать символы строки к нижнему регистру используется функция LOWER().

Функция INITCAP преобразовывает каждый первый символ слова к верхнему регистру, а все остальные символы к нижнему при условии, что символ-разделитель между словами пробел.

вернет строку String1 String2.

3) Функции для обрезания начальных и концевых пробелов LTRIM(строка), RTRIM(строка), TRIM(строка). Соответственно первая функция обрезает все начальные пробелы строки, вторая – все концевые, а третья все начальные и концевые.

SELECT LTRIM(‘ str1’) FROM DUAL вернет строку str1,
SELECT RTRIM(‘str2 ’) FROM DUAL вернет строку str2,
SELECT TRIM(‘ str3 ’) FROM DUAL вернет строку str3.

4) Функция замены части строки другой строкой REPLACE(исходная_строка, заменяемая_подстрока, заменяющая_подстрока). Для большей ясности рассмотрим пример, в некотором текстовом поле таблицы хранится число. Причем символ-разделитель между целой и дробной частью в некоторых полях «.», а нам для дальнейшей обработки данных нужно, чтобы он во всех полях должен быть «,». Для этого воспользуемся функцией REPLACE следующим образом. REPLACE(field1, ’.’, ’,’) и все символы «.» в поле field будут заменены на символ «,».

SELECT REPLACE(‘My_string’,’_’,’@’) FROM DUAL вернет строку My@string.

5) Функции преобразования данных к другим типам данных. TO_CHAR(число) преобразует число в текст. TO_NUMBER(строка) преобразует текст в число. TO_DATE(строка, формат_даты) преобразует строку в дату определенного формата.

SELECT TO_CHAR(123) FROM DUAL вернет строку 123,
SELECT TO_NUMBER(‘12345’) FROM DUAL вернет число 12345,
SELECT TO_DATE(’01.01.2010’,’dd.mon.yyyy’) FROM DUAL вернет дату 01.JAN.2010.

6) Функция определения вхождения подстроки в строку INSTR(исходная_строка, подстрока, номер_символа). Даная функция позволяет определять номер символа в исходной строке с которого начинается искомая подстрока (если такая есть). Иначе возвращается 0. Например нам нужно определить все должности в таблице Table1, в наименовании которых встречается подстрока «менеджер». Для этого вполне подойдет следующий оператор

То есть оператор SELECT выведет только те записи из таблицы TABLE1 где искомая подстрока «менеджер» будет найдена. Причем поиск будет осуществляться с первого символа. Если поиск нужно осуществлять с другой позиции, то номер символа для начала поиска указывается в третьем параметре.

SELECT INSTR(‘Small string’, ‘string’, 1) FROM DUAL вернет значение 7,
SELECT INSTR(‘Small string’, ‘String’, 1) FROM DUAL вернет значение 0.

7) Функция выделения в исходной строке подстроки SUBSTR(исходная_строка, номер_начального_символа, количество_символов). Рассмотрим такой пример, в пользовательской таблице хранится адрес в виде наименование населенного пункта, название улицы, номер дома. Причем мы точно знаем, что для наименования населенного пункта отводится строго 20 символов (если наименовании населенного пункта меньше чем 20 символов, то остальная часть заполняется пробелами), для наименования улицы 30 символов, для номера дома 3 символа. Далее нам необходимо перенести все адреса из нашей таблицы в другую и при этом все 3 компонента адреса должны быть в разных полях. Для выделения компонент адреса применим функцию SUBSTR().

Конечно для переноса данных необходимо воспользоваться оператором INSERT, но для понимания работы функции SUBSTR вполне подойдет рассмотренный пример.
SELECT SUBSTR(‘My_string’, 4, 3) FROM DUAL вернет строку str.

Рассмотренные выше функции можно использовать во входных параметрах. Так если нам нужно выделить все символы, после какого-то определенного, то в функцию SUBSTR можно передать номер искомого символа из функции INSTR. Например если нужно перенести все символы из поля таблицы, которые расположены после «,» то можно использовать такую конструкцию
SELECT SUBSTR(My_string, INSTR(My_string, ‘,’, 1), LENGTH(My_string)- INSTR(My_string, ‘,’, 1)+1) FROM DUAL.
Для определения начального символа мы вызываем функцию INSTR(), которая вернет номер символа первого вхождения подстрок «,». Далее мы определяем количество символов до конца строки как разницу длины строки и номера первого вхождения подстроки.

8) Для определения кода символа используется функция ASCII(строка), которая возвращает код 1 символа строки. Например

SELECT ASCII(W) FROM DUAL вернет значение 87.

9) Обратная функция преобразования кода символа в символ CHR(число).

SELECT CHR(87) FROM DUAL вернет символ W.

Функции для работы с числами в Oracle.

В СУБД Oracle имеется ряд функций для работы с числами. К ним относятся функции возведение числа в степень POWER(), округление ROUND() и т. д.

1) Функция ABS(число) возвращает абсолютное значение аргумента.
SELECT ABS(-3) FROM DUAL вернет значение 3.

2) Функция CEIL(число) возвращает наименьшее целое, большее или равное переданному параметру.
SELECT CEIL(4.5) FROM DUAL вернет значение 5.

3) Функция FLOOR(число) возвращает наибольшее целое, меньшее или равное переданному параметру.
SELECT FLOOR(3.8) FROM DUAL вернет значение 3.

4) Функция MOD(число_1, число_2) возвращает остаток от деления первого параметра на второй.
SELECT MOD(5, 3) FROM DUAL вернет значение 2. Примечание. Если второй параметр равен 0, то функция возвращает первый параметр.

5) Функция округления ROUND(число_1, число_2). Округляет первый переданный параметр до количества разрядов, переданного во втором параметре. Если второй параметр не указан, то он принимается равным 0, то есть округление производится до целого значения. Примеры
SELECT ROUND(101.34) FROM DUAL вернет значение 101,
SELECT ROUND(100.1268, 2) FROM DUAL вернет значение 100.13
SELECT ROUND(1234000.3254, -2) FROM DUAL вернет значение 1234000,
SELECT ROUND(-100.122, 2) FROM DUAL вернет значение -100.12.

6) Функция усечения значения TRUNC(число_1, число_2). Возвращает усеченное значение первого параметра до количества десятичных разрядов, указанного во втором параметре. Примеры
SELECT TRUNC(150.58) FROM DUAL вернет значение 150
SELECT TRUNC(235.4587, 2) FROM DUAL вернет значение 235.45
SELECT TRUNC(101.23, -1) FROM DUAL вернет значение 100

7) В СУБД Oracle имеется ряд тригонометрических функций SIN(число), COS(число), TAN(число) и обратные им ACOS(число), ASIN(число), ATAN(число). Они возвращают значение соответствующей названию тригонометрической функции. Для прямых функции параметром является значение угла в радианах, а для обратных – значение функции. Примеры
SELECT COS(0.5) FROM DUAL вернет значение 0.877582561890373
SELECT SIN(0.5) FROM DUAL вернет значение 0.479425538604203
SELECT TAN(0.5) FROM DUAL вернет значение 0.546302489843791
SELECT ACOS(0.5) FROM DUAL вернет значение 1.0471975511966
SELECT ASIN(0.5) FROM DUAL вернет значение 0.523598775598299
SELECT ATAN(0.5) FROM DUAL вернет значение 0.463647609000806

8) Гиперболические функции. SINH(число),
COSH(число), TANH(число). SINH() возвращает гиперболический синус переданного параметра, COSH() возвращает гиперболический косинус переданного параметра, TANH() возвращает гиперболический тангенс переданного параметра. Примеры
SELECT COSH(0.5) FROM DUAL вернет значение 1.12762596520638
SELECT SINH(0.5) FROM DUAL вернет значение 0.521095305493747 SELECT TANH(0.5) FROM DUAL вернет значение 0.46211715726001

9) Функция возведения в степень POWER(число_1, число_2). Примеры
SELECT POWER(10, 2) FROM DUAL вернет значение 100
SELECT POWER(100, -2) FROM DUAL вернет значение 0.0001

10) Логарифмические функции. LN(число) возвращает натуральный логарифм переданного параметра, LOG(число_1, число_2) возвращает логарифм второго переданного параметра по основанию, переданному первом параметре. Причем первый параметр должен быть больше нуля и не равен 1. Примеры
SELECT LN(5) FROM DUAL вернет значение 1.6094379124341
SELECT LOG(10, 3) FROM DUAL вернет значение 0.477121254719662

11) Функция извлечения квадратного корня SQRT(число). Пример
SELECT SQRT(4) FROM DUAL вернет значение 2.

12) Функция возведение числа е в степень EXP(число). Пример
SELECT EXP(2) FROM DUAL вернет значение 7.38905609893065.

Функции для работы с датами в Oracle

На практике очень часто необходимо анализировать данные в виде дат, производить некоторые операции над ними, изменять формат. Все эти операции уже реализованы в виде встроенных функций. Рассмотрим самые основные из них.

1) ADD_MONTHS(дата, количество_месяцев) возвращает дату, отстоящую от даты, переданной в первом параметре на количество месяцев, указанном во втором параметре. Примеры
SELECT ADD_MONTHS(’01-JAN-2010’, 2) FROM DUAL вернет дату ’01.03.2010’
SELECT ADD_MONTHS(’01-JAN-2010’, -3) FROM DUAL вернет дату ’01.10.2009’
SELECT ADD_MONTHS(’30-JAN-2010’, 1) FROM DUAL вернет дату ’28.02.2010’

2) Для определения текущей даты и времени применяется функция SYSDATE. Область применения данной функции намного шире чем может показаться на первый взгляд. В первую очередь это контроль за вводом данных в БД. Во многих таблицах выделяется отдельное поля для сохранения даты последнего внесения изменений. Также очень удобно контролировать некие входные параметры для отчетов, особенно если они не должны быть больше чем текущая дата. Помимо даты данная функция возвращает еще и время с точностью до секунд. Пример
SELECT SYSDATE FROM DUAL вернет дату ‘22.05.2010 14:51:20’

3) Если необходимо определить последний день месяца, то для этого вполне подойдет функции LAST_DAY(дата). Её можно использовать для определения количества дней, оставшихся в месяце.
SELECT LAST_DAY(SYSDATE) – SYSDATE FROM DUAL.
В результате выполнения данного оператора будет выведено количество дней от текущей даты до конца месяца. Пример
SELECT LAST_DAY(’15-FEB-2010’) FROM DUAL вернет дату ’28.02.2010’.

4) Функция для определения количества месяцев между датами MONTHS_BETWEEN(дата_1, дата_2). Примеры
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’01-JAN-2010’) FROM DUAL вернет значение -6
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’10-JAN-2010’) FROM DUAL вернет значение -6.29032258064516.
Примечание. Если дни месяцев совпадают, то функция возвращает целое число, в противном случае результат будет дробным, причем количество дней в месяце будет принято 31.

5) Функция NEXT_DAY(дата, день_недели) позволяет определить следующую дату от даты, переданной в первом параметре, которая соответствует дню недели, переданном во втором параметре. Пример
SELECT NEXT_DAY(’01-JUL-2009’, ’mon’) FROM DUAL вернет дату ‘06.07.2009’, то есть следующий понедельник после 1 июля 2009 наступил 6 числа.

Значение параметра День недели
mon Понедельник
tue Вторник
wed Среда
thu Четверг
fri Пятница
sat Суббота
sun воскресенье

6) Округление даты ROUND(дата, формат). Второй параметр не обязателен, если его не указывать, то он принимается за ‘DD’, то есть округление будет произведено до ближайшего дня. Примеры
SELECT ROUND(SYSDATE) FROM DUAL вернет дату ‘23.05.2010’
SELECT ROUND(SYSDATE, MONTH) FROM DUAL вернет дату ‘01.06.2010’, округляется до ближайшего первого дня месяца.

Формат Единица округления
СС, SCC Век
SYYYY, YYYY, YEAR Год
Q Квартал
MM, MONTH Месяц
WW Тот же день недели, что и первый день года
W Тот же день недели, что и первый день месяца
DD, J День
Day, DY Первый день недели
HH, HH12, HH24 Час
MI Минута

7) Усечение даты. Функция TRUNC(дата, формат). Также как и рассмотренная выше может не иметь второго параметра. В таком случае усечение будет производиться до ближайшего дня. Примеры
SELECT TRUNC(SYSDATE) FROM DUAL вернет дату ’22.05.2010’
SELECT TRUNC(SYSDATE, ‘WW’) FROM DUAL вернет дату ’01.05.2010’
SELECT TRUNC(SYSDATE, ‘Day’) FROM DUAL вернет дату ‘16.05.2010’.


Функции преобразования данных в Oracle

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

1) TO_CHAR(данные, формат). На первый взгляд синтаксис довольно прост, но за счет второго параметра можно очень точно описать в какой формат преобразовать данные. Итак в строку можно преобразовать как дату, так и числовое значение. Рассмотрим вариант преобразования даты к строке. Значения самых распространенных форматов приведены в таблице, более полная информация содержится в технической документации.

Формат Описание формата
D День недели
DD День месяца
DDD День года
MM Номер месяца
MON Сокращенное название месяца
MONTH Полное название месяца
Q Квартал
YY, YYY, YYYY Год
HH, HH12, HH24 Час
MI Минут
SS Секунда

Таблица значений форматов для преобразования числа в строку.

Формат Описание формата
99D9 Указание позиции разделителя десятичной точки. Число девяток соответствует максимальному количеству цифр
999G99 Указание позиции группового разделителя
99,999 Возвращает запятую в указанной позиции
99.999 Возвращает точку в указанной позиции
99V9999 Возвращает значение умноженное на 10 в степени n, где n число девяток после V.
0999 Возвращает ведущие нули, а не пробелы
9990 Возвращает конечные нули, а не пробелы
9.99EEEE Возвращает число в экспоненциальной форме
RM Возвращает число в римской системе исчисления

SELECT TO_CHAR(SYSDATE, ‘D-MONTH-YY’) FROM DUAL вернет строку ‘7-MAY -10’
SELECT TO_CHAR(SYSDATE, ‘DDD-MM-YYYY’) FROM DUAL вернет строку ‘142-05-2010’
SELECT TO_CHAR(SYSDATE, ‘Q-D-MM-YYY’) FROM DUAL вернет строку ‘2-7-05-010’
SELECT TO_CHAR(1050, ‘9.99EEEE) FROM DUAL вернет строку ‘ 1.050E+03’
SELECT TO_CHAR(1400, ‘9999V999’) FROM DUAL вернет строку ‘1400000’
SELECT TO_CHAR(48, ‘RM’) FROM DUAL вернет строку ‘ XLVIII’

2) Функция преобразования строки в дату TO_DATE(строка, формат). Возможные значения форматов уже рассмотрены выше, поэтому приведу несколько примеров использования данной функции. Примеры
SELECT TO_DATE(’01.01.2010’, ‘DD.MM.YYYY’) FROM DUAL вернет дату ‘01.01.2010’
SELECT TO_DATE(’01.JAN.2010’, ‘DD.MON.YYYY’) FROM DUAL вернет дату ‘01.01.2009’
SELECT TO_DATE(’15-01-10’, ‘DD-MM-YY’) FROM DUAL вернет дату ‘15.01.2010’.

3) Функция преобразования строки в числовое значение TO_NUMBER(строка, формат). Самые распространенные значения форматов перечислены в таблице, поэтому рассмотрим применение данной функции на примерах. Примеры
SELECT TO_NUMBER(‘100’) FROM DUAL вернет число 100
SELECT TO_NUMBER(‘0010.01’, ’9999D99’) FROM DUAL вернет число 10.01
SELECT TO_NUMBER(‘500,000′,’999G999’) FROM DUAL вернет число 500000.

Если Вам понравилась статья, проголосуйте за нее

Окна в мир аналитических функций Oracle: введение

Эта статья является переводом статьи из журнала Oracle, который публикуют каждые два месяца. Ссылка на оригинал — тыц

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

Для выполнения примеров используется схема HR. Если у вас не установлена такая схема, необходимо выполнить скрипт:

Илон Маск рекомендует:  Что такое код imagefilledpolygon

Developer notes

Oracle. Аналитические функции.

Общий синтаксис для использования аналитических функций следующий

Рассмотрим основные части данного синтаксиса.

Синтаксис для задания конструкции фрагментации выглядит следующим образом

partition by выражение [, выражение] [, выражение]

Данная конструкция логически разбивает результирующее множество на N групп
по критериям, задаваемым выражениями фрагментации. Аналитические функции применяются к каждой группе независимо, — для каждой новой группы они сбрасываются. Если не указать конструкцию фрагментации, все результирующее множество считается
одной группой.

Конструкция упорядочения имеет следующий синтаксис

order by выражение [asc | desc] [nulls first | nulls last]

Конструкция order by задает критерий сортировки данных в каждой группе (в каждом фрагменте). Это, несомненно, влияет на результат выполнения любой аналитической функции. При наличии (или отсутствии) конструкции order by аналитические функции вычисляются по-другому. Например.
— без конструкции order by

select ename, sal, avg(sal) over ()
from emp

— с конструкцией order by

select ename, sal, avg(sal) over (order by ename)
from emp

Здесь стоит отметить следующее, на самом деле наличие конструкции order by в вызове аналитической функции добавляет стандартную конструкцию окна — RANGE UNBOUNDED PRECEDING. Это означает, что для вычисления используется набор из всех предыдущих и текущей строки в текущем фрагменте. При отсутствии order by стандартным окном является весь фрагмент. То есть по-сути предыдущий запрос будет выглядеть следующим образом

select ename, sal, avg(sal) over (order by ename RANGE UNBOUNDED PRECEDING)
from emp

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

select deptno, ename, sal,
sum(sal) over (partition by deptno order by ename rows 2 preceding) sliding_total
from emp
order by deptno, ename

Можно создавать окна по двум критериям: по диапазону (RANGE) значений данных или по
смещению (ROWS) относительно текущей строки
. Использование конструкции range как было сказано ранее в некоторых случаях используется неявно, RANGE UNBOUNDED PRECEDING например. Она требует брать все строки вплоть до текущей, в соответствии с порядком, задаваемым конструкцией order by. Следует помнить, что для использования окон
необходимо задавать конструкцию order by.

Окно определяется диапазоном строк, объединяемых в соответствии с заданным порядком.
Применять конструкцию range можно либо с числовыми выражениями (NUMBER), либо с выражениями, значением которого является дата (DATE). Еще одно ограничение для таких окон состоит в том, что в конструкции order by может быть только один столбец — диапазоны по природе своей одномерны. Нельзя задать диапазон в N-мерном пространстве. Пример.
Пусть необходимо выбрать зарплату каждого сотрудника и среднюю зарплату всех принятых на работу в течение 100 предыдущих дней, а также среднюю зарплату всех принятых на работу в течение 100 следующих дней. Соответствующий запрос будет выглядеть так:

select ename, hiredate, sal,
avg(sal) over (order by hiredate asc range 100 preceding) avg_sal_100_days_before,
avg(sal) over (order by hiredate desc range 100 preceding) avg_sal_100_days_after
from emp
order by hiredate desc

Помимо определения окна по диапазону (RANGE), также окна определяются и по количеству строк (ROWS). Для окон по строкам нет ограничений, присущих окнам по диапазону; данные могут быть любого типа и упорядочивать можно по любому количеству столбцов.
Например, пусть нужно вычислить среднюю зарплату для сотрудника и пяти принятых на работу до него и после него. Запрос можно записать следующим образом

select ename, hiredate, sal,
avg(sal) over ( order by hiredate asc rows 5 preceding ) avg_5_before,
avg(sal) over ( order by hiredate desc rows 5 preceding) avg_5_after
from emp
order by hiredate

Зная как определяются окна (по диапазону или по количеству строк), рассмотрим как окончательно задаются окна. В простейшем случае, окно задается с помощью одной из трех следующих взаимоисключающих конструкций.

— UNBOUNDED PRECEDING.
Окно начинается с первой строки текущей группы и заканчивается текущей обрабатываемой строкой.

— CURRENT ROW.
Окно начинается (и заканчивается) текущей строкой.

— Числовое_выражение PRECEDING.
Окно начинается со строки за числовое_выражение строк до текущей, если оно задается по строкам, или со строки, меньшей по значению столбца, упомянутого в конструкции order by, не более чем на числовое выражение, если оно задается по диапазону.

— Числовое_выражение FOLLOWING.
Окно заканчивается (или начинается) со строки, через числовое_выражение строк после текущей, если оно задается по строкам, или со строки, большей
по значению столбца, упомянутого в конструкции order by, не более чем на числовое_выражение, если оно задается по диапазону.

Стоит отметить, что окно CURRENT ROW в простейшем виде, вероятно, никогда не используется, поскольку ограничивает применение аналитической функции одной текущей строкой, а для этого аналитические функции не нужны. В более сложном случае для окна задается также конструкция BETWEEN. В ней CURRENT ROW можно указывать в качестве начальной или конечной строки окна. Начальную и конечную строку окна в конструкции BETWEEN можно задавать с использованием любой из перечисленных выше конструкций. Например, можно задать окно так, что обрабатываемая строка не будет
последней, а окажется где-то в середине окна

select ename, hiredate,
first_value(ename) over (order by hiredate asc range between 100 preceding and 100 following),
last_value(ename) over (order by hiredate asc range between 100 preceding and 100 following)
from emp
order by hiredate asc

В данном запросе дополнительно использованы функции first_value() и last_value(), которые возвращают первое значений текущего окна и последнее значение также текущего окна, соответственно, в то время как диапазон окна ограничен слева текущая скользящая дата — 100 дней и справа к текущей скользящей дате + 100 дней, в этом и состоит смысл выражения — between 100 preceding and 100 following .

select
level,
count(*) over (order by level asc rows 2 preceding) asc_count,
count(*) over (order by level desc rows 2 preceding) desc_count
from dual
connect by level

Окно rows 2 preceding, как видно из результата запроса, содержит от 1 до 3 строк (это определяется тем, как далеко текущая строка находится от начала группы). Для первой строки группы имеем значение 1 (предыдущих строк нет). Для следующей строки в группе таких строк 2. Наконец, для третьей и далее строк значение count(*) остается постоянным, поскольку мы считаем только текущую строку и две предыдущие.

select
n,
sum(n) over (order by days range 2 preceding) n_sum,
days
from (
select
level n,
( to_date(‘10.01.2014’, ‘dd.mm.yyyy’) + (level — 1) ) days
from dual
connect by level

В данном случае идет суммирование в пределах окна, диапазон которого составляет скользящий день и 2 предыдущих дня

select
ename,
sal,
rank() over (order by sal) rank,
dense_rank() over (order by sal) dens_rank,
row_number() over (order by sal) row_number
from emp
order by sal

Данный запрос демонстрирует работу ранжирующих функций rank(), dense_rank() и row_number() по окладам работников. Обратите внимание на поведение данных функций в строках с одинаковыми значениями окладов.

select
ename,
deptno,
sal,
rank() over (partition by deptno order by sal) rank,
dense_rank() over (partition by deptno order by sal) dens_rank,
row_number() over (partition by deptno order by sal) row_number
from emp
order by deptno

Можно предыдущий запрос фрагментировать по отделам, то есть разбить на группы по отделам, в пределах которых будут работать аналитические функции, которые при выходе за пределы групп будут сбрасывать результаты. Результат представлен ниже

Рассмотрим пример применения функций lag() и lead(). Для того, чтобы можно было обращаться в текущей строке к предыдущим строкам, необходимо использовать функцию — lag(). Синтаксис ее таков

lag(поле_для_обращения, смещение, значение_для_замещения_null)
over (partition by выражение order by выражение)

поле_для_обращения — поле, по которому нужно просматривать значения;
смещение — смещенная строка, с которой просматривается поле, по-умолчанию равно 1, если проставить 0, тогда будет просматриваться текущее поле;
значение_для_замещения_null — по-умолчанию равно null, в случае отсутствия значения в просматриваемом поле, возвращает данное значение. Здесь стоит отметить, что подставляемое значение должно быть того же типа, что и просматриваемое поле;
для данной функции обязательно использование order by

with
main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
)
select
ename,
sal,
lag(sal) over (order by rownum) previous_sal
from numerated_main

with
main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
)
select
ename,
sal,
lag(sal, 2, 0) over (order by rownum) previous_sal
from numerated_main

Логика и синтаксис работы функции lead() аналогичен предыдущей функции с одной лишь разницей: просмотр идет не назад, а вперед

with
main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
)
select
ename,
sal,
lead(sal, 2, 0) over (order by rownum) previous_sal
from numerated_main

Ниже для ознакомления приведен неполный список аналитических функций. Для более полной информации обращайтесь к документации. Всем успехов.

ru.natapa.org

Oracle, как мы знаем, является широко используемым языком кодирования. Программа известна как набор кодов и инструкций в технической терминологии или терминологии кодирования. Эти программы состоят из небольших модулей или мини-программ, которые предназначены для выполнения определенных задач. Этим подпрограммам назначаются различные задачи, и они вызываются через вызывающую программу или другую подпрограмму в случае выполнения определенной указанной задачи.


Функции и процедуры — не что иное, как подпрограммы в Oracle. Они используются для выполнения набора конкретных инструкций от пользователя. Например, функция AVG () используется для вычисления среднего значения всех значений, которые предоставляются программе в качестве входных данных.Аналогичным образом, процедуры также используются для таких конкретных задач в программе. Однако поразительное различие между ними заключается в том, что функция всегда возвращает значение, но процедура не всегда возвращает его. Процедура успешно выполняет задачу, но может возвращать или не возвращать значение.

Сравнение между функцией и процедурой:

функция

Процедура

Всегда возвращает значение.

Может или не может вернуть значение.

Функция выполняет конкретную задачу.

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

Вызов из операторов SQL

Такой вызов возможен для функций.

Такой вызов не осуществим для процедур.

Функции обычно используются для выполнения вычислений.

Процедуры используются для выполнения бизнес-логики.

Функция не выполняется независимо. Он должен быть частью исполняемого оператора.

Сама процедура представляет собой исполняемый оператор, поэтому она может выполняться независимо.

Введение в Oracle 10g

В предыдущей части статьи мы рассмотрели встроенные арифметические функции. В части 5 речь пойдет о функциях работы с текстовой информацией, которые могут применяться в запросах и программном коде на языке PL/SQL.

Функция CONCAT(str1, str2)

Данная функция выполняет конкатенацию строк str1 и str2. Если один из аргументов равен NULL, то он воспринимается как пустая строка. Если оба аргумента равны NULL, то функция возвращает NULL.

SELECT CONCAT(‘У попа ‘, ‘была собака’) x1,

CONCAT(‘Test’, NULL) x2,

CONCAT(NULL, ‘Test’) x3,

CONCAT(NULL, NULL) x4

X1

X2

X3

X4

У попа была собака

Для конкатенации строк Oracle поддерживает специальный оператор конкатенации «||», который работает аналогично функции CONCAT, например:

SELECT CONCAT(‘У попа ‘, ‘была собака’) x1,

‘У попа ‘ || ‘была собака’ x2

X1

X2

У попа была собака

У попа была собака

Не следует путать оператор конкатенации «||», эквивалентный вызову функции CONCAT, и оператор «+», применяемый в арифметических операциях. В Oracle это разные операторы, но за счет автоматического приведения типов возможны трудноуловимые ошибки, например:

В данном случае возвращается числовое значение 8, а не текстовая строка «53». Это связано с тем, что, обнаружив арифметическую операцию «+», Oracle автоматически пытается привести аргументы к типу NUMBER.

Функция LOWER(str)

Функция LOWER преобразует все символы строки str в строчные.

SELECT LOWER(‘TeXt DATA’) X

X

Функция UPPER(str)

Функция UPPER преобразует все символы строки str в прописные.

SELECT UPPER(‘TeXt DATA’) X

X

Функция INITCAP(str)

Возвращает строку str, в которой первые буквы всех слов преобразованы в прописные. Функция удобна для форматирования полного имени при построении отчетов.

SELECT INITCAP(‘ИваноВ петр сиДорович’) X1

X1

Иванов Петр Сидорович

Функции LTRIM(str [,set]) и RTRIM(str [,set])

Функция LTRIM удаляет все символы с начала строки до первого символа, которого нет в наборе символов set. По умолчанию set состоит из одного пробела и может не указываться. Функция RTRIM аналогична LTRIM, но удаляет символы, начиная от конца строки. Рассмотрим несколько примеров:

SELECT LTRIM(‘ TeXt DATA’) X1,

LTRIM(‘ _ # TeXt DATA’, ‘ #_’) X2,

LTRIM(‘ 1234567890 TeXt DATA’, ‘ 1234567890’) X3

X1

X2

X3

Функции LPAD(str,n, [,char]) и RPAD(str,n, [,char])

Функция LPAD возвращает строку str, дополненную слева символом char, до достижения строкой длины в n символов. По умолчанию символ-заполнитель равен пробелу и может не указываться. Если длина переданной функции строки больше n, то функция возвращает строку без изменений. Функция RPAD аналогична LPAD, но производит дополнение строки справа. Данные функции очень удобны для форматирования текстовой информации при подготовке отчетов.

SELECT LPAD(‘Test’, 20) x1,

LPAD(‘Test’, 20, ‘_’) x3


X1

X2

X3

Функция REPLACE(str, search_str, [,replace_str])

Функция REPLACE осуществляет поиск образца search_str в строке str и каждое найденное вхождение заменяет на replace_str. По умолчанию replace_str равен пустой строке, поэтому вызов функции REPLACE с двумя аргументами приводит к удалению всех найденных вхождений. Поиск подстроки ведется с учетом регистра.

SELECT REPLACE(‘У попа была собака’, ‘собака’, ‘кошка’) x1,

REPLACE(‘У попа была злая собака’, ‘злая’) x2,

REPLACE(‘У попа была собака’, ‘Собака’, ‘Кошка’) x3

X1

X2

X3

У попа была кошка

У попа была собака

У попа была собака

Функция TRANSLATE(str, from_mask, to_mask)

Функция TRANSLATE анализирует строку str и заменяет в ней все символы, встречающиеся в строке from_mask, на соответствующие символы из to_mask. Для корректной работы функции строки from_mask и to_mask должны иметь одинаковую длину или строка from_mask должна быть длиннее, чем to_mask. Если from_mask длинее, чем to_mask, и в процессе обработки строки str обнаружатся символы, соответствующие одному из символов from_mask, и при этом им не найдется соответствия в to_mask, то такие символы будут удалены из строки str. Если передать from_mask или to_mask, равное NULL, то функция возвратит значение NULL. Сравнение производится с учетом регистра.

SELECT TRANSLATE(‘Test 12345’, ‘e2’, ‘E!’) x1,

TRANSLATE(‘Test 12345’, ‘e234’, ‘E’) x2

X1

X2

Данная функция удобна для решения ряда практических задач, связанных с перекодировкой символов или с поиском запрещенных символов. Например, необходимо проанализировать пароль и выяснить, содержит ли он хотя бы одну цифру. Реализация данной проверки при помощи TRANSLATE имеет вид:

IF TRANSLATE(PassWd, ‘0123456789’, ‘*’) = PassWd THEN

ADD_ERROR(‘Ошибка — Пароль должен содержать хотя бы одну цифру !’);

Другой пример: идет подготовка числа к его преобразованию в NUMBER. Необходимо заменить разделители десятичных знаков «,» и «.» на «.» и удалить пробелы. Реализация данной операции при помощи TRANSLATE имеет вид:

SELECT TRANSLATE(‘123 455,23’, ‘., ‘, ‘..’) x1,

TRANSLATE(‘-123 455.23’, ‘., ‘, ‘..’) x2

X1

X2

Функция SUBSTR(str, m [,n])

Функция SUBSTR возвращает фрагмент строки str, начиная с символа m длиной n символов. Длину можно не указывать — в этом случае возвращается строка от символа m и до конца строки str. Нумерация символов идет с 1. Если указать m = 0, то копирование все равно начнется с первого символа. Задание отрицательного значения m приводит к тому, что символы отсчитываются от конца строки, а не от начала. Задание значений m, превышающих по абсолютному значению длину строки, приводит к тому, что функция возвращает NULL.

SELECT SUBSTR(‘У попа была собака’, 13) x1,

PL SQL — функции, процедуры, переменные, курсоры и циклы plsql

Programming Language for SQL (PL/SQL) – решение Oracle, предоставляющее средства, которые позволяют выполнять сложную обработку информации (plsql).

Илон Маск рекомендует:  Что такое код opengl

Программные единицы PL/SQL

  • Процедура — это подпрограмма, которая выполняет специфическое действие (CREATE PROCEDURE).
  • Функция — это подпрограмма, которая вычисляет значение (CREATE FUNCTION).
  • PL/SQL пакеты — это объект базы данных, который группирует логически связанные типы, программные объекты и подпрограммы PL/SQL. Пакеты обычно состоят из двух частей — спецификации и тела. Спецификация пакета — это интерфейс с вашими приложениями, она объявляет типы, переменные, константы, исключения, курсоры и подпрограммы, доступные для использования в пакете. Тело пакета полностью определяет курсоры и подпрограммы, тем самым реализуя спецификацию пакета (CREATE PACKAGE и CREATE PACKAGE BODY).
  • Динамический SQL
    • Native Dynamic SQL (NDS)
    • DBMS_SQL
  • Триггеры — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных. В Oracle различают следующие виды триггеров: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE (CREATE TRIGGER).
  • Опции компилятора
  • Управление зависимостями
  • Хинты или подсказки (Oracle Hints) — средство, позволяющее явным образом влиять на план запроса. Хинты определяют общие цели и подходы для оптимизации плана выполнения запроса, включая правила и методы доступа к данным — указание порядка соединения таблиц, указание метода соединения таблиц, указание конкретного индекса для доступа к таблице.

Структура блока PL/SQL

Блок PL/SQL может содержать до четырех разделов, однако только один из них является обязательным.

  • Заголовок. Используется только в именованных блоках, определяет способ вызова именованного блока или программы. Не обязателен.
  • Раздел объявлений. Содержит описания переменных, курсоров и вложенных блоков, на которые имеются ссылки в исполняемом разделе и разделе исключений. Не обязателен.
  • Исполняемый раздел. Команды, выполняемые ядром PL/SQL во время работы приложения. Обязателен.
  • Раздел исключений. Обрабатывает исключения (предупреждения и ошибки). Не обязателен.

Структура блока PL/SQL для процедуры показана на рисунке:

Рисунок «Процедура, содержащая все четыре раздела»:

Хранимая процедура

Хранимая процедура — это определенный набор инструкций, написанных на языке PL/SQL. Вызов процедуры приводит к выполнению содержащихся в ней инструкций. Процедура хранится в базе данных, поэтому и называется хранимой. Процедура состоит из двух основных частей:спецификации и тела.
Спецификация процедуры (procedure specification) включает в себя имя процедуры и описание ее входных и выходных данных. Эти входные и выходные данные называются формальными параметрами (formal parameters) или формальными аргументами (formal arguments). Если при вызове процедуры указываются параметры командной строки или другие входные данные, эти значения называются фактическими (actual) параметрами или фактическими аргументами.
Пример спецификации

Здесь мы видим процедуру с тремя формальными параметрами. Слово IN после имени параметра означает, что при вызове процедура может считать из этого параметра входное значение. Слово OUT означает, что процедура может использовать данный параметр для возврата значения в ту программу, из которой она была вызвана. Комбинация IN OUT после имени параметра говорит о том, что параметр может использоваться как для передачи значения процедуре, так и для возврата значения.
Тело процедуры (procedure body) — это блок PL/SQL-кода.

Хранимые функции

Функция PL/SQL похожа на процедуру PL/SQL: она также имеет спецификацию и тело. Главное различие между процедурой и функцией в том, что функция предназначена для возврата значения, которое может использоваться в более крупном SQL-Операторе.

Триггеры

Триггер — это процедура PL/SQL, которая выполняется автоматически, когда происходит некоторое заданное событие, называемое триггерным событием (triggering event).
Например, можно писать триггеры, срабатывающие при выполнении над таблицей операций INSERT, UPDATE или DELETE; при выдаче команд DDL; при входе пользователя в систему или его выходе из системы; при запуске или останове базы данных; при возникновении ошибок.
Между триггерами и процедурами PL/SQL есть три различия:

  • Триггеры нельзя вызывать из кода программы. Oracle вызывает их автоматически в ответ на определенное событие.
  • Триггеры не имеют списка параметров.
  • Спецификация триггера немного отличается от спецификации процедуры.

Структура блока PL/SQL

Базовый блок PL/SQL состоит из четырех секций:

  • секции заголовка (header section);
  • необязательной секции объявлений (declaration section);
  • выполняемой секции (execution section);
  • необязательной секции исключений (exception section).


Анонимный блок (anonumous block) — это блок PL/SQL без секции заголовка, иначе говоря, секции имени, поэтому он и называется анонимным. Анонимные блоки могут выполняться из SQL*Plus и использоваться в функциях, процедурах и триггерах PL/SQL. Вспомните, что сами процедуры, функции и триггеры также состоят из базовых блоков. Это означает, что базовый блок можно помещать в другой базовый блок.

Секция объявлений

Секция объявлений не является обязательной. В случае использования она начинается после секции заголовка и оканчивается перед ключевым, словом BEGIN. Эта секция содержит объявления переменных, констант, курсоров, исключений, функций и процедур PL/SQL, которые будут использоваться в выполняемой секции и секции исключений. Все объявления переменных и констант должны размещаться до объявлений функций или процедур. О переменных и константах PL/SQL будет подробно рассказано в следующем разделе. Объявление сообщает PL/SQL о том, что нужно создать переменную, константу, курсор, функцию или процедуру согласно приведенной спецификации. Когда выполнение базового блока завершается, все элементы, объявленные в секции объявлений, перестают существовать. Элементы, объявленные в секции объявлений базового блока, могут использоваться только в пределах этого блока. Одним словом, все, что находится в секции объявлений, при надлежит блоку и может использоваться только внутри него, а следовательно, существует только на протяжении его времени жизни. Часть кода, в которой может использоваться переменная, называется областью видимости (scope).

Выполняемая секция

Выполняемая секция начинается с ключевого слова BEGIN и заканчивается либо ключевым словом EXCEPTION, если присутствует секция исключений, либо ключевым словом END, за которым следуют необязательное имя функции или процедуры и точка с запятой. Выполняемая секция содержит один и более PL/SQL-операторов, выполняемых при передаче управления данному блоку. Структура выполняемой секции показана ниже.

В выполняемом коде PL/SQL чаще всего встречается оператор присваивания (:=). Он указывает, что нужно вычислить выражение справа и поместить результат в переменную слева.

Секция исключений

В ходе выполнения PL/SQL-оператора может возникнуть ошибка, которая сделает невозможным дальнейшее выполнение программы. Такие исключительные ситуации называются исключениями (exceptions). Пользователь, вызвавший процедуру, должен быть проинформирован о возникновении исключения, а также о причинах, его вызвавших. Вы можете выдать пользователю содержательное сообщение об ошибке, или предпринять некоторые корректирующие действия и повторить операцию, выполнявшуюся до возникновения ошибки. Вы также можете откатить изменения, которые были произведены в базе данных к этому моменту. PL/SQL помогает вам во всех этих случаях, предоставляя средства обработки исключений (exception handling).

Секция исключений начинается с ключевого слова EXCEPTION и продолжается до конца блока. Каждому исключению соответствует оператор WHEN имя_исключения, указывающий, что должно быть сделано при возникновении данного исключения. Все операторы, находящиеся между оператором, вызвавшим ошибку, и секцией исключений, игнорируются. Выполнение оператора, указанного в секции исключений, называется обработкой исключения (exception handling). Процесс, включающий в себя обнаружение ошибки, определение, какое исключение описывает ее наилучшим образом, и передачу PL/SQL информации, позволяющей найти соответствующий код в секции исключений, называется возбуждением исключения (raising exception).

Переменные

Переменные — это именованные контейнеры. Они могут содержать информацию (данные) различных видов. В зависимости от того, какую информацию в них можно помещать, они имеют различные типы данных, а чтобы отличать их друг от друга, им присваиваются имена. PL/SQL хранит числа в переменных типа NUMBER, а текст — в переменных типа CHAR или VARCHAR2. Синтаксис объявления переменной в PL/SQL может иметь любую из следующих форм:

Имя_переменной — это любой правильный идентификатор PL/SQL. Правильный идентификатор PL/SQL должен:

  • Иметь не более 30 символов в длину и не содержать пробельных символов (собственно пробелов и знаков табуляции).
  • Состоять только из букв, цифр от 0 до 9, символа подчеркивания (_), знака доллара ($) и знака фунта (#).
  • Начинаться с буквы.
  • Не совпадать с зарезервированными словами PL/SQL или SQL, которые имеют специальное значение. Например, именем переменной не может быть слово BEGIN, которое обозначает начало выполняемой секции базового блока PL/SQL.

Тип_данных — это любой допустимый тип данных SQL или PL/SQL. Модификатор NOT NULL требует, чтобы переменная имелазначение. Если он указан, переменной должно быть присвоено значение по умолчанию.

Объявление констант PL/SQL

Синтаксис объявления константы имеет следующий вид:

В отличие от переменных константам обязательно присваивается значение, которое нельзя изменять на протяжении времени жизни константы. Константы очень полезны для поддержания безопасности и дисциплины при разработке больших и сложных приложений. Например, если вы хотите гарантировать, что процедура PL/SQL не будет модифицировать передаваемые ей данные, можете объявить их константами. Если процедура все же попытается их модифицировать, PL/SQL возбудит исключение.

Оператор IF

Оператор IF имеет следующий синтаксис:

Действие_1 … альтернативное Действие представляют один или несколько PL/SQL-операторов. Каждая группа операторов выполняется только в том случае, если выполнено соответствующее условие. После того как обнаружено выполнение одного из условий, остальные условия не проверяются.

Циклы

PL/SQL предоставляет три различные конструкции для итеративной обработки. Каждая из них позволяет циклически выполнять набор операторов PL/SQL. Выход из цикла осуществляется в зависимости от некоторого условия.

Конструкция LOOP имеет следующий синтаксис:

При наличии конструкции WHEN все операторы в теле цикла повторяются до тех пор, пока выражение условие_выхода не примет положительное значение (т.е. не станет истинным). Условие выхода проверяется на каждом проходе, иначе называемом итерацией. Как только выражение принимает значение «истина», все операторы после EXIT пропускаются, итерации прекращаются и выполнение продолжается с первого оператора, следующего за END LOOP. Если условие WHEN отсутствует, операторы между LOOP и EXIT выполняются только один раз. Очевидно, что опустив условие WHEN, вы поступите нелогично. В конце концов идея цикла состоит в том, чтобы обеспечить потенциально многократное выполнение кода.

Цикл WHILE

Еще одной разновидностью цикла является цикл WHILE. Он хорошо подходит в ситуациях, когда количество итераций заранее неизвестно, и определяется некоторым внешним фактором. Цикл WHILE имеет следующий синтаксис:

Условие WHILE проверяется перед каждым входом в цикл. Если оно имеет значение «истина», то выполняется очередная итерация.

Цикл FOR

В цикле FOR для подсчета итераций используется переменная-счетчик, называемая также индексом цикла (loop index). По завершении каждой итерации счетчик увеличивается, начиная с нижнего предела, или уменьшается, начиная с верхнего предела. Как только его значение выйдет за указанный диапазон, цикл завершается. Синтаксис цикла FOR выглядит следующим бразом:

Курсоры

Курсор — это исключительно важная конструкция PL/SQL, лежащая в основе взаимодействия PL/SQL и SQL. Название «курсор» означает «текущий набор записей». Курсор представляет собой специальный элемент PL/SQL, с которым связан SQL-оператор SELECT. Используя курсор, можно отдельно обрабатывать каждую строку связанного с ним SQL-оператора. Курсор объявляется в секции объявлений базового блока. Он открывается командой OPEN, а выборка строк осуществляется с помощью команды FETCH. После завершения всей обработки курсор закрывается командой CLOSE. Закрытие курсора освобождает те системные ресурсы, которые использовались, пока он был открыт. Строки, выбранные курсором, можно заблокировать, чтобы предотвратить их модификацию другими пользователями. Закрытие курсора или выполнение явной операции COMMIT или ROLLBACK приведет к разблокированию строк. Для SQL-операторов, используемых в коде PL/SQL, применяются скрытые, или неявные (implicit), курсоры, а также явные (explicit) курсоры, т.е. те, которым присвоено имя.

Объявление курсора и атрибуты курсора

Курсор объявляется в процедуре PL/SQL следующим образом:

Параметры курсора похожи на параметры процедуры, за тем исключением, что они всегда являются входными (IN). Использование параметров OUT или IN OUT невозможно, поскольку курсор не может их модифицировать. Параметры используются в конструкции WHERE курсорного оператора SELECT. Спецификация возврата показывает, записи какого типа будут выбираться оператором SELECT. Таблица_или_столбец — это имя столбца, который предстоит обновлять, или имя таблицы, в которой предстоит удалять или обновлять строки. Оно должно входить в число имен таблиц и столбцов, указанных в операторе SELECT курсора, и предназначено для документирования, показывая, какие элементы могут быть потенциально модифицированы кодом, использующим данный курсор. Команда FOR UPDATE блокирует строки, выбранные оператором SELECT при открытии курсора. Строки остаются заблокированными до тех пор, пока вы не закроете курсор рассмотренными выше способами. Атрибуты курсора:

  • имя_курсора%ISOPEN — Позволяет проверить, открыт ли курсор. Если курсор имя_курсора уже открыт, возвращается значение TRUE
  • имя_курсора%ROWCOUNT — Количество строк таблицы, возвращенных оператором SELECT курсора
  • имя_курсора%FOUND — Позволяет проверить, была ли успешной последняя попытка получения записи из курсора. Если запись была выбрана, то возвращается значение TRUE
  • имя_курсора%NOTFOUND — Противоположен атрибуту FOUND. Если записей больше не найдено, возвращается значение TRUE

Записи PL/SQL

Запись PL/SQL— это набор данных базовых типов. К ней можно обращаться, как к единому целому. Для доступа к отдельным полям записи применяется нотация имя_записи_имя_поля, которую вы уже использовали для столбцов таблицы. Записи могут иметь один из трех типов, перечисленных ниже; вы можете объявлять переменные, имеющие тип записи.

  • Основанные на таблице (table-based) Эти записи имеют поля, совпадающие по имени и типу со столбцами таблицы. Если курсор выбирает всю строку — например, оператором SELECT * FROM некоторая_таблица — то возвращаемые им записи можно непосредственно копировать в переменную, имеющую тип записи, основанной на таблице некоторая_таблица.
  • Основанные на курсоре (cursor-based) Поля этих записей совпадают по имени, типу и порядку с заключительным списком столбцов в курсорном операторе SELECT.
  • Определенные программистом (programmer-defined) Это записи, тип которых определяете вы сами.

Использование команд OPEN, FETCH и CLOSE

Команды открытия курсора, выборки из курсора и закрытия курсора имеют следующий синтаксис:

После открытия курсор содержит набор записей, если в результате успешного выполнения оператора SELECT из базы данных были выбраны заданные строки. Каждая команда FETCH удаляет запись из открытого курсора и перемещает ее содержимое либо в переменную PL/SQL, тип записи которой совпадает с типом записи курсора, либо в группу переменных PL/SQL, где каждая переменная в списке совпадает по типу с соответствующим полем в записи курсора. Перед тем как пытаться выбрать из курсора очередную запись, следует проверить с помощью атрибутов FOUND и NOTFOUND, есть ли в нем еще записи. Выборки из пустого курсора будут все время давать последнюю запись, не приводя к ошибке. Не забывайте проверять атрибуты FOUND и NOTFOUND при использовании FETCH. Фактическая обработка записей из курсора обычно выполняется внутри цикла. При написании такого цикла неплохо начать с проверки, была ли найдена запись в курсоре. Если да, можно продолжать необходимую обработку; в противном случае следует выйти из цикла. То же самое можно сделать более коротким путем, использовав курсорный цикл FOR. При этом PL/SQL будет осуществлять открытие, выборку и закрытие без вашего участия.

Курсорный цикл FOR

Синтаксис курсорного цикла FOR имеет следующий вид:

Этот цикл выбирает записи из курсора в переменную типа запись_курсора. Поля записи_курсора можно использовать для доступа к данным из операторов PL/SQL, выполняемых в цикле. Когда все записи выбраны, цикл завершается. Для удобства открытие и закрытие курсора производится автоматически. Попытавшись выбрать запись из неоткрытого курсора, вы получите сообщение in valid cursor (недействительный курсор). Если не закрывать курсоры, то в конце концов количество открытых курсоров достигнет максимальной величины, допускаемой системой.

Конструкция WHERE CURRENT OF

Когда курсор открывается для обновления или удаления выбранных записей, можно использовать конструкцию WHERE CURRENT OF имя_курсора для доступа к таблице и строке, которые соответствуют последней записи, выбранной в конструкции WHERE оператора UPDATE или DELETE.

Обработка ошибок

В языке PL/SQL ошибки всех видов интерпретируются как исключения — ситуации, которые не должны возникать при нормальном выполнении программы.
К числу исключений относятся:

  • ошибки, генерируемые системой (например, нехватка памяти или повторяющееся значение индекса);
  • ошибки, вызванные действиями пользователя;
  • предупреждения, выдаваемые приложением пользователю.

PL/SQL перехватывает ошибки и реагирует на них при помощи так называемых обработчиков исключений. Механизм обработчиков исключений позволяет четко отделить код обработки ошибок от основной логики программы, а также дает возможность реализовать обработку ошибок, управляемую событиями. Независимо от того, как и по какой причине возникло конкретное исключение, оно всегда обрабатывается одним и тем же обработчиком в разделе исключений.
При возникновении ошибки — как системной, так и ошибки в приложении — в PL/SQL инициируется исключение. В результате выполнение блока прерывается, и управление передается для обработки в раздел исключений текущего блока, если он имеется. После обработки исключения возврат в тот блок, где исключение было инициировано, невозможен, поэтому управление передается во внешний блок.
Схема передачи управления при возникновении исключения:

Илон Маск рекомендует:  Библиотека minify

Существует два типа исключений:

  • Системное исключение определяется в Oracle и обычно инициируется исполняемым ядром PL/SQL, обнаружившим ошибку. Одним системным исключениям присваиваются имена (например, NO_DATA_FOUND), другие ограничиваются номерами и описаниями.
  • Исключение, определяемое программистом, актуально только для конкретного приложения. Имя исключения можно связать с конкретной ошибкой Oracle с помощью директивы компилятора EXCEPTION_INIT или же назначить ошибке номер и описание процедурой RAISE_APPLICATION_ERROR.

Исключения

Исключение — это состояние ошибки, которое активизируется — или возбуждается — при возникновении некоторой проблемы. Существует много разных исключений, каждое из которых связано с определенным типом проблем. При возникновении исключительной ситуации выполнение кода останавливается на операторе, который возбудил исключение, и управление передается той части блока, которая обрабатывает это исключение. Если блок не содержит выполняемой секции, PL/SQL пытается найти выполняемую секцию во включающем базовом блоке (enclosing basic block), т.е. в блоке, который является внешним по отношению к коду, возбудившему исключение. Если в непосредственном включающем блоке отсутствует обработчикданного исключения, то поиск продолжается в блоках следующих уровней, пока не будет найден подходящий обработчик, а если его найти не удается, то выполнение программы прекращается с выдачей сообщения о необрабатываемой ошибке. Часть блока, предназначенная для обработки исключений, — это идеальное место для выдачи информативных сообщений об ошибках и выполнения очистки (cleanup), позволяющей избавиться от всего, что могло бы в дальнейшем вызвать путаницу или проблемы. Если исключение было возбуждено в ходе выполнения процедуры, вставляющей строки в таблицу, то типичная процедура очистки может включать в себя оператор ROLLBACK. После того как управление было передано обработчику исключения, оно уже не возвращается оператору, ставшему причиной этого исключения. Вместо этого управление передается оператору включающего базового блока, который следует сразу за вызовом вложенного блока или процедуры/функции.

Системные исключения

В PL/SQL можно выдавать пользователям информацию об ошибке двумя способами. Первый способ — использовать команду SQLCODE, которая возвращает код ошибки. Этот код представляет собой отрицательное число, обычно равное номеру ошибки ORA, которая выводится при завершении приложения, если исключение осталось необработанным. Второй способ — возвращать текстовое сообщение, описывающее ошибку. Неудивительно, что соответствующая команда называется SQLERRM. В обработчике исключения можно использовать как SQLCODE, так и SQLERRM. Замечание: не у всех системных исключений есть имена. Системные исключения:

  • CURSOR_ALREADY_OPEN — Попытка открыть уже открытый курсор;
  • DUP_VAL_ON_INDEX — Попытка вставить повторяющееся значение в столбец, имеющий уникальный индекс, а следовательно, ограничение уникальности;
  • INVALID_CURSOR — Попытка применить команду FETCH к неоткрытому курсору или попытка закрыть курсор, который не открывался;
  • NO_DATA_FOUND — Попытка выполнить SELECT INTO, когда SELECT возвращает нулевое количество строк, а также другие причины;
  • PROGRAM_ERROR — Внутренняя ошибка. Обычно означает, что вам нужно обратиться в службу поддержки Oracle;
  • STORAGE_ERROR — Программе не хватает системной памяти;
  • TIME_OUT_ON_RESOURCE — Программа слишком долго ожидала доступности некоторого ресурса;
  • TOO_MANY_ROWS — SELECT INTO в PL/SQL вернул более одной строки;
  • VALUE_ERROR — PL/SQL встретил неправильное преобразование или усечение данных, или неправильное ограничение на данные;
  • ZERO_DMDE — Попытка деления на нуль;
  • OTHERS — Все прочие исключения и внутренние ошибки, которые не охватываются исключениями, определенными в базовом блоке. Используется в тех случаях, когда вы точно не знаете, какое именованное исключение предстоит обрабатывать, и хотите обрабатывать любые возбуждаемые исключения.


Исключения, определяемые программистом

Одной из удобных возможностей PL/SQL является то, что он позволяет вам определять свои собственные исключения. При возбуждении и обработке они должны именоваться и объявляться аналогично любым другим элементам PL/SQL. Исключение объявляется в секции объявлений. Аналогично любой другой объявленной там переменной, исключение действительно только для данного блока. Вы можете использовать свои собственные исключения для обработки ошибок, которые система не обнаруживает или не считает за ошибки.

Схема сложного запроса PL/SQL с использованием временных таблиц

Ниже рассмотрена схема получения результирующей таблицы в Oracle Database, используя временные таблицы.

Аналитические функции в Oracle

Содержание


Общие положения


Общая информация

В версии СУБД Oracle 8.1.6 появился новый класс из 26 функций, названных аналитическими, и получившим дальнейшее развитие в версии 9. Их описания были созданы совместными усилиями фирм IBM, Informix, Oracle и Compaq путем разработки так называемых «улучшений» некоторых конструкций, имеющихся в стандарте SQL1999.

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

Цели введения аналитических функций в Oracle

Техническая цель введения аналитических функций — дать лаконичную формулировку и увеличить скорость выполнения «аналитических запросов» к БД, то есть запросов, имеющих смыслом выявление внутренних соотношений и зависимостей в данных. Более точно, пользование аналитическими функциями может дать следующие выгоды перед обычными SQL-операторами:

  • Лаконичную и простую формулировку . Многие аналитические запросы к БД традиционными средствами сложно формулируются, а потому с трудом осмысливаются и плохо отлаживаются.
  • Снижение нагрузки на сеть . То, что раньше могло формулироваться только серией запросов, сворачивается в один запрос. По сети только отправляется запрос и получается окончательный результат.
  • Перенос вычислений на сервер . С использованием аналитических функций нет нужды организовывать расчеты на клиенте; они полностью проводятся на сервере, ресурсы которого могут быть более подходящи для быстрой обработки больших объемов данных.
  • Лучшую эффективность обработки запросов . Аналитические функции имеют алгоритмы вычисления, неразрывно связанные со специальными планами обработки запросов, оптимизированными для большей скорости получения результата.

Стратегическая цель введения в Oracle аналитических функций — дать базовое средство для построения ИС типа «складов данных» (data warehouse, DW), ИС «аналитического характера» (business intelligence systems, BI) или OLAP-систем. По представлениям разработчиков, набор таких базовых средств помимо аналитических функций формируют еще и прочие средства Oracle, такие как

  • конструкции ROLLUP, CUBE и связанные с ними в предложениях с GROUP BY
  • материализованные выводимые таблицы (materialized views)

Классификация видов аналитических функций в Oracle

Согласно классификации из документации по Oracle, аналитические функции могут быть следующих видов:

(a) функции ранжирования
(b) статистические функции для плавающего интервала
(c) функции подсчета долей
(d) статистические функции LAG/LEAD с запаздывающим/опережающим аргументом
(e) статистические функции (линейная регрессия и т. д.)

Основные технические особенности


Место указания аналитических функций в SQL-предложении

Аналитические функции принимают в качестве аргумента столбец промежуточного результата вычисления SQL-предложения и возвращают тоже столбец. Поэтому местом их использования в SQL-предложении могут быть только фразы ORDER BY и SELECT, выполняющие завершающую обработку логического промежуточного результата.

Сравнение с обычными функциями агрегирования

Многие аналитические функции действуют подобно обычным скалярным функциям агрегирования SUM, MAX и прочим, примененным к группам строк, сформированным с помощью GROUP BY. Однако обычные функции агрегирования уменьшают степень детализации, а аналитические функции нет. Поясняющий сравнительный пример:

SELECT deptno, job, SUM(sal) sum_sal
FROM emp
GROUP BY deptno, job;

SELECT ename, deptno, job,
SUM(sal) OVER (PARTITION BY deptno, job) sum_sal
FROM emp;

Аналитические функции в Oracle (Часть 3)

Виды аналических функций

В качестве базовой в аналитической функции могут быть указаны традиционные для Oracle статистические (агрегатные, то есть обобщающие) функции COUNT, MIN, MAX, SUM, AVG и другие («стандартные агрегатные функции» по документации). Примеры приводились выше. Можно обратить внимание на то, что аналитические функции со статистическими агрегатами разумно обрабатывают NULL:

SELECT ename, hiredate, sal,
AVG(sal)
OVER (ORDER BY hiredate
RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL ‘1’ SECOND PRECEDING ) avg_sal
FROM emp;

Ниже приводится полный перечень аналитических функций в версии СУБД 9.2:

AVG *
CORR *
COVAR_POP *
COVAR_SAMP *
COUNT *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
MAX *
MIN *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (вид_функции_линейной_регрессии) *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE

Звездочкой помечены функции, допускающие использование плавающего интервала расчета.

Некоторые из этих функций рассматриваются ниже.

Функции ранжирования

Функции ранжирования позволяют «раздать» строкам «места» в зависимости от имеющихся в них значениях. Некоторые примеры:

SELECT ename, sal,
ROW_NUMBER () OVER (ORDER BY sal DESC) AS salbacknumber,
ROW_NUMBER () OVER (ORDER BY sal) AS salnumber,
RANK() OVER (ORDER BY sal) AS salrank,
DENSE_RANK() OVER (ORDER BY sal) AS saldenserank
FROM emp;

(раздать сотрудникам места в порядке убывания/возрастания зарплат)

Функции подсчета долей

Функции подсчета долей позволяют одной SQL-операцией получить для каждой строки ее «вес» в таблице в соответствии с ее значениями. Некоторые примеры:

SELECT ename, sal, RATIO_TO_REPORT(sal) OVER () AS salshare FROM emp;

(доли сотрудников в общей сумме зарплат)

Пример выдачи доли сотрудников с меньшей или равной зарплатой, чем у «текущего»:

SELECT job, ename, sal,
CUME_DIST() OVER (PARTITION BY job ORDER BY sal ) AS cume_dist
FROM emp;

ANALYST SCOTT
ANALYST FORD
CLERK SMITH
CLERK JAMES
CLERK ADAMS
CLERK MILLER
MANAGER CLARK
MANAGER BLAKE
MANAGER JONES
PRESIDENT KING
SALESMAN WARD
SALESMAN MARTIN
SALESMAN TURNER
SALESMAN ALLEN

14 rows selected.

(видно, что три четверти клерков имеют зарплату, меньше чем ADAMS).


Проранжировать эту выдачу по доле сотрудников в группе можно функцией PERCENT_RANK:

SELECT job, ename, sal,
CUME_DIST() OVER (PARTITION BY job ORDER BY sal) AS cume_dist,
PERCENT_RANK() OVER (PARTITION BY job ORDER BY sal) AS pct_rank
FROM emp;

ANALYST SCOTT
ANALYST FORD
CLERK SMITH
CLERK JAMES
CLERK ADAMS
CLERK MILLER
MANAGER CLARK
MANAGER BLAKE
MANAGER JONES
PRESIDENT KING
SALESMAN WARD
SALESMAN MARTIN
SALESMAN TURNER
SALESMAN ALLEN

14 rows selected.

Процентный ранг отсчитывается от 0 и изменяется до 1.

Некоторые жизненные примеры аналитических запросов


Для типов сегментов, более других расходующих дисковое пространство, выдать главных пользователей, ответственных за такой расход

Построить такой запрос на основе таблицы SYS.DBA_SEGMENTS, можно пошагово.

Шаг 1. Выдать типы сегментов в БД, общий объем памяти на диске для каждого типа и долю числа типов с равным или меньшим общим объемом памяти:

SELECT segment_type,
SUM(bytes) bytes,
CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_percentile
FROM sys.dba_segments
GROUP BY segment_type;

Шаг 2. Отобрать 40% «наиболее расточительных» по дисковой памяти типов:

SELECT *
FROM
(SELECT segment_type,
SUM(bytes) bytes,
CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_percentile
FROM sys.dba_segments
GROUP BY segment_type)
WHERE bytes_percentile >= 0.5;

Шаг 3. Отобрать пользователей, занимающих первые пять мест по расходованию памяти среди «наиболее расточительных» типов сегментов:

SELECT *
FROM
(
SELECT owner,
SUM(bytes) bytes,
RANK() OVER(ORDER BY SUM(bytes) DESC) bytes_rank
FROM sys.dba_segments
WHERE segment_type IN
(SELECT segment_type
FROM
(SELECT segment_type,
SUM(bytes) bytes,
CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_percentile
FROM sys.dba_segments
GROUP BY segment_type)
WHERE bytes_percentile >= 0.5)
GROUP BY owner
)
WHERE bytes_rank
/

Выдать список периодов наиболее активного переключения журнальных файлов БД

Список переключений журнальных файлов хранится в динамической таблице v$loghist. Ниже приводится один из вариантов запроса.

var treshold number
exec :treshold := 30
alter session set nls_date_format=’MON-DD HH24:MI:SS’;

SELECT
start_time,
end_time,
ROUND((end_time — start_time)*24*60, 2) delta_min,
switches,
switches / ((end_time — start_time)*24*60) per_minute
FROM
(
SELECT
MIN(time_stamp) start_time,
MAX(time_stamp) end_time,
count (*) switches
FROM
(
SELECT time_stamp, freq10, more,
SUM(ABS(indicator)) OVER (ORDER BY time_stamp) part
FROM
(
SELECT time_stamp, freq10,
SIGN(freq10 — :treshold — 0.5) more,
SIGN(freq10 — :treshold — 0.5) — LAG(SIGN(freq10 — :treshold — 0.5), 1)
OVER (ORDER BY time_stamp) indicator
FROM
(
SELECT first_time time_stamp,
GREATEST(
COUNT(*)
OVER (ORDER BY first_time
RANGE BETWEEN CURRENT ROW AND INTERVAL ’10’ MINUTE FOLLOWING)
,
COUNT(*)
OVER (ORDER BY first_time
RANGE BETWEEN INTERVAL ’10’ MINUTE PRECEDING AND CURRENT ROW)
) freq10
FROM v$loghist
) /* frequency table */
) /* frequency treshold overcome table */
) /* transient partitioned table */
WHERE more > 0
GROUP BY part
)
WHERE (end_time — start_time)*24*60 > 0
/


    Фактически проверяется не частота переключений журнальных файлов, а частота фиксации первого изменения в журнальных файлах. Это не совсем одно и то же, но, похоже, сильно коррелирующие события.

и вот небольщой пример

SELECT ‘первый день’ den, ‘1первая неделя’ ned FROM dual UNION ALL

SELECT ‘второй день’ den, ‘1первая неделя’ ned FROM dual UNION ALL

SELECT ‘третий день’ den, ‘1первая неделя’ ned FROM dual UNION ALL

SELECT ‘первый день’ den, ‘2вторая неделя’ ned FROM dual UNION ALL

SELECT ‘второй день’ den, ‘2вторая неделя’ ned FROM dual UNION ALL

SELECT ‘третий день’ den, ‘2вторая неделя’ ned FROM dual UNION ALL

SELECT ‘первый день’ den, ‘3третья неделя’ ned FROM dual UNION ALL

SELECT ‘второй день’ den, ‘3третья неделя’ ned FROM dual UNION ALL

SELECT ‘третий день’ den, ‘3третья неделя’ ned FROM dual

) SELECT dense_rank() over (ORDER BY ned) dr,row_number() over ( PARTITION BY ned ORDER BY ned) rn,

Ссылки по теме

Популярные статьи
Информационная безопасность Microsoft Офисное ПО Антивирусное ПО и защита от спама Eset Software


Бестселлеры
Курсы обучения «Atlassian JIRA — система управления проектами и задачами на предприятии»
Microsoft Office 365 для Дома 32-bit/x64. 5 ПК/Mac + 5 Планшетов + 5 Телефонов. Подписка на 1 год. Электронный ключ
Microsoft Windows 10 Профессиональная 32-bit/64-bit. Все языки. Электронный ключ
Microsoft Office для Дома и Учебы 2020. Все языки. Электронный ключ
Курс «Oracle. Программирование на SQL и PL/SQL»
Курс «Основы TOGAF® 9»
Microsoft Windows Professional 10 Sngl OLP 1 License No Level Legalization GetGenuine wCOA (FQC-09481)
Microsoft Office 365 Персональный 32-bit/x64. 1 ПК/MAC + 1 Планшет + 1 Телефон. Все языки. Подписка на 1 год. Электронный ключ
Windows Server 2020 Standard
Курс «Нотация BPMN 2.0. Ее использование для моделирования бизнес-процессов и их регламентации»
Антивирус ESET NOD32 Antivirus Business Edition
Corel CorelDRAW Home & Student Suite X8

О нас
Интернет-магазин ITShop.ru предлагает широкий спектр услуг информационных технологий и ПО.

На протяжении многих лет интернет-магазин предлагает товары и услуги, ориентированные на бизнес-пользователей и специалистов по информационным технологиям.

Хорошие отзывы постоянных клиентов и высокий уровень специалистов позволяет получить наивысший результат при совместной работе.

ru.natapa.org

Oracle, как мы знаем, является широко используемым языком кодирования. Программа известна как набор кодов и инструкций в технической терминологии или терминологии кодирования. Эти программы состоят из небольших модулей или мини-программ, которые предназначены для выполнения определенных задач. Этим подпрограммам назначаются различные задачи, и они вызываются через вызывающую программу или другую подпрограмму в случае выполнения определенной указанной задачи.

Функции и процедуры — не что иное, как подпрограммы в Oracle. Они используются для выполнения набора конкретных инструкций от пользователя. Например, функция AVG () используется для вычисления среднего значения всех значений, которые предоставляются программе в качестве входных данных.Аналогичным образом, процедуры также используются для таких конкретных задач в программе. Однако поразительное различие между ними заключается в том, что функция всегда возвращает значение, но процедура не всегда возвращает его. Процедура успешно выполняет задачу, но может возвращать или не возвращать значение.

Сравнение между функцией и процедурой:

функция

Процедура

Всегда возвращает значение.

Может или не может вернуть значение.

Функция выполняет конкретную задачу.

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

Вызов из операторов SQL

Такой вызов возможен для функций.

Такой вызов не осуществим для процедур.

Функции обычно используются для выполнения вычислений.

Процедуры используются для выполнения бизнес-логики.

Функция не выполняется независимо. Он должен быть частью исполняемого оператора.

Сама процедура представляет собой исполняемый оператор, поэтому она может выполняться независимо.

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL