Функции – псевдополя

Функции преобразования

HEX (<целое выражение>) - преобразует целое, заданное в качестве аргумента, в строку, представляющую шестнадцатеричную запись данного целого;

ROUND (<числовое выражение, точность>) - округляет с указанной точностью числовое выражение, заданное в качестве аргумента; точность определяет степень одной десятой, до которой надо округлять.

TRUNC (<числовое выражение, точность>) - обрезает незначащие цифры у заданного в качестве аргумента выражения, причем точность определяет смещение влево от запятой последней значащей цифры;

Математические функции:

ACOS(<числовое выражение>) - арккосинус от заданного значения; ASIN(<числовое выражение>) - арксинус от заданного значения;

ATAN (<числовое выражение>) - арктангенс от заданного значения;

COS(<числовое выражение>) - косинус от заданного числа радиан;
SIN(<числовое выражение>) - синус от заданного числа радиан;

TAN (<числовое выражение>) - тангенс от заданного числа радиан;
ABS (<числовое выражение>) - абсолютное значение;

EXP (<степень>) - вычисление заданной экспоненты;

LOGN (<выражение>) - вычисление натурального логарифма;
LOG10 (<выражение>) - вычисление десятичного логарифма;
MOD (<делимое>, <делитель>) - остаток от деления;

POW (<значение>, <степень>) - возведение в степень;

ROOT (<значение>, <степень>) - вычисление корня заданной степени;
SQRT (<числовое выражение>) - квадратный корень;

Функции – псевдополя

CURRENT <диапазон> - возвращает точный момент времени (тип DATETIME), когда происхoдит обработка данного запроса; параметр <диапазон> задает (как и при описании типа DATETIME) требуемое точность - от года до тысячных секунды (например, CURRENT DAY TO SECOND);

TODAY - возвращает дату (тип DATE) выполнения даного запроса;

USER - возвращает имя пользователя (как он зарегестрировался в операционной системе), который выполняет данный запрос.

Следует напомнить про специальное значение NULL - "не определено". Если это значение встретится в выражении, то и все выражение будет иметь значение NULL. Даже умножив нуль на NULL, Вы все равно получите NULL. Конкатенация любой строки с неопределенной строкой (то есть строкой, имеющей значение NULL) тоже в результате даст NULL.

В выражениях можно использовать и скобки. С их помощью Вы имеете возможность управлять порядком вычисления внутри выражения.

Агрегатные функции

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

SELECT MAX(price) FROM items

В этом запросе будут просмотрены все записи из таблицы items и будет определено максимальное значение среди всех полей price.

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

SELECT COUNT(*) FROM items WHERE name="джинсы"

А если мы хотим получить пару значений - минимальная и максимальная цены на кеды, то это достигается следующим запросом:

SELECT MIN(price), MAX(price) FROM items WHERE name="кеды"

Некоторые агрегатные функции перечислены в таблице:

AVG(<имя поля>) - среднее по всем значениям данного поля;
COUNT(*) - количество записей;
MAX(<имя поля>) - максимум из всех значений данного поля;
MIN(<имя поля>) - минимум по всем значениям данного поля;
SUM(<имя поля>) - сумма всех значений данного поля.

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

SELECT name, COUNT(*), MAX(price) FROM items GROUP BY name

Полученные результаты могли бы быть, например, такими:

кеды 2 3000.00
джинсы 4 46000.00
рога 1 999999.85

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

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

SELECT name, COUNT(*), MAX(price) FROM items
GROUP BY name HAVING COUNT(*) > 1

и получим следующие результаты:

кеды 2 3000.00
джинсы 4 46000.00

5.5.4. Логические выражения в условии SQL-операторов

Когда выше обсуждались SQL-операторы и, в частности, раздел WHERE (условие), то были перечислены лишь некоторые возможные способы задания условия на выбираемые записи. В частности, были упомянуты операции сравнения (больше, меньше и т.д.) и логические связки (и, или, не). В условии поиска (в разделе WHERE) операторов SELECT, UPDATE и DELETE можно использовать и более интересные логические выражения.

Сопоставление текстовых полей с образцом

Для сравнения текстовых строк с образцом существуют логические операции LIKE и MATCHES. Операция LIKE обладает несколько меньшими возможностями по сравнению с MATCHES, поэтому рассматриваться не будет. Формат операции MATCHES:

<имя поля> MATCHES <образец>

Образец для оператора MATCHES является текстовой строкой и строится по следующим правилам:

· специальный символ '*' соответсвует произвольной последовательности из 0 или более символов;

· специальный символ '?' соответсвует одному произвольному символу;

· символы внутри квадратных скобок ([...]) позволяют задавать удовлетворяющие образцу символы (например, [абв] или [аеиуюыя]) диапазон символов через дефис (например, [А-Я] или [0-9]), или с помощью символа '^' указывать те символы, которые не удовлетворяют образцу (например, [^абв]);

· специальный символ '\' отменяет значение специального символа, указанного за '\', например '\*' означает именно символ '*', а не произвольную последовательность символов;

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

Например, по следующему запросу

SELECT person_id FROM persons WHERE lname MATCHES "[Бб]ендер"

будут выбраны номера записей (значения поля person_id) для лиц с фамилиями "Бендер" и "бендер".

Если нам требуется отобрать все записи о товарах из таблицы items, в названии которых присутствует слово "грибы" (например, "Соленые грибы", "Грибы по-французски", "Грибы-мухоморы маринованные"), то такую задачу решает следующий запрос:

SELECT item_id FROM items WHERE name MATCHES "*[Гг]рибы*"

Операцию MATCHES, как и любую другую, можно использовать вместе с другими логическими операциями путем использования логических связок OR/NOT/AND и скобок:

SELECT item_id FROM items
WHERE (name MATCHES "теннис") AND price > 20.00

Проверка на вхождение в множество

Но самая, пожалуй, мощная логическая операция, которую можно использовать в условии для операторов SELECT, UPDATE и DELETE ­это операция IN. Эта логическая операция возвращает истину, когда значение слева от слова IN входит в множество значений, указанное справа от слова IN. Множество возможный значений может быть указано явно - через запятую в скобках, а может формроваться другим оператором SELECT:

<выражение> IN (<значение 1>, <значение 2>,....)

<выражение> IN (<оператор SELECT>)

Например, если мы хотим получить адреса фирм "АО Рога и Копыта" и "ТОО Добро пожаловать", то должны выполнить следующий запрос

SELECT name, address FROM companies
WHERE name IN ("АО Рога и Копыта", "ТОО Добро пожаловать")

Очевидно, последний запрос можно сформулировать и с помощью операций "=" и "OR", но во многих случаях использование операции IN более наглядно и компактно.

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

SELECT name, address FROM companies WHERE company_id IN
(SELECT company FROM items WHERE name = "джинсы")

В запросах подобного рода во внутренний оператор SELECT можно добавить слово UNIQUE - это позволит повысить скорость обработки запроса:

SELECT name, address FROM companies WHERE company_id IN
(SELECT UNIQUE company FROM items WHERE name = "джинсы")

Если же требуется найти все фирмы, которые еще не поставляют нам кеды, то такой запрос получается небольшой модификацией предыдущего - заменой "джинсы" на "кеды" и добавлении операции NOT:

SELECT name, address FROM companies WHERE company_id NOT IN (SELECT company FROM items WHERE name = "кеды")

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

DELETE FROM companies WHERE company_id NOT IN
(SELECT company FROM items).

А так как у нас есть еще таблица persons с информацией о работниках фирм, то для сохранения логической целостности базы данных, надо провести еще и соответсвующие удаления из этой таблицы - вместе с информацией о фирме надо удалять и информацию о ее работниках и, более того, эти два удаления надо объединить в транзакцию. Поэтому, правильная последовательность действий по удалению из базы информации о фирмах, которые ничего не производят и не поставляют, будет выглядеть так:

BEGIN WORK
DELETE FROM companies WHERE company_id NOT IN
(SELECT company FROM items)
DELETE FROM persons WHERE company NOT IN (SELECT company_id FROM companies)
COMMIT WORK

Если известно, что оператор SELECT возвращает одну запись, то можно вместо слова IN использовать проверку на равенство. Например, если мы хотим получить список самых дорогих товаров, а именно их названия и цену (цена будет для всех одна - максимальная), то надо выплнить запрос:

SELECT name, price FROM items WHERE price = (SELECT MAX(price) FROM items)

Проверка на существование выборки

С помощью логической операции EXISTS можно проверить, возвращает ли тот или иной SELECT-оператор какие-то значения. Соответственно, можно в условии определять только те записи, для которых существует (или не существует) какая-то информация. Формат логической операции EXISTS:

EXISTS (<оператор SELECT>)

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

SELECT name, address FROM companies WHERE
NOT EXISTS (SELECT company FROM items
WHERE items.company=companies.company_id)

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

Сравнивая операцию EXISTS с операцией IN, можно заметить. что одни и те же запросы можно выразить с помощью разных средств (а именно, EXISTS или IN). Какое из средств выбрать, должно определяться логикой задачи и эффективностью исполнения. Последнее, в свою очередь, зависит от реализации и может принципиально различаться для SQL-серверов разных фирм.

Условия "Существует" и "Для всех"

Всем, кто когда-то более или менее серьезно, занимался математикой, известны кванторы "Существует" и "Для всех". Аналогичные операции введены и в SQL. Эти операции должны использоваться после операции сравнения (больше, меньше, равно и т.д.), а в качестве правого операнда должен задаваться оператор SELECT:

<выражение> <операция сравнения> ALL (<оператор SELECT>)
<выражение> <операция сравнения> ANY (<оператор SELECT>)

Соответсвенно, операция ALL возвращает значение "истина", если операция сравнения истинна для каждого значения, возвращаемого оператором SELECT, а операция ANY - хотя бы для одного. Если оператор SELECT не вернул ни одного знчения, то операция ALL возвращает истину, а ANY - ложь. Вместо ключевого слова ANY можно использовать слоово SOME.

Например, если мы хотим определить фирму-производителя самых дешевых кед, то запрос с использованием операции ALL будет выглядеть следующим образом:

SELECT company_id, name FROM companies WHERE
EXISTS (SELECT company FROM items
WHERE companies.company_id = items.company
AND items.name = "кеды"
AND items.price <= ALL
(SELECT price FROM items WHERE name = "кеды")
)

Тот же самый запрос мог быть сформулирован и с помощью агрегатной функции MIN:

SELECT company_id, name FROM companies WHERE
EXISTS (SELECT company FROM items
WHERE companies.company_id = items.company
AND items.name = "кеды"
AND items.price =
(SELECT MIN(price) FROM items WHERE name = "кеды")
)

5.5.5. Слияние двух выборок

Результаты работы двух или более операторов SELECT могут быть объединены в одну выборку с помощью операторов UNION или UNION ALL. Оператор UNION, помещенный между двумя операторами SELECT делает из двух выборок одну, причем повторяющиеся записи отсутствуют в результирующей выборке. Оператор UNION ALL не убирает повторяющиеся записи. Синтаксис:

<оператор SELECT>
UNION [ALL]
<оператор SELECT>
UNION [ALL]
<оператор SELECT>
................

Например, если бы мы имели вместо одной таблицы companies две ­фирмы-поставщики (suppliers) и фирмы-производители (manufacters), и хотели бы получить выборку с названиями всех фирм, с которыми мы работаем, то требуемый запрос формулируется так:

SELECT name FROM suppliers
UNION
SELECT name FROM manufactures

С помощью операторов UNION и UNION ALL вы можете объединять выборки, возвращающие самые разные поля из разных таблиц. Но при этом все операторы SELECT должны возвращать записи с одинаковым количеством полей и соответсвующие поля во всех операторах SELECT должны иметь приводимые типы данных.

5.5.6. Сортировка выборки

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

Для упорядочивания выборки в оператор SELECT добавляется раздел ORDER BY. Этот раздел указывается псле условия (раздела WHERE). После слов ORDER BY перечисляются имена полей, по которым надо производить сортировку. Поля, по которым надо сортировать, обязательно должны быть в списке выбираемых полей (присутствовать в разделе SELECT).

Например, если мы хотим получить в алфавитном порядке список фирм, с которыми мы работаем, то надо выполнить запрос:

SELECT name FROM companies ORDER BY name

Для упорядочивания в обратном порядке, например, если мы хотим получить список названий и цен на товары в порядке уменьшения цен, то после имени поля в разделе GROUP BY надо поставить ключевое слово DESC:

SELECT name, price FROM items ORDER BY price DESC

Если в разделе ORDER BY указано несколько полей, то вначале производится сортировка по первому полю, затем записи, у которых первые поля совпадают, сортируются по второму полю и т.д.

Если мы желаем отсортировать по некоторому значению, которое не является полем, а, например, вычисляется по значению поля, то для этого в разделе ORDER BY надо указывать не имена полей и не выражения, а номер выражения (начиная с 1) в разделе SELECT. Например, у нас есть таблица result с результатами некоторого физического эксперимента. Пусть в этой таблице есть поле angle со значением некоторого угла, и height - высота чего-то там для заданного угла. Если мы хотим получить выборку, содержащую значения полей angle, height и sin(angle), при этом отсортировать эту выборку в соответствии с синусом угла, то надо выполнить запрос

SELECT angle, height, SIN(angle) FROM results ORDER BY 3

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

5.5.7. Вставка в таблицу нескольких строк одновременно

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

INSERT INTO <имя таблицы> (имя поля1, имя поля2,...) <оператор SELECT>

Например, мы хотим построить таблицы, в которой перечисленв все фирмы, ничего не поставляющие. Пусть такая таблица называется strange_companies и имеет структуру, идентичную таблице companies. Тогда соответствующий оператор INSERT будет выглядеть так:

INSERT INTO strange_companies(name, address)
SELECT name, address FROM companies
WHERE company_id NOT IN
(SELECT UNIQUE company FROM items)

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

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

SELECT name, address FROM companies
WHERE company_id NOT IN (SELECT UNIQUE company FROM items)
INTO TEMP strange_companies

Оператор SELECT... INTO TEMP автоматически создает таблицу, причем эта таблица имеет столько полей, сколько выражений указано в разделе SELECT. Если в разделе SELECT выражение есть просто значение поля (указно имя поля), то формируемая временная таблица будет иметь поле такого же типа с таким же именем. Если в разделе SELECT стоит составное выражение, то имя поля формируется автоматически по некоторым правилам (здесь они не рассматриваются). В нашем примере, временная таблица strange_companies будет иметь два поля - name и address. Типы этих полей будут идентичны типам полей name и address таблицы companies, то есть CHAR(40) и CHAR(60) соответственно.

Поммо повышения производительности, использование временных таблиц во многих случаях позволяет упростить программу.

Кроме оператора SELECT... INTO TEMP временные таблицы могут быть созданы с помощью оператора создания таблицы - в этом случае он записывается как CREATE TEMP TABLE. Например:

CREATE TEMP TABLE strange_companies (
name CHAR(40),
address CHAR(60)
)

5.6. Использование SQL в языках программирования

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

SQL обычно встраивается в другие языки. Например, существуют продукты Informix ESQL/C, /COBOL, /Ada, /FORTRAN, которые представляют собой препроцессор и библиотеки соответственно для С/С++, COBOL, Ada и FORTRAN. На ESQL/C, например, SQL-оператор, встраиваемый в программу, должен начинаться с символа '$' или со слов 'exec sql’. Пример фрагмента программы на ESQL/C:

$database test;
for (i=0; i++; i<10) {
$insert into my_table values ($i);
$select name into $buf from my_2nd_table where key = i;
printf ("key = %d, name = %s\n", i, buf);
}

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

Другой вариант построения программы, работающий с SQL-сервером - это непосредственное встраивание SQL в язык программирования. Именно по такой схеме построены Informix 4GL и NewEra ­SQL-операторы являются одними из возможных операторов этого языка. Если приведенную выше программу переписать на Informix-4GL, то она будет выглядеть так:

DATABASE test
FOR I=0 TO 9
INSERT INTO my_table VALUES (i)
SELECT name INTO buf FROM my_2nd_table WHERE key = i
DISPLAY "KEY = ", i, " NAME = ", buf
END IF

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

программист (или конечный пользователь) "рисует" форму или отчет. О связи с SQL-сервером позаботится оболочка, которая будет заполнять форму или отчет данными, автоматически посылая SQL-запросы на сервер.

Курсоры

Другой вопрос, который надо решить для того, чтобы можно было реально программировать на SQL - это как обработать набор записей, возвращаемых оператором SELECT. Ведь человеку привычней обрабатывать данные последовательно, ряд за рядом. Конечно, можно было бы реализовать запись всех выбранных значений в массив или список, но это означает, что надо все записи, полученные в результате работы оператора SELECT переслать на компьютер-клиент, а затем переслать обратно сделанные исправления. Но это не очень хорошо по двум причинам. Первое ­перегружается сеть, если клиент и сервер находятся на разных компьютерах. Второе - надо решить задачу согласования изменений и блокировок, чтобы два пользователя не смогли одновременно изменять одну и ту же запись.

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

всегда можно сказать, какой номер от начала выборки какая запись имеет. Например, нам надо выдать на экран имена и фамилии из таблицы persons в алфавитном порядке. Воспользуемся для этого курсором, а уж потом дадим точный синтаксис описания курсора и правила работы с ним (используем Informix 4GL для описания перемнных и оператора вывода):

{ определяем переменные для считывания данных из курсора }
DEFINE ln LIKE persons.lname
DEFINE fn LIKE persons.fname
{ определяем курсор }
DECLARE my_cursor CURSOR FOR
SELECT lname, fname FROM persons ORDER BY lname, fname
{последовательно перебираем все значения из курсора и печатаем их}
FOREACH my_cursor INTO ln, fn
DISPLAY ln, fn
END FOREACH

Результат работы данного программного фрагмента мог бы быть примерно следующим:

Антонов Антон
Антонов Сергей
Бендер Остап
Шапокляк Алексей

В приведенном простом примере три оператора работают с курсором - DECLARE, FOREACH и END FOREACH. С помощью оператора DECLARE мы объявили курсор, то есть связали его с некоторой выборкой из базы. Затем, с помощью цикла FOREACH... END FOREACH [10]) мы перебрали все записи из выборки, соответсвующей курсору и сделали с ними то, что хотели - вывели на экран. С таким же успехом можно было бы вывести эту информацию в отчет или провести статистическую обработку. Причем, в данной выборке мы уже имеем упорядоченность - вначале по фамилии, а для одинаковых фамилий - по имени. Упорядоченность была указана в SELECT-запросе, связанном с данным курсором (раздел ORDER BY).

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

Если при работе с курсором предполагается последовательный доступ к записям из выборки, то есть известно, что каждая запись будет выбрана один раз и в том порядке, который сформирован связанным оператором SELECT, то курсор может быть определен следующим вариантом оператора DECLARE:

DECLARE <имя курсора> CURSOR FOR <SELECT-оператор>

Оператор DECLARE связывает курсор с указанным оператором SELECT. Можно представить себе, что курсор - это некоторая структура данных, содержащая, в частности, позицию текущего ряда в выборке, сформированной оператором SELECT.

Например:

{курсор для перебора названий и адресов фирм}
DECLARE cursor1 CURSOR FOR
SELECT name, address FROM companies ORDER BY name

{курсор для перебора названий товаров и фирм, их производящих}
DECLARE dialog_c CURSOR FOR
SELECT items.name, company.name FROM companies,items
WHERE companies.company_id = items.company ORDER BY items.name

После того, как курсор описан, с ним можно начинать работу. Простейший пример использования курсора был приведен выше ­цикл FOREACH <имя курсора>.... END FOREACH. Внутри этого цикла производится последовательный перебор записей из выборки.

Последовательный курсор хорош для случаев, когда не надо организовывать диалог с пользователем - например, для формирования отчетов. Если же на основе курсора планируется организовать диалог с пользователем, то последовательным курсором здесь не обойтись - человеку привычно просматривать содержимое базы данных в любых направлениях (как вперед, так и назад). Для таких задач используется скроллируемый курсор, который описывается так:

DECLARE <имя курсора> SCROLL CURSOR FOR <SELECT-оператор>

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

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

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

OPEN <имя курсора>

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

FETCH NEXT <имя курсора>
FETCH PREVIOUS <имя курсора>
FETCH FIRST <имя курсора>
FETCH LAST <имя курсора>
FETCH CURRENT <имя курсора>
FETCH RELATIVE <смещение> <имя курсора>
FETCH ABSOLUTE <номер записи> <имя курсора>

Смысл ключевых слов NEXT, PREVIOUS и т.д. и, соответственно, выполняемых ими действий, понятен из их перевода с английского:

· NEXT - перейти на следующую запись в выборке;

· PREVIOUS - перейти на предыдущую запись в выборке;

· FIRST - перейти на первую запись выборки;

· LAST - перейти на последнюю запись выборки;

· CURRENT - никуда не перемещаясь, перечитать текущую запись;

· RELATIVE <смещение> - сместиться от текущей записи на <смещение> записей, причем если смещение больше нуля, то сместиться "вниз" - к последней записи, а если меньше - то вверх, к первой записи;

· ABSOLUTE <номер записи> - перейти на запись, имеющую в данной выборке указанный порядковый номер, причем записи нумеруются с единицы.

После оператора OPEN, кстати, текущей записи нет (текущей записью является несуществующая запись с номером ноль). То есть, сразу после открытия курсора оператор FETCH CURRENT выполнять нельзя.

Слово NEXT в операторе FETCH можно опускать - оно принимается по умолчанию. Вместо ключевого слова PREVIOUS можно использовать синоним PRIOR.

В конкретных языках программирования для задания переменных можно (как и для оператора SELECT) использовать расширение INTO со списком переменных:

... INTO <переменная>, <переменна>...

Например, если в программе на INFORMIX-4GL нам надо прочитать очередную запись в переменные, то соотвествующий оператор будет выглядеть так:

FETCH NEXT my_cursor INTO my_var1, my_var2

Или для программы на INFORMIX ESQL/C:

$fetch next $my_cursor into $my_var1, $my_var2

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

CLOSE <имя курсора>

Если же Вы хотите полностью освободить все ресурсы, связанные с объявленным курсором, то для этого используется оператор

FREE <имя курсора>

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

----------------T-------------
¦ lname ¦ fname ¦
+---------------+-------------+
¦ Антонов ¦ Сергей ¦
¦ Шапокляк ¦ Алексей ¦
¦ Антонов ¦ Антон ¦
¦ Бендер ¦ Остап ¦
L---------------+--------------

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

DECLARE my_scroll_cursor SCROLL CURSOR FOR
SELECT lname, fname FROM persons ORDER BY lname, fname
OPEN my_scroll_cr

После этих операторов сформирован набор записей в следующем порядке (порядок уже определен разделом ORDER BY оператора SELECT):

---------T-----------T----------
¦ No п/п ¦ lname ¦ fname ¦
+--------+-----------+----------+
¦ 1 ¦ Антонов ¦ Антон ¦
¦ 2 ¦ Антонов ¦ Сергей ¦
¦ 3 ¦ Бендер ¦ Остап ¦
¦ 4 ¦ Шапокляк ¦ Алексей ¦
L--------+-----------+-----------

Теперь рассмотрим последовательность операторов FETCH и запись, ими выбираемая (порядок выполнения операторов FETCH, очевидно, существенен):

---------------------T------------------------------------
¦ Оператор FETCH | Выбранная запись No (lname, fname) ¦
+--------------------+------------------------------------+
¦ FETCH NEXT ¦ 1 (Антонов Антон) ¦
¦ FETCH NEXT ¦ 2 (Антонов Сергей) ¦
¦ FETCH CURRENT ¦ 2 (Антонов Сергей) ¦
¦ FETCH NEXT ¦ 3 (Бендер Остап) ¦
¦ FETCH RELATIVE -2 ¦ 1 (Антонов Антон) ¦
¦ FETCH LAST ¦ 4 (Шапокляк Алексей) ¦
¦ FETCH ABSOLUTE 3 ¦ 3 (Бендер Остап) ¦
¦ FETCH PREVIOUS ¦ 2 (Антонов Сергей) ¦
¦ FETCH FIRST ¦ 1 (Антонов Антон) ¦
¦ FETCH RELATIVE 2 ¦ 3 (Бендер Остап) ¦
L--------------------+-------------------------------------

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

Область взаимодействия SQLCA

Сервер базы данных для каждого пользователя (точнее, для каждого процесса) заводит так называемую область взаимодействия SQLCA (SQL Communication Area). Эта область фактически является глобальной структурой данных, состощей из нескольких именованных полей (переменных). Каждая переменная этой структуры содержит ту или иную характеристику последнего выполненного оператора или состояние сервера в целом. Поля структуры SQLCA перечислены в таблице:

-----------T------------T----------------------------------------
¦ название ¦ тип ¦ описание ¦
+----------+------------+----------------------------------------+
¦ SQLCODE ¦ целое ¦ Содержит признак завершения оператора. ¦
¦ ¦ ¦ Может принимать следующие значения: ¦
¦ ¦ ¦ 0 - признак успешного завершения; ¦
¦ ¦ ¦ 100 - признак того, что запрос завер- ¦
¦ ¦ ¦ шен нормально, но не было найдено ¦
¦ ¦ ¦ ни одной записи; ¦
¦ ¦ ¦ отрицательное значение - признак неу- ¦
¦ ¦ ¦ дачного завершения; содержит код ¦
¦ ¦ ¦ ошибки. ¦
+----------+------------+----------------------------------------+
¦ SQLERRM ¦ строка из ¦ Содержит текстовую строку с описанием ¦
¦ ¦ 71 символа ¦ ошибки в случае, если поле SQLCODE ¦
¦ ¦ ¦ меньше нуля. ¦
+----------+------------+----------------------------------------+
¦ SQLERRD ¦ массив из ¦ Описывает результат выполнения послед- ¦
¦ ¦ 6 целых ¦ него оператора SQL: ¦
¦ ¦ ¦ 1-й элемент - внутренняя информация; ¦
¦ ¦ ¦ 2-й элемент - содержит сгенерированное ¦
¦ ¦ ¦ сервером значение поля типа SERIAL ¦
¦ ¦ ¦ для оператора INSERT, либо допол- ¦
¦ ¦ ¦ нительный код ошибки; ¦
¦ ¦ ¦ 3-й элемент - равен количеству обрабо- ¦
¦ ¦ ¦ танных записей; ¦
¦ ¦ ¦ 4-й элемент - примерная "стоимость" ¦
¦ ¦ ¦ выполнения данного оператора; ¦
¦ ¦ ¦ 5-й элемент - смещение ошибки в текс- ¦
¦ ¦ ¦ товой записи оператор SQL ¦
¦ ¦ ¦ 6-й элемент - внутренняя информация; ¦
L----------+------------+-----------------------------------------

Таблица: Поля области взаимодействия (SQLCA) [11] ⁾

Предположим, мы хотим снизить на 20% цену на все товары, описанные в таблице items. А заодно, сообщить пользователю, что цены снижены на столько-то товаров. С ипользованием Informix ESQL/C и области взаимодействия SQLCA фрагмент программы будет выглядеть так:

$update items set price = 0.8*price;
printf ("Цены снижены на %d наименований.\n", sqlca.sqlerrd[2]);

В данном примере (на ESQL/C) количество обработанных записей содержится в sqlca.sqlerrd[2], так как в языке C элементы массива нумеруются с 0 и 3-й элемент массива sqlca.sqlerrd как раз и будет иметь индекс 2.

Теперь рассмотрим пример вставки связанных значений в две таблицы. Например, мы подписали договор с новым поставщиком на поставку некоторого товара. Нам надо вставить информацию о поставщике в таблицу companies, а о товаре в таблицу items. Причем, в таблице items есть поле company, которое указывает на запись в таблице companies. Эта запись определяет фирму-поставщика данного товара (см. описание структуры базы данных в предыдущем номере). Можно, конечно, вставить информацию о фирме, по ее имени найти ее ключ, и это значение использовать для ссылки. Но это плохо - выполняется лишняя операция поиска и нигде не сказано, что имя компании уникально. С помощью области взаимодействия SQLCA данная задача решается достаточно просто (пример на Informix 4GL или NewEra):

DEFINE new_serial INTEGER
............
BEGIN WORK
INSERT INTO companies(name) VALUES ("Наш новый партнер")
LET new_serial = SQLCA.SQLERRD[2] -- ключ (company_id) для новой фирмы
INSERT INTO items (name, company) VALUES ("Новый товар", new_serial)
COMMIT WORK

В данном примере, кстати, объединять два оператора в одну транзакцию (BEGIN WORK... COMMIT WORK) очень желательно. Если этого не сделать и произойдет какой-то сбой между двумя операторами INSERT, то в базе окажется информация о компании, но не будет информации о товаре. В случае использования транзакции этого можно не опасаться.

Другое очень важное использование области взаимодействия SQL ­это проверка на то, что в результате оператора выборки (FETCH или SELECT) была найдена хотя бы одна запись. Для этого служит поле SQLCODE области взаимодействия. Данное поле также устанавливается оператором открытия курсора OPEN. Например, если мы хотим после открытия курсора выдать либо первую запись, либо сообщить о том, что записей не найдено, то соответствующий фрагмент программы будет выглядеть примерно так (пример на Informix ESQL/C):

#define NOTFOUND 100
........
$declare x scroll cursor for select name, address from companies; $open x;
/* теперь проверим на наличие хотя бы одной записи в выборке */
if (sqlca.sqlerrd == NOTFOUND) {
/* нет ни одной записи - сообщить об этом */
printf("Записей не найдено.\n");
}
else {
/* показать первую запись и перейти к диалогу */
$fetch x into $name, $address;
printf("Фирма %s расположена по адресу %s.\n", name, address);
.......
/* диалог с пользователем */
}
$close x; /* закрыте курсора по завершению работы */

Примером проверки ситуации, когда пользователь, выбрав последнюю запись, захотел выбрать еще и следующую (несуществующую), может служить следующий фрагмент программы на языке Informix 4GL:

FETCH x INTO name, address
{ не вышли ли мы за границы выборки? }
IF (SQLCA.SQLERRD = NOTFOUND)
THEN { больше записей нет - сообщить об этом }
ERROR "Записей больше нет."
END IF

