Лекции по "Систе́ма управле́ния ба́зами да́нных "

Пример 4.16.Вывести список клиентов в алфавитном порядке.

SELECT Клиент.Фамилия, Клиент.Фирма

    FROM Клиент

    ORDER BY Клиент.Фамилия

Пример 4.16. Список клиентов в алфавитном порядке.

Во фразе ORDER BY может быть указано и больше одного элемента. Главный (первый) ключ сортировки определяет общую упорядоченность строк результирующей таблицы. Если во всех строках результирующей таблицы значения главного ключа сортировки являются уникальными, нет необходимости использовать дополнительные ключи сортировки. Однако, если значения главного ключа не уникальны, в результирующей таблице будет присутствовать несколько строк с одним и тем же значением старшего ключа сортировки. В этом случае, возможно, придется упорядочить строки с одним и тем же значением главного ключа по какому-либо дополнительному ключу сортировки.

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

SELECT Клиент.Фирма, Клиент.Фамилия

    FROM Клиент

    ORDER BY Клиент.Фирма, Клиент.Фамилия DESC

Пример 4.17. Список фирм и клиентов. Названия фирм в алфавитном порядке, имена клиентов в каждой фирме в обратном порядке.

 

Лекция 3. Триггеры и хранимые процедуры

 

Хранимые процедуры и триггеры, о которых уже неоднократно упоминалось в предыдущих лекциях, являются одними из самых мощных средств InterBase для реализации бизнес-логики на стороне сервера. Использование этих инструментов приводит к:

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

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

Хранимые процедуры (Stored Procedures)

Каждая хранимая процедура является самостоятельной программой, скомпилированной во внутренний двоичный язык InterBase, и является частью метаданных (данные о данных) базы данных. Другими словами, хранимые процедуры являются частью базы данных и хранятся вместе с таблицами, индексами и другими объектами БД. Хранимую процедуру можно вызвать из клиентского приложения, из другой хранимой процедуры или триггера. Хранимые процедуры могут быть двух типов:

• выполняемые процедуры, которые либо вообще не возвращают результатов, а только выполняют какие-то действия, либо возвращают только один набор выходных параметров. Такие процедуры вызываются командой EXECUTE PROCEDURE.
• процедуры выборки, которые предназначены для создания многострочных выходных данных, такие процедуры вызываются командой SELECT и используются, как виртуальные таблицы.

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

SET TERM <новый_терминатор><старый_терминатор>

 

CREATE PROCEDURE Имя_Процедуры

[(<входной_параметр><тип_данных>

[,<входной_параметр><тип_данных> […]])]

[RETURNS

(<выходной_параметр><тип_данных>

[,<выходной_параметр><тип_данных> […]])]

AS

<тело_процедуры>

 

<тело_процедуры> =

[DECLARE [VARIABLE] <переменная><тип_данных>; […]]

BEGIN

<составной оператор>

END<терминатор>

 

SET TERM <старый_терминатор><новый_терминатор>

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

Терминаторы

Терминаторами называются символы окончания SQL оператора. Установка терминаторов не относится напрямую к синтаксису хранимых процедур или триггеров, однако попытка создания процедуры без переопределения терминатора, скорее всего, приведет к ошибке. Дело в том, что внутри создаваемой процедуры неоднократно может встречаться символ ";", который по умолчанию является символом конца оператора в языкеSQL. В этом случае утилитаIBConsoleрешит, что оператор закончен, и попытается его выполнить. Но процедура еще не будет прочитана до конца, что и приведет к ошибке. Выход: переопределить терминатор. Делается это довольно просто:

SET TERM <новый_терминатор><старый_терминатор>.

В качестве нового символа окончания вы можете использовать любой редкий символ, например "^" или "&". Затем в теле процедуры может сколько угодно раз встречаться символ ";",SQLпри этом не воспримет его как окончание оператора. ЗавершающуюENDпроцедуры следует закрыть установленным вами терминатором, в этом случае процедура будет прочитанаIBConsoleдо конца и выполнена без ошибок. А напоследок вы вновь переопределяете терминатор, устанавливая стандартный символ ";". Например:

SETTERM ^;

CREATEPROCEDURE ……^

SETTERM ;^

Здесь, и далее в лекции показаны примеры различных фрагментов процедуры, а не всей процедуры в целом. Поэтому выполнять данные примеры вInteractive SQLне нужно, это все равно приведет к ошибке. В конце лекции будут представлены три примера рабочих процедур.

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

Заголовок

Заголовок процедуры состоит из следующих разделов:

1. Имя процедуры- обязательный элемент. Имя должно быть уникальным во всей базе данных. Пример:

CREATE PROCEDURE Proc1

2. Входные параметры- необязательный элемент. Входные параметры, как и в процедурахDelphi, служат для передачи в процедуру каких-то значений из внешнего приложения, другой процедуры или триггера. При этом типы данных этих параметров могут быть любыми, определенными вSQL, кроме массивов. Параметры объявляются в виде списка "параметр тип", несколько параметров разделяются запятой. Имена входных параметров процедуры не обязаны соответствовать именам параметров вызывающего приложения, но типы данных должны совпадать. Пример:
3. CREATE PROCEDURE Proc2

(perem1 Integer, perem2 Float, perem3 Date)

