Основы программирования в СУБД Oracle. SQL+PL/SQL.

Использование вычисляемых столбцов

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

{Выражение} As {псевдоним}

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

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

Если операции в выражении имеют одинаковый приоритет, то их выполнение производится слева направо.

Рассмотрим примеры использования вычисляемых столбцов. Если значение столбца commission_pct в таблице Employees обозначает надбавку к зарплате как часть заработной платы, то общая зарплата с учетом комиссионных может быть вычислена с использованием выражения:

SALARY * (1 + COMMISSION_pct) As Total_Salary

Следует иметь в виду то, что у некоторых сотрудников значение столбца commission_pct равно NULL. А если один из элементов выражения равен NULL, то и все выражение будет иметь значение NULL. Данную проблему можно решить, используя специальные функции, которые мы рассмотрим позже.

Пример 2.31. Вывести данные о размере комиссионных для сотрудников, которые получают комиссионные

SELECT employee_id, first_name, last_name, department_id,

commission_pct*salary as commission

FROM Employees

WHERE commission_pct IS NOT NULL;

Вычисляемые столбцы можно использовать в предложении WHERE.

Пример 2.32. Вывести данные о продажах товаров, в которых сумма одной покупки превышала 300 000

SELECT product_id, order_id, item_id, quantity, unit_price,

quantity*unit_price

FROM Order_items

WHERE quantity*unit_price> 300000;

Использование псевдостолбца ROWNUM

Значение псевдостолбца ROWNUM равно номеру записи, возвращаемой запросом. Используя этот столбец, можно ограничить число строк в результате выполнения запроса.

Пример 2.33. Вывести пять строк с данными о продажах товаров, в которых сумма одной покупки превышала 300 000

SELECT product_id, order_id, item_id, quantity, unit_price,

quantity*unit_price

FROM Order_items

WHERE quantity*unit_price> 300000

AND ROWNUM <=5;

Следует иметь в виду, что любой запрос, содержащий условие ROWNUM = N, где N> 1, будет пуст. Это происходит потому, что первая строка, возвращаемая запросом, имеет значение ROWNUM = 1, это значение не удовлетворяет условию ROWNUM = N и поэтому не попадает в результат выполнения запроса. После этого каждая следующая строка будет иметь значение ROWNUM = 1 и также не будет удовлетворять условию ROWNUM = N.

Оператор конкатенации

Оператор конкатенации (слияния) записывается двумя вертикальными чертами (||) и используются для того, чтобы объединить при выводе данных два или несколько столбцов или литералов в один столбец.

Синтаксис:

{столбец1/литерал1} || {столбец2/литерал2} …As {псевдоним}

Оператор конкатенации можно применять для строк, чисел и дат. Даты и числа при слиянии конвертируются в строковые значения. При слиянии строки значения со значением типа NULL Oracle возвращает строковое значение.

Пример 2.34. Вывести данные о заказах, оформленных сотрудником 165

SELECT ′ Order ′ ||order_id|| ′ from ′ ||order_date

|| ′ is ′ ||status AS Order_Statys

FROM Orders

WHERE salesman_id =165;

Сортировка

Результат выполнения оператора SELECT может быть упорядочен по значению одного или нескольких столбцов. Для этого служит предложение ORDER BY, которое имеет следующий синтаксис:

ORDER BY {имя столбца | номер столбца [ASC|DESC]}

Пример 2.35. Вывести данные о сотрудниках, упорядочив их в порядке убывания зарплаты

SELECT employee_id, first_name, last_name, department_id, salary

FROM Employees

ORDER BY salary DESC;

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

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

SELECT employee_id, first_name, last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC;

В предложении ORDER BY можно использовать псевдонимы столбцов.

Пример 2.37. Вывести данные о сотрудниках, которые получают комиссионные, упорядочив их в порядке убывания суммы комиссионных

SELECT employee_id, first_name, last_name, department_id,

commission_pct*salary as commission

FROM Employees

WHERE commission_pct IS NOT NULL

ORDER BY commission DESC;

Можно сортировать строки по столбцам, не указанным в предложении SELECT.

Пример 2.38. Вывести данные о сотрудниках, которые работают в отделе 80, упорядочив их в порядке убывания рейтинга

SELECT employee_id, first_name, last_name, department_id, salary

FROM Employees

WHERE department_id = 80

ORDER BY rating_e;

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

Пример 2.39. Вывести пять строк с данными о продажах товаров с максимальными суммами (запрос содержит ошибку)

SELECT product_id, order_id, item_id, quantity, unit_price,

quantity*unit_price

FROM Order_Items

WHERE ROWNUM <=5

ORDER BY quantity*unit_price DESC;

Полученный результат выглядит весьма правдоподобно. Результат содержит пять строк, и они упорядочены в порядке убывания сумм. Ошибка заключается в том, что сортируются не все строки, а только первые пять строк. Правильный вариант решения рассматриваемой задачи приведен в следующем примере.

Пример 2.40. Вывести пять строк с данными о продажах товаров с максимальными суммами

SELECT product_id, order_id, item_id, quantity, unit_price,

quantity*unit_price

FROM Order_Items

ORDER BY quantity*unit_price DESC

FETCH FIRST 5 ROWS ONLY;

Этот запрос содержит строку FETCH FIRST 5 ROWS ONLY, которая выбирает первые пять строк после сортировки. Этот оператор появился в Oracle 12.

Задачи для самостоятельного решения

1. Вывести данные о товарах, у которых столбец rating_p имеет значение 3 или 4, а price <1000.

2. Вывести first_name, last_name сотрудников, у которых first_name начинается на букву P и в last_name есть буква r.

3. Вывести значения столбцов employee_id, department_id, first_name, last_name, job_id, salary, department_id сотрудников, у которых зарплата salary> 9000 и работают в одном из отделов: 50, 80, 100.

4. Вывести содержимое столбца street_address в таблице Locations тех строк, у которых значение этого столбца начинается не с цифры.

5. Вывести first_name, last_name, job_id и суммарную зарплату за год в следующем виде:

Michael Hartstein занимает должность MK_MAN, и зарплата за год составляет 156 000.

6. Вывести значения столбцов employee_id, department_id, first_name, last_name, department_id, job_id, salary, bonus для сотрудников, у которых зарплата salary <10 000. Вычисляемый столбец bonus содержит размер премии, которая вычисляется по формуле: Salary * (1 +0.1 * rating_e). Выводимые данные упорядочить по размеру премии.

7. Вывести значения столбцов employee_id, department_id, first_name, last_name, job_id сотрудников, которые работают в отделах 50 или 80, но не являются менеджерами. Менеджерами являются те сотрудники, у которых столбец job_id содержит подстроку MAN.

8. Вывести значения столбцов employee_id, department_id, first_name, last_name, job_id, salary сотрудников, у которых код должности (job_id) имеет значение IT_PROG и зарплата имеет одно из значений (4800, 6000, 9000), а также о сотрудниках, у которых код должности (job_id) имеет значение SA_REP, а зарплата находится вне диапазона от 7000 до 9000.

Глава 3. Типы данных и встроенные функции

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

Типы данных можно разбить на три категории:

– числовые типы;

– символьные типы;

– типы даты и времени.

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

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

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

FROM {источник данных}

В качестве источника данных мы будем использовать служебную таблицу DUAL, которая доступна для всех пользователей. Таблица DUAL имеет один столбец с именем DUMMY, тип данных которого VARCHAR2 (), и содержит одну строку со значением X.

Пример 3.1. Вывод содержимого таблицы DUAL

SELECT *

FROM Dual;

Числовые типы

Числовые типы используются для работы с числовыми данными. Можно использовать следующие форматы чисел:

– целые числа;

– вещественные числа в формате с фиксированной точкой;

– вещественные числа в формате с плавающей точкой.

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

NUMBER (n) – целое число не более n цифр;

NUMBER (n,m) – вещественное число с фиксированной точкой,

n – максимальное число цифр в записи числа а m – число цифр справа от десятичной точки;

NUMBER – вещественное число с плавающей точкой с точностью до 38 цифр.

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

Таблица 3.1. Основные функции для обработки чисел

Функция ROUND

Выполняет округление до ближайшего числа с заданной точностью (результат может быть не целым). Синтаксис:

ROUND (n,m)

n – численное значение;

m – точность округления.

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

Пример 3.2. Примеры использования функции ROUND

SELECT ROUND (147.76), ROUND (147.76,1), ROUND (147.76, -1)

FROM Dual;

Пример 3.3. Вывести значение зарплаты, округленное до 1000

SELECT employee_id, first_name, last_name, department_id, salary, ROUND (salary, -3)

FROM Employees;

Функция TRUNC

Усекает (отбрасывает) значащие цифры справа без округления. Синтаксис:

TRUNC (n, m)

Параметры n, m аналогичны параметрам функции ROUND.

Пример 3.4. Пример использования функции TRUNC

SELECT TRUNC (147.76), TRUNC (147.76,1), TRUNC (147.76, -1)

FROM Dual;

Функция MOD

Возвращает остаток от деления n на m. Синтаксис:

MOD (n,m)

где n и m – численные значения целого или вещественного типа.

Пример 3.5. Пример использования функции MOD

SELECT mod (5,2), mod (6.5,1),mod (6.5,2.2)

FROM Dual;

Пример 3.6. Найти сотрудников с нечетным рейтингом

SELECT employee_id, first_name, last_name,

department_id, rating_e

FROM Employees

WHERE MOD (rating_e,2) =1;

Пример 3.7. Вывести ту часть зарплаты сотрудника, которая меньше 1000

SELECT employee_id, first_name, last_name, department_id,

salary, MOD (salary,1000)

FROM Employees;

Функция POWER

Возводит число x в степень n. Синтаксис:

POWER (x,n)

x – численное значение;

n – степень, может иметь вещественный тип и отрицательное значение.

Пример 3.8. Пример использования функции POWER

SELECT POWER (2,2),POWER (9,0.5),POWER (10, -1)

FROM Dual;

Функция SQRT

Возвращает квадратный корень от числа. Синтаксис:

SQRT (x)

x – численное значение;

Пример 3.9. Пример использования функции SQRT

SELECT SQRT (1), SQRT (9),SQRT (7)

FROM Dual;

Символьные типы

Символьные типы используются для работы с данными, представленными в виде текста. Основными символьными типами являются:

CHAR (n) – строка символов фиксированной длины n, позволяет хранить символьные данные длиной от 1 до 2000 символов. Если длина (n) явно не указана, то она считается равной 1. Если длина присваиваемого значения будет меньше n, то оно дополняется пробелами справа.

VARCHAR2 (n) – строка символов переменной длины, которая может содержать не более n – 1 символа, предназначена для хранения символьных данных длиной от 1 до 4000 символов. Хранит столько символов, сколько содержит присваиваемое значение. Значение n трактуется как максимально возможная длина строки.

Типы NCHAR (n) и NVARCHAR2 (n) – предназначены для хранения символьных данных фиксированной и переменной длинны в формате Unicode.

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

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

– функции преобразования регистра;

– функции обработки строк.

Таблица 3.2. Функции преобразования регистра

Пример 3.10. Вывести название товара, используя различные функции преобразования регистра

SELECT

UPPER (Product_name) As UPPER,

LOWER (Product_name) As LOWER,

INITCAP (Product_name) As INITCAP

FROM Products

WHERE product_id = 50;

Довольно часто столбец, имеющий символьный тип, содержит значения в различных регистрах. Например, столбец first_name может содержать как значение DAVID, так и значение David. В этом случае запрос, содержащий условие выбора: first_name = ′DAVID′ или first_name = ′David′ выведет только часть необходимых данных. Эту проблему можно решить, используя функции преобразования регистра.

Пример 3.11. Вывести данные о сотрудниках, у которых столбец first_name имеет значение DAVID, или David, или david

SELECT employee_id, first_name, last_name, department_id, salary

FROM Employees

WHERE UPPER (first_name) = ′DAVID′;

Таблица 3.3. Функции обработки строк

Примеры использования функций обработки строк

Функции LPAD () и RPAD () можно использовать для отображения результата выполнения запроса в виде, который более удобен для восприятия.

Пример 3.12. Вывод данных о зарплате сотрудников без использования функций LPAD () и RPAD ()

SELECT first_name||′ ′||last_name || ′ has a monthly salary of ′

|| salary || ′ dollars. ′ AS Pay

FROM Employees;

Пример 3.13. Вывод данных о зарплате сотрудников c использованием функций LPAD () и RPAD ()

SELECT RPAD (first_name||′ ′||last_name,25)

|| ′ has a monthly salary of ′

|| LPAD (salary,6) || ′ dollars.» AS Pay

FROM Employees;

Рассмотрим более подробно функцию INSTR, которая часто используется при работе с символьными данными.

Функция INSTR возвращает номер позиции в строке str, начиная с которой строка search_str входит в строку str. Если вхождений не найдено, то функция возвращает значение 0. Синтаксис:

INSTR (str, search_str [, n [, m]])

– str – исходная строка;

– search_str – строка поиска;

– n – начало поиска, определяет начальную позицию, с которой следует начинать поиск;

– m – вхождение, определяет номер вхождения, который следует возвратить.

Пример 3.14. Использование функции INSTR для нахождения позиции первого пробела в названии товара

SELECT product_name, INSTR (product_name, ′ ′)

FROM Products;

Пример 3.15. Использование функции INSTR для нахождения позиции второго пробела в названии товара

SELECT product_name, INSTR (product_name, ′ ′,1,2)

FROM Products;

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

Пример 3.16. Вывести первые два слова из названия товара

SELECT SUBSTR (product_name,1,

(INSTR (product_name, ′ ′,1,2) -1))

FROM Products;

Пример 3.17. Вывести названия товаров, первое слово которых состоит из трех символов

SELECT product_id, product_name

FROM Products

WHERE Length (SUBSTR (product_name,1,

(INSTR (product_name, ′ ′) -1))) =3;

Используя функцию INSTR, можно осуществлять поиск по части строчного значения.

Пример 3.18. Вывести данные о товарах, в названии которых есть слово Core

SELECT *

FROM Products

WHERE INSTR (UPPER (product_name), ′CORE»′> 0;

Типы даты и времени

Эти типы используются для работы с данными, представляющими собой даты с учетом времени. Тип Date является основным при работе с данными, представляющими собой дату и время. При использовании этого типа данные хранятся в формате DD-MM-YY HH: MI: SS, где:

DD – двузначное значение дня;

MM – двузначный номер месяца;

YY – две последние цифры года;

HH, MI, SS – двузначные значения часа, минуты и секунды.

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

Пример 3.19. Вывод текущей даты в формате по умолчанию

SELECT SYSDATE

FROM Dual;

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

Пример 3.20. Вывод текущей даты с использованием функции TO_CHAR

SELECT TO_CHAR (SYSDATE, ′DD MONTH YYYY, HH24:MI: SS′)

As Date_Time

FROM Dual;

К типам данных, используемых для представления значений даты и времени, также относятся:

– TIMESTAMP – аналогичен типу Date, но время хранится с точностью до миллиардной доли секунды. Для получения текущей даты в этом формате используется функция LOCALTIMESTAMP;

– TIMESTAMP WITH TIME ZONE – хранит вместе со значением даты и времени информацию о часовом поясе. Часовым поясом называется смещение от времени по Гринвичу. Для получения текущей даты в этом формате используется функция SYSTIMESTAMP.

Пример 3.21. Использование функция LOCALTIMESTAMP

SELECT LOCALTIMESTAMP

FROM DUAL;

Пример 3.22. Использование функция SYSTIMESTAMP

SELECT SYSTIMESTAMP

FROM DUAL;

Функции для работы с данными, имеющими тип даты и времени

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

Таблица 3.4. Функции для работы с данными, имеющими тип даты и времени

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

Прибавление целого значения n к значению типа Date эквивалентно прибавлению n дней к дате. Прибавление значения n/24 к значению типа Date эквивалентно прибавлению n часов к дате.

Если в выражении участвует строка, содержащая значение даты, то ее рекомендуется преобразовать к значению типа Date, используя функцию TO_DATE ().

Рассмотрим примеры, в которых значения, имеющие тип Date, участвуют в арифметических выражениях.

Пример 3.23. Вывод значения текущей даты, увеличенного на 45 дней

SELECT SYSDATE, SYSDATE +45

FROM DUAL;

Пример 3.24. Вывод значения текущей даты и времени, увеличенного на два часа

SELECT TO_CHAR (SYSDATE, ′DD MONTH YYYY, HH24:MI: SS′)

As Date1,

TO_CHAR (SYSDATE +2/24, ′DD MONTH YYYY, HH24:MI: SS′)

As Date2

FROM DUAL;

Пример 3.25. Определить количество дней, прошедших между датой приема на работу и сегодняшним днем

SELECT employee_id, ROUND (SYSDATE – hire_date AS DAYS, -2)

FROM Employees;

Следует обратить внимание на то, что этот запрос возвращает дробное значение, так как значение, возвращаемое функцией SYSDATE, содержит текущее время, которое трактуется как часть суток: например, 12 часов отобразятся в виде значения 0,5.

Пример 3.26. Определить количество недель, прошедших между датой приема на работу и сегодняшним днем

SELECT employee_id, TRUNC ((SYSDATE – hire_date) /7)

AS WEEKS

FROM Employees;

Для определения интервалов между двумя датами в месяцах следует использовать специальную функцию MONTHS_BETWEEN.

Пример 3.27. Вывести данные о сотрудниках и количестве месяцев, прошедших между датой приема на работу и сегодняшним днем

SELECT employee_id, first_name, last_name,

department_id, hire_date,

—

TRUNC (MONTHS_BETWEEN (SYSDATE, hire_date)) AS MONTHS

– —

FROM Employees;

Пример 3.28. Вывести данные о сотрудниках, которые проработали более 30 лет

SELECT employee_id, first_name, last_name, salary,

department_id, hire_date

FROM Employees

WHERE MONTHS_BETWEEN (SYSDATE, hire_date)> 360;

Функция NEXT_DAY (x, день недели) возвращает следующую ближайшую дату, соответствующую определенному дню недели: например, среда.

Пример 3.29. Использование функции NEXT_DAY

SELECT SYSDATE AS «Сегодня», EXT_DAY (SYSDATE, ′Tuesday′)

AS Tuesday

FROM DUAL;

Функция LAST_DAY (x) возвращает дату, соответствующую последнему дню месяца, которому принадлежит x.

Пример 3.30. Использование функции LAST_DAY

SELECT SYSDATE, LAST_DAY (SYSDATE)

FROM DUAL;

Функция ROUND (x, {параметр}) округляет дату x, если параметр отсутствует, то до начала ближайших суток; если {параметр} = MM/ MON / MONTH – то до начала ближайшего месяца; если параметр = YY / YYYY /YEAR – то до начала ближайшего года.

Пример 3.31. Использование функции ROUND c параметром MM

SELECT ROUND (TO_DATE (′12.05.2018′, ′DD.MM.YYYY′),′MM′)

As ′′ ROUND MONTCH 12.05.2018 ′′,

ROUND (TO_DATE (′20.05.2018′, ′DD.MM.YYYY′),′MM′)

As ′′ ROUND MONTCH 20.05.2018 ′′

FROM DUAL;

Пример 3.32. Использование функции ROUND c параметром YYYY

SELECT ROUND (TO_DATE (′12.05.2018′, ′DD.MM.YYYY′),′YYYY′)

As ′′ ROUND YEAR 12.05.2018 ′′,

ROUND(TO_DATE(′12.07.2018′,′DD.MM.YYYY′),′YYYY′)

As ′′ ROUND YEAR 12.07.2018 ′′

FROM Dual;

Функция TRUNC (x, {параметр}) отличается от ROUND тем, что возвращает начало текущих суток, начало текущего месяца, начало текущего года соответственно.

Пример 3.33. Использование функции TRUNC c параметром MM.

SELECT TRUNC (TO_DATE (′12.05.2018′, ′D.MM.YYYY′),′MM′)

As ′′ TRUNC MONTCH 12.05.2018 ′′,

TRUNC (TO_DATE (′20.05.2018′, ′D.MM.YYYY′),′MM′)

As ′′ TRUNC MONTCH 20.05.2018 ′′

FROM DUAL;

Функция EXTRACT

Функция EXTRACT возвращает значение заданного поля даты-времени из значения, имеющего тип date. Синтаксис:

EXTRACT ({часть даты} FROM {дата})

Таблица 3.5. Часть даты, возвращаемая функцией EXTRACT

Пример 3.34. Вывести данные о сотрудниках, которые были приняты на работу в 1999 году

SELECT employee_id, first_name, last_name, department_id, hire_date

FROM Employees

WHERE EXTRACT (YEAR FROM hire_date) =1999;