SQL1
         

когда вы овладели оператором EXISTS,


Теперь, когда вы овладели оператором EXISTS, вы узнаете о трёх специальных операторах, ориентируемых на подзапросы. (Фактически имеются только два, так как ANY и SOME - одно и то же.) Если вы поймёте работу этих операторов, вы будете понимать все типы подзапросов предиката, используемых в SQL. Кроме того, вам будут представлены различные способы того, как данный запрос может быть сформирован с использованием различных типов подзапросов предиката, и вы поймёте преимущества и недостатки каждого из этих подходов.
ANY, ALL и SOME напоминают EXISTS, принимая подзапросы как аргументы; однако они отличаются от EXISTS тем, что используются совместно с реляционными операциями. В этом отношении они напоминают оператор IN, когда тот используется с подзапросами: они берут все значения, выведенные подзапросом и обрабатывают их как модуль. Однако, в отличие от IN, они могут использоваться только с подзапросами.

ИСПОЛЬЗОВАНИЕ ПРЕДЛОЖЕНИЯ UNION


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

В этой главе вы научитесь использовать предложение UNION в SQL. UNION отличается от подзапросов тем, что в нём ни один из двух (или более) запросов не управляется другим запросом. Все запросы выполняются независимо друг от друга, а уже вывод их объединяется.



ВВОД, УДАЛЕНИЕ И ИЗМЕНЕНИЕ ЗНАЧЕНИЯ ПОЛЯ


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

Будет показано использование запросов при формировании полной группы строк для вставки, а также - как может использоваться предикат для управления изменением значений и удаления строк.

Материал в этой главе составляет полный объём знаний, показывающий, как создавать и управлять информацией в базе данных (БД). Более мощные способы проектирования предикатов будут обсуждены в следующей главе.



В этой главе вы узнаете,


В этой главе вы узнаете, как использовать подзапросы в командах модификации. Вы обнаружите, что нечто подобное вы уже видели при использовании подзапросов в запросах. Понимание того, как подзапросы используются в командах SELECT, сделает их применение в командах модификации более уверенным, хотя и останутся некоторые вопросы. Завершением команды SELECT является подзапрос, но не предикат, и поэтому его использование отличается от использования простых предикатов с командами модификации, которые вы уже выполняли раннее с командами UPDATE и DELETE. Вы использовали простые запросы, чтобы производить значения для INSERT, а теперь мы можем расширить эти запросы, чтобы включать в них подзапросы.
Важный принцип, который надо соблюдать при работе с командами модификации: нельзя в предложении FROM любого подзапроса модифицировать таблицу, к которой обращаетесь с помощью основной команды. Это относится ко всем трём командам модификации. Хотя имеется большое количество ситуаций, в которых будет полезно сделать запрос той таблицы, которую вы хотите модифицировать, причем во время её модификации, это слишком усложняет операцию, чтобы использовать её на практике.
Не делайте ссылки к текущей строке таблицы, указанной в команде, которая является соотнесённым подзапросом.

СОЗДАНИЕ ТАБЛИЦ




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

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

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

Эта глава вводит нас в область SQL, называемую DDL (Язык Определения Данных), где создаются объекты данных SQL.

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



ОГРАНИЧЕНИЕ ЗНАЧЕНИЙ ВАШИХ ДАННЫХ


В Главе 17 вы узнали, как создаются таблицы. Теперь мы более основательно покажем вам, как вы можете устанавливать ограничения в таблицах. Ограничения это часть определений таблицы, вводящая ограничения на значения, которые вы можете вводить в столбцы. До этого места в данной книге ограничениями на значения, которые вы могли вводить, были тип данных и размер вводимых значений, которые должны быть совместимы с теми столбцами, куда эти значения помещаются (как определено для команды CREATE TABLE или команды ALTER TABLE).

Ограничения дают вам значительно б?ольшие возможности, и скоро вы это увидите. Вы также узнаете, как определять значения по умолчанию. По умолчанию - это значение, которое вставляется автоматически в любой столбец таблицы, когда значение для этого столбца отсутствует в команде INSERT для этой таблицы. NULL это наиболее широко используемое значение по умолчанию, но в этой главе будет показано, как определять и другие значения по умолчанию.



ПОДДЕРЖКА ЦЕЛОСТНОСТИ ВАШИХ ДАННЫХ


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

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

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

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



ПРЕДСТАВЛЕНИЕ. ВВЕДЕНИЕ.


ПРЕДСТАВЛЕНИЕ (VIEW) (я называю его также "просмотр" - прим. ред.) это объект данных, который не содержит никаких данных его владельца. Это тип таблицы, чьё содержание выбирается из других таблиц с помощью выполнения запроса. По мере изменения значений в таблицах, эти изменения автоматически отражаются представлением.

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



ИЗМЕНЕНИЕ ЗНАЧЕНИЙ С ПОМОЩЬЮ ПРЕДСТАВЛЕНИЙ


В этой главе рассказано о командах модификации языка DML - ВСТАВИТЬ (INSERT), ИЗМЕНИТЬ (UPDATE) и УДАЛИТЬ (DELETE) - и о том, когда они применяются для представлений. Как сказано в предыдущей главе, использование команд модификации в представлениях это косвенный способ использования их в ссылочных таблицах с помощью запросов представлений. Однако не все представления могут модифицироваться.

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

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



В этой главе вы научитесь


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

С другой стороны - пользователи, которые создают таблицы, сами имеют права на управление этими таблицами. Привилегии это то, что определяет, может ли указанный пользователь выполнить данную команду. Имеется несколько типов привилегий, соответствующих нескольким типам операций. Привилегии даются и отменяются двумя командами SQL: GRANT (ДОПУСК) и REVOKE (ОТМЕНА). Эта глава покажет вам, как эти команды используются.

ГЛОБАЛЬНЫЕ АСПЕКТЫ SQL


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

Этот материал даст вам возможность сконфигурировать вашу БД, отменять действия ошибок, и определять, как действия одного пользователя в БД будут влиять на действия других пользователей.



КАК ДАННЫЕ SQL ХРАНЯТСЯ В УПОРЯДОЧЕННОМ ВИДЕ?


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



ИСПОЛЬЗОВАНИЕ SQL С ДРУГИМ ЯЗЫКОМ (ВЛОЖЕННЫЙ/ВСТРОЕННЫЙ SQL)


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

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



в будущем. Эта команда даёт


Синтаксис (стандартный)

GRANT ALL [PRIVILEGES]