И, естественно, область взаимодействия SQL активно используется для контроля за ошибками и состоянием базы данных. Для этого надо проверять сотояние сервера SQL после выполнения какого-либо оператора. Надо отметить, что можно управлять реакцией прикладной программы на возникновение ошибок. Например, программа может автоматически завершаться при возникновении ошибки, а может и продолжать работу, сама обрабатывая ошибочную ситуацию (см. следующий параграф). Предположим, что программа работает в режиме, когда обработкой ошибок занимется сама программа и при возникновении ошибки выполнение программы продолжается. Приведем фрагмент программы, проверяющий наличие базы данных, и, если таковая отсутствует, создающий ее (Informix ESQL/C):

$database my_base;
if (sqlca.sqlerrd < 0) {
/* база данных отсутствует, пытаемся ее создать */
$create database my_base;
if (sqlca.sqlerrd < 0) {
/* ошибка при создании базы */
printf("Базы данных нет и она не может быть создана!\n");
exit(0);
}
}

Управление ошибками

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

Указание программе прекращать выполнение при возникновении ошибке задается так:

WHENEVER SQLERROR STOP

Для перехватывания ошибок и обработки их внутри программы служит следующий вариант оператора WHENEVER:

WHENEVER SQLERROR CONTINUE

или

WHENEVER SQLERROR CALL <имя функции>

В случае CONTINUE при возникновении ошибки управление будет передано на следующий оператор, а в случае CALL - вначале будет вызвана указанная функция. Отслеживание возникновения ошибки в этом случае производится тестированием поля SQLCODE области взаимодействия SQL.

С помощью оператора WHENEVER можно отлавливать и обрабатывать не только возникновение ошибок, но и другие события. Например, оператор WHENEVER можно использовать для отслеживания события "записей не найдено" или при выдаче сервером предупреждений. Соответствующие варианты оператора WHENEVER выглядят так:

WHENEVER NOT FOUND { CONTINUE или STOP или <имя функции> }

WHENEVER SQLWARNING { CONTINUE или STOP или <имя функции> }

Для примера приведем простую программу на Informix-4GL с тремя операторами WHENEVER. Первый оператор задает режим продолжения работы (CONTINUE) при вознкновении ошибок, второй - задает реакцию на ненахождение выборки, а третий - говорит о необходимости завершить программу при возникновении ошибки. Любая ошибка между первым и третьим опреатором WHENEVER будет проигнорирована, а ошибка после третьего оператора WHENEVER приведет к аварийному завершнию программы.

MAIN
DEFINE char_num INTEGER
DATABASE test
WHENEVER SQLERROR CONTINUE -- первый оператор WHENEVER
DISPLAY "Пытаемся выполнить первый оператор INSERT"
INSERT INTO test_table(color) VALUES ("красный")
IF SQLCA.SQLCODE < 0
THEN
DISPLAY "Ошибка при выполнении оператора вставки:", SQLCA.SQLERRM
END IF
WHENEVER NOT FOUND CONTINUE -- второй оператор WHWNEVER
WHENEVER SQLERROR STOP -- третий оператор WHENEVER
DISPLAY "Пытаемся выполнить второй оператор INSERT"
INSERT INTO test_table(color) VALUES ("зеленый")
CLOSE DATABASE
DISPLAY "Программа выполнена"
END MAIN

После первого оператора INSERT мы можем проверить, был ли он реально выполнен путем сравнения SQLCA.SQLCODE с нулем. После второго оператора INSERT данная проверка бессмыслена, так как был выполнен оператор WHENEVER ERROR STOP и любая ошибка приведет к завершению программы.

5.7. Программирование сервера базы данных

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

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

5.7.1. Динамический SQL

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

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

В системах разработки приложений (ESQL/C, NewEra, 4GL и т.д.) имеется возможность формировать и исполнять SQL-запроса в процессе работы прикладной программы, в динамике ее исполнения. Отсюда и название - "динамический SQL" (впрочем, автор не считает это название очень удачным). Фактически, это тот же самый язык - SQL, но SQL-оператор не подвергаетсясинтаксическому разбору на этапе компиляции прикладной программы, а в текстовом виде передается непосредственно серверу базу данных.

Имеются следующие операторы для работы с динамическим SQL:

PREPARE <имя оператора> FROM <текстовая строка>

EXECUTE <имя оператора>

FREE <имя оператора>

Оператор PREPARE "подготавливает" SQL-оператор для исполнения. Сам SQL-оператор указывается либо явно в виде текстовой строки, либо через значение текстовой переменной. Оператор PREPARE "связывает" с SQL-оператором имя. Это имя - самое обычное имя в среде разработки. Примеры (Informix-4GL):

PREPARE sel1 FROM "select name from items where price < 1.50"

PREPARE empty_comp FROM
"INSERT INTO companies(name) VALUES ('undef')"

Оператор PREPARE посылает переданный ему текст SQL-серверу. Сервер анализирует переданные ему SQL-операторы и, если нет ошибок, переводит их во внутреннее представление.

После того, как оператор подготовлен, он может быть исполнен оператором EXECUTE. Здесь-то и надо указывать имя, данное подготовленному оператору:

EXECUTE sel1
EXECUTE empty_comp

Один и тот же подготовленный оператор можно исполнять многократно. Оператор FREE освобождает все ресурсы (память), связанные с подготовленным оператором. Выполнять оператор FREE следует тогда, когда подговленный оператор заведомо больше не потребуется:

FREE sel1

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

Если на момент подготовки SQL-оператора не все конкретные значения известны, то имеется возможность подставлять эти значения в момент исполнения. Для этого SQL-оператор, подготовленный с помощью PREPARE, должен быть снабжен параметрами. Параметры, значения которых будут определяться в момент исполнения, задаются символом "?":

PREPARE select2 FROM "SELECT price FROM items WHERE name =?"
PREPARE new_comp FROM
"INSERT INTO companies(name, address) VALUES (?,?)"

Для задания фактических параметров в оператор EXECUTE надо добавить раздел USING:

EXECUTE new_comp USING "Кооператив 'Эх, ухнем'", "Москва, Арбат, 21"
EXECUTE new_comp USING "ИЧП 'Бумеранг'", "Магадан, п/я 777"

Параметры в операторах PREPARE/EXECUTE являются позиционными. То есть при исполнении на место первого вопросительного знака подставляется первое значение в разделе USING, на место второго вопросительного знака - второе значение из раздела USING и т.д.

Подготовленный оператор может использоваться при описании курсора. Например, если в зависимости от желания пользователя нам надо выполнить сортировку товара либо по названию, либо по цене, то это на Informix-4GL реализуется следующим образом:

DEFINE string1 CHAR(60)
..........
IF flag
THEN { сортируем по названию }
LET string1 = "SELECT name, price FROM items ORDER BY name"
ELSE { сортируем по цене }
LET string1 = "SELECT name, price FROM items ORDER BY price"
END IF
PREPARE select_st FROM string1
DECLARE my_cursor CURSOR FOR select_st
..........

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

Помимо возможности формировать запросы не на этапе написания программы, а на этапе ее иполнения, операторы PREPARE/EXECUTE/FREE могут быть полезны еще, как минимум, в двух случаях: для повышения эффективности программы и для исполнения SQL-операторов, которые понятны серверу, но которых нет в системе разработки.

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

(напомним, что все серьезные реляционные СУБД выполнены по схеме клиент-сервер). SQL-сервер исполняет запрос в четыре этапа: (1) анализирует пришедший запрос, (2) выбирает оптимальный способ его исполнения, (3) исполняет и (4) отсылает результаты приложению. Если выполнять несколько одинаковых запросов, то для каждого запроса будeт делаться все четыре этапа. Если же мы подготавливаем запрос с помощью оператора PREPARE, а затем несколько раз исполняем его оператором EXECUTE, то анализ запроса и поиск оптимального способа исполнения будет делаться только один раз - в момент выполнения оператора PREPARE. А на каждое исполнение запроса оператором EXECUTE требуется только два последних этапа - непосредственное исполнение и отсылка результатов.

Другое полезное свойство подготавливаемых операторов - это расширение возможностей среды разработки. Подготавливаемый оператор для приложения существует только как текстовая строка. Его исполнение и синтаксический разбор возложен на SQL-сервер. Поэтому с помощью операторов PREPARE/EXECUTE можно выполнить SQL-запрос, не предусмотренный в синтаксисе среды разоаботки.

Например, Вы имеете среду разработки Informix-4GL старой версии (предположим, 4-й). Она вас вполне устраивает. Но в качестве SQL-сервера используется 7-я версия Informix DS Dynamic Scalable Architecture. Этот сервер "понимает" уже значительно более широкий набор SQL-операторов по сравнению с 4-й версией Informix-4GL. В частности, оператор создания триггеров CREATE TRIGGER (о том, что это такое, будет сказано ниже) может быть исполнен сервером, но отсутствует в продукте Informix-4GL версии 4.10. Используя операторы PREPARE/EXECUTE триггер можно создать:

PREPARE cr_trig FROM "CREATE TRIGGER trig1..."
EXECUTE cr_trig
FREE cr_trig

Подготовленные с помощью оператора PREPARE SQL-запросы доступны (видимы) только в данном приложении, а именно между операторами DATABASE... CLOSE DATABASE. То есть, если вы завершили работу (а точнее, закрыли базу данных оператором CLOSE DATABASE), то подготовленные запросы пропадают. Или, если вы подготовили SQL-запрос, то другой пользователь за другим компьютером не может выполнить подготовленный вами запрос (этот другой пользователь, конечно, может выполнить ту же самую последовательность PREPARE/EXECUTE/FREE, но это будет уже другой SQL-запрос). Однако, существует возможность подготовить для исполнения SQL-запросы так, что эти запросы будут доступны многим пользователям. Но для этого используется уже другой механизм - хранимые процедуры.

5.7.3. Хранимые процедуры

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

Основное назначение хранимых процедур - это функциональное расширение схемы базы данных. Хранимая процедура может содержать некоторый нетривиальный набор операторов, реализующий то или иное логическое действие. Например, администратор банковской системы разработал хранимую процедуру, которая реализует функцию "занести на счет номер X сумму Y". Программист, разрабатывающий приложение пользуется этой процедурой, но не знает КАК именно она это делает. В результате:

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

· - никто кроме администратора не знает о том, как устроена схема базы данных и меньше шансов, что кто-то использует эти знания в корыстных целях;

· - независимо от типа рабочего места одно и тоже действие выполняется гарантированно одними и теми же операторами - выше надежность разработанной системы;

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

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

Для написания хранимых процедур используется сильно расширенный SQL. Причем, расширение делается не за счет увеличения числа операторов манипулирования данными, а за счет управляющих структур - ведь в SQL нет ни оператора присваивания, ни циклов, ни процедур. Формально не очень понятно, можно ли считать язык написания хранимых процедур частью SQL или нет. Скорее нет, это самостоятельный язык, но эти два языка настолько тесно связаны, что рассматривать их по отдельности бессмысленно.

Языки написания хранимых процедур в настоящий момент сильно различается у разных производителей. Для серверов Informix язык для создания хранимых процедур носит название SPL - Stored Procedure Languages. Именно его (точнее, его подмножество), мы и рассмотрим.

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

CREATE PROCEDURE <имя процедуры> (<формальные параметры>)
<тело хранимой процедуры>
END PROCEDURE

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

CREATE PROCEDURE <имя процедуры> (<формальные параметры>)
RETURNING <тип 1>, <тип 2>...
<тело хранимой процедуры>
END PROCEDURE

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

Примеры описаний хранимых процедур:

CREATE PROCEDURE incr_account (account_no INTEGER,
quantity MONEY(20,2))
.....
END PROCEDURE

CREATE PROCEDURE add_new_user (name CHAR(20) default NULL)
RETURNING INTEGER {серийный номер нового пользователя}
.....
END PROCEDURE

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

Хранимая процедура, хоторая больше не нужна, может быть удалена. Для этого используется оператор:

DROP PROCEDURE <имя хранимой процедуры>

Например:

DROP PROCEDURE add_new_user

Для исполнения хранимой процедуры используется оператор

EXECUTE PROCEDURE <имя процедуры> (<фактические параметры>)

Например:

EXECUTE PROCEDURE incr_account (213917008, 23000000.00)

Если хранимая процедура возвращает какие-то значения (то есть в ее описании есть раздел RETURNING), то при исполнении этой процедуры в оператор EXECUTE PROCEDURE надо добавить раздел INTO с перечислением имен переменных (иногда вместо INTO используется слово RETURNING):

EXECUTE PROCEDURE <имя процедуры> (<фактические параметры>)
INTO <имя переменной>,...

Например:

EXECUTE PROCEDURE add_new_user ("Прутков") INTO user_id

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

EXECUTE PROCEDURE incr_account (123456789, 1500000.00)
EXECUTE PROCEDURE incr_account
(account_no=123456789, quantity=1500000.00)
EXECUTE PROCEDURE incr_account
(quantity=1500000.00, account_no=123456789)

Если при исполнении хранимой процедуры значения каких-либо фактических параметров не указаны, то используются значения по умолчанию. Значения по умолчанию задаются при описании хранимой процедуры с помощью ключевого слова DEFAULT. Например, в описанной выше процедуре add_new_user для параметра name задано значение по умолчанию - NULL.

Язык хранимых процедур

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

В разделе описаний описываются внутренние переменные. Каждое описание начинается со служебного слова DEFINE, после которого идет имя переменной и ее тип.

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

В разделе исполняемых операторов могут использоваться любые операторы из таблицы 1, а также любой из SQL-операторов, за исключеием тех, которые создают, удаляют, открывают или закрывают базу данных. При обращении к хранимой процедуре управление передается первому оператору в ее теле. Исполнение оператора RETURN завершает хранимую процедуру и возвращает исполнение в то место, откуда хранимая процедура была вызвана. Хранимая процедура завершаетя также и при достижении оператора END PROCEDURE. При этом никаких значений в вызывающую программу не возвращается.

В качестве примера рассмотрим реализацию хранимых процедур, приведенных при рассмотрении оператора CREATE PROCEDURE:

CREATE PROCEDURE incr_account (account_no INTEGER, quantity MONEY(20,2))
UPDATE accounts SET value = value + quantity
WHERE account_id = account_no
END PROCEDURE

Хранимая процедура может содержать не только SQL операторы, но и операторы, обычные для универсальных языков программирования. Например:

CREATE PROCEDURE add_new_user (name CHAR(20) default NULL)
RETURNING INTEGER {серийный номер нового пользователя}
DEFINE user_id INTEGER;
INSERT INTO users(lname) VALUES (name);
LET user_id = SQLCA.SQLERR[2];
RETURN user_id
END PROCEDURE

Рассмотрим основные операторы, имеющиеся в языке хранимых процедур Informix SPL (SPL - это аббревиатура отStored Procedure Language):

CALL - то же самое, что и EXECUTE PROCEDURE.

CONTINUE - продолжить выполнение цикла. После этого слова надо указать какой именно цикл надо продолжить выполнять -FOR/WHILE/FOREACH. Примеры:

CONTINUE FOR
....
CONTINUE FOREACH

DEFINE - определить внутреннюю переменную. Для каждой переменной надо задать ее имя и ее тип:

DEFINE <имя переменной> <тип>

Примеры:

DEFINE user_id INTEGER
DEFINE comp_name CHAR(40)

EXIT - прекратить выполнение цикла. После этого слова надо указать какой именно цикл надо прекратитьвыполнять - FOR/WHILE/FOREACH. Примеры:

EXIT WHILE
....
EXIT FOR

FOR - цикл со счетчиком. Аналогичен оператору цикла со счетчиком в других языках программирования. Значения шага, начального и конечного значений вычисляются заранее, до начала выполнения операторов тела цикла:

FOR <переменная> = <целое> TO <целое> STEP <целое>
<операторы>
END FOR

Если шаг (STEP) не указан, то он принимается равным 1 если начальное значение меньше конечного, и -1, если нет. Пример:

FOR i = 1 TO 14
....
END FOR

IF - условный оператор. Проверяет условие и, если оно верно, выполняет операторы после слова THEN. Если не верно, и присутствует раздел ELSE - то выполняются операторы после слова ELSE:

IF <условие> THEN <операторы> END IF
IF <условие>
THEN <операторы>
ELSE <операторы>
END IF

Если после слова ELSE по логике должен следовать другой оператор IF, то такое сочетание ELSE IF... END IF END IF можно записывать более компактно: ELIF... END IF. Пример:

IF j > 20
THEN RETURN j
ELIF j > 10
THEN RETURN 10
ELSE RETURN 0
END IF

LET - оператор присваивания. Вычисляет выражение и присваивает его значение указанной переменно:

LET <имя переменной> = <выражение>

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

LET i = 0
LET str1 = "абвгдежз"

RAISE EXCEPTION - возбудить ошибку. Если на данную ошибку не стоит реакция (нет соответствующего оператора ON EXCEPTION), то исполнение хранимой процедуры завершается и в вызвавшую программу "передается" данная ошибка:

RAISE EXCEPTION <номер ошибки>

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

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

RETURN <выражение 1>,....

SYSTEM - позволяет выполнить внешнюю команду. Имя этой внешней команды передается операционной системе, то есть это может быть или встроенная команда ОС, или исполнимый файл:

SYSTEM <символьная строка>
SYSTEM <имя символьной переменной>

Например, можно выполнить командный файл my_cmd:

SYSTEM "/usr/local/bin/my_cmd"

WHILE - цикл с завершением по условию. Аналогичен циклу WHILE в других языках программирования:

WHILE <условие>
<операторы>
END WHILE

Пример:

LET i = 1
WHILE i = 1
EXECUTE PROCEDURE my_proc RETURNING I
END WHILE

BEGIN... END - блок операторов. В блоке операторов можно определять и использовать локальные переменные. Допустимые любые другие операторы SPL. Пример:

BEGIN
DEFINE i CHAR(20)
LET i = "Ну, погоди!"
IF... THEN
BEGIN
DEFINE i INTEGER
LET i = 0
....
END
END IF
END

5.7.4. Триггеры

Идея триггеров

Поиск по сайту: