Использование функции CAST
Иногда возникает потребность трактовать значение одного типа как значение другого типа. Например, использовать числовое значение как символьную строку или наоборот. В этом случае применяют функцию
CAST (<значение> AS <тип данных>)
Функция CAST делает копию значения, преобразуя его к указанному типу данных. При этом не следует забывать о множестве типов данных, в которое может быть преобразовано данное значение:
Пример.
Найти покупателя, который делал закупки то ли на 209, то ли на 309 единиц товара. На сколько именно, никто не помнит; вспомнили лишь, что последними разрядами в количестве отпускаемого товара были цифры '09'. Тогда приводим значения столбца KOLVO к типу CHARACTER и к результату применяем LIKE (рис. 25.33).ЗАМЕЧАНИЕ.
Значение типа DATE не нужно приводить к строковому типу (в примере не используется тип DATE! убрать ссылку на пример), поскольку два этих типа являются совместимыми и значение DATE в InterBase может трактоваться как строковое. Например, в приводимом ниже запросе объединяются значения типа DATE и строковое, и результат трактуется как строковое значение (результат на рис. 25.34).SELECT "Дата расхода: " I I DAT_RASH, POKUP, KOLVO FROM RASHOD WHERE KOLVO > 1000
Нигде до этого не объяснен синтаксис ||
Пример.
Выдать все покупки товара за 20 число (предположим, каждого месяца). В InterBase нет встроенных функций для разделения даты на число, месяц и год. Варианты решения:• в приложении, разработанном на Delphi, анализировать даты при помощи процедуры DecodeDate;
• написать UDF (User Defined Function, определенную пользователем функцию), которая реализует выделение номера дня из даты, и использовать имя этой функции в операторе SELECT;
• привести значение даты к типу CHAR (или, что лучше, трактовать значение даты как строковое значение) и применить к полученному значению LIKE, CONTAINING или STARTING WITH, в зависимости от потребности.
Воспользуемся последним способом (результат на рис. 25.35).
SELECT DAT_RASH, TOVAR, POKUP, KOLVO FROM RASHOD .
WHERE CAST(DAT_RASH AS CHAR(6)) STARTING WITH "20"
или, устранив ненужное, в данном случае, приведение типов,
SELECT DAT_RASH, TOVAR, POKUP, KOLVO FROM RASHOD WHERE DAT_RASH STARTING WITH "20"
Как видно из приводимого выше результата выполнения оператора SELECT, строковый формат представления значений типа DATE упрощает выделение номера дня, наименования месяца и номера года. (Ранее не был объяснен формат задания даты, поэтому не понятно, как выделять месяц)
Предложение WHERE : задание сложных условий поиска
Ранее были рассмотрены простые варианты задания условия поиска в предложении WHERE (сравнение столбца с константой и внутреннее соединение). Однако условия поиска могут быть достаточно сложными, чему способствует и сам синтаксис оператора SELECT. Рассмотрим основные конструкции для построения сложных условий поиска.
Использование подзапросов
Часто невозможно решить поставленную задачу путем использования одного запроса. Это особенно актуально в тех случаях, когда при использовании условия поиска в предложении WHERE
Сравниваемое значение> <оператор> <значение, с которым сравнивать>
значение, с которым надо сравнивать,
заранее не определено и должно быть вычислено в момент выполнения оператора SELECT.Другой причиной, которая должна побудить к использованию вложенных подзапросов, является то, что во многих случаях значение, с которым надо сравнивать, должно представлять собой не одно, а несколько значений.
Внутренний подзапрос представляет собой также оператор SELECT и кодирование его предложений подчиняется тем же правилам, что и для основного оператора SELECT.
В общем случае оператор SELECT с подзапросом имеет вид
SELECT .. .
FROM ...
WHERE Сравниваемое значение> <оператор> SELECT ...
FROM ...
WHERE ...
Пример.
Выдать все даты, на которые приходится максимальный отпуск товаров (результат на рис. 25.36).SELECT KOLVO, DAT_RASH FROM RASHOD
WHERE KOLVO = (SELECT MAX(KOLVO) FROM RASHOD
KOLVO
DAT_RASH
1
10.01.97
Puc. 25.36.
Сначала нужно найти количество максимального отпуска товаров, поскольку это значение неизвестно. Это и делает внутренний подзапрос (SELECT MAX(KOLVO) FROM RASHOD )
Далее выполняется основной запрос, как если бы он был записан так:
SELECT KOLVO, DAT_RASH FROM RASHOD WHERE KOLVO = 4000
Пример.
Усложним предыдущий пример. Определить дату, когда со склада было отгружено максимальное количество товара, и реквизиты покупателя, который этот товар купил (результат на рис. 25.37).SELECT R.KOLVO, R.DAT_RASH, P.POKUP, P.GOROD, P.ADRES
FROM RASHOD R, POKUPATELI P
WHERE (R.POKUP = P.POKUP) AND
KOLVO =(SELECT MAX(KOLVO)
FROM RASHOD
)
По сравнению с предыдущим примером в запрос включено внутреннее соединение таблиц RASHOD и POKUPATELI.
ЗАМЕЧАНИЕ.
Распространенной ошибкой является использование вложенного оператора SELECT, который вместо единичного значения способен возвращать список значений.Выше по тексту есть абзац: Другой причиной, которая должна побудить к использованию вложенных подзапросов, является то, что во многих случаях значение, с которым сравнивать, должно представлять собой не одно, а несколько значений. Поясним, почему этот абзац нельзя распространять на следующий пример.
Пример.
Найти в таблице POKUPATELI покупателя, у которого поле GOROD содержит "С-Петербург" и выдать все осуществленные им покупки товаров, из таблицы RASHOD. Может быть написан следующий потенциально ошибочный запрос (результат на рис. 25.38).SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, R.POKUP FROM RASHOD R
WHERE R.POKUP = (SELECT POK.POKUP FROM POKUPATELI РОК
WHERE UPPER(РОК.GOROD) = 'С-ПЕТЕРБУРГ '
)
Хотя для значения "С-Петербург" и был выдан корректный результат, такой запрос потенциально ошибочен, поскольку способен возвращать несколько значений; например, если поменять "С-Петербург" на "Москва", получим уведомление об ошибке (рис. 25.39)
SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, R.POKUP FROM RASHOD R
WHERE R.POKUP = (SELECT POK.POKUP FROM POKUPATELI РОК
WHERE UPPER(РОК.GOROD) = 'МОСКВА' )
Вложение подзапросов
Часто невозможно обойтись одним подзапросом. Тогда в подзапросе используют вложенный подзапрос.
Пример. Составить список отгрузки товаров покупателю, который в свое время купил максимальную партию какого-либо товара (результат на рис. 25.40).
SELECT RRR.* FROM RASHOD RRR
WHERE RRR.POKUP IN
(SELECT R.POKUP FROM RASHOD R
WHERE KOLVO = (SELECT MAX(RSH.KOLVO) FROM RASHOD RSH)
)
ЗАМЕЧАНИЕ.
IN использован вместо знака равенства на тот случай, если встретится два и более покупателя, имеющие одинаковое число максимальных покупок. В этом случае запрос вернет записи из RASHOD по всем таким покупателям. Использование знака равенства '=' вместо IN способно привести к ошибке при выдаче в качестве результата "среднего" запроса (SELECT R.POKUP...) множества значений вместо требуемого одного.Логический порядок выполнения запроса.
Вычисляется максимальное значение в столбце KOLVO ("самый вложенный" подзапрос SELECT MAX). Далее в "среднем" подзапросе SELECT R.POKUP выбирается покупатель, купивший какой-либо товар в количестве, равном значению, вычисленному в "самом вложенном" подзапросе. Вслед за этим "самый главный" запрос SELECT RRR выбирает записи с покупателем, наименование которого выдано "средним" подзапросом.ЗАМЕЧАНИЕ.
Поставленную задачу могут выполнить и другие запросы,напримерSELECT RRR.* FROM RASHOD RRR
WHERE RRR.POKUP IN
(SELECT R.POKUP FROM RASHOD R GROUP BY R.POKUP
HAVING MAX(R.KOLVO) = (SELECT MAX(RSH.KOLVO) FROM RASHOD RSH)
)
Использование EXISTS
Бывают случаи, когда в условии поиска нужно указать, что из таблицы требуется отобрать только те записи, для которых подзапрос возвращает одно или более значений. В этом случае в условии поиска указывается предложение EXISTS (<подзапрос>)
Пример. Выдать список всех покупателей, которые получали товар со склада (результат на рис. 25.41).
SELECT P.POKUP FROM POKUPATELI P
WHERE EXISTS (SELECT R.POKUP FROM RASHOD R WHERE R.POKUP = P.POKUP)
Использование SINGULAR
Если в условии поиска нужно указать, что из таблицы требуется выбрать лишь те записи, для которых подзапрос возвращает только одно значение, указывается предложение
SINGULAR (<onepaTOp_select >)
Пример.
Выдать список всех покупателей, купивших только один товар (результат на рис. 25.42).SELECT P.* FROM POKUPATELI P
WHERE SINGULAR (SELECT * FROM RASHOD R WHERE R.POKUP = P.POKUP)
POKUP
GOROD
ADRES
ЛИРА ТОО
МОСКВА
пр. Стромыкина 21
Рис. 25.42
Использование ALL, SOME
Если в условиях поиска необходимо указать , что сравниваемое значение (значение столбца, результат вычисления выражения) должно находиться в определенных отношениях со всеми значениями из множества значений, возвращаемых подзапросом, применяют предложение типа
<Сравниваемое значение> {[NOT] {ALL | SOME | ANY} (<подзапрос>)
где подзапрос может возвращать более одного значения. Оператор определяет операцию сравнения (>, >=, < и т.д.). Отношение сравниваемого значения и значений, возвращаемых подзапросом, устанавливается словами ALL и SOME (ANY).
ALL
определяет, что условие поиска истинно, когда сравниваемое значение находится в отношении, определяемом оператором, со всеми значениями, возвращаемыми подзапросом. Например:WHERE STOLBEZ > ALL (SELECT POLE FROM TABLIZA)
определяет, что текущее значение столбца STOLBEZ должно быть больше всех значений в столбце POLE из таблицы TABLIZA.
SOME
(вместо него можно указать ANY) что условие поиска истинно, когда сравниваемое значение находится в отношении, определяемом оператором, хотя бы с одним значением, возвращаемым подзапросом. Например: WHERE STOLBEZ > SOME (SELECT POLE FROM TABLIZA)определяет, что текущее значение столбца STOLBEZ должно быть больше хотя бы одного значения в столбце POLE из таблицы TABLIZA.
Пример.
Перечислить все факты отгрузки товаров со склада, в которых количество единиц отгружаемого товара превышает среднее значение (результат на рис. 25.43).SELECT * FROM RASHOD R1
WHERE R1.KOLVO > ALL (SELECT AVG(R2.KOLVO) FROM RASHOD R2 GROUP BY POKUP)
Пример.
Перечислить все факты отгрузки товаров со склада, в которых количество единиц отгружаемого товара превышает среднее значение отгрузки хотя бы одного товара (результат на рис. 25.44).SELECT * FROM RASHOD R1
WHERE Rl.KOLVO > SOME (SELECT AVG(R2.KOLVO) FROM RASHOD R2 GROUP BY POKUP )
Использование HAVING и агрегатных функций для вложенных подзапросов
Если в условиях поиска для вложенного запроса нужно указать агрегатную функцию, используется HAVING..
Пример. Определить покупателя, у которого средняя покупка больше средней покупки других покупателей, и среднее число покупки этого покупателя (результат на рис. 25.45).
SELECT R1.POKUP, AVG(Rl.KOLVO) FROM RASHOD R1
GROUP BY R1.POKUP HAVING AVG(R1.KOLVO) >= ALL
(SELECT AVG(R2.KOLVO) FROM RASHOD R2 GROUP BY R2 . POKUP )
Пример. Определить адрес покупателя, который приобрел наибольшее количество товаров (результат на рис 25.46).
SELECT P.* FROM POKUPATELI P
WHERE P.POKUP =
(SELECT RR.POKUP FROM RASHOD RR
GROUP BY RR.POKUP HAVING SUM(RR.KOLVO) >= ALL
(SELECT SUM(RRR.KOLVO) FROM RASHOD RRR GROUP BY RRR.POKUP)
)
Пример. Перечислить все товары, которые приобрел покупатель, купивший наибольшее количество товаров (результат на рис. 25.47).
SELECT DISTINCT R.TOVAR FROM RASHOD R
WHERE R.POKUP =
(SELECT RR.POKUP FROM RASHOD RR
GROUP BY RR.POKUP HAVING SUM(RR.KOLVO) >= ALL
(SELECT SUM(RRR.KOLVO) FROM RASHOD RRR GROUP BY RRR.POKUP)
)
Пример.
Перечислить стоимость единиц товаров, которые приобрел покупатель, купивший наибольшее количество товаров (результат на рис 25.48)SELECT DISTINCT R.TOVAR, T.ED_IZM, T.ZENA FROM RASHOD R, TOVARY T
WHERE T.TOVAR = R.TOVAR AND R.POKUP = (SELECT RR.POKUP FROM RASHOD RR
GROUP BY RR.POKUP HAVING SUM(RR.KOLVO) >= ALL
(SELECT SUM(RRR.KOLVO) FROM RASHOD RRR GROUP BY RRR.POKUP )
Внешние соединения
Выше нами были рассмотрены внутренние соединения таблиц базы данных Напомним, что внутренние соединения имеют место, если в предложении WHERE указано условие
<имя столбца таблицы 1> <оператор> <имя столбца таблицы 2>
Например,
SELECT RASHOD.*, TOVARY.ZENA FROM RASHOD, TOVARY
WHERE RASHOD.TOVAR = TOVARY.TOVAR
В этом случае осуществляется декартово произведение таблиц 1 и 2 и из полученного НД отбираются записи, удовлетворяющие условию поиска (RASHOD TOVAR = TOVARY.TOVAR)
Существует также и другой вид соединения таблиц, внешнее соединение. Оно определяется в предложении FROM согласно спецификации SELECT {* | <значение1> [, <значение2> ...1}
FROM <таблица1> <вид соединения> JOIN < таблица2> ON <условие поиска>
Внешнее соединение похоже на внутреннее соединение, но в результирующий НД включаются также записи ведущей таблицы соединения, которые объединяются с пустым множеством записей другой таблицы. Какая из таблиц будет ведущей, определяет вид соединения
• LEFT -
(левое внешнее соединение), когда ведущей является таблица1 (расположенная слева от вида соединения);•
RIGHT - (правое внешнее соединение), когда ведущей является таблица1 (расположенная справа от вида соединения);Пример.
Пусть имеем таблицы| A.P1 | А.Р2 | В.Р2 |
| а | х | 1 |
| B | х | 1 |
| С | у | 2 |
| d | 1 | |
| Рис 25.49 | ||
| A.P1 | А.Р2 | В.Р2 |
| а | х | 1 |
| b | х | 1 |
| с | у | 2 |
| 2 | ||
| Рис 25.50 |
Таблица А Таблица В
Р1
Р2
РЗ
а
х
400
b
х
200
с
у
500
d
Тогда выполнение оператора SELECT, реализующего внешнее левое соединение,
SELECT A.P1, А.Р2, В.Р2 FROM A LEFT JOIN В ON А.Р2 = В.Р1
приведет к выдаче результирующего НД (рис 25.49):
Пунктиром показаны столбцы ведущей таблицы А. Как видно, для записи таблицы А, где столбец A.P1 имеет значение 'd', нет парных записей в таблице В, для которых удовлетворялось бы условие поиска А.Р2 = В.Р1. Поэтому данная запись таблицы А показана в соединении с пустой записью.
В то же время, выполнение оператора SELECT, реализующего внешнее правое соединение,
SELECT A.P1, А.Р2, В.Р2 FROM A RIGHT JOIN B ON А.Р2 = В.Р1
приведет к выдаче такого результирующего НД (рис 25.50)
Пунктиром показаны столбцы ведущей таблицы В. Как видно, для записи таблицы В, где столбец В.Р1 имеет значение 'z' и столбец В.Р2 имеет значение '2', нет парных записей в таблице А, для которых удовлетворялось бы условие поиска А.Р2= В.Р1. Поэтому данная запись таблицы В показана в соединении с пустой записью.
Пример.
Построить внешнее соединение по таблице RASHOD с таблицей POKUPATELI, т.е. показать покупателя, соответствующего каждому расходу (результат на рис.25.51).SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, P.POKUP, P.GOROD
FROM RASHOD R LEFT JOIN POKUPATELI P ON R.POKUP = P.POKUP
или
SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, P.POKUP, P.GOROD
FROM POKUPATELI P RIGHT JOIN RASHOD R ON R.POKUP = P.POKUP
DAT_RASH
TOVAR
KOLVO
POKUP
GOROD
10.01.97
Сахар
20
Лира, ТОО
МОСКВА
10.01.97
Сахар
509
Рис. 25.51.
Пример.
Построить внешнее соединение по таблице POKUPATELI с таблицей RASHOD, т.е. показать все расходы по каждому покупателю (результат на рис.25.52).SELECT P.POKUP, P.GOROD, R.DAT_RASH, R.TOVAR, R.KOLVO
FROM POKUPATELI P LEFT JOIN RASHOD R ON R.POKUP = P.POKUP
или
SELECT P.POKUP, P.GOROD, R.DAT_RASH, R.TOVAR, R.KOLVO
FROM RASHOD R RIGHT JOIN POKUPATELI P ON R.POKUP = P.POKUP
PQKUP
GOROD
DAT_RASH
TOVAR
KOLVO
Лира ТОО
МОСКВА
10.01.97
Сахар
20
Саяны. ИЧП
Москве
10.01.97
Кока-кола
4000
Саяны, ИЧП
Москва
20.01.97
Сахар
30
Адмирал, АО
С.Петербург
10.6l.97
Ставрида консорв,
3000
Адмирал. АО
С-Петербург
20.01.97
Кока-кола
1000
Адмирал. АО
С-Петербург
10.01.97
Кока-кола
100
Геракл
Уфа
Рис. 25.52.
UNION - объединение результатов выполнения нескольких операторов SELECT
Иногда бывает полезным объединять два или более результирующих НД, возвращаемых после выполнения двух или более операторов SELECT. Такое объединение производится при помощи оператора UNION. Результирующие НД должны иметь одинаковую структуру, т.е. одинаковый состав возвращаемых столбцов. Если в результирующих НД имеется одна и та же запись, в сводном НД она не дублируется.
Пример.
Соединить результаты выполнения трех запросов:1. (результирующий НД на рис. 25.53).
SELECT R.* FROM RASHOD R
WHERE R.TOVAR CONTAINING 'Ставрида'
2. (результирующий НД на рис. 25.54).
SELECT R.* FROM RASHOD R
WHERE R.KOLVO >= 3000
3. (результирующий НД на рис. 25.55).
SELECT R.* FROM RASHOD R
WHERE R.POKUP = 'Лира, ТОО'
Произведем объединение трех результирующих наборов данных (результат объединений на рис. 25.56).
SELECT R.* FROM RASHOD R WHERE R.TOVAR CONTAINING 'Ставрида'
UNION SELECT R.* FROM RASHOD R WHERE R.KOLVO >= 3000
UNION SELECT R.* FROM RASHOD R WHERE R.POKUP = 'Лира, ТОО'
Использование IS NULL
Если требуется выдать все записи, в которых некоторый столбец (или результат вычисления выражения) имеет значение NULL, достаточно в условии поиска указать
<значение> IS [NOT] NULL
Пример.
Показать все факты отгрузки товаров со склада, для которых не указан покупатель (результат на рис. 25.57).SELECT * FROM RASHOD WHERE POKUP IS NULL
Использование операции сцепления строк
Операция || соединяет два строковых значения, которые могут быть представлены выражениями:
<строковое выражение1> || <строковое выражение2>
Операцию || можно использовать как после слова SELECT для указания возвращаемых значений, так и в предложении WHERE.
Пример.
Выдать в виде единого столбца имена покупателей и названия их городов (результат на рис. 25.58).Работа с разными БД в одном запросе
В одном запросе можно использовать таблицы из разных БД. В этом случае имя таблицы указывается в формате ПсевдонимБД:ИмяТаблицы
Под псевдонимом БД понимается псевдоним, определенный в утилите BDE Administrator.
Ниже приведен пример обращения в одном запросе к таблицам БД InterBase (псевдоним ' MONITOR') и Oracle (псевдоним 'DWH'):
SELECT U.* FROM ":MONITOR:NLS " N, ":DWH:OLAP_UPE" U
WHERE U.SC_CODE = N.COD_SCENARIO
ORDER BY U.SC CODE
Оператор SELECT
25.1. Простейший вид оператора SELECT *
25.2. Использование предложения WHERE *
25.2.1. Сравнение значения столбца с константой *
25.2.2. Сравнение значения столбца из одной таблицы со значением столбца из другой таблицы (внутреннее соединение) *
25.3. Использование псевдонимов таблиц *
25.4. Предложение ORDER BY - определение сортировки *
25.5. Устранение повторяющихся значений *
25.6. Расчет значений результирующих столбцов на основе арифметических выражений *
25.8. Использование группировок записей *
25.9. Предложение HAVING - наложение ограничений на группировку записей *
25.10. Предложение WHERE : задание сложных условий поиска *
25.10.1. Использование логических выражений *
25.10.2. Сравнение столбца с результатом вычисления выражения *
25.10.3. Использование BETWEEN *
25.10.4. Использование IN (список значений) *
25.10.5. Использование STARTING *
25.10.6. Использование CONTAINING *
25.10.7. Использование функции UPPER *
25.10.9. Использование функции CAST *
25.11. Использование подзапросов *
25.13. Дополнительные возможности использования подзапросов, возвр единичное значение *
25.13.1. Использование EXISTS *
25.13.2. Использование SINGULAR *
25.14. Использование подзапросов, возвращающих множество значений *
25.14.1. Использование ALL, SOME *
25.14.2. Использование HAVING и агрегатных функций для вложенных подзапросов *
25.16. UNION - объединение результатов выполнения нескольких операторов SELECT *
25.17. Использование IS NULL *
25.18. Использование операции сцепления строк *
25.19. Работа с разными БД в одном запросе *
Оператор SELECT - один из наиболее важных и самый используемый оператор SQL. Он позволяет производить выборки данных из ТБД и преобразовывать к нужному виду полученные результаты. Это очень мощный оператор. При его помощи можно реализовать весьма сложные и громоздкие условия выбора данных из различных таблиц.
Для тех, кто ранее не использовал SQL для доступа к БД, примеры использования данного оператора наглядно продемонстрируют один из основополагающих принципов "больших" (промышленных) СУБД: средства хранения данных и доступа к ним отделены от средств представления данных; операции над данными производятся в масштабе наборов данных, а не отдельных записей. Разработчики, долгое время использовавшие локальные ("персональные") СУБД, в процессе перехода от Paradox, dBase, Clarion и т.п. к архитектуре "клиент-сервер" обычно при изучении оператора SELECT вынуждены менять устоявшиеся парадигмы мышления: локальные СУБД реализуют в основном навигационный подход доступа к данным (выборка по одной записи) и подразумевают достаточно прочную обратную связь между способами хранения данных, доступа к ним и их представления пользователю в приложении.
Оператор SELECT - средство, которое полностью абстрагировано от вопросов представления данных; все внимание при его применении сконцентрировано на проблемах доступа к данным.
Оператор SELECT имеет следующий формат:
SELECT [DISTINCT | ALL] {* | <значение1> [, <значение2> ...]}
FROM <таблица1> [, < таблица2> ...]
[WHERE <условия_поиска>]
[GROUP BY столбец [COLLATE collation} [,столбец! [COLLATE collation] ...]
[HAVING < условия поиска >]
[UNION <оператор select>]
[PLAN <план выполнения_запроса>]
[ORDER BY <список_столбцов>]
Этот формат с первого взгляда достаточно громоздок и кажется поэтому сложным. Такое впечатление при дальнейшем изучении оператора SELECT наверняка покажется Вам неверным. Поэтапно рассмотрим возможности, которые предоставляет оператор SELECT.
Явное указание списка значений
В этом случае оператор INSERT применяется для добавления одной записи и имеет формат
INSERT INTO <объект> [(столбец1 [, столбец2 ...])]
VALUES (<значение1> [, <значение2> ...])
Значения
назначаются столбцам по порядку следования тех и других в операторе: первому по порядку столбцу назначается первое значение, второму столбцу - второе значение и т.д.Пример.
Добавить в таблицу ТОVARY новую запись:INSERT INTO RASHOD (N_RASH, DAT_RASH,KOLVO, TOVAR, POKUP)
VALUES (45, "20-FEB-1997", 100, "Сахар", "Саяны, ИЧП")
Поскольку столбцы таблицы RASHOD указаны в полном составе и именно в том порядке, в котором они перечислены при создании таблицы RASHOD оператором CREATE TABLE, оператор можно упростить:
INSERT INTO RASHOD VALUES (45, "20-FEB-1997", 100, "Сахар", "Саяны, ИЧП")
Для установки уникального значения поля первичного ключа N_RASH можно воспользоваться генератором:
INSERT INTO RASHOD
VALUES (GEN_ID(RASHOD_N_RASH,2), "20-FEB-1997", 100,"Сахар", "Саяны, ИЧП")
Указание значений при помощи оператора SELECT
Второй формой оператора INSERT является
INSERT INTO <объект> [(столбец1 [, столбец2 ...])]
<оператор SELECT>При этом значениями, которые присваиваются столбцам, являются значения, возвращаемые оператором SELECT. Порядок их назначения столбцам аналогичен предыдущей форме оператора INSERT: значение первого по порядку столбца результирующего набора данных оператора SELECT присваивается первому столбцу оператора INSERT, второй - второму и т.д. Следует обратить внимание на важную особенность: поскольку оператор SELECT в общем случае возвращает множество записей, то и оператор INSERT в данной форме приведет к добавлению в объект аналогичного количества новых записей.
Пример.
Пусть в БД определена таблица RASHOD_DATA, по составу и порядку следования полей аналогичная таблице RASHOD:CREATE TABLE RASHOD_DATA(
N_RASH INTEGER NOT NULL,
DAT_RASH DATE NOT NULL,
KOLVO INTEGER NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
POKUP VARCHAR(20) COLLATE PXW_CYRL,
PRIMARY KEY(N_RASH)
);
Пусть в эту таблицу нужно ежедневно копировать все записи о расходах товара со склада за текущую дату. Представим, что эти сведения в дальнейшем ежедневно
переправляются в территориально удаленную бухгалтерию, к офису которой не успели подвести сетевые кабели (или вообще не собираются этого делать).
Тогда ежедневная выгрузка записей из таблицы RASHOD в таблицу RASHOD_DATA будет реализовываться таким оператором:
INSERT INTO RASHOD_DATA
SELECT * FROM RASHOD WHERE DAT_RASH = "20-JAN-1997"
Пусть перед выполнением данного оператора таблица RASHOD_DATA пуста. Тогда после выполнения указанного оператора она будет иметь следующее содержимое:
N_RASH
DAT_RASH
KOLVO
TOVAR
POKUP
8
20-JAN-1997
1000
Кока-кола
Адмирал, АО
7
20-JAN-1997
20
Кока-кола
<null>
6
20-JAN-1997
30
Сахар
Саяны, ИЧП
Оператор INSERT
Оператор INSERT применяется для добавления записей в объект. В качестве объекта может выступать ТБД или просмотр (VIEW), созданный оператором CREATE VIEW. В последнем случае записи могут добавляться в несколькотаблиц.
Формат оператора INSERT:
INSERT INTO <объект> [(столбец1 [, столбец2 ...])|
{VALUES (<значение1> [, <значение2> ...]) | <оператор SELECT> }
Список столбцов указывает столбцы, которым будут присвоены значения в добавляемых записях. Список столбцов может быть опущен. В этом случае подразумеваются все столбцы объекта, причем в том порядке, в котором они определены в данном объекте.
Поставить в соответствие столбцам списки значений можно двумя способами. Первый состоит в явном указании значений после слова VALUES, второй - в формировании значений при помощи оператора SELECT.
Ператор UPDATE
Оператор UPDATE применяется для изменения значения в группе записей или - в частном случае - в одной записи объекта. В качестве объекта могут выступать ТБД или просмотр, созданный оператором CREATE VIEW. В последнем случае могут изменяться значения записей из нескольких таблиц.
Формат оператора UPDATE:
UPDATE <объект>
SET столбец1 = <значение1> (,столбец2 = <значение2>...]
[WHERE <условие поиска >]
При корректировке каждому из перечисленных столбцов присваивается соответствующее значение. Корректировка выполняется для всех записей, удовлетворяющих условию поиска. Условие поиска задается так же, как в операторе SELECT.
ВНИМАНИЕ !
Если опустить WHERE <условие поиска>, в объекте будут изменены все записи.
Пример.
В таблице RASHOD заменить дату на "24.01.97" и количество увеличить на 2 единицы для всех записей с датой "20.01.97":UPDATE RASHOD
SET DAT_RASH = "24-JAN-1997", KOLVO = KOLVO + 2
WHERE DAT_RASH = "20-JAN-1997"
Содержимое таблицы RASHOD до выполнения оператора UPDATE:
| NRASH | DATRASH | KOLVO | TOVAR | POKUP |
| 5 | 10-JAN-1997 | 4000 | Кока-кола | Саяны, ИЧП |
| 1 | 10-JAN-1997 | 100 | Кока-кола | Адмирал, АО |
| 2 | 10-JAN-1997 | 20 | Сахар | Лира, ТОО |
| 3 | 10-JAN-1997 | 509 | Сахар | <null> |
| 4 | 10-JAN-1997 | 3000 | Ставрида консерв | Адмирал, АО |
| 8 | 20-JAN-1997 | 1000 | Кока-кола | Адмирал, АО |
| 7 | 20-JAN-1997 | 20 | Кока-кола | <null> |
| 6 | 20-JAN-1997 | 30 | Сахар | Саяны, ИЧП |
Содержимое таблицы RASHOD после выполнения оператора UPDATE:
| NRASH | DATRASH | KOLVO | TOVAR | POKUP |
| 5 | 10-JAN-1997 | 4000 | Кока-кола | Саяны, ИЧП |
| 1 | 10-JAN-1997 | 100 | Кока-кола | Адмирал, АО |
| 2 | 10-JAN-1997 | 20 | Сахар | Лира, ТОО |
| 3 | 10-JAN-1997 | 509 | Сахар . | <null> |
| 4 | 10-JAN-1997 | 3000 | Ставрида консерв. | Адмирал, АО |
| 8 | 24-JAN-1997 | 1002 | Кока-кола | Адмирал, АО |
| 7 | 24-JAN-1997 | 22 | Кока-кола | <null> |
| 6 | 24-JAN-1997 | 32 | Сахар | Саяны, ИЧП |
Оператор DELETE
Оператор DELETE предназначен для удаления группы записей из объекта. В качестве объекта могут выступать ТБД или просмотр, созданный оператором CREATE VIEW. В последнем случае могут удаляться записи из нескольких таблиц. В частном случае может быть удалена только одна запись.
Формат оператора DELETE:
DELETE FROM <объект> [WHERE <условие поиска>];
Удаляются все записи из объекта, удовлетворяющие условию поиска. Условие поиска задается так же, как в операторе SELECT.
ВНИМАНИЕ
! Если опустить WHERE <условие поиска>, из объекта будут удалены все записи.Пример.
Удалить из таблицы RASHOD все записи о расходе товара "Кока-кола" за дату "10.01.97".DELETE FROM RASHOD
WHERE (TOVAR = "Кока-кола") AND (DAT_RASH = "10-JAN-1997")
Добавление, изменение, удаление записей
Язык SQL ориентирован на выполнение операций над группами записей, хотя в некоторых случаях операция может проводиться и над отдельной записью. Поэтому неудивительно, что операторы добавления, изменения и удаления записей в общем случае вызывают соответствующие операции над группами записей.
ПОЯСНЕНИЕ.
Все приводимые ниже операторы выполнялись в утилите InterBase WISQL. Результаты выполнения операторов также показываются в виде, возвращаемом утилитой WISQL. Следует обратить внимание на представление дат - в WISQL они показываются в строковом виде. Не стоит беспокоиться о совместимости столбцов типа дата, когда выполняются операторы INSERT, UPDATE или DELETE при посредстве компонента TQuery из приложения, написанного на Delphi. Даты InterBase в приложениях, написанных на Delphi, без проблем представляются компонентами "поле типа даты и времени" TDateTimeField и отдельными переменными типа TDateTime.Понятие просмотра как виртуальной таблицы
В БД может быть определен просмотр, являющий собой виртуальную таблицу, в которой представлены записи из одной или нескольких таблиц. Порядок формирования записей в просмотре определяется оператором SELECT. Для создания просмотра применяется оператор
CREATE VIEW ИмяПросмотра [(столбец_view1 [,столбец_view ...])]
AS <onepaтop_select> [WITH CHECK OPTION];
где после ИмениПросмотра следует необязательный список столбцов, оператор_select есть полнофункциональный оператор SELECT, а необязательный параметр WITH CHECK OPTION определяет, допускать ли для обновляемых просмотров ввод записей, не удовлетворяющих условию формирования просмотра.
Для удаления просмотра используется оператор
DROP VIEW ИмяПросмотра;
Пример.
Создать просмотр, содержащий дату расхода, наименование товара, количество расхода товара из таблицы RASHOD и цену товара из таблицы TOVAR:CREATE VIEW FULL_RASHOD AS
SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, T.ZENA
FROM RASHOD R, TOVARY T
WHERE R.TOVAR = T.TOVAR;
После этого к просмотру FULL_RASHOD можно обращаться как к обычной таблице БД:
SELECT * FROM FULL_RASHOD;
Преимущества создания просмотров:
• единожды определив просмотр, не нужно всякий раз формировать оператор SELECT; это важно для сложных операторов SELECT, выполняющих соединение одной или нескольких таблиц;
• просмотр может предоставлять подмножество столбцов из таблицы, что важно для обеспечения сохранности данных и, возможно, усиления безопасности.
Способы формирования просмотров
Просмотр может создаваться как:
1) вертикальный срез таблицы,
когда в просмотр включается подмножество столбцов таблицы, например:CREATE VIEW RASH_VERT AS
SELECT DAT_RASH, TOVAR, KOLVO
FROM RASHOD
2) горизонтальный срез таблицы,
когда в просмотр включаются все столбцы, но не все записи таблицы, например:CREATE VIEW RASH_HORIZ AS
SELECT * FROM RASHOD WHERE TOVAR = "Кока-кола";
3) вертикально-горизонтальный срез таблицы, когда в просмотр включается подмножество столбцов и подмножество строк, например:
CREATE VIEW RASH_VERT_HORIZ AS
SELECT DAT_RASH, TOVAR, KOLVO FROM RASHOD WHERE TOVAR = "Кока-кола";
4) подмножество строк и столбцов соединения разных таблиц, например:
CREATE VIEW FULL_RASHOD AS
SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, T.ZENA
FROM RASHOD R, TOVARY T WHERE R.TOVAR = T.TOVAR;
Указание столбцов просмотра в операторе CREATE VIEW
Имена столбцов просмотра должны указываться в операторе CREATE VIEW в том случае, когда в качестве столбца просмотра определяется выражение, например:
CREATE VIEW STOIM_RASHOD (DAT_RASH, TOVAR, STOIM) AS
SELECT R.DAT_RASH, R.TOVAR, R.KOLVO * T.ZENA
FROM RASHOD R, TOVARY T WHERE R.TOVAR = T.TOVAR;
В противном случае имена столбцов просмотра указывать не обязательно. В случае, если список имен столбцов опущен, имена столбцов считаются идентичными именам полей, возвращаемых оператором SELECT.
Обновляемые и необновляемые просмотры
Чтобы просмотр можно было обновлять, то есть применять к нему операции добавления, изменения и удаления записей, необходимо одновременное выполнение трех условий:
• просмотр должен формироваться из записей только одной таблицы;
• в просмотр должен быть включен каждый столбец таблицы, имеющий атрибут NOT NULL;
• оператор SELECT просмотра не должен использовать агрегирующих функций, режима DISTINCT, предложения HAVING, соединения таблиц, хранимых процедур и функций, определенных пользователем.
Если просмотр удовлетворяет этим условиям, к нему могут применяться операторы
INSERT, UPDATE и DELETE.
Пример.
В следующем просмотре можно добавлять, корректировать и удалять записи:CREATE VIEW UPDATABLE_RASH AS
SELECT N_RASH,DAT_RASH, TOVAR, KOLVO FROM RASHOD;
Для того чтобы к просмотру можно было применить операторы UPDATE и DELETE, для него одновременно должны выполняться два условия:
• просмотр должен формироваться из записей только одной таблицы;
• оператор SELECT просмотра не использует агрегатных функций, режима DISTINCT, предложения HAVING, соединения таблиц, хранимых процедур и функций, определенных пользователем.
Пример.
В следующем просмотре можно корректировать и удалять записи, но нельзя добавлять:CREATE VIEW LESSUPDAPTABLE_RASH AS
SELECT DAT_RASH, TOVAR, KOLVO, POKUP FROM RASHOD;
Пример.
В следующем просмотре нельзя добавлять, корректировать и удалять записи:CREATE VIEW A AS
SELECT R.TOVAR, T.ZENA
FROM RASHOD R, TOVARY T WHERE R.TOVAR = T.TOVAR;
Использование CHECK OPTION
Если для обновляемого просмотра указан параметр CHECK OPTION, будут отвергаться все попытки добавления новых или изменения существующих записей таким образом, чтобы нарушалось условие WHERE оператора SELECT данного просмотра.
Пример.
В данный просмотр невозможно добавить записи со значением поля KOLVO, меньшим 1000:CREATE VIEW RASH_1000_CHECK AS
SELECT * FROM RASHOD WHERE KOLVO > 1000 WITH CHECK OPTION;
Компоненты Delphi и использование просмотров
Просмотр есть виртуальная таблица БД. "Виртуальная" означает буквально следующее: при работе с просмотром возникает впечатление, что эта таблица физически существует, хотя на самом деле это не так. Поэтому компонент TTable может содержать имя просмотра в свойстве TableName, а компонент TQuery использовать SQL-запрос, в котором наравне с "нормальными" таблицами производится обращение и к просмотру. Поведение просмотра в приложении аналогично поведению обычной таблицы БД, с учетом особенностей, определяемых параметром CHECK OPTION и возможностью полного (добавление, удаление, изменение), частичного (изменение, удаление записей) обновления просмотра или невозможностью такового. Следует помнить, что подобно НД, возвращаемому оператором SELECT, НД, возвращаемый просмотром, показывает записи в состоянии, в котором они были на момент открытия набора данных компонента TTable или TQuery. Для того чтобы внесенные после открытия НД изменения стали актуальны в просмотре, компонент, использующий просмотр, должен быть переоткрыт.
Понятие хранимой процедуры
Хранимая процедура - это модуль, написанный на процедурном языке InterBase и хранящийся в базе данных как метаданные (то есть как данные о данных). Хранимую процедуру можно вызывать из приложения.
Существует две разновидности хранимых процедур: процедуры выбора и процедуры действия.
Процедуры выбора
могут возвращать более одного значения. В приложении имя хранимой процедуры выбора подставляется в оператор SELECT вместо имени таблицы или обзора.Процедуры
действия вообще могут не возвращать данных и используются для реализации каких-либо действий.Хранимым процедурам можно передавать параметры и получать обратно значения параметров, измененные в соответствии с алгоритмами хранимых процедур.
Преимущества использования хранимых процедур:
• способность одной процедуры, расположенной на сервере, совместно использоваться многими приложениями;
• разгрузка приложений клиента путем переноса части кода на сервер и вследствие этого - упрощение клиентских приложений;
• при изменении хранимой процедуры на сервере все изменения немедленно становятся доступны для клиентских приложений; при внесении же изменений в приложение клиента требуется повторное распространение новой версии клиентского приложения между пользователями;
• улучшенные характеристики выполнения, связанные с тем, что хранимые процедуры выполняются сервером, что приводит, в частности, к уменьшению сетевого графика.
Создание хранимой процедуры
Хранимая процедура создается оператором
CREATE PROCEDURE ИмяПроцедуры
[ (входной_параметр тип_данньк [, входной_параметр тип_данных...])]
[RETURNS
(выходной_параметр тип_данньк [, выходной_параметр тип_данньк ...])] AS
<тело процедуры>;
Входные параметры
служат для передачи в процедуру значений из вызывающего приложения. Изменять значения входных параметров в теле процедуры бессмысленно: эти изменения будут забыты после окончания работы процедуры.Выходные параметры
служат для возврата результирующих значений. Значения выходных параметров устанавливаются в теле процедуры и после окончания ее работы передаются в вызывающее приложение.И входные, и выходные параметры могут быть опущены, если в них нет необходимости.
Тело процедуры имеет формат
[<объявление локальных переменных процедуры>]
BEGIN
< оператор>
[<оператор> ...]
END
Пример.
Хранимая процедура FIND_MAX_KOLVO возвращает в выходном параметре MAX_KOLVO максимальное количество отгруженного со склада товара, наименование которого передается во входном параметре IN_TOVAR:CREATE PROCEDURE FIND_MAX_KOLVO (IN_TOVAR VARCHAR(20))
RETURNS(MAX_KOLVO INTEGER) AS
BEGIN
SELECT MAX(KOLVO) FROM RASHOD WHERE TOVAR = : IN_TOVAR
INTO : MAX_KOLVO;
SUSPEND;
END
Заметим, что в приведенной процедуре за ненадобностью отсутствует определение локальных переменных.
Объявление локальных переменных
Локальные переменные, если они определены в процедуре, имеют срок жизни от начала выполнения процедуры и до ее окончания. Вне процедуры такие локальные переменные неизвестны и попытка обращения к ним вызовет ошибку. Локальные переменные используют для хранения промежуточных значений.
Формат объявления локальных переменных:
DECLARE VARIABLE <имя переменной > <тип данных>;
Пример объявления:
CREATE PROCEDURE FULL_ADR (TOVARCHIK VARCHAR(20))
RETURNS (GOROD_ADRES VARCHAR(40)) AS
DECLARE VARIABLE NAIDEN_POKUPATEL VARCHAR(20);
DECLARE VARIABLE MAX_KOLVO INTEGERS;
BEGIN
END
Операторные скобки BEGIN... END
Операторные скобки BEGIN ... END, во-первых, ограничивают тело процедуры, а во-вторых, могут использоваться для указания границ составного оператора.
Под простым оператором понимается единичное разрешенное действие, например:
РОК = "Покупатель не указан";
Под составным оператором понимается группа простых или составных операторов, заключенная в операторные скобки BEGIN ... END.
Оператор присваивания
Оператор присваивания служит для занесения значений в переменные. Его формат:
Имя переменной = выражение;
где в качестве выражения могут выступать переменные, арифметические и строковые выражения, в которых можно использовать встроенные функции, функции, определенные пользователем, а также генераторы. Пример:
OUT_TOVAR = UРРЕR(TOVAR);
Оператор IF... THEN ... ELSE
Условный оператор IF ... THEN ... ELSE имеет формат
IF (<условие>) THEN
< оператор 1>
[ELSE
< оператор 2>]
В случае, если условие истинно, выполняется оператор 1, если ложно -оператор 2.
Оператор SELECT
Оператор SELECT используется в хранимой процедуре для выдачи единичной сроки. По сравнению с синтаксисом обычного оператора SELECT, в процедурный оператор добавлено предложение
INTO :переменная |, переменная...]
Оно служит для указания переменных или выходных параметров, в которые должны быть записаны значения, возвращаемые оператором SELECT (те результирующие значения, которые перечисляются после ключевого слова SELECT).
Пример.
Приводимый ниже оператор SELECT возвращает среднее и сумму по столбцу KOLVO и записывает их соответственно в AVG_KOLVO и SUM_KOLVO, которые могут быть как локальными переменными, так и выходными параметрами процедуры. Расчет среднего и суммы по столбцу KOLVO производится только для записей, у которых значение столбца TOVAR совпадает с содержимым IN_TOVAR (входной параметр или локальная переменная).SELECT AVG(KOLVO), SUM(KOLVO)
FROM RASHOD WHERE TOVAR = :IN TOVAR
INTO : AVG_KOLVO, :SUM_KOLVO;
Оператор FOR SELECT... DO
Оператор FOR SELECT ... DO имеет следующий формат:
FOR
<оператор SELECT>
DO
< оператор>;
Оператор SELECT
представляется в расширенном синтаксисе оператора SELECT для алгоритмического языка хранимых процедур и триггеров, то есть в нем может присутствовать предложение INTO.Алгоритм работа оператора FOR SELECT... DO заключается в следующем. Выполняется оператор SELECT, и для каждой строки результирующего набора данных, возвращаемого данным SELECT, выполняется оператор, следующий за словом DO. Этим оператором часто бывает SUSPEND (см. ниже), который приводит к возврату выходных параметров в вызывающее приложение.
Пример.
Процедура RASHOD_TOVARA выдает все расходы товара для конкретного товара, определяемого содержимым входного параметра IN_TOVAR. Рассмотрим логику работы оператора FOR SELECT . . . DO данной процедуры.CREATE PROCEDURE RASHOD_TOVARA(IN_TOVAR VARCHAR(20))
RETURNS (OUT_DAT DATE, OUT_POKUP VARCHAR(20), OUT_KOLVO INTEGER) AS
BEGIN
FOR SELECT DAT_RASH, POKUP, KOLVO FROM RASHOD WHERE TOVAR = : IN_TOVAR
INTO :OUT DAT, :OUT_POKUP, :OUT_KOLVO
DO SUSPEND;
END
Выполняется оператор SELECT, который возвращает дату расхода, наименование покупателя и количество расхода товара для каждой записи, у которой столбец TOVAR содержит значение, идентичное значению во входном параметре IN_TOVAR. Указанные значения записываются в выходные параметры (соответственно OUT_DAT, OUT_POKUP, OUT_KOLVO). Имени параметра в этом случае предшествует двоеточие. После выдачи каждой записи результирующего НД выполняется оператор, следующий за словом DO. В данном случае это оператор SUSPEND. Он возвращает значения выходных параметров вызвавшему приложению и приостанавливает выполнение процедуры до запроса следующей порции выходных параметров от вызывающего приложения.
Такая процедура является процедурой выбора, поскольку она может возвращать множественные значения выходных параметров в вызывающее приложение. Обычно запрос к такой хранимой процедуре из вызывающего приложения осуществляется при помощи оператора SELECT, например:
SELECT MAX_KOLVO FROM FIND_MAX_KOLVO("Сахар")
Оператор SUSPEND
Оператор SUSPEND передает в вызывающее приложение значения результирующих параметров (перечисленных после слова RETURNS в описании функции), имеющие место на момент выполнения SUSPEND. После этого выполнение хранимой процедуры приостанавливается. Когда от оператора SELECT вызывающего приложения приходит запрос на следующее значение выходных параметров, выполнение хранимой процедуры возобновляется.
Пример.
Процедура POK_LIST выдает всех покупателей, у которых имеются покупки данного товара, в количестве, превосходящем средний размер покупки по данному товару. В случае, если наименование покупателя - пустое, вместо имени покупателя выводится "Покупатель не указан".CREATE PROCEDURE POK_LIST (IN_TOVAR VARCHAR(20))
RETURNS(РОК VARCHARf20)) AS
DECLARE VARIABLE AVG_KOLVO INTEGER;
BEGIN
SELECT AVG(KOLVO) FROM RASHOD WHERE TOVAR = :IN_TOVAR INTO : AVG_KOLVO;
FOR SELECT POKUP FROM RASHOD WHERE KOLVO > : AVG_KOLVO INTO : РОК
DO
BEGIN
IF (:POK IS NULL) THEN РОК = "Покупатель не указан";
SUSPEND;
END
END
Оператор WHILE... DO
Оператор имеет формат
WHILE (<условие>) DO < оператор>
Алгоритм выполнения оператора- в цикле проверяется выполнение условия, если оно истинно, выполняется оператор. Цикл продолжается до тех пор, пока условие не перестанет выполняться.
Пример.
Рассмотрим процедуру SUM_0_N, которая подсчитывает сумму всех чисел от 0 до числа, определяемого входным параметром N Вычисление суммы реализовано в цикле с использованием оператора WHILE .. DO.CREATE PROCEDURE SUM_0_N (N INTEGER)
RETURNS(S INTEGER) AS
DECLARE VARIABLE TMP INTEGER;
BEGIN
S = 0;
TMP = 1;
WHILE (TMP <= N) DO
BEGIN
S = S + TMP;
TMP = TMP + 1;
END
END
Оператор EXIT
Оператор EXIT инициирует прекращение выполнения процедуры и выход в вызывающее приложение.
Пример.
Процедура MAX_VALUE возвращает максимум из двух чисел, передаваемых как входные параметры, в случае, если одно из чисел имеет значение NULL, процедура завершается (в этом случае выходной параметр содержит значение NULL)'CREATE PROCEDURE MAX_VALUE(A INTEGER, В INTEGER)
RETURNS(M_V INTEGER) AS
BEGIN
IF ((:A IS NULL) OR (:B IS NULL )) THEN EXIT;
IF (:A > :B) THEN M_V = : A;
ELSE M_V = :B;
END
Оператор EXECUTE PROCEDURE
Оператор
EXECUTE PROCEDURE имя [параметр (, параметр...]];
[RETURNING_VALUES параметр [, параметр...]];
Выполняет другую хранимую процедуру из тела данной хранимой процедуры. При этом после слова PROCEDURE перечисляются входные параметры, если они есть, а после RETURNING_VALUES перечисляются выходные параметры.
Пример
Перепишем приведенную выше процедуру POK_LIST таким образом, чтобы из ее тела вызывалась другая процедура, AVG_KOLVO, возвращающая среднее число отпуска конкретного товараCREATE PROCEDURE AVG_KOLVO (TVR VARCHAR(20)
RETURNS(OUT_AVG_KOLVO INTEGER)
AS
BEGIN
SELECT AVG(KOLVO) FROM RASHOD WHERE TOVAR = :TVR INTO :OUT_AVG_KOLVO;
SUSPEND;
END
CREATE PROCEDURE POK_LIST1 (IN_TOVAR VARCHAR(20))
RETURNS(РОК VARCHAR(20)) AS
DECLARE VARIABLE AVG_KOLVO INTEGER;
BEGIN
EXECUTE PROCEDURE AVG_KOLVO(:IN_TOVAR)
RETURNING_VALUES : AVG_KOLVO;
FOR SELECT POKUP FROM RASHOD WHERE KOLVO > : AVG_KOLVO INTO : РОК
DO
BEGIN
IF (:POK IS NULL) THEN РОК = "Покупатель не указан";
SUSPEND;
END
END
Оператор POST_EVENT
Оператор POST_EVENT "Имя события";
применяется для посылки сервером клиентским приложениям сообщения о наступлении какой-либо ситуации, связанной с именем события. Приложение должно зарегистрироваться на сервере для получения уведомления о наступлении событий и указать список интересующих приложение событий. Более подробно см. раздел "Работа с событиями".
Алгоритмический язык хранимых процедур
Для написания тела хранимой процедуры применяют особый алгоритмический язык. Помимо хранимых процедур, данный язык применяется также для написания триггеров.
Рассмотрим конструкции алгоритмического языка хранимых процедур и триггеров.
Вызов процедур выбора в приложении клиента
Процедуры выбора могут возвращать несколько значений одного и того же выходного параметра или группы выходных параметров. Возврат текущего значения выходных параметров соответствует возврату строки. Таким образом, процедура выбора возвращает НД, состоящий в общем случае из нескольких строк. Для выдачи каждого значения выходных параметров в вызывающее приложение в хранимой процедуре выбора применяется оператор SUSPEND. Формирование строки результирующего НД (то есть текущих значений выходных параметров) производится операторами SELECT...INTO или FOR...SELECT.
Для обращения к хранимой процедуре выбора в приложении клиента используется компонент TQuery. Вызов хранимой процедуры производится в операторе SELECT, в предложении FROM, с указанием входных параметров процедуры. Выходные параметры процедуры (все или часть) указываются в качестве возвращаемых значений оператора SELECT.
ЗАМЕЧАНИЕ.
Для обращения к хранимой процедуре выбора может использоваться и компонент TStoredProc, открываемый при помощи метода Open. Однако в общем случае возможность обращения к процедуре выбора из компонента TStoredProc определяется особенностями SQL-сервера. К хранимым процедурам выбора, определенным в Borland InterBase, рекомендуется обращаться из оператора SELECT, который помещается в свойство SQL компонента TQuery.Пример.
Выше приводилась процедура выбора RASHOD_TOVARA, которая выдает все расходы товара для конкретного товара, определяемого содержимым входного параметра IN_TOVAR:CREATE PROCEDURE RASHOD_TOVARA(IN_TOVAR VARCHAR(20))
RETURNS (OUT_DAT DATE, OUT_POKUP VARCHAR(20), OUT_KOLVO
INTEGER) AS
BEGIN
FOR SELECT DAT_RASH, POKUP, KOLVO FROM RASHOD WHERE TOVAR = : IN_TOVAR
INTO :OUT_DAT, :OUT_POKUP, :OUT_KOLVO
DO
SUSPEND;
END
Для того чтобы обратиться к данной процедуре из клиентского приложения, разработанного на Delphi, создадим форму (рис. 28.1) разместив в ней:
1. компонент TDatabase, осуществляющий управление соединением с удаленной БД;
2. компонент TTable, ассоциированный с таблицей TOVARY, для выбора названия товара для передачи в хранимую процедуру как входного параметра;
3. компонент TQuery;
4. компонент TButton для инициации доступа к процедуре.
В свойстве SQL компонента TQuery необходимо определить SQL-запрос на обращение к процедуре:
SELECT *
FROM RASHOD_TOVARA(:PARAM1)
а в свойстве Params компонента TQuery необходимо указать тип параметра PARAM1 как String. В обработчике нажатия кнопки происходит присваивание параметру значения текущего названия товара из компонента TTable, после чего компонент TQuery активизируется:
procedure TForm1.ButtonlClick(Sender: TObject);
begin
Query1.Close;
Query1.ParamByName('param1').Value :=
Table1.FieldByName('TOVAR').Value;
Query1.Open; end;
Результаты работы приложения показаны на рисунке 28.2.
Изменение и удаление хранимых процедур
Изменение хранимой процедуры производится оператором
ALTER PROCEDURE ИмяПроцедуры
[ (входной_параметр тип_данньк [, входной_параметр тип_данных... ]) ]
[RETURNS
(входной параметр тип данных [.входной параметр тип данных ...])]
AS
<тело процедуры>;
Принципы построения оператора аналогичны изложенным выше принципам построения оператора CREATE PROCEDURE. После выполнения оператора ALTER PROCEDURE предыдущее определение процедуры заменяется на данное определение параметров, переменных и тела процедуры.
Для удаления хранимой процедуры из базы данных используется оператор
DROP PROCEDURE ИмяПроцедуры;
Создание триггеров
Триггер создается оператором
CREATE TRIGGER ИмяТриггера FOR ИмяТаблицы
[ACTIVE I INACTIVE]
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE}
[POSITION номер]
AS <тело триггера>
Для определения тела триггера используется процедурный язык, рассмотренный в разделе, посвященном использованию хранимых процедур. В него добавляется возможность доступа к старому и новому значениям столбцвв изменяемой записи OLD и NEW - возможность, недоступная при определении тела хранимых процедур.
Структура отела триггера:
[<объявление локальных переменных процедуры>]
BEGIN
< оператор>
END
Определение заголовка триггера
Заголовок триггера имеет формат
... TRIGGER ИмяТриггера FOR ИмяТаблицы
[ACTIVE | INACTIVE]
{BEFORE | AFTER}
{DELETE | INSERT UPDATE}
[POSITION номер]
• ACTIVE | INACTIVE - указывает, активен триггер или нет. Можно определить триггер "про запас", установив для него INACTIVE. В дальнейшем можно переопределить триггер как активный. По умолчанию действует ACTIVE.
• BEFORE | AFTER - указывает, будет выполняться триггер до (BEFORE) или после (AFTER) запоминания изменений в БД.
• DELETE \ INSERT \ UPDATE - указывает операцию над ТБД, при выполнении которой срабатывает триггер.
• POSITION номер - указывает, каким по счету будет выполняться триггер в случае наличия группы триггеров, обладающих одинаковыми характеристиками операции и времени (до, после операции) вызова триггера. Значение номера задается числом в диапазоне 0..32 767. Триггеры с меньшими номерами выполняются раньше.
Например, если определены триггеры
CREATE TRIGGER A FOR RASHOD BEFORE INSERT POSITION 1 ...
CREATE TRIGGER С FOR RASHOD BEFORE INSERT POSITION 0...
CREATE TRIGGER D FOR RASHOD BEFORE INSERT POSITION 44 ...
CREATE TRIGGER В FOR RASHOD AFTER INSERT POSITION 1 ...
CREATE TRIGGER E FOR RASHOD AFTER INSERT POSITION 44 ...
для операции добавления новой записи в таблицу RASHOD они будут выполнены в следующей последовательности С, А, D, В, E.
Значения OLD и NEW
Значение OLD. Имя Столбца позволяет обратиться к состоянию столбца, имевшему место до внесения возможных изменений, а значение NEW. Имя Столбца - к состоянию столбца после внесения возможных изменений.
В том случае, если значение в столбце не изменилось, OLD ИмяСтолбца будет равно NEW ИмяСтолбца.
Пример.
Если в записи таблицы TOVARY изменилось значение столбца TOVAR, соответствующие изменения должны быть внесены в таблицу RASHOD:CREATE TRIGGER BU_TOVARY FOR TOVARY
ACTIVE
BEFORE UPDATE
AS
BEGIN
IF (OLD.TOVAR 0 NEW.TOVAR) THEN UPDATE RASHOD
SET TOVAR = NEW.TOVAR
WHERE TOVAR = OLD.TOVAR;
END
Обеспечение каскадных воздействий
Если между двумя или более ТБД установлены отношения ссылочной целостности (отношения "один-ко-многим", "один-к-одному"), при изменении столбца связи в родительской таблице должно быть изменено значение столбца связи у записей соответствующих дочерних таблиц. Такое воздействие на дочернюю таблицу носит название каскадного обновления. Если в родительской таблице удалена запись, должны быть удалены все связанные с ней записи в дочерней таблице. Такое воздействие на дочернюю таблицу носит название каскадного удаления.
Ограничение ссылочной целостности таблиц по внешнему ключу приводит к блокировке изменения и удаления записи в родительской таблице, если для нее есть дочерние записи в дочерней таблице:
CREATE TABLE TOVARY
(TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL, PRIMARY KEY(TOVAR));
CREATE TABLE RASHOD
(N_RASH INTEGER NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
PRIMARY KEY(N_RASH),
FOREIGN KEY(TOVAR) REFERENCES TOVARY
);
Для реализации автоматического выполнения каскадных обновлений и изменений необходимо, во-первых, удалить ссылочные целостности, блокирующие такие изменения в определении БД и, во-вторых, определить сами триггеры для родительской таблицы.
Триггер, реализующий каскадное обновление в дочерней таблице, будет в числе прочих содержать оператор
IF (OLD.ПoлeCвязиPoдитeля <> NEW.ПoлeCвязиPoдитeля) THEN
UPDATE ДочерняяТаблица
SET ПолеСвязиДочернейТаблицы = NEW.ПолеСвязиРодителя
WHERE ПолеСвязиДочернейТаблицы = OLD.ПолеСвязиРодителя ;
Триггер, реализующий каскадное удаление в дочерней таблице, будет в числе прочих содержать оператор
DELETE FROM ДочерняяТаблица
WHERE ПолеСвязиДочернейТаблицы =ПолеСвязиРодителя ;
Пример.
Напишем триггеры, выполняющие каскадные обновления и каскадные удаления в дочерней таблице RASHOD после соответственно изменения значения столбца связи или удаления записи в родительской таблице TOVARY:CREATE TRIGGER BUJTOVARY FOR TOVARY
ACTIVE
BEFORE UPDATE
AS
BEGIN
IF (OLD.TOVAR 0 NEW.TOVAR) THEN UPDATE RASHOD
SET TOVAR = NEW.TOVAR
WHERE TOVAR = OLD.TOVAR;
END
CREATE TRIGGER AD_TOVARY FOR TOVARY
ACTIVE
AFTER DELETE
AS
BEGIN
DELETE FROM RASHOD
WHERE RASHOD.TOVAR = TOVARY.TOVAR;
END
Ведение журнала изменений
Журнал изменений в БД представляет собой таблицу БД, в которой фиксируются действия над всей базой данных или отдельными ее таблицами. В многопользовательских системах ведение такого журнала позволяет определить источник недостоверных или искаженных данных.
Пример.
Определим в базе данных таблицу TOVARY_LOGCREATE TABLE TOVARYJLOG(
DAT_IZM DATE, /* дата изменения */
DEISTV CHAR(3), /* операция */
OLD_TOVAR VARCHAR(20), /* старое значение TOVAR*/
NEW_TOVAR VARCHAR(20) /* новое значение TOVAR */
) ;
в которую будем автоматически записывать любые изменения, добавления, удаления в таблице ТО VARY. При этом будем фиксировать дату, операцию (INS, UPD, DEL) над таблицей TOVARY, а также старое и новое значение столбца TOVAR. Для операции удаления новое значение столбца TOVAR будет пустым. Для операции добавления пустым будет старое значение столбца TOVAR.
CREATE TRIGGER TOVARY_ADD_LOG FOR TOVARY
ACTIVE
AFTER INSERT
AS
BEGIN
INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_T,OVAR, NEW_TOVAR)
VALUES ("NOW","ADD","",NEW.TOVAR) ;
END
CREATE TRIGGER TOVARY_UPD_LOG FOR TOVARY
ACTIVE
AFTER UPDATE
AS
BEGIN
INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_TOVAR,
NEW_TOVAR)
VALUES ("NOW","UPD",OLD.TOVAR,NEW.TOVAR) ;
END
CREATE TRIGGER TOVARY_DEL_LOG FOR TOVARY
ACTIVE
AFTER UPDATE
AS
BEGIN
INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_TOVAR, NEW_TOVAR)
VALUES ("NOW","DEL",OLD.TOVAR,"");
END
Пусть в таблицу TOVARY внесены некоторые изменения. Тогда, выполнив оператор
SELECT * FROM TOVARY_LOG;
получим историю изменений в таблице TOVARY:
DAT_IZM DEISTV OLD_TOVAR NEW_TOVAR
30-JUN-1997 ADD оГУРЦЫ
30-JUN-1997 UPD оГУРЦЫ Огурцы
30-JUN-1997 DEL оГУРЦЫ
Использование триггеров для реализации бизнес-правил
Триггеры активно используются для реализации бизнес-правил. В частности, это может быть установка с помощью генераторов уникальных значений индексных полей, накапливание статистики в других таблицах и многое другое. К сложностям, возникающим при реализации бизнес-правил при помощи триггеров, следует отнести неразвитость средств отладки логики кода, составляющего тело триггеров.
Покажем на примерах реализацию некоторых бизнес-правил при помощи триггеров.
Пример.
Пусть столбец N_RASH в таблице RASHOD должен содержать уникальное значение. Для этой цели определим генератор RASHOD_N_RASH и установим его начальное значение 20.CREATE GENERATOR RASHOD_N_RASH;
SET GENERATOR RASHOD_N_RASH TO 20;
При добавлении новой записи будем присваивать столбцу N_RASH вновь добавляемой записи уникальное значение, полученное при помощи генератора.
CREATE TRIGGER BI_RASHOD_GEN FOR RASHOD
ACTIVE
BEFORE INSERT
BEGIN
NEW.N_RASH = GEN_ID(RASHOD_N_RASH,1);
END
Пример.
Пусть в БД имеется таблица STAT_TOVARY, в которой на каждую дату накапливается количество отпущенного товара:CREATE TABLE STAT_TOVARY(
DAT_RASH DATE NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
KOLVO INTEGER NOT NULL
) ;
Тогда следующие триггеры реализуют автоматическое заполнение корректных значений в эту таблицу после добавления, изменения и удаления записи в таблице RASHOD, содержащей сведения о расходе товаров со склада:
CREATE TRIGGER AI_RASHOD FOR RASHOD
ACTIVE
AFTER INSERT
AS
DECLARE VARIABLE CNT INTEGER; DECLARE VARIABLE OLD_KOLVO_VAL INTEGER;
BEGIN
/* выбрать число записей в таблице STAT_TOVARY по данному товару за дату расхода */
SELECT COUNT(*) FROM STAT_TOVARY
WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR) INTO :CNT;
/* если число записей = 0, добавить запись в таблицу STAT_TOVARY по данному товару и дате */
IF (:CNT = 0) THEN
INSERT INTO STAT_TOVARY (DAT_RASH, TOVAR, KOLVO)
VALUES(NEW.DAT_RASH, NEW.TOVAR, NEW.KOLVO) ;
ELSE
/* иначе добавить новое количество товара в уже существующей записи для этого товара и этой даты в STAT_TOVARY */
BEGIN
SELECT KOLVO FROM STAT_TOVARY
WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR)
INTO :OLD_KOLVO_VAL;
UPDATE STAT_TOVARY
SET KOLVO = :OLD_KOLVO_VAL + NEW.KOLVO
WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR);
END
END
CREATE TRIGGER AU_RASHOD FOR RASHOD
ACTIVE
AFTER UPDATE
AS
DECLARE VARIABLE CNT INTEGER;
DECLARE VARIABLE OST_KOLVO INTEGER;
DECLARE VARIABLE OLD_KOLVO_VAL INTEGER;
BEGIN
/* в таблице статистики STAT_TOVARY найти общее количество расхода старого товара по старой дате */
/* из таблицы RASHOD */
SELECT KOLVO FROM STAT_TOVARY
WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR)
INTO :OLD_KOLVO_VAL;
/* в таблице статистики STAT_TOVARY уменьшить общее число прихода старого товара */
/* на старое значение количества расхода из таблицы RASHOD */
UPDATE STAT_TOVARY
SET KOLVO = :OLD_KOLVO_VAL - OLD.KOLVO
WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR) ;
OST_KOLVO = OLD_KOLVO_VAL - OLD.KOLVO;
/* если оставшееся количество расхода за эту дату по этому товару равно 0, удалить */
/* запись из таблицы STAT_TOVARY */
IF (:OST_KOLVO = 0) THEN
DELETE FROM STAT_TOVARY
WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR);
/* выбрать число записей в таблице STAT_TOVARY по новому товару за новую дату расхода */
SELECT COUNT (*) FROM STAT_TOVARY
WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR)
INTO :CNT;
/* если число записей = 0, добавить запись в таблицу STAT_TOVARY по новому товару и новой дате */
IF (:CNT = 0) THEN
INSERT INTO STAT_TOVARY (DAT_RASH, TOVAR, KOLVO)
VALUES(NEW.DAT_RASH, NEW.TOVAR, NEW.KOLVO) ;
ELSE
/* иначе добавить новое количество товара в уже существующей записи для данного товара*/
/* и новой даты в STAT_TOVARY*/
BEGIN
SELECT KOLVO FROM STAT_TOVARY
WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR)
INTO :OLD_KOLVO_VAL;
UPDATE STAT_TOVARY
SET KOLVO = :OLD_KOLVO_VAL + NEW.KOLVO
WHERE (STAT_TOVARY.DAT_RASH = NEW.DAT_RASH) AND (STAT_TOVARY.TOVAR = NEW.TOVAR);
END
END
CREATE TRIGGER ADL_RASHOD FOR RASHOD
ACTIVE
AFTER DELETE
AS
DECLARE VARIABLE OST_KOLVO INTEGER; DECLARE VARIABLE OLD_KOLVO_VAL INTEGER;
BEGIN
/* в таблице статистики STAT_TOVARY найти общее количество расхода товара за дату из таблицы RASHOD */
SELECT KOLVO FROM STAT_TOVARY
WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR)
INTO :OLD_KOLVO_VAL;
/* в таблице статистики STAT_TOVARY уменьшить общее число прихода товара */
/* на значение количества расхода товара из удаленной таблицы RASHOD */
UPDATE STAT_TOVARY
SET KOLVO = :OLD_KOLVO_VAL - OLD.KOLVO
WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR);
OST_KOLVO = OLD_KOLVO_VAL - OLD.KOLVO;
/* если оставшееся количество расхода по товару за эту дату равно 0, удалить запись из таблицы STAT TOVARY
IF (:OST_KOLVO = 0) THEN
DELETE FROM STAT_TOVARY
WHERE (STAT_TOVARY.DAT_RASH = OLD.DAT_RASH) AND (STAT_TOVARY.TOVAR = OLD.TOVAR);
END
Покажем состояние таблицы STAT_TOVARY после добавления в таблицу RASHOD новых записей по расходу за 12 и 14 февраля:
SELECT * FROM STAT_TOVARY ORDER BY DAT_RASH, TOVAR
DATRASH
TOVAR
KOLVO
12-JAN-1997
Кока-кола
23
12-JAN-1997
Сахар
100
14-JAN-1997
Кока-кола
3
Пусть после изменения в таблице RASHOD информации о расходе товара за 12 февраля:
• вместо 11 единиц товара "Кока-кола" в действительности пришла 21 единица товара "Ставрида консерв.";
• вместо 100 единиц товара "Сахар" пришло 98 единиц этого товара.
Покажем состояние таблицы STAT_TOVARY после внесения в таблицу RASHOD указанных изменений:
SELECT * FROM STAT_TOVARY ORDER BY DAT_RASH, TOVAR
DATRASH
TOVAR
KOLVO
12-JAN-1997
Кока-кола
12
12-JAN-1997
Сахар
98
12-JAN-1997
Ставрида консерв.
21
14-JAN-1997
Кока-кола
3
Покажем состояние таблицы STAT_TOVARY после удаления расхода за 12 февраля товара "Сахар в размере 98 ед. и за 14 февраля товара "Кока-кола в размере 2 ед. из таблицы RASHOD:
SELECT * FROM STAT_TOVARY ORDER BY DAT_RASH, TOVAR
DATRASH
TOVAR
KOLVO
12-JAN-1997
Кока-кола
12
12-JAN-1997
Ставрида консерв.
21
14-JAN-1997
Кока-кола
1
Изменение и удаление триггеров
Изменить существующий триггер можно при помощи оператора
ALTER TRIGGER ИмяТриггера FOR ИмяТаблицы
[ACTIVE | INACTIVE]
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE}
[POSITION номер]
AS <тело триггера>
После выполнения этого оператора все старые определения триггера заменяются на определения, указанные в операторе ALTER TRIGGER
Для удаления триггера следует воспользоваться оператором
DROP TRIGGER ИмяТриггера;
Работа с триггерами
Триггер -
это процедура БД, автоматически вызываемая SQL-сервером при обновлении, удалении или добавлении новой записи в ТБД. Непосредственно из программы к триггерам обратиться нельзя. Нельзя и передавать им входные параметры и получать от них значения выходных параметров. Триггеры всегда реализуют действие.По событию изменения ТБД триггеры различаются на вызываемые при:
• добавлении новой записи;
• изменении существующей записи;
• удалении записи.
По отношению к событию, влекущему их вызов, триггеры различаются на:
• выполняемые до наступления события;
• выполняемые после наступления события. Преимущества использования триггеров:
• автоматическое обеспечение каскадных воздействий в дочерних таблицах при изменении, удалении записи в родительской таблице выполняется на сервере. Пользователю нет необходимости заботиться о программной реализации каскадных воздействий. Поскольку каскадные воздействия выполняет сервер, нет необходимости пересылать изменения в таблицах БД из приложения на сервер, что снижает загрузку сетевого трафика;
• изменения в триггерах не влекут необходимости изменения программного кода в клиентских приложениях и не требуют распространения новых версий клиентских приложений у пользователей.
ЗАМЕЧАНИЕ.
При откате транзакции откатываются также и все изменения, внесенные в БД триггерами.Просмотр метаданных
Чтобы увидеть метаданные БД, выберите нужный псевдоним в левом окне утилиты и нажмите знак '+' для раскрытия дерева метаданных. При этом для удаленных БД будут запрошены имя пользователя и пароль . В правом окне будут выведены характеристики псевдонима БД, а в левом - построено дерево метаданных (рис. 311).
Дерево метаданных включает в себя ветви:
Domains -
домены;Tables -
таблицы;Views -
виртуальные таблицы (просмотры), Procedures - хранимые процедуры;Functions -
функции, определенные пользователем,Generators -
генераторы;Exceptions
- исключения;Blob Filters
- BLOB-фильтры.Для работы с определенным типом метаданных следует выбрать соответствующую ветвь дерева и раскрыть список, выбрав знак '+' слева от названия ветви. Если в опциях элемента меню View отмечен флаг System Data, будут показываться и системные данные, включаемые в каждую БД. На рис.31.2 показана раскрытая ветвь Tables, включающая данные о системных таблицах, а на рис. 31.3. - та же ветвь, не включающая информацию о системных таблицах.
Для каждой хранимой процедуры (ветвь Procedures} показываются входные и выходные параметры. Для каждого параметра в правом окне приводится характеризующая его информация: порядок (Order), вид - входной или выходной (Kind), имя домена (Domain), тип данных (Type), длина (Length) и число знаков в дробной части (Scale).
Для просмотра определения компонента данных (домена, таблицы, процедуры и т.д.) или его текста следует выбрать соответствующее имя в дереве в левом окне, а в правом окне выбрать закладку Definition или Text. На рис. 31.4 показан текст SQL-оператора CREATE PROCEDURE FIND_MAX_KOLVO, создавшего хранимую процедуру FIND_MAX_KOLVO.
К БД может быть выполнен SQL-запрос, при этом безразлично, на какой ветви мы будем находиться при выполнении запроса. На рис. 31.5 показано выполнение запроса, относящегося к таблицам RASHOD и ТО VARY, в то время как текущей в дереве является ветвь хранимой процедуры FIND_MAX_KOLVO.
Для выполнения SQL-оператора следует в правом окне выбрать закладку Enter SQL, набрать текст оператора и нажать кнопку с изображением молнии. Тогда, в случае корректности введенного оператора, в нижнем правом окне будет выбран результат выполнения запроса.
Таким образом, например, могут быть созданы новые таблицы, процедуры, просмотры и т.д., а также изменены и удалены существующие, то есть выполнено изменение структуры БД (аналогично тому, как это можно делать в Database Desktop или, что более характерно для удаленных БД, соответствующей утилитой интерактивного SQL, например, WISQL для InterBase). Например, введем оператор
CREATE TABLE SOMETABLE(
SOMEINT INTEGER NOT NULL,
SOMECHAR CHAR(10),
PRIMARY KEY (SOMEINT)
) ;
для создания новой таблицы БД SOMETABLE. После успешного выполнения оператора следует обновить информацию о БД в утилите Database Explorer. Для этого следует выбрать элемент меню View | Refresh. После этого имя новой таблицы появится в списке таблиц.
Просмотр и изменение данных
Чтобы просмотреть данные и, возможно, внести в них изменения, следует выбрать закладку Data и в таблице данных просмотреть и, если необходимо, изменить данные (рис. 31.6).
ЗАМЕЧАНИЕ 1.
Добавить, изменить или удалить записи также можно в окне Enter SQL с помощью операторов INSERT, UPDATE, DELETE.ЗАМЕЧАНИЕ
2 Просмотр данных в окне Data можно также осуществлять для просмотров View.Просмотр столбцов
Ветвь Columns, исходящая из каждой таблицы, показывает названия столбцов, входящих в структуру данной ТБД. Для каждого столбца в правом окне утилиты показываются, порядковый номер, имя системного домена столбца, тип, длина, число знаков в дробной части (если есть), значение по умолчанию.
Просмотр ограничений на значения столбцов
Из узла, соответствующего имени таблицы, исходят ветви, каждая из которых определяет тот или иной вид ограничения на значения столбцов таблицы:
• Primary Key -
ограничения первичного ключа;• Referential Constraints -
ограничения ссылочной целостности;• Unique Constraints -
ограничения уникального ключа;• Check Constraints -
условные ограничения, накладываемые на значение столбцаДля каждого такого ограничения показываются: имя ограничения в БД и столбцы, входящие в то или иное ограничение. Если выбрать закладку Text в правом окне, для каждого ограничения показывается соответствующий оператор ALTER TABLE ADD CONSTRAINT, приведший к созданию ограничения.
