    24 ГЛАВА 

    Дополнительные сведения об SQL 

    Эта глава не является непосредственным продолжением главы 5, "SQL для 
простых смертных". Базовый синтаксис операторов SQL значительно отличается от 
полного синтаксиса, поэтому существует разрыв между материалом, изложенным в 
этой главе и во вводной главе по SQL. Данная глава предназначена для тех, кто 
уже имеет опыт работы с SQL, а глава 5 адресована начинающим. 
    Предполагается, что вы уже знаете, как подключиться к базе данных и 
выполнить команды SQL с помощью вашего какого-нибудь редактора операторов SQL. 
В примерах этой главы используется СУБД InterBase и ее утилита WISQL. Вы 
можете работать с любым подходящим редактором. 
    В этой главе широко используется база данных и таблицы, созданные в главе 
5. Создайте эти объекты, прежде чем приступать к работе с примерами. Для 
получения более подробной информации обращайтесь к главе 5. 
    Некоторые таблицы, используемые в этой главе, были определены в части 
"Учебный курс" этой книги. Их содержимое не играет существенной роли для 
понимания представленных здесь идей, но при желании за информацией об этих 
таблицах можно обратиться к главе 8, "Ваша первая система клиент/сервер". 
    В этой главе сравниваются два главных семейства синтаксиса SQL: ANSI и 
Sybase. В СУБД InterBase используются соглашения ANSI SQL. Поэтому примеры, 
созданные с использованием синтаксиса InterBase, будут работать и на других 
поддерживающих соглашения ANSI платформах, таких как Oracle. В то же время, 
СУБД Sybase SQL Server, как и ее лицензированная "кузина" Microsoft SQL 
Server, во многом существенно отклоняются от стандарта ANSI. 

    На заметку 
    Хотя в этой главе часто сравниваются различные диалекты SQL, системы 
управления базами более подробно описаны в главах 15-18. Каждой из платформ - 
Oracle, InterBase, Sybase и Microsoft SQL Server - посвящена отдельная глава, 
содержащая много полезных сведений, включая нюансы диалекта SQL для данной СУБД. 
    Я умышленно старался избегать повторения информации, содержащейся в 
документации по той или иной СУБД. Задача этой главы — коснуться различных 
тонкостей использования SQL. Некоторые из рассматриваемых вопросов отнесены к 
сложным, поскольку они предназначены не для новичков. Другие являются 
действительно сложными для понимания. Полное описание SQL — это обширная тема, 
которой хватило бы на несколько книг. В данной главе рассмотрено достаточное 
количество вопросов, поэтому каждый сможет найти что-нибудь для себя. 
    Обратите внимание, что я стараюсь говорить на языке SQL. Вместо того, 
чтобы пытаться описать SQL на языке людей, я объясняю SQL на самом SQL. В этом 
я поступаю по примеру учительницы французского языка, которая преподает на 
французском и заставляет студентов изъясняться по-французски. Если вы намерены 
пользоваться языком SQL, давайте говорить на нем! 

    448 IV. Темы повышенной сложности 

    DDL иDML 

    Мы рассмотрим две широкие категории операторов SQL: команды языка описания 
данных (DDL — Data Definition Language) и языка управления данными (DML — Data 
Manipulation Language). Язык DDL используется для создания объектов базы 
данных и управления ими. Например, CREATE TABLE — это оператор DDL. Язык DML 
управляет запросами и обновлением данных этих объектов. Примером команды DML 
является оператор SQL UPDATE. Мы обсудим синтаксис как DDL, так и DML, чтобы 
было легче понять различие между ними. Хотя более язык SQL охватывает как DDL, 
так и DML, полезно знать, к какому классу относится та или иная команда SQL. 
Команды каждого из этих классов имеют между собой много общего. 

    Синтаксис операторов DDL 

    Приведенные ниже операторы служат для создания и изменения объектов баз 
данных. Они не обновляют и не модифицируют сами данные, а лишь определяют 
способ хранения данных и получения доступа к ним со стороны пользователей и 
других объектов. 
    Базы данных Как отмечалось в главе 5, команда CREATE DATABASE используется 
для создания новых баз данных. Поскольку со временем объем данных возрастает, 
вам потребуется расширить базы данных, созданные ранее. Это поможет повысить 
производительность за счет распределения базы данных между несколькими 
логическими дисками и (в зависимости от платформы) позволит хранить в базе 
больше данных. Чтобы расширить существующую базу данных, используйте команду 
ALTER DATABASE. Вот синтаксис InterBase. 

    ALTER DATABASE 
    ADD 'C:\DATA\IB\ORDENT2' 

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

    ALTER DATABASE ORDENT ON ORDENT2=100 

    В СУБД Sybase и Microsoft SQL Server команда ALTER DATABASE действительно 
увеличивает физический размер и объем базы данных, а не только позволяет 
распределить базу данных по нескольким дискам. 

    Сегменты и табличные области 

    Для повышения производительности большой базы данных ее зачастую 
распределяют по нескольким различным дискам, размещая отдельные объекты базы 
данных на отдельных дисках. В СУБД Microsoft и Sybase SQL Server для этой цели 
используются сегменты (segment) базы данных. Поместив таблицу на один диск, а 
ее индексы — на другой, можно значительного улучшить производительность, 
особенно если эти диски управляются различными контроллерами. 
    |	Таблицы и индексы в Sybase SQL Server (System 10 и более поздних 
версиях) можно разделить следующим образом. 
    1.   Создайте новую связь с физическим диском (в терминологии SQL Server 
эта связь называется устройством (device)) с помощью команды DISK INIT. 

    DISK INIT 
    name ="INDDEV", 
    physname="SYS:DATA\INDDEV.DAT", 
    vdevno=25, 
    size=51200 
    2.   Поместите базу данных на этот диск с помощью команды ALTER DATABASE. 
    I 
    ALTER DATABASE ORDENT ON INDDEV=100 

    Глава 24. Дополнительные сведения об SQL	449 
    15 Delphi 3 м системы клиент/сервер 

    3.   Создайте новый сегмент на вновь созданном устройстве с помощью 
команды sp_addsegment. sp_addsegment   'indexeseg',    'ORDENT',    'INDDEV 
    где  'indexeseg'  — имя нового сегмента,  'ORDENT'  — имя базы данных и ' 
INDDEV  — имя только что добавленного устройства. 
    4.    На вновь образованном сегменте создайте объект базы данных. 
Например, с помощью следующего синтаксиса можно на новом сегменте создать индекс. 
    CREATE   INDEX   INVOICES02   ON   INVOICES   (CustomerNumber)   ON   indexeseg 
    Теперь таблица INVOICES и индекс INVOICES02 расположены на различных 
сегментах и, надеюсь, на разных дисках, за счет чего исключается обращение к 
одной головке диска при обращении к таблице с использованием индекса INVOICES02. 
    В Oracle эту же задачу можно выполнить за счет создания табличной области 
(tablespace) на различных дисках. Для этого используется команда CREATE 
TABLESPACE, а для создания новых объектов в новой табличной области — команды 
CREATE TABLE и CREATE INDEX. Например, в следующем фрагменте создается 
табличная область. 

    CREATE TABLESPACE indexspace 
    DATAFILE 'c:\oracle\indexspace.dat' SIZE 500K REUSE 
    AUTOEXTEND ON NEXT 50OK MAXSIZX' 10M; 

    Следующая команда помещает в эту табличную область индекс. 

    CREATE   INDEX  emp02   ON  emp   (ename,    job)   TABLESPACE   indexspace; 

    Поскольку табличная область представляет собой специальный файл данных, то 
индекс связывается с этим файлом. Если файл и сама таблица расположены на 
различных дисках, значит сегментация окажется эффективной и поможет уменьшить 
задержки при выполнении операций ввода/вывода, которые часто происходят при 
работе с интенсивно используемыми таблицами. 
    Оптимальный проект базы данных предполагает планирование сегментации до 
начала создания объектов базы данных, поскольку их довольно трудно переместить 
впоследствии. Выбирая вариант сегментации базы данных, придерживайтесь 
следующих трех правил. 
    Q Держите журналы регистрации транзакций и данные на различных дисках. 
Хранить журналы регистрации на одном физическом устройстве с данными не только 
опасно, поскольку они могут пострадать одновременно, но и нерационально с 
точки зрения производительности, поскольку операции с данными и журналом 
регистрации будут конкурировать между собой. В целях предотвращения катастрофы 
с данными и повышения производительности, размещайте журнал регистрации и 
данные на разных дисках. 
    Q  Пытайтесь отделить таблицы от соответствующих некластерных индексов. 
