Книги, научные публикации
Pages: | 1 | 2 | 3 | 4 | ... | 5 | С. Н. Смирнов, И. С. Задворьев Учебное пособие 2-е издание исправленное и дополненное Москва 2002 УДК 681.3.06 32.973.2 57 Смирнов С. И. С. ...
-- [ Страница 2 ] --Оператор определения таблиц Oracle содержит довольно большое число ключевых слов и параметров. Рассмотрим со кращенное множество конструкций. Полный синтаксис опе ратора CREATE TABLE может быть получен из соответст вующего раздела документации SQL Reference.
CREATE TABLE та блицы \ [ DEFAULT выражение]... ] }, [{ CLUSTER (, | целое | PCTUSED целое | INITRANS целое | целое TABLESPACE \ STORAGE \ | ] \ DISABLE [AS запрос] | NOCACHE] Ключевое слово DEFAULT указывает на то, что при вво де данных соответствующему столбцу будет присвоено зна чение, определенное переменной выражение, если в операто ре INSERT не указано явно другое значение столбца.
выражение должен соответствовать типу данных столбца и выражение не должно содержать ссылок на другие Ключевые слова PCTUSED, TABLESPACE, STORAGE, RECOVERABLE, UNRECOVERABLE характеризуют пространство, распреде ляемое при работе с таблицей.
Когда таблица создается, то система автоматически зервирует место как для данных, так и для индексов. Это ме сто делится на две части, называемые сегментами: сегмент данных и сегмент индексов. Сегменты состоят из блоков. За писи заносятся в в физической последовательности.
Первая запись хранится в первом блоке первого экстента.
Следующие за ней записи заносятся в первый блок до тех пор, пока он не заполнится. Блок считается заполненным, ко гда очередной записи не хватает оставшегося в блоке. Когда по мере занесения записей за полняются все блоки первого экстента, выделенного таблице, для данных выделяется первый дополнительный экстент. Ко гда будет заполнен и этот экстент, сервер выделит следую щий.
После выделения таблице экстента данных индекса он не освобождается до тех пор, пока таблица не будет унич тожена. Однако если все данные удалены блока данных, этот блок становится доступным для повторного использова ния этой же таблицей или кластером.
Ключевое слово PCTFREE определяет процент простран ства который резервируется для нужд модификации данных таблицы. Допустимые значения от 0 до 99. Значение по умолчанию 10. То есть, если данный параметр не указан, то при заполнении каждого блока пространства остается не использованным. Это пространство используется для запи си в него данных при выполнении в дальнейшем операций модификации строк таблицы.
Ключевое слово PCTUSED определяет минимальный процент использования пространства блока, при котором в SQL Ч язык обработки данных Oracle него вводятся данные. Допустимые значения от 1 до 99. Зна чение по умолчанию 40. То есть, если в блоке менее 40% пространства в него вводятся данные при выполнении операции вставки. Сумма значений параметров PCTFREE и PCTUSED не должна превышать Ключевое слово определяет начальное число параллельных транзакций, могут выполняться для модификации данных блока. Значение по умолчанию Клю чевое слово определяет максимальное число параллельных транзакций, могут выполняться для модификации данных блока. В большинстве случаев явное задание этих параметров не Ключевое слово определяет имя таблич области, в которой будет размещена таблица. Если значе ние параметра не определено, то таблица размещается в таб личной области, по умолчанию для пользователя, который является владельцем схемы, содержащей таблицу.
Ключевое слово STORAGE определяет объем внешней памяти, выделяемый под таблицу. Для больших таблиц целе сообразно явно выделять требуемую память для уменьшения запросов на динамическое выделение пространства для таб лицы.
Для управления записью в журнал контрольной инфор мации используются ключевые слова RECOVERABLE и UNRECOVERABLE. Значение UNRECOVERABLE может быть использовано только с ключевым словом AS подзапрос, при этом операция создания таблицы выполняется за счет исключения записи управляющей информации в журнал.
Но при этом автоматическое восстановление операции созда ния таблицы в случае сбоя становится невозможным.
Ключевое слово CLUSTER указывает привязку столбцов таблицы к кластеру. Обычно столбцы кластера образуют из элементов первичного ключа.
Ключевое слово ENABLE указывает на включение огра ничений целостности для данной таблицы. Соответствующее ограничение целостности должно быть определено в данном предложении создания таблицы. По умолчанию все ограни чения целостности, определенные в предложении, включают ся.
Ключевое слово DISABLE указывает на выключение ог раничений целостности для данной таблицы. Соответствую щее ограничение целостности должно быть определено в дан ном предложении создания таблицы.
Конструкция AS запрос включает в создаваемую таблицу строки, являющиеся результатом выполнения запроса. Обра тите внимание на необходимость определенной осторожности при использовании вставки строк через подзапрос и опреде ление ограничений целостности в том же предложении. (Если результат запроса не соответствует ограничениям целостно сти, то Oracle не создает таблицу и возвращает сообщение об ошибке.) Ключевое слово CACHE указывает на то, что блоки, вы бираемые из таблицы, помечаются в системном кэше, как наиболее используемые. маленьких таб лиц, для преобразований кодов в значения. По умолчанию используется значение для которого выбранные блоки помещаются в конец таблицы частот обра щений к кэшу.
Таблицы и столбцы можно документировать с помощью конструкции COMMENT.
Рассмотрим пример создания таблицы с тремя атри бутами в схеме пользователя Ограничение указывает, что атрибут является первичным ключом, ограничение nn_Tabl_At2, указывает, что атрибут не допускает ввода неопределенных значений, значение атрибута по есть текущая дата.
SQL CREATE TABLE 2 (Atl NUMBER CONSTRAINT pk_Tabl_Atl KEY, 3 NUMBER CONSTRAINT nn NOT NULL, SQL Ч обработки данных Oracle 4 DATE DEFAULT Table Листинг Протокол создания таблицы с размеще нием в определенной табличной области и оп ределенными параметрами хранения Следующий пример демонстрирует создание таблицы с двумя атрибутами Atl и размещенной в таблич ной области (которая должна быть создана заранее) с ассоциированным с первичным ключом индексом, разме щенным в табличной области Под таблицу резер вируется начальный экстент в 100 килобайт и определяется экстент приращения в 50 килобайт.
CREATE TABLE Tab 2 (Atl NUMBER CONSTRAINT pk_Tabl_Atl PRIMARY KEY 3 USING INDEX TABLESPACE 4 NUMBER) 5 TABLESPACE app_data 6 STORAGE (INITIAL 100K NEXT Table created.
25. Протокол создания таблицы с размеще нием в определенной табличной области и оп параметрами хранения Существующие таблицы могут быть модифицированы с помощью команды ALTER TABLE. С ее помощью добавить один или несколько новых столбцов, ограничения целостности, модифицировать определение существующего столбца (тип данных, длину, умалчиваемое значение или ог раничение целостности NOT NULL), модифицировать пара метры хранения и транзакций NEXT, Изменение таблицы, особенно включение/отключение ограничений це имеет свои особенности, рассмотренные в разделе "Средства обеспечения целостности данных в Oracle".
удаления из базы данных таблицы (вместе с ее со держимым) используется оператор DROP TABLE. Для вы полнения операции уничтожения таблицы необходимо быть либо владельцем таблицы, либо иметь привилегию DROP ANY Когда таблица уничтожается, все блоки стано вятся свободными для использования под данные или индек сы других таблиц. Оператор удаления таблицы Oracle исполь зует следующий синтаксис: DROP TABLE [ CASCADE ].
Все индексы и триггеры, ассоциированные с таблицей, даже если они были созданы другим пользователем, удаляют ся. Все хранимые программы, зависящие от таблицы, остают ся, но становятся недействительными (непригодными для ис пользования). Все синонимы удаленной таблицы остаются, но возвращают ошибку при обращении к ним.
синонимы и программы вновь становятся актуальными, если таблица создается заново (после их перекомпиляции).
Если указано ключевое слово CASCADE CONSTRAINTS, то удаляются все ограничения целостности, ссылающиеся на первичные и уникальные ключи данной таблицы. Если такие ссылки существуют, CASCADE отсутствует, то удаление таблицы не выполняется и сервер возвращает сообщение об ошибке. Перед удалением таблицы рекоменду ется определить через представление словаря данных зависимости других таблиц от данной пример предложения для уничтожения таблицы DROP TABLE Table dropped;
26. Пример предложения для уничтоже ния таблицы ТаЫ SQL Ч Oracle определения и представлений Представление Ч это поименованная, динамически под держиваемая сервером выборка из одной или нескольких таб лиц или других представлений. Оператор SELECT, опреде ляющий выборку, ограничивает видимые пользователем дан ные. Кроме того, представление позволяет эффективно огра ничить данные, которые пользователь может модифициро вать. Используя администратор базы данных ограничивает доступную пользователям часть логического пространства базы данных только теми данными, которые реально им необходимы. Оператор определения представле ний Oracle использует следующий синтаксис:
CREATE [OR REPLACE] | NO VIEW ] [ ) ] AS запрос WITH { READ ONLY | CHECK OPTION [CONSTRAINT Ключевые слова OR REPLACE указывают на принуди тельное замещение старого представления новым. Использо вание этого параметра позволяет не выполнять повторного предоставления привилегий, которое было бы необходимо, если использовать команды DROP VIEW и CREATE VIEW для уничтожения и создания представления заново..
Ключевое слово FORCE указывает на принудительное создание представления вне зависимости от того, существуют ли базовые таблицы представления, и есть ли у пользователя, создающего представление, привилегии на выборку из базо вых таблиц. Если предложение CREATE VIEW не имеет син таксических ошибок, то Oracle может создать представление даже в том случае, когда определяющий представление за прос не может быть выполнен. Такое представление считает ся "созданным с ошибками".
4. 1628.
Конструкция NO FORCE (используемая по умолчанию) указывает на обязательность существования базовых таблиц и представлений и наличия у пользователя, создающего пред привилегий на доступ к базовым таблицам. При нарушении какого-либо из этих условий представление не создается.
Параметр запрос используется для обозначения синтаксически правильного не содержащего ключе вого слова ORDER BY или конструкции FOR UPDATE.
Ключевое слово WITH READ ONLY указывает на запре щение для базовых таблиц операций модификации данных с указанием представления.
Ключевое слово WITH CHECK OPTION указывает на то, что строки в базовых таблицах, изменяемые и вставляемые через представление, должны соответствовать критерию от бора в запросе, определяющем представление.
Ключевое CONSTRAINT определяет имя ограни чения, используемое для проверки.
Представление является изменяемым, то есть по отноше нию к нему можно использовать оператор DELETE, INSERT и UPDATE, в том случае, если выполняются следующие ус ловия для образующего представление запроса:
Ч в списке выборки не указано ключевое слово DISTINCT;
Ч каждое арифметическое выражение в списке выборки представляет собой одну спецификацию и специфи кация одного столбца не появляется более одного раза;
Ч в условии выборки раздела WHERE не используются подзапросы;
Ч в запросе отсутствуют конструкции GROUP и HAVING.
Если в списке выборки спецификации запроса имеется хотя бы одно арифметическое выражение, состоящее не из одной спецификации столбца, или если имя хотя бы одного столбца участвует в списке выборки более одного раза, опре деление должно содержать список имен столбцов таблицы.
SQL Ч обработки данных Oracle Более просто, нужно явно именовать столбцы представляе мой таблицы, если эти имена не наследуются от столбцов таблиц раздела FROM спецификации запроса.
Требование WITH CHECK OPTION в определении пред ставления имеет смысл только в случае определения изме няемой представляемой таблицы, которая определяется спе цификацией содержащей раздел WHERE. При нали чии этого требования не допускаются изменения представ ляемой таблицы, приводящие к появлению в базовых таб лицах строк, не видимых в представляемой таблице (то есть таких строк, которые не удовлетворяют условию поиска раз дела WHERE спецификации Если WITH CHECK OPTION в определении представления отсутствует, такой контроль не С помощью специального представления словаря данных можно узнать, какие из столбцов, включенных в представление, доступны для изме нения. Обсуждение вопросов, связанных с обновляемыми представлениями, построенными над несколькими базовыми таблицами, выходят за рамки этой книги.
Рассмотрим пример создания представления с огра ничением только для чтения. Попытка вставки данных через представление отвергается системой, несмотря на явное предоставление привилегии пользователю и2. Вставка дан ных в базовую таблицу выполняется успешно. Ниже приведен протокол использования представления Vul.
SQL> CONNECT CREATE TABLE NUMBER, Table created.
CREATE VIEW Vul AS 2 SELECT * FROM Tab2 WITH READ ONLY;
View created.
SQL> GRANT INSERT ON Tab2 TO Grant succeeded.
GRANT INSERT ON TO Grant succeeded.
SQL> CONNECT Connected.
SQL> INSERT INTO VALUES INSERT INTO ul.VUl VALUES * ' ERROR at line 1:
data manipulation operation not legal on this view SQL> INSERT INTO VALUES 1 row created.
Листинг Протокол создания представления с ог раничением только для чтения Рассмотрим пример создания представления Vu2 с про веркой принадлежности вводимых данных области допусти мых значений. Для представления Vu2 попытка вставки дан ных, не удовлетворяющих критерию также отверга ется системой.
CONNECT SQL> CREATE VIEW Vu2 AS SELECT * FROM Tab 2 WHERE 1 WITH CHECK View SQL> GRANT INSERT, SELECT ON Vu2 TO Grant succeeded.
Ч обработки данных CONNECT Connected.
SQL> INSERT INTO ul.Vu2 VALUES INSERT INTO VALUES * ERROR at line 1:
view WITH CHECK violation SQL> INSERT INTO ul.Vu2 VALUES row created.
SQL> SELECT * FROM AT 2 Листинг Протокол создания представления с проверкой принадлежности области значений Как отмечено выше, для того, чтобы конструктивно рабо тать с представлением, должен, как минимум, иметь привилегию SELECT для всех таблиц, которые учас твуют в запросе, формирующем представление. Поэтому при вилегии, которыми обладает пользователь на базовые табли цы, наследуются представлением для пользователя, который создает. Если пользователь обладает любой комбинацией привилегий INSERT, UPDATE, DELETE для базовых таблиц, то эти привилегии будут автоматически наследоваться пред ставлением. В то же время пользователь, не имеющий приви легий на модификацию строк базовых таблиц, не может по лучить соответствующие привилегии в представлении.
Еще одно свойство представлений состоит в том, что они позволяют реализовать доступ пользователей к данным, которые являются производными базовых таблиц. Например, пусть пользователь создает представле ние, в котором для таблицы вычисляется поэлементная сумма и среднее значение значений в столбцах и со ответственно. Пользователю и2 предоставляется право вы борки из представления, но не базовой таблицы. Ниже при веден протокол примера, иллюстрирующего данное свойство:
Connected.
SQL> CREATE VIEW Vu3 AVGAt2) 2 AS SELECT SUM FROM View created.
SQL> GRANT SELECT ON Vu3 TO Grant succeeded.
CONNECT Connected.
SQL> SELECT * FROM SQL> SELECT * FROM SELECT * FROM * ERROR at line insufficient privileges 29. Протокол создания представления и вы полнения операций выборки данных из пред ставления и базовой таблицы Удаление представления выполняется командой DROP VIEW. Для удаления представления быть его владельцем или иметь привилегию DROP ANY VIEW. Ис пользуется следующий синтаксис:
DROP VIEW SQL Ч обработки данных Oracle При удалении представления объекты, ссылающиеся на удаляемое представление, не уничтожаются, а становятся не действительными. Привилегии на удаляемое представление также отменяются. Рассмотрим пример удаления представле ния Vu3:
DROP VIEW View Листинг 30. Пример удаления представления Средства и Пользователь Ч это объект Oracle, который может вы полнять предусмотренные действия с объектами Oracle в со ответствии с поддерживаемой технологией.
Оператор определения пользователей Oracle использует следующий синтаксис:
CREATE USER IDENTIFIED { BY пароль \ EXTERNALLY } [DEFAULT [TEMPORARY TABLESPACE [QUOTA [{ К | М }] | UNLIMITED } ON Параметр имя задает имя, под которым пользователь регистрируется в системе. Имя пользователя должно быть указано в кодировке, поддерживаемой сервером.
Ключевое слово BY указывает, что подтверждающий подлинность пользователя пароль будет указан явно в пара метре Обратите внимание, что существуют некоторые ограничения на использование двухбайтовых кодировок. В частности, Oracle рекомендует использовать по крайней мере один символ из однобайтового набора ASCII и по сути за прещает использование в паролях символов и Если указан параметр EXTERNALLY, то сервер проверяет соот ветствие зарегистрированного операционной системой поль зователя и пользователя Oracle.
Конструкция DEFAULT TABLESPACE указывает имя табличной области, задаваемое параметром которая используется для объектов создавае мого пользователя по умолчанию. Если она не указана, то для объектов создаваемого пользователя будет использоваться табличная область SYSTEM. Учитывая то, что в табличной области SYSTEM размещен словарь данных, использовать ее для пользовательских объектов нецелесообразно.
Конструкция TEMPORARY TABLESPACE указывает системе имя табличной области, задаваемое параметром которая используется по умолча нию для временных сегментов создаваемого пользователя.
Временные сегменты используются для хранения промежу точных данных. Если ключевое слово TEMPORARY TABLESPACE не указано, то для временных сегментов соз пользователя будет использоваться табличная об ласть SYSTEM. Для многопользовательской системы, к кото рой предъявляются повышенные требования по производи тельности, табличные области для временных сегментов и для объектов пользователя желательно разнести по различ ным физическим носителям.
Ключевое слово QUOTA задает ограничения на исполь зуемое пользователем пространство в конкретной табличной области. Максимально допустимое пространство задается па раметром в мегабайтах, если указано ключевое слово М, килобайтах, если указано ключевое слово К, или в байтах, если не указано ни М, ни К. Указание ключевого сло ва UNLIMITED разрешает пользователю использовать про странство без ограничений. Обратите внимание, что при реги страции пользователя можно использовать ключевое слово SQL Ч Oracle QUOTA несколько раз и определить для пользователя допус тимое пространство в нескольких табличных областях.
Ключевое слово PROFILE назначает пользователю про параметром Если ключевое слово PROFILE не указано, пользователю приписывается про филь DEFAULT.
Для регистрации пользователей необходимо иметь вилегию CREATE USER.
Рассмотрим пример создания пользователя который использует пароль U1PSW и которому назначена табличная область по умолчанию app_data с ограничением на исполь зуемое пространство в 1 мегабайт, и разрешением использо вать табличную область tools с ограничением на исполь зуемое пространство 500 килобайт.
CREATE USER ul IDENTIFIED BY 2 app_data 3 1M ON app_data 4 QUOTA 500K ON tools;
User created.
Листинг Протокол операции регистрации пользо вателя Для изменения пользователей используется оператор ALTER USER. Оператор изменения пользователей Oracle ис пользует следующий синтаксис:
ALTER USER IDENTIFIED { пароль \ EXTERNALLY } [DEFAULT TABLESPACE TABLESPACE [QUOTA К | | UNLIMITED } ON [PROFILE Рассмотрим пример, иллюстрирующий изменение пароля пользователя:
ALTER USER BY User altered.
Листинг Пример выполнения операции изменения пароля пользователя Для исключения из базы данных пользователя использу ется оператор DROP USER. При исключении пользователя должны быть удалены все объекты, принадлежащие этому пользователю. Для выполнения операции исключения вателя необходимо иметь привилегию DROP USER.
Оператор исключения пользователя Oracle использует следующий синтаксис: DROP USER имя [ Параметр имя задает имя пользователя в системе. Если указано ключевое слово CASCADE, то автома тически удаляются все объекты исключаемого пользователя.
Если ключевое слово CASCADE не а в схеме пользо вателя содержатся объекты, возвращается сообщение об ошибке. Также будет возвращено сообщение об ошибке, если пользователь подключен в его исключения.
Обратите внимание на то, что если указано ключевое сло во CASCADE, то автоматически удаляются все ограничения целостности, которые имеют ссылки на первичные и уни кальные ключи удаляемых таблиц исключаемого пользова теля. Представления, синонимы, функции и процедуры, ссы на объекты исключаемого помеча ются как недействительные.
Рассмотрим пример, иллюстрирующий выполнение опе рации исключения пользователя:
SQL Ч обработки данных Oracle SQL> DROP USER ul;
DROP USER Ul * ERROR at line CASCADE must be specified to drop DROP USER ul CASCADE;
User dropped.
Листинг Пример выполнения операции исключения пользователя Операция вставки строк Операция INSERT используется для вставки строк в таб лицу или базовые таблицы представления. Предложение INSERT имеет следующий синтаксис:
INSERT INTO \ } \ ( ) } ) ] (выражение выражение ] | Фразы INSERT должны быть записаны в указанном порядке. При вставке строк с использованием представления строки добавляются в базовые таблицы пред ставления. Необязательный параметр использует ся для уточнения имени схемы, в которой находится соответ ствующий объект Oracle. По умолчанию используется схема пользователя, выполняющего устанавливает имя связи с уда ленной базой данных. Если имя связи с удаленной базой дан ных не указано, предполагается, что соответствующий объект Oracle расположен в основной базе данных.
Параметр задает подзапрос, который в дан ном контексте рассматривается как представление.
Параметр выражение заменяется на вычисляемое выра жение, обычно базирующееся на данных столбцов из таблиц, представлений и снимков, указанных в перечне значений ключевого слова INTO.
Параметр задает подзапрос, который фор мирует множество вводимых строк.
Рассмотрим примеры применения оператора INSERT.
Проиллюстрируем использование оператора вставки строк на таблицах и созданных и заполненных предложе ниями:
CREATE TABLE CREATE TABLE Tab2 (Atl Простейшим вариантом ввода данных является вставка строки явным указанием списка значений.
INSERT INTO 1 row created.
Листинг 34. Вставка строки явным указанием списка значений Если значение какого-либо атрибута не определено и описание таблицы не запрещает наличие неопределенного значения данного атрибута, то может использоваться вставка строки с неопределенным значением атрибута.
SQL> INSERT INTO 1 row created.
Листинг 35. Вставка строки с неопределенным зна чением одного из атрибутов Данные, вводимые в таблицу, могут быть результатом за проса к другой таблице, снимку или представлению.
SQL Ч Oracle SQL> INSERT INTO Tab2 SELECT FROM WHERE = 1 row, Листинг 36. Вставка данных, являющихся результа том запроса к некоторой таблице или пред ставлению Данные, вводимые в таблицу, могут быть элементом по следовательности или результатом функции. Обычно таким образом формируются уникальные (за счет свойств последо вательности) идентификаторы и временные метки. Проиллю стрируем использование оператора вставки строк на таблице и последовательности созданных предложениями:
CREATE TABLE (Atl DATE, Table created.
CREATE SEQUENCE IdSeq;
Sequence created."
SQL> INSERT INTO VALUES (SYSDATE, 1 row created.
Листинг 37. Вставка данных, являющихся элементом последовательности и результатом выполнения встроенной функции удаления Операция DELETE используется для удаления строк из таблицы или базовых таблиц представления. Предложение DELETE имеет следующий синтаксис:
DELETE [FROM] { ] { | } ] | подзапрос } [ [WHERE условие ] Фразы предложения DELETE должны быть записаны в указанном порядке. При удалении строк с указанием пред ставления удаляются строки из базовых таблиц представле ния. Необязательный параметр используется для уточнения имени схемы, в которой находится соответствую щий объект Oracle. По умолчанию используется схема поль зователя, выполняющего операцию.
Параметр имя связи с удален ной базой данных. Если имя связи с удаленной базой данных не указано, предполагается, что соответствующий объект. Oracle расположен в базе данных.
Параметр подзапрос задает подзапрос, который в данном контексте рассматривается как представление.
Если ключевое слово WHERE отсутствует, то из таблицы удаляются все строки. Если же ключевое сл.ово WHERE ис пользуется, удаляются строки, для которых условие выполня ется. Все удаляемые строки соответствующие индексы ос вобождают занимаемую ими память.
Рассмотрим пример, иллюстрирующий применение опе ратора DELETE. Выполнение первого предложения приводит к удалению из всех строк, для которых значе ние атрибута не превышает Второе предложение уда ляет все строки из таблицы DELETE FROM WHERE Atl < = 1 row deleted.
SQL> DELETE Tabl;
1 row deleted.
Листинг 38. Удаление строк с использованием кри терия отбора и безусловное удаление строк Ч обработки данных Операция модификации Операция осуществляет модификацию строк из таблицы, базовой таблицы представления или снимка. Предложение UPDATE имеет следующий синтаксис:
| \ } | } [ SET { [, ) = ( ) ] \ { выражение \ } } [WHERE условие ] Фразы предложения должны записаны в указанном порядке. При модификации строк с параметром изменяются строки из базовых таблиц представления. Необязательный параметр исполь зуется для уточнения имени схемы, в которой находится соот ветствующий объект Oracle. По умолчанию используется схе ма пользователя, выполняющего операцию.
Параметр устанавливает имя связи с удален ной базой данных. Если имя связи с удаленной базой данных не указано, предполагается, что соответствующий объект Oracle расположен в локальной базе данных.
Параметры за дают подзапросы, которые в данном контексте рассматрива ется также представление.
выражение представляет собой вычисляемое выражение, обычно базирующееся на данных столбцов из таблиц.
Рассмотрим несколько примеров, при менение оператора UPDATE. В таблице выполняется модификация строк с использование критерия отбора моди фицируемых строк.
SELECT * FROM Tabl;
AT 1 AAA 2 BBB SQL> UPDATE Tabl SET = WHERE = 1;
1 row updated.
SQL> SELECT * FROM Tabl;
AT 1 CCC 2 BBB SQL> UPDATE Tabl SET Atl = 2 WHERE LIKE 1 row updated.
SQL> SELECT * FROM Tabl;
AT 1. CCC 3 BBB Листинг Примеры модификации строк таблицы Специальные SQL При выполнении выборки данных в критерии за даваемом ключевым словом WHERE, используются специ альные предикаты IN, BETWEEN, LIKE, EXIST, IS NULL и предикаты с квантором. Ниже приводятся синтаксические конструкции и примеры использования этих предикатов.
SQL Ч обработки данных Oracle Предикат IN Предикат IN определяет множество, вхождение в которое определяет истинность предиката.
Предикат IN определяется следующими синтаксическими правилами:
Предикат IN [NOT] IN \ Типы левого операнда выражение и значений из списка правого операнда должны быть сравнимыми. Напомним, что результирующий набор записей подзапроса должен содер жать ровно один столбец.
Значение предиката равно TRUE в том и только в том случае, когда значение левого операнда совпадает хотя бы с одним значением из списка правого операнда. Если список правого операнда пуст может быть, если правый операнд задается подзапросом) или значение подразумеваемого пре диката сравнения х = у (где х Ч значение арифметического выражения левого операнда) равно FALSE для каждого эле мента у списка правого операнда, то значение предиката IN равно FALSE.
По определению значение предиката NOT IN S равно значению предиката NOT (x IN S).
Обычно запрос с предикатом IN используется для про верки вхождения в явно определенное множество небольшой размерности или во множество, формируемое подзапросом.
Проиллюстрируем использование предиката IN на таблице, созданной и заполненной предложениями:
CREATE TABLE CHAR(3), INSERT INTO INSERT INTO Tabl INSERT INTO Tabl Следующие демонстрируют варианты использо вания предиката IN:
SQL> SELECT. * FROM 2 WHERE IN 3 AND NOT IN AT A С SELECT * FROM Tabl WHERE IN 2 (SELECT Atl FROM Tabl WHERE Atl > AT В С 40. Запросы, характеризующие использова ние предиката IN. с множеством, задаваемым явным перечислением, и множеством, задавае мым подзапросом Оператор BETWEEN похож на оператор IN. В отличие от определения элементов перечислением или с по мощью подзапроса, как это делается для предиката IN, пре дикат BETWEEN определяет диапазон, принадлежность зна чения к которому определяет истинность предиката.
вое слово BETWEEN указывается перед начальным значени ем, затем идет ключевое слово AND и завершает конс трукцию конечное значение. Для предиката BETWEEN по рядок следования начального и конечного значений важен.
Предикат BETWEEN имеет следующий синтаксис:
SQL Ч данных Oracle Предикат BETWEEN выражение [NOT] BETWEEN AND По определению результат х BETWEEN у AND z тот же самый, что результат логического выражения х > = у AND х < = z. Результат х NOT BETWEEN у AND z тот же самый, что результат NOT (x BETWEEN у AND z).
Обычно запрос с предикатом BETWEEN используется для проверки вхождения значения в диапазон дат или число вой диапазон.
Проиллюстрируем использование предиката BETWEEN на таблице, созданной и заполненной предложениями:
CREATE TABLE (Atl INSERT INTO INSERT INTO Tabl INSERT INTO Tabl Следующие запросы демонстрируют два варианта ис пользования предиката BETWEEN (напомним, что функция SYSDATE возвращает текущую дату и Oracle поддерживает естественную арифметику дат):
SELECT SYSDATE FROM Tabl 2 WHERE Atl BETWEEN AND SYSDATE;
AT2 SYSDATE 01-01-2002 SELECT * FROM Tabl 2 WHERE BETWEEN 0 AND 1;
AT 01-01-2002 01-07-2002 Листинг 41. Запросы, характеризующие использова ние предиката BETWEEN с диапазоном дат и чисел Сравните результат предыдущего запроса с результатом следующего запроса:
SELECT FROM 2 WHERE BETWEEN 1 AND 0;
no rows selected Запрос, характеризующий использование предиката BETWEEN с числовым диапазоном LIKE Предикат LIKE применим только к полям типа CHAR, и Предикат принимает истинное значение при вхождении определенной подстроки в строку. В качестве механизма формирования условия используется шаблон, состоящий из специальных символов и обычных символов используемой кодировки. В роли специальных сим волов выступают:
символ подчеркивания замещающий любой одиноч ный символ;
символ процента замещающий последовательность любого числа символов (включая пустой символ).
Например, на шаблоне предикат LIKE принимает истинное значение для всех значения атри бутов, первый из которых символ а на шаблоне предикат LIKE принимает истинное значение для всех значе ния атрибутов, первый из которых символ а длина произ вольна. Если ограничение накладывается на значения индек сированного столбца, а шаблон начинается со специального SQL Ч данных Oracle то использование индекса в этом случае, как пра вило, невозможно.
Предикат LIKE имеет следующий синтаксис:
[NOT] LIKE шаблон Типы данных столбца левого операнда и образца должны быть типами символьных строк. Значение предиката истинно, если шаблон определяет заданного значения атри бута. Обратите внимание, что значение предиката LIKE не определено (UNKNOWN), если значение атрибута не опреде лено.
Проиллюстрируем использование предиката LIKE на таб лице, созданной и заполненной предложениями:
CREATE TABLE Tabl (Atl INSERT INTO Tabl INSERT INTO Tabl INSERT INTO Tabl INSERT INTO Tabl INSERT INTO Tabl Следующие запросы демонстрируют варианты поиска значений атрибутов, задаваемых различными шаблонами:
SQL> SELECT * FROM Tabl WHERE Atl LIKE AB ABC ACB ADC SQL> SELECT * FROM Tabl WHERE Atl LIKE AB Раздел CAB SELECT * FROM WHERE Atl LIKE ACB SQL> SELECT * FROM Tabl WHERE Atl LIKE ABC ADC Листинг 43. Запросы, характеризующие использова ние LIKE с различными шаблонами IS NULL Распространенной является ситуация, когда в некоторых записях таблицы присутствуют атрибуты с неопределенными значениями, например, потому что значение атрибута не было введено. В языке SQL для указания неопределенного значе ния атрибута используется значение NULL. Когда значение атрибута есть NULL, подразумевается, что атрибут не принял никакого конкретного значения. Значение NULL поддержи вается специальным образом и не имеет какого-либо типа данных. Атрибут с любым типом данных может иметь значе ние NULL.
Для (а также младших версий) для символьных значений переменной длины значения и NULL эквивалент ны. Это же верно для типов CHAR, RAW, LONG RAW, LONG, DATE, BLOB, CLOB, MLSLABEL.
Выражение типа Atl = NULL или Atl IN (NULL) будет иметь неопределенное значение, независимо от значения Ч данных Oracle Для операций со значением NULL применяются следую щие правила: результатом сравнения любого значения со че.нием NULL всегда является NULL;
применение логическо го оператора NOT к значению NULL все равно возвращает NULL;
если в условном операторе выражение возвращает значение NULL, то последовательность операторов, указан ная в этом условном операторе, не выполняется.
Для обработки неопределенных значений в языке SQL используется специальный оператор IS NULL.
Предикат IS NULL имеет следующий синтаксис:
Предикат IS NULL ::= IS [NOT] NULL Предикат IS [NOT] NULL всегда принимает значения TRUE или FALSE. При этом значение х IS NULL равно TRUE тогда и только тогда, когда значение х не определено. Значе ние предиката х IS NOT NULL равно значению NOT (x IS NULL).
Проиллюстрируем использование предиката IS NULL на таблице, созданной и заполненной предложениями:
CREATE TABLE INSERT INTO INSERT INTO Tabl Следующие запросы демонстрируют использование диката IS NULL:
SQL> SELECT * FROM Tabl WHERE IS NULL;
AT В SQL> SELECT * FROM Tabl WHERE IS NOT NULL;
Х A Листинг 44. Примеры использования предиката IS NULL Предикат EXISTS Предикат EXISTS принимает истинное значение, если не пуст результат некоторого подзапроса. Предикат EXISTS мо жет вычисляться автономно или в комбинации с другими предикатами, соединенными логическими связками. Данный предикат не может принимать неопределенного значения, то есть его значением всегда является TRUE или FALSE. Значе ние равно TRUE тогда и только тогда, когда результат вычис ления подзапроса не пуст.
Предикат EXIST использует следующий синтаксис:
Предикат EXISTS EXISTS подзапрос Проиллюстрируем использование предиката EXISTS на таблице, созданной и заполненной предложениями:
CREATE TABLE INSERT INTO VALUES INSERT INTO Tabl VALUES Следующий показывает, как с использованием предиката EXISTS выполняется выборка всех строк таблицы для которых есть (существует) строка, у которой атри бут At2 имеет меньшее значение:
SQL> SELECT * FROM ТаЫ a WHERE EXISTS 2 (SELECT * FROM Tabl b WHERE > SQL Ч обработки данных Oracle AT В 45. Пример использования предиката EXISTS для вложенного подзапроса Обратите внимание на распространенную ошибку при использовании предиката Следующий запрос выдает все таблицы а не вторую строку, как можно ошибочно ожидать:
SELECT * FROM Tabl WHERE EXISTS (SELECT WHERE На самом деле подзапрос, указанный в скобках, возвра щает что-то и, следовательно, предикат EXISTS принимает истинное значение для любой строки таблицы Oracle допускает использование агрегирующих функций в подзапросе. При этом необходимо осознавать сомнитель ность подобных конструкций: ведь если найдены данные для вычисления функции, предикат EXISTS возвра щает истинное значение независимо от результата Предикаты с ANY и Предикат ALL несет стандартную нагрузку квантора все общности, а предикаты ANY и SOME соответствуют стандар тно понимаемому квантору Предикаты ANY и SOME в Oracle несут одинаковую смысловую и полностью взаимозаменяемы. В любом запросе вместо пре диката ANY можно использовать SOME и наоборот Ч ре зультат будет одинаков. Использование двух равнозначных ключевых слов, видимо, обусловлено стремлением облегчить запросов для англоязычных пользователей. Пре дикаты с кванторами имеют следующий синтаксис:
Предикат с квантором выражение {Х-= \ О \ |<= I > I < } I SOME | ANY } подзапрос пояснения механизма вычисления результата запроса обозначим через х результат вычисления выражения левой части предиката, а через S результат вычисления подзапроса.
Предикат (х операция ALL S) принимает значение TRUE, если S пусто или значение предиката х операция s истинно для каждой строки s, входящей в S. Предикат х операция ALL S имеет значение FALSE, если значение предиката х операция s ложно хотя бы для одной строки s, входящей в Предикат (х операция SOME S) принимает значение TRUE, если значение предиката х операция s истинно хотя бы для одной строки s, входящей в S. Предикат (х операция SOME S) принимает значение FALSE, если S пусто или зна чение предиката х операция s ложно для каждой строки s, входящей в S.
Предостережем читателя, знакомого с математической логикой, от поспешных формально-логических выводов. Дей ствительно, любой запрос, содержащий предикат SOME, мо жет быть сформулирован с предикатом EXISTS, но обратное утверждение неверно. Отличие обработки предиката с EXISTS от обработки предиката с SOME или с ALL в том, как обрабатываются пустые значения (NULL). С формальной точки зрения можно построить любой правильный запрос, пользуясь только предикатами EXISTS и IS NULL. Тем не менее многие пользователи находят применение предикатов SOME и ALL более удобным.
операции Теоретико-множественные операции объединяют резуль таты многокомпонентного запроса в единый результат.
SQL Ч данных Oracle Операции, поддерживаемые в Oracle, интерпретируются образом:
UNION Ч формальное объединение результатов всех ис ходных запросов в виде отношения (то есть с устранением повторяющихся строк);
UNION ALL Ч формальное объединение результатов всех исходных запросов с сохранением строк;
INTERSECT Ч формальное пересечение;
включает строки, входящие во все результаты составляющих запросов, повторяющиеся строки исключаются;
MINUS Ч результирующее множество содержит все вошедшие в результат первого запроса, но не вошед шие в результат второго;
повторяющиеся строки исклю чаются.
Все теоретико-множественные операции имеют одина ковый приоритет и выполняются слева направо. Очевидно, что, исключением MINUS, операции коммутативны. По скольку в последующих версиях Oracle для совместимости с планируемыми международными стандартами, возможно, приоритет операции INTERSECT будет выше, чем остальных теоретико-множественных операций, целесообразно для уст ранения неоднозначности расставлять скобки, явно опреде ляющие порядок выполнения операций.
Естественно, что результаты запросов во всех множест вах, участвующих в теоретико-множественной операции, должны быть согласованы по количеству столбцов и их ти пам.
Обратите внимание, что допускается проведение опера ции, если в части результатов данные имеют тип a часть Ч Соответствующие данные результата всегда имеют тип При выполнении теоретико-множественных операций, отличных от UNION ALL, неявно выборка всех и их сортировка, поэтому время выполнения запроса может оказаться неожиданно большим.
Проиллюстрируем использование теоретико-множест венных операций на таблицах, созданных и заполненных предложениями:
TABLE CREATE Tab2 (Atl INSERT INTO VALUES INSERT INTO Tabl VALUES INSERT INTO Tabl VALUES INSERT INTO Tab2 VALUES INTO Tab2 VALUES (3);
INSERT INTO Tab2 VALUES Следующие запросы демонстрируют использование тео ретико-множественных операций:
SELECT FROM Tabl UNION SELECT * FROM SELECT * FROM Tabl 2 UNION ALL SELECT * FROM SELECT * FROM Tabl 2 INTERSECT SELECT * FROM SQL Ч данных Oracle SELECT * FROM MINUS SELECT * FROM 46. Примеры использования теоретико множественных операций в запросах Внешнее объединение Прежде чем обсуждать внешнее объединение, рассмот рим простое объединение (simple join). Если при выполнении выборки не используется ключевое слово WHERE, то результатом является декартово произведение таблиц, снимков или представлений, участвующих в выборке.
Обычно комбинировать все строки одной таблицы со всеми строками другой не требуется, поэтому используется критерий отбора, определяемый ключевым словом WHERE.
Такое объединение называется простым.
Проиллюстрируем технику выполнения операций декар това произведения и простого объединения на таблицах, соз данных и заполненных предложениями:
CREATE TABLE ) CREATE TABLE Tab2 (Atl NUMBER, INSERT INTO VALUES (1, INSERT INTO Tabl (2, INTO Tabl VALUES (3, INSERT INTO Tab2 VALUES (1, INSERT INTO Tab2 VALUES (3, INSERT INTO VALUES (5, Рассмотрим пример декартова произведения таблицы на таблицу и простого объединения, где критерием отбора служит равенство значений атрибутов таблиц и SELECT 2 FROM AT2 AT 1 A 2 В 3 С 1 A 3 b 2 В 3 b 3 С 3 b 1 A 5 с 3 С 9 selected.
SELECT * FROM 2 WHERE AT2 AT 1 A 3 С 3 b Листинг Примеры декартова произведения и про стого соединения для запросов, содержащих две таблицы Внешнее объединение (outer join) в общем случае отби рает больше строк, чем простое объединение. При выполне нии операции внешнего объединения отбираются все строки, которые были бы отобраны при выполнении операции про стого объединения и дополнительно отбираются строки из одной таблицы, которым не обнаружено соответствия по ис SQL Ч обработки данных пользуемому критерию отбора ни с одной строкой другой таблицы.
Внешнее отображается в конструкции фра зы WHERE в одной из двух форм:
= ( + ) = Символ внешнего объединения (+) должен следовать не посредственно за столбцом, по отношению к которому вы полняется внешнее объединение.
Для лучшего понимания операции внешнего объединения сравните результаты простого объединения, приведенные в листинге 47, и двух вариантов операции внешнего объ единения, приведенных в примере, представленном ниже.
SQL> SELECT FROM 2 WHERE Tabl.Atl = A A 1 A 2 В 3 С 3 b SELECT * FROM 2 WHERE = A A 1 А 1 а з Х С 3 b 5 с Листинг 48. Примеры внешнего объединения, зада на столбцах различных таблиц Для сортировки результатов запроса по возрастанию или убыванию используется ключевое слово ORDER BY. Без ука зания этого ключевого слова строки извлекаются в произ вольном порядке.
Сортировка задается использованием следующей синтак сической конструкции:
ORDER -положение | I DESC] Параметр выражение принимает значение выражения, базирующегося на одном или нескольких столбцах, перечис ленных после ключевого слова SELECT. Строки с одинако выми значениями по выражению упорядочиваются по второму выражению (если оно определено) и так далее.
Параметр задает число, идентифицирующее позицию столбца в перечислении после ключевого слова SELECT, то есть вместо указания имен столбцов можно ука зать номер позиции столбца в списке SELECT.
Ключевые слова ASC или DESC определяют возрастаю щий или убывающий соответственно порядок сортировки.
Обратите внимание на то, что значение NULL рассмат ривается как "самое тяжелое" и размещается в конце списка при сортировке в порядке возрастания (ASC) и в начале спи ска Ч при сортировке в порядке убывания (DESC).
На использование ключевого слова ORDER BY жены Ч ключевое слово ORDER BY в предложении SELECT должно быть размещено после всех остальных ключевых слов за исключением FOR UPDATE. Фрагменты с ми словами ORDER BY и FOR UPDATE можно менять мес тами;
SQL Ч данных Oracle Ч если в предложении SELECT присутствует ключевое слово DISTINCT, то в ORDER BY не должны ствовать столбцы, не упоминавшиеся в списке отбора SELECT;
Ч ключевое слово ORDER BY нельзя использовать в подзапросах операторов INSERT, UPDATE, CREATE TABLE и CREATE VIEW;
Проиллюстрируем механизм сортировки на таблице, соз данной и заполненной предложениями:
CREATE TABLE (Atl NUMBER, INSERT INTO VALUES (1, INSERT INTO Tabl VALUES (1, INSERT INTO Tabl VALUES (2, INSERT INTO Tabl VALUES (3, INSERT INTO Tabl VALUES (3, Рассмотрим пример сортировки с использованием выражений 10 и At2, заданных в списке сортировки их положениями в списке SELECT.
SELECT FROM Tabl 2 ORDER 1 ASC, AT 10 A 20 В 30 С 30 A 49. Пример сортировки с использованием двух выражений в списке сортировки В любом случае на сортировку тратится значительное ко личество ресурсов. При использовании режима сортировки 5. № 1628.
Раздел обязательно надо что сортировка неявно произво дится при использовании агрегирующих функций. При указа нии ключевого слова DISTINCT также будет применяться сортировка для исключения повторяющихся кортежей, если только при этом не используется уникальный индекс.
Обратите внимание, в Oracle для любого запроса вует псевдостолбец который возвращает последо вательный номер строки в результирующем наборе и приме няется главным образом для ограничения числа строк, воз вращаемых запросом. Значение ROWNUM назначается до сортировки и после применения к результирующему набору сортировки теряет упорядоченность.
Иерархии Выдача на основании их иерархической упоря доченности осуществляется с использованием ключевых слов CONNECT BY и START WITH. Ключевое слово CONNECT BY определяет структуру иерархической связи. CONNECT BY определяет как строки, выбираемые в по рядке, так и отношение, используемое для объединения строк в иерархию (заданием ключевого слова PRIOR).
Иерархическое упорядочение задается использованием следующей синтаксической конструкции:
[START WITH условие] CONNECT BY условие Ключевое слово задаваемое в параметре условие после ключевого слова CONNECT BY, определяет порядок выборки элементов иерархии. Нисходящая выборка по иерар хии (от корня к листьям дерева) или восходящая (от листьев к корню дерева) определяется положением ключевого слова PRIOR относительно атрибутов, задающих отношение иерар хии. Количество уровней CONNECT BY ограничивается дос тупной пользователю памятью.
SQL Ч обработки данных Oracle Ключевое слово CONNECT BY нельзя использовать вме сте с подзапросами и объединениями.
Ключевое слово START WITH идентифицирует строки (или строку), будут использоваться в качестве корня дерева. Соответствующие строки (строка) определяются че рез условие, которому они должны удовлетворять. Отсутст вие ключевого слова START WITH означает начало выборки иерархий со всех столбцов, удовлетворяющих условию, за данному ключевым словом WHERE. В предложении START WITH допустимы В любом запросе автоматически определяется и может в нем использоваться псевдостолбец LEVEL, которому при сваивается значение 1 для корневого элемента иерархии, 2 Ч для вершин, непосредственно связанных с корневым элемен том и т. д.
Проиллюстрируем технику иерархической выборки на в которой атрибут задает номер вершины-роди теля, а атрибут задает номер вершины-потомка.
Пусть таблица создана и заполнена предложениями:
CREATE TABLE (Atl NUMBER, INSERT INTO Tabl VALUES (1, 2) INSERT INTO Tabl VALUES (1, 3) INSERT INTO Tabl VALUES (2, 4) INSERT INTO Tabl VALUES (3, 5) INSERT INTO Tabl VALUES (4, INSERT INTO Tabl VALUES 7) Рассмотрим запрос, в котором выборка осуществляется в направлении "родитель-потомок" (ключевое слово PRIOR справа от знака равенства). Выводимый псевдостолбец LEVEL показывает уровень вложения в иерархии. Корневая вершина имеет значение атрибута равное SQL> SELECT LEVEL, Atl, At2 FROM Tabl 2 CONNECT BY Atl = PRIOR At START WITH = LEVEL 1 1 2 2 3 4 1 1 2 3 3 5 Листинг 50. Пример иерархической выборки с правлением "родитель-потомок" (нисходящая Сопоставьте результат предыдущего запроса с результа том запроса, в котором выборка осуществляется в обратном направлении (ключевое слово PRIOR слева от знака равенст ва). Корневая вершина имеет значение атрибута At2 равное 7.
SQL> SELECT LEVEL, FROM 2 CONNECT BY PRIOR Atl = 3 START At2 = 7;
LEVEL AT 1 5 2 3 3 1 Листинг Пример иерархической с восхо дящим направлением выборки SQL Ч обработки Oracle Группирование и функции Для организации группирования отобранных данных с целью их совместной обработки используется ключевое сло во GROUP BY. Совместная обработка данных обычно сво дится к вычислению некоторой функции: суммы, среднего значения, числа элементов множества отобранных значений и т. п. Ключевое слово используется для формирова ния дополнительных условий включения групп в результи рующее множество.
Использование ключевого слова GROUP BY приводит к тому, что оператор SELECT выдает одну производную строку для каждой группы строк, формируемых на основе одинако вых значений для столбцов или выражений. Следует отме тить, что, как правило, все столбцы, которые указываются в конструкции GROUP BY и которым осуществляется груп пировка, должны присутствовать в списке после ключевого слова SELECT. В противном случае при выполнении запроса можно получить сообщение о том, что группирующая функ ция не является Синтаксис конструкции группирования строк:
GROUP выражение выражение ] [HAVING условие] Элемент выражение может быть атрибутом, константой или функцией от них.
Ключевое слово HAVING используется для уточнения, какие группы из GROUP BY будут включаться в оконча тельный результат. Предложения, содержащие ключевые сло ва BY и HAVING, обрабатываются Oracle следую образом:
1. Из рассмотрения удаляются * все строки, не удовлетворяющие условию WHERE;
2. Вычисляются и формируются группы в соответствии с предложением GROUP BY;
Из результирующего множества удаляются все группы, не удовлетворяющие условию HAVING.
Если присутствуют оба ключевых слова (GROUP BY и HAVING), они могут в любом порядке.
Проиллюстрируем использование ключевых слов GROUP BY и HAVING на таблице, созданной и заполненной предло жениями:
CREATE TABLE NUMBER, INSERT INTO Tabl INSERT INTO Tabl INSERT INTO Tabl INSERT INTO Tabl Следующие запросы демонстрируют использования группирования без дополнительных условий и с тельными условиями.
SQL> SELECT Atl, AVG(At2) "Среднее 2 FROM Tabl GROUP BY Atl;
Среднее 1 1. 2 3. SQL> SELECT Atl, "Среднее 2 FROM Tabl GROUP BY Atl HAVING AVG(At2) > 2;
Среднее 2 3. Листинг Примеры расчетов безусловного средне го и условного среднего SQL Ч Обратите внимание, что условие, заданное ключевым словом HAVING, должно относиться к сформированной ус ловием GROUP BY группе, а не к конкретным значениям ат рибута. Если условие отбора относится не к группе, а к атри бутам, то оно должно быть указано после ключевого слова WHERE. Приведенный пример иллюстрирует сказанное:
SELECT AVG(At2) "Среднее 2 FROM GROUP BY Atl HAVING > 1;
ERROR at line not a GROUP BY SELECT Atl, AVG(At2) "Среднее 2 FROM Tabl WHERE > 1 GROUP BY Atl;
Ч Среднее 1 2 2. Листинг 53. Примеры формирования условий, опреде ляющих группу для групповой функции Групповые функции возвращают результаты, вычислен ные по группе строк, которые сформированы запросом с предложением GROUP BY оператора SELECT.
При выполнении вычислений для групповых функций по умолчанию используется ключевое слово ALL, которое ука зывает, что результат включает все значения атрибута, в том числе и дублирующие. Если в запросе использовано ключевое слово DISTINCT, то групповые функции рассматривают только отличающиеся значения атрибутов или выражений.
Все групповые функции, кроме не учитывают в вычислениях атрибутов, имеющих неопределенное значения (NULL). Для замены неопределенного значения числовых атрибутов на 0 обычно используют встроенную функцию NVL.
Все примеры использования групповых функций бази руются на таблице, созданной и заполненной CREATE TABLE INSERT INTO Tabl INSERT INTO Tabl INSERT INTO Tabl INSERT INTO Tabl Функция вычисления среднего значения AVG возвращает среднее значение числового аргумента выражение, не вклю чая в вычисления значения NULL. Функция сле дующий синтаксис:
ALL] выражение) Для пояснения различий, связанных с включением или исключением конкретных значений атрибутов, рассмотрим следующие примеры.
SQL> SELECT FROM Tabl;
1. SQL> SELECT DISTINCT Atl) 2 FROM 1. SELECT 2 FROM SQL Ч обработки данных Oracle Примеры, иллюстрирующие учет включе ния в группу конкретных атрибутов f Функция вычисления суммы SUM возвращает сумму зна чений числовых атрибутов, не включая в вычисления значе ния NULL. Функция следующий синтаксис:
выражение) Функция вычисления дисперсии (стандартного уклоне ния) STDDEV возвращает дисперсию значений числовых ат рибутов, не включая в вычисления значения NULL. Функция использует следующий синтаксис:
[DISTINCT |. ALL] выражение) Функция VARIANCE вычисляет квадрат дисперсии зна чений числовых атрибутов, не включая в вычисления зна чения NULL. Функция использует следующий синтаксис:
| Приведенный ниже пример иллюстрирует применение статистических групповых функций:
SELECT SUM(ATl) STDDEV(ATl) 4.57735027. Пример вычисления груп повых функций Раздел Функция подсчета числа отобранных строк COUNT воз вращает количество выбранных строк. Особый вариант ис пользования функции возвращает число строк в таблице, включая дубликаты и атрибуты с неопределенными значениями. Функция использует следующий синтаксис:
ALL] выражение \ *) пояснения различий в подсчетах при использовании функции COUNT рассмотрим SQL> SELECT 2 FROM Tabl;
Листинг 56. Пример различий в вычислении функции числа строк Функция выбора наибольшего значения МАХ возвращает максимальное значение параметра. Функция использует сле дующий синтаксис:
| ALL] выражение) Функция выбора наименьшего значения возвращает минимальное значение параметра. Функция использует сле дующий | ALL] Для примеров использования групповых функций МАХ и MEM используем таблицу, созданную и заполненную предло жениями:
CREATE TABLE Tabl (Atl INSERT INTO Tabl VALUES SQL Ч язык данных INSERT INTO INSERT INTO Tabl Рассмотрим пример определения максимального и мини мального значения столбца.
SQL> SELECT FROM Tabl;
С 15-06- Листинг 57. Пример определения максимального и минимального значения столбца языка запросов В заключение рассмотрим полный синтаксис средства формирования запросов к базе данных. Оператор SELECT наилучшим образом демонстрирует изящество мощь средств выборки данных в реляционных системах управления базами данных.
[DISTINCT | ALL] { *| { | \ выражение [ [AS] ] } ] \ I снимка выражение [ ] } ] } FROM \ \ } } | } [ { \ \ } подзапроса) } [ ] [WHERE условие ] { { BY - выражение выражение ] условие ] } WITH CONNECT BY.
| UNION ALL | INTERSECT | ELECT ] [ORDER BY { выражение \ положение \ } [ASC I DESC] выражение I положение \ ] [ASC |...] [FOR UPDATE [OF |.
]]. [, | Описание полного синтаксиса оператора выборки достаточно велико. Поэтому будем рассматривать сокращен ное множество конструкций оператора SELECT. Полное опи сание синтаксиса доступно в соответствующей части доку ментации SQL Reference.
Фрагменты предложения SELECT должны быть записа ны в указанном порядке. Указание ключевого слова приводит к устранению из отобранных данных повторяющихся строк. Указание ключевого слова ALL при водит к предъявлению всех отобранных данных, включая по вторяющиеся строки. По используется значение ALL.
Наличие параметра * (звездочка) означает выбор столбцов из всех таблиц, представлений и снимков, указан ных в перечне значений ключевого слова FROM.
Конкретный выбор значения параметра \ означает выбор всех столбцов из таблицы, представления или снимка. Необяза тельный параметр используется для уточнения имени схемы, в которой находится соответствующий объект.
По умолчанию используется схема пользователя, выполняю щего запрос.
SQL Ч язык обработки данных Oracle Параметр выражение заменяется на выра жение, которое обычно базируется на данных столбцов из таблиц, представлений и снимков, указанных в перечне зна чений ключевого слова FROM.
Параметр задает альтер нативное имя столбца или выражения для формирования за головка при выводе ответа на запрос. Заданное значение па также использоваться в выражении ORDER BY.
Ключевое слово FROM определяет таблицы, представле ния или снимки, из которых будут данные.
Параметр устанавливает имя связи уда ленной базой данных. Если имя связи с удаленной базой дан ных не указано, предполагается, что соответствующий объект расположен в основной базе данных.
Параметр задает подзапрос, который в данном контексте рассматривается так же, как представление.
Параметр задает альтер нативное, обычно короткое, имя, которое в контексте данного запроса является обязательным для именем соответ ствующей таблицы, представления или снимка.
Ключевое слово WHERE определяет логическое условие отбора данных. Если ключевое слово WHERE опущено, то возвращается декартово произведение всех представ лений и снимков, указанных в перечне значений ключевого слова FROM.
Ключевые слова GROUP BY и HAVING для формирования некоторой обобщающей информации о группах строк, имеющих определенные значения в одном нескольких полях, описываемые параметрами выражение и условие. Для каждой группы строк, формируемой предложе нием GROUP BY выражение, создается одна строка произ водных данных. Множество групп может быть уточнено ло гическим условием отбора, определяемым предложением HAVING.
Ключевые слова START WITH и CONNECT BY задают иерархический порядок отбора данных запроса. Конкретная иерархическая упорядоченность задается параметрами усло вие.
Ключевые слова UNION, ALL, MINUS задают теоретико-множественные операции объеди нения результатов нескольких запросов, сформированных соответствии со значением параметра Для объединенных результатов не допускается использование конструкции FOR UPDATE.
Ключевое слово ORDER BY определяет порядок, в кото ром будут выдаваться строки результирующего отношения.
Параметр выражение определяет значение, по которому вы полняется сортировка. Базис сортировки может также быть указан параметром положение, то есть порядковым номером в списке вывода (задаваемом после ключевого слова SELECT). По умолчанию используется сортировка по возрас танию (ASC).
Конструкция FOR UPDATE определяет необходимость блокировки отобранных строк. Необязательное ключевое слово OF уточняет перечень таблиц или представлений, дан ные из которых должны быть заблокированы.
Необязательное ключевое слово указывает на то, что в случае, если требуемые для блокировки строки не доступны (то есть заблокированы другим процессом), воз вращается сообщение об ошибке (как правило, ORA-00054).
Если ключевое слово NOWAIT не указано, то запроса будет тех пор, пока не будут ос вобождены все требуемые для блокировки строки.
SQL Ч язык данных Oracle с удаленными базами Снимки данных Создание связей с удаленной базой данных Oracle Для создания связи с удаленной базой данных использу ется SQL-оператор CREATE DATABASE LINK. Поддержи ваются связи как с удаленными базами данных под управле нием Oracle, так и с базами данных некоторых других произ водителей, например DB2 фирмы IBM. Естественно, что и на локальной, и на удаленной базе данных должно быть уста новлено специальное программное обеспечение.
Для создания связи с удаленной базой данных димо иметь учетную запись в удаленной базе данных. Для этой учетной записи имя пользователя может совпадать с именем пользователя, который создает связь, а может и раз личаться.
Оператор создания связи с удаленной базой данных Oracle использует следующий синтаксис:
CREATE [PUBLIC] DATABASE [CONNECT TO IDENTIFIED BY USING Если ключевое слово PUBLIC опущено, создается связь, доступная только создавшему ее пользователю. Если же оно указано, связь становится доступной всем пользователям.
Параметр задает имя, которое дается созда ваемой связи. Параметры имя задают имя пользователя и пароль в удаленной базе данных. Параметр определяет имя за дающего спецификации связи с удаленной базой данных.
Создав связь с удаленной базой данных, можно обра щаться к таблицам удаленной базы в запросах, ссылаясь на них во фразе FROM с тем же эффектом, что и при прямом подключении к удаленной базе данных. Если в предложении создания связи конструкция, содержащая имя пользователя и пароль, отсутствует, будут использоваться имя и пароль те кущего пользователя. Для доступа к объектам удаленной ба зы данных к их именам добавляется имя связи (@имя_связи).
Запросы, выполняемые с использованием связи с удален ной базой данных, подчиняются следующим ограничениям:
Ч максимальное количество связей с удаленными база ми, которые можно использовать в одном запросе, ется значением параметра файла параметров;
Ч использование команд языка дан ными INSERT, DELETE, UPDATE требует наличия установки для сервера Oracle возможностей, реализуемых компонентой Distributed Options.
Рассмотрим пример определения связи ко торая использует учетную запись пользователя ul с паролем в удаленной базе данных, определяемой строкой связи sunora. После успешного создания связи с удаленной базой данных можно выполнить запрос к ее таблицам, как показано в приведенном примере:
CREATE DATABASE LINK 2 CONNECT TO ul IDENTIFIED BY ulpsw 3 USING Database link created.
SELECT * FROM Листинг создания связи с удаленной базой данных и запроса с использованием со зданной связи SQL Ч обработки данных Oracle Для того чтобы скрыть от пользователя что таблица пользователя находится в удаленной базе данных, использовать синоним. Приведенный в листинге пример иллюстрирует данный метод.
CREATE SYNONYM suntabl 2 FOR Synonym created.
SQL> SELECT * FROM suntabl;
59. Протокол создания синонима для ной поддержки связи с удаленной базой дан ных и запроса с его использованием Для удаления связи с удаленной базой данных использу команда DROP DATABASE Для выполнения этой команды необходимо либо быть владельцем связи с удален ной базой данных, либо иметь привилегию DROP ANY DA TABASE LINK. Оператор уничтожения связи с удаленной базой данных Oracle использует следующий синтаксис:
DROP [PUBLIC] LINK Параметр PUBLIC должен быть определен для удаления общей связи с удаленной базой данных. Параметр задает имя удаляемой связи. Рассмотрим пример отмены (удаления) связи с удаленной базой данных по имени sun link.
SQL> DROP DATABASE link Database link dropped.
Листинг 60. Пример, удаления с удаленной ба зой данных Средства определения и Снимок Ч это поименованная динамически поддержи ваемая сервером выборка из одной или нескольких таблиц или представлений, обычно размещенных на удаленной базе данных. Сервер гарантирует актуальность снимка в рамках принятой технологии: а именно, формирование снимка (мате риализация соответствующего запроса) производится в соот ветствии с некоторым расписанием. Используя снимки, адми нистратор безопасности обеспечивает доступ пользователям к тем частям удаленных баз данных, которые реально необхо димы для выполнения их Для того чтобы механизм снимков работал, на серверах локальной и удаленной баз данных должен быть установлен пакет DBMS_SNAPSHOT, в котором находятся процедуры, выполняющие обновление снимков. Для серверов Oracle с Procedural Option такой пакет устанавливается автоматически, для остальных версий поставки для создания пакета необхо димо, чтобы пользователь SYS выполнил сценарии dbmssnap.sql и Для создания снимков, исполь таблицы и представления удаленной базы данных, необходимо, чтобы сервер был установлен с дополнительны ми возможностями, реализуемыми компонентой Distributed Option.
Оператор определения снимков Oracle использует дующий синтаксис:
CREATE SNAPSHOT целое | PCTUSED целое | целое I целое | SQL Ч данных Oracle STORAGE } ] [CLUSTER ( ] [USING INDEX] целое \ PCTUSED целое | INITRANS целое \ целое I TABLESPACE STORAGE ] [REFRESH I COMPLETE | FORCE [START WITH дата_1 } [NEXT ] ] [FOR UPDATE] AS запрос Ключевые слова PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, STORAGE характеризуют про странство, распределяемое при работе со Ключевое слово PCTFREE определяет процент блоков, резервируемых для дальнейшей модификации данных табли цы. Допустимые значения от 0 до 99. Значение по умолчанию 10, то есть если данный параметр не указан, то при заполне нии каждого блока 10% пространства остается не использо ванным для записи в него данных для выполнения в даль нейшем модификации строк снимка.
Ключевое слово PCTUSED определяет минимальный процент использования пространства блока, при котором в него вводятся данные. Допустимые значения от 1 до 99. Зна чение по умолчанию 40, то есть, если в блоке занято менее 40% пространства, в него вводятся данные при выполнении операции вставки. Сумма значений параметров PCTFREE и PCTUSED не должна превышать 100.
Ключевое слово INITRANS определяет начальное число параллельных транзакций, которые могут выполняться для модификации данных блока. Значение по Клю чевое слово MAXTRANS определяет максимальное число параллельных транзакций, которые могут выполняться для модификации данных блока. В случаев явное задание этих параметров не Ключевое слово TABLESPACE определяет таблич ной области, в которой будет размещена таблица. Если значе Раздел ние параметра не определено, то таблица размещается в таб личной области, заданной по умолчанию для пользователя, который является владельцем схемы, содержащей снимок.
Ключевое слово STORAGE определяет объем внешней памяти, выделяемый под снимок. Для больших снимков целе сообразно явно выделять требуемую память для уменьшения запросов на динамическое выделение пространства.
Ключевое слово CLUSTER указывает привязку столбцов снимка к кластеру. Обычно столбцы кластера образуют из элементов первичного ключа базовых таблиц.
Ключевое слово USING INDEX определяет создание ин декса для уменьшения времени доступа к данным снимка.
При этом ключевые слова PCTFREE, PCTUSED, MAXTRANS, TABLESPACE, STORAGE характеризуют про странство, распределяемое для индекса и имеют тот же смысл, что и соответствующие параметры, характеризую щие пространство самого снимка.
Ключевое слово REFRESH определяет технологию об новления снимка. Если задан параметр COMPLETE, то для обновления данных снимка заново выполняется запрос, фор мирующий данные снимка. Если задан параметр FAST (быст рое обновление), то для обновления данных снимка исполь зуется информация об измененных данных в мастер-таблице, хранящаяся в журнальном файле снимка. При используемом по умолчанию параметре FORCE, решение о технологии об новления снимка принимается системой (обычно это быстрое обновление).
Ключевое START WITH определяет с помощью параметра дату первого автоматического обновления снимка. Естественно, параметр должен быть выраже нием типа дата. Ключевое слово NEXT определяет с помо щью параметра интервал между автоматическими обновлениями снимка. Обратите внимание на то, что, если ключевое слово REFRESH опущено, то автоматического об новления данных снимка не происходит. Также не происхо SQL Ч обработки данных Oracle дит автоматического обновления данных снимка, если опу щены оба ключевых слова START WITH и NEXT.
Ключевое слово FOR UPDATE указывает на возможность изменения данных снимка. Если Oracle включает под держку репликации данных (Replication Option), то модифи кация данных снимка приводит к соответствующим измене ниям в мастер-таблице.
Ключевое слово AS запрос включает в создаваемый сни мок строки, являющиеся результатом выполнения запроса.
Параметр запрос используется для обозначения любого синтаксически правильного запроса, не содержащего ключе вого слова ORDER BY или ключевого слова FOR UPDATE.
Рассмотрим пример создания снимка с таблицы, разме щенной на удаленном сервере. Пусть таблица размеще на на удаленном сервере и доступ к ней осуществляется через связь с именем Приводимый пример показывает, как создается снимок, и иллюстрирует тот факт, что измене ния в мастер-таблице не распространяются на данные снимка автоматически.
SQL> SNAPSHOT snap_suntabl AS 2 SELECT.* FROM Tabl@sun_ora_link WHERE Snapshot SELECT * FROM Г SQL> CONNECT Connected.
SQL> INSERT INTO 1 row created.
DELETE FROM Tabl WHERE row deleted.
CONNECT SELECT * FROM Листинг 61. Пример создания снимка таблицы уда ленной базы данных Для модификации снимка с целью установки частоты ав томатического изменения в 1 час можно воспользоваться ко мандой ALTER SNAPSHOT. После того как введением клю чевого слова REFRESH снимок сделан обновляемым авто матически, изменения, введенные в мастер-таблицу в пре дыдущем примере, актуализируются в снимке ALTER SNAPSHOT snap_suntabl REFRESH COMPLETE 2 START WITH SYSDATE NEXT SYSDATE + Snapshot altered.
SQL> SELECT * FROM Листинг 62. Пример автоматического изменения снимка данных с заданным временным лом SQL Ч данных Oracle последовательностей называется объект базы данных, генерирующий неповторяющиеся целые числа. Полученные из последовательности числа обычно используются в качест ве значений для первичных ключей.
Числа, создаваемые последовательностью, могут либо возрастать постоянно, либо только до определенного предела, либо, по достижении предела, начинать возрастание заново, с значения. Последовательность может создавать цепочки как увеличивающихся чисел, так и уменьшающихся.
Можно задавать также и приращение значений.
Псевдостолбец NEXTVAL используется для генериро вания очередного номера из указанной последовательности.
Ссылка на NEXTVAL приводит к генерированию очередного номера. Обращение имеет следующий синтаксис:
Псевдостолбец используется для ссылки на текущее значение последовательного номера. В текущем се ансе NEXTVAL должен быть использован хотя бы один раз до ссылки на CURRVAL. Обращение к имеет сле дующий синтаксис:
. ' На одно предложение SQL лишь один но вый номер;
иными словами, если в данном предложении псевдостолбец NEXTVAL применительно к одной и той же последовательности встречается несколько раз, то лишь для первого обращения будет возвращен новый номер послёдова тельности, а все остальные обращения в этом же предложе нии возвратят тот же самый номер.
Для создания последовательности требуется привилегия CREATE SEQUENCE. Для создания последовательности, размещенной в схеме другого пользователя, необходима при вилегия CREATE ANY SEQUENCE.
Оператор определения последовательности Oracle ис пользует следующий синтаксис:
CREATE SEQUENCE ] BY приращение] [START WITH \ \ NOMINVALUE] | NOCYCLE] [CACHE \ NOCACHE] [ORDER | Параметр последовательности задает имя вательности. Параметр указывает на схему, в ко торой определяется последовательность. Если владелец по следовательности не указан явно, то подразумевается пользо ватель, выдавший команду CREATE SEQUENCE.
Ключевое слово INCREMENT BY определяет интервал между последовательными номерами. Если параметр прира щение имеет отрицательное значение, то последовательность убывающая, если положительное Ч последовательность воз растающая. Допустимо любое целое число, не равное нулю.
Значение по умолчанию 1 (возрастающая последовательность значений).
Ключевое слово START WITH через параметр началь задает первый генерируемый последовательный номер. Если ключевое слово не указано, то по умолчанию для возрастающих последовательностей начальный генерируе мый последовательный номер равен значению параметра" SQL Ч обработки Oracle а для убывающих последовательностей Ч Ключевое слово MAXVALUE через параметр задает максимальное значение последователь ного номера, которое будет генерироваться. Параметр наи определяет верхнюю границу последо вательности, которая может быть любым целым числом, количеством знаков, не превышающим 28 цифр и большим, чем параметры и (если они заданы). Отсутствие верхней границы указывается ключевым словом NOMAXVALUE, которое определяет для убывающих последовательностей значение а для возраста ющих последовательностей Ключевое слово MINVALUE через параметр задает минимальное значение последова тельного будет генерироваться. Параметр определяет нижнюю границу последо вательности, которая может быть любым целым числом, с количеством знаков, не превышающим 28 цифр, меньшим, чем параметры и (если значение параметров задано). Отсутствие нижней гра ницы указывается ключевым словом NOMINVALUE, которое определяет для убывающих последовательностей значение а для возрастающих последовательностей значение Ключевое слово является значением, исполь зуемым по умолчанию, и предполагает завершение гене рирования последовательных номеров по достижении конца последовательности. Любая попытка получить очередной элемент последовательности после этого приведет к ошибке.
Если при определении последовательности указан параметр CYCLE, то после достижения очередным последова тельности значения параметра (для воз растающих последовательностей) выдается значение пара метра Если параметры и не указаны, то исполь зуются их значения по умолчанию. Для убывающих последо Раздел параметры меняются местами по смыслу поня тия убывающей последовательности. Обратите внимание, что ключевое слово START WITH влияет только на первый эле мент последовательности. При возврате на начало последо вательность будет начинаться с параметра в слу чае возрастающей последовательности и параметра MAXVALUE Ч в случае убывающей.
Ключевое слово CACHE указывает на использование тех ники предварительной подготовки элементов последова тельности, что обеспечивает их быстрое получение при запро се. Число последовательных номеров, хранящихся в области кэша оперативной памяти, определяется параметром ключевого слова CACHE. Кэширование по следовательности обеспечивают более быструю генерацию элементов последовательности. Заполнение кэша для каждой данной последовательности происходит после запроса перво го элемента этой последовательности. В случае краха сис темы все последовательные номера, не исполь зованные в зафиксированных транзакциях, теряются. По умолчанию предполагается, что в памяти будут кэшироваться 20 последовательных элементов для каждой последо вательности. Значение параметра ключе вого слова CACHE не должно превышать разницы парамет рами, задаваемыми ключевыми словами MAXVALUE и MINVALUE.
Ключевое слово ORDER обеспечивает генерацию после довательных элементов точно в порядке поступления запро сов. В большинстве случаев независимо от того, указано ли ключевое слово ORDER, элементы последовательности в порядке поступления запросов.
Одна последовательность может использоваться для нерации первичных ключей для нескольких таблиц. Если два пользователя одновременно обращаются к одной после довательности, номера для каждого пользователя иметь промежутки, так как из непрерывной последовательности по переменно получают номера оба пользователя. Каждый из Х SQL Ч язык обработки данных Oracle двух пользователей не будет видеть последовательные номе ра, сгенерированные для другого пользователя. При генера ции элемента последовательности счетчик изменя ется независимо от того, успешно или неуспешно завершена транзакция.
Рассмотрим пример создания последовательности с име нем Начальный элемент последовательности определен равным 2, параметры и определены равными 3 и 1 соответственно.
SQL> CREATE SEQUENCE Seql 2 1 START WITH Sequence created.
SQL> SELECT dual;
NEXTVAL SQL> SELECT FROM dual;
NEXTVAL SQL> SELECT FROM dual;
ERROR:
sequence exceeds MAXVALUE and cannot be no rows selected Листинг Пример создания последовательности выборки ее элементов Изменим предыдущий пример и создадим тельность с именем Seq2.
Начальный элемент последовательности, параметры наи и определены, как в предыдущем примере, но дополнительно определим параметр цикла, равный 2, и параметр кэширования, равный 2, (если таким образом не определить параметр кэширования, то про изойдет ошибка, так как требуется, чтобы параметра ключевого слова CACHE не превышало разности параметрами, задаваемыми ключевыми сло вами MAXVALUE и CREATE SEQUENCE Seq 2 MINVALUE 1 START WITH 2 CYCLE CACHE 2;
Sequence created.
SQL> SELECT Seq2.NEXTVAL NEXTVAL SELECT FROM NEXTVAL SQL> SELECT FROM NEXTVAL Листинг 64. Пример создания последовательности с образованием элементов Для удаления последовательностей используется команда DROP SEQUENCE. Для выполнения данной операции необ ходимо быть владельцем либо иметь привилегию DROP ANY SEQUENCE.
SQL Ч обработки данных Oracle Оператор удаления последовательностей Oracle исполь зует следующий синтаксис:
DROP SEQUENCE Одной из ситуаций, когда необходимо уничтожение по следовательности, является повторный старт последователь ности. При этом необходимо учитывать, что объекты, кото рые зависят от этой последовательности, станут непригодны ми для использования. Поэтому для приведения последова тельности к требуемому состоянию (сдвига на требуемое чис ло элементов) рекомендуется использовать более изощрен ные способы. Например, с помощью анонимного PL/SQL блока требуемое число раз вычислить следующее значение для циклической последовательности.
Пример уничтожения последовательности приведен ниже.
SQL> DROP SEQUENCE Seql;
Sequence dropped.
Листинг 65. Пример уничтожения последовательности синонимов в Oracle Синоним Ч это объект базы данных, используемый для альтернативного именования. Обычно синонимы создаются для таблиц, представлений, последовательностей для общего использования (без указания префикса схемы) или для скры той ссылки на удаленную базу данных. Наличие синонимов позволяет приложениям обеспечивать независимость от того, в какой схеме размещена таблица или представление, ка кой конкретно локальной базе данных систе мы хранятся необходимые данные.
Оператор определения синонима Oracle использует сле дующий синтаксис:
CREATE [PUBLIC] SYNONYM FOR [ ] объекта связиБД] Ключевое PUBLIC определяет, что синоним будет доступен всем пользователям. По умолчанию синоним досту пен только создавшему его пользователю.
Параметр имя синонима Ч имя синонима, следующее глашениям по именованию объектов Oracle. Параметр задает имя существующей в базе данных схемы.
Для создания синонима в произвольной схеме нужны соответ ствующие привилегии. Если имя схемы для синонима опу щено, то предполагается, что синоним создается в схеме пользователя, выдавшего команду.
Параметр указывает на существующую связь к удаленной базе данных. Если параметр опущен, синоним ссылается к объекту, принадлежащему пользователю, определенному связью с удаленной базой дан ных.
Параметр задает имя схемы, в которой нахо дится объект, для которого создается синоним. Наконец, па раметр указывает на этот объект.
Синоним должен иметь имя, отличное от остальных объ ектов данного пользователя. Рассмотрим соответствующий Пусть в таблице пользователя Administrator на ходится информация, необходимая для замены некоторых кодов на действительные наименования некоторых объектов.
Для пользователя заводится одноименный синоним, упро щающий обращение к требуемой таблице (конечно, одно именность не является обязательным требованием). Наличие необходимых привилегий у пользователя, создающего сино ним, предполагается. Обратите внимание на явного разрешения пользователю выполнять операцию SQL Ч язык обработки данных Oracle выборки из таблицы (даже несмотря на то, что она скрыта за синонимом). Пусть таблица RefCodes создана и за полнена пользователем Administrator с использованием сле дующих CREATE TABLE NUMBER, INSERT INTO INSERT INTO RefCodes Пользователь создает синоним для пользовате ля ссылаясь на таблицу RefCodes из схемы пользователя Administrator. При этом, несмотря на правильность синонима, объект, на который ссылается синоним, пользователю не доступен.
CONNECT Connected.
CREATE SYNONYM 2 FOR created.
SQL> CONNECT Connected.
SQL> SELECT * FROM RefCodes;
SELECT * FROM REFCODES ERROR at -line table or view does not exist Листинг 66. Пример создания синонима После предоставления прав на выполнение операции вы борки пользователь может оперировать с объектом, скры тым за синонимом.
SQL> CONNECT Connected.
GRANT SELECT ON RefCodes TO Grant succeeded.
CONNECT SELECT *.
AT 2 Text Пример создания синонима и установле ния доступа к объекту, за синонимом Для удаления из базы данных синонима используется ко манда DROP SYNONYM. Для удаления синонима необходи мо быть его владельцем или иметь привилегию DROP ANY SYNONYM. Для удаления общего синонима также не обходимо быть его владельцем или иметь привилегию DROP ANY PUBLIC SYNONYM. Оператор удаления синонима Oracle использует следующий синтаксис:
DROP [PUBLIC] SYNONYM Необязательное ключевое слово PUBLIC определяет факт удаления общего синонима. Параметр опреде ляет имя удаляемого синонима. Изменить синоним можно, его, и, назначив заново.
SQL> DROP Synonym dropped.
Пример удаления синонима SQL Ч язык обработки данных Oracle с табличными в Oracle Оператор определения табличной области Oracle исполь зует следующий CREATE TABLESPACE [SIZE целое_число [AUTOEXTEND | ON [NEXT,...] STORAGE ] | | Параметр определяет имя со здаваемой табличной области. Параметр определяет файла операционной системы, в котором будут размещаться данные создаваемой табличной области.
Ключевое слово SIZE определяет первично распределяе мое пространство под табличную область, задаваемое в бай тах, килобайтах (указан параметр К) или в мегабайтах (указан параметр М). файл с именем, определенным параметром не су ществует, то указывать ключевое слово SIZE с параметром, характеризующим выделяемую память, обязательно.
Ключевое слово AUTOEXTEND указывает на разреше ние (ON) или запрещение (OFF) автоматического расширения пространства, выделенного для табличной области. Ключевое слово задает приращение задаваемое в байтах, килобайтах (указан параметр К) или в мегабайтах (указан параметр М). Дополнительно можно определить с помощью ключевого слова MAXSIZE максимальный размер выделяемого под табличную область пространства, задавае мый в байтах, килобайтах (указан параметр К) или в мегабай тах параметр М). Если указано ключевое слово 6. Заказ № 1628.
Раадел UNLIMITED, то размер пространства, выделяемого под таб личную область, не имеет логических ограничений.
Задаваемое умолчанию значение ключевого слова ONLINE обеспечивает автоматический перевод в оператив ный режим созданной табличной области. Задаваемое по умолчанию значение ключевого слова PERMANENT опреде ляет создание постоянной табличной области.
Рассмотрим пример создания дополнительной табличной области app_data, данные которой размещаются в файле (в файловом ве операционной системы SUN Solaris). Под файл для таб личной области резервируется один мегабайт дискового про странства и допускается автоматическое приращение фраг ментами по одному мегабайту. Дополнительно представлен пример создания табличной области с другими ха рактеристиками.
SQL> CREATE TABLESPACE app_data 3 SIZE 1M AUTOEXTEND ON NEXT 4 UNLIMITED;
Tablespace created.
SQL> CREATE TABLESPACE 3 500K AUTOEXTEND ON NEXT 1M MAXSIZE Tablespace created.
Листинг 69. Примеры создания табличных областей Для изменения параметров табличных областей исполь зуется оператор ALTER TABLESPACE. С его помощью мож но выполнить для табличной области следующие изменения:
добавить файлы данных;
переименовать файлы данных;
из менить параметры хранения по умолчанию;
перевести таб личную область в автономный или оперативный режим;
на SQL Ч обработки данных Oracle чать или завершить копирование;
разрешить или запретить запись в табличную область. В качестве примера запретим запись в табличную область app_data. Эта операция использу ется, скажем, для защиты от изменений в нерабочее время. Установленные режимы табличных областей можно просмотреть в представлении словаря данных DBA TABLESPACES.
ALTER app_data READ Tablespace altered.
Листинг 70. Пример защиты табличной области от записи Удаление табличной области с возможным удалением всех объектов базы данных, расположенных в данной таблич ной области, осуществляется предложением DROP TABLESPACE. Для выполнения данной операции необходи мо обладать привилегией DROP TABLESPACE. Табличная область SYSTEM не может быть удалена.
Оператор удаления табличной области Oracle использует следующий синтаксис:
DROP TABLESPACE [INCLUDING CONTENTS] Конструкция INCLUDING CONTENTS определяет, что табличная область должна быть удалена, даже если она со держит данные. Если данный параметр опущен и табличная область не содержит данных, она удаляется. Если же при опущенном ключевом слове INCLUDING CONTENTS таб личная область содержит данные, то возвращается сообщение об ошибке и табличная область не удаляется.
Перед удалением табличную область рекомендуется пе ревести в автономный режим (OFFLINE). Рекомендация свя зана с тем, что до тех пор, пока пользователи работают с объ ектами табличной области, она не может быть удалена.
Ниже приведен пример удаления табличной области app_data вместе со всеми содержащимися в ней объектами.
SQL> DROP TABLESPACE INCLUDING CONTENTS;
Tablespace dropped.
Листинг Пример удаления табличной области Ч процедурное расширение SQL PL/SQL Ч это процедурное расширение Oracle стандарт ного языка SQL. Многие фирмы-производители программных продуктов стремятся расширить и усовершенствовать воз можности стандартного языка SQL, поэтому почти каждый сервер реляционной базы данных поддерживает какое-либо расширение стандартных возможностей SQL. Рас ширения SQL обеспечивают пользователю расширение спек тра решаемых задач, повышение эффективности или упроще ние типовых действий с базой данных. Моделью PL/SQL служил язык программирования поэтому PL/SQL обла дает набором средств, характерных для любого современного языка программирования высокого уровня.
Программы, созданные на языке PL/SQL, могут работать совместно в различных частях прикладной системы, постро енной с использованием технологий Oracle. Например, в при ложении, разработанном на Oracle Developer 2000, триггер формы (на стороне клиента) может вызывать для выполнения некоторого действия хранимую процедуру (на стороне серве ра).
Знакомство с PL/SQL необходимо каждому разработчику приложений для С помощью PL/SQL можно улучшить производительность разрабатываемого приложения и систе мы целом. Вместо интерпретируемых операторов SQL Oracle позволяет использовать предварительно скомпилиро ванные и, следовательно, быстро выполняющиеся програм мы. Используя PL/SQL, также можно значительно уменьшить объем обработки в клиентской части приложения и нагрузку на сеть. Например, может понадобиться выполнить различ ные наборы операторов SQL в зависимости от результата не которого запроса. Запрос, последующие операторы и операторы условного управления могут быть включены в один блок PL/SQL и пересланы серверу за одно обращение к сети. С различными версиями сервера Oracle поставляются различные версии языка в которых поддерживаются или не поддерживаются те или иные механизмы. В появились новые возможности, в частности поддержка объ ектных расширений в программах на PL/SQL.
В этом разделе будут рассмотрены следующие вопросы:
структура программы PL/SQL, константы и ти пы данных PL/SQL, операторы управления выполнением про граммы и исключительных ситуаций, различные виды программ PL/SQL: хранимые процедуры, функции, па кеты и триггеры.
Структура на Программа на PL/SQL обычно состоит из трех блоков:
блока описаний, исполнительного блока и блока обработки исключительных ситуаций. Исполнительный блок может структурирован с использованием операторных скобок BEGIN END.
Синтаксически программа на PL/SQL оформляется сле дующим образом:
DECLARE операторы BEGIN операторы...
Ч процедурное SQL EXCEPTION опера торы END;
Программа такого вида называется анонимным блоком. В блоке DECLARE описываются переменные, константы и оп ределяемые пользователем типы данных. Первый оператор BEGIN отмечает начало тела основной программы. В тело программы могут быть вложены другие блоки, ограниченные операторными скобками BEGIN и END. В блоке EXCEPTION определяются фрагменты программного кода для обработки исключительных ситуаций в программе. Последний оператор END указывает конец тела программы. В анонимном блоке могут отсутствовать блоки DECLARE и EXCEPTION, но обя зательно должен присутствовать блок операторов, ограни ченный операторными скобками BEGIN и END. В вырожден ном случае там может присутствовать только оператор NULL.
В любые части программы на PL/SQL можно включать комментарии. Текст, который начинается с символов и продолжается до конца текущей строки, рассматривается как комментарий. Многострочные комментарии включаются жду символами и Использование комментариев яв ляется хорошей практикой составления программ. Также хо рошей практикой программирования является придание тек сту программ удобочитаемости с помощью выделения син таксических конструкций языка двумя-тремя отступами или использование для форматирования кода специальных средств, например Software.
Перед блоком могут присутствовать команды установки переменных окружения для различных инструмен тальных средств.
и В ограниченном операторами DECLARE и блоке программы PL/SQL описываются переменные, типы и кон Любая или константа должна иметь один из допустимых в PL/SQL типов. Константа идентифицируется ключевым CONSTANT и отличается от тем, что попытка изменить ее значение приводит к сообще нию об ошибке. Присваивание значений переменным осуще ствляется оператором Типы данных в PL/SQL практиче ски совпадают с описанными выше типами данных SQL за исключением некоторых несущественных различий (данные могут иметь отличающуюся максимальную длину, различия в реализации, имеется специфические для PL/SQL типы данных и т. д.). Наряду со ска лярными типами данных в PL/SQL присутствуют составные:
тип RECORD, массивы и Составной тип данных RECORD предназначен хра нения и обработки записей. Каждая запись имеет атрибуты, которые могут быть при Обращение к атрибутам записи производится с использова нием полной нотации, включающей имя переменной и имя отделенное точкой. В переменные типа RECORD удобно выполнять выборку данных с помощью курсоров.
PL/SQL-таблицы и способы работы с ними будут рассмотре ны далее.
Рассмотрим пример простейшей программы, в которой определяются переменные и выполняются действия по вы числению натурального логарифма чисел 2 и 3. Команды ус тановки переменных окружения SET SERVEROUTPUT и SET ECHO определяют режим на терминал пользователя.
Процедура обеспечивает вывод данных на терминал пользователя и позже будет рассмотрена подробно, функция LN вычисляет натуральный логарифм.
Символ указывает на завершение текста анонимного блока и является командой к интерпретации и выполнению.
set SQL> set echo on;
Ч языка SQL SQL> DECLARE 2 CONSTANT двух равен 3 CONSTANT трех равен 4 Arg NUMBER := 2;
Ч здесь задается начальное значение аргумента 5 Ч Исполнительный блок 6 BEGIN. 8 Arg 10 END;
/ Логарифм Логарифм трех равен PL/SQL procedure successfully completed.
Листинг Пример программы на PL/SQL, вычисляю щей логарифмы чисел Управление выполнением программы Операторы большинства языков программирования, в том числе и PL/SQL, выполняются последовательно. Такая схема называется потоком команд. В любом развитом языке присутствует более или менее богатый набор операторов управления потоком Соответствующие операторы позволяют выполнять условные переходы, циклически вы полнять группу операторов и осуществлять выход из цикла при выполнении определенных условий. В PL/SQL преду смотрено несколько операторов, с помощью которых можно управлять выполнением потока команд программы. Рассмот рим соответствующие программные конструкции.
ветвления Оператор позволяет проверить условие и, в зависимости от результатов проверки (TRUE или FALSE), выполнить различные группы операторов. Альтер нативная последовательность операторов определяется клю чевым словом ELSE. Границы действия оператора IF опреде ляются закрывающей операторной скобкой END IF. Для расширения структуры ветвления дополнительно предусмотрены операторные скобки ELSIF, задающие структуры ветвления более глубокого уровня.
Oracle использует следующий синтаксис конструкции ветвления в PL/SQL:
* - Х IF THEN - условие_2 THEN Ч ветвь ELSIF ELSE -- операторы альтернативы END IF;
Рассмотрим пример, иллюстрирующий механизм ветвле ния в программах на PL/SQL. Программа выводит сообщение о классе излучения в зависимости от значения вводимого па раметра длины волны (длина волны заданной микронах). Для ввода данных используется стандартное со глашение SQL*Plus: переменная, имя которой предваряется знаком вводится с терминала пользователя.
SQL> DECLARE 2 NUMBER;
Ч Длина волны 4 Text2 VARCHAR2 (30.) свет ' 5 := 6 Ч Исполнительный блок 7 BEGIN PL/SQL Ч процедурное расширение языка SQL 8 := 10 IF (Lamda > 0.65) 11 THEN 12 (Lamda < 13 THEN 14 ELSE 16 END IF;
/ Enter value for 0. old 8: Lamda := new Lamda Ультрафиолет PL/SQL procedure successfully completed.
73. Пример программы на PL/SQL, исполь зующей ветвление цикла Организация цикла оформляется в программе на PL/SQL несколькими способами. Самый простой Ч использование оператора LOOP. Выйти из цикла можно несколькими спосо бами. Конструкция EXIT WHEN обеспечивает выход из ла при выполнении условия в соответствующем операторе.
Рассмотрим пример определения числа, факториал которого является наименьшим числом, большим заданной константы (например, 1 000 000 000).
SQL> DECLARE 2 Arg Перем. для факториала 3 I NUMBER;
Ч Переменная-счетчик 4 Limit NUMBER := 1000000000;
Ч Граница 5 VARCHAR2 := числа, впервые превышающий 1 000 000 000 ' Ч Исполнительный блок 1 BEGIN 8 I 0;
9 Arg := 1;
10 LOOP 11 EXIT WHEN ARG > Limit;
12 Arg + 13 I := I + 1;
14 END LOOP;
17 число ) END;
Факториал числа, впервые превышающий 1000 000 Искомое число = PL/SQL procedure successfully completed.
Листинг 74. Пример процедуры на PL/SQL, исполь зующей цикл, управляемый оператором EXIT WHEN Еще одним распространенным вариантом организации цикла является цикл, управляемый ключевым словом WHILE.
Управление данного типа обеспечивает выполнение цикла до тех пор, пока условие, определенное ключевым словом WHILE, является истинным (TRUE). Модифицируем преды дущий пример, изменив константу и задав условие выхода из цикла ключевым словом WHILE.
SQL> DECLARE 2 NUMBER;
Ч Перем. для факториала 3 I NUMBER;
Ч Переменная-счетчик 4 Limit NUMBER 1000000000000;
Ч Граница 5 := числа, впервые превышающий 1 000 000 000 Ч Исполнительный блок 7 BEGIN Ч расширение SQL 8 I := 0;
9 Arg := I;
10 WHILE Arg < 1000000000000 LOOP 11 Arg := 12 I I + 13 END LOOP;
16 число 17 END;
Факториал числа, впервые превышающий 1 000 000 000 Искомое число = PL/SQL procedure successfully completed.
Листинг 75. Пример программы на PL/SQL с циклом, управляемым оператором WHILE Цикл, управляемый оператором используется в том случае, точно известно, сколько раз нужно выполнять итерацию цикла. Рассмотрим пример расчета факториала за данного числа. Обратите внимание, что переменную цикла (в данном случае I) описывать в блоке DECLARE не нужно.
DECLARE 2 Arg Перем. для факториала 3 Limit NUMBER Граница 4 числа 5 Ч Исполнительный блок 6 BEGIN 7 FOR I IN LOOP 8 Arg 9 END LOOP;
11 END;
/ Факториал числа 20 = PL/SQL procedure successfully completed.
Листинг 76. Пример процедуры на PL/SQL, с циклом, управляемым оператором FOR Оператор Оператор перехода GOTO позволяет осуществить пере ход по метке, присутствующей в теле программы. С помощью уникального идентификатора, заключенного в двойные угло вые скобки, можно пометить любую часть программы PL/SQL для организации безусловного перехода по метке.
Апеллируя к классической аргументации Э. можно утверждать, что использование данного оператора в програм мах не является хорошим стилем для языков, обладающих развитыми средствами обработки событий и управления по током команд.
Курсоры Ключевым понятием языка PL/SQL является курсор. Кур сор Ч это поименованный запрос, содержащий некоторое фиксированное число строк в выборке. По существу курсор является окном, через которое пользователь получает доступ к информации базы данных. Курсоры, в частности, могут ис пользоваться для присваивания конкретных значений пере менным программы. PL/SQL неявно объявляет курсор для всех SQL-предложений манипулирования данными, включая запросы, возвращающие ровно одну строку. Допустимое ко личество курсоров на сессию устанавливается параметром инициализации OPEN_CURSOR в файле параметров.
PL/SQL Ч SQL Пусть в базе данных существует таблица созданная и заполненная предложениями:
CREATE TABLE ТаЫ NUMBER, INSERT INTO ТаЫ VALUES (1, ) INSERT INTO VALUES (2, INSERT INTO Tabl VALUES (3, Существует три основных типа курсоров: неявный, явный и курсорный цикл FOR.
Неявный курсор создается автоматически при выполне нии оператора вида SELECT... INTO. В процессе выполне ния курсор открывается, после чего из него выбираются дан ные, и закрывается. Все эти действия производятся сервером за один шаг. Если неявный курсор возвращает более чем одну строку, возбуждается предопределенная исключительная си туация и выдается сообщение об ошиб ке. Если неявный курсор вообще не возвращает строк, другая предопределенная исключительная ситуация и также выдается сообщение об ошибке.
Пример выборки данных с использованием неявного курсора приведен в листинге 77.
DECLARE 3 Arg 4 BEGIN 5 SELECT INTO 6 FROM Tabl WHERE 7 I I 8 END;
9 / 1 A DECLARE Argl NUMBER;
Arg 4 BEGIN Раздел 5 INTO FROM 6 WHERE At 7 I I I 9 / DECLARE * ERROR at line 1:
no data found at line DECLARE 2 Argl NUMBER;
3 Arg 4 BEGIN 5 SELECT Atl,At2 INTO FROM Tabl 6 WHERE IN 7 | | | |Arg2) 9 / DECLARE * ERROR at line exact fetch returns more than re quested number of rows ORA-06512: at line 77. Пример программы на PL/SQL, осуществ ляющей выборку данных с не цикл FOR Ч это синтаксическая конструк ция, которая позволяет использовать курсор в пределах цик ла. Обычно он используется, когда обрабатывается каждая возвращаемая строка. Курсор объявляется в секции DECLARE с помощью ключевого слова CURSOR. Обратите внимание, в следующем примере переменная в которую в цикле выбираются данные, не требует объявления.
SQL> DECLARE PL/SQL Ч процедурное расширение SQL 2 CURSOR Curl IS SELECT FROM 3 vl 4 BEGIN 5 FOR rec IN Curl LOOP 7 END LOOP;
9 END;
10 / PL/SQL procedure successfully completed.
78. программы на PL/SQL, ляющей выборку данных с использованием кур сорного цикла FOR Простейший вариант курсорного цикла FOR представля ет собой запрос, встроенный в описайие цикла:
SQL> DECLARE 2 vl 3 BEGIN 4 FOR rec IN (SELECT FROM Tabl) 6 END 9 / PL/SQL procedure successfully completed.
79. Пример программы на PL/SQL, осуществ ляющей выборку данных с использованием за проса, встроенного в описание цикла Рассмотрим пример выборки данных с использованием явных курсоров. Объявим курсор Curl, ориентированный на получение данных из таблицы Раздел CURSOR Curl IS * FROM Первым шагом, необходимым для работы с курсором, яв ляется открытие курсора, которое выполняется командой:
OPEN Выборка данных из курсора может быть выполнена в на бор переменных подходящих типов, командой FETCH, на пример, таким образом:
FETCH Curl INTO Самый простой путь определить набор переменных, в ко торые планируется выборка данных из курсора - - объявить переменную типа RECORD, основанную на типе курсора.
этом если выражение SELECT изменяется, то поля этой переменной также изменяются, но уже автоматически.
Полностью процедура получения данных из таблицы Tabl выглядит следующим образом:
DECLARE 2 Cursor Curl IS SELECT * FROM 3 Rec 4 BEGIN 5 OPEN Curl;
Ч Курсор должен быть открыт 6 FOR I IN LOOP 7 FETCH Curl INTO rec;
' 9 END LOOP;
10 END;
11 / 1 A 2 В PL/SQL Ч процедурное расширение SQL 3 С PL/SQL procedure successfully completed.
Листинг 80. Пример программы на PL/SQL, осуществ ляющей выборку данных с использованием яв ного курсора Программа, представленная в листинге 80, неудачна тем, что цикл настроен на получение конкретного числа строк, которых может и не быть в таблице. В PL/SQL для курсоров предусмотрены специальные методы и принимающих противоположные булевские чения. Метод возвращает значения ес ли выборка в курсор пустая, то есть не содержит строки. Об ратите внимание, что после открытия курсора, но до первой команды FETCH, методы и прини мают неопределенное значение (UNKNOWN). Незнание этого факта может привести к достаточно распространенной ошиб ке. При цикла с использованием оператора WHILE и выполнением проверки на истинность на входе цикл не будет выполнен ни разу, несмотря на наличие данных, удовлетворяющих запросу.
Метод возвращает число строк, выбран ных после открытия курсора.
При объявлении переменных также можно использовать специальные атрибуты и Атрибут %TYPE предназначен для определения типа данных пере менной, константы или столбца. Этот атрибут обычно приме няется при объявлении переменной с типом данных, завися щим от определения столбца в таблице базы данных. Атрибут обычно применяется, когда объявляется пере менная-запись, которая должна иметь такую же структуру, что и строка в таблице или представлении, или запись, извле каемая из курсора. Использование атрибутов и предохраняет от возможных ошибок при изме нении типов данных столбцов таблиц или структуры самих таблиц.
С учетом дополнительных объектов и методов PL/SQL рассмотрим новый вариант программы выборки строк табли цы с использованием курсоров. Обратите внимание на повторный вывод последней строки. Попытайтесь исправить организацию цикла для устранения повторного вывода.
DECLARE 2 TYPE tabl_rec_type IS RECORD Ч Определение нового типа данных 3 Tabl-.Atl%TYPE, Ч Переменная типа ат рибута таблицы 4 Arg2 Ч Переменная типа ат рибута At2 таблицы 5 Tabl_rec -- Определение объ екта сконструированного типа Cursor Curl IS SELECT * FROM Tabl;
-- Опре деление курсора 7 BEGIN 8 OPEN Curl;
Ч Курсор должен быть открыт 9 LOOP 10 EXIT WHEN 11 FETCH Curl INTO 13 END LOOP;
/ 1 A 2 В 3 С 3 С, PL/SQL procedure successfully completed.
Листинг Пример модифицированной программы, осуществляющей выборку данных с использова курсора Ч процедурное расширение SQL Объявление курсора может содержать параметрический запрос. Значения параметров задаются при открытии курсора.
Имя параметра должно отличаться от имени столбца в запро се курсора. В противном случае будет возвращена каждая строка! (х = х всегда TRUE). Рассмотрим пример выборки данных с параметрическим запросом. В листинге 82 приведен пример с использованием параметрического курсора и изме ненной организацией цикла, исключающей повторный вывод последней строки.
SQL> DECLARE 2 TYPE tabl_rec_type IS Ч Определение нового типа данных 3 Переменная типа рибута таблицы 4 Arg2 Ч Переменная типа ат рибута таблицы 5 Tabl_rec Ч Определение объ екта сконструированного типа Cursor Cur2 (I NUMBER) IS SELECT * FROM WHERE = 1;
7 BEGIN 8 OPEN Ч Курсор открыт с параметром 9 FETCH INTO 10 WHILE LOOP 12 FETCH Cur2 INTO 13 END LOOP;
14 END;
1 2 PL/SQL procedure successfully completed.
Пример программы на PL/SQL, осуществ ляющей выборку данных с использованием па раметрического курсора работы с явными курсорами может использоваться конструкция Ч ссылка на курсор. Сначала объявляется ссылка на курсор, а описание курсора производится потом.
Как правило, ссылки на курсор применяются, когда запрос для курсора формируется динамически. Пример работы приведен в листинге DECLARE 2 TYPE cursor_type IS REF CURSOR;
3 Curl 5. rec 6 BEGIN 7 * FROM 8 l_query:= WHERE 9 OPEN Curl FOR 10 FETCH Curl INTO rec;
12 " CLOSE 13 END;
14 / PL/SQL procedure successfully completed.
Листинг 83. Пример программы на PL/SQL, осуществ ляющей выборку данных с использованием ссылки на курсор Обработка исключительных ситуаций Большинство развитых языков программирования обла дают встроенными механизмами обработки языковые средства рены и в PL/SQL. При возникновении предопределенной или объявленной пользователем ситуации происходит автомати PL/SQL Ч процедурное расширение SQL передача управления в нужный фрагмент блока EXCEPTION программы на PL/SQL, где и происходит преду смотренная обработка возникшей исключительной ситуации.
Некоторые предопределенные исключительные ситуации PL/SQL представлены в таблице 3. Полный перечень исклю чительных ситуаций может быть найден в руководстве по языку PL/SQL.
Таблица предопределенные исключи тельные PL/SQL Символическое имя Описание предопределенной предопределенной исключительной исключительной ситуации LOGIN_DENIED Неуспешное подключение к сер веру (например, введен ошибоч ный пароль) NOT_LOGGED_ON Попытка выполнить действие без подключения к серверу Oracle INVALID_CURSOR Ссылка на курсор или недопустимая операция с курсором.
, Не найдены данные, соответству ющие оператору SELECT INTO Попытка вставить в столбец с ограничением на уникальность значения значение-дубликат TOO_MANY_ROWS Оператор SELECT INTO возвра щает более одной строки Арифметическая ошибка, ошиб ка преобразования или усечения Рассмотрим пример программы с обработкой тельных ситуаций. В тексте программы пропущен оператор открытия курсора. Поэтому при обращении к методу неоткрытого курсора возникнет исключительная ситуация DECLARE 3 Arg 4 CURSOR Curl IS SELECT * FROM 5 BEGIN 6 WHILE LOOP 7 Curl INTO END 9 EXCEPTION 10 WHEN THEN открыт 12 END;
/.
He открыт курсор PL/SQL procedure successfully completed.
84. Пример обработки исключительной си туации в программе на PL/SQL Для обработки исключительных ситуаций также можно использовать специальный обработчик PL/SQL OTHERS или описать пользовательскую исключительную ситуацию и за программировать ее обработку. Ключевое слово OTHERS блока EXCEPTION определяет механизм универсальной об работки исключительных ситуаций, не вошедших в список ситуаций, обрабатываемых явно. Использование специально го обработчика исключительных ситуаций OTHERS является хорошим стилем программирования, при котором в програм ме не возникает необработанных ситуаций.
Дополним пример, представленный в листинге 84. Вве дем в текст программы запрещенную операцию деления на ноль, и обработаем данную исключительную ситуацию в спи ске OTHERS. (На самом деле в Oracle предопределена ис ключительная ситуация ZERO_DIVIDE, но в данном примере Ч процедурное расширение SQL это не важно, а важно то, что ее нет в списке блока EXCEPTION.) SQL> DECLARE 3 Arg 4 CURSOR Curl IS SELECT * FROM 5 Arg3 := 1;
6 BEGIN 7 Arg3 := 8 WHILE LOOP 9 FETCH Curl INTO 10 END LOOP;
11 EXCEPTION WHEN INVALID_CURSOR THEN 13 открыт 14 WHEN OTHERS THEN / Ошибка PL/SQL procedure successfully completed.
85. Пример обработки всех необъявленных исключительных ситуаций в списке OTHERS Рассмотрим технику определения и обработки пользова тельских исключительных ситуаций. Исключительная ция, определяемая пользователем, должна быть объявлена в блоке DECLARE программы. В PL/SQL используется сле дующий синтаксис объявления исключительной ситуации:
EXCEPTION;
Например, предопределенные исключения PL/SQL объ явлены как глобальные в пакете STANDARD в схеме пользователя SYS.
В программе условие возникновения исключительной си туации определяется стандартными средствами PL/SQL, чаще всего операторами После обнаружения условий возникновения исключительной ситуации она генерируется оператором RAISE. PL/SQL используется следующий син таксис генерации исключительной ситуации:
RAISE Оператор RAISE генерирует определенную пользовате лем исключительную ситуацию и передает управление в блок EXCEPTION.
Рассмотрим пример обработки пользовательской исклю чительной ситуации. Достаточно часто в роли пользователь ских исключительных ситуаций выступает выход некоторого хранимого в базе данных значения атрибута за заданные гра ницы. В качестве примера в роли исключительной ситуации рассмотрим превышение значением порога, равного 2.
SQL> DECLARE 2 Argl 3 Arg 4 Special_case EXCEPTION;
5 Cursor Curl IS SELECT * FROM 6 BEGIN 1 OPEN Curl;
8 FETCH Curl INTO 9 WHILE LOOP 10 FETCH Curl INTO 11 IF Argl > 12 THEN RAISE 13 END IF;
14 END LOOP;
15 EXCEPTION 16 WHEN Special_case THEN 17 ис ключительная 18 WHEN OTHERS Ч процедурное яаыка SQL Пользовательская исключительная ситуация PL/SQL procedure successfully completed.
86. Пример определения и обработки поль зовательской исключительной ситуации в про грамме на PL/SQL Для обработки исключительных ситуаций, которые от сутствуют в таблице 3, следует выражение PRAGMA С помощью объявленных таким можно обработать практически любую ошибку вера, возникающую при работе программы PL/SQL. Для объ явления соответствующего исключения достаточно знать но мер ошибки. следующем примере продемонстрируем обра ботку ошибки преобразования символьного значения в дату по заданной маске:
Ч сначала узнаем номер ошибки DECLARE 2 DATE;
3 BEGIN 5 END;
6 / * ERROR at line a non-numeric character was found where a numeric was expected at line ' DECLARE 2 vl DATE;
3 EXCEPTION;
'Х 4 PRAGMA 5 BEGIN 7 EXCEPTION 8 WHEN THEN 9 ошибка преобразования 10 END;
11 / Произошла ошибка преобразования даты PL/SQL procedure successfully completed.
87. Пример определения исключительной си туации с помощью PRAGMA EXCEPTION функции и Реализация языка программирования высокого уровня предполагает возможность создания и поддержки процедур и функций. В PL/SQL процедуры и функции, связанные единым целевым назначением, в пакеты. Особенно стью является то, что процедуры и функции являются объек тами базы данных. Это означает, что их описание хранится словаре данных, а собственно код хранится не в файловой системе, а непосредственно в базе данных. Функции отлича ются от процедур тем, что функции возвращают в вызываю щую среду одно значение соответствующего типа данных, а процедура не возвращает ничего. Oracle также поддерживает специальный тип процедур Ч триггер, который рассматрива ется как самостоятельный объект базы данных. Детально триггеры будут рассмотрены ниже, а коротко триггер можно охарактеризовать как процедуру, автоматически запускаемую сервером при наступлении некоторого события.
Исполняемый код процедур и функций хранится в базе данных в откомпилированной форме, поэтому выполнение Ч процедурное расширение SQL типовых операций, Характерных для конкретного приложе ния, целесообразно оформлять в виде процедур и функций.
Такой подход уменьшает накладные расходы за счет сущест венного упрощения фазы синтаксического анализа.
выполнения стандартного набора типовых действий по обработке данных в Oracle предусмотрен достаточно бога тый набор функций, которые кратко описываются далее.
SQL-функции Oracle Набор встроенных в язык SQL функций позволяет вы полнить многие типовые операции обработки данных вызо вом соответствующей функции. Ниже приводится описание функций, сгруппированное по типовым задачам.
встроенные можно использовать в SQL выражениях (как в списке после ключевого слова SELECT, так и в других конструкциях). Очевидно, что, имея привиле гии на выборку данных из любой таблицы, можно вычислить значение встроенной функции от любого набора параметров (указав ее в перечне списка SELECT). Однако имеется еще один способ. Каждая база данных Oracle имеет специальную общедоступную таблицу с именем dual. Эта таблица находит ся в схеме пользователя SYS и содержит один столбец dummy и одну строку. нее существует общий синоним. Один из наиболее распространенных вариантов использования табли цы dual Ч вычисление результатов SQL-функций помощью запросов к ней.
соответствие числовых кодов и символов Функция возвращает символ, имеющий соответствующее значение параметра но в используемом коде (обычно ASCII). Пример применения функции представлен в листинге 88.
SELECT 2 FROM CHR ABC 88. Пример применения функции определения символа по его номеру в кодовой таблице возвращает числовое значение (номер) символа, заданного параметром символ. Пример при менения функции в листинге 89.
SQL> SELECT "Функция ASCII" FROM dual;
Функция ASCII 89. Пример применения функции определения номера символа Функции Функция преобразует каждую первую букву слов параметра строка в прописную, а все последую щие в строчные. Пример применения функции представлен в листинге 90.
SQL> SELECT 2 "Функция INITCAP" FROM Ч процедурное расширение SQL Функция INITCAP База Данных Пример применения функции преобразо вания первой буквы в прописную Функция преобразует букву параметра строка в строчную. Пример применения функции представлен в листинге SQL>SELECT бАзА "Функция LOWER" FROM dual;
Функция LOWER база данных Пример применения функции преобразо вания букв в строчные Функция преобразует каждую букву па раметра строка прописную. Пример применения функции представлен в листинге SQL> SELECT 2 "Функция UPPER" FROM Функция UPPER БАЗА ДАННЫХ Листинг Пример применения функции преобразо букв в прописные Символьные функции усечения и дополнения строк Функция возвращает значение параметра дополненное слева до числа символов, которое задано пара метром символом-наполнителем, заданным параметром По умолчанию символом наполнителем является пробел.
Функция возвращает значение параметра дополненное справа до числа символов, которое задано пара метром символом-наполнителем, заданным параметром По умолчанию символом наполнителем является Пример применения функции LPAD и RPAD представлен в листинге SELECT 14, 2 "Функция LPAD" FROM Функция LPAD +++база данных SELECT 14, 2 "Функция RPAD" FROM Функция RPAD база данных+++ Листинг Пример применения функций дополнения строк Функция возвра щает усеченное слева значение параметра Из Ч процедурное расширение SQL ки параметра символы удаляются слева до тех пор, пока удаляемый символ входит во множество символов пара метра По умолчанию состо ит из символа пробела.
Функция возвра щает усеченное справа значение параметра Из строки параметра символы удаляются справа до тех пор, пока удаляемый символ входит во множество символов параметра По умолчанию состоит из символа пробела. Примеры применения функций и представлены в листинге 94.
SQL>SELECT база 2 "Функция LTRIM" FROM dual;
Функция LTRIM база данных данных 2 "Функция RTRIM" FROM dual;
Функция RTRIM база данных SQL> SELECT 2 "Функция LTRIM" FROM dual;
Функция LTRIM данных Листинг Примеры применения функций усечения строк 7. Заказ № 1628.
Символьные функции Функция возвращает значение параметра для которой выполнено следующее преобразование. Все вхожде ния параметра замещены значением параметра Если в строке содержится больше символов, чем в строке то символы, которым нет соответствия, замещаются на пустой символ (то есть исключаются из результирующей Функция TRANSLATE может применяться, в частности, для текстов, подготовленных с использованием различных рас кладок клавиатур. применения TRANSLATE представлен в листинге 95.
SQL>S.ELECT TRANSLATE с Функция TRANSLATE применя етсяё в 2 "Функция TRANSLATE" FROM Функция TRANSLATE Функция TRANSLATE в частности, Листинг 95. Пример применения функции преобразо ' вания символов строк Функция возвращает значение параметра стро для которой выполнено следующее преобразование. Все вхождения параметра замещены значением параметра Если параметр не задан, то все вхождения параметра удаляются. Пример применения функции REPLACE пред ставлен в листинге 96.
PL/SQL Ч процедурное SQL Gold 2 "Функция REPLACE" FROM dual;
Функция Фирма LG Листинг Пример функции замены под строк.
связанные с выделением подстрок Функция позиция возвращает подстроку параметра начиная с позиции, заданной параметром позиция, и длиной, заданной параметром Если параметр дли не задан, то возвращается подстрока до конца строки, заданной параметром Примеры примене ния функции SUBSTR представлены в листинге 97.
SQL> SELECT 2 "Функция SUBSTR" FROM dual;
Функция SUBSTR BCDE SQL> SELECT 2 "Функция SUBSTR" FROM dual;
Функция SUBSTR Примеры применения функции SUBSTR для выделения подстрок Функция возвращает пози цию вхождения строки, задаваемой параметром стро в строку, задаваемую параметром По зиция начала поиска задается необязательным числовым па раметром а необязательный пара метр задает требуемое число вхождений строки поиска в основную строку. Значения по умолчанию для необязательных параметров Ч При отсутствии требуе мого параметра строки поиска в основную строку функция возвращает значение 0. Примеры применения функ ции представлены листинге 98.
, 2 "Функция INSTR" FROM dual;
Функция INSTR О SELECT 1, 2 "Функция INSTR" FROM dual;
Функция INSTR SQL> SELECT "Функция INSTR" FROM dual;
Функция INSTR Примеры применения. функции определе ния позиций вхождений подстрок PL/SQL Ч расширение SQL Функция возвращает длину строки, заданной параметром строка. Пример применения функции представлен в листинге 99.
SQL> SELECT 2 "Функция LENGTH" FROM dual;
Функция LENGTH 99. Пример применения функции определения длины строки Числовые с в и Функция возвращает число е (основание натуральных логарифмов) в степени параметра числовой Функция возвращает натураль ный логарифм положительного параметра мент. Пример применения функций ЕХР и LN представлен в листинге 100.
SELECT "Функции LN и 2 FROM dual;
LN и ЕХР Листинг Пример применения функций логарифми рования и возведения в степень Функция воз вращает значение параметра основание в степени параметра Если параметр основание отрицатель ный, то параметр должен быть целым.
Функция возвращает значе ние квадратного корня параметра Если параметр отрицательный, то возвращает ся сообщение об ошибке.
Функция возвраща ет логарифм по основанию, заданному параметром параметра числовой Параметр основание может быть любым положительным числом, за исключением 1, а параметр должен быть положительным числом.
Примеры применения функций POWER и LOG представ лены в листинге SQL> SELECT "Функция POWER" 2 FROM Функция POWER - SQL> SELECT 2 "Функции POWER и LOG" FROM Функции POWER и LOG Листинг Примеры применения функций логариф мирования и возведения в степень функции Функции возвращают соответственно Ч процедурное расширение SQL синус, косинус и тангенс параметра раметр предполагается заданным в ра дианах.
Функции и возвращают соответственно арксинус и аркко синус параметра Параметр число предполагается находящимся в диапазоне от - до При вычислении функции с параметром мент вне указанного диапазона, выдается сообщение Функция возвращает арктангенс параметра Функции возвращают со ответственно гиперболический синус, гиперболический коси нус и гиперболический тангенс параметра Примеры применения тригонометрических функций представлены в листинге SELECT 2 "Функции SIN и FROM dual;
Функции SIN и SQL> SELECT FROM dual;
SELECT FROM dual * ERROR at line 1:
argument is out of range SQL> SELECT FROM 10381. SQL> SELECT FROM dual;
Функция TANH Листинг Примеры применения ских функций Числовые функции, с округлениями Функция округ ляет значение параметра числовой с точностью, определяемой параметром позиция. Параметр позиция опре деляет число десятичных знаков после запятой. Если пара метр позиция отрицательный, то аргумент округляется до це лых соответствующего масштаба (для значения пара метра до десятков, от Ч2 Ч до сотен и т. Значение па раметра позиция по умолчанию Ч 0.
Функция усекает значение параметра с точностью, опре деляемой параметром позиция. Параметр позиция определяет число десятичных знаков после запятой. Если параметр пози ция отрицательный, то аргумент до целых чисел соответствующего масштаба (для значения параметра -1 до десятков, от -2 до сотен и т. д.). Значение параметра позиция по умолчанию Ч 0.
Примеры применения функций ROUND и пред- ставлены в листинге SELECT "Функция 2 FROM Функция ROUND -. Ч процедурное расширение SQL SQL> SELECT "Функция 2 FROM TRUNC -. Листинг 103. Примеры применения функций округле ния Функция возвращает наи большее целое, меньшее или равное значению параметра чи Функция возвращает меньшее целое, большее или равное значению параметра чи Примеры применения функций FLOOR и CEIL представ лены в листинге SELECT - "Дробная 2 FROM dual;
Дробная часть SQL> SELECT "Функция CEIL" FROM dual;
Функция Примеры применения функций я Числовые связанные со числа Функция возвращает абсолют ное значение числа, заданного параметром Раздел Функция возвращает -1, если параметр < 0, возвращает 0, если пара метр = 0, и возвращает 1, если параметр числовой > 0.
Пример применения функций ABS и SIGN представлен листинге SELECT 2 FROM dual;
ABS(l) SIGN(2) SIGN{-2) 1 1. I Листинг 105. Пример применения связанных со знаком числа Числовые с модулярной Функция основание) возвра щает остаток от деления параметра на значение, определяемое параметром основание. Использова ние отрицательных значений параметра основание не мендуется, поскольку результат не соответствует принятому определению модуля числа. Пример применения функции MOD представлен в листинге SQL> SELECT 13) "Функция MOD" 2 FROM Функция MOD Листинг 106. Пример применения функций определе ния остатка числа PL/SQL Ч расширение SQL Функции, оперирующие с датами Функция SYSDATE возвращает дату и время, опреде ляемые средствами операционной системы сервера базы дан ных.
Функция дата округляет значение параметра дата по шаблону, определяемому параметром формат. Если параметр формат опущен, то аргумент дата округляется до дней (время устанавливается на полночь).
Функция дата усекает значение па раметра дата по шаблону, определяемому параметром фор мат. Если параметр формат аргумент дата усе кается до ближайшего дня (время устанавливается на ночь).
Некоторые значения параметра формат представлены в таблице 4.
Наиболее употребительные значения па раметра формат для дат Параметр Тип YYYY Преобразование до года. Округление в YEAR большую сторону происходит с 1 июля.
YY MONTH Преобразование до месяца. Округление в большую сторону происходит с 16 числа.
MM W Преобразование до того же дня недели, что первый день месяца.
Преобразование до дня.
DD J DAY Преобразование до начального дня недели.
D HH Преобразование до часа.
Раздел MI Преобразование до минут.
Функция возвращает дату дня, который является первым днем, более поздним, чем текущая дата с названием, совпадающим с указанным пара метром Функции ROUND, NEXT_DAY обычно исполь зуются для вычисления календарных интервалов, в частности для приложений, связанных с учетом трудовой активности.
Примеры применения функций SYSDATE, ROUND, TRUNC представлены в листинге SQL>SELECT HH24:MI')S 2 FROM dual;
S 15-04-2002 11: SQL> SELECT 2 "Функция 3 FROM dual;
Функция ROUND 15-04-2002 00: SELECT 2 Функция TRUNC" 3 FROM dual;
Функция TRUNC 15-04-2002 00: SQL> SELECT 2 Функция TRUNC" Ч процедурное расширение SQL 3 FROM Функция TRUNC 15-04-2002 11: SELECT 2 Функция FROM dual;
Функция NEXT_DAY 21-04-2002 11:17: Листинг Примеры применения функций округле ния и усечения дат Функции типов данных Функция возвращает результат преобразования значения параметра аргумент типа NUMBER или DATE в символьную строку. Для чисел если параметр формат опущен, аргумент преобразовывается в строку с длиной, достаточной для хранения всех значащих цифр. Некоторые значения параметра формат для преобра зования числовых значений представлены в таблице Таблица 5. Наиболее употребительные значения па раметра формат для чисел Формат Вид выводимого результата 9 Выводится цифра. Лидирующий ноль за меняется пробелом.
0 Выводится цифра. Лидирующий 0 выво дится.
ЕЕЕЕ Результат выводится в экспоненциальной нотации.
G Выводится символ-разделитель (обычно, запятая).
Раздел Примеры применения функций представлены в листинге 108.
SQL> SELECT 2 "Функция FROM dual;
Функция ТО CHAR SQL> SELECT 2 Функция FROM dual;
Функция 1E+ Листинг 108. Примеры применения функции преобра зования в символьную строку Функция возвращает результат преобразования значения параметра символьного типа в тип DATE. Если параметр формат опущен, должен соответствовать формату даты, принятому в системе по умол чанию. Наиболее употребительные значения параметра фор мат представлены выше в таблице 4. Пример применения функции представлен в листинге 2 "Функция FROM Функция TO_DATE 01-04-2002 00:00: 109. Пример применения функции вания символьных строк в Х Х Ч расширение SQL Функция возвра щает результат преобразования значения параметра символьного типа в значение типа NUMBER.
Параметр может представлять числа в любой допустимой Oracle нотации.
Пример применения функции представлен в листинге НО.
SELECT 3 "Функция dual;
ТО NUMBER 87. Листинг 110. Пример применения функции преобразо вания символьных строк в числа Функция воз вращает результат преобразования значения параметра сим символьного типа в тип ROWID.
Символьная функция возвращает результат операции значения параметра сим из параметра в Функция возвра щает результат преобразования значения параметра содержащий значение, в значение типа RAW.
Функция возвращает результат преобразования значения параметра при котором многобайтные символы, имеющие однобайтные эквиваленты заменяются на соответ ствующие однобайтовые символы.
" При использовании в выражениях различных типов дан ных Oracle выполняет неявное преобразование типов. Для выполнения преобразования Oracle может преобразовать кон станту в тип данных столбца;
значение столбца к типу дан ных константы;
тип данных столбца к типу данных другого столбца. Например, при выборке значения из столбца типа DATE в переменную типа CHAR или автомати чески вызывается функция для преобразования даты из внутреннего формата в строку, используя формат да ты, установленный по Если требуется получить строку, содержащую дату в другом формате, то следует явно вызвать функцию преобразования типа с подхо дящей маской форматирования. Обратно, при записи в стол бец типа DATE значения из переменной типа CHAR или автоматически вызывается функция TO_DATE.
При этом предполагается, что строка содержит символьную запись даты в формате по умолчанию. В противном случае произойдет ошибка.
Преобразования данных являются контекстно зависимыми, поэтому нельзя ожидать, что во всех случаях будет выполняться тот же вид преобразования. Вместо того чтобы полагаться на неявные или автоматические преобразо вания, необходимо использовать явное преобразование, обес печиваемое функциями SQL. Неявное преобразование типов также может негативно влиять на производительность, осо если значения столбца таблицы с большим числом строк преобразовываются к типу данных константы.
Функции замены аргументов Функция возвращает если имеет неопределенное значе ние (NULL), в противном случае возвращается Тип данных возвращаемого типом данных параметра Ч расширение SQL Проиллюстрируем применение функции на таблице, созданной и заполненной предложениями:
CREATE TABLE NUMBER, INSERT INTO VALUES (1, INSERT INTO Tabl (NULL, Пусть приложение замены всех неопределенных числовых значений на 0, а неопределенных символьных зна чений на знак SQL> SELECT NVL 0.) "Функция NVL", NVL (At2, ' * ) 2 "Функция NVL" FROM Функция NVL Функция NVL 1 * О А 111. Пример применения функции замены не определенных значений Обратите внимание, если второй аргумент функции является вычисляемым выражением,. то он вычисляется даже в том случае, когда первый аргумент не является Проиллюстрируем сказанное следующим примером:
SELECT 2 D 3 FROM D 14-03-2002 14:17: SQL> SELECT 2 FROM dual SELECT * ERROR at line a non-numeric character was found where a numeric was expected Листинг 112. иллюстрирующий обязатель ность вычисления второго аргумента функции NVL Перегружаемая SQL-функция DECODE (выражение,...] ) возвращает параметра если параметр выражение совпадает с параметром где х принимает значение 2,....
Pages: | 1 | 2 | 3 | 4 | ... | 5 | Книги, научные публикации