Oracle. Более сложные манипуляции с данными
Ограничение диапазона выбираемых записей
Одной из наиболее распространенных операций, которые вы будете выполнять при выборе записей из таблицы, является получение определенного подмножества записей. Это подмножество будет постоянно меняться в зависимости от поставленных вопросов (например: «Какие клиенты не получали от меня сообщений более двух недель?» или «Какие товары продавались партиями, содержащими более чем 100 штук за последние 30 дней?»). Подобная фильтрация записей выполняется путем добавления к оператору SELECT специальной конструкции WHERE. В ней указываются условия (conditions), которым должны удовлетворять отображаемые записи. Синтаксис выглядит следующим образом:
SELECT столбцы FROM имя_таблицы WHERE условие(я);
Использование шаблонов
При поиске в текстовых столбцах часто возникает потребность отыскать небольшой фрагмент текста в любом месте столбца. Это делается с помощью шаблонов (wildcards), представляющих произвольную часть текста. При поиске текста можно применять шаблоны «_» (для представления одиночных символов) и «%» (для представления группы символов), а также оператор LIKE.
Сортировка по отдельным столбцам
Для изменения последовательности вывода записей достаточно добавить к команде SELECT конструкцию ORDER BY. В ней указывается один или несколько столбцов, по которым Oracle будет сортировать записи. Синтаксис конструкции ORDER BY следующий:
SELECT * FROM имя_таблицы ORDER BY столбец_сортировки ASC или DESC;
где, ASC — возрастающая сортировка, а DESC — убывающая сортировка.
Отображение только уникальных значений
Бывают ситуации, когда нужно узнать, какие значения содержит столбец, но при этом желательно увидеть только по одному экземпляру каждого значения для этого используются модификаторы DISTINCT или UNIQUE.
Выбор уникальных значений похож на выбор обычного списка значений, нужно лишь указать в команде SELECT модификатор DISTINCT или UNIQUE. Модификаторы DISTINCT и UNIQUE дают одинаковый эффект, но DISTINCT более распространен.
Выбор из DUAL
Согласитесь — было бы удобно иметь таблицу, где гарантированно присутствует только одна запись. Светлые головы из Oracle тоже об этом подумали и предусмотрели в процессе установки Oracle создание таблицы с именем DUAL, доступной всем пользователям.
Модификация данных в таблице
Изменить данные в таблице Oracle очень легко. Используемая для этого команда называется UPDATE, а ее синтаксис выглядит следующим образом:
UPDATE имя_таблицы SET имя_столбца = новое_значение WHERE условие;
Удаления записей с таблицы
Последний из фундаментальных навыков, необходимых при работе с таблицами, — это умение удалять записи. Команда DELETE имеет следующий синтаксис:
DELETE FROM имя_таблщы WHERE условие;
Удаление всех строк
Для этого применяются два способа: использовать команду DELETE, не указывая условие WHERE, или использовать новую для вас команду TRUNCATE (Усечь).
Усечение таблицы
Основное достоинство команды TRUNCATE — скорость. Когда Oracle выполняет эту команду, он не просматривает существующие записи, а просто выбрасывает их. Другой выигрыш от использования этой команды состоит в том, что она автоматически освобождает табличное пространство, которое занимали усеченные записи. Команда TRUNCATE имеет следующий синтаксис:
TRUNCATE TABLE имя_таблицы;
Предупреждение
Команда TRUNCATE необратима! Используйте ее только в случае действительной необходимости!
Управление транзакциями
До этого момента вы создавали и удаляли таблицы, вставляли и удаляли записи — короче говоря, делали все, что хотели, не задумываясь о том, как это может повлиять на других пользователей. В реальной жизни вам придется работать с таблицами, содержащими чужие данные, и выполненные вами изменения неизбежно затронут других пользователей. Чтобы отвечать за свои действия, вы должны понимать, каким образом Oracle вносит изменения в базу данных. Это немедленно принесет свою выгоду: в частности, вы научитесь использовать средства отмены (undo), предоставляемые Oracle.
Отмена транзакций DML
Когда вы вставляете, обновляете или удаляете данные, Oracle не выполняет эти изменения немедленно. Вам кажется, что изменения произошли тотчас же, если ввести команду SELECT, они будут отражены в выходных данных. Однако на самом деле изменения хранятся во временной области памяти и будут применены к таблице только в ответ на одну из нескольких специальных команд.
Отмена в Oracle выполняется с помощью команды ROLLBACK (Откат). Производя откат одной и более транзакций, вы сообщаете Oracle, что они не должны применяться к базе данных. Команда ROLLBACK имеет следующий синтаксис:
ROLLBACK TO имя_точки _отката;
Возможности команды ROLLBACK не ограничиваются только одним уровнем отмены. В сочетании с другой командой, SAVEPOINT, она позволяет возвращаться в любую из предварительно установленных точек. Команда SAVEPOINT имеет следующий синтаксис:
SAVEPOINT имя_точки_сохранения;
Что дает использование нескольких точек возврата? Прежде всего, гибкость. При выполнении большого пакета команд можно устанавливать точки сохранения после каждой логической группы команд, и если следующая группа по какой-то причине даст неудовлетворительный результат, вы всегда сможете вернуться к последней точке и применить лишь те изменения, которые
были выполнены до нее.
Имена точек сохранения — всего лишь метки, поэтому они могут быть любыми. У вас есть возможность выбирать имена, по которым будет видно, какие данные охватывает каждая точка. Для имен точек сохранения используются соглашения, похожие на соглашения для имен таблиц и столбцов: максимальная длина — 30 символов, а первым символом должна быть буква. Теперь, научившись отменять изменения, вы готовы к тому, чтобы делать их постоянными. Соответствующая команда называется COMMIT (Завершить).
Поскольку она приводит к записи всех ваших изменений в таблицу базы данных (что делает невозможным откат), все точки сохранения, установленные к моменту завершения транзакции, сбрасываются.
Доступность данных для других пользователей
По команде COMMIT изменения записываются в базу данных, разделяемую всеми остальными пользователями, поэтому завершение вашей работывлияет на то, какие данные будут им видны. Доступ к вашим изменениям станет возможен только после выдачи команды COMMIT. Следовательно, вы можете вставить тысячу новых записей, изменить тысячу существующих, еще тысячу удалить, но ни одно из этих изменений не отразится в операторах SELECT других пользователей, пока вы не завершите свою работу командой COMMIT.
Явное и не явное завершение
Выше мы описали команду COMMIT. Однако явный ввод команды—это лишь один из способов завершения изменений в базе данных. Некоторые команды Oracle выполняют завершение предыдущих команд, не дожидаясь ваших указаний, т.е. неявно. Говоря конкретнее, любые команды DDL (например, CREATE TABLE или DROP TABLE) неявно завершают изменение всех не сохраненных данных перед выполнением своих функций. Выход из Oracle также приводит к автоматическому завершению ваших изменений.
Итоги
Сначала вы узнали, как с помощью конструкции WHERE оператора S ELECT ограничить диапазон записей, возвращаемых Oracle. При фильтрации можно использовать разнообразные операторы сравнения, включая =, !=, <>, >, <, >=, <=, BETWEEN и NOT. Вы также научились создавать объединенные условия, разделяя их словом AND, и условия «или», разделенные словом OR. Если требуется выбрать записи, содержащие любое значение из некоторого множества, то вместо многочисленных конструкций OR можно использовать функцию IN.
При поиске текста можно применять шаблоны «_» (для представления одиночных символов) и «%» (для представления группы символов), а также оператор LIKE. Чтобы найти записи с пустыми столбцами любого типа или, наоборот, избежать их вывода, нужно указать в конструкции WHERE оператор IS NULL или IS NOT NULL.
Добившись от оператора SELECT вывода именно тех записей, которые вам нужны, можно изменить порядок их отображения, добавив конструкцию ORDER BY. В ней указывается столбец (или столбцы), по которому должны сортироваться записи на экране. (Помните, что ORDER BY изменяет только порядок вывода записей, а не их фактическое расположение в таблице, так как последнее заняло бы слишком много времени.) Как правило, крайний левый из отображаемых столбцов следует делать первым столбцом сортировки, следующий отображаемый столбец — вторым, и т. д. В противном случае другим пользователям будет трудно понять, как отсортированы записи.
Бывают ситуации, когда нужно узнать, какие значения содержит столбец, но при этом желательно увидеть только по одному экземпляру каждого значения. Этого можно достичь, добавив модификатор DISTINCT сразу после ключевого слова SELECT. Вы можете в реальном времени выполнять вычисления с данными, не содержащимися в таблице, выбирая значения из DUAL.
Когда дело доходит до модификации данных в таблице, применяется команда UPDATE. В ней вы указываете таблицу, подлежащую обновлению, значения столбцов, а затем задаете условие, которому должна удовлетворять запись для обновления. Условие WHERE играет очень важную роль, поскольку при его отсутствии будет обновлена каждая запись! То же самое справедливо и при удалении записей по команде DELETE: если опустить конструкцик) WHERE, вы удалите все записи таблицы. Если ваша цель в этом и состоит, лучше использовать команду TRUNCATE TABLE, которая не считывает каждую запись таблицы перед ее удалением и поэтому работает гораздо быстрее.
В Oracle есть средства отмены в виде группы команд, относящихся к категории «управление транзакциями». Наиболее важна команда ROLLBACK, отменяющая любые команды DML (INSERT, UPDATE и DELETE), выполненные с момента последнего завершения. Явное завершение выполняется при вводе команды COMMIT. Неявное завершение происходит при выполнении любой из операций DDL (в частности, CREATE и DROP), а также при выходе из Oracle.
Точки сохранения обеспечивают возможность многоуровневой отмены — иначе говоря, позволяют вам решать, на какое количество команд DML выполнить откат. Выдавая команду SAVEPOINT, вы устанавливаете именованный маркер, к которому можно будет вернуться по команде ROLLBACK. Точки сохранения остаются активными до завершения изменений данных; после этого они исчезают. Измененные данные становятся видимы другим пользователям и программам, работающим с базой данных Oracle, только после завершения изменений. (Непонимание этого факта может легко привести к недоразумению. Например, кто-то вставляет, обновляет или удаляет данные, а потом удивляется, почему изменения не видны на компьютерах его коллег. Обычно это означает, что пользователь забыл завершить свои изменения.)
Примеры
1. Выбрать записи из таблицы table1, где поле product_price>=50 и product_price<=100:
SELECT * FROM table1 WHERE product_price>=50 AND product_price<=100;
или
SELECT * FROM table1 WHERE product_price BETWEEN 50 AND 100;
2. Выбрать записи из таблицы table1, поля которых не входят в диапазон product_price>=50 и product_price<=100:
SELECT * FROM table1 WHERE product_price<50 OR product_price>100;
или
SELECT * FROM table1 WHERE product_price NOT BETWEEN 50 AND 100;
3. Выбрать записи из таблицы table1, где поле product_name содержит любое значение из группы (‘SmallWidget’, ‘MediumWidget’, ‘ChromePhoobar’):
SELECT * FROM table1 WHERE product_name=’SmallWidget’ OR product_name=’MediumWidget’ OR product_name=’ChromePhoobar’;
или
SELECT * FROM table1 WHERE product_name IN (‘SmallWidget’, ‘MediumWidget’, ‘ChromePhoobar’);
4. Выбрать данные из таблицы table1, где поле product_name содержит, сначала пять любых символов, далее слово Chrome и еще любой набор символов:
SELECT * FROM table1 WHERE product_name LIKE ‘_____Chrome%’;
5. Выбрать данные из таблицы table1 и отсортировать по убыванию по полю product_price:
SELECT * FROM table1 ORDER BY product_price DESC;
6. Выбрать уникальные значения из таблицы table1:
SELECT DISTINCT * FROM table1;
7. Переименовать все товары в таблице table1, поле product_name с SmallWidget в LargeWidget:
UPDATE table1 SET product_name = ‘LargeWidget’ WHERE product_name = ‘SmallWidget’;
8. Удалить все данные из таблицы table1, где поле product_name SmallWidget:
DELETE FROM table1 WHERE product_name=’SmallWidget’;