Что делают подзапросы

Подзапросы находятся в предложении WHERE внешнего оператора. Их роль состоит в том, чтобы задавать для этого предложения условия поиска. Разные виды подзапросов дают разные результаты. Некоторые подзапросы создают список значений, который затем передается замыкающему оператору. Другие подзапросы создают единственное значение, которое затем проверяется замыкающим оператором с помощью оператора сравнения. Существуют также и подзапросы, возвращающие логические значения.

Другие коррелированные подзапросы

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

EXISTS

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

SELECT *

FROM CONTACT

WHERE EXISTS

(SELECT *

FROM CUSTOMER

WHERE CustStat" - 'CA'

AND CONTACT.CuSl.? = CUSTOMER.CustID) ;

Обратите внимание на такую ссылку, как CONTACT.CuslTD. Она указывает на столбец из внешнего запроса. Этот столбец сравнивается с другим столбцом, CUSTOMER.CustID, находящемся в таблице внутреннего запроса. Для каждой строки внешнего запроса вы проверяете внутренний запрос, т.е. в предложении WHERE внутреннего запроса используется значение столбца CustID из текущей строки таблицы CONTACT. Эта таблица указана во внешнем запросе.

Столбец CustID связывает таблицу CONTACT с таблицей CUSTOMER. SQL переходит в первую строку таблицы CONTACT, затем находит строку в таблице CUSTOMER, имеющую то же значение CustID, и проверяет в этой строке значение столбца CustState. Если CUSTOMER.CustState = 'СА, то в выводимую таблицу добавляется текущая строка таблицы CONTACT. Точно так же обрабатывается и следующая запись этой таблицы. Так как запрос указывает SELECT * FROM CONTACT, то возвращаются все поля таблицы с данными контактных представителей, в том числе поля с фамилиями и телефонными номерами представителей.

Использование вложенных запросов

Глава 11. Использование вложенных запросов

Коррелированные подзапросы перед

Коррелированные подзапросы, перед которыми стоит ключевое слово IN

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

SELECT *

FROM CONTACT

WHERE 'CA' IN

(SELECT CustState

FROM CUSTOMER

WHERE CONTACT.CustID = CUSTOMER.CustID) ;

Оператор выполняется с каждой записью таблицы CONTACT. Если значение столбца CustID этой записи совпадает с соответствующим значением столбца таблицы CUSTOMER, то значение CUSTOMER.CustState сравнивается со значением 'СА. Результатом выполнения подзапроса является список, в котором содержится не более одного элемента. Ваш этот единственный элемент представляет собой 'СА', то выполняется условие предложения WHERE из замыкающего оператора и строка добавляется в выводимую запросом таблицу.

Коррелированные подзапросы, перед которыми стоят операторы сравнения

Как будет показано в следующем примере, перед коррелированным подзапросом может стоять также любой из шести операторов сравнения.

Компания Zetec выплачивает каждому своему продавцу премию, которая зависит от общей суммы, вырученной им от продаж за месяц. Чем выше эта сумма, тем выше процент премии. Список этих процентов хранится в таблице BONUSRATE (ставка премии) со столбцами MIN_AMOUNT (нижняя граница), МАХ_AMOUNT (верхняя граница) (процент премии).

MIN_AMOUNT	MAX_AMOUNT	BONUS_PCT
----------------	----------------	--------------
0.00	24999.99	0
25000.00	49999.99	0.001
50000.00	99999.99	0.002
100000.00	249999.99	0.003
250000 .00	499999.99	0.004
500000.00	749999.99	0.005
750000.00	999999.99	0.006

Если у продавца ежемесячная сумма продаж составляет 100000-249999,99 долл., то он получает премию в размере 0,3% от этой суммы.

Продажи записываются в главную таблицу сделок TRANSMASTER.

TRANSMASTER
-----------------
Столбец	Тип	Ограничения
---------	----	----------------
TRANSID (идентификатор сделки)	INTEGER	PRIMARY KEY
CUSTID (идентификатор покупателя)	INTEGER	FOREIGN KEY
EMPID (идентификатор сотрудника)	INTEGER	FOREIGN KEY
TRANSDATE (дата сделки)	DATE
NET_AMOUNT (облагаемая налогом сумма)	NUMERIC
FREIGHT (стоимость перевозки)	NUMERIC
TAX (налог)	NUMERIC
INVOICETOTAL (итоговая сумма счета-фактуры)	NUMERIC

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

SELECT BONUS_PCT

FROM BONUSRATE

WHERE MIN_AMOUNT <=

(SELECT SUM (NET_AMOUNT)

FROM TRANSMASTER

WHERE EMPID = 133)

AND MAX_AMOUNT >=

(SELECT SUM (NET_AMOUNT)

FROM TRANSMASTER

WHERE EMPID =133) ;

Этот запрос интересен тем, что в нем содержатся два подзапроса, для которых приходится использовать логическую связку AND. В подзапросах применяется итоговый оператор SUM, и он возвращает единственное значение — общую сумму продаж за месяц для сотрудника с идентификационным номером 133. Затем это значение сравнивается со значениями в столбцах MIN_AMOUNT и MAX_AMOUNT из таблицы BONUSRATE, и в результате получается процент премии для этого сотрудника.

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

SELECT BONUS_PCT

FROM BONUSRATE

WHERE MIN_AMOUNT <=

(SELECT SUM (NET_AMOUNT)

FROM TRANSMASTER WHERE EMPID =

(SELECT EMPID

FROM EMPLOYEE

WHERE EMPLNAME = 'Coffin'))

AND MAX_AMOUNT >=

(SELECT SUM (NET_AMOUNT)

FROM TRANSMASTER WHERE EMPID =

(SELECT EMPID

FROM EMPLOYEE

WHERE EMPLNAME = 'Coffin'));

В этом примере, чтобы получить процент премии для сотрудника по фамилии Коффин, используются подзапросы, вложенные в другие подзапросы, а те, в свою очередь, вложены в замыкающий запрос. Эта структура работает только тогда, когда вам наверняка известно, что в компании работает один-единственный сотрудник с этой фамилией. А если вы знаете, что имеются несколько сотрудников с фамилией Коффин? Тогда в предложение WHERE из подзапроса самого нижнего уровня можно добавлять все новые и новые условия, пока не появится уверенность, что будет выбрана единственная строка таблицы EMPLOYEE.

Кванторы ALL SOME и ANY

Кванторы ALL, SOME и ANY

Другой способ сделать так, чтобы подзапрос возвращал единственное значение, — поставить перед этим подзапросом оператор сравнения с квантором. В сочетании с оператором сравнения квантор общности ALL (все) и кванторы существования SOME (некоторый) и ANY (какой-либо) обрабатывают список, возвращенный подзапросом, и в результате этот список сводится к единственному значению.

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

SELECT * FROM NATIONAL ;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Sal	Maglie	11
Don	Newcombe	9
Sandy	Koufax	13
Don	Drysdale	12
SELECT * FROM AMERICAN ;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Whitey	Ford	12
Don	Larson	10
Bob	Turley	8
Allie	Reynolds	14

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

SELECT *

FROM AMERICAN

WHERE CompleteGames > ALL

(SELECT CompleteGames FROM NATIONAL) ;

Вот его результат:

FirstName	LastName	СompleteGames
-----------	-----------	------------------
Allie	Reynolds	14

Подзапрос (SELECT CompleteGames FROM NATIONAL) возвращает значения из столбца CompleteGames (количество бессменных игр) для всех питчеров Национальной лиги. Выражение > ALL означает, что надо возвращать только те значения CompleteGames из таблицы AMERICAN, которые больше любого значения, возвращаемого подзапросом. Иными словами, "больше наивысшего значения, возвращаемого подзапросом". В этом случае таким наивысшим значением является 13. В таблице AMERICAN единственной строкой, где находится большее значение, является запись Элли Рейнолдса (АШе Reynolds) с его 14 играми, бессменно сыгранными на подаче.

А что если ваше первоначальное допущение ошибочно? Что если лидером высшей лиги по количеству бессменных игр был все-таки питчер Национальной лиги, несмотря на то, что в Национальной лиге нет назначенного хиттера? Если это так, то запрос

SELECT *

FROM AMERICAN

WHERE CompleteGames > ALL

