Geum.ru

Курс лекций "Базы данных и субд" Логинова С. А. Лекция 13. Язык sql. Команды dml. Команды управления транзакциями. Команды

Вид материалаКурс лекций

Содержание


Values (60, 5)
АСИД: (А) Атомарность
SAVEPOINT имя
ROLLBACK [TO SAVEPOINT имя]
Подобный материал:

Курс лекций “Базы данных и СУБД” Логинова С. А.



Лекция 13. Язык SQL. Команды DML. Команды управления транзакциями.


Команды DML

Команды языка манипулирования данными (DML) позволяют добавлять, удалять и изменять строки таблиц.

Вставка строк

Вставка строк в таблицу осуществляется с помощью команды INSERT языка SQL:

INSERT INTO таблица [(столбец, …)]

VALUES (значение, …);

где:

таблица
Имя таблицы, в которую должна быть вставлена строка.

столбец
Имя столбца или список имен столбцов таблицы.

значение
Значение соответствующего столбца.

С помощью данной команды можно вставить только одну строку.

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

Пример: В таблицу отделов добавить отдел маркетинга с №20, расположенный в регионе №2.

INSERT INTO s_dept

VALUES (20, ‘Marketing’, 2);

Результат:

1 row created.

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

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

Пример: В таблицу отделов добавить отдел продаж с №31, расположенный в регионе №5.

INSERT INTO s_dept (id, name, region_id)

VALUES (31, ‘Sales’, 5);

Данный запрос является верным с синтаксической точки зрения. Но в таблице s_dept уже есть отдел с номером 31, а столбец id (номер отдела) является первичным ключом таблицы s_dept, то есть не может содержать повторяющихся значений. Поэтому строка вставлена не будет, а сервер вернет ошибку.

Результат:

INSERT INTO s_dept (id, name, region_id)

*

ERROR at line 1:

ORA-00001: unique constraint (USER60.S_DEPT_ID_PK) violated

Пример: В таблицу отделов добавить отдел продаж с №51, расположенный в регионе №6.

INSERT INTO s_dept (id, name, region_id)

VALUES (51, ‘Sales’, 6);

Данный запрос является верным с синтаксической точки зрения. Отдела с номером 51 не существует, то есть ограничение PRIMARY KEY не нарушается. Но столбец region_id (номер региона) является внешним ключом и ссылается на столбец id таблицы регионов s_region. В этой таблице нет региона с номером 6, и вставка строки, ссылающейся на такой номер, нарушает ссылочную целостность. Поэтому строка вставлена не будет, а сервер вернет ошибку.

Результат:

INSERT INTO s_dept (id, name, region_id)

*

ERROR at line 1:

ORA-02291: integrity constraint (USER60.S_DEPT_REGION_ID_FK) violated - parent key not found

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

Пример: В таблицу отделов добавить технический отдел с №25, номер региона пока оставить пустым.

Явный способ:

INSERT INTO s_dept (id, name, region_id)

VALUES (25, ‘Techics’, null);

Эквивалентный неявный способ:

INSERT INTO s_dept (id, name)

VALUES (25, ‘Techics’);

Результат:

1 row created.

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

Пример: В таблицу отделов добавить отдел с №60, номером региона 5. Имя отдела оставить пустым.

INSERT INTO s_dept (id, region_id)

VALUES (60, 5);

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

Результат:

INSERT INTO s_dept (id, region_id)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("USER60"."S_DEPT"."NAME")

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

Пример: В таблицу служащих добавить служащего по фамилии ‘Smith’. Номер служащего установить равным 40, в качестве идентификатора пользователя установить значение функции USER, в качестве даты начала работы – текущую дату. В качестве размера зарплаты и номера отдела установить размер зарплаты и номер отдела служащего №10. Остальные поля оставить пустыми.

INSERT INTO s_emp (id, last_name, userid, start_date, salary, dept_id)

VALUES (40, 'Smith', USER, SYSDATE,

(SELECT salary FROM s_emp WHERE id=10),

(SELECT dept_id FROM s_emp WHERE id=10));

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

Пример: В таблицу служащих добавить служащего по фамилии ‘Jones’. Номер служащего установить равным 41, в качестве идентификатора пользователя установить ‘Jones41’, в качестве даты начала работы – 12 часов дня 13 ноября 2003 года. Остальные поля оставить пустыми.

INSERT INTO s_emp (id, last_name, userid, start_date)

VALUES (41, 'Jones', 'Jones41', TIMESTAMP '2003-11-13 12:00:00' );


Если вы будете использовать формат дат не установленный по умолчанию на сервере, дата не будет обработана. Поэтому сервер не сможет распознать, например, строку ‘13/11/2003 12:00’ как дату. Команда INSERT не будет выполнена, сервер вернет ошибку.

Результат:

VALUES (41, 'Jones', 'Jones41', '13/11/2003 12:00')

*

ERROR at line 2:

ORA-01830: date format picture ends before converting entire input string

Можно явно указать серверу, какую дату Вы хотите вставить, с помощью функции TO_DATE:

INSERT INTO s_emp (id, last_name, userid, start_date)

VALUES (41, ‘Jones’, ‘Jones41’,
TO_DATE(‘13/11/2003 12:00’, ‘dd/mm/yyyy hh24:mi’));

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

INSERT INTO таблица [(столбец, …)]

подзапрос;

где:

таблица
Имя таблицы, в которую должны быть вставлены строки.

столбец
Имя столбца или список имен столбцов таблицы.

подзапрос
Подзапрос, возвращающий строки в таблицу.

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

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

INSERT INTO s_region (id, name)

(SELECT MAX(id)+1, 'Oceania'

FROM s_region);

Обновление строк

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


UPDATE таблица

SET столбец = значение, …

[WHERE условие];

где:

таблица
Имя таблицы.

столбец
Имя столбца, значение которого обновляется.

значение
Новое значение столбца.

условие
Условие, задающее строки, которые необходимо изменить.

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

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

Пример: Служащему № 25 установить зарплату равную 2500.

UPDATE s_emp

SET salary=2500

WHERE id=25;

Результат:

1 row updated.

Пример: Всех служащих 31 отдела перевести в 41 отдел, при этом повысить им зарплату на 20%.

UPDATE s_emp

SET dept_id=41, salary=salary*1.2

WHERE dept_id=31;

Результат:

2 rows updated.

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

UPDATE s_emp x

SET salary=(SELECT count(id)*1000

FROM s_emp

WHERE manager_id=x.id)

WHERE id IN (SELECT manager_id FROM s_emp);

Результат:

8 rows updated.

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

Пример: Всем служащим установить пустое значение комиссионного процента.

UPDATE s_emp

SET commission_pct=null;

Результат:

25 rows updated.

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

Строки таблицы можно удалить с помощью команды DELETE языка SQL:

DELETE FROM таблица

[WHERE условие];

где:

таблица
Имя таблицы.

условие
Условие, задающее строки, которые необходимо удалить.

При использовании предложения WHERE удаляются только те строки, которые удовлетворяют условию, указанному в этом предложении. Если предложение WHERE команды DELETE опущено – удаляются все строки таблицы.

Пример: Уволился служащий №25. Удалить информацию о нем из таблицы s_emp.

DELETE FROM s_emp

WHERE id=25;

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

Пример: Удалить информацию о 32 отделе.

DELETE FROM s_dept

WHERE id=32;

Так как в дочерней по отношению к s_dept таблице s_emp есть информация о служащих, работающих в 32 отделе, при удалении строки об отделе №32 будет нарушено ограничение ссылочной целостности. Поэтому строка удалена не будет, а сервер вернет соответствующую ошибку.

Результат:

DELETE FROM s_dept

*

ERROR at line 1:

ORA-02292: integrity constraint (USER60.S_EMP_DEPT_ID_FK) violated - child record found.

Понятие транзакции

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

Обычно транзакцию образует последовательность команд DML – команд, изменяющих данные базы. Суть транзакции в следующем. Когда пользователь производит изменения, они изменяют только буфер БД, то есть не записываются физически в базу и не видны другим пользователям. Кроме того, если пользователь изменяет какие-то строки таблицы, они блокируются и становятся недоступны другим пользователям. После того, как пользователь закончит производить изменения, он может либо зафиксировать их, подав команду COMMIT, либо отменить, подав команду ROLLBACK. Если пользователь фиксирует изменения, производится запись обновленных данных в базу, предшествующее состояние базы теряется, все изменения становятся видны другим пользователям, а строки, которые изменялись разблокируются. Если пользователь отменяет изменения, то база данных остается в том состоянии, как и была, как будто никаких действий не происходило, строки также разблокируются. Таким образом, транзакцию можно расценивать, как одну логическую операцию, результат которой либо удовлетворяет пользователя и он фиксирует изменения, либо не устраивает пользователя и он отменяет изменения. Кроме того, транзакцию можно расценивать, как защиту действий пользователя от вмешательств других пользователей.

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

Транзакция обладает четырьмя важными свойствами, известными как свойства АСИД:
  • (А) Атомарность. Транзакция выполняется как атомарная операция – либо выполняется вся транзакция целиком, либо она целиком не выполняется.
  • (С) Согласованность. Транзакция переводит базу данных из одного согласованного (целостного) состояния в другое согласованное (целостное) состояние. Внутри транзакции согласованность базы данных может нарушаться.
  • (И) Изоляция. Транзакции разных пользователей не должны мешать друг другу (например, как если бы они выполнялись строго по очереди).
  • (Д) Долговечность. Если транзакция выполнена, то результаты ее работы гарантированно должны сохраниться в базе данных, даже если в следующий момент произойдет сбой системы.

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

Транзакция обычно начинается автоматически с момента присоединения пользователя к СУБД и продолжается до тех пор, пока не произойдет одно из следующих событий:
  • Подана команда COMMIT – зафиксировать транзакцию. При этом автоматически начинается новая транзакция.
  • Подана команда ROLLBACK – откатить транзакцию. При этом автоматически начинается новая транзакция.
  • Выполнена команда, неявно фиксирующая транзакцию. Такими командами являются команды DDL (CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT) и DCL (GRANT, REVOKE). После выполнения любой из таких команд изменения фиксируются так, как если бы была подана команда COMMIT, откат изменений невозможен. При этом также автоматически начинается новая транзакция.
  • Произошло отсоединение пользователя от СУБД. При этом происходит автоматическая фиксация транзакций.
  • Произошел сбой системы.

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

Существует три команды управления транзакциями языка SQL.

COMMIT;

Данная команда завершает текущую транзакцию, делая постоянными все произведенные изменения и начиная новую транзакцию.

SAVEPOINT имя;

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

ROLLBACK [TO SAVEPOINT имя];

где имя – имя точки сохранения.

Завершает текущую транзакцию, отменяя все произведенные изменения. При этом в случае, если Вы используете просто команду ROLLBACK, откат изменений производится к началу транзакции и начинается новая транзакция. Если Вы используете команду ROLLBACK TO SAVEPOINT имя, осуществляется откат изменений в текущей транзакции до указанной точки сохранения.

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

Начало транзакции

Первый_блок_команд_DML --Выполнена некоторая последовательность команд DML

SAVEPOINT one; --Задана точка сохранения с именем one

Второй_блок_команд_DML --Выполнена некоторая последовательность команд DML

SAVEPOINT two; --Задана точка сохранения с именем two

Третий_блок_команд_DML --Выполнена некоторая последовательность команд DML

ROLLBACK TO SAVEPOINT one; --Выполнен откат изменений к маркеру one

После выполнения последней команды текущая транзакция будет включать только последовательность Первого_блока_команд_DML и данные о точке сохранения one. Действия Второго_блока_команд_DML и Третьего_блока_команд_DML будут отменены и утеряны. Кроме того, будет потеряна точка сохранения two.

Команды COMMIT и ROLLBACK явно обрабатывают транзакцию. Необходимо помнить, что в ряде случаев происходит неявная обработка транзакции. Такой случай – неявная фиксация транзакции при выполнении команды DDL или DCL. В частности, если Вы выполнили ряд команд DML, а затем команду DDL или DCL, то результаты команд DML фиксируются вместе с результатами команды DDL или DCL и откат становится невозможен. Поэтому выполнение команд DDL и DCL требуют дополнительной осторожности.

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





Л екция 13. Язык SQL. Команды DML. Команды управления транзакциями.