DML для InterBase/FireBird
Оператор 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
Обратите внима