(SELECT CompleteGames FROM NATIONAL) ;

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

NОТ EXISTS

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

SELECT *

FROM CONTACT

WHERE NOT EXISTS

(SELECT *

FROM CUSTOMER

WHERE CustState = 'CA'

AND CONTACT.CustID = CUSTOMER.CustID) ;

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

Операторы UPDATE DELETE и INSERT

Операторы UPDATE, DELETE и INSERT

Кроме операторов SELECT, предложения WHERE могут быть и в операторах UPDATE, DELETE и INSERT. А в этих предложениях, в свою очередь, могут быть такие же подзапросы, как и в предложениях WHERE, используемых в операторе SELECT.

Например, Zetec только что заключила с Olympic Sales соглашение о партнерстве, согласно которому Zetec "задним числом" предоставляет Olympic Sales десятипроцентную скидку на весь прошлый месяц. Информацию об этой скидке можно ввести в базу данных, используя оператор UPDATE:

UPDATE TRANSMASTER

SET NET_AMOUNT = NET_AMOUNT * 0.9

WHERE CUSTID =

(SELECT CUSTID

FROM CUSTOMER

WHERE COMPANY = 'Olympic Sales')

В операторе UPDATE можно также использовать и коррелированный подзапрос. Предположим, что в таблице CUSTOMER имеется столбец LAST_MONTHS_MAX (максимум за последние месяцы), а руководство Zetec хочет предоставить скидку для всех сделок, которые превышают значение LAST_MONTHS_MAX данного клиента:

UPDATE TRANSMASTER ТМ

SET NET__AMOUNT = NET_AMOUNT * 0.9

WHERE NET_AMOUNT >

(SELECT LAST_MONTHS_MAX

FROM CUSTOMER С

WHERE C.CUSTID = TM.CUSTID) ;

Обратите внимание, что этот подзапрос является коррелированным. Дело в том, что предложение WHERE, расположенное в последней строке оператора, обращается одновременно и к значению CUSTID из строки, полученной с помощью подзапроса из таблицы CUSTOMER, и к значению CUSTID из текущей строки-кандидата на обновление, которая находится в таблице TRANSMASTER.

Подзапрос в операторе UPDATE может обращаться и к обновляемой таблице. Предположим, что руководство Zetec хочет дать десятипроцентную скидку покупателям, купившим товаров на сумму более 10 000 долларов:

UPDATE TRANSMASTER TM1

SET NET_AMOUNT = NET_AMOUNT * 0.9

WHERE 10000 < (SELECT SUM(NET_AMOUNT)

FROM TRANSMASTER TM2

WHERE TM1.CUSTID = TM2.CUSTID);

Во внутреннем подзапросе для всех строк таблицы TRANSMASTER, которые относятся к одному и тому же покупателю, вычисляется (с помощью функции SUM) сумма значений из столбца NET_AMOUNT. Что это означает? Предположим, что в таблице TRANSMASTER к покупателю со значением CUSTID, равным 37, относятся четыре строки, в которых столбец NET_ AMOUNT имеет такие значения: 3000, 5000, 2000 и 1000. Для этого значения CUSTID сумма значений NET_AMOUNT равна 11000.

Обратите внимание, что порядок, в котором оператор UPDATE обрабатывает строки, определяется конкретной реализацией и обычно является непредсказуемым. Этот порядок может зависеть от того, каким образом строки хранятся на диске. Предположим, что в имеющейся реализации для значения столбца CUSTID, равного 37, строки таблицы TRANSMASTER обрабатываются в следующем порядке. Первой — строка со значением NET_AMOUNT, равным 3000, затем — с NET_AMOUNT, равным 5000, и т.д. После обновления первых трех строк со значением CUSTID, равным 37, у них в столбце NET_AMOUNT будут такие значения: 2700 (90% от 3000), 4500 (90% от 5000) и 1800 (90% от 2000). А затем, когда в TRANSMASTER идет обработка последней строки, в которой значение CUSTID равно 37, a NET_AMOUNT равно 1000, то значение функции SUM, возвращенное подзапросом, должно быть равно 10000. Это значение получается как сумма новых значений NET_AMOUNT из первых трех строк со значением CUSTID, равным 37, а также старого значения из последней строки, имеющей то же значение CUSTID. Таким образом, может показаться, что последняя строка для значния CUSTID, равного 37, не должна обновляться — ведь сравнение с этим значением SUM не будет истинным (10000 не меньше SELECT SUM(NET_AMOUNT)). Но при обращении подзапроса к обновляемой таблице оператор UPDATE работает уже по-другому. В этом операторе при всех проверках подзапросов используются старые значения обновляемой таблицы. В предыдущем операторе UPDATE для столбца CUSTID, равного 37, подзапрос возвращает 11000, т.е. первоначальное значение SUM.

Подзапрос в предложении WHERE работает точно так же, как оператор SELECT или UPDATE. To же самое верно для DELETE или INSERT. Чтобы удалить записи обо всех сделках Olympic Sales, используйте такой оператор:

DELETE TRANSMASTER

WHERE CUSTID =

(SELECT CUSTID

FROM CUSTOMER

WHERE COMPANY = 'Olympic Sales') ;

Как и в случае с UPDATE, подзапросы DELETE также могут быть коррелированными и также могут обращаться к изменяемой таблице (у которой в данном случае удаляют строки). Здесь действуют правила, похожие на те, что используются для подзапросов оператора UPDATE. Предположим, вы хотите удалить из таблицы CUSTOMER все строки тех пользователей, для которых итог NET_AMOUNT больше 10000 долларов:

DELETE TRANSMASTER TM1

WHERE 10000 < (SELECT SUM(NET_AMOUNT))

FROM TRANSMASTER TM2

WHERE TM1.CUSTID = TM2.CUSTID) ;

Этот запрос удаляет из таблицы TRANSMASTER все строки, в которых столбец CUSTID содержит 37, а также строки, относящиеся к другим пользователям, сумма покупок которых превышает 10000 долларов. Все обращения к TRANSMASTER, имеющиеся в подзапросе, указывают на содержимое этой таблицы, которое было перед любыми удалениями, уже выполненными текущим оператором. Поэтому даже при удалении из таблицы TRANSMASTER последней строки, в которой значение столбца CUSTID равно 37, подзапрос все равно выполняется на этой таблице таким образом, как если бы не было никаких удалений. В итоге подзапрос возвращает значение 11000.

При обновлении, удалении или вставке записей базы данных есть риск сделать так, что данные в изменяемой таблице не будут соответствовать данным в других таблицах из этой базы. Такое несоответствие называется аномалией изменения (см. главу 5). Если из таблицы TRANSMASTER удаляются записи, а от нее зависит другая таблица, TRANSDETAIL (подробности сделок), то записи, соответствующие удаленным записям из первой таблицы, необходимо удалять и из второй. Эта операция называется каскадным удалением, поскольку удаление родительской записи должно вызывать каскад удалений связанных с ней дочерних записей. В противном случае неудаленные дочерние записи становятся "записями-призраками".

В операторе INSERT может находиться предложение SELECT. Такие операторы применяются для заполнения таблиц с текущей информацией. Ниже приведен запрос для создания таблицы с содержимым TRANSMASTER за 27 октября.

CREATE TABLE TRANSMASTER_1027

(TRANSID INTEGER, TRANSDATE DATE,

. . . ) ;

INSERT INTO TRANSMASTER_1027

(SELECT * FROM TRANSMASTER

WHERE TRANSDATE = 2000-10-27) ;

Если требуется информация лишь о крупных сделках, то запрос будет таким:

INSERT INTO TRANSMASTER_102 7

(SELECT * FROM TRANSMASTER TM

WHERE TM.NET_AMOUNT > 10000

AND TRANSDATE 2000-10-27) ;

Подзапросы перед которыми стоит ключевое слово IN

Подзапросы, перед которыми стоит ключевое слово IN

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

SELECT слисок_столбцов

FROM таблица

WHERE выражение IN (подзапрос) ;

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

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

SELECT Model

FROM COMP_USED

WHERE CompID IN

(SELECT CompID

; FROM COMPONENT

WHERE CompType = 'Monitor') ;