Для этого обычно помещают все таблицы в базе данных на один диск, а индексы — 
на другой. Заметим, что кластерный индекс и саму таблицу нельзя разместить на 
различных дисках. Если вы попытаетесь сделать это, то таблица "последует" за 
своим кластерным индексом и переместится на сегмент, определенный для 
кластерного индекса. (Кластерные индексы обсуждаются в разделе "Кластерные и 
некластерные индексы" ниже в этой главе.) 
    Q  Старайтесь также разделить и поместить на различные диски основные 
таблицы, особенно те из них, доступ к которым может осуществляться 
одновременно. Если в базе данных имеются три основные таблицы и огромное 
количество других, менее важных таблиц, то идеальная конфигурация предполагает 
наличие как минимум восьми различных дисков: по одному для каждой из трех 
основных таблиц, один для менее важных таблиц, и соответствующие диски с 
индексами по числу дисков с таблицами. 
    Заметим, что InterBase не поддерживает сегментацию, хотя вы можете 
распределить базу данных по нескольким дискам. Однако при этом для отдельных 
дисков нельзя определить отдельные объекты базы данных. 

    Индексы 

    Индексы в SQL создаются (как вы уже догадались) с использованием команды 
CREATE  INDEX. Вот ее основной синтаксис: 
    CREATE INDEX INVOICES02 ON INVOICES (CustomerNumber) 
    где INVOICES02 — имя нового индекса, INVOICES — имя таблицы, для которой 
создается индекс, а 
    CustomerNumber — ключ индекса. 

    450 IV. Темы повышенной сложности 

    В InterBase имена индексов должны быть уникальными для всей базы данных, в 
которой они находятся. В СУБД Sybase и Microsoft SQL Server они должны быть 
уникальными только в пределах каждой таблицы. 

    Уникальные индексы 

    Уникальные индексы создаются с помощью варианта команды создания индексов 
CREATE UNIQUE INDEX, например: 
    CREATE  UNIQUE   INDEX  INVOICES01   ON   INVOICES   (InvoiceNo) 

    Убывающие индексы 

    По умолчанию ключи индексов располагаются по возрастанию. В InterBase с 
помощью ключевого слова DESCENDING можно создать убывающие индексы. Вот пример: 
    CREATE   DESCENDING   INDEX   INVOICES03   ON   INVOICES    (Amount) 
    При этом запросы, подобные следующему, будут выполняться быстрее. 
    SELECT * FROM INVOICES ORDER BY Amount DESCENDING 

    Подключение и отключение индекса 

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

    ALTER INDEX INVOICES03 INACTIVE 

    Чтобы снова подключить индекс, просто замените ключевое слово INACTIVE на ACTIVE. 

    ALTER INDEX INVOICES03 ACTIVE 

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

    Генераторы и последовательности 

    Многие СУБД содержат средства автоинкремента значений столбцов. Столбцы с 
автоинкрементом — неотъемлемый атрибут реляционных баз данных, поэтому в 
основных СУБД обеспечивается их поддержка на системном уровне. 
    В СУБД Sybase и Microsoft SQL Server столбцы с автоинкрементом создаются 
путем указания атрибута identity. Столбцы с таким атрибутом подобны обычным 
столбцам, за тем исключением, что их значения автоматически генерируются 
системой. Вот пример определения столбца с автоинкрементом. 

    CREATE TABLE CUSTOMER 
    ( 
    CustomerNumber numeric(18) NOT NULL identity, 
    LastName char(30) NOT NULL, 
    FirstName char (30) NOT NULL, 
    StreetAddress char(30) NOT NULL, 
    City char(20) NOT NULL, 
    State char(2) Not NULL, 
    Zip char(10) NOT NULL 
    ) 

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

    CREATE  GENERATOR  CustomerNumbergen; 

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

    SET TERM !! ; 'CREATE TRIGGER CUSTOMERInsert FOR CUSTOMER 

    Глава 24. Дополнительные сведения об SQL	451 

    BEFORE INSERT POSITION 0 AS BEGIN 
    NEW.CustomerNumber=GEN_ID(CustomerNumberGEN, END SET TERM ; !! 

    Создание индексов (особенно кластерных) с использованием ключей с 
автоинкрементом может вызвать проблемы совместного доступа как в СУБД Sybase, 
так и в Microsoft SQL Server. Это связано с тем, что обе эти платформы 
придерживаются постраничной модели блокировки (page-based locking model). (В 
СУБД Microsoft SQL Server уже имеется режим Insert Row Locking, но эта 
возможность еще не до конца отработана.) Постраничная пессимистическая 
реализация доступа имеет свои преимущества, но один из ее основных недостатков 
состоит в том, то несвязанные транзакции могут блокировать друг друга, если 
они изменяют строки, расположенные на одной и той же странице. Другими 
словами, обновление адреса, находящегося в записи, у которой поле 
customerNumber имеет значение 1000, может блокировать обновление записи 
CustomerNumber=1001 просто потому, что обе эти строки находятся на одной и той 
же странице в базе данных. Если кластерный индекс создается на основе более 
случайного ключа, скажем, поля StreetAddress в нашем примере, то вероятность 
возникновения такой ситуации уменьшается. Заметим, что СУБД, реализующие 
архитектуру блокировки на уровне строк (например, Oracle), защищены от этой проблемы. 
    В СУБД Oracle подобный подход реализуется с использованием объектов базы 
данных, называемых последовательностями (sequence). Для создания 
последовательности в Oracle применяется следующий синтаксис. 

    CREATE SEQUENCE CustomerSEQ; 

    Как и в InterBase, вновь созданную последовательность можно использовать 
для хранения значений в коде Oracle PL/SQL. 

    Представления 

    Представления (view) состоят из скомпилированных операторов SELECT, с 
которыми можно работать, как с обычными таблицами. По существу, представления 
не содержат каких-либо данных — они состоят только из скомпилированного 
SQL-запроса. Представления похожи на хранимые процедуры, особенно на процедуры 
выбора. При запросе к представлению сервер объединяет команду SELECT, 
используемую для создания представления, с командой, которая используется для 
запроса, и выполняет комбинированный запрос к таблицам, лежащим в основе этого представления. 
    Для создания представлений используйте команду CREATE VIEW. Пример 
использования этой команды приводится в листинге 42.1. 

    Листинг 24.1. Пример использования команды CREATE VIEW 

    CREATE   VIEW   NEWLEASES   AS 
    SELECT   * 
    FROM  LEASE 
    WHERE  MovedlnDate   >   "12/01/96" 

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

    SELECT * FROM NEWLEASES 

    Оператор SELECT, образующий представление, может выполнять все те же 
действия, что и обычный оператор SELECT, за исключением применения ключевого 
слова ORDER BY. Это ограничение касается большинства СУБД клиент/сервер, 
включая InterBase, Oracle, Sybace и Microsoft SQL Server. 
    При создании модифицируемого представления налагаются дополнительные 
ограничения. Модифицируемое представление в СУБД InterBase должно 
удовлетворять следующим условиям. 
    О  Оператор SELECT должен ссылаться либо на одну таблицу, либо на другое 
модифицируемое представление. 

    452 IV. Темы повышенной сложности 

    О  Чтобы представление могло поддерживать команду INSERT, столбцы 
соответствующей таблицы, исключенные из представления, должны допускать 
значение NULL. 
    Q  Оператор SELECT не должен содержать подзапросов, предиката DISTINCT, 
ключевого слова HAVING, вычисляемых столбцов, объединенных таблиц, 
определенных пользователем функций и хранимых процедур. 
    Можно создать модифицируемое представление, для которого при вставке или 
модификации строк будет гарантироваться соответствие критериям выбора, 
наложенных представлением. Для этого в операторе CREATE VIEW используется 
ключевое выражение WITH CHECK OPTION со следующим синтаксисом/ 
    CREATE VIEW OKPROPERTY AS SELECT * FROM PROPERTY WHERE State="OK" WITH 
CHECK OPTION 
    Теперь любые попытки вставить или обновить запись, значение поля state 
которой не равно "ОК", будут игнорироваться. 
    Представления в Microsoft и Sybase SQL Server являются более гибкими в том 
смысле, что они могут поддерживать несколько таблиц и оставаться 
модифицируемыми. Но и для них существует много ограничений. Вот перечень 
ограничений, которые накладываются на представления СУБД SQL Server. 
    О   В операторе  SELECT,  формирующем представление,  не допускается 
использование  ключевых слов 

    ORDER   BY, COMPUTE И INTO   И оператора UNION. 

    О	Нельзя создавать представления на основе временных таблиц. 
    Q	Для представлений нельзя создавать триггеры и индексы. 
    Q	Для полей текста или изображений в представлении нельзя использовать 
процедуры Readtext и Writetext. 
    Для модифицируемых представлений существуют дополнительные ограничения. С1 
  Представления на основе нескольких таблиц не поддерживают команду DELETE. 
    Q  Столбцы лежащих в основе представления таблиц, не включенные в 
представление, должны допускать значение NULL, чтобы представление могло 
поддерживать команду INSERT. 
    Q  Представления, содержащие столбцы с вычислениями, не поддерживают 
команду INSERT. 
    О  Представления на основе нескольких таблиц, созданные с использованием 
ключевых слов DISTINCT и 
    WITH CHECK OPTION не поддерживают команду INSERT. 
    Q   Представления, созданные с использованием ключевого слова DISTINCT, не 
поддерживают команду UPDATE. 
    О  При модификации представления, основанного на нескольких таблицах, все 
обновляемые столбцы должны принадлежать одной таблице. 
    О  Столбцы с вычислениями в представлении не могут быть модифицированы. 
    Q  Представления, содержащие вычисляемые столбцы, не могут быть модифицированы. 

    Динамические представления 

    При обращении с запросом к представлению сервер находит в базе данных 
оператор SQL, создающий это представление, и выполняет его. Хотя данные, 
возвращаемые сервером, могут быть самыми разнообразными, оператор SELECT, 
выполняемый сервером, остается одним и тем же. 
    Динамическое представление (dynamic view) — это представление, 
использующее различные операторы SELECT в зависимости от условий во время 
вызова. Представление в листинге 24.1 отображает записи только по тем объектам 
недвижимости, которые находится в шт. Оклахома (ОК — код этого штата). В таком 
представлении критерий никогда не изменяется, даже если изменяются 
возвращаемые строки, — это статическое представление. В то же время, для 
динамического представления критерий, передаваемый серверу, изменяется в 
зависимости от внешних условий во время обращения к нему. Вот пример, 
написанный на диалекте Transact-SQL Sybase/Microsoft: 

    CREATE VIEW CONTACTLISTV SELECT * FROM CONTACTLISTV WHERE EnteredBy=suser name() 

    Глава 24. Дополнительные сведения об SQL	453 

    EnteredBy — это столбец таблицы CONTACTLIST, в который записывается имя 
пользователя, вводящего новую запись. Каждый раз при добавлении новой записи 
его значение автоматически задается с помощью ограничения DEFAULT. Когда 
пользователь приложения добавляет новую запись, в этом поле сохраняется 
соответствующее значение suser_name (). Благодаря этому, вы можете ограничить 
возвращаемый пользователю набор данных только теми записями таблицы 
CONTACTLIST, которые он ввел сам. Используемый сервером критерий поиска 
зависит от того, кто в данный момент является текущим пользователем приложения 
— таким образом это представление является динамическим. 

    Представления и права доступа 

    Чтобы получить доступ к представлению, пользователю не нужно иметь прав 
доступа к лежащим в его основе таблицам. Права доступа к представлениям и 
таблицам, лежащим в их основе, различны. Существует единственное исключение из 
этого правила: пользователи, создающие представления, должны иметь 
соответствующие права доступа к их таблицам. 
    Если пользователь имеет права доступа для выполнения некоторой операции 
над представлением, даже если у него нет таких же прав на лежащие в основе 
представления таблицы, он может получить доступ к этим таблицам через 
представление. Более того, в СУБД Sybase даже рекомендуется использовать 
представления таким образом в качестве одной из мер обеспечения безопасности. 
    Однако существует несколько причин, по которым не следует увлекаться таким 
подходом. Разместив часть прав доступа в представлениях, вы лишаете себя 
возможности централизованного управления зашитой и правами доступа. В этом 
случае для получения полной картины распределения прав доступа к системе вам 
придется работать, как минимум, с двумя средствами — традиционным 
предоставлением и лишением прав с помощью команд GRANT и REVOKE и 
представлениями таблиц. Более того, многие средства администрирования не 
настолько совершенны, чтобы сообщать, когда пользователь, не имеющий прав 
доступа к таблице, получает эти права через представление. Если вы хотите 
ограничить права доступа пользователя к таблице, я советую вам сделать это 
посредством таблиц, а не представлений. 
    Представления целесообразно использовать при подготовке данных к 
экспортированию. Большинство СУБД архитектуры клиент/сервер обладают теми или 
иными возможностями импортирования и экспортирования данных. В СУБД Oracle 
имеются команды EXPORT и IMPORT, а в Sybase и Microsoft SQL Server — утилита 
ВКР (Bulk Copy Program). При экспортировании данных SQL Server, содержащих 
даты, обычно возникает проблема, связанная с тем, что утилита ВСР пропускает 
часть полей даты и времени при экспортировании в формат ASCII. Чтобы устранить 
эту проблему, нужно создать представления для экспортируемых таблиц, 
отформатировав поля даты и времени таким образом, чтобы они содержали полную 
информацию о дате и времени. Затем эти представления с помощью утилиты ВСР 
можно экспортировать в ASCII-файлы без какой-либо потери информации. 

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

    Хранимые процедуры — это скомпилированные программы на языке SQL. Нередко 
они состоят из большого количества операторов SQL и хранятся в базе данных 
наряду с другими объектами. Существует два типа хранимых процедур: процедуры 
выбора и выполняемые процедуры. Процедуры выбора можно использовать вместо 
таблицы в операторе SELECT. Выполняемые процедуры, как ясно из их названия, 
предназначены для выполнения каких-либо операций. При этом они могут 
возвращать или не возвращать данные. 
    Хранимые процедуры создаются с помощью команды CREATE PROCEDURE. Вот 
пример синтаксиса этой команды в InterBase. 

    CREATE PROCEDURE CLEARCALLS 
    AS 
    BEGIN 
    DELETE   FROM  CALLS  WHERE   CallDateTime  <   '01/01/96'; END 

    Эта же процедура на диалекте Transact-SQL СУБД SQL Server выглядит 
следующим образом. 

    CREATE PROCEDURE CLEARCALLS AS 
    DELETE FROM CALLS WHERE CallDateTime < '01/01/96' 

    454 IV. Темы повышенной сложности 

    Если процедура получает параметры из вызывающей программы, то ее синтаксис 
слегка изменяется. Вот синтаксис InterBase: 

    CREATE  PROCEDURE  CLEARCALLS   (BeginningDate  DATE) 
    AS 
    BEGIN 
    DELETE FROM CALLS WHERE CallDateTime < :BeginningDate; END 

    Эта же процедура в СУБД SQL Server имеет следующий синтаксис. 

    CREATE PROCEDURE CLEARCALLS (@BeginningDate datetime) AS 
    DELETE FROM CALLS WHERE CallDateTime < @BeginningDate 


    Процедуры выбора 

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

    CREATE PROCEDURE LISTPROP (State char(2)) 
    RETURNS (PROPERTYNO INTEGER, 
    ADDRESS VARCHAR(30)) 
    AS 
    BEGIN 
    FOR SELECT PropertyNo, Address 
    FROM PROPERTY 
    WHERE State = :State 
    INTO :PropertyNo, :Address 
    DO 
    SUSPEND; END 

    Синтаксис FOR SELECT... DO используется для возвращения результатов в 
вызывающую программу. Команда SUSPEND приостанавливает выполнение процедуры до 
тех пор, пока вызывающая программа не затребует следующую строку. Выходные 
параметры возвращаются перед тем, как приостанавливается выполнение процедуры. 
    Исходный текст хранимых процедур целесообразно хранить в файлах сценариев 
SQL. Эти сценарии можно создать и отредактировать практически в любом 
текстовом редакторе. Не забывайте включать необходимые операторы CONNECT и SET 
TERM. Сценарии можно выполнить с использованием команды Run an ISQL Script 
меню утилиты WISQL. Пример сценария приводится в листинге 24.2. 

    Листинг 24.2. Хранимая процедура, помещенная в файл сценария SQL 

    CONNECT "C:\DATA\RENTMAN\RENTMAN.GDB" USER SYSDBA PASSWORD masterkey; 
    SET TERM A ; 
    /* Хранимая процедура */ 
    CREATE PROCEDURE LISTPROP 
    RETURNS (PROPERTYNO INTEGER, ADDRESS VARCHAR(30)) 
    AS 
    BEGIN 
    FOR SELECT PropertyNo, Address 
    FROM PROPERTY 
    INTO :PropertyNo, :Address 
    DO 
    SUSPEND; END 
    SET TERM ; 

    Оператор CONNECT в начале файла предназначен для подключения к базе 
данных. Команда SET TERM временно изменяет символ разграничения операторов 
SQL: используемый по умолчанию символ точки с запятой (;) меняет на символ 
крышки (л). Таким образом запрещается выполнение команд, определенных в 
хранимой процедуре, во время выполнения самой команды CREATE PROCEDURE. После 
выполнения команды CREATE PROCEDURE повторно вызванная команда SET TERM 
восстанавливает используемый по умолчанию символ разграничения команд SQL. 

    Глава 24. Дополнительные сведения об SQL	455 

    Выполняемые процедуры 

    Выполняемые процедуры немного отличаются от процедур выбора тем, что в них 
не нужно включать оператор RETURNS. Вот пример выполняемой процедуры СУБД InterBase: 

    CREATE   PROCEDURE   insertWORKTYPE   (WorkTypeCode   smallint, 
    Description char(30),   TaskDuration  float) 
    AS 
    BEGIN 
    INSERT   INTO  WORKTYPE  VALUES    (:WorkTypeCode,    .-Description,    
:TaskDuration); END 

    Эта же процедура диалекте Transact-SQL СУБД SQL Server будет выглядеть 
следующим образом. 

    CREATE   PROCEDURE   insertWORKTYPE   (@WorkTypeCode   smallint, 
    @Description  char(30),   @TaskDuration   float) 
    AS 
    INSERT INTO WORKTYPE VALUES (@WorkTypeCode, SDescription, @TaskDuration) 


    Хранимые функции 

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

    CREATE FUNCTION get_bal(CustomerNo IN NUMBER) 
    RETURN NUMBER 
    IS 
    custbal   NUMBER(11,2); BEGIN 
    SELECT  balance 
    INTO  custbal 
    FROM  CUSTOMER 
    WHERE   CustomerNo  =  get_bal.CustomerNo; RETURN(custbal); END 

    В СУБД SQL Server на процесс создания таких функций накладывается больше 
ограничений. Тем не менее, он по-прежнему напоминает традиционные методы 
вызова функций. Вот пример синтаксиса Sybase: 

    CREATE   PROCEDURE   get_bal   @CustomerNo   int,   SBalance  money=NULL  
OUTPUT AS 
    SELECT  SBalance  =  Balance 
    FROM   CUSTOMER 
    WHERE   CustomerNo  =   @CustomerNo RETURN  100  *   SBalance  /*  
Преобразование  к целому типу.   */ 

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

    declare @mybal money 
    select @mybal = null 
    exec @mybal = get_bal @CustomerNo=3 /* Содержит номер клиента, в данном 
случае 3. */ 
    select @mybal / 100 

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

    declare Smybal money select @mybal = null 

    456	IV. Темы повышенной сложности 

    exec  get_bal   @CustomerNo=3,   Smybal  OUTPUT select   Smybal 

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

    Пакеты 

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

    CREATE   OR  REPLACE   PACKAGE   CustomerPKG  AS 
    FUNCTION  addcust(CustomerName  VARCHAR2,   Address  VARCHAR2, City 
VARCHAR2,   State   CHAR(2),   Zip  CHAR(10)) RETURN  NUMBER; 
    PROCEDURE  deletecust(CustomerNumber NUMBER); Invalid_State  EXCEPTION; 
Invalid_City  EXCEPTION; END  CustomerPKG 

    Механизм создания пакетов Oracle предполагает также отделение заголовка 
пакета от его тела. Этот подход аналогичен выделению в модулях Delphi разделов 
interface и implementation. Оператор CREATE PACKAGE объявляет общедоступный 
интерфейс пакета. Общедоступный интерфейс — это информация, доступная внешним 
программам, которую они используют для ссылок на внутренние объекты пакета. 
Используйте оператор CREATE PACKAGE BODY для реализации самого пакета 
следующим образом. 

    CREATE   OR  REPLACE   PACKAGE  BODY   CustomerPKG  AS tot_custs  NUMBER; 
    FUNCTION  addcust(CustomerName  VARCHAR2,   Address  VARCHAR2, City  
VARCHAR2,    State  CHAR(2),   Zip  CHAR(10)) RETURN  NUMBER   is   
NewCustomerNo  NUMBER(4); BEGIN 
    IF  State   IS  NULL  THEN  RAISE   Invalid_State; 
    IF  City   is   NULL  THEN   RAISE   Invalid_City; 
    SELECT   CustomerSEQ.NEXTVAL 
    INTO NewCustomerNo 
    FROM   DUAL; 
    INSERT   INTO  CUSTOMER   (CustomerNo,   CustomerName,   Address,   City,  
 State,   Zip) 
    VALUES   (NewCustomerNo,   CustomerName,   Address,   City,   State,   Zip); 
    tot_cust   :=  tot_custs   +   1; RETURN(NewCustomerNo) ; END; 
    PROCEDURE  deletecust(CustomerNumber  NUMBER)    IS BEGIN 
    DELETE   FROM   CUSTOMER 
    WHERE  CUSTOMER.CustomerNumber  =  deletecust.CustomerNumber; 
    tot_custs   :=  tot__custs   -   1; END; END  CustomerPKG 

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

    Глава 24. Дополнительные сведения об SQL	457 

    Исключительные ситуации 

    Обработка исключительных ситуаций поддерживается СУБД InterBase, Oracle и 
Sybase. В InterBase для определения новой исключительной ситуации используется 
команда CREATE EXCEPTION, а для ее генерации — команда EXCEPTION. В Oracle для 
описания определенной пользователем исключительной ситуации используется 
синтаксис DECLARE. . .EXCEPTION и команда RAISE — для ее генерации. В Sybase 
исключительная ситуация не определяется как отдельный объект, однако в любом 
месте программы можно вызвать обработку исключительной ситуации, используя 
команду RAISERROR. Вы также можете создавать определенные пользователем 
сообщения, которые впоследствии могут быть использованы вместе с командой 
RAISERROR и связаны с событиями системных ошибок. Вот пример исключительной 
ситуации СУБД InterBase и хранимой процедуры, которая ее вызывает. 

    /* Исключительная ситуация. */ 
    CREATE EXCEPTION CREDIT_TOO_HIGH 
    "Заказанный кредит очень велик. Все ссуды казино должны быть меньше $5000."; 
    /* Сценарий SQL для процедуры, использующей исключительную ситуацию. */ 
    CONNECT "C:\DATA\IN\CASINO"; 
    SET TERM л; 
    CREATE PROCEDURE insertCREDIT (VoucherNumber smallint, VoucherDate date, 
    CustomerNumber int, Amount float) 
    AS 
    BEGIN 
    IF (:Amount > 5000) THEN 
    EXCEPTION CREDIT_TOO_HIGH; 
    ELSE 
    INSERT INTO CREDIT 
    VALUES (:VoucherNumber, :VoucherDate, :CustomerNumber, :Amount); END л 
    SET TERM ; л EXIT; 

    Приведенная процедура ограничивает кредит казино до $5000. Заметьте, что 
синтаксис IF. . .THEN используется для определения того, превышает ли 
содержимое поля Amount во вставляемой строке значение $5000. Команда EXCEPTION 
используется для вызова исключительной ситуации CREDIT_TOO_HIGH, когда 
значение Amount больше $5000. 
    Вот синтаксис СУБД SQL Server для выполнения той же функции (я написал его 
так, чтобы он мог быть сохранен в отдельном файле сценария, как было 
рекомендовано ранее). 

    sp_addmessage 20001, 
    "Заказанный кредит очень велик. Все ссуды казино должны быть меньше 
$5000." GO 
    use CASINO GO 
    CREATE   PROCEDURE   insertCREDIT   (@VoucherNumber   smallint,   
@VoucherDate   date, SCustomerNumber  int,   @Amount   float) AS 
    IF  @Amount   >   5000 
    RAISERROR   20001 ELSE 
    INSERT   INTO   CREDIT 
    VALUES   (@VoucherNumber,   @VoucherDate,   @CustomerNumber,   @Amount) GO 

    Обратите внимание на то, что СУБД SQL Server также поддерживает включение 
щаблрнрв в текст сообщения, подобно функции Delphi Format и функции С printf 
(). Таким образом, сообщение и предыдущая процедура могут быть сконструированы 
следующим образом. 

    st_addmessage 50010, 
    "Заказанный кредит %1 очень велик. Все ссуды казино должны быть меньше $5000." 
    GO 
    use ORDENT 
    GO 
    CREATE PROCEDURE insertCREDIT (@VoucherNumber smallint, @VoucherDate date, 
    @CustomerNumber int, @Amount float) 

    458            IV. Темы повышенной сложности 

    AS 
    IF  @Amount   >   5000 
    RAISERROE   50010,    @Amount ELSE 
    INSERT   INTO  CREDIT 
    VALUES   (@VoucherNumber,   §VoucherDate,   gCustomerNumber,   @Amount) 

    Обратите внимание на использование шаблона %1. В СУБД Sybase можно 
использовать до 20 пронумерованных параметров сообщения. Обратите также 
внимание на модифицированный вызов RAISERROR. Переменная @Amount передается 
как второй параметр команды. Несмотря на то, что строка сообщения является 
вторым (необязательным) параметром RAISERROR, она показывает, что передан 
номер определенного пользователем сообщения и использует оставшиеся аргументы 
как параметры сообщения. 
    СУБД Sybase SQL Server также поддерживает возможность связывания 
определенных пользователем сообщений с системными событиями, такими как 
нарушение ограничения. Например: 

    ALTER  TABLE   INVOICES ADD   CONSTRAINT   INVALID_CUSTOMER FOREIGN  KEY   
(CustomerNumber) REFERENCES   CUSTOMER(CustomerNumber) GO 
    exec   sp_addmessage   50010,    "Запись   с  указанным кодом клиента  не  
 обнаружена.   Укажите  правильный  код  клиента." GO 
    exec   sp_bindmsg   INVALID_CUSTOMER,50010 GO 
    Обратите внимание на использование имени ограничения в вызове sp_bindmsg. 
Оно устанавливает отношения между ограничением, созданным командой ALTER 
TABLE, и сообщением, созданным вызовом 
    sp addmessage. 


    Триггеры 

    Триггеры — это подпрограммы, написанные на языке SQL, которые выполняются, 
когда в таблице происходит определенное событие. Они настолько похожи на 
хранимые процедуры, что в СУБД InterBase поддерживаются специализированные 
расширения языка SQL, которые применяются и к хранимым процедурам и к 
триггерам. Триггеры связываются с какими-то определенными событиями таблицы: 
вставки, модификации или удаления. Вот пример триггера InterBase: 

    CREATE   TRIGGER   DELETEJDETAIL   FOR  WORDERS ACTIVE  BEFORE   DELETE AS 
  BEGIN 
    DELETE   FROM  WODETAIL 
    WHERE  WODETAIL.Work_Order_Number=OLD.Work_Order_Number; END 

    Этот триггер удаляет строки из подчиненной таблицы описания работ по 
наряду при удалении соответствующей записи из главной таблицы нарядов. Такой 
тип удаления известен как каскадное удаление (cascading delete) — удаление, 
сделанное в одной таблице, вызывает удаление в другой, использующей общий с 
первой таблицей ключ. 
    Обратите внимание на использование контекстной переменной OLD. Ее можно 
применять для ссылки на значение столбца перед выполнением операций UPDATE или 
DELETE. Контекстная переменная NEW ссылается на новое значение столбца в 
операциях INSERT или UPDATE. 
    Обратите также внимание на использование ключевого слова BEFORE. Триггеры 
могут активизироваться перед (before) или после (after) операций INSERT, 
UPDATE или DELETE. 
    В InterBase к каждому событию таблицы может быть привязано до 32768 
триггеров. Ключевое слово POSITION определяет порядок их выполнения. Триггеры 
с одинаковым значением POSITION выполняются в случайном порядке. Например: 
    CREATE TRIGGER GENERATE_WORKTYPECODE FOR WORKTYPE ACTIVE BEFORE INSERT 
-POSITION 0 '"AS BEGIN"'"" 
    NEW.Work_Type_Code  =  GEN_ID(Work_Type_CodeGEN,   1); END 

    Глава 24. Дополнительные сведения об SQL	459 

    СУБД Sybase и Microsoft SQL Server поддерживают несколько отличный 
синтаксис создания триггера. 

    CREATE   TRIGGER   INVOICESDelete   ON   CUSTOMER FOR   DELETE   AS 
    DELETE   FROM  INVOICES  WHERE   INVOICES.CustomerNumber-(SELECT  
CustomerNumber   FROM  deleted) 

    Обратите внимание на использование логической таблицы deleted. Это 
виртуальная таблица — она используется для ссылки на строку, которая удаляется 
из реальной таблицы, к которой привязан данный триггер. СУБД SQL Server также 
определяет таблицу updated, которая выполняет те же функции, что и таблица 
deleted, но для операций вставки и обновления. 
    В СУБД SQL Server реализован полезный механизм для определения того, какие 
столбцы будут обновляться в запросах. Для этого используйте синтаксис IF 
UPDATE (имя_столбца), заменяя имя_столбца на имя столбца в таблице. Можно 
ограничить действия триггеров, используя информацию о том, какие столбцы были 
изменены, а какие нет. 
    Существует два основных класса триггеров: триггеры операторов и триггеры 
строк. Триггеры операторов выполняются только для данного оператора DML, 
независимо от того, на сколько строк он действует. Такой тип триггеров 
поддерживает СУБД SQL Server. В отличие от этого, в Oracle разрешается 
определять как триггеры операторов, так и триггеры строк. Для этого 
используется ключевое выражение FOR EACH ROW оператора CREATE TRIGGER. Это в 
значительной степени влияет на способ разработки триггеров, поэтому вы должны 
удостовериться, что выбранная вами СУБД поддерживает данный тип триггеров. 

    Таблицы 

    Столбцы добавляются в таблицы и удаляются из них с использованием команды 
SQL ALTER TABLE. Серверы некоторых СУБД, таких как Microsoft SQL Server, не 
поддерживают удаление столбцов из существующих таблиц. InterBase и Sybase, с 
другой стороны, позволяют это сделать. Вот синтаксис для добавления столбца в таблицу: 

    ALTER TABLE CUSTOMER ADD PhoneNumber char(10) 

    А вот синтаксис для его удаления: 

    ALTER TABLE CUSTOMER DROP PhoneNumber 

    Обратите внимание, что нельзя добавить столбец со значениями NOT NULL в 
таблицу, уже содержащую несколько строк, поскольку при добавлении в таблицу 
столбцу автоматически присваивается значение NULL. 

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

    Синтаксис операторов DML 

    В следующем разделе описывается синтаксис языка управления данными (DML — 
Data Manipulation Language), реализованный в виде операторов SQL. Здесь 
исследуется синтаксис SQL, который используется в различных СУБД для настройки 
запросов, а также множество других вопросов, связанных с DML. В отличие от 
синтаксиса DDL, одним из важных моментов для операторов DML является 
производительность. Некорректное использование оператора SQL может привести к 
тому, что запрос будет выполняться медленнее, чем нужно. 

    SELECT 

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

    460 IV. Темы повышенной сложности 

    Многоуровневые объединения 

    Объединения, в которых используется более двух таблиц, называются 
многоуровневыми объединениями (multi-tier join). Примером многоуровневого 
объединения является объединение таблицы Table! с таблицей ТаЫе2, когда 
таблица ТаЫе2 объединена с таблицей ТаЫеЗ. Вот пример создания многоуровневого объединения: 

    SELECT  w.Work_Order_Number,   t.Name 
    FROM  WORDER  w,    LEASE   1,   TENANT   t 
    WHERE  w.Property_Number=l.Property_Number 
    and   1.Tenant__Number=t.Tenant_Number 

    В этом запросе таблицы WORDER и LEASE объединяются с использованием 
столбца Property_Number, a таблицы LEASE и TENANT — с использованием столбца 
Tenant_Number. В результате все три таблицы будут объединены в одно целое. 

    Самообъединения 

    Помимо объединения с другими таблицами, таблица может быть соединена сама 
с собой. Такой тип объединения называется рефлексивным (reflexive join) или 
самообъединением (self-join). Вот пример запроса с самообъединением: 

    SELECT   I.CustomerNumber,   I.Amount,    (I.Amount   /   
SUM(12.Amount))*100   Percentage FROM       INVOICES   I, 
    INVOICES   12, 
    WHERE   I.CustomerNumber=I2.CustomerNumber GROUP  BY  I.CustomerNumber,    I.Amount 

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

    Тета-объединения 

    Тета-объединения (theta join) связывают две таблицы с использованием 
символов операции неравенства (таких как о). Вот пример тета-объединения: 

    SELECT   С.CustomerNumber,    0.Amount,    Sum(02.Amount)    OTHERS 
    FROM  CUSTOMER  C, 
    INVOICES   0, 
    INVOICES   02 
    WHERE  C.CustomerNumber=0.CustomerNumber 
    AND  C.CustomerNumber<>02.CustomerNumber 
    GROUP  BY  C.CustomerNumber,   0.Amount 

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

    Декартово произведение 

    Декартово произведение (Cartesian join) используется для создания набора 
данных, содержащего все строки из двух различных таблиц. Вот пример: 

    SELECT t.Name, w.Description FROM TENANT t, WORKTYPE w 

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

    Глава 24. Дополнительные сведения об SQL	461 

    SELECT   INTO 

    СУБД Sybase и Microsoft SQL Server поддерживают разновидность команды 
SELECT, которая перенаправляет возвращаемый набор данных в другую таблицу. Это 
позволяет создать новую таблицу и заполнить ее данными за один шаг. Вот пример 
такого синтаксиса: 

    SELECT  w.Work_Order_Number,   t.Name 
    INTO   #MYWORKTABLE 
    FROM  WORDER  w,   LEASE   1,   TENANT   t 
    WHERE  w.Property_Number=l.Property_Number 
    and  1.Tenant_Number=t.Tenant_Number 

    Обратите внимание на то, что ключевое слово INTO следует сразу же за 
списком полей оператора SELECT. Именно он перенаправляет набор данных, 
возвращаемый командой SELECT, во вторую таблицу. В этом случае новая таблица 
является временной (что указывается использованием символа # в ее имени) — 
такие таблицы удаляются при отключении от сервера. Не путайте ключевое слово 
INTO СУБД SQL Server с его использованием в Oracle и InterBase. Ключевое слово 
INTO используется в СУБД Oracle и InterBase только для присвоения значений 
переменным, при этом таблица не создается. 

    На заметку 
    Эквивалентный оператор в СУБД Oracle имеет следующий вид. 

    CREATE TABLE MYWORKTABLE (Work_Order_Number, Name) 
    AS 
    SELECT  w.Work_Order_Number,   t.Name 
    FROM  WORDER  w,    LEASE   1,   TENANT   t 
    WHERE  w. Property_Number=l. Property_Number 
    and  l.Tenant_Number=t;Tenant_Nuraber 

    Это специальная версия команды CREATE TABLE, позволяющая заполнить таблицу 
данными при ее создании. Заметим, что в таблицах, которые создаются таким 
способом, нельзя задавать типы данных или размер столбцов. Сервер СУБД Oracle 
получает эту информацию прямо из вложенного запроса. 

    INSERT 

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

    INSERT INTO WORDERBACKUP SELECT * FROM WORDER 

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

    На заметку 
    СУБД Microsoft SQL Server поддерживает изящное расширение этой команды, 
позволяющее выполнять хранимую процедуру вместо вложенного запроса. Эта версия 
команды более мощная, чем стандартный синтаксис INSERT, и позволяет получать 
данные с удаленных серверов. Заметим, что в InterBase этот результат можно 
получить, выбрав строки для команды INSERT из хранимой процедуры SELECT. 

    DELETE 

    Наряду со стандартным синтаксисом команды DELETE, некоторые СУБД позволяют 
использовать внутри операторов DELETE объединения и вложенные запросы для их 
более точного определения. Например, вы можете удалить только тех клиентов, у 
которых не существует заказов. Если вы еще не применили к 

    462 IV. Темы повышенной сложности 

    базе данных механизма обеспечения ссылочной целостности, чтобы 
гарантировать автоматическое выполнение такой операции, можете воспользоваться 
объединением или вложенным запросом для определения, какие клиентов имеют 
заказы, и удалить только тех, кто их не имеет. 
    Если вы работаете с СУБД Sybase или Microsoft SQL Server и вам необходимо 
удалить все данные из таблицы, используйте команду TRUNCATE TABLE вместо 
многократного повторения команды DELETE, удаляющей строки таблицы. Этот метод 
является более быстрым и сводит к минимуму количество записей в журнале 
регистрации транзакций. 

    UPDATE 

    Некоторые СУБД позволяют команде UPDATE получать значения из других 
таблиц, выполняя объединение в процессе ее выполнения. Вот пример такого оператора: 

    UPDATE CUSTOMER 
    FROM CUSTOMER С, 
    ZIP Z 
    SET   C.City=Z.City,   С.State=Z.State 
    WHERE   C.ZipCode  =   Z.ZipCode 

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

    Курсоры 

    Курсоры (cusror) позволяют получить доступ к отдельным строкам в таблице. 
Существует четыре основных команды, связанных с курсорами: DECLARE, OPEN, 
FETCH и CLOSE. Для курсоров, допускающих модификацию данных, можно также 
использовать команды UPDATE и DELETE. 
    Курсоры определяются с использованием операторов SELECT. Если курсор 
допускает модификацию, то в этот оператор необходимо также включить список 
модифицируемых столбцов. Вот синтаксис: 

    DECLARE   c_PROPERTY  CURSOR FOR   SELECT   *   FROM   PROPERTY 

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

    OPEN c_PROPERTY 

    Заметим, что команда OPEN не возвращает строк — для этого используется 
команда FETCH. Вот ее синтаксис: 

    FETCH c__PROPERTY 

    При выполнении команды FETCH из таблицы извлекается одна строка. При 
каждом последующем обращении к команде FETCH извлекается новая строка. СУБД 
InterBase, Oracle и SQL Server поддерживают только однонаправленные курсоры — 
вы не можете передвигаться по набору данных курсора в обратном направлении. 
Чтобы вернуться назад, закройте курсор и вновь откройте его. 

    На заметку 
    Хотя большинство СУБД не поддерживают двунаправленные курсоры, тем не 
менее, вы можете их использовать в приложениях Delphi. Borland Database Engine 
эмулирует двунаправленный курсор в приложениях Delphi, независимо от того, 
поддерживается он СУБД или нет. в*™:; 
    Строки, возвращаемые модифицируемым курсором, можно модифицировать с 
использованием специальных версий команд UPDATE и DELETE. Модифицируемый 
курсор должен быть объявлен с помощью оператора FOR UPDATE OF. Вот пример: 

    DECLARE c_PROPERTY CURSOR FOR SELECT * FROM PROPERTY FOR UPDATE OF Address 

    Глава 24. Дополнительные сведения об SQL	463 

    В списке, определяемом с помощью ключевого выражения FOR UPDATE OF, 
используйте только те поля, которые вы действительно хотите модифицировать. 
Объявление неиспользуемых полей приводит к напрасной трате ресурсов сервера. 
    Для удаления или изменения текущей строки модифицируемого курсора 
используйте синтаксис WHERE CURRENT OF имя_курсора. Например: 

    UPDATE PROPERTY 
    SET Address="357 Riverside Avenue" 
    WHERE CURRENT OF c_PROPERTY 

    Для удаления строки используйте следующий синтаксис. 

    DELETE FROM PROPERTY 
    WHERE CURRENT OF c_PROPERTY 

    Используйте команду CLOSE, чтобы закрыть курсоры, с которыми вы закончили 
работу. Вот синтаксис: 

    CLOSE c_PROPERTY 

    В СУБД InterBase команда CLOSE также освобождает все системные ресурсы, 
связанные с курсором. В Sybase для этого используется команда DEALLOCATE 
CURSOR. Вот ее синтаксис: 

    DEALLOCATE   CURSOR   с   PROPERTY 


    Оптимальный SQL 

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

    Синтаксис SQL 

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

    Синтаксис ANSI 

    По возможности, используйте в исходном тексте операторов SQL синтаксис 
ANSI. Единственное исключение я делаю для объединений. Синтаксис СУБД Sybase и 
Microsoft SQL Server для объединений является более компактным и простым для 
чтения, чем многословный синтаксис ANSI. По крайней мере, отдавайте себе отчет 
в том, чем отличается используемый вами SQL и синтаксис ANSI. 
    Тем самым вы сможете помочь своим коллегам, которые будут ознакамливаться 
с вашей работой, поскольку этот синтаксис используется многими производителями 
СУБД. Кроме того, при этом вы расширите перечень книг и учебных пособий, 
которыми сможете воспользоваться, поскольку по ANSI SQL существует гораздо 
больше материалов, чем по отдельным диалектам разных производителей. И 
наконец, придерживаясь стандарта ANSI, вы будете лучше подготовлены к работе 
над кроссплатформными проектами. 

    464 IV. Темы повышенной сложности 

    Полные имена столбцов 

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

    Псевдонимы таблиц 

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

    Имена столбцов в операторах INSERT 

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

    GROUP   BY 

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

    WHERE 

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

    Избегайте декартова произведения 

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

    Используйте скобки 

    Чтобы разграничить сложные конструкции WHERE, состоящие из многочисленных 
операторов AND и OR, используйте скобки. Тем самым вы установите 
приоритетность элементарных операций и сделайте исходный текст операторов 
легче читаемым. 

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

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

    Комментарии 

    Следует избегать использования вложенных комментариев, поскольку их 
реализация отличается для различных платформ. Нужно также избегать избыточного 
комментирования. Существует подход, согласно которому все программы, 
написанные как на традиционных языках программирования так и на SQL, должны 
иметь подробные комментарии. Я не являюсь сторонником такого подхода по 
следующей причине. Мне кажется, что подробные комментарии в SQL не нужны, 
поскольку сам по себе SQL похож на английский язык. Он изначально 
разрабатывался компанией IBM как структурированный английский язык запросов 
(Structured English Query Language), откуда и произошла аббревиатура SQL. 
Поэтому правильно написанный код SQL зачастую документирует сам себя. 

    Глава 24. Дополнительные сведения об SQL	465 

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

    BEGIN...END 

    Используйте синтаксис BEGIN . . . END даже в тех ситуациях, когда делать 
это необязательно. Например, в диалекте Transact-SQL СУБД Sybase и Microsoft 
SQL Server после оператора IF можно опустить BEGIN. . .END, если при условии 
IF выполняется лишь один оператор SQL. Используйте BEGIN.. .END во всех 
операторах IF и во всех циклах, даже если такой синтаксис является 
необязательным. При этом исходный текста станет более читабельным и позволит 
при необходимости легко преобразовать условные операторы, состоящие из одной 
строки, в многострочные условные операторы. 

    Производительность 

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

    Индексы 

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

    Кластерные и некластерные индексы 

    СУБД Sybase и Microsoft SQL Server поддерживают расширение базового 
синтаксиса CREATE INDEX, позволяющее создавать кластерные индексы (clustered 
index). Некластерные индексы (nonclustered index) — это индексы в обычном 
смысле на самом низком уровне — они хранят ключевое значение со ссылкой на 
физическую таблицу, по которому можно получить данные из этой таблицы. 
Кластерные индексы хранят настоящую запись данных на самом низком уровне 
дерева индексов. Другими словами, вместо простого хранения ключевого значения 
на самом низком уровне дерева кластерных индексов, сервер СУБД SQL Server 
хранит там целую строку. По определению в таблице может быть только один 
кластерный индекс. 
    В кластерном индексе сами данные отсортированы в соответствии с 
определенным ключом, что обеспечивает намного большую производительность по 
сравнению с обычными индексами, особенно для запросов, возвращающих диапазоны 
последовательных строк. Нет необходимости возвращаться к физической таблице 
для поиска ключа, размещенного в индексе. Кластерный индекс и таблица — это 
одно и то же: нет отдельной таблицы, в которой нужно что-то искать. 
    Для создания кластерных индексов в СУБД SQL Server используется следующий синтаксис. 

    CREATE  CLUSTERED  INDEX  INVOICES01  ON   INVOICES   (OrderNumber) К 
сожалению, в СУБД InterBase нет аналогичной возможности. 

    На заметку 
    Не путайте кластерные индексы СУБД Sybase и Microsoft SQL Server с 
кластерами СУБД Oracle. Хотя эти термины похожи, но они имеют различный смысл. 
В Oracle 8 анонсирована возможность работы с чисто индексными таблицами "Index 
Only Tables" — индексами, которые хранят данные на самом низком уровне. Такие 
индексы представляют собой функциональный эквивалент кластерных индексов SQL Server. 
    Если выбранная вами СУБД поддерживает кластерные индексы, то используйте 
их вместо некластерных индексов для хранения главного ключа таблицы. 

    466	IV. Темы повышенной сложности 

    Охватывающие индексы 

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

    CREATE   INDEX  ORDER04   ON  ORDERS   (OrderDate,   Amount) Теперь запрос 
    SELECT  OrderDate,   SUM (АгаоицЦ.   TotalAmount 
    FROM  ORDERS 
    GROUP BY OrderDate 

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

    Производительность команды SELECT 

    Следующие несколько советов относятся к команде SQL SELECT. Они 
обеспечивают простые способы улучшения производительности операторов SELECT 
без внесения радикальных изменений в исходный текст операторов. 

    Использование функции EXISTS 

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

    IF (SELECT COUNT(*) FROM TABLENAME WHERE condition) > 0 

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

    IF EXISTS    (SELECT   *   FROM  TABLENAME  WHERE   condition) 

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

    SELECT DISTINCT 

    Использование команды SELECT DISTINCT почти всегда является 
необязательным. Чтобы понять, почему это так, давайте сначала разберемся, 
какую функцию выполняет ключевое слово DISTINCT. Ключевое слово DISTINCT 
предназначено для исключения одинаковых строк из набора данных, возвращаемого 
командой SELECT. Возникает вопрос: "Почему возвращаемый набор данных содержит 
дубликаты? Является ли это следствием неэффективного запроса?". Если это так, 
то для решения этой проблемы нужно изменить запрос. Чаще команда SELECT 
DISTINCT используется для того, чтобы скрыть результаты декартова 
произведения, от которых программист не может избавиться сам. Команду SELECT 
DISTINCT следует использовать только тогда, когда она обеспечивает 
единственный способ получения нужного результата, а не самый легкий. 

    WHERE 

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

    Ограничение набора данных 

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

    Глава 24. Дополнительные сведения об SQL	467 

    чала ограничивали количество обрабатываемых строк. Если это в принципе 
возможно, то результата можно достичь с помощью индексов. Если вам постоянно 
приходится ограничивать набор данных посредством ключевого слова WHERE, для 
которого нет соответствующего индекса, то, возможно, самое время его добавить. 
    Заметим, что упорядочивание ключевых слов FROM и WHERE не обязательно 
гарантирует некий конкретный порядок доступа к таблице. В СУБД, имеющих 
оптимизаторы запросов (а это характерно практически для всех основных СУБД), 
оптимизатор оставляет за собой право перестроить запрос для достижения 
оптимальной производительности. От этого можно отказаться (например, в 
Microsoft SQL Server с помощью команды SET FORCE PLAN ON), но нужно стараться 
давать возможность оптимизатору делать свое дело. 

    Ключевые поля в функциях и выражениях 

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

    SELECT * FROM ORDERS 
    WHERE CAST (OrderDate AS CHAR(8) 
    >=   '07/01/95' 

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

    Использование ключевого слова LIKE 

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

    SELECT * FROM CUSTOMER 
    WHERE LastName LIKE "%Joh%on" 

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

    SELECT * FROM CUSTOMER WHERE LastName LIKE "Joh%on" 

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

    Вложенные запросы 

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

    SELECT UseDate,   SUM(Amount)   AmountSpent 
    FROM  RECEIPTS 
    WHERE  CardType  in   (SELECT  CardType   FROM CARDTYPE  WHERE  Type='I') 
    GROUP  BY  UseDate 

    468 IV. Темы повышенной сложности 

    Чтобы ускорить выполнение этого запроса, можно жестко запрограммировать 
список, возвращаемый вложенным запросом, если он изменяется редко. При этом 
остается открытой возможность того, что пользователь может добавить запись в 
таблицу CARDTYPE, которая не будет включена в созданный вами отчет. Оцените 
сами, стоит ли рассматривать возможность использования такого подхода. 
    Другой подход состоит в том, чтобы создать запрос в Delphi динамически. 
Для построения динамического запроса нужно выполнить следующие действия. 
    1.    Щелкните на объекте класса TQuery, в свойстве SQL которого 
содержится запрос к серверу о типах карточек, выданных физическим лицам. 
    2.   Создайте цикл, перебирающий все строки возвращаемого набора данных и 
создающий строку, содержащую список всех типов карточек, отделяя его элементы 
друг от друга запятыми. Эта строка должна выглядеть примерно так: 
    4,2,4,12,13,20' 
    3.    Передайте эту строку динамическому запросу SQL для использования в 
его операторе IN. Этот запрос должен выглядеть примерно так: 

    SELECT  UseDate,   SUM(Amount)   AmountSpent FROM  RECEIPTS 
    WHERE  CardType   in   (:CardTypeList) GROUP  BY  UseDate 

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

    SELECT   r.UseDate,   SUM(r.Amount)   AmountSpent FROM RECEIPTS   r,   
CARDTYPE   с WHERE   r.CardType  =  c.CardType and  c.Type='I' GROUP  BY  UseDate 

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

    ORDER  BY 

    Следует избегать использования ключевого слова ORDER BY. Этот 
неэффективный механизм может привести к тому, что сервер будет без 
необходимости сортировать большие наборы данных, возвращаемые запросами. Если 
вы не уверены в том, что вам действительно необходимо упорядочить данные с 
помощью этого ключевого слова, не используйте его. Заметим, что кластерные 
индексы СУБД Sybase и Microsoft SQL Server автоматически упорядочивают строки 
возвращаемых наборов данных. Кроме того, на некоторых платформах данные 
упорядочиваются оператором GROUP BY. Хотя язык SQL не гарантирует никакого 
естественного порядка строк, некоторые СУБД обеспечивают упорядочивание строк 
теми или другими средствами. Более подробную информацию вы найдете в 
документации используемого вами сервера. 

    HAVING 

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

    SELECT  CUSTOMER.LastName,   COUNT(*)   NumberWithName 
    FROM  CUSTOMER 
    GROUP  BY  CUSTOMER.LastName 
    HAVING  CUSTOMER.LastNameO'Citizen' 

    Этот же запрос лучше переписать с использованием ключевого слова WHERE. 

    SELECT CUSTOMER.LastName, COUNT(*) NumberWithName 
    FROM CUSTOMER 
    WHERE CUSTOMER. LastNameO ' Citizen' 
    GROUP BY CUSTOMER.LastName 

    Глава 24. Дополнительные сведения об SQL	469 

    Такой подход лучше, поскольку данные не подвергаются ненужной группировке. 
При использовании HAVING большинство серверов сначала группируют данные, а 
затем фильтруют их согласно критерию, определенному с помощью ключевого слова 
HAVING. Это менее эффективно, чем просто игнорировать ненужные данные. 
    Использование HAVING оправдано только для определения набора данных, 
возвращаемого по вычисляемым столбцам. Поскольку содержимое этих столбцов не 
известно до тех пор, пока сервер не создаст возвращаемый набор данных, то для 
них нельзя использовать оператор SELECT с ключевым словом WHERE. Если вы 
хотите определить набор данных такого типа, то вам придется воспользоваться 
ключевым словом HAVING. Вот пример: 

    SELECT   CUSTOMER.LastName,   COUNT(*)   NumberWithName 
    FROM  CUSTOMER 
    WHERE   CUSTOMER. LastNameo' Citizen' 
    GROUP BY  CUSTOMER.LastName 
    HAVING  COUNT(*)    >   2 

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

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

    COMPUTE 

    Диалект Transact-SQL СУБД Sybase и Microsoft SQL Server поддерживает 
способ вычисления значений столбцов, возвращаемых оператором SELECT, без 
использования вычисляемых столбцов в самом операторе SELECT. Это делается с 
помощью ключевого слова COMPUTE. Оно следует за остальными элементами 
оператора SELECT и просто выполняет те или иные операции над столбцами 
возвращаемого набора данных с помощью обычных функций, например, зим (). 
    SELECT CustomerNumber, Amount FROM ORDERS COMPUTE SUM(Amount) 
    Эту возможность не стоит использовать по нескольким причинам. Во-первых, 
большинство внешних средств не знают, как правильно обрабатывать ключевое 
слово COMPUTE как часть возвращаемого набора данных. Во-вторых, COMPUTE имеет 
некоторые несущественные ограничения, которые делают его менее полезным, чем 
можно было бы ожидать. В-третьих, что наиболее важно, такие запросы можно 
построить без использования ключевого слова COMPUTE, например: 

    0.Amount,   SUM(02.Amount)   TotalOrders 
    SELECT   0.CustomerNumber, FROM	ORDERS   0, ORDERS   02 GROUP  BY   0.CustomerNumber, 
    0.Amount 

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

    GROUP   BY 

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

    SELECT  OrderDate,   CustomerNumber,   SUM(Amount)   TotalOrders 
    FROM  ORDERS 
    GROUP BY OrderDate, CustomerNumber 

    470 IV. Темы повышенной сложности 

    и 

    SELECT OrderDate, CustomerNumber, SUM(Amount) TotalOrders 
    FROM ORDERS 
    GROUP BY CustomerNumber,   OrderDate 

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

    CREATE   INDEX  ORDERS03   ON  ORDERS   (CustomerNumber,   OrderDate) 

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

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

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

    Осторожнее с параметрами 

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

    SET NOCOUNT ON в СУБД SQL Server 

    По умолчанию СУБД Sybase и Microsoft SQL Server передают количество строк, 
обработанных каждым оператором в хранимой процедуре, приложению, выполнявшему 
эту хранимую процедуру. Обычно кли-ентное приложение не использует эти данные. 
Вы можете повысить производительность хранимых процедур в SQL Server за счет 
использования команды SET NOCOUNT ON. Единственный побочный эффект применения 
команды SET NOCOUNT состоит в том, что команда READTEXT, при использовании с 
функцией DB-Library dbreadtext (), может работать некорректно. Поскольку такая 
ситуация встречается чрезвычайно редко, то команду SET NOCOUNT ON можно 
считать безопасной и включать в исходный текст хранимых процедур по умолчанию. 

    Инициализация локальных переменных 

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

    SELECT City, State, Zip INTO :City, :State, :Zip FROM CUSTOMER 

    А вот для тот же пример, но на Transact-SQL СУБД SQL Server: 

    SELECT   @City=City,    @State=State,    @Zip=Zip FROM  CUSTOMER 

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

    Глава 24. Дополнительные сведения об SQL	471 

    Поиск второстепенной информации 

    Вспомогательную информацию в хранимых процедурах ищите в последнюю 
очередь. Идея состоит в том, чтобы сначала уменьшить набор данных, а затем 
использовать его для поиска второстепенной информации. 
    Например, если вы суммируете данные в таблице кредитных карточек, 
содержащей несколько миллионов строк, не ищите имя владельца каждой карточки в 
процессе суммирования. Займитесь его поиском лишь тогда, когда вы максимально 
уменьшили возвращаемый набор данных и готовы вернуть эти строки в клиентное 
приложение. Самый эффективный способ для этого состоит в том, чтобы выполнить 
запрос за несколько проходов и сохранить полученный набор данных во временных 
таблицах при каждом успешном проходе. После полного определения данных 
объедините их с таблицей CUSTOMER, чтобы вернуть дополнительную информацию о 
потребителях в клиентное приложение. 
    Хотя на первый взгляд совершение нескольких проходов по большому набору 
данных кажется нерациональным, не забывайте, что при необходимости сервер сам 
делает несколько проходов. Поэтому вы таким образом лишь выступаете в роли 
оптимизатора своего же запроса. Лучшее, что вы можете сделать для оптимизации 
запроса хранимой процедуры, — как можно быстрее уменьшить набор данных, с 
которыми она работает. Для этого иногда требуется выполнение нескольких 
проходов и использование временных таблиц. 

    Многотабличные запросы 

    Бывают ситуации, когда стандартного метода группировки данных с помощью 
оператора SELECT оказывается недостаточным. В частности, в SQL иногда 
достаточно сложно реализовать многотабличные запросы. Многотабличный запрос 
организует данные в строки и столбцы, подобно электронным таблицам. Если ваши 
данные организованы линейно, как большинство данных, то может оказаться 
непросто представить их в виде сетки. 
    Лучше всего объяснить это на примере. Допустим, вы пишете запрос, 
возвращающий информацию о продажах для трех крупнейших производителей 
автомобилей в Америке: Ford, General Motors и Chrysler. Клиент хочет, чтобы 
тип автомобиля (сверхкомпактный, компактный, полноразмерный, грузовик и т.д.) 
указывался в левой части отчета, а в правой части располагались столбцы с 
данными продаж по каждому из производителей. Обычно, чтобы получить 
необходимую информацию, нужно написать оператор SQL, подобный следующему: 

    SELECT  CarType,   Maker,   Sales  TotalSales FROM  BIGTHREESALES 

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

    CREATE   TABLE   CARCROSS 
    (	CarType                                                   CHAR(10)       
       NULL, FordSales                           FLOAT    NULL, GMSales        
                                        FLOAT    NULL, ChryslerSalesFLOAT    NULL) 

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

    INSERT  INTO  CARCROSS   (CarType,   FordSales) Select  CarType,   Sales   
FordSales FROM  BIGTHREESALES WHERE Maker='Ford' 

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

    SELECT Sales INTO :GMSales FROM BIGTHREESALES WHERE Maker='GM' AND CarType='SC' 

    472 IV, Темы повышенной сложности 

    UPDATE CARCROSS 
    SET GMSales:=GMSales 
    WHERE CarType='SC' 

    В качестве альтернативы можно использовать следующий синтаксис SQL Server: 

    UPDATE CARCROSS 
    SET GMSales=Sales 
    FROM SALES S, CARCROSS С 
    WHERE Maker='GM' 
    AND S.CarType=C.CarType 

    Существует, однако, и лучший способ. Он основывается на использовании 
одного из главных достоинств SQL — возможности легко группировать и 
суммировать данные. Это позволяет представить данные в многотабличном формате 
ценой минимальных усилий посредством свертывания (folding), или упрощения 
(flattening), запроса. Вот пример предыдущего запроса с использованием метода выравнивания. 
    Сначала соберем и поместим в соответствующий столбец информацию о компании Ford. 

    INSERT   INTO  CARCROSS    (CarType,   FordSales) Select  CarType,   Sales 
  FordSales FROM  BIGTHREESALES WHERE  Maker='Ford' 

    Затем сделаем то же самое для данных о компании General Motors. 

    INSERT   INTO  CARCROSS   (CarType,   GMSales) Select  CarType,   Sales  
GMSales FROM  BIGTHREESALES WHERE  Maker='GM' 

    И, наконец, для Chrysler. 

    INSERT   INTO  CARCROSS    (CarType,    ChryslerSales) Select   CarType,   
Sales   ChryslerSales FROM  BIGTHREESALES WHERE Maker='Chrysler' 

    Теперь строки в таблице CARCROSS будут выглядеть примерно так, как 
показано на рис. 24.1. 

    SELECT	» FROM CARCROSS			__J 
    
    CarType	FordSales	GMSales	ChryslerSales	! 
    SC	1i45i(it32.e	NULL	NULL	;'М 
    CO	1145632.8	NULL	NULL 
    FS	3U3U662.0	HULL	NULL	" 
    TR	2356232. В	HULL	NULL 
    SC	MULL	233*li5. В	NULL	i't 
    CO	NULL	1225U3.B	NULL 
    FS	HULL	3l«lt56SU.O	NULL	',': 
    TR	HULL	22333*5. в	NULL 
    SC	HULL	NULL	3200322.8 
    CO	HULL	NULL	2388622.8 
    FS	HULL	NULL	U335622.8 
    TR	NULL	NULL	5543321. в	':•!: 

    Рис. 24.1. Таблица CARCROSS до свертывания 

    Глава 24. Дополнительные сведения об SQL 473 

    Таблицу CARCROSS можно свернуть с помощью следующего запроса. 

    SELECT  CarType,   SUM(FordSales)    FordSales,   SUM(GMSales) GMSales,   
SUM(ChryslerSales)   ChryslerSales FROM  CARCROSS GROUP  BY  CarType 

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

    S3 WISQL32- (Untilled) on KHtN/puLis2: 
    Ffe   ?<й   Еотм»   Зиму   Цф 
    SELECT CarType, SUM(FordSales) FordSales, SUM(GMSales) GMSales, 
SUM(ChrjislerSales) ChryslerSales j* FROM CARCROSS	_ GROUP BV CarType 
    Ж   ще  _>р 
    CarType         FordSales 
    GMSales 
    ChryslerSales 
    1454432.8 145632.0 3431(662.0 2356232.0 
    2331)1(5.8 122543.0 3445654.0 2233345.0 
    3200322.0 
    2300622.0 
    4335622.0 
    5543321.0 

    Рис. 24.2. Набор данных, возвращаемый в результате выполнения операции свертывания 

    Как вы видите, мы получили требуемый формат отчета без каких-либо 
упомянутых ранее неуклюжих приемов. 

    Архивы отчетной информации и SQL 

    Одна из классических проблем, с которой сталкиваются разработчики архивов 
отчетной информации (data warehouse) связана с поиском наилучшего способа 
хранения и получения итоговых данных. В архивах отчетной информации обычно 
хранится большое количество итоговых данных. Как правило, пользователь хочет 
иметь доступ не только к исходным данным таблиц, но и к итоговым данным. В 
связи с этим возникает несколько интересных проблем, связанных с архитектурой 
архивов отчетной информации. Первая проблема — это поиск соответствующего 
места для хранения итоговых данных. Если они хранятся в той же базе данных, 
что и первичные данные, нужно ли их перемещать в специальные итоговые таблицы? 
Возможно, их нужно поместить в специальную итоговую базу данных. Тогда как 
получить доступ к итоговым данным? Не удвоится ли при этом работа по 
администрированию данных? И какие из хранимых процедур и отчетов, 
разработанных для первичных данных, необходимо изменить для работы также и с 
итоговыми данными? 
    Один из возможных ответов на эти вопросы, позволяющий оптимизировать 
операции ведения архивов отчетной информации, требует некоторого увеличения 
объема дискового пространства, выделяемого для базы данных. Он заключается в 
том, что я называю встроенным суммированием (in-line summarization). Это 
означает такую организацию первичных таблиц, чтобы итоговые строки можно было 
разместить в той же таблице и опрашивать с помощью тех же запросов, что и 
строки первичных данных. При этом отпадает необходимость создания итоговых 
таблиц или баз данных, а также отдельных хранимых процедур и отчетов для 
работы с итоговыми данными. 

    474 IV. Темы повышенной сложности 

    Лучше всего объяснить это на примере. Допустим, у вас есть база данных, в 
которой хранятся сведения о миллионах операций с кредитными карточками. 
Ежемесячно вы хотите суммировать операции по дню, номеру кредитной карточки и 
месту проведения операции. Вам необходимо вычислить, как общее количество 
операций, так и общую сумму денег, задействованных в этих операциях. Другими 
словами, если Джон До (John Doe) совершил три покупки в магазине Foley 1 
января, то вы хотите получить только одну запись. В ней должны содержаться 
номер кредитной карточки Джона, дата, код магазина Foley, число операций и 
общая стоимость покупок. 
    Вместо того, чтобы хранить итоговую информацию в отдельных таблицах, вы 
можете хранить ее в исходной таблице, внеся в ее проект лишь несколько 
изменений. Например, допустим исходная компоновка таблицы выглядела следующим образом. 

    CREATE   TABLE   CARDTRANS 
    (CardNumber	char(20) NOT NULL, TransactionDate date NOT NULL, Location int 
NOT NULL, Amount float NOT NULL) 

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

    SELECT  CardNumber,   TransactionDate,   Location, -ffiyjCLLII. 
NumberTrans,   SUM(Amount)   Amount 
    FROM'CARDTRANS 
    WHERE  TransactionDate  between   '01/01/95'   AND   '02/01/95' GROUP  BY  
CardNumber,   TransactionDate,   Location 

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

    CREATE  TABLE   CARDTRANS 
    (CardNumber	char (20) NOT NULL, TransactionDate date NOT NULL, Location 
int NOT NULL, NumberTrans int NOT NULL, Amount float NOT NULL) 

    Обратите внимание на добавление столбца NumberTrans. Что хранится в этом 
поле в строках первичных данных? В нем хранится та же информация, что и в 
итоговой строке — количество операций для данной строки в таблице. Для строк 
первичной информации значение NumberTrans всегда равно 1. Вы можете даже 
определить для этого ограничение DEFAULT. Для итоговых строк поле NumberTrans 
должно содержать количество операций, определенное ключевыми полями этой 
строки. В примере с Джоном До это значение равно 3, но на этом месте может 
стоять любое число, которое можно задать с помощью типа данных int. Вот 
рассмотренный выше запрос, переделанный таким образом, чтобы можно было 
обрабатывать как первичную информацию, так и итоговую. 

    SELECT   CardNumber,   TransactionDate,   Location,   SUM(NumberTrans)   
NumberTrans,   SUM(Amount) 
    Amount 
    FROM  CARDTRANS 
    WHERE  TransactionDate  between   '01/01/95'   AND   '02/01/95' 
    GROUP  BY  CardNumber,   TransactionDate,   Location 

    Обратите внимание, что единственное изменение состоит в переходе от 
использования функции COUNT (*) для определения числа операций к использованию 
функции SUM (NumberTrans). Такой подход работает как для строк с итоговой, так 
и для строк с первичной информацией, благодаря включению в таблицу столбца NumberTrans. 
    ~"Т5б"ычно в процессе суммирования данные для одного месяца включаются во 
временную таблицу с использованием запроса, аналогичного предыдущему. Затем 
исходные первичные данные удаляются из таблицы CARDTRANS. И наконец, вместо 
них в таблицу вставляются итоговые данные. Все эти действия выполняются на 
сервере в процессе" одной транзакции, чтобы предотвратить случайное удаление данных. 
    Заметим, что для хранения итоговых данных вам, возможно придется увеличить 
размер некоторых полей, таких как Amount. Например, если вы определили для 
поля Amount тип данных Sybase smallmoney, то его лучше переопределить с 
использованием типа money. 

    Глава 24. Дополнительные сведения об SQL	475 

    На заметку 
    На некоторых платформах в процессе суммирования можно также отделить 
информации о времени от данных столбца TransactionDate. Некоторые СУБД (такие 
как Sybase и Microsoft SQL Server) хранят информацию о дате и времени в данных 
одного типа. Это означает, что в строках первичной информации поле 
TransactionDate может содержать и дату операции, и время. Поскольку нам 
требуется итоговая информация за день, то информацию о времени можно удалить. 
Для удаления информации о времени в СУБД SQL Server используется следующий оператор. 

    SELECT  CardNumber,   CONVERT(char(8) ,   TransactionDate, 
    Location,   SUM(NumberTrans)   NumberTrans, FROM   CARDTRANS 
    112) , SUM(Amount)   Amount 
    WHERE  TransactionDate  between   49950101'   AND   '19950201' 
    GROUP  BY  CardNumber,   CONVERT(char(8),   TransactionDate,    112),   Location 

    Обратите внимание на использование функции CONVERT для перевода данных 
столбца TransactionDate в тип CHAR (8). При необходимости SQL Server может 
автоматически конвертировать тип CHAR в DATETIME. Третий параметр функции 
CONVERT, 112, преобразует дату в формат CCCCMMDD. При хранении даты с 
использованием типа CHAR (8) информация о времени отсекается. 
    Этот метод позволяет избежать головной боли, связанной с суммированием 
первичных данных в отдельных итоговых таблицах или базах данных. Он требует 
небольших дополнительных затрат дискового пространства для каждой записи, что 
обеспечивает его применимость для обработки как первичной, так и итоговой информации. 

    Резюме 

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

    Что дальше... 

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

    476 IV. Темы повышенной сложности 