| {SELECT

| INSERT

| DELETE

| UPDATE [(<column llst>)]

| REFERENCES [(<column llst>)l } . , . .

ON <table name> . , . .

TO PUBLIC | <Authorization ID> . , . .

[WITH GRANT OPTION];

Аргумент ALL (ВСЕ), с или без PRIVILEGES (ПРИВИЛЕГИИ), включает каждую привилегию в список привилегий. PUBLIC (ОБЩИЙ) включает всех существующих пользователей и всех созданных в будущем. Эта команда даёт возможность передать права для выполнения действий в таблице с указанным именем. REFERENCES позволяет дать права на использование столбцов в списке столбцов <column list> как родительский ключ для внешнего ключа. Другие привилегии состоят из права выполнять команды, для которых привилегии указаны их именами в таблице. UPDATE подобен REFERENCES и может накладывать ограничения на определенные столбцы. GRANT OPTION даёт возможность передавать эти привилегии другим пользователям.

Синтаксис (нестандартный)

GRANT DBA

| RESOURCE

| CONNECT ... .

TO <Authorization ID> . , . .

[IDENTIFIED BY> password>

CONNECT дает возможность передавать право на регистрацию и некоторые другие ограниченные права.

RESOURCE дает пользователю право создавать таблицы.
DBA дает возможность передавать почти все права.

IDENTIFIED BY используется вместе с CONNECT для создания или изменения пароля пользователя.


ГРУППОВЫЕ ПРЕДСТАВЛЕНИЯ


Групповые представления это представления, наподобие запроса Ratingcount в предыдущем примере, которые содержат предложение GROUP BY или которые основываются на других групповых представлениях.

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

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

CREATE VIEW Totalforday AS SELECT odate, COUNT (DISTINCT cnum), COUNT (DISTINCT snum), COUNT (onum), AVG (amt), SUM (amt) FROM Orders GROUP BY odate;

Теперь вы сможете увидеть всю эту информацию с помощью простого запроса:

SELECT * FROM Totalforday;

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

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



ГРУППЫ ПРИВИЛЕГИЙ. ГРУППЫ ПОЛЬЗОВАТЕЛЕЙ.


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

Stephen может предоставить и SELECT, и INSERT в таблице Заказов для Adrian:

GRANT SELECT, INSERT ON Orders TO Adrian;

или и для Adrian, и для Diane:

GRANT SELECT, INSERT ON Orders TO Adrian, Diane;

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



ИДЕНТИФИКАЦИЯ СТРОК (ПЕРВИЧНЫЙ КЛЮЧ)


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

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

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



ИМЕНА ТАБЛИЦ И СТОЛБЦОВ


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

Salespeople.snum

Salespeople.city

Orders.odate

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

Если мы должны связать эти столбцы (кратковременно), мы должны будем указать их с именами Salespeople.city или Customers.city, чтобы SQL мог их различать.



ИМЕНОВАНИЕ СТОЛБЦА ДЛЯ ВСТАВКИ (INSERT)


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

Предположим, что вы берёте значения для таблицы Заказчиков из отчёта, выводимого на принтер, который помещает их в таком порядке: city, cname, cnum; и, для упрощения, вы хотите ввести значения в том же порядке:

INSERT INTO Customers (city, cnamе, cnum) VALUES ('London', 'Honman', 2001);

Обратите внимание, что столбцы rating и snum отсутствуют. Это значит, что в эти строки автоматически установлены значения по умолчанию. По умолчанию может быть введено значение NULL или другое значение, определяемое по умолчанию. Если ограничение запрещает использование значения NULL в данном столбце и в этот столбец не установлено значение по умолчанию, этот столбец должен быть обеспечен значением для любой команды INSERT, которая относится к таблице (смотри в Главе 18 информацию об ограничениях на значения NULL и "по умолчанию").



ИМЕНОВАНИЕ СТОЛБЦОВ


В нашем примере поля наших представлений имеют свои имена, полученные прямо из имён полей основной таблицы. Это удобно. Однако вам нужно снабдить ваши столбцы новыми именами:

когда некоторые столбцы являются выводимыми, и поэтому не имеющими имен.

когда два или более столбцов в объединении имеют те же имена, что в их базовой таблице.

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



ИНДЕКСЫ


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

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

Когда вы создаёте индекс в поле, ваша база данных (БД) запоминает соответствующий порядок всех значений этого поля в области памяти. Предположим, что наша таблица Заказчиков имеет тысячи входов, а вы хотите найти заказчика с номером=2999. Так как строки не упорядочены, ваша программа будет просматривать всю таблицу, строку за строкой, проверяя каждый раз значение поля cnum на равенство значению 2999. Однако, если бы имелся индекс в поле cnum, то программа могла бы выйти на номер 2999 прямо по индексу и дать информацию о том, как найти правильную строку таблицы.

Хотя индекс значительно улучшает эффективность запросов, использование индекса несколько замедляет операции модификации DML (такие как INSERT и DELETE), а сам индекс занимает память. Следовательно, каждый раз, когда вы создаёте таблицу, вы должны принять решение, индексировать её или нет.

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

Синтаксис для создания индекса обычно следующий (помните, что это не ANSI-стандарт):

CREATE INDEX ON (column name [,column name]...);

Таблица, конечно, должна уже быть создана и должна содержать имя столбца. Имя индекса не может быть использовано для чего-то другого в БД (любым пользователем). Однажды созданный, индекс будет невидим пользователю. SQL сам решает, когда он необходим, чтобы ссылаться на него, и делает это автоматически. Если, например, таблица Заказчиков будет наиболее часто упоминаться в запросах продавцов к их собственной клиентуре, было бы правильно создать такой индекс в поле snum таблицы Заказчиков:

CREATE INDEX Clientgroup ON Customers (snum);

Теперь тот продавец, который имеет отношение к этой таблице, сможет найти собственную клиентуру очень быстро.



INSERT (ВСТАВИТЬ)


Синтаксис

INSERT INTO <table name> (<column llst>)

VALUES (<value llst>) I <query>;

INSERT создает одну или больше новых строк в таблице <table name>. Если используется предложение VALUES, значения строк вставляются в таблицу <table name>. Если запрос <query> указан, каждая строка вывода будет вставлена в таблицу <table name>. Если список столбцов <column list> отсутствует, все столбцы таблицы <table name , принимаются в упорядоченном виде.



ИНТЕРАКТИВНЫЙ И ВЛОЖЕННЫЙ SQL


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

Вложенный SQL состоит из команд SQL, помещённых внутри программ, которые обычно написаны на другом языке (типа КОБОЛа или ПАСКАЛя). Это делает такие программы более мощными и эффективным.

Однако, допуская эти языки, приходится иметь дело со структурой SQL и стилем управления данных, который требует некоторых расширений интерактивного SQL. Передача SQL-команд во вложенный SQL является пропускаемой ("passed off") для переменных или параметров, используемых программой, в которую они были вложены.

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



INTERSECT И MINUS


Команда UNION, как вы уже видели в Главе 14, может объединить два запроса, объединив их вывод в один. Два других, обычно имеющихся способа объединения отдельных запросов, - это INTERSECT (Плюс) и MINUS (Минус). INTERSECT выводит только строки, произведённые обоими перекрестными запросами, в то время как MINUS выводит строки, которые производятся одним запросом, но не другим.

Следовательно, следующие два запроса

SELECT * FROM Salespeople WHERE city = 'London'

INTERSECT

SELECT * FROM Salespeople WHERE 'London' IN (SELECT city FROM Customers WHERE Customers.snum = Salespeople.snum);

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

SELECT * FROM Salespeople WHERE city = 'London'

MINUS

SELECT * FROM Salespeople WHERE 'London' IN (SELECT sity FROM Customers WHERE Customers.snum = Salespeople.snum);

удалит строки, выбранные вторым запросом, из вывода первого, и, таким образом, будут выведены все продавцы в Лондоне, которые не имели там заказчиков.

MINUS иногда ещё называют DIFFERENCE (ОТЛИЧИЕ).



ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ В ПОДЗАПРОСАХ


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

Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY, будет выбирать одиночное значение для использования в основном предикате. Например, вы хотите увидеть все заказы, имеющие сумму выше средней на 4-е октября (вывод показан на Рисунке 10.3):

SELECT * FROM Orders WHERE amt > (SELECT AVG (amt) FROM Orders WHERE odate = 10/04/1990);

=============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE amt > | | (SELECT AVG (amt) | | FROM Orders | | WHERE odate = 01/04/1990); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | 3005 2345.45 10/03/1990 2003 1002 | | 3006 1098.19 10/03/1990 2008 1007 | | 3009 1713.23 10/04/1990 2002 1003 | | 3008 4723.00 10/05/1990 2006 1001 | | 3010 1309.95 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================

Рисунок 10.3 Выбор всех сумм со значением выше среднего на 10/04/1990

Средняя сумма приобретений на 4 октября - 1788.98 (1713.23 + 75.75) делится пополам, что в целом равняется 894.49. Все строки со значением в поле amt выше этого являются выбранными. Имейте в виду, что сгруппированные агрегатные функции, которые являются агрегатными функциями, определёнными в терминах предложения GROUP BY, могут производить многочисленные значения. Они, следовательно, недопустимы в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе. Вы должны использовать одиночную агрегатную функцию с предложением WHERE, что устранит нежелательные группы.

Например, следующий запрос который должен найти среднее значение комиссионных продавца в Лондоне,

SELECT AVG (comm) FROM Salespeople GROUP BY city HAVlNG city = "London";

не может использоваться в подзапросе! Во всяком случае, это не лучший способ формировать запрос.

Другим способом может быть

SELECT AVG (comm) FROM Salespeople WHERE city = "London";



ИСПОЛЬЗОВАНИЕ АРГУМЕНТОВ ALL И PUBLIC


SQL поддерживает два аргумента для команды GRANT, которые имеют специальное значение: ALL PRIVILEGES (ВСЕ ПРИВИЛЕГИИ), или просто ALL, и PUBLIC (ОБЩИЕ).

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

Например, Diane может выдать Stephen весь набор привилегий в таблице Заказчиков с помощью такой команды:

GRANT REFERENCES ON Salespeople TO Diane;

(привилегии UPDATE и REFERENCES, естественно, применяются ко всем столбцам.)

А это другой способ высказать ту же мысль:

GRANT ALL ON Customers TO Stephen;

PUBLIC больше похож на тип аргумента - он захватывает всё (catch-all), - чем на пользовательскую привилегию.

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

GRANT SELECT ON Orders TO PUBLIC;

Конечно, вы можете предоставить любые или все привилегии каждому, но это, очевидно, нежелательно. Все привилегии, за исключением SELECT, позволяют пользователю изменять (или, в случае REFERENCES, ограничивать) содержание таблицы. Разрешение всем пользователям изменять содержание ваших таблиц создаст проблемы.

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

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



ИСПОЛЬЗОВАНИЕ CHECK ДЛЯ ПРЕДОПРЕДЕЛЕНИЯ ДОПУСТИМОГО ВВОДИМОГО ЗНАЧЕНИЯ


Мы можем также использовать ограничение CHECK, чтобы защитить от ввода в поле определённых значений, и таким образом предотвратить ошибку. Например, предположим, что городами, в которых мы имеем офисы сбыта, являются Лондон, Барселона, Сан-Хосе и Нью-Йорк. Если вам известны все продавцы, работающие в каждом из этих офисов, нет необходимости разрешать ввод других значений. Если же нет, использование ограничения может предотвратить опечатки и другие ошибки.

CREATE TABLE Salespeople (snum integer NOT NULL UNIQUE, sname char(10) NOT NULL UNIQUE, city char(10) CHECK, (city IN ('London', 'New York', 'San Jose', 'Barselona')), comm decimal CHECK (comm < 1));

Конечно, если вы собираетесь сделать это, вы должны быть уверены, что ваша компания не открыла уже других новых офисов сбыта. Большинство программ баз данных поддерживают команду ALTER TABLE (см. Главу 17), которая позволяет изменять определение таблицы, даже когда она находится в использовании. Однако изменение или удаление ограничений не всегда возможно для этих команд, даже там, где это вроде бы поддерживается.

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

CREATE TABLE Orders (onum integer NOT NULL UNIQUE, amt decimal, odate date NOT NULL, cnum integer NOT NULL, snum integer NOT NULL);

Как мы уже говорили в Главе 2, тип DATЕ (ДАТА) широко поддерживается, но не является частью стандарта ANSI. Что же делать, если мы используем БД, которая, следуя ANSI, не распознаёт тип DATЕ? Если мы объявим поле odate с любым числовым типом, мы не сможем использовать слэш (/) или тире (-) в качестве разделителя. Так как печатаемые номера это символы ASCII, мы можем объявить тип поля date - CHAR. Основная проблема в том, что мы должны будем использовать одинарные кавычки всякий раз, когда ссылаемся на значение поля odate в запросе. Нет более простого решения этой проблемы там, где тип DATЕ стал таким популярным. В качестве иллюстрации, давайте объявим поле odate типом CHAR. Мы можем, как минимум, наложить на него наш формат с ограничением CHECK:

CREATE TABLE Orders (onum integer NOT NULL UNIQUE, amt decimal, odate char (10) NOT NULL CHECK (odate LIKE '--/--/----'), cnum NOT NULL, snum NOT NULL);

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



ИСПОЛЬЗОВАНИЕ COUNT СО СТРОКАМИ, А НЕ ЗНАЧЕНИЯМИ


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

SELECT COUNT (*) FROM Customers

COUNT со звёздочкой включает и NULL, и дубликаты; по этой причине DISTINCT не может быть использован. DISTINCT может производить более высокие числа, чем COUNT особого поля, который удаляет все

=============== SQL Execution Log ============ | | | SELECT COUNT (*) | | FROM Customers; | | ==============================================| | | | ------- | | 7 | | | | | ===============================================

Рисунок 6.4 Подсчет строк вместо значений

строки, имеющие избыточные или NULL-данные в этом поле. DISTINCT неприменим c COUNT (*), потому что он не имеет никакого действия в хорошо разработанной и поддерживаемой БД. В такой БД не должно быть ни таких строк, которые являлись бы полностью пустыми, ни дубликатов (первые не содержат никаких данных, а последние полностью избыточны). Если всё-таки имеются полностью пустые или избыточные строки, вы, вероятно, не захотите, чтобы COUNT скрыл от вас эту информацию.



ИСПОЛЬЗОВАНИЕ COUNT ВМЕСТО EXISTS


Подчеркнём, что все формулировки с ANY и ALL могут быть в точности выполнены с EXISTS, в то время как обратное будет неверно. Хотя в этом случае также верно и то, что подзапросы EXISTS и NOT EXISTS могут проколоть при выполнении тех же самых подзапросов с COUNT(*) в предложении SELECT подзапроса. Если больше чем ноль строк в выводе будет подсчитано, это эквивалентно EXISTS; в противном случае это работает так же, как NOT EXISTS. Следующее является этому примером (вывод показан на Рисунке 13.12):

SELECT * FROM Customers outer WHERE NOT EXISTS (SELECT * FROM Customers inner WHERE outer.rating < = inner.rating AND inner.city = 'Rome');

=============== SQL Execution Log ============ | | | SELECT * | | FROM Customers outer | | WHERE NOT EXISTS | | (SELECT * | | FROM Customers inner | | WHERE outer.rating <= inner.rating | | AND inner.city = 'Rome'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | =============================================

Рисунок 13.12 Использование EXISTS с соотнесённым подзапросом

Это должно также быть выполнено как

SELECT * FROM Customers outer WHERE 1 > (SELECT COUNT (*) FROM Customers inner WHERE outer.rating < = inner.rating AND inner.city = 'Rome');

Вывод к этому запросу показан на Рисунке 13.13. Теперь вы начинаете понимать, сколько способов имеется в SQL. Если это всё кажется несколько сложным на данном этапе, нет причин волноваться. Вы обучаетесь, чтобы использовать ту технику, которая больше отвечает вашим требованиям и наиболее понятна для вас. Начиная с этого места, мы хотим показать вам большое количество возможностей, чтобы вы могли найти ваш собственный стиль.

=============== SQL Execution Log ============ | | | SELECT * | | FROM Customers outer | | WHERE 1 > | | (SELECT COUNT (*) | | FROM Customers inner | | WHERE outer.rating <= inner.rating | | AND inner.city = 'Rome'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | =============================================

Рисунок 13.13 Использование COUNT вместо EXISTS



ИСПОЛЬЗОВАНИЕ DISTINCT


Обратите внимание в вышеупомянутом примере, что DISTINCT, сопровождаемый именем поля, с которым он применяется, помещён в круглые скобки, но не сразу после SELECT, как раньше. Такого использования DISTINCT с COUNT, применяемого к индивидуальным столбцам, требует стандарт ANSI, но большое количество программ не предъявляют такого требования.

=============== SQL Execution Log ============ | | | SELECT COUNT (DISTINCT snum) | | FROM Orders; | | ==============================================| | | | ------- | | 5 | | | | | ===============================================

Рисунок 6.3 Подсчет значений поля

Вы можете выполнять несколько подсчётов (COUNT) в полях с помощью DISTINCT в одиночном запросе, что, как мы видели в Главе 3, не выполнялось, когда вы выбирали строки с помощью DISTINCT.
DISTINCT может использоваться таким образом с любой агрегатной функцией, но наиболее часто он используется с COUNT. С MAX и MIN это просто не будет иметь никакого эффекта, а SUM и AVG вы обычно применяете для включения повторяемых значений, так как они эффективнее общих и средних значений всех столбцов.



ИСПОЛЬЗОВАНИЕ EXISTS С СООТНЕСЁННЫМИ ПОДЗАПРОСАМИ


В соотнесённом подзапросе предложение EXISTS оценивается отдельно для каждой строки таблицы, имя которой указано во внешнем запросе, точно так же, как и другие операторы предиката, когда вы используете соотнесённый подзапрос. Это даёт возможность использовать EXISTS как верный предикат, который генерирует различные ответы для каждой строки таблицы, указанной в основном запросе. Следовательно, информация из внутреннего запроса будет сохранена, если выведена непосредственно, когда вы используете EXISTS таким способом. Например, мы можем вывести продавцов, которые имеют нескольких заказчиков (вывод для этого запроса показан на Рисунке 12.2):

SELECT DISTINCT snum FROM Customers outer WHERE EXISTS (SELECT * FROM Customers inner WHERE inner.snum = outer.snum AND inner.cnum < > outer.cnum);

=============== SQL Execution Log ============ | | | SELECT DISTINCT cnum | | FROM Customers outer | | WHERE EXISTS | | (SELECT * | | FROM Customers inner | | WHERE inner.snum = outer.snum | | AND inner.cnum < > outer.cnum); | | ============================================= | | cnum | | ----- | | 1001 | | 1002 | =============================================

Рисунок 12.2 Использование EXISTS с соотнесённым подзапросом

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



ИСПОЛЬЗОВАНИЕ IN ИЛИ EXISTS ВМЕСТО ANY


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

SELECT * FROM Salespeople WHERE city IN (SELECT city FROM Customers);

Этот запрос даст вывод, показанный на Рисунке 13.2.

Однако оператор ANY может использовать другие реляционные операторы, помимо равно (=), и, таким образом, делать сравнения, которые превосходят возможности IN. Например, мы могли бы найти всех продавцов с их заказчиками, которые следуют в алфавитном порядке (вывод показан на Рисунке 13.3).

SELECT * FROM Salespeople WHERE sname < ANY (SELECT cname FROM Customers);

=============== SQL Execution Log ============ | SELECT * | | FROM Salespeople | | WHERE city IN | | (SELECT city | | FROM Customers); | | ============================================= | | cnum cname city comm | | ----- -------- ---- -------- | | 1001 Peel London 0.12 | | 1002 Serres San Jose 0.13 | | 1004 Motika London 0.11 | =============================================

Рисунок 13.2 Использование IN в качестве альтернативы ANY

=============== SQL Execution Log ============ | SELECT * | | FROM Salespeople | | WHERE sname < ANY | | (SELECT cname | | FROM Customers); | | ============================================= | | cnum cname city comm | | ----- -------- ---- -------- | | 1001 Peel London 0.12 | | 1004 Motika London 0.11 | | 1003 Axelrod New York 0.10 | =============================================

Рисунок 13.3 Использование оператора ANY с операцией "меньше" (<)

продавцов для их заказчиков, которые упорядочены в алфавитном порядке (вывод показан на Рисунке 13.3).

SELECT * FROM Salespeople WHERE sname < ANY (SELECT cname FROM Customers);

Все строки были выбраны для Serres и Rifkin, потому что нет других заказчиков, чьи имена следовали бы за ними в алфавитном порядке. Обратите внимание, что это является основным эквивалентом следующему запросу с EXISTS, вывод которого показан на Рисунке 13.4:

SELECT * FROM Salespeople outer WHERE EXISTS (SELECT * FROM Customers inner WHERE outer.sname < inner.cname);


=============== SQL Execution Log ============ | SELECT * | | FROM Salespeople outer | | WHERE EXISTS | | (SELECT * | | FROM Customers inner | | WHERE outer.sname < inner.cname); | | ============================================= | | cnum cname city comm | | ----- -------- ---- -------- | | 1001 Peel London 0.12 | | 1004 Motika London 0.11 | | 1003 Axelrod New York 0.10 | =============================================

Рисунок 13. 4 Использование EXISTS как альтернативы оператору ANY

Любой запрос, который может быть сформулирован с ANY (или, как мы увидим, с ALL),  мог бы быть сформулирован также с EXISTS, хотя обратное будет неверно. Строго говоря, вариант с EXISTS не абсолютно идентичен вариантам с ANY или с ALL из-за различия в обработке пустых (NULL) значений (что будет обсуждаться позже в этой главе). Тем не менее, с технической точки зрения, вы могли бы делать это без ANY и ALL, если проявите находчивость в использовании EXISTS (и IS NULL).

Большинство пользователей, однако, находят ANY и ALL более удобными в использовании, чем EXISTS, который требует соотнесённых подзапросов. Кроме того, в зависимости от реализации, ANY и ALL могут, по крайней мере в теории, быть более эффективными, чем EXISTS. Подзапросы ANY или ALL могут выполняться один раз и иметь вывод, используемый, чтобы определять предикат для каждой строки основного запроса. EXISTS, с другой стороны, берёт соотнесенный подзапрос, который требует, чтобы весь подзапрос повторно выполнялся для каждой строки основного запроса. SQL пытается найти наиболее эффективный способ выполнения любой команды и может попробовать преобразовать менее эффективную формулу запроса в более эффективную (но вы не можете всегда рассчитывать на получение самой эффективной формулировки).

Основная причина для формулировки EXISTS как альтернативы ANY и ALL в том, что ANY и ALL могут быть несколько неоднозначны из-за способа использования этого термина в английском языке, как вы это скоро увидите. С приходом понимания различия способов формулирования данного запроса, вы сможете поработать над процедурами, которые сейчас кажутся вам трудными или неудобными.


ИСПОЛЬЗОВАНИЕ NOT EXISTS


Предыдущий пример показал, что EXISTS может работать в комбинации с булевыми операторами. Конечно, самым простым способом (и, вероятно, чаще всего используемым с EXISTS) является оператор NOT. Один из способов, которым мы могли бы найти всех продавцов только с одним заказчиком, будет состоять в том, чтобы инвертировать наш предыдущий пример. (Вывод для этого запроса показан на Рисунке 12.4:)

SELECT DISTINCT snum FROM Customers outer WHERE NOT EXISTS (SELECT * FROM Customers inner WHERE inner.snum = outer.snum AND inner.cnum < > outer.cnum);



ИСПОЛЬЗОВАНИЕ NOT СО СПЕЦИАЛЬНЫМИ ОПЕРАТОРАМИ


Операнды могут непосредственно предшествовать булеву NOT.

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

SELECT * FROM Customers WHERE city NOT NULL;

При отсутствии значений NULL (как в нашем случае), будет выведена вся таблица Заказчиков. Аналогично можно ввести следующее

SELECT * FROM Customers WHERE NOT city IS NULL;

что также приемлемо. Мы можем также использовать NOT с IN:

SELECT * FROM Salespeople WHERE city NOT IN ('London', 'San Jose');

А вот другой способ подобного же выражения:

SELECT * FROM Salespeople WHERE NOT city IN ('London', ' San Jose');

Вывод для этого запроса показан на Рисунке 5.9.

Таким же способом вы можете использовать NOT BETWEEN и NOT LIKE.

=============== SQL Execution Log ============ | | | SELECT * | | FROM Salespeople | | WHERE сity NOT IN ('London', 'San Jose';) | | ==============================================| | snum sname city comm | | ------ ---------- ----------- ------- | | 1003 Rifkin Barcelona 0.15 | | 1007 Axelrod New York 0.10 | | | ===============================================

Рисунок 5.9 Использование NOT с IN



ИСПОЛЬЗОВАНИЕ ОГРАНИЧЕНИЙ ДЛЯ ИСКЛЮЧЕНИЯ ПУСТЫХ (NULL) ЗНАЧЕНИЙ


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

Очевидно, что первичные ключи никогда не должны быть пустыми, поскольку это будет нарушать их функциональные возможности. Кроме того, такие поля как имена требуют в большинстве случаев определённых значений. Например, вы, вероятно, захотите иметь имя для каждого заказчика в таблице Заказчиков. Если вы поместите ключевые слова NOT NULL сразу после типа данных (включая размер) столбца, любая попытка поместить значение NULL в это поле будет отклонена. В противном случае SQL принимает, что NULL разрешен.

Например, давайте улучшим наше определение таблицы Продавцов, не позволяя помещать NULL-значения в столбцы snum или sname:

CREATE TABLE Salespeople (Snum integer NOT, Sname char (10) NOT, city char (10), comm decimal);

Важно помнить, что любому столбцу с ограничением NOT NULL должно быть установлено значение в каждом предложении INSERT, воздействующем на таблицу. При отсутствии NULL, SQL может не иметь значений для установки в эти столбцы, если, конечно, значение по умолчанию, описанное ранее в этой главе, уже не было назначено. Если ваша система поддерживает использование ALTER TABLE, чтобы добавлять новые столбцы к уже существующей таблице, вы можете, вероятно, помещать ограничение столбцов типа NOT NULL для этих новых столбцов. Однако, если вы предписываете новому столбцу значение NOT NULL, текущая таблица должна быть пустой.



ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННОЙ INDICATOR ДЛЯ ЭМУЛЯЦИИ NULL-ЗНАЧЕНИЙ SQL


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

Например, если NULL-значение было извлечено из поля city для главной переменной city, которая связана с переменной indicator i_city, вы должны установить значение city, равное последовательности пробелов. Это будет необходимо, только если вы будете распечатывать его на принтере; его значение не должно отличаться от логики вашей программы. Естественно, i_city автоматически устанавливается в отрицательное значение.

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

If sity = 'London' then comm: = comm + .01 else comm: = comm - .01

Любое значение, вводимое в переменную city, будет равно "London" или не будет равно. Следовательно, в каждом случае значение комиссионных будет либо увеличено, либо уменьшено. Однако эквивалентные команды в SQL выполняются по разному:

EXEC SQL UPDATE Salespeople SET comm = comm + .01 WHERE sity = 'London';

и

EXEC SQL UPDATE Salespeople SET comm = comm .01; WHERE sity < > 'London';

(Вариант на ПАСКАЛе работает только с единственным значением, в то время как вариант на SQL работает со всеми таблицами.) Если значение city в варианте на SQL будет равно значению NULL, оба предиката будут неизвестны, и значение comm, следовательно, не будет изменено в любом случае. Вы можете использовать переменную indicator, чтобы сделать поведение вашего главного языка не противоречащим этому, с помощью создания условия, которое исключает NULL значения:

If i_city > = O then begin If city = 'London' then comm: = comm + .01 else comm: = comm - .01; end; {begin and end нужны здесь только для понимания}



ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННЫХ ОСНОВНОГО ЯЗЫКА В SQL


Основной способ, которым SQL и части базового языка ваших программ будут связываться друг с другом - значения переменных. Естественно, что разные языки распознают различные типы данных для переменных. ANSI определяет эквиваленты SQL для четыре базовых языков: ПЛ/1, Паскаль, КОБОЛ и ФОРТРАН; всё это подробно описано в Приложении B. Эквиваленты для других языков определяет проектировщик.

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

Текущим значением переменной может быть значение, используемое в команде. Главные переменные должны:

быть объявленными в SQL DECLARE SESSION (РАЗДЕЛ ОБЪЯВЛЕНИЙ), который будет описан далее;

иметь совместимый тип данных с их функциями в команде SQL (например, числовой тип, если он вставляется в числовое поле);

быть присвоенными значению во время их использования в команде SQL, если команда SQL самостоятельно не может сделать назначение;

предшествовать двоеточию (:), когда они упоминаются в команде SQL.

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

Предположим, что у вас есть четыре переменные с именами id_num, salesperson, loc и comm. Они содержат значения, которые вы хотите вставить в таблицу Продавцов. Вы могли бы вложить следующую команду SQL в вашу программу:

EXEC SQL INSERT INTO Salespeople VALUES (:id_num, :salesperson, :loc, :comm)

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


Для Паскаля и PL/1 это будет точка с запятой, для КОБОЛА - слово END-EXEC, а для ФОРТРАНА не будет никакого завершения. В других языках это зависит от реализации, и поэтому мы договоримся, что будем использовать точку с запятой (в этой книге) всегда, чтобы не противоречить интерактивному SQL и Паскалю. Паскаль завершает вложенный SQL и собственные команды одинаково: точкой с запятой. Способ сделать команду полностью такой, как описано выше, состоит в том, чтобы включать её в цикл и повторять её с различными значениями переменных, как показано в следующем примере:

while not end-ot-file (input) do begin readln (id_num, salesperson, loc, comm); EXEC SOL INSERT INTO Salespeople VALUES (:id_num, :salesperson, :loc, :comm); end;

Фрагмент программы на ПАСКАЛЕ определяет цикл, который будет считывать значения из файла, сохранять их в четырёх именованных переменных, сохранять значения этих переменных в таблице Продавцов, а затем считывать следующие четыре значения, повторяя этот процесс до тех пор, пока весь входной файл не будет прочитан. Считается, что каждый набор значений завершается возвратом каретки (для не знакомых с Паскалем: функция readln считывает вводимую информацию и переходит на следующую строку источника этой информации). Это дает вам простой способ передать данные из текстового файла в реляционную структуру.

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

while not end-ot-file (input) do begin readln (id_num, salesperson, loc, comm); if comm > = .12 then EXEC SQL INSERT INTO Salespeople VALUES (:id_num, :salesperson, :loc, :comm); end;

Только строки, которые выполнят условие comm >= .12, будут вставлены в вывод. Это показывает, что можно использовать и циклы, и условия как нормальные для главного языка.


ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ, КОТОРЫЕ ВЫДАЮТ МНОГО СТРОК С ПОМОЩЬЮ ОПЕРАТОРА IN


Вы можете использовать подзапросы, которые производят любое число строк, если вы применяете специальный оператор IN (операторы BETWEEN, LIKE и IS NULL не могут использоваться с подзапросами). Как вы помните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в заказе, чтобы предикат был верным.

Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой подзапрос, который не будет работать с реляционным оператором, и найти все атрибуты таблицы Заказов для продавца в Лондоне (вывод показан на Рисунке 10.4):

SELECT * FROM Orders WHERE snum IN (SELECT snum FROM Salespeople WHERE city = "LONDON");

=============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum IN | | (SELECT snum | | FROM Salespeople | | WHERE city = 'London'); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3003 767.19 10/03/1990 2001 1001 | | 3002 1900.10 10/03/1990 2007 1004 | | 3006 1098.19 10/03/1990 2008 1007 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================

Рисунок 10.4 Использование подзапроса с IN

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

SELECT onum, amt, odate, cnum, Orders.snum FROM Orders, Salespeople WHERE Orders.snum = Salespeople.snum AND Salespeople.city = "London";

Хотя это и произведёт тот же самый вывод, что в примере с подзапросом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эффективнее извлекать из таблицы Продавцов значения поля snum, где city = "London", а затем искать эти значения в таблице Заказов, как это делается в варианте с подзапросом. Внутренний запрос даёт нам snums=1001 и snum=1004. Внешний запрос затем даёт нам строки из таблицы Заказов, где эти поля snum найдены.


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

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

SELECT onum, amt, odate FROM Orders WHERE snum = (SELECT snum FROM Orders WHERE cnum = 2001);

Вы можете устранить потребность в DISTINCT, используя IN вместо (=):

SELECT onum, amt, odate FROM Orders WHERE snum IN (SELECT snum FROM Orders WHERE cnum = 2001);

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



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

SELECT comm FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE city = "London");

Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в Лондоне. Но это только для данного случая. Нет никакой причины, чтобы некоторые заказчики в Лондоне не могли быть назначены кому-то ещё. Следовательно, IN - это наиболее логичная форма для использования в запросе.

=============== SQL Execution Log ============== | | | SELECT comm | | FROM Salespeople | | WHERE snum IN | | (SELECT snum | | FROM Customers | | WHERE city = 'London'); | | =============================================== | | comm | | ------- | | 0.12 | | | | | ================================================

Рисунок 10.5 Использование IN с подзапросом для вывода одного значения

Между прочим, префикс таблицы для поля city в предыдущем примере не обязателен, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов. SQL всегда ищет первое поле в таблице, обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в предложении WHERE означает, что имеется ссылка на Customer.city (поле city таблицы Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает что это правильно. Это предположение может быть отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим позже, когда будем говорить о соотнесенных подзапросах. Если возможен беспорядок, конечно же, лучше всего использовать префиксы.


ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С DELETE


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

DELETE FROM Customers WHERE snum = ANY (SELECT snum FROM Salespeople WHERE city = 'London');

Эта команда удалит из таблицы Заказчиков строки Hoffman и Clemens (назначенных для Peel), и Pereira (назначенного для Motika). Конечно, вы захотите удостовериться, правильно ли сформирована эта операция, прежде чем удалить или изменить строки Peel и Motika.

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

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

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

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


DELETE FROM Salespeople WHERE EXISTS (SELECT * FROM Customers WHERE rating = 100 AND Salespeople.snum = Customers.snum);

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

DELETE FROM Salespeople WHERE 100 IN (SELECT rating FROM Customers WHERE Salespeople.snum = Customers.snum);

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

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

DELETE FROM Salespeople WHERE snum IN (SELECT snum FROM Orders WHERE amt = (SELECT MIN (amt) FROM Orders b WHERE a.odate = b.odate));

Подзапрос в предикате DELETE принимает соотнесённый подзапрос. Этот внутренний запрос находит минимальный заказ суммы приобретений для даты каждой строки внешнего запроса. Если эта сумма - такая же, как и сумма текущей строки, предикат внешнего запроса верен, что означает, что текущая строка имеет наименьший заказ для этой даты. Поле snum продавца, ответственного за этот заказ, извлекается и передается в основной предикат команды DELETE, которая затем удаляет все строки с этим значением поля snum из таблицы Продавцов (так как snum это первичный ключ таблицы Продавцов, то, естественно, там должна иметься только одна удаляемая строка для значения поля snum, выведенного с помощью подзапроса. Если имеется больше одной строки, все они будут удалены.) Поле snum = 1007, которое будет удалено, имеет наименьшее значение на 3 октября; поле snum = 1002, наименьшее на 4 октября; поле snum = 1001, наименьшее в заказах на 5 октября (эта команда кажется довольно грубой, особенно когда она удаляет Peel создавшего единственный заказ на 5 октября, но зато это хорошая иллюстрация).

Если вы хотите сохранить Peel, вы могли бы добавить другой подзапрос, который сделал бы это:

DELETE FROM Salespeople WHERE snum IN (SELECT snum FROM Orders a WHERE amt = (SELECT MIN (amt) FROM Orders b WHERE a.odate = b.odate) AND 1 < (SELECT COUNT onum FROM Orders b WHERE a.odate = b.odate));

Теперь для дня, в котором был создан только один заказ, будет произведен счёт = 1 во втором соотнесённом подзапросе. Это сделает предикат внешнего запроса неправильным, и поля snum, следовательно, не будут переданы в основной предикат.


ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С INSERT


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

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

INSERT INTO SJpeople SELECT * FROM Salespeople WHERE city = 'San Jose';

Теперь мы можем использовать подзапрос, чтобы добавить в таблицу SJpeople всех продавцов, которые имеют заказчиков в San Jose, независимо от того, находятся ли там продавцы или нет:

INSERT INTO SJpeople SELECT * FROM Salespeople WHERE snum = ANY (SELECT snum FROM Customers WHERE city = 'San Jose');

Оба запроса в этой команде функционируют так же, как если бы они не являлись частью выражения INSERT. Подзапрос находит все строки для заказчиков в San Jose и формирует набор значений snum. Внешний запрос выбирает строки из таблицы Salespeople, где эти значения snum найдены. В этом примере, строки для продавцов Rifkin и Serres, которые назначены заказчикам в San Jose - Liu и Cisneros, будут вставлены в таблицу SJpeople.



ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С UPDATE


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

UPDATE Salespeople SET comm = comm + .01 WHERE 2 < = (SELECT COUNT (cnum) FROM Customers WHERE Customers.snum = Salespeople.snum);

Теперь продавцы Peel и Serres, имеющие нескольких заказчиков, получат повышение своих комиссионных. Имеется разновидность последнего примера из предыдущего раздела с DELETE. Он уменьшает комиссионные продавцов которые оформили наименьшие заказы, но не стирает их в таблице:

UPDATE Salespeople SET comm = comm - .01 WHERE snum IN (SELECT snum FROM Orders a WHERE amt = (SELECT MIN (amt) FROM Orders b WHERE a.odat = b.odate));



ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ, СОЗДАННЫХ ВО ВНЕШНИХ ЗАПРОСАХ ТАБЛИЦ


Запрещение на обращение к таблице, которая модифицируется командой INSERT, не предохранит вас от использования подзапросов, которые обращаются к таблице, используемой в предложении FROM внешней команды SELECT. Таблица, из которой вы выбираете значения, чтобы произвести их для INSERT, не будет задействована командой, и вы сможете обращаться к этой таблице любым способом, которым вы обычно это делали, но только если эта таблица указана в автономном запросе. Предположим, что имеется таблица Samecity, в которой мы запомним продавцов с заказчиками в их городах. Мы можем заполнить таблицу используя, соотнесённый подзапрос:

INSERT INTO (Samecity SELECT * FROM (Salespeople outer WHERE city IN (SELECT city FROM Customers inner WHERE inner.snum = outer.snum);

Ни таблица Samecity, ни таблица Продавцов не должны быть использованы во внешних или внутренних запросах INSERT. В качестве другого примера предположим, что у вас установлена премия для продавца, имеющего самый большой заказ, на каждый день. Вы следите за этим в таблице с именем Bonus, которая содержит поле snum продавцов, поле odate и поле amt. Вы должны заполнить эту таблицу информацией, которая хранится в таблице Заказов, используя следующую команду:

INSERT INTO Bonus SELECT snum, odate, amt FROM Orders a WHERE amt = (SELECT MAX (amt) FROM Orders b WHERE a.odate = b.odate);

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



ИСПОЛЬЗОВАНИЕ ПРЕДСТАВЛЕНИЙ ДЛЯ ФИЛЬТРАЦИИ ПРИВИЛЕГИЙ


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

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



ИСПОЛЬЗОВАНИЕ ПРЕДСТАВЛЕНИЙ В ТАБЛИЦАХ КАТАЛОГА


Поскольку SYSTEMCATALOG это таблица, вы можете использовать её в представлении. Фактически можно считать, что имеется такое представление с именем SYSTEMTABLES.

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

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

CREATE VIEW Datatables AS SELECT * FROM SYSTEMCATALOG WHERE owner < > 'SYSTEM';

РАЗРЕШИТЬ ПОЛЬЗОВАТЕЛЯМ ВИДЕТЬ (ТОЛЬКО) ИХ СОБСТВЕННЫЕ ОБЪЕКТЫ

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

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

CREATE VIEW Owntables AS SELECT * FROM SYSTEMCATALOG WHERE Owner = USER;

Теперь вы можете предоставить всем пользователям доступ к этому представлению:

GRANT SELECT ON Owntables TO PUBLIC;

Каждый пользователь теперь способен выбирать (SELECT) только те строки из SYSTEMCATALOG, владельцем которых он сам является.

ПРЕДСТАВЛЕНИЕ SYSTEMCOLUMNS

Одно небольшое добавление к этому позволит каждому пользователю просматривать таблицу SYSTEMCOLUMNS для столбцов из его собственных таблиц.

Сначала давайте рассмотрим ту часть таблицы SYSTEMCOLUMNS, которая описывает наши типовые таблицы (другими словами, исключим сам каталог):

tname cname datatype cnumber tabowner ----------- ----- -------- ------- --------

Salespeople snum integer 1 Diane

Salespeople sname char 2 Diane

Salespeople city char 3 Diane


Salespeople comm decimal 4 Diane

Customers cnum integer 1 Claire

Customers cname char 2 Claire

Customers city char 3 Claire

Customers rating integer 4 Claire

Customers snum integer 5 Claire

Orders onum integer 1 Diane

Orders odate date 2 Diane

Orders amt decimal 3 Diane

Orders cnum integer 4 Diane

Orders snum integer 5 Diane

Как видите, каждая строка этой таблицы показывает столбец таблицы в БД. Все столбцы данной таблицы должны иметь разные имена, так же как каждая таблица должна иметь данного пользователя, и, наконец, все комбинации пользователей, таблиц и имён столбцов должны различаться между собой.

Следовательно, табличные столбцы: tname (имя таблицы), tabowner (владелец таблицы) и cname (имя столбца) вместе составляют первичный ключ этой таблицы. Столбец datatype (тип данных) говорит сам за себя. Столбец cnumber (номер столбца) указывает на местоположение этого столбца внутри таблицы. Для упрощения мы опустили параметры длины столбца, точности и масштаба.

Для справки показана строка из SYSTFMCATALOG, которая ссылается на эту таблицу:

tname owner numcolumns type CO ------------- ------ ----------- ----- ---

SYSTEMCOLUMNS System 8 B

Некоторые SQL-реализации будут обеспечивать вас б&#x0301;ольшим количеством данных, чем показано в этих столбцах, но показанное является основой любых реализаций.

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

CREATE VIEW Owncolumns AS SELECT * FROM SYSTEMCOLUMNS WHERE tabowner = USER;

GRANT SELECT ON Owncolumns TO PUBLIC;


ИСПОЛЬЗОВАНИЕ СООТНЕСЁННЫХ ПОДЗАПРОСОВ ДЛЯ ПОИСКА ОШИБОК


Иногда полезно выполнять запросы, которые разработаны специально так, чтобы находить ошибки. Это всегда возможно при появлении дефектной информации, которую можно ввести в вашу БД, и, если она введена, бывает трудно её выявить. Следующий запрос не должен производить никакого вывода. Он просматривает таблицу Заказов, чтобы увидеть, совпадают ли поля snum и cnum в каждой строке таблицы Заказчиков, и выводит каждую строку, где этого совпадения нет. Другими словами, запрос выясняет, тот ли продавец кредитовал каждую продажу (он принимает поле cnum как первичный ключ таблицы Заказчиков, который не будет иметь никаких двойных значений в этой таблице).

SELECT * FROM Orders main WHERE NOT snum = (SELECT snum FROM Customers WHERE cnum = main.cnum);

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



ИСПОЛЬЗОВАНИЕ SQLCODE ДЛЯ УПРАВЛЕНИЯ ЦИКЛАМИ


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

Look_at_more: = lhe; EXEC SQL OPEN CURSOR Londonsales; while Look_at_more and SQLCODE = O do begin EXEC SQL FETCH London$ales INTO :id_num, :Salesperson, :loc, :comm; writeln (id_num, Salesperson, loc, comm); writeln ('Do you want to see more data? (Y/N)'); readln (response); If response = 'N' then Look_at_more: = Fabe; end; EXEC SQL CLOSE CURSOR Londonsales;



ИСПОЛЬЗОВАНИЕ СТРОК И ВЫРАЖЕНИЙ С UNION


Иногда вы можете вставлять константы и выражения в предложения SELECT, используемые с UNION. Это не следует строго указаниям ANSI, но это полезная и необычно используемая возможность. Константы и выражения, используемые вами, должны соответствовать совместимым стандартам, как мы говорили ранее. Эта свойство полезно, например, чтобы устанавливать комментарии, указывающие, какой запрос вывел данную строку. Предположим, что вы должны сделать отчёт о том, какие продавцы выполняют наибольшие и наименьшие заказы по датам. Мы можем объединить два запроса, вставив туда текст, чтобы различать вывод каждого из них.

SELECT a.snum, sname, onum, 'Highest on', odate FROM (Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders c WHERE c.odate = b.odate)

UNION

SELECT a.snum, (sname, (onum ' Lowest on', odate FROM (Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MIN (amt) FROM Orders c WHERE c.odate = b.odate);

Вывод этой команды  показан на Рисунке 14.4. Мы должны были добавить дополнительный пробел в строку 'Lowest on', чтобы сделать её совпадающей по длине со строкой 'Highest on'. Обратите внимание, что Peel выбран при наличии и самого высокого, и самого низкого (фактически он единственный) заказов на 5 октября. Так как вставляемые строки двух этих запросов различны, строки не будут устранены как дубликаты.

=============== SQL Execution Log ============ | | | AND b.amt = | | (SELECT min (amt) | | FROM Orders c | | WHERE c.odate = b.odate); | | ============================================= | | | | ----- ------- ------ ---------- ----------- | | 1001 Peel 3008 Highest on 10/05/1990 | | 1001 Peel 3008 Lowest on 10/05/1990 | | 1001 Peel 3011 Highest on 10/06/1990 | | 1002 Serres 3005 Highest on 10/03/1990 | | 1002 Serres 3007 Lowest on 10/04/1990 | | 1002 Serres 3010 Lowest on 10/06/1990 | | 1003 Axelrod 3009 Highest on 10/04/1990 | | 1007 Rifkin 3001 Lowest on 10/03/1990 | ===============================================

Рисунок 14.4 Выбор наивысших и наинизших заказов, определяемых с помощью строк



ИСПОЛЬЗОВАНИЕ UNION С ORDER BY


До сих пор мы не оговаривали, что данные нескольких запросов будут выводиться в каком-то особом порядке. Мы просто показывали вывод сначала из одного запроса, а затем из другого. Конечно, вы не можете полагаться на вывод, приходящий в произвольном порядке. Мы как раз сделаем так, чтобы этот способ выполнения примеров был более простым. Вы можете использовать предложение ORDER BY, чтобы упорядочить вывод из объединения, точно так же, как это делается в индивидуальных запросах. Давайте пересмотрим наш последний пример, чтобы упорядочить имена с помощью их порядковых номеров. Это может внести противоречие, такое как повторение имени Peel в последней команде, как вы сможете увидеть из вывода показанного в Рисунке 14.5.

SELECT a.snum, sname, onum, 'Highest on', odate FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders c WHERE c.odate = b.odate)

UNION

SELECT a.snum, sname, onum, 'Lowest on', odat FROM Salespeople a, Orders b WHEREa.snum = b.snu AND b.amt = (SELECT MIN (amt) FROM Orders c WHERE c.odate = b.odate)

ORDER BY 3;

=============== SQL Execution Log ============ | (SELECT min (amt) | | FROM Orders c | | WHERE c.odate = b.odate) | | ORDER BY 3; | | ============================================= | | | | ----- ------- ------ ---------- ----------- | | 1007 Rifkin 3001 Lowest on 10/03/1990 | | 1002 Serres 3005 Highest on 10/03/1990 | | 1002 Serres 3007 Lowest on 10/04/1990 | | 1001 Peel 3008 Highest on 10/05/1990 | | 1001 Peel 3008 Lowest on 10/05/1990 | | 1003 Axelrod 3009 Highest on 10/04/1990 | | 1002 Serres 3010 Lowest on 10/06/1990 | | 1001 Peel 3011 Highest on 10/06/1990 | ===============================================

Рисунок 14.5 Формирование объединения с использованием ORDER BY

Пока ORDER BY используется по умолчанию, мы не должны его указывать. Мы можем упорядочить наш вывод с помощью нескольких полей, одно внутри другого, и указать ASC или DESC для каждого, точно так же, как мы делали это для одиночных запросов. Заметьте, что номер 3 в предложении ORDER BY указывает, какой столбец из предложения SELECT будет упорядочен. Так как столбцы объединения это столбцы вывода, они не имеют имён, и, следовательно, должны определяться по номеру. Этот номер указывает их место среди других столбцов вывода. (Смотрите Главу 7, обсуждающую столбцы вывода.)



ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ ДЛЯ МОДИФИКАЦИИ


Вы можете использовать скалярные выражения в предложении SET команды UPDATE, включив его в выражение поля, которое будет изменено. В этом их отличие от предложения VALUES команды INSERT, в котором выражения не могут использоваться; это свойство скалярных выражений - весьма полезная особенность. Предположим, что вы решили удвоить комиссионные всем вашим продавцам. Вы можете использовать следующее выражение:

UPDATE Salespeople SET comm = comm * 2;

Всякий раз, когда вы обращаетесь к указанному значению столбца в предложении SET, произведённое значение может получится из текущей строки; прежде в ней будут сделаны какие-то изменения с помощью команды UPDATE. Естественно, вы можете скомбинировать эти особенности и сказать "удвоить комиссию всем продавцам в Лондоне" таким предложением:

UPDATE Salespeople SET comm = comm * 2 WHERE city = 'London';