Вначале SQL выполняет запрос самого нижнего уровня, т.е. обрабатывает таблицу COMPONENT, возвращая значения CompID из тех строк, в которых значением СотрТуре является 'Monitor'. В результате появляется список идентификационных номеров всех мониторов. Затем внешний запрос сравнивает с полученным списком значение CompID из каждой строки таблицы COMP_USED. Если сравнение бьло успешным, то значение Model из той же строки добавляется в виртуальную таблицу, создаваемую внешним оператором SELECT. В результате появляется список всех моделей ваших товаров, в состав которых входит монитор. Следующий пример показывает, что получится, если этот запрос действительно запустить на выполнение:

Model

--------

СХ3000

СХ3010

СХ3020

МХ3030

МХ3020

МХ3030

Теперь известно, каких товаров в скором времени не будет у вас на складе. Рекламу этих товаров следует на время, по возможности, свернуть.

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

Подзапросы, перед которыми стоит ключевое слово NOT IN

Запрос с ключевым словом IN, приведенный в предыдущем разделе, помог руководству фирмы узнать, какие товары нельзя будет продавать. Хотя это и ценная информация, но на ней много не заработаешь. А вот что действительно надо знать руководству Zetec — какие товары молено будет активно продавать. Руководство фирмы хочет продвигать именно те товары, в состав которых мониторы не входят. Такую информацию можно получить с помощью подзапроса, перед которым стоит ключевое слово NOT IN:

SELECT Model

FROM COMP_USED

WHERE Model NOT IN

(SELECT Model

FROM COMP_USED

WHERE CompID IN

(SELECT CompID

FROM COMPONENT

WHERE CompType = 'Monitor')) ;

В результате выполнения этого оператора получаем следующее:

Model

--------

РХ3040

РВ3050

РХ3040

РВ3050

Здесь надо сказать о двух моментах.

В этом запросе имеются два уровня вложенности. Два подзапроса — это в точности оператор предыдущего запроса. Теперь он вложен во внешний замыкающий оператор SELECT. Он принимает список товаров, в состав которых входят мониторы. Перед SELECT стоит другое ключевое слово — NOT EM. В результате действия внешнего оператора создается еще один список. В нем перечислены модели всех продуктов, за исключением тех, в состав которых входят мониторы. В получившейся виртуальной таблице некоторые строки могут повторяться. Причина повторений следующая. Название товара, собранного из нескольких компонентов, среди которых нет мониторов, встречается в нескольких строках таблицы COMPJUSED. И каждой такой строке соответствует отдельная строка в получившейся виртуальной таблице.

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

SELECT DISTINCT Model

FROM COMP_USED

WHERE Model NOT IN

(SELECT Model

FROM COMP_USED

WHERE CompID IN

(SELECT CompID

FROM COMPONENT

WHERE CompType = 'Monitor')) ;

Как и ожидалось, результат получился следующий:

Model

--------

РХ3040

РВ3050

Подзапросы в предложении HAVING

Коррелированный подзапрос можно задавать не только в предложении WHERE, но и в предложении HAVING. Как уже говорилось в главе 9, перед этим предложением обычно находится предложение GROUP BY. Предложение HAVING действует как фильтр, который должен ограничивать группы, созданные предложением GROUP BY. Группы, которые не удовлетворяют условию предложения HAVING, в результат не попадут. Если предложение HAVING используется таким образом, то оно проверяется для каждой группы, созданной
предложением GROUP BY. Если же предложения GROUP BY нет, то предложение HAVING проверяется для всего набора строк, переданного предложением WHERE. Тогда этот набор считается одной группой. А если нет ни предложения WHERE, ни предложения GROUP BY, то условие предложения HAVING проверяется уже для всей таблицы:

SELECT TM1.EMPID

FROM TRANSMASTER TM1

GROUP BY TM1.EMPID

HAVING MAX (TM1.NET_AMOUNT) >= ALL

(SELECT 2 * AVG (TM2.NET_AMOUNT)

FROM TRANSMASTER TM2

WHERE TM1.EMPID <> TM2.EMPID) ;

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

Строки таблицы TRANSMASTER группируются внешним запросом по значениям столбца EMPID. Это делается с помощью предложений SELECT, FROM и GROUP BY. Получившиеся группы фильтруются предложением HAVING. В нем для каждой из групп вычисляется (с помощью функции МАХ) максимум значений из столбца NET_AMOUNT, которые находятся в строках этой группы. Внутренний запрос дважды проверяет среднее значение NET_AMOUNT для всех тех строк, в которых значения столбца EMPID отличаются от значения этого столбца в текущей группе внешнего запроса. Обратите внимание, что в последней строке запроса приходится указывать два значения, взятые из разных EMPID. Поэтому в предложениях FROM из внешнего и внутреннего запросов приходится для таблицы TRANSMASTER указывать два разных псевдонима. Эти псевдонимы затем используются в сравнении, расположенном в последней строке запроса. Цель их использования состоит в том, чтобы показать — обращение должно идти к значению столбца ЕМРЮ из текущей строки внутреннего подзапроса (ТМ2.ЕМРШ), а также к значению того же столбца, но на этот раз из текущей группы внешнего подзапроса (TM1.EMPID).

Таблица PRODUCT

Таблица 11.1. Таблица PRODUCT

Столбец	Тип	Ограничения
Model (модель)	Char (6)	PRIMARY KEY
ProdName (название товара)	Char (35)
ProdDesc (описание товара)	Char (31)
ListPrice (цена)	Numeric (9,2)

Таблица COMPONENT

Таблица 11.2. Таблица COMPONENT

Столбец	Тип	Ограничения
CompiD (идентификатор компонента)	char (6)	PRIMARY KEY
CompType (тип компонента)	char (10)
CompDesс (описание компонента)	char (31)

Таблица COMPOSED

Таблица 11.3. Таблица COMPOSED

Столбец	Тип	Ограничения
Model (модель)	char (6)	FOREIGN KEY (ДЛЯ PRODUCT)
CompiD (идентификатор компонента)	char (б)	FOREIGN KEY (ДЛЯ COMPONENT)

Компонент может использоваться во множестве товаров, а товар — состоять из множества компонентов (отношение "многие ко многим"). Такая ситуация может привести к нарушениям целостности данных. Чтобы этого не случилось, создайте промежуточную таблицу COMP_USED, связывающую COMPONENT с PRODUCT. Компонент может быть указан во многих строках COMEMJSED, но в каждой строке этой таблицы указывается только один компонент (отношение "один ко многим"). И, аналогично, товар может быть указан также во многих строках COMP_USED, но в каждой строке этой таблицы также указывается только один товар (еще одно отношение "один ко многим"). С помощью промежуточной таблицы сложное отношение "многие ко многим" разбивается на два относительно простых отношения "один ко многим". Этот процесс упрощения отношений — как раз один из примеров нормализации.

Таблица CUSTOMER

Таблица 11.4. Таблица CUSTOMER

Столбец	Тип	Ограничения
CustiD (идентификатор покупателя)	INTEGER	PRIMARY KEY
Company (компания)	CHAR (40)
СustAddress (адрес покупателя)	CHAR (30)
Custcity (из какого города покупатель)	CHAR (20)
Custstate (из какого штата)	CHAR (2)
Сustzip (почтовый код покупателя)	CHAR (10)
CustPhone (телефон покупателя)	CHAR (12)
ModLevel	INTEGER

Таблица CONTACT

Таблица 11.5. Таблица CONTACT

Столбец	Тип	Ограничения
CustID	INTEGER	FOREIGN KEY
ContFName (имя представителя)	CHAR (10)
СontLName (фамилия представителя)	CHAR (16)
ContPhone (телефон представителя)	CHAR (12)
Continfo (информация о представителе)	CHAR (50)

Скажем, вам надо посмотреть контактную информацию о компании Olympic Sales, но вы не помните, какой у этой компании идентификатор в столбце CustiD. Используйте такой вложенный запрос:

SELECT *

FROM CONTACT

WHERE CustiD =

(SELECT CustiD

FROM CUSTOMER

WHERE Company = 'Olympic Sales') ;

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

CustiD	ContFName	ContLName	ContPhone	Contlnfo
-------	-------------	--------------	------------	----------
118	Jerry	Attwater	505-876-3456	Will play
				major role in
				coordinating
				the
				wireless
				Web.

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

