УП СУБД ч1

Раздел 12. Язык Oracle SQL. Команды манипулирования данными

В настоящем разделе описаны команды манипулирования данными языка Oracle SQL, которые предназначены для добавления новых записей, удаления записей и изменения существующих записей в SQL-таблицах.

12.1 Добавление записей

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

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

CREATE TABLE Customer		–	таблица клиентов
(	name CHAR(40),	–	имя клиента
	address CHAR( 50 ),	–	его адрес
	gender CHAR(1) );	–	пол
CREATE TABLE Item		–	таблица товаров
(	item CHAR(40),	–	название товара
	description CHAR(1000),	–	детальное описание
	price NUMBER );	–	цена единицы товара
CREATE TABLE «Order»		–	заказы товаров
(	customer CHAR(40),	–	имя клиента
	orderdate DATE,	–	дата заказа
	item CHAR(40),	–	название товара
	quantity INTEGER,	–	количествоединицвзаказе
	totalsum NUMBER,	–	общая цена заказа
	shipping_address CHAR(50), –		адрес для доставки
	shipping_date DATE);	–	дата доставки заказа

131

Простейший вариант команды INSERT приведен ниже:

INSERT INTO Customer

VALUES ( ‘Mr.Pundleberry’, ‘Green str. 2’, ‘F’ ); (12.1)

При исполнении данной команды в таблицу Customer будет добавлена строка, полям которой будут присвоены указанные значения вычисленных выражений. Типы, количество и порядок значений должны соответствовать определению таблицы. Таким образом, первому полю новой строки будет присвоено значение первого выражения из списка и т.д. В качестве выражений можно использовать NULL и DEFAULT, в первом случае значению поля будет присвоено пустое значение, а во втором – так называемое значение по умолчанию.

INSERT INTO Item	(12.2)
VALUES ( ‘DVD Disk’, NULL, 6.35 );
INSERT INTO Item	(12.3)
VALUES ( ‘Book’, DEFAULT, 12.50 );

Здесь в обоих случаях в созданных записях полю description будет присвоено значение NULL (во втором случае – поскольку значение NULL является значением по умолчанию, если явно не указано другое).

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

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

INSERT INTO Item ( item, price )	(12.4)
VALUES ( ‘Watch’, 199.99 )

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

132

Теперь рассмотрим внимательнее структуру таблицы «Order». Обратите внимание, что в ней, в отличие от таблицы acceptedorders, теперь хранятся: поле totalsum, значение которого вычисляется как произведение цены единицы товара и количества заказанных единиц, и поле shipping_ address (адрес доставки), значение которого дублируется из поля address таблицы Customer. Например, если необходимо добавить строку, соответствующую данным «‘Mr.O’»Raily’ заказал ‘11-08-2008’ товар ‘DVD Disk’ в количестве две штуки», то одним из вариантов команды для добавления новой записи в таблицу «Order» является следующий:

INSERT INTO «Order»

VALUES ( ‘Mr.O’’Raily’,

TO_DATE( ‘11-08-2008’, ‘DD-MM-YYYY’ ), ‘DVD Disk’,

2, 6.35 * 2, ‘Docks’, NULL);

(12.5)

Однако у этого варианта есть следующие недостатки. Вопервых, необходимо знать цену товара, которая хранится в таблице Item в строке, соответствующей товару ‘DVD Disk’, и подставить это значение в текст запроса в выражение для вычисления общей стоимости заказа. Во-вторых, необходимо получить значение адреса для доставки, которая хранится в таблице Customer, и также подставить это значение в INSERT-команду.

Для устранения в том числе подобных неудобств можно использовать вариант команды INSERT с подзапросом. Например, эквивалентным для команды 12.5 будет следующий вариант:

INSERT INTO «Order»
SELECT C.name,
TO_DATE( ‘11-08-2008’, ‘DD-MM-YYYY’ ),
I.item , 2, 2*I.price, C.address
FROM Customer C, Item I, NULL
WHERE C.name = ‘Mr.O’’Raily’	(12.6)
AND I.item = ‘DVD Disk’;

Здесь вместо конструкции VALUE указан текст SELECTзапроса, который исполняется, и сформированное результирующее множество построчно добавляется в таблицу «Order»; при этом, естественно, поля выборки подзапроса

133

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

Следует обратить внимание, что SELECT-запрос в пос­ леднем примере является двухтабличным и с формальной точки зрения является запросом с соединением (точнее говоря, кросс-соединением). Однако никакой взаимосвязи между записями обеих таблиц не существует и не подразумевается, что в том числе следует из текста запроса, – отсутствует условие эквисоединения. Этот пример иллюстрирует «механическое» использование двухтабличного запроса с целью извлечения данных из двух различных таблиц. Однако при работе с подобными запросами необходимо контролировать, сколько в действительности записей окажется в результирующем множестве. В нашем примере запись одна, и это следует из соглашения, что в таблицах Customer и Item должно быть в точности по одной записи для каждого клиента и товара соответственно.

Команды INSERT с подзапросами удобно использовать для заполнения данными новых таблиц на основе существующих строк других таблиц. Типичным примером является следующая команда, которая копирует в таблицу CourseResult имена студентов и названия посещаемых курсов, оставляя поле result незаполненным:

INSERT INTO CourseResult	(12.7)
SELECT student, title, NULL FROM Attends

12.2 Удаление строк

Команда DELETE предназначена для удаления одной или нескольких строк из SQL-таблицы. Ниже приведен простейший вариант использования этой команды:

DELETE FROM Courseresult

(12.8)

Служебное слово FROM является необязательным.

134

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

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

TRUNCATE TABLE Courseresult

(12.9)

Команда DELETE обычно используется в комбинации с конструкцией WHERE для спецификации условия селекции удаляемых записей. Это логическое условие в основном аналогично логическим условиям, используемым в SELECTзапросах, и может включать в том числе коррелированные подзапросы. Например, команда 12.10 удаляет сведения о заказах из таблицы «Order» с более ранней датой, чем ‘12-08-2008’.

DELETE “Order” WHERE orderdate <	(12.10)
TO_DATE( ‘12-08-2008’, ‘DD-MM-YYYY’ )

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

DELETE Attends A WHERE
NOT EXISTS ( SELECT * FROM Student S
WHERE S.name = A.student )
OR A.title NOT IN	(12.11)
( SELECT title FROM course )

Обратите внимание, что в командах DELETE также можно использовать синонимы таблиц (Attends A).

12.3 Изменение записей

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

135

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

Следующий пример изменяет значения поля result таблицы CourseResult, устанавливая в каждой записи значение

NULL:

UPDATE CourseResult SET result = NULL

(12.12)

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

UPDATE “Order” O SET totalsum = NULL	(12.13)
WHERE SHIPPING_DATE IS NULL

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

UPDATE “Order” O SET totalsum = quantity *

( SELECT price FROM Item WHERE item = O.item ),

SHIPPING_DATE =
( SELECT CURRENT_DATE FROM DUAL )	(12.14)
WHERE SHIPPING_DATE IS NULL

Впрочем, команду 12.14 можно переписать и без использования второго подзапроса:

UPDATE «Order» O SET totalsum = quantity *

( SELECT price FROM Item WHERE item = O.item ),

SHIPPING_DATE = CURRENT_DATE	(12.15)
WHERE SHIPPING_DATE IS NULL

136

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

UPDATE «Order» SET SHIPPING_DATE = ( CASE WHEN orderdata <

TO_DATE( ‘15-08-2008’, ‘DD-MM-YYYY’ )

THEN orderdata
ELSE LAST_DAY( CURRENT_DATE )	(12.16)
END )

Выводы

•команды манипулирования данными (DDL-команды) используются для добавления, удаления и изменения записей таблиц;

•синтаксически правильная команда DDL может быть не выполнена, если на таблицу наложены ограни­ чения;

•команда INSERT предназначена для добавления новых записей в таблицу;

•команда INSERT может использоваться для добавления одной записи (при помощи ключевого слова VALUE) или группы записей, образованных значением SELECTподзапроса;

•для удаления записей используется команда DELETE;

•для изменения существующих записей используется команда UPDATE;

•команды DELETE и UPDATE обычно используются с конструкцией WHERE, которая используется для селекции записей;

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

137

Вопросы для контроля

1.Объясните, почему исполнение некоторых корректных команд манипулирования данными приводит к возникновению ошибок.

2.Опишите два основных вида команды INSERT и объясните различия между ними.

3.Объясните различия между командами DELETE и

TRUNCATE.

4.Опишите структуру (синтаксис) команды UPDATE.

5.Можно ли использовать подзапросы в командах манипулирования данными?

138

Заключение

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

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

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

139

Литература

Основная:

1.Андон, Ф. Язык запросов SQL : учебный курс / Ф. Андон, В. Резниченко. – СПб. : Питер, 2006. – 415 с.

2.Малыхина, М. П. Базы данных: основы, проектирование, использование / М. П. Малыхина. – СПб. : БХВПетербург, 2006. – 528 с.

3.Моисеенко, С. SQL. Задачи и решения / С. Моисеенко. – СПб. : Питер, 2006. – 256 с.

4.Прайс, Дж. Oracle 10g SQL / Дж. Прайс – М. : Лори, 2007. – 566 с.

Дополнительная:

1.Барсегян, А. А. Методы и модели анализа данных: OLAP и DataMining / А. А. Барсегян, М. С. Куприянов,

В.В. Степаненко,­­ И. И. Холод. – СПб. : Вильямс, 2004 г. – 331 с.

2.Бобровский, С. Oracle Database XE для Windows. Эффективное использование / С. Бобровский. – М. : Лори, 2009. – 512 с.

3.Конев, И. Информационная безопасность предприятий / И. Конев, А. Беляев. – СПб. : БХВ-Петербург, 2003. – 478 с.

4.Конолли, Т. Базы данных. Проектирование, реализация и сопровождение / Т. Конолли, К. Бегг. – М. : Вильямс, 2000. – 1120 с.

5.Маклаков, С. В. BPWin, ERWin. CASE-средства разработки информационных систем / С. В. Маклаков. – М. : Диалог-МИФИ, 2000. – 256 с.

6.Фатрелл, Р. Управление программными проектами / Р. Фатрелл, Д. Шафер, Л. Шафер. – М. : Вильямс, 2003. – 1136 с.

7.Энсор, Д. Oracle. Проектирование баз данных / Д. Энсор, Й. Стивенсон. – Киев : BHV, 1998. – 557 с.

140