4. Выходные параметры- необязательный элемент. Выходные параметры служат для возврата в вызывающее приложение списка результирующих значений. Объявление выходных параметров (если они есть), начинается ключевым словомRETURNS, после которого в скобках параметры перечисляются в виде списка "параметр тип". Пример:
5. CREATE PROCEDURE Proc3
6. (vhod_param1 Integer)

RETURNS (vihod_param1 Double, vihod_param2 Varchar(10))

7. Ключевое слово AS- обязательный элемент, указывающий на окончание заголовка процедуры. Пример:
8. CREATE PROCEDURE Proc4
9. RETURNS (param char(50))

AS

Тело процедуры

Хранимые процедуры, как и процедуры в Delphi, могут иметь локальные переменные, или не иметь их. Если локальных переменных нет, тело процедуры представляет собой только составной оператор, заключенный в скобки BEGIN … END. Причем эти скобки обязательны, даже если в процедуре всего только один оператор.

Если же локальные переменные имеются, то вначале их нужно объявить после ключевых слов DECLARE VARIABLE, после чего следует составной оператор. При этом следует помнить, что объявление каждой переменной является отдельным оператором и должно завершаться точкой с запятой. Пример:

SET TERM ^;

 

CREATE PROCEDURE MyProc (param1 Integer)

RETURNS (param2 Varchar(20), param3 Double Precision)

AS

DECLARE VARIABLE perem1 Varchar(10);

DECLARE VARIABLE perem2 Date;

DECLARE VARIABLE perem3 Integer;

BEGIN

   … 

END^

 

SET TERM ;^

В приведенном примере мы вначале переопределяем терминатор, после чего приступаем непосредственно к описанию процедуры. В процедуре имеется один входящий, и два выходящих параметра, а также объявлены три локальные переменные. Заметим, что ключевое слово DECLARE обязательно, а вот слово VARIABLE можно опустить. Если вы желаете, чтобы ваша база данных была совместима с ранними версиями InterBase, то VARIABLE лучше указывать. Завершается процедура новым терминатором "^", после чего мы переопределяем его на стандартный символ ";".

Блок кода процедуры

Блок кода процедуры начинается ключевым словом BEGIN, и оканчивается ключевым словом END. Блок кода может состоять из одного или нескольких операторов, а также содержать вложенные блоки кода BEGIN … END.

В блоке кода процедуры могут встречаться:

• операторы присваивания, которые присваивают значения локальным переменным, входным или выходным параметрам (в отличие от оператора ":=" вDelphi, в SQL это просто знак равно "=");
• операторы SELECT для выборки данных из таблиц. Результаты выборки могут присваиваться переменным или параметрам;
• циклы, такие как FOR и WHILE;
• управляющие структуры IF;
• операторы EXECUTE PROCEDURE для вызова другой хранимой процедуры;
• комментарии, заключенные в скобки/* … */;
• символы сравнения>=,>,<=,<,<>,=,!<(не меньше),!>(не больше),!=(не равно);
• команды модификации таблиц, такие как INSERT, UPDATE или DELETE;
• и др.

Важно! Если в блоке кода локальные переменные используются внутри SQL-оператора (например, SELECT), перед их именами следует ставить двоеточие. В других операторах этого делать не нужно.

Оператор присваивания

Оператор присваивания имеет вид

<переменная/выходной  параметр> = <выражение>

и служит для присвоения локальной переменной или выходному параметру какого-либо значения. Здесь есть несколько правил. Во-первых, переменная или выходной параметр должны иметь совместимый тип данных с выражением. Во-вторых, перед именем переменной или выходного параметра двоеточие не ставится. В-третьих, в InterBase выражение может быть либо строковым, либо арифметическим. В первом случае выражение может содержать оператор конкатенации (объединения) строк "||", во втором случае - четыре арифметических оператора+,-,*и/. Помимо этого, выражение может содержать значения однотипных столбцов таблиц, или результат работы другой процедуры.

Условный оператор IF… THEN … ELSE

В отличие от Delphi, в InterBase условное выражение оператора IF обязательно нужно помещать в круглые скобки, кроме того, перед ELSE точка с запятой не опускается:

IF (<условное_выражение>) THEN <оператор_1>; [ELSE <оператор_2>]

 

Как обычно, если <условное_выражение> возвращает истину, то выполняется <оператор_1>,

в противном случае выполняется <оператор_2>. Пример:

 

IF (KOLVO>5 AND KOLVO<10) THEN …;

Заметьте, что приоритет операций сравнения выше, чем логических операций AND, OR и NOT, поэтому при использовании более чем одного условия нет необходимости заключать каждое из них в отдельные скобки. Альтернативный вариант ELSE не является обязательным и может быть опущен.

Оператор SELECT

Хранимая процедура может содержать оператор SELECT для вывода одного или нескольких значений и присвоения этих значений локальным переменным или выходным параметрам. Пример:

SELECT * FROM TABLE_FIRMA

INTO :fam, :imya, :otch

Таблица TABLE_FIRMA содержит три текстовых поля, содержащие фамилию, имя и отчество сотрудника. В примере берется первая запись таблицы, и значения ее полей присваиваются локальным переменным (или выходным параметрам)fam,imyaиotch. Однако более типичным является применение этого оператора с условием выборки, возвращающим лишь одно значение:

SELECT MAX(KOLVO) FROM SKLAD

INTO :p_kolvo

Цикл FOR SELECT и SUSPEND

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