Если в сравнении '=' используется подзапрос, то в списке SELECT этого подзапроса должен находиться один столбец (CustiD в этом примере). Подзапрос должен возвратить только одну строку. Это необходимо для того, чтобы в сравнении было одно значение.

В этом примере я предполагаю, что в таблице CUSTOMER находится только одна строка, в которой столбец Company содержит значение 'Olympic Sales'. Если в операторе CREATE TABLE, с помощью которого была создана таблица CUSTOMER, для столбца Company было установлено ограничение UNIQUE (уникальный), то это дает гарантию, что подзапрос в предыдущем примере возвратит только одно значение (или вообще ни одного). Однако подзапросы, похожие на тот, что используется в примере, обычно используются со столбцами, для которых это ограничение не установлено. В этих случаях, чтобы значения в столбце не повторялись, приходится полагаться на другие средства.

А если окажется, что в столбце Company таблицы CUSTOMER находится больше одного значения 'Olympic Sales' (филиалы в разных штатах), то выполнение подзапроса вызовет ошибку.

С другой стороны, если ни один покупатель из CUSTOMER не работает в Olympic Sales, то подзапрос возвратит значение NULL и результатом сравнения будет значение "unknown" (неизвестно). В этом случае итоговая виртуальная таблица будет пустой. Дело в том, что предложение WHERE возвращает только строки, для которых было получено значение True, а строки со значениями False и "unknown" будут отфильтрованы. Такое может, скорее всего, произойти, если по чьей-то ошибке в столбце Company окажется неправильное название, например 'Olumpic Sales'.

Хотя в таких структурах оператор равенства (=) и является самым распространенным, но в них можно использовать и пять остальных операторов сравнения. Для каждой строки из таблицы, которая указана в предложении замыкающего оператора, единственное значение, возвращаемое подзапросом, сравнивается с выражением из предложения WHERE того же оператора. Если результатом сравнения является значение True, то строка добавляется в выводимую виртуальную таблицу.

Если в состав подзапроса будет включена итоговая функция, то он гарантированно возвратит единственное значение. Эти функции всегда возвращают единственное значение. (Об итоговых функциях см. в главе 3.) Естественно, такой подзапрос будет полезен только тогда, когда требуется получить значение именно итоговой функции.

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

SELECT Model, ProdName, ListPrice

FROM PRODUCT

WHERE ListPrice =

(SELECT MAX(ListPrice)

FROM PRODUCT) ;

Это пример вложенного запроса, в котором подзапрос и замыкающий оператор работают с одной и той же таблицей. Подзапрос возвращает единственное значение — максимальную цену из столбца ListPrice таблицы PRODUCT. А внешний запрос возвращает все строки из той же таблицы, имеющие максимальное значение в столбце ListPrice.

В следующем примере показан подзапрос сравнения, в котором используется оператор сравнения, отличный от '=':

SELECT Model, ProdName, ListPrice

FROM PRODUCT

WHERE ListPrice <

(SELECT AVG(ListPrice)

FROM PRODUCT) ;

Подзапрос возвращает единственное значение — среднее значение цен, находящихся в столбце ListPrice таблицы PRODUCT. А внешний запрос возвращает все строки из той же таблицы, в которых значение столбца ListPrice меньше этого среднего значения.

Первоначально стандарт языка SQL разрешал иметь в сравнении только один подзапрос, который должен был находиться в правой части запроса. Согласно стандарту SQL: 1999 подзапросом может быть любой из двух операндов сравнения и даже оба сразу. А стандарт SQL:2OO3 поддерживает эту возможность.

Вложенные подзапросы которые возвращают наборы строк

Вложенные подзапросы, которые возвращают наборы строк

Предположим, что вы работаете на фирме по сборке компьютерных систем. В вашей компании, Zetec Corporation, из покупаемых комплектующих собирают системы, которые затем продают другим компаниям и правительственным агентствам. Информацию о своем бизнесе вы храните в реляционной базе данных. Она состоит из множества таблиц, но сейчас вас интересуют только три: PRODUCT (товар), COMP_USED (использованные компоненты) и COMPONENT (компонент). В таблице PRODUCT содержится список всех выпускаемых вашей фирмой стандартных товаров (табл. 11.1). В таблице COMPONENT перечисляются производственные компоненты товаров (табл. 11.2), а в таблице COMP_USED хранятся данные о том, из каких компонентов состоят произведенные товары (табл. 11.3).

Вложенные запросы которые являются

Вложенные запросы, которые являются проверкой на существование

Запрос возвращает данные из всех табличных строк, которые удовлетворяют его условиям. Иногда возвращается много строк, а иногда — только одна. Бывает так, что в таблице ни одна строка не удовлетворяет условиям и поэтому ни одна из них не возвращается. Перед подзапросом можно ставить предикаты EXISTS (существует) и NOT EXISTS (не существует). Такая структура, в которой сочетаются подзапрос и один из этих предикатов, сообщает, имеются ли в таблице, указанной в предложении FROM (из) подзапроса, какие-нибудь строки, соответствующие условиям предложения WHERE (где) того же подзапроса.

Подзапросы, перед которыми ставится один из предикатов EXISTS или NOT EXISTS, принципиально отличаются от тех подзапросов, о которых уже говорилось в этой главе. Во всех предыдущих случаях SQL вначале выполняет подзапрос, а затем применяет результат этой операции по отношению к замыкающему оператору. А подзапросы с предикатами EXISTS и NOT EXISTS — это коррелированные подзапросы, и выполняются они по-другому.

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

Подзапрос или возвращает одну, или несколько строк, или вообще не возвращает ни одной. Если он возвращает хотя бы одну строку, то предикат EXISTS является истинным и свое действие выполняет замыкающий оператор. В тех же условиях предикат NOT EXISTS является ложным, и замыкающий оператор свое действие не выполняет. После обработки строки в таблице внешнего оператора та же операция выполняется со следующей строкой. Это действие повторяется до тех пор, пока не будут обработаны все строки из таблицы, указанной замыкающим оператором.

Вложенные запросы возвращающие одно значение

Вложенные запросы, возвращающие одно значение

Часто перед подзапросом полезно ставить один из шести операторов сравнения (=, о, <, <=, >, >=). Это можно делать тогда, когда у выражения, стоящего перед оператором, вычисляется единственное значение, а подзапрос, стоящий после оператора, также выдает одно значение. Исключением является оператор сравнения, сразу после которого находится квантор (ANY, SOME или ALL).

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

Зачем использовать подзапрос

Во многих случаях с помощью подзапроса можно получить тот же результат, что и с помощью объединения (JOIN). Как правило, сложность синтаксиса подзапроса сопоставима со сложностью синтаксиса объединения. Выбор способа построения запроса при этом часто становится делом вкуса пользователя базы данных. Одни предпочитают использовать объединения (JOIN), в то время как другие — применять вложенные запросы. Впрочем, иногда бывает так, что получить нужный результат с помощью объединений (JOIN) невозможно. Тогда приходится использовать вложенный запрос или разбивать задачу на несколько операторов SQL и выполнять их поочередно.

Что такое рекурсия

Это довольно старая возможность таких языков программирования, как Logo, LISP и C++. В этих языках можно определить функцию (совокупность одной или множества команд), которая выполняет заданную операцию. Главная программа вызывает функцию, выполняя для этого команду, которая называется вызовом функции. В процессе своей работы функция вызывает сама себя — это самая простая форма рекурсии.

Для иллюстрации достоинств и недостатков рекурсии приведем простую программу, в которой одна из функций использует рекурсивные вызовы. Эта программа, написанная на C++, чертит на экране компьютера спираль, начиная с единичного сегмента, направленного вверх.

В ее состав входят три функции.

Функция line(n) чертит отрезок длины n. Функция Jeft_turn(d) поворачивает "чертежный инструмент" на d градусов против часовой стрелки. Функция spiral(segment), которая определяется следующим образом:

void spiral(int segment)

{

line(segment);

left_turn(90);

spiral(seement + 1);

}

Если из главной программы вызвать spiral(1), то будут выполняться такие действия:

spiral(1) чертит единичный отрезок (т.е. единичной длины), направленный вверх; spiral(1) выполняет поворот на 90 градусов против часовой стрелки; spiral(1) вызывает spiral(2); spiral(2) чертит отрезок, равный по длине двум единичным и направленный влево; spiral(2) выполняет поворот на 90 градусов против часовой стрелки; spiral(2) вызывает spiral(3); и т.д.

Постепенно благодаря программе появляется спиральная кривая, изображенная на Рисунок 12.1.

Что такое рекурсивный запрос

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

Где еще можно использовать рекурсивный запрос

Любая задача, которую можно представить в виде древовидной структуры, поддается решению с помощью рекурсивного запроса. Классическим примером того, как такие запросы используются в промышленности, является обработка материалов (процесс превращения сырья в конечный продукт). Предположим, ваша компания выпускает новый гибридный бензи-ново-электрический автомобиль. Такую машину собирают из узлов (двигателя, батарей и т.п.), которые, в свою очередь, состоят из меньших подузлов (коленчатого вала, электродов и пр.), а те — из еще меньших компонентов. Данные обо всех этих компонентах компонентов сохранять в реляционной базе очень трудно — если, конечно, в ней не используется рекурсия. Рекурсия дает возможность, начав с целой машины, добраться любым путем к самой малой детали. Хотите найти данные о крепежном винте, который держит клемму отрицательного электрода вспомогательной батареи? Это можно — и причем без особых затрат времени. Справляться с такими задачами SQL может с помощью структуры WITH RECURSIVE (рекурсивный оператор).

Кроме того, рекурсия вполне естественна при анализе "что, если?". Например, что произойдет, если руководство авиакомпании Vannevar Airlines решит прекратить полеты ю Портленда в Шарлотт? Как это повлияет на полеты в те города, куда сейчас можно добраться кз Портленда? Рекурсивный запрос незамедлительно даст ответ на эти вопросы.

Где можно использовать запрос

Во многих трудных ситуациях рекурсивные запросы помогают сэкономить и время, и нервы. Предположим, например, что у вас есть пропуск, который дает право бесплатно летать любым авиарейсом воображаемой компании Vannevar Airlines. Неплохо, правда? И тут встает вопрос: Куда же можно бесплатно попасть? Все авиарейсы Vannevar Airlines перечислены в таблице FLIGHT (авиарейс), и для каждого из них указан его номер, начальный пункт и место назначения (табл. 12.1).

Рекурсивные запросы

Глава 12. Рекурсивные запросы

Экономия времени с помощью рекурсивного запроса

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

WITH RECURSIVE

ReachableFrom (Source, Destination)

AS (SELECT Source, Destination

FROM FLIGHT

UNION

SELECT in.Source, out.Destination

FROM ReachableFrom in, FLIGHT out

WHERE in.Destination = out.Source

)

SELECT * FROM ReachableFrom

WHERE Source = "Portland";

В начале первого прохода, выполняемого во время рекурсии, в таблице FLIGHT будет семь строк, а в ReachableFrom (означает "можно попасть из") — ни одной. Оператор UNION берет семь строк из FLIGHT и копирует их в таблицу ReachableFrom. Тогда в ReachableFrom появятся данные, показанные в табл. 12.2.

Маленькие трудности

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

Результат вызова spiral(1)

Рисунок 12.1 Результат вызова spiral(1)

Сбой недопустим

Такой сценарий развития событий показывает одну из опасностей, связанных с использованием рекурсии. Программа, написанная для того, чтобы обращаться к себе самой, вызывает на выполнение свой новый экземпляр, а тот, в свою очередь, вызывает еще один, и так до бесконечности. Обычно это не то, что нужно. Чтобы решить проблему, программисты помешают в рекурсивную функцию условие завершения — предел того, насколько глубоко должна зайти рекурсия. В результате программа выполняет нужные действия, а затем красиво завершается. Условие завершения мы можем поместить и в нашу программу черчения спиралей, чтобы сберечь ресурсы компьютера и избежать головокружения у программистов:

void spiral2(int segment)

{

if (segment <= 10)

{

line(segment);

left_turn(90) ,

spiral2(segment+ 1) ;

}

При вызове программа spiral2(l) выполняется и затем рекурсивно вызывает сама себя до тех пор, пока значение segment не превысит 10. Как только значение segment станет равным 11, конструкция if (segment <= 10) возвратит значение False, и код, находящийся во внутренних скобках, будет пропущен. Управление снова передается предыдущему вызову spiral2(l), а оттуда постепенно возвращается к самому первому вызову, после которого программа завершается.

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

Рекурсия — это мощный инструмент для повторного выполнения кода. Она идеально подходит для поиска в древовидных структурах, например, в файловых системах, сложных электронных схемах или многоуровневых распределенных сетях.

Авиарейсы компании Vannevar Airlines

Таблица 12.1. Авиарейсы компании Vannevar Airlines

Flight No. (номер авиарейса)	Source (начальный пункт)	Destination (место назначения)
3141	Portland (Портленд)	Orange County (округ Ориндж)
2173	Portland	Charlotte (Шарлотт)
623	Portland	Daytona Beach (Дейтона-Бич)
5440	Orange County	Montgomery (Монтгомери)
221	Charlotte	Memphis (Мемфис)
32	Memphis	Champaign (Шампейн)
981	Montgomery	Memphis

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

CREATE TABLE FLIGHT (
FlightNo	INTEGER	NOT NULL,
Source	CHARACTER (30),
Destination	CHARACTER (30)
) ;

Как только таблица будет создана, ее можно заполнить данными из табл. 12.1.

Предположим, вы хотите лететь из Портленда к своему другу в Монтгомери. Естественно, что вы зададите себе вопросы: "В какие города я попаду самолетами Vannevar Airlines, если начинать с Портленда? А куда я смогу долететь самолетами этой же авиакомпании, если садиться на самолет в Монтгомери?" В некоторые города долететь без промежуточных посадок можно, а в другие — нельзя. По пути в некоторые города придется делать не менее одной такой посадки. Конечно, можно найти все города, куда самолеты Vannevar Airlines могут вас доставить из любого выбранного вами города просто, что называется, "в лоб". Но если вы будете искать города, выполняя один запрос за другим, то тогда вами выбран...

Таблица ReachableFrom

Таблица 12.2. Таблица ReachableFrom после одного прохода рекурсии

Source	Destination
Portland	Orange County
Portland	Charlotte
Portland	Daytona Beach
Orange County	Montgomery
Charlotte	Memphis
Memphis	Champaign
Montgomery	Memphis

Интересное начнется уже при втором проходе. Предложение WHERE (WHERE in. Destination = out. Source)означает, что просматриваются только те строки в которых поле Destination таблицы ReachableFrom равно полю Source таблиш FLIGHT. Для каждой такой строки берутся значения поля Source из ReachableFrom и пол Destination из FLIGHT, а затем в качестве новой строки добавляются в ReachableFrom. Результат этого прохода показан в табл. 12.3.

Таблица ReachableFrom

Таблица 12.3. Таблица ReachableFrom после двух проходов рекурсии

Source	Destination
Portland	Orange County
Portland	Charlotte
Portland	Daytona Beach
Orange County	Montgomery
Charlotte	Memphis
Memphis	Champaign
Montgomery	Memphis
Portland	Montgomery
Portland	Memphis
Orange County	Memphis
Charlotte	Champaign

Эти результаты выглядят намного более полезными. Теперь в таблице ReachableFrom поле Destination содержит все города, в которые можно попасть из любого города, находящегося в поле Source той же таблицы, делая при этом не более одной промежуточной посадки. Затем во время следующего прохода рекурсия обработает маршруты с двумя промежуточными посадками и будет так продолжать до тех пор, пока не будут найдены все города, куда только можно попасть.

После завершения рекурсии третий и последний оператор SELECT (который в рекурсии не участвует) выделяет из ReachableFrom только те города, в которые можно попасть из Портленда. В этом примере можно попасть во все остальные шесть городов, причем с достаточно малым числом промежуточных посадок. Так что вам не придется метаться, как будто вы скачете на ходуле с пружиной.

Если вы внимательно изучите код рекурсивного запроса, то увидите, что он не выглядит проще, чем семь отдельных запросов. Однако у этого запроса есть два преимущества:

после его запуска постороннее вмешательство больше не требуется; он быстро работает.

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

Что же делает запрос рекурсивным? То, что мы определяем таблицу ReachableFrom на основе ее самой. Рекурсивной частью определения является второй оператор SELECT, который расположен сразу после UNION. ReachableFrom — это временная таблица, которая наполняется данными по мере выполнения рекурсии. И это наполнение продолжается до тех пор, пока все возможные пункты назначения не окажутся в ReachableFrom. Повторяющихся строк в этой таблице не будет, потому что туда их не пропустит оператор UNION. Когда рекурсия завершится, в таблице ReachableFrom окажутся все города, в которые можно попасть из любого города-начального пункта. Третий и последний оператор SELECT возвращает только те города, в которые вы можете попасть из Портленда. Так что желаем приятного путешествия.

Трудный способ

Найти то, что хотите узнать, — при условии, что у вас есть терпение и время, — можно с помощью последовательности запросов, в первом из которых начальным пунктом является Портленд:

SELECT Destination FROM FLIGHT WHERE Source = "Portland";

Этот первый запрос возвращает Orange County, Charlotte и Daytona Beach. Первый из них, если хотите, можно сделать начальным пунктом уже во втором запросе:

SELECT Destination FROM FLIGHT WHERE Source = "Orange County";

В результате второй запрос возвращает Montgomery. В третьем же запросе можете снова использовать результаты первого запроса, взяв на этот раз в качестве начального пункта второй город:

SELECT Destination FROM FLIGHT WHERE Source = "Charlotte";

Этот запрос возвращает Memphis. Результаты первого запроса можно использовать ив четвертом, взяв в качестве начального пункта последний из этих результатов:

SELECT Destination FROM FLIGHT WHERE Source = "Daytona Beach";

Прошу прощения, четвертый запрос возвращает неопределенное значение — у Vannevar Airlines нет авиарейсов из Дейтона-Бич. Но в качестве начального пункта можете также использовать город (Montgomery), который возвращен вторым запросом, что и делается в очередном, пятом, запросе:

SELECT Destination FROM FLIGHT WHERE Source = "Montgomery";

В результате его выполнения возвращается Memphis, но для вас это не имеет значения. Вы еще раньше узнали, что в этот город попасть можно через Шарлотт. Но Мемфис в качестве начального пункта можно использовать в следующем запросе:

SELECT Destination FROM FLIGHT WHERE Source = "Memphis";

Этот запрос возвращает Champaign. Им также можно пополнить список городов, куда вы можете попасть (пусть даже с промежуточной посадкой). А так как вас интересуют авиарейсы и с промежуточными посадками, то в запросе в качестве начального пункта можно использовать и этот город:

SELECT Destination FROM FLIGHT WHERE Source = "Champaign";

Обидно! Запрос возвращает неопределенное значение; оказывается у Vannevar Airlines нет авиарейсов и из Шампейн. (Пока что семь запросов. Они еще не действуют кому-то на нервы?)

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

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

Администратор базы данных

В большинстве крупных баз данных с большим количеством пользователей высшей властью обладает администратор базы данных (database administrator, DBA). У администратора имеются права и полномочия на любые действия с базой данных. Впрочем, администратор еще должен нести и огромную ответственность. Он может легко испортить базу данных и "пустить на ветер" тысячи часов работы. Все администраторы должны ясно и тщательно продумывать те последствия, которые может иметь каждое их действие.

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

Самый лучший способ стать администратором — это самостоятельно установить систему управления базой данных. В руководстве по установке находится учетная запись, или регистрационное имя (login), а также пароль. Это регистрационное имя удостоверяет, что вы являетесь привилегированным пользователем. В системе такой привилегированный пользователь называется администратором базы данных, иногда — системным администратором, или суперпользователем (к сожалению, ему, в отличие от Супермена, плащ и сапоги не положены). В любом случае первым официальным актом, который вы совершите после ввода учетной записи и пароля (иначе говоря, регистрации), должно стать изменение полученного вами пароля на свой собственный, секретный. Если пароль не будет изменен, то любой, кто прочитает руководство пользователя СУБД, сможет также зарегистрироваться с полным набором полномочий. Вряд ли вам это понравится. Но если изменение сделано, то зарегистрироваться в качестве администратора смогут только те, кто знает ваш новый пароль.

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

Совет 1
Совет 1

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

Аннулирование полномочий

Наряду с предоставлением полномочий доступа существует необходимость иметь возможность аннулировать эти полномочия. Обязанности сотрудников со временем изменяются, следовательно, изменяются их потребности в доступе к данным. Нередки случаи перехода на работу к конкуренту. В этом случае все полномочия перешедших сотрудников придется отозвать. В SQL удаление полномочий на доступ выполняется с помощью оператора REVOKE (отозвать). Его синтаксис аналогичен синтаксису оператора GRANT, но только результат получается противоположный.

REVOKE [GRANT OPTION FOR] список-полномочий

ON объект

FROM список-пользователей [RESTRICT | CASCADE] ;

С помощью этой структуры можно отзывать перечисленные в списке полномочия, не затрагивая при этом все остальные. Главное отличие между операторами REVOKE и GRANT состоит в том, что в первом из них применяется одно из двух необязательных ключевых слов — RESTRICT (ограничить) или CASCADE (каскадное удаление). Пусть для предоставления полномочий вы использовали оператор GRANT вместе с WITH GRANT OPTION. Тогда применение ключевого слова CASCADE в операторе REVOKE приводит к отзыву указанных полномочий как у того пользователя, которому вы их предоставили, так и у всех пользователей, кому (благодаря атрибуту WITH GRANT OPTION) эти полномочия он уже успел предоставить. С другой стороны, оператор REVOKE с ключевым словом RESTRICT будет отзывать полномочия пользователя, который никому больше их не предоставлял. Если пользователь уже с кем-то поделился полномочиями, указанными в операторе REVOKE с ключевым словом RESTRICT, то выполнение этого оператора будет прервано и будет выведено сообщение об ошибке.

Оператор REVOKE с необязательным предложением GRANT OPTION FOR (возможность предоставления) можно использовать, чтобы отзывать у пользователя возможность предоставлять указанные полномочия, но оставляя их для самого этого пользователя. Если оператор содержит предложение GRANT OPTION FOR и ключевое слово CASCADE, то отзываются все полномочия, предоставленные пользователем, а также полномочия этого пользователя на предоставление полномочий. А если в операторе есть и GRANT OPTION FOR и RESTRICT, то события развиваются по одному из двух вариантов.

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

Обеспечение безопасности базы данных

Глава 13. Обеспечение безопасности базы данных

Иерархическая структура полномочий доступа

Рисунок 13.1. Иерархическая структура полномочий доступа

Инициирование выполнения операторов SQL

В некоторых случаях выполнение одного оператора SQL может вызвать запуск другого оператора или даже целого их блока. Поддержка такой функции (триггерной схемы) и была осуществлена в версии SQL:2OO3. Триггер— это механизм, который задает триггер-событие (событие для запуска), время активизации триггера и одно или несколько запускаемых действий. Триггер-событие инициирует запуск, выражаясь простым языком, дает команду "огонь". Время активизации триггера указывает, в какой момент должно произойти действие: непосредственно перед триггер-событием или после него. Запускаемое действие — это выполнение одного или нескольких операторов SQL. При запуске более одного оператора SQL все операторы должны содержаться в пределах структуры BEGIN ATOMIC... END. Само триггер-событие может использовать оператор INSERT, UPDATE или DELETE.

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

Как показано в следующем примере, пользователь или роль должны иметь привилегию на создание триггера:

CREATE TRIGGER CustomerDelete BEFORE DELETE

ON CUSTOMER FOR EACH ROW

WHEN State = NY

INSERT INTO CUSTLOG VALUES ('deleted a NY customer') :

Теперь при каждом удалении нью-йоркского клиента из таблицы CUSTOMER в регистрационной таблице CUSTLOG будет сделана запись об удалении.

Использование доменов наборов

Использование доменов, наборов символов, сопоставлений и трансляций

На безопасность также влияют домены, наборы символов, сопоставления и трансляции. В частности, создавая домены, внимательно следите, чтобы из-за них не пострадала ваша система безопасности.

Можно определить домен, который охватывает какой-либо набор столбцов. Таким образом, у всех этих столбцов был один и тот же тип, а также одни и те же ограничения. Теперь столбцы, создаваемые оператором CREATE TABLE, смогут унаследовать тип и ограничения домена. Конечно, если нужно, то для отдельных столбцов эти характеристики можно перезаписать. Однако домены — это удобное средство, которое дает возможность с помощью одного объявления задавать многочисленные характеристики сразу для целого набора столбцов.

Домены удобны тогда, когда есть множество таблиц, имеющих столбцы с похожими характеристиками. Например, база данных вашей фирмы может состоять из нескольких таблиц. Представим, что в каждой из них находится столбец PRICE (цена), у которого должен быть тип данных DECIMAL(10,2), а значения в этом столбце должны быть не отрицательными и не больше 10000. Тогда, прежде чем создавать таблицы с такими столбцами, нужно создать домен, указывающий характеристики этих столбцов. Создание домена PriceTypeDomain (домен типа цены) показано в следующем примере:

CREATE DOMAIN PriceTypeDomain DECIMAL (10,2)

CHECK (Price > = 0 AND Price <= 10000) ;

Возможно, в каком-либо наборе таблиц ваши товары будут определяться с помощью столбца ProductCode (код товара), у которого в каждой таблице тип данных составляет CHAR(5), первый символ должен быть X, С или Н, а последний — или 9, или 0. Для таких столбцов также можно создать домен, например ProductCodeDomain (домен кода товара), что и делается в следующем примере:

CREATE DOMAIN ProductCodeDomain CHAR (5)

CHECK (SUBSTR (VALUE, 1,1) IN ("X", "С", "Н")

AND SUBSTR (VALUE, 5,1) IN ("9", "0") ) ;

Определив домены, можно приняться за создание таблиц, например таблицы PRODUCT (товар):

CREATE TABLE PRODUCT

(ProductCode ProductCodeDomain,

ProductName CHAR (30),

Price PriceTypeDomain) ;

Как только в определении таблицы для поля ProductCode или Price нужно задавать тип данных, указывается соответствующий домен. Таким образом, эти столбцы получают нужные типы данных и, кроме того, для них устанавливаются ограничения, определенные в операторах CREATE DOMAIN.

При использовании доменов возникают вопросы, связанные с безопасностью. Если кто-то другой вдруг захочет использовать созданные вами домены, то может ли такое использование привести к осложнениям? Может. Что если кто-то создаст таблицу со столбцом, в котором используется домен PriceTypeDomain? Пользователь может в этом столбце постепенно увеличивать значения и делать это до тех пор, пока столбец не перестанет их принимать. Таким образом можно будет определить верхнюю границу значений PriceType (тип цены), которую вы указали в предложении CHECK (проверка) оператора CREATE DOMAIN. И если значение этой верхней границы является закрытой информацией, необходимо запретить использовать домен PriceType неуполномоченным пользователям. Чтобы защитить вас в подобных ситуациях, SQL позволяет использовать чужие домены только тем, кому владельцы доменов явно предоставят соответствующее разрешение. Такое разрешение может предоставлять только владелец домена (и, конечно же, администратор). А само предоставление разрешения выглядит так:

GRANT USAGE ON DOMAIN PRICE_TYPE TO SALES_MGR ;

Язык управления данными как часть SQL

Операторы SQL, используемые для создания баз данных, составляют группу, которая называется языком определения данных (Data Definition Language, DDL). Создав базу данных, для добавления, изменения или удаления из нее данных можно использовать другие инструкции , известные под собирательным названием язык манипулирования данными (Data Manipu-п Language, DML). В SQL есть также операторы, которые не попадают ни в одну из этих категорий. Иногда программисты называют эти операторы языком управления данными (Data Control Language, DCL). Операторы DCL в основном защищают базу данных от несанкционированного доступа, от нежелательных последствий одновременной работы сразу нескольких пользователей, а также от аварий в электрических сетях и неисправностей оборудования. В этой главе рассказывается о защите от несанкционированного доступа.

Экономия времени и сил благодаря

Экономия времени и сил благодаря совместному использованию операторов GRANT и REVOKE

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

GRANT SELECT, INSERT, DELETE

ON CUSTOMER

TO Tyson, Keith, David ;

GRANT UPDATE

ON CUSTOMER (Company, CustAddress, CustCity,

CustState, CustZip, CustPhone, ModelLevel)

TO Tyson, Keith, David ;

GRANT SELECT

ON CUSTOMER

TO Jenny, Valerie, Melody, Neil, Robert, Sam,

Brandon, MichelleT, Allison, Andrew,

Scott, MishelleB, Jaime, Linleigh, Matt, Amanda;

А теперь попробуем упростить этот код. Все пользователи обладают полномочиями просмотра таблицы CUSTOMER. Менеджеры по продажам имеют на эту таблицу полномочия вставки и удаления, а также могут обновлять любой ее столбец, кроме CustlD. Поэтому тот же результат, что и в предыдущих операторах, можно получить более легким способом:

GRANT SELECT

ON CUSTOMER

TO SalesReps

GRANT INSERT, DELETE, UPDATE

ON CUSTOMER

TO Tyson, Keith, David ;

REVOKE UPDATE

ON CUSTOMER (CustlD)

FROM Tyson, Keith, David ;

Это та же защита, что и в предпоследнем примере, и для нее также надо использовать три оператора. Никто не может изменить данные в столбце CustlD. Полномочия INSERT, DELETE и UPDATE имеют только Тайсон, Кейт и Дэвид. Как видно, три последних оператора значительно короче, так как в них не приходится вводить имя каждого сотрудника отдела продаж и перечислять каждый столбец таблицы.

Модификация табличных данных

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

GRANT UPDATE (BonusPct)

ON BONUSRATE

TO SalesMgr ;

Исходя из рыночной конъюнктуры, менеджер по продажам может регулировать значения премиальных процентов, на основе которых рассчитываются премии продавцов (столбец ВоnusPct). Однако менеджер не может изменять значения в столбцах Min Amount и Max Amount, определяющие диапазон, который соответствует каждому уровню шкалы премий. Чтобы разрешить модификацию значений всех столбцов таблицы, необходимо указать все имена столбцов или, как в следующем примере, — ни одного:

GRANT UPDATE

ON BONUSRATE

TO VPSales ;

Предоставление полномочий

Администратор базы данных может предоставить любому пользователю любые полномочия. Владелец объекта также может предоставить любому пользователю любые полномочия, связанные с этим объектом. Однако те, кто получил таким образом свои полномочия, не могут их, в свою очередь, предоставить третьим лицам. Это ограничение позволяет администратору или владельцу объекта в достаточной степени сохранять контроль над ситуацией. Доступ к объекту могут получить только пользователи, уполномоченные на это администратором или владельцем объекта.

Если смотреть с точки зрения безопасности, то представляется разумным ограничить возможность раздавать полномочия доступа. Тем не менее часто пользователям нужны именно права на предоставление полномочий. Конвейер не может остановиться только из-за того, что кто-то заболел, находится в отпуске или ушел на обед. Вы можете дать некоторым пользователям право предоставлять их права доступа надежным сменщикам. Для передачи пользователю такого права в операторе GRANT используется предложение WITH GRANT OPTION (предоставляющий полномочия). Следующий оператор показывает пример того, как можно использовать это предложение:

GRANT UPDATE (BonusPct)

ON BONUSRATE

TO SalesMgr

WITH GRANT OPTION ;

Теперь менеджер по продажам может предоставить права на обновление данных при помощи следующего оператора:

GRANT UPDATE (BonusPct)

ON BONUSRATE

TO AsstSalesMgr ;

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

Предоставление полномочий пользователям

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

GRANT СПИСОК-ПОЛНОМОЧИЙ

ON объект

ТО список-пользователей

[WITH GRANT OPTION] ;

Предложение WITH GRANT OPTION означает "предоставляющий полномочия"; список полномочий в операторе GRANT определяется следующим образом:

ПОЛНОМОЧИЯ [ , ПОЛНОМОЧИЯ] . . .

или

ALL PRIVILEGES

В свою очередь, вот как здесь определяются полномочия:

SELECT

| DELETE

| INSERT [(имя-столбца[, имя-столбца] . ..) ]

| UPDATE [(имя-столбца[, имя-столбца]...)]

|REFERENCES [(имя-столбца[, имя-столбца] ...)]

| USAGE

| UNDER

| TRIGGER

| EXECUTE

А объект в операторе GRANT определяется таким способом:

[TABLE] <имя таблицы>

|DOMAIN <имя домена>

| COLLATION <имя сопоставления>

| CHARACTER SET <имя символьного набора>

|TRANSLATION <имя трансляции>

| ТУРЕ <схематмчески обозначенный определенный пользователем тип>

|SEQUENCE <имя генератора последовательности спецификатор указателя шаблона>

И наконец, список пользователей в операторе определяется следующим образом:

регистрационное-имя [, регистрационное-имя]...

| PUBLIC

Указанный синтаксис применяется к представлению точно так же, как и к таблице. Полномочия SELECT, DELETE, INSERT, UPDATE и REFERENCES относятся только к таблицам и представлениям. А полномочие USAGE имеет отношение к доменам, наборам символов, сопоставлениям и трансляциям. В последующих разделах приведены различные примеры использования оператора GRANT.

Просмотр данных

А вот пример предоставления полномочий просмотра:

GRANT SELECT

ON PRODUCT

TO PUBLIC ;

Эти полномочия позволяют пользователям системы просматривать содержимое таблицы PRODUCT (товар).

"Публика"

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

В большинстве установленных баз данных пользовательские полномочия представляют собой иерархическую структуру. В этой структуре полномочия администратора находятся на самом высоком уровне, а полномочия рядовых пользователей — на самом низком. Пример иерархической структуры полномочий приведен на Рисунок 13.1.

Роли

Одним из типов идентификатора подтверждения полномочий, причем не единственным, является имя пользователя. Это имя удостоверяет пользователя или программу, имеющих полномочия на выполнение с базой данных одной или множества операций. Если в большой организации с большим числом пользователей предоставлять полномочия отдельно каждому сотруднику, то такая операция может занять очень много времени. В стандарте SQL:2OO3 есть понятие роли. Оно-то и помогает решить эту проблему.

Роль, определяемая именем, — это набор полномочий, предоставляемый совокупности пользователей, которым нужен одинаковый уровень доступа к базе данных. Например, одинаковые полномочия должны быть у всех пользователей, имеющих роль SecurityGuard (означает "охранник"). Эти полномочия, скорее всего, должны отличаться от тех, что предоставляются пользователям, имеющим роль SalesClerk ("торговый служащий").

Помни: Так как в основном стандарте SQL:2OO3 ролей нет, то в некоторых реализациях их также может не быть. Перед тем как пытаться использовать роли, проверьте документацию конкретной СУБД.

Для создания роли можно использовать примерно такой синтаксис:

CREATE ROLE SalesClerk ;

После того как роль создана, вы можете назначить ее тому или иному пользователю с помощью оператора GRANT:

GRANT SalesClerk to Becky ;

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

Ссылки для связанных друг с другом таблиц

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

Предположим, например, что в таблице LAYOFF_LIST (список временно уволенных) находятся имена и фамилии сотрудников, которых в следующем месяце временно уволят. Доступ с правом SELECT к этой таблице имеют только уполномоченные сотрудники администрации. Однако один неуполномоченный сотрудник обнаружил, что первичным ключом таблицы LAYOFF_LIST является EmpID (идентификатор сотрудника). Тогда этот сотрудник создает новую таблицу SNOOP ("ищейка"), в которой EmpID является внешним ключом. Этот внешний ключ и дает возможность потихоньку заглядывать в таблицу LAYOFF_LIST. Как создать внешний ключ с помощью предложения REFERENCES (ссылки), см. в главе 5. Все эти приемы должны быть известны каждому системному администратору.

CREATE TABLE SNOOP

(EmpID INTEGER REFERENCES LAYOFF_LIST) ;

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

Стандарт SQL:2003 не позволяет таким способом взламывать систему защиты. Он требует, чтобы любые права на использование ссылок предоставлялись уполномоченным пользователем другим пользователям только в явном виде. Каким образом это сделать, показано в следующем примере:

GRANT REFERENCES (EmpID)

ON LAYOFF_LIST

TO PERSONNEL_CLERK ;

Удаление из таблицы устаревших строк

Покупатели переезжают в другие города. Сотрудники увольняются, уходят на пенсию или в мир иной. Товары устаревают. Жизнь продолжается, данные базы теряют актуальность. Устаревшие записи необходимо удалять. С другой стороны, следует тщательно контролировать, кто какие записи может удалять. И с этой задачей справится оператор GRANT:

GRANT DELETE

ON EMPLOYEE

TO PersonnelMgr ;

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

Уровни пользовательского доступа

SQL:2003 обеспечивает контролируемый доступ к девяти функциям управления базами
данных.

Создание, просмотр, обновление и удаление: эти функции соответствуют операторам INSERT, SELECT, UPDATE и DELETE (см. главу 6). Ссылки задаются при помощи ключевого слова REFERENCES (см. главы 3 и 5). Ссылки задают ограничения таблицы, зависящей от другой таблицы базы данных. Использование, которое указывается с помощью ключевого слова USAGE, относится к доменам, наборам символов, сопоставлениям и трансляциям. (О доменах, наборах символов, сопоставлениях и трансляциях см. в главе 5.) Определение новых типов данных задается с помощью ключевого слова UNDER при работе с типами данных, определяемыми пользователем. Ответ на событие задается с помощью ключевого слова TRIGGER. Благодаря ему выполняется заданный оператор или целый блок операторов SQL всякий раз при возникновении предопределенного события. Выполнение стандартной программы задается с помощью ключевого слова EXECUTE.

Владельцы объектов базы данных

Кроме администраторов, есть еще один класс привилегированных пользователей — это владельцы объектов базы данных. Такими объектами, например, являются таблицы и представления. Любой пользователь, создающий какой-либо объект базы данных, может назначить владельца этого объекта. Владелец таблицы обладает всеми возможными полномочиями, которые только связаны с этой таблицей, включая управление доступом к ней. Представление создается на основе таблиц, причем владелец представления необязательно должен быть владельцем этих таблиц. Однако в этом случае владелец представления получает на него полномочия, аналогичные имеющимся у него на таблицы, на основе которых это представление создано. Отсюда следует вывод, что нельзя обойти защиту таблицы, принадлежащей другому пользователю, создав на ее основе какое-либо представление.

Неплохая работенка, но...
Вас, вероятно, интересует, как можно стать администратором базы данных и купаться в лучах славы, уважения и восхищения, сопутствующих этой должности. Очевидный ответ состоит в том, чтобы подлизаться к своему боссу. Иногда достаточно демонстрировать компетентность, честность и надежность при выполнении своих ежедневных обязанностей. На самом же деле, главное, что требуется для этой должности, — иметь крепкие нервы. Говоря о славе, уважении и восхищении, я всего лишь шутил. Если с базой данных происходит что-то не то, всегда виноват администратор. А ведь рано или поздно это случается. Так что начинайте тренировать выдержку.

может храниться информация, не предназначенная

Внимание

Этот оператор может быть по-настоящему опасным. В столбцах таблицы PRODUCT — например, таких как CostOfGoods (стоимость товаров), — может храниться информация, не предназначенная для всеобщего обозрения. И чтобы предоставить доступ к большей части информации, скрывая при этом важные данные, определите на основе таблицы представление, в котором не будет столбцов с конфиденциальной информацией. Затем предоставляйте полномочия SELECT не на саму таблицу, а на ее представление. Синтаксис этой процедуры приведен ниже. CREATE VIEW MERCHANDISE AS
SELECT Model, ProdName, ProdDesc, ListPrice
        FROM PRODUCT ;
GRANT SELECT
    ON MERCHANDISE
    TO PUBLIC ;
Пользуясь лишь представлением MERCHANDISE (товары), рядовой пользователь не сможет увидеть CostOfGoods или любой другой столбец из таблицы PRODUCT, за исключением тех четырех, которые перечислены в операторе CREATE VIEW. Это столбцы Model (модель), ProdName (название товара), ProdDesc (описание товара), ListPrice (цена по прейскуранту).

Вставка данных

Ниже приведен пример предоставления полномочий на вставку данных в таблицу:

GRANT INSERT

ON CUSTOMER

TO SalesClerk ;

Эти полномочия позволяют служащему из отдела продаж добавлять в таблицу CUSTOMER (клиент) новые записи.