Основная информация об операторе SELECT
DML для InterBase/FireBird
Основная информация об операторе SELECT
Оператор SELECT является самым важным и самым используемым оператором языка SQL. Утверждение «язык SQL – это оператор SELECT» недалеко от истины, поскольку SQL – это язык запросов, а оператор SELECT и является средством формулирования запросов.
Именно этот оператор демонстрирует отличия локальных и серверных БД. В локальной БД извлечение любой информации требует детального знания структуры БД и подробного описания алгоритма получения необходимой информации. В серверных же БД с помощью оператора SELECT формулируется запрос к SQL-серверу о том, какая информация должна быть получена. А каким образом эта информация будет получена, какие алгоритмы ее извлечения будут использованы и как они связаны со структурой БД – все это забота SQL-сервера, его внутренняя работа, скрытая от клиента.
Вообще, локальные базы данных ориентированы на работу с отдельными записями и имеют соответствующие средства навигации (перемещения) по наборам данных для перехода к конкретной записи. Серверные БД ориентированы на работу с множествами записей, удовлетворяющих некоторым требованиям выборки, и имеют средства для указания (выбора) этого множества.
Одним из следствий такого подхода является отсутствие в серверных БД возможности свободной навигации по наборам данных. Так, например, нет другой возможности перейти на произвольную запись НД, как только перебором всех предыдущих записей (имеется ввиду именно перемещение по НД, хотя выбрать нужную запись не составит труда, если указать ее уникальные идентифицирующие признаки). Более того, обычно возможно движение по НД только от начала к концу, но не наоборот. Для реализации возможности произвольного двунаправленного движения необходимо обеспечить буферизацию данных на локальном рабочем месте.
Итак, вся работа в серверных БД ориентирована на действия с некоторыми множествами записей, а средством указания этих множеств является оператор SELECT.
Простейшая форма оператора SELECT
SELECT {* | <значение 1>[,<значение 2>,...]}
FROM <таблица 1>[,<таблица 2>,...];
где <значение 1>, <значение 2>, ... – обычно имена столбцов;
<таблица 1>, <таблица 2>, ... – имена таблиц.
Приведенный оператор извлекает значения указанных столбцов из всех строк указанных таблиц. Если из таблицы необходимо извлечь значения всех столбцов, то вместо перечисления их имен можно использовать символ ‘*’.
Например, для извлечения всего содержимого таблицы лиц:
SELECT * FROM Lica;
что эквивалентно:
SELECT Nlic, Name, Tip, Podr FROM Lica;
Предложение WHERE
Обычно из таблиц извлекают не все строки, а только те из них, которые удовлетворяют определенному условию поиска. С этой целью в оператор SELECT добавляется предложение вида:
WHERE <условие поиска>
где в наиболее простом случае условие поиска имеет вид:
<условие поиска> = <имя столбца><оператор>константа
где <оператор>={<|>|<+|>+|!<|!>|=|<>|!=}, а константа может быть строковым или числовым значением.
Например: извлечь из таблицы наличия все строки, касающиеся предмета с условным номером 3:
SELECT * FROM Nalichie WHERE Predmet=3;
Для задания более сложных условий поиска можно использовать логические операторы: AND, OR, NOT.
Замечание. Всегда следует помнить о важном отличии использования логических операторов в SQL от использования их в других языках программирования: в SQL наивысший приоритет имеют операции сравнения. Поэтому сначала выполняются операторы сравнения, а потом уже логические. Такая особенность SQL избавляет от необходимости использования множества скобок.
Например: извлечь из таблицы наличия строку, касающуюся наличия у лица с условным номером 5 предмета с условным номером 12:
SELECT * FROM Nalichie WHERE Lico=5 AND Predmet=12;
Как видите, здесь нет необходимости в скобках.
Если необходимо проверить наличие значения (NOT NULL) для некоторого столбца, то используется конструкция вида:
<имя столбца> IS [NOT] NULL
Например, дать список всех подотчетных лиц (для них указывается подразделение):
SELECT * FROM Lica WHERE Podr IS NOT NULL;
Внутреннее соединение таблиц
В условии поиска может сравниваться значение столбца из одной таблицы со значением столбца из другой таблицы:
<имя столбца таблицы1> <оператор> <имя столбца таблицы2>
Такие условия часто используются для реализации механизма, аналогичного Lookup-полям локальных БД.
Например, извлечь информацию из таблицы лиц, с указанием реальных наименований типов лиц и подразделений, а не их условных номеров:
SELECT Lica.Name, Tipy.Name, Podrazd.Name
FROM Lica, Tipy, Podrazd
WHERE Tipy.NTip=Lica.Tip AND Lica.Podr=Podrazd.NPodr;
В соответствии с этим оператором, для каждой строки из таблицы лиц будет найдена строка с таким же условным номером типа лица в таблице типов лиц и строка с таким же условным номером подразделения в таблице подразделений. В результирующий НД будут включены не условные номера типов и подразделений, а их реальные наименования (при необходимости можно включить и условные номера).
Обратите внимание, что в предложении WHERE перечисляются все таблицы, участвующие в выборке, а ссылка на столбцы разных таблиц осуществляется путем включения в имя столбца префикса в виде имени его таблицы.
Такой способ объединения информации из разных таблиц получил название внутреннего соединения таблиц.
Замечание: Для внутреннего соединения порядок перечисления таблиц в условии поиска не имеет значения (не важно, столбец какой из таблиц упоминается слева, а какой – справа от знака равенства).
Логически внутреннее соединение таблиц выполняется следующим образом. Вначале строится полное декартово произведение перечисленных таблиц, то есть перебираются все возможные комбинации строк этих таблиц:
строка1 таблицы1, строка1 таблицы2, строка1 таблицы3
строка1 таблицы1, строка1 таблицы2, строка2 таблицы3
строка1 таблицы1, строка1 таблицы2, строка3 таблицы3
. . .
строка1 таблицы1, строка2 таблицы2, строка1 таблицы3
строка1 таблицы1, строка2 таблицы2, строка2 таблицы3
. . .
строка2 таблицы1, строка1 таблицы2, строка1 таблицы3
строка2 таблицы1, строка1 таблицы2, строка2 таблицы3
строка2 таблицы1, строка1 таблицы2, строка3 таблицы3
. . .
Затем из полученного (вероятно очень большого) НД выбираются строки, удовлетворяющие условиям поиска. (Фактически, в целях минимизации работы, SQL-сервер работает несколько иначе, но логически – процесс выглядит именно так.)
Например, пусть таблицы T1 и T2 имеют столбцы C1, C2, C3 и C1, C2, соответственно. И пусть T2 является родительской по отношению к T1, причем связаны они по значению столбцов T2.C1 и T1.C2:
Таблица T1
| C1 | C2 | C3 |
| a | ||
| b | ||
| c | ||
| d |
Таблица T2
| C1 | C2 |
| x | |
| y | |
| z |
Тогда выполнение оператора:
SELECT T1.C1, T2.C2, T1.C3 FROM T1, T2 WHERE T1.C2=T2.C1;
приведет к построению следующего декартова произведения:
T1*T2
| T1.C1 | T1.C2 | T1.C3 | T2.C1 | T2.C2 |
| a | x | |||
| a | y | |||
| a | z | |||
| b | x | |||
| b | y | |||
| b | z | |||
| c | x | |||
| c | y | |||
| c | z | |||
| d | x | |||
| d | y | |||
| d | z |
из которого будут выбраны строки, удовлетворяющие условию поиска T1.C2=T2.C1:
| T1.C1 | T2.C2 | T1.C3 |
| a | x | |
| b | x | |
| c | y |
Замечание 1. При внутреннем соединении таблиц, в результирующий НД не включаются записи, для которых нет соответствия в парной таблице (в рассмотренном выше примере выборки из таблицы лиц с реальными наименованиями подразделений и типов в результирующий НД не включены поставщики и направления списания, так как для них нет соответствия в таблице подразделений). Если такие записи необходимо включить (например, для включения в выборку лиц поставщиков и направлений списания), следует использовать внешнее соединение таблиц (оно будет рассмотрено ниже).
Замечание 2. Рассмотренную в этом пункте форму записи внутреннего соединения таблиц называют неявной формой внутреннего соединения. Альтернативная явная форма предполагает использование предложения JOIN … ON для каждой присоединяемой таблицы. В этом случае приведенный выше пример будет выглядеть следующим образом:
SELECT Lica.Name, Tipy.Name, Podrazd.Name
FROM Lica
JOIN Tipy ON Tipy.NTip=Lica.Tip
JOIN Podrazd ON Lica.Podr=Podrazd.NPodr;
Рекомендуется использовать такую форму записи, поскольку она более информативна и позволяет четко разграничивать условия соединения таблиц (в предложениях JOIN после ключевого слова ON) от условий выбора строк (в предложении WHERE).
Псевдонимы таблиц
При извлечении информации из нескольких таблиц, для имен столбцов указываются префиксы в виде имен таблиц, поскольку в разных таблицах могут быть столбцы с одинаковыми именами. Для упрощения записи соответствующих операторов, таблицам могут назначаться псевдонимы. Они указываются в предложении FROM:
FROM <таблица 1> <псевдоним 1>[, <таблица 2> <псевдоним 2>, ...]
Например:
SELECT L.Name, T.Name, P.Name
FROM Lica L, Tipy T, Podrazd P
WHERE T.NTip=L.Tip AND L.Podr=P.NPodr;
Замечание. Для указания всех столбцов некоторой таблицы следует использовать запись вида: псевдоним.*
Сортировка результирующего НД
В общем случае порядок перечисления строк в НД, возвращаемом оператором SELECT, будет случайным, и предсказать его невозможно.
Для того чтобы результирующий НД был отсортирован в определенном порядке, в операторе SELECT используется предложение:
ORDER BY <список столбцов>
Например, список подотчетных лиц, упорядоченный по наименованиям подразделений и по наименованиям лиц внутри подразделений:
SELECT L.Name, T.Name, P.Name
FROM Lica L, Tipy T, Podrazd P
WHERE T.NTip=L.Tip AND L.Podr=P.NPodr
ORDER BY P.Name, L.Name;
Замечание 1. В отличие от Lookup-полей локальных БД, здесь возможна сортировка и по полям, извлекаемым из родительского НД.
Замечание 2. Для сортировки нет необходимости в наличии индекса, однако индекс существенно ускоряет процесс сортировки.
Исключение повторяющихся записей
Иногда возникает необходимость оставить в результирующем НД по одной строке для каждого значения в некотором столбце (или комбинации столбцов), то есть исключить повторяющиеся строки. Например, если создать список всех предметов, имеющихся на предприятии, путем выборки наименований предметов из таблиц наличия и предметов, то в нем будут повторяющиеся наименования, так как один и тот же предмет может быть в наличии у разных подотчетных лиц.
Для удаления из результирующего НД повторяющихся строк в операторе выборки после слова SELECT ставится слово DISTINCT. Противоположное по смыслу значение имеет слово ALL (включать все строки). Его можно не указывать, так как оно подразумевается по умолчанию.
Например, выборка наименований предметов, имеющихся на предприятии:
SELECT DISTINCT P.Name FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Kolvo>0
ORDER BY P.Name;
Замечание 1. Слово DISTINCT относится ко всем столбцам, перечисленным в SELECT. Нельзя указать его только для одного столбца. Соответственно, повторяющимися считаются строки, имеющие одинаковые значения во всех перечисленных столбцах.
Замечание 2. Использовать DISTINCT следует с осторожностью, поскольку его применение может существенно замедлить выполнение запроса. Во избежание проблем, лучше сравнить скорость выполнения оператора с DISTINCT и без него.
Использование выражений
В списке столбцов оператора SELECT можно использовать выражения (вычисляемые столбцы). Например:
SELECT P.Name, N.Kolvo, P.Cena*N.Kolvo
FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Lico=3
ORDER BY P.Name;
Вычисляемым столбцам можно присваивать имена:
SELECT P.Name, N.Kolvo, P.Cena*N.Kolvo AS Stoimost
FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Lico=3
ORDER BY P.Name;
Выражения можно использовать и в предложении WHERE. Например, выбрать только те предметы, суммарная стоимость которых превышает 1000 рублей:
SELECT P.Name, N.Kolvo, P.Cena*N.Kolvo
FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Lico=3 AND P.Cena*N.Kolvo>1000
ORDER BY P.Name;
Агрегатные функции
Предназначены для расчета итоговых значений по набору данных:
COUNT(<выражение>) – подсчитывает количество вхождений значения выражения во все строки результирующего НД;
SUM(<выражение>) – суммирует значения выражения для всех строк;
AVG(<выражение>) – находит среднее значение выражения для всех строк;
MIN(<выражение>) – находит минимальное значение выражения;
MAX(<выражение>) – находит максимальное значение выражения.
Например, число записей в таблице предметов:
SELECT COUNT(*) FROM Predmety;
Число различных предметов, имеющихся на предприятии:
SELECT COUNT(DISTINCT Predmet) FROM Nalichie
WHERE Kolvo>0;
Суммарное наличие у лица с условным номером 3:
SELECT SUM(P.Cena*N.Kolvo) AS Vsego
FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Lico=3;
Средняя сумма проводки за Январь месяц:
SELECT AVG(P.Cena*V.Kolvo) AS SrSumma
FROM Provodki V, Predmety P
WHERE P.NPredm=V.Predmet AND
V.Date>=”1-JAN-2012” AND V.Date<=”31-JAN-2012”;
Самая крупная передача предметов от лица с условным номером 1 к лицу с номером 3 за Январь месяц:
SELECT P.Name, V.Date, MAX(P.Cena*V.Kolvo) AS MaxProvodka
FROM Provodki V, Predmety P
WHERE P.NPredm=V.Predmet AND V.Rashod=1 AND V.Prihod=3
AND V.Date>=”1-JAN-2012” AND V.Date<=”31-JAN-2012”;
3.3.2.Модификация наборов данных
Операторы добавления, изменения и удаления строк в SQL, как и все другие операторы SQL, ориентированы на работу с группами строк, а не с отдельными строками. Поэтому всегда следует уделять особое внимание таким операторам в части ограничения выборки (предложение WHERE) с тем, чтобы не получить неожиданный результат.
Добавление строк в таблицу осуществляется оператором INSERT следующего формата:
INSERT INTO имя_таблицы[(столбец_1[, столбец_2 ...])]
{VALUES(<значение_1>[, <значение_2> ...]) |
<оператор SELECT>};
Список столбцов указывает столбцы, которым будут присваиваться значения. Если список опущен, то значения будут присваиваться всем столбцам таблицы, причем в том порядке, в котором они создавались.
Замечание. Во избежание недоразумений, список столбцов рекомендуется всегда указывать, поскольку их порядок может изменяться при изменении структуры таблицы.
Значения, присваиваемые столбцам, могут указываться двояко. Они или указываются явно, после ключевого слова VALUES, или формируются с помощью оператора SELECT.
При явном задании значений оператор INSERT имеет вид:
INSERT INTO имя_таблицы[(столбец_1[, столбец_2, ...])]
VALUES(<значение_1>[, <значение_2> ...]);
Этот оператор добавит в таблицу одну строку, в которой значение_1 будет присвоено столбцу_1, значение_2 – столбцу_2 и т.д. Значения должны соответствовать типу столбцов (с учетом возможностей автоматического преобразования).
Например, добавить в таблицу наличия строку для предмета с условным номером 12 у лица с номером 5 и количеством в 100 единиц:
INSERT INTO Nalichie(Lico, Predmet, Kolvo)
VALUES(5, 12, 100);
Если какой-либо столбец отсутствует в списке присвоения, то ему не будет присвоено никакого значения (NULL).
Во второй своей форме (с оператором SELECT) INSERT добавляет в таблицу столько строк, сколько их будет в НД, возвращенном оператором SELECT. При этом значения присваиваются столбцам в том порядке, в каком они перечислены в операторе INSERT и в SELECT. Например, для копирования всех строк из таблицы проводок в таблицу архива (с той же структурой, что и таблица проводок) можно использовать оператор вида:
INSERT INTO Archive SELECT * FROM Provodki;
Здесь списки столбцов опущены, так как подразумевается полная идентичность структур таблиц. В общем же случае следует указать список столбцов и в INSERT и в SELECT.
Изменение значений столбцов в строках таблицы осуществляется с помощью оператора UPDATE следующего формата:
UPDATE имя_таблицы
SET столбец_1=<значение 1>[, столбец_2=<значение 2> ...]
[WHERE <условие поиска>];
Изменению подвергаются все строки таблицы, удовлетворяющие условию поиска. Будьте внимательны, используя этот оператор! Если опустить предложение WHERE, то будут изменены все строки таблицы ! Условие поиска в UPDATE задается так же, как и в операторе SELECT.
Например, деноминация цен в таблице предметов путем деления цены на 1000:
UPDATE Predmety SET Cena=Cena/1000;
Изменить в таблице проводок все даты проводок за 1.01.2012 на 11.01.2012:
UPDATE Provodki SET Data=”11-JAN-2012”
WHERE Data=”1-JAN-2012”;
Удаление строк из таблицы выполняется оператором:
DELETE FROM имя_таблицы [WHERE <условие поиска>];
Обратите внимание, удаляются все строки, удовлетворяющие условию поиска. Если опустить условие поиска, то таблица будет полностью очищена!
3.3.3.Использование генераторов
В InterBase/FireBird нет автоинкрементных полей. Вместо них применяются генераторы, которые возвращают уникальные значение целого типа. Для создания генератора используется оператор вида:
CREATE GENERATOR имя_генератора;
Этот оператор создает генератор и устанавливает его начальное значение в ноль. Если необходимо изменить начальное значение для созданного генератора, то используется оператор:
SET GENERATOR имя_генератора TO целое_число;
Для получения уникального значения к генератору обращаются с помощью функции:
GEN_ID (имя_генератора, шаг);
Эта функция возвращает увеличенное на шаг предыдущее значение, выданное генератором. При необходимости можно также использовать отрицательные значения для шага.
Замечание. Не рекомендуется для одного и того же генератора переустанавливать начальное значение или при разных обращениях использовать разный шаг, чтобы исключить вероятность получения неуникальных значений.
Например, для присвоения условных номеров предметам можно использовать следующий генератор:
CREATE GENERATOR PredmetN;
. . .
INSERT INTO Predmety(NPredm, Name, EdIzm, Cena)
VALUES(GEN_ID(PredmetN), ”Бензин АИ-92”, ”л”, 19.50);
Замечание 1. Не существует оператора DROP GENERATOR. Если необходимо удалить генератор из БД, то это следует сделать в системной таблице RDB$GENERATORS. Там же можно посмотреть всю информацию о созданных генераторах.
Замечание 2. Чаще всего присвоение уникальных значений полям производится в триггерах, вызываемых перед добавлением новой строки. Например, для таблицы предметов можно использовать такой триггер:
CREATE TRIGGER BI_Predmety FOR Predmety
ACTIVE BEFORE INSERT AS
BEGIN
NEW.NPredm=GEN_ID(PredmetN,1);
END
Соответственно, в операторе добавления новой записи значение полю условного номера предмета присваивать не надо, поскольку такое присвоение будет автоматически выполнено в созданном триггере.
Замечание 3. Если присвоение уникальных значений полям желательно выполнять в клиентской части приложения, то для получения этих уникальных значений с сервера можно создать хранимую процедуру вида:
CREATE PROCEDURE Get_PredmetN RETURNS(N Integer) AS
BEGIN
N=GEN_ID(PredmetN,1);
END
и вызывать ее перед добавлением новой строки в таблицу предметов.
3.3.4.Хранимые процедуры
Одним из наиболее важных преимуществ клиент-серверной модели БД является перенос основной массы вычислительной работы на сервер. Это, с одной стороны, снижает требования к оборудованию клиентских рабочих мест, а с другой, уменьшает нагрузку на сеть, поскольку по сети передается не вся информация, а лишь результаты её обработки. Основной составляющей при реализации такого подхода являются хранимые процедуры, выполняемые на сервере.
Хранимая процедура – это программный модуль, написанный на специальном языке хранимых процедур, и хранящийся в БД как её элемент.
Хранимые процедуры могут использоваться при подготовке данных для отчетов, внесении массовых изменений в БД, для выполнении периодических действий в БД и множества других операций. Вообще, можно сформулировать такое правило: если какое-либо действие может быть выполнено без участия клиента, то его следует реализовать на сервере с помощью хранимой процедуры или триггера.
С некоторой долей условности хранимые процедуры можно разделить на две категории:
· процедуры выбора так же, как и оператор SELECT, могут возвращать многострочные наборы данных (то есть несколько наборов значений своих выходных параметров);
· процедуры действия возвращают один набор значений своих выходных параметров или вообще ничего не возвращают, а только выполняют некоторые действия в БД.
Создание хранимой процедуры
Выполняется оператором вида:
CREATE PROCEDURE имя_процедуры
[(вх.параметр1 тип_данных[,вх.параметр2 тип_данных...])]
[RETURNS(вых.параметр1 тип_данных[,вых.параметр2
тип_данных...])]
AS <тело процедуры>;
Входные параметры предназначены для передачи в процедуру некоторых значений из точки вызова. Выходные параметры предназначены для возврата результирующих значений.
Обратите внимание на четкое разграничение входных и выходных параметров. В принципе, входные параметры можно изменять в теле процедуры, но это никак не скажется на точке ее вызова.
Тип данных для параметров может быть любым, кроме массивов. И входные и выходные параметры могут отсутствовать.
Тело процедуры имеет формат:
[<объявление локальных переменных>]
BEGIN
<оператор>
[<оператор>...]
END
Операторы в теле процедуры разграничиваются точкой с запятой.
Для написания процедур используется специальный внутренний язык InterBase /FireBird, который называется PSQL. Он является процедурным языком и, соответственно, в нем есть операторы управления ходом вычислительного процесса. Этот же язык используется для написания триггеров.
Объявление локальных переменных
Локальные переменные служат для хранения промежуточных результатов и доступны только в пределах своей процедуры. Формат объявления:
DECLARE VARIABLE имя_переменной тип_данных;
В качестве типа данных может использоваться любой тип InterBase/FireBird. Для объявления каждой переменной используется отдельный оператор объявления.
Замечание.При обращении к локальным переменным и параметрам, в операторах SELECT, INSERT, UPDATE и DELETE их имена должны предваряться двоеточием. В противном случае они будут рассматриваться как имена столбцов. Т.е. во всех операторах, где могут присутствовать столбцы наборов данных, имена параметров процедуры и локальных переменных должны предваряться двоеточием, чтобы отличить их от имен столбцов (имена столбцов и переменных могут совпадать). Во всех остальных операторах они используются без двоеточия.
Операторные скобки BEGIN ... END
Используются двояко. Во-первых, они ограничивают исполняемую часть процедуры, а, во-вторых, позволяют везде, где можно записать один оператор, записать группу операторов.
Замечание 1:Ни после BEGIN, ни после END точка с запятой не ставится.
Замечание 2:Для комментариев используются скобки вида:
/* комментарий */
Выражения и оператор присваивания
Оператор присваивания имеет вид:
имя_переменной=<выражение>;
В качестве выражения могут выступать арифметические, строковые и логические выражения, в которых помимо констант и переменных могут использоваться встроенные функции, функции, определенные пользователем, и генераторы.
В арифметических выражениях могут использоваться операторы: *, /, +, -; в логических: AND, OR, NOT. В строковых выражениях для объединения строк используется оператор конкатенации “||”. Например:
Ans=’Текущая дата - ’ || ’TODAY’;
Для преобразования всех символов строкового выражения к верхнему регистру используется встроенная функция UPPER. Например, ее можно использовать при поиске вне зависимости от регистра написания поискового контента:
IF (UPPER(Name)=’МОСКВА’) THEN...
Здесь условие сравнения будет выполнено и для значения «Москва» в столбце Name, и для «москва» и для «МОСКВА» и т.д.
Для преобразования значений из одного типа в другой используется встроенная функция CAST. Формат использования функции CAST:
CAST({<выражение> | NULL} AS тип_данных)
Например:
DECLARE VARIABLE Data CHAR(11);
. . .
Data=CAST(’TODAY’ AS CHAR);
Для указания дат могут использоваться следующие встроенные функции, которые вызываются посредством литералов:
’NOW’ – текущая дата и время;
’TODAY’ – текущая дата без времени;
’TOMORROW’ – завтрашняя дата без времени;
’YESTERDAY’ – вчерашняя дата без времени.
Замечание (важно).Если в арифметическом или строковом выражении встретится переменная, не имеющая значения (NULL), то и результатом выражения будет NULL не зависимо от значений всех остальных переменных и констант в выражении.
Например, если A IS NULL; B=1; то после выполнения A=A+1; или B=A+B; результат будет NULL.
Поэтому, если нет уверенности, что переменная (или столбец) обязательно будет иметь значение (NOT NULL), то перед использованием её в выражении необходимо сделать проверку на NULL:
IF (A IS NULL) THEN A=0; A=A+1;
Точно также в логических выражениях подразумевается не двух-, а трехзначная логика (TRUE, FALSE, UNKNOWN). Соответственно, в операторе IF часть THEN выполняется, если логическое выражение дает значение TRUE, и часть ELSE, если – FALSE или UNKNOWN.
Помимо встроенных функций и агрегатных функций (MIN, MAX, SUM,...) пользователем могут быть определены собственные UDF-функции (User Defined Function). Они записываются на любом языке программирования и оформляются в виде DLL-библиотек. Перед использованием UDF-функция должна быть объявлена с указанием местоположения ее библиотеки.
Замечание. С дистрибутивами InterBase и FireBird поставляются библиотеки ib_udf.dll и fbudf.dll, в которых определено множество строковых и математических функций. Скрипты объявления этих функций записаны в файлах ib_udf.sql и fbudf.sql. Если предполагается использовать только часть этих функций, скрипты следует отредактировать.
Последовательность выполнения операторов в выражениях:
1) сначала выполняются объединения строк;
2) арифметические операторы;
3) операторы сравнения;
4) логические операторы выполняются в последнюю очередь.
Порядок выполнения арифметических операторов:
1) умножение;
2) деление;
3) сложение;
4) вычитание.
Замечание. Умножение и деление, а также сложение и вычитание имеют разный приоритет. Благодаря большему приоритету операции умножения по отношению к делению, обеспечивается более высокая точность выполнения операций для чисел с плавающей точкой.
Порядок выполнения операций сравнения:
1) =
2) <>
3) >
4) <
5) >=
6) <=
7) !>
8) !<
Замечание. Операторы ALL, SOME, EXISTS, IN, NULL, LIKE, ... выполняются после всех операций сравнения в порядке слева-направо.
Порядок выполнения логических операторов:
1) NOT
2) AND
3) OR
Замечание. Порядок выполнения операторов можно изменять с помощью скобок. В сложных выражениях рекомендуется всегда использовать скобки.
Условный оператор
Имеет формат:
IF (<условие>) THEN <оператор_1> [ELSE <оператор_2>]
где условие представляет собой логическое выражение, которое может давать результат TRUE, FALSE или UNKNOWN. Если результат TRUE, то выполняется оператор_1, если FALSE или UNKNOWN – то оператор_2.
Условие обязательно заключается в скобки. Часть ELSE в операторе может отсутствовать.
Замечание. И оператор_1 и оператор_2 должны заканчиваться точкой с запятой.
Пример. Сформировать строку S из двух строк: S1 и S2, которые могут не иметь значения (NULL):
IF (S1 IS NOT NULL) THEN
IF (S2 IS NOT NULL) THEN
S = S1 || S2;
ELSE
S = S1;
ELSE
IF (S2 IS NOT NULL) THEN
S = S2;
ELSE
S = ’’;
То же самое можно сделать и так:
IF (S1 IS NULL) THEN S1=’’;
IF (S2 IS NULL) THEN S2=’’;
S = S1 || S2;
Оператор цикла
Имеет формат:
WHILE (<условие>) DO <оператор>
Оператор выполняется до тех пор, пока условие имеет значение TRUE.
Пример. Процедура вычисления факториала:
CREATE PROCEDURE Factorial(N INTEGER)
RETURNS(F DOUBLE PRECISION) AS
DECLARE VARIABLE I INTEGER;
BEGIN
F = 1;
I = 2;
WHILE (I <= N) DO
BEGIN
F = F*I; I = I+1;
END
END
Оператор выборки одного набора значений
Имеет такой же формат, как и обычный оператор SELECT, но к нему добавляется предложение INTO, в котором через запятую перечисляются переменные или параметры, которым должны быть присвоены выбранные значения.
Замечание. Оператор SELECT в этой форме должен возвращатьтолько один набор значений или ни одного. В случае возвращения нескольких наборов будет сгенерирована ошибка.
См. пример использования такого оператора в следующем пункте.
Оператор множественной выборки
Имеет формат:
FOR <оператор SELECT> DO <оператор_1>
Для каждого набора значений, возвращенных оператором SELECT, выполняется оператор_1. SELECT обязательно должен содержать предложение INTO.
Пример. Процедура расчета количества предметов одного и того же наименования без учета цены (купленные в разное время предметы могут иметь разную цену):
CREATE PROCEDURE KolvoPredm(Name VARCHAR(30))
RETURNS(Kolvo DOUBLE PRECISION) AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE K DOUBLE PRECISION;
BEGIN
Kolvo = 0;
FOR SELECT NPredm FROM Predmety WHERE UPPER(Name)=:Name
INTO :N DO
BEGIN
SELECT SUM(Kolvo) FROM Nalichie WHERE Predmet=:N
INTO :K;
IF (K IS NULL) THEN K = 0;
Kolvo = Kolvo + K;
END
END
Обратите внимание на использование префикса в виде двоеточия для имен параметров и переменных в операторах SELECT для того, чтобы отличить их от имен столбцов (в UPPER(Name) имеется ввиду столбец Name таблицы Predmety, а после знака равенства :Name указывает на входной параметр Name). В операторе же присваивания для переменных и параметров префикс не используется, поскольку там не может быть имен столбцов.
Отметим также, что приведенная процедура является процедурой действия, поскольку возвращает только одно значение выходного параметра.
Оператор приостановки SUSPEND
Используется в процедурах выбора для указания момента передачи в вызывающее приложение очередного набора значений выходных параметров.
Когда в процедуре выбора достигается оператор SUSPEND, в вызывающее приложение возвращается набор значений параметров, перечисленных в предложении RETURNS, и выполнение процедуры приостанавливается до прихода запроса на следующий набор значений.
Замечание:Оператор SUSPEND – характерный признак процедуры выбора.
Пример. Изменим процедуру, приведенную в предыдущем примере, таким образом, чтобы она выдавала количество предметов одного и того же наименования для каждой из возможных цен:
CREATE PROCEDURE KolvoPredmPoCene(Name VARCHAR(30))
RETURNS(Cena DOUBLE PRECISION, Kolvo DOUBLE PRECISION) AS
DECLARE VARIABLE N INTEGER;
BEGIN
FOR SELECT NPredm, Cena FROM Predmety
WHERE UPPER(Name)=:Name INTO :N, :Cena DO
BEGIN
SELECT SUM(Kolvo) FROM Nalichie WHERE Predmet=:N
INTO :Kolvo;
SUSPEND;
END
END
Данная процедура является процедурой выбора, поскольку в общем случае может возвращать несколько наборов значений выходных параметров (цена предметов заданного наименования и количество предметов заданного наименования по этой цене; а цен для одного и того же наименования предметов может быть несколько).
Оператор выхода EXIT
Немедленно прекращает выполнение процедуры и передает управление на последний оператор END в процедуре. EXIT может находиться в любом месте, в том числе, и внутри цикла.
Соотношение операторов SUSPEND, EXIT и END
| Тип процедуры | SUSPEND | EXIT | последний END |
| процедура действия | передает управление на последний END | передает управление на последний END | возвращает значения параметров и передает управление вызывающему приложению |
| процедура выбора | возвращает значения параметров и приостанавливает процедуру до следующего запроса | передает управление на последний END | передает управление вызывающему приложению и сообщает, что поток записей окончен |
Вызов других процедур
Хранимые процедуры в процессе выполнения могут вызывать другие хранимые процедуры.
Допустимы рекурсивные вызовы, при этом для каждого вызова создается отдельный экземпляр процедуры. Максимально допустимая глубина рекурсии – 1000 (ограничена во избежание бесконечной рекурсии), хотя физически ограничение может наступить раньше, ввиду превышения допустимого размера стека.
Формат вызова другой процедуры:
EXECUTE PROCEDURE имя_процедуры
[входной_параметр_1[,входной_параметр_2...]]
[RETURNING_VALUES выходной_параметр_1
[, выходной_параметр_2...]];
Список входных параметров указывает на переменные и/или содержит константы, значения которых будут использованы для передачи в вызывающую процедуру. Список выходных параметров указывает на переменные, которым будут присвоены возвращённые процедурой значения.
Обратите внимание, что списки параметров записываются без скобок. Все имена переменных в списках должны предваряться двоеточием.
Пример. Процедура выбора проводок по конкретному предмету, стоимость которых более чем в три раза превосходит среднюю стоимость проводок по этому предмету (то есть отбор наиболее существенных проводок по конкретному предмету):
CREATE PROCEDURE AvgProvodka(NPr INTEGER)
RETURNS(AvgSumma DOUBLE PRECISION) AS
BEGIN
SELECT AVG(V.Kolvo*P.Cena) FROM Provodki V, Predmety P
WHERE V.Predmet=P.NPredm AND V.Predmet=:NPr
INTO :AvgSumma;
END
CREATE PROCEDURE SelectBigProvodki(NPredm INTEGER)
RETURNS(NameRashod CHAR(30), NamePrihod CHAR(30),
Data DATE, Kolvo DOUBLE PRECISION) AS
DECLARE VARIABLE AVS DOUBLE PRECISION;
BEGIN
EXECUTE PROCEDURE AvgProvodka :NPredm
RETURNING_VALUES :AVS;
FOR SELECT L1.Name, L2.Name, V.Data, V.Kolvo
FROM Provodki V
JOIN Lica L1 ON L1.Nlic=V.Rashod
JOIN Lica L2 ON L2.Nlic=V.Prihod
JOIN Predmety P ON P.NPredm=V.Predmet
WHERE P.Cena*V.Kolvo > 3*:AVS
INTO :NameRashod, :NamePrihod, :Data, :Kolvo DO
SUSPEND;
END
Обратите внимание, что во второй процедуре в операторе SELECT приходится строить два внутренних соединения таблицы проводок с таблицей лиц для извлечения реальных наименований лица расхода и лица прихода. Соответственно, таблице лиц приходится назначать два псевдонима (L1 и L2) для указания условий этих соединений.
Отметим также, что возможности оператора SELECT столь обширны, что обе эти процедуры можно заменить одним оператором SELECT (как это сделать, станет ясно позже, после обсуждения вложенных операторов SELECT). И это далеко не самое сложное из того, на что способен этот оператор.
Исключительные ситуации (ИС)
Если при выполнении хранимой процедуры происходит ошибка, то генерируется исключительная ситуация.
Исключительные ситуации бывают трех видов:
1) ошибки SQL – возвращают номер ошибки SQLCODE;
2) ошибки InterBase/FireBird – возвращают номер ошибки GDSCODE;
3) исключительные ситуации, определенные пользователем.
Исключительные ситуации, определенные пользователем, являются таким же элементом БД, как и сами хранимые процедуры. Поэтому перед использованием они должны быть определены в БД с помощью оператора:
CREATE EXCEPTION имя_исключит_ситуации ’<сообщение>’;
Сообщение возвращается в приложение в случае наступления исключительной ситуации.
После того, как исключительная ситуация определена, она может быть инициирована в процедуре или триггере с помощью оператора:
EXCEPTION имя_исключит_ситуации;
Например:
. . .
CREATE EXCEPTION KolvoLessZero ’Количество меньше нуля’;
. . .
IF (Kolvo < 0) THEN EXCEPTION KolvoLessZero;
. . .
Удаление исключительной ситуации из БД выполняется оператором:
DROP EXCEPTION имя_исключит_ситуации;
изменение:
ALTER EXCEPTION имя_исключит_ситуации ’<сообщение>’;
Если исключительная ситуация не обрабатывается в процедуре (для такой обработки используется специальный оператор WHEN), то при её наступлении выполнение процедуры прекращается, все выполненные в ней действия отменяются и в вызывающее приложение передается сообщение об ошибке.
Удаление и изменение хранимых процедур
Удаление хранимой процедуры осуществляется оператором:
DROP PROCEDURE имя_процедуры;
Изменение процедуры возможно либо с помощью пары операторов: DROP – CREATE, либо с помощью оператора ALTER PROCEDURE, имеющего такой же формат, как и оператор CREATE PROCEDURE.
Замечание.Оператор ALTER PROCEDURE удобно использовать в тех случаях, когда процедуру нельзя удалить из-за взаимосвязи с другими процедурами или триггерами.
Вызов хранимых процедур
Выполнение хранимой процедуры действия инициируется оператором:
EXECUTE PROCEDURE имя_процедуры
[(входной_параметр_1[, входной_параметр_2...])];
Например, рассчитать с помощью приведенной в предыдущих примерах процедуры среднюю стоимость проводок по предмету с условным номером 15:
EXECUTE PROCEDURE AvgProvodka(15);
Для обращения к процедуре выбора используется оператор SELECT, в предложении FROM которого указывается имя процедуры с перечислением значений входных параметров. В списке возвращаемых значений оператора SELECT указываются либо все выходные параметры (*) или только необходимые. Например, показать с помощью приведенной в предыдущих примерах процедуры наиболее крупные проводки по предмету с условным номером 12:
SELECT * FROM SelectBigProvodki(12);
или показать между какими лицами выполнялись крупные проводки по указанному предмету:
SELECT DISTINCT NameRash, NamePrih FROM SelectBigProvodki(12);
Замечание. Процедуру выбора также можно вызвать и с помощью оператора EXECUTE, но при этом будет рассчитан только первый набор значений выходных параметров. Если процедуру действия вызвать через оператор SELECT, то она будет выполнена, но значения выходных параметров не будут возвращены.
Способы вызова хранимых процедур из клиентских приложений обсуждаются в методическом пособии по выполнении лабораторных работ при описании компонентов Delphi, предназначенных для работы с InterBase/FireBird.
3.3.5.Триггеры
Триггер – это процедура, автоматически выполняемая сервером при добавлении, изменении или удалении строки в таблице, с которой триггер связан.
В отличие от хранимых процедур, триггеры не имеют ни входных, ни выходных параметров и их нельзя вызывать непосредственно. Вызов триггера порождается только соответствующим действием в таблице, с которой триггер связан.
Триггеры, так же как и все хранимые процедуры работают в контексте конкретной транзакции. Поэтому откат транзакции, в рамках которой сработал триггер, приведет к отмене и всех изменений, выполненных триггером.
В зависимости от события, к которому привязан триггер, различаются триггеры, вызываемые при:
1) добавлении строки;
2) удалении строки;
3) изменении строки.
В зависимости от времени срабатывания триггеры делятся на:
1) выполняемые до наступления события;
2) выполняемые после наступления события
Создание триггера
Выполняется оператором:
CREATE TRIGGER имя_триггера FOR имя_таблицы
[{ACTIVE | INACTIVE}] {BEFORE | AFTER}
{DELETE | INSERT | UPDATE} [POSITION номер]
AS <тело_триггера>;
где имя_таблицы указывает на таблицу, действия в которой триггер должен отслеживать;
ACTIVE | INACTIVE – определяет активность триггера (триггер можно включать и отключать). По умолчанию считается ACTIVE;
DELETE | INSERT | UPDATE – указывает при каком действии в таблице должен срабатывать триггер;
BEFORE | AFTER – указывает: до или после действия должен срабатывать триггер;
POSITION – определяет порядок срабатывания триггеров, если к одному и тому же событию привязано несколько триггеров (номер – целое число в интервале от 0 до 32767). Триггер с меньшим номером срабатывает раньше.
Например, если для некоторой таблицы T1 были определены следующие триггеры:
CREATE TRIGGER Tr1 FOR T1 BEFORE INSERT POSITION 1 AS ...
CREATE TRIGGER Tr2 FOR T1 BEFORE INSERT POSITION 0 AS ...
CREATE TRIGGER Tr3 FOR T1 BEFORE INSERT POSITION 9 AS ...
CREATE TRIGGER Tr4 FOR T1 AFTER INSERT POSITION 4 AS ...
CREATE TRIGGER Tr5 FOR T1 AFTER INSERT POSITION 2 AS ...
то порядок их срабатывания будет следующим: Tr2, Tr1, Tr3, Tr5, Tr4.
Тело триггера определяется точно также, как и тело хранимой процедуры. Единственным отличием является возможность обращения к значениям столбцов, которые имели место до изменения строки и после её изменения. С этой целью к именам столбцов добавляются префиксы OLD и NEW.
Префикс OLD указывает на предыдущее или текущее значение столбца в строке, которая изменяется или удаляется. Для добавляемых строк префикс OLD не используется.
Префикс NEW указывает на новое значение столбца в изменяемой или добавляемой строке. Для удаляемых строк он не используется.
Замечание 1. Префиксы столбцов могут использоваться в любых операторах тела триггера, в том числе и в SELECT.
Замечание 2. Новые значения, присваиваемые столбцам, могут быть изменены только до выполнения операции. То есть, например, если триггер AFTER INSERT попытается изменить значение NEW.имя_столбца, то это действие не будет иметь результата. Кроме того, фактические значения столбцов не изменяются до тех пор, пока не будет закончена операция вставки или изменения строки. Поэтому триггеры, привязанные к одному и тому же событию, не могут видеть изменения вносимые друг другом.
Замечание 3. Если триггер выполняет действие, которое приводит к тому, что он срабатывает снова, то образуется бесконечный цикл. Поэтому необходимо обеспечить, чтобы действия триггера не приводили к его срабатыванию вновь, даже неявным образом. Например, бесконечный цикл будет иметь место, если триггер, срабатывающий по INSERT в некоторой таблице, будет пытаться добавить запись в ту же таблицу.
Замечание 4. В триггерах допускается вызов хранимых процедур, генерация исключительных ситуаций и их обработка.
Изменение и удаление триггера
Для изменения триггера используется оператор ALTER TRIGGER. Этот оператор может изменять:
1) только заголовок триггера, включая событие, к которому он привязан, и его активность;
2) только тело триггера;
3) и тело и заголовок.
По своему формату оператор ALTER TRIGGER полностью совпадает с оператором CREATE TRIGGER за одним исключением: в нем отсутствует предложение FOR (то есть триггер, созданный для одной таблицы, нельзя затем перепривязать к другой таблице).
Если оператор ALTER TRIGGER используется для изменения заголовка, то в нем перечисляются только те параметры, которые должны быть изменены. Например, для временного отключения некоторого триггера Tr1 следует использовать такой оператор:
ALTER TRIGGER Tr1 INACTIVE;
Если изменяется момент срабатывания триггера (BEFORE, AFTER), то должно быть указано и действие (DELETE, INSERT, UPDATE).
Для изменения только тела триггера в операторе ALTER TRIGGER ничего не должно быть между именем триггера и словом AS.
Для удаления триггера используется оператор:
DROP TRIGGER имя_триггера;
Не может быть удален триггер, используемый в текущий момент времени.
Использование триггеров
Триггеры представляют собой очень мощный инструмент серверных БД. Они используются для выполнения разнообразных действий. Рассмотрим некоторые из них.
Поддержание ссылочной целостности. Например, для каскадного изменения и удаления строк в дочерней таблице, для родительской таблицы должны быть определены два триггера следующего вида:
CREATE TRIGGER имя_триггера FOR имя_родит_таблицы
ACTIVE BEFORE UPDATE AS
BEGIN
IF (OLD.поле_ПК_родит_таблицы <> NEW.поле_ПК_родит_таблицы) THEN
UPDATE имя_дочерн_таблицы
SET поле_ВК_дочерн_таблицы = NEW.поле_ПК_родит_таблицы
WHERE поле_ВК_дочерн_таблицы = OLD.поле_ПК_родит_таблицы;
END
CREATE TRIGGER имя_триггера FOR имя_родит_таблицы
ACTIVE BEFORE DELETE AS
BEGIN
DELETE FROM имя_дочерн_таблицы
WHERE поле_ВК_дочерн_таблицы = OLD.поле_ПК_родит_таблицы;
END
Внесение взаимосвязанных изменений. Например, добавление проводки должно сопровождаться соответствующими изменениями в таблице наличия (здесь для простоты рассмотрен вариант передачи предметов между подотчетными лицами):
CREATE EXCEPTION BadProvodka ’Недопустимая проводка’;
CREATE TRIGGER BI_Provodki FOR Provodki
ACTIVE BEFORE INSERT AS
DECLARE VARIABLE KolvoRash DOUBLE PRECISION;
DECLARE VARIABLE KolvoPrih DOUBLE PRECISION;
BEGIN
SELECT Kolvo FROM Nalichie
WHERE Predmet=NEW.Predmet AND Lico=NEW.Rashod
INTO KolvoRash;
SELECT Kolvo FROM Nalichie
WHERE Predmet=NEW.Predmet AND Lico=NEW.Prihod
INTO KolvoPrih;
IF (KolvoRash >= NEW.Kolvo) THEN
BEGIN
UPDATE Nalichie
SET Kolvo = Kolvo – NEW.Kolvo
WHERE Predmet=NEW.Predmet AND Lico=NEW.Rashod;
IF (KolvoPrih IS NULL) THEN
INSERT INTO Nalichie(Lico, Predmet, Kolvo)
VALUES(NEW.Prihod, NEW.Predmet, NEW.Kolvo);
ELSE
UPDATE Nalichie
SET Kolvo = Kolvo + NEW.Kolvo
WHERE Predmet=NEW.Predmet AND Lico=NEW.Prihod;
END
ELSE
EXCEPTION BadProvodka;
END
Ведение лог-таблиц, в которых отражаются изменения, вносимые в БД. Например, для таблицы лиц создадим лог-таблицу следующего формата:
CREATE TABLE LicaLog(
Data DATE,
Deistvie VARCHAR(3),
UserName VARCHAR(20),
OldName VARCHAR(30),
NewName VARCHAR(30));
в которую будем заносить информацию об операциях над таблицей лиц с указанием даты и времени операции, ее характера, имени пользователя, выполнившего операцию, старого и нового значения измененных полей.
Для таблицы лиц создадим три триггера примерно такого содержания:
. . .
AFTER INSERT
. . .
INSERT INTO LicaLog(Data, Deistvie, UserName, OldName, NewName)
VALUES(’NOW’, ’INS’, USER, ’’, NEW.Name);
. . .
. . .
AFTER UPDATE
. . .
INSERT INTO LicaLog(Data, Deistvie, UserName, OldName, NewName)
VALUES(’NOW’, ’UPD’, USER, OLD.Name, NEW.Name);
. . .
. . .
AFTER DELETE
. . .
INSERT INTO LicaLog(Data, Deistvie, UserName, OldName, NewName)
VALUES(’NOW’, ’DEL’, USER, OLD.Name, ’’);
. . .
Автоматическое преобразование данных перед их записью в БД. Например, преобразование вводимого текста к верхнему регистру.
Реализация бизнес-правил. Например, для поставщиков и направлений списания не должно указываться подразделение (пусть условный номер типа лица для поставщиков равен 1, а для направлений списания – 3):
CREATE TRIGGER BI_Lica FOR Lica ACTIVE BEFORE INSERT AS
BEGIN
IF (NEW.Tip=1 OR NEW.Tip=3) THEN NEW.Podr=NULL;
END
3.3.6.Дополнительная информация об операторе SELECT
Определение агрегированных значений для групп записей
Иногда возникает необходимость подсчета агрегированных значений (сумма, среднее, минимум …) не по всему набору данных, а по некоторым его группам, характеризуемым одинаковыми значениями определенных полей. Например, суммарное наличие по подотчетным лицам или по подразделениям.
С этой целью в операторе SELECT используется предложение:
GROUP BY столбец_1[, столбец_2...]
Замечание. При использовании предложения GROUP BY один из столбцов результирующего набора данных обязательно должен быть представлен агрегатной функцией.
Например, суммарное наличие по подотчетным лицам:
SELECT L.Name, SUM(N.Kolvo*P.Cena)
FROM Nalichie N, Lica L, Predmety P
WHERE L.NLic=N.Lico AND P.NPredm=N.Predmet
GROUP BY L.Name;
или количество проводок, сгруппированных по предметам и датам:
SELECT P.Name, V.Data, COUNT(V.*)
FROM Provodki V, Predmety P
WHERE P.NPredm=V.Predmet
GROUP BY P.Name, V.Data;
Замечание. В предложении SELECT могут присутствовать в чистом виде (т.е. не в качестве аргументов агрегатных функций) только те столбцы, которые присутствуют в предложении GROUP BY.
Если в результирующий набор данных необходимо включать агрегированное значение не по всем группам, а только по тем из них, которые удовлетворяют некоторому условию, то предложение GROUP BY дополняется предложением:
HAVING <условие_поиска>
где условие_поиска формируется по тем же правилам, что и предложение WHERE, за одним исключением: здесь, в отличие от WHERE, можно использовать агрегатные функции.
Например, список подотчетных лиц, имеющих наличие общей стоимостью менее 1000 руб., с указанием количества наименований, значащихся за ними предметов:
SELECT L.Name, COUNT(N.*)
FROM Nalichie N, Lica L, Predmety P
WHERE L.NLic=N.Lico AND P.NPredm=N.Predmet
AND N.Kolvo>0
GROUP BY L.Name HAVING SUM(N.Kolvo*P.Cena)<1000;
Замечание. В предложении HAVING, также как и в предложении SELECT могут использоваться непосредственно только столбцы, перечисленные в GROUP BY, а все остальные столбцы могут упоминаться только в качестве аргументов агрегатных функций.
Сложные условия поиска в предложении WHERE
В условии поиска можно указать, что некоторое выражение должно иметь значение в определенном интервале:
<выражение> [NOT] BETWEEN <значение_1> AND <значение_2>
Например, список предметов с ценой от 1000 до 5000 рублей:
SELECT Name, Cena FROM Predmety
WHERE Cena BETWEEN 1000 AND 5000;
Можно указать на вхождение значения выражения в некоторый список значений:
<выражение> [NOT] IN (<значение_1>[, <значение_2>...])
Например, список проводок за 1, 2 и 3 декабря 2012г.:
SELECT * FROM Provodki
WHERE Data IN (’1-DEC-2012’, ’2-DEC-2012’, ’3-DEC-2012’);
Для символьных выражений можно потребовать, чтобы его значение начиналось с определенной подстроки:
<выражение> [NOT] STARTING [WITH] <подстрока>
или включало в себя подстроку:
<выражение> [NOT] CONTAINING <подстрока>
Часто для сравнения символьных значений полезно бывает использовать функцию приведения символов к верхнему регистру UPPER. Например, отобрать все фамилии вида: Иванов, ИВАНОВ, Иванченко, ... :
SELECT Name FROM Lica WHERE UPPER(Name) STARTING ’ИВАН’;
Вложенные запросы
При необходимости операторы SELECT могут быть вложенными. Например, дать информацию о наиболее дорогостоящем предмете:
SELECT Name, Cena FROM Predmety
WHERE Cena=(SELECT MAX(Cena) FROM Predmety);
Здесь необходим вложенный SELECT, так как в предложении WHERE нельзя использовать агрегатную функцию.
Замечание. Если вложенный запрос используется в сравнении со знаком равенства, то необходимо следить, чтобы он возвращал единственное значение (в противном случае будет сгенерирована ошибка). Если же нет уверенности в том, что будет возвращено единственное значение, то вместо знака равенства следует использовать ключевое слово включения в множество IN. Например, список проводок в отношении самого дорогостоящего предмета (следует учесть, что несколько различных предметов могут иметь одну и ту же максимальную цену):
SELECT * FROM Provodki WHERE Predmet IN (
SELECT NPredm FROM Predmety WHERE Cena=(
SELECT MAX(Cena) FROM Predmety));
Иногда для вложенного запроса важно знать, что существует хотя бы одна соответствующая запись, а количество этих записей не важно. В этом случае используется выражение:
[NOT] EXISTS (<запрос>)
Например, выбрать подотчетных лиц, у которых нет ни одной записи в таблице наличия (пусть условный номер типа лица для подотчетных лиц равен 2):
SELECT Name FROM Lica L WHERE Tip=2 AND NOT EXISTS(
SELECT * FROM Nalichie N WHERE N.Lico=L.NLic);
Если необходимо отобрать записи, для которых вложенный запрос возвращает одну и только одну строку, то используется выражение:
SINGULAR(<запрос>)
Например, выбрать подотчетные лица, у которых имеется единственная запись в таблице наличия:
SELECT Name FROM Lica L WHERE Tip=2 AND SINGULAR(
SELECT * FROM Nalichie N WHERE N.Lico=L.NLic);
В операторе SELECT имеется возможность сравнить значение некоторого выражения со всеми значениями, возвращаемыми вложенным запросом:
<выражение> <оператор> {ALL|SOME|ANY} (<запрос>)
В случае ALL сравнение дает истину, если значение выражения находится в отношении, указываемом оператором, со всеми значениями, возвращенными вложенным запросом. Для SOME (ANY – синоним) достаточно выполнения отношения хотя бы для одного значения, возвращенного вложенным запросом.
Например, выбрать подотчетные лица, у которых нет предметов, с ценой свыше тысячи рублей:
SELECT Name FROM Lica L WHERE 1000 > ALL(
SELECT P.Cena FROM Predmety P, Nalichie N
WHERE N.Lico=L.NLic AND P.NPredm=N.Predmet);
или выбрать лиц, у которых есть предметы с ценой