Язык PL/SQL плотно интегрирован с базой данных Oracle. Из кода PL/SQL можно выполнять любые команды DML (Data Manipulation Language), в том числе INSERT
, UPDATE
, DELETE
и MERGE
и, конечно же, запросы на выборку.
Команды DDL (Data Definition Language) выполняются только в режиме динамического SQL.
Несколько SQL-команд можно сгруппировать на логическом уровне в одну транзакцию, чтобы их результаты либо все вместе сохранялись (закрепление), либо все вместе отменялись (откат). В этой статье рассматриваются SQL-команды, используемые в PL/ SQL для управления транзакциями.
Чтобы оценить важность транзакций в Oracle, необходимо хорошо понимать их основные свойства:
- Атомарность. Вносимые в ходе транзакции изменения состояния имеют атомарный характер: либо выполняются все изменения сразу, либо не выполняется ни одно из них.
- Согласованность. Транзакция корректно изменяет состояние базы данных. Действия, выполняемые как единое целое, не нарушают ограничений целостности, связанных с данным состоянием.
- Изолированность. Возможно параллельное выполнение множества транзакций, но с точки зрения каждой конкретной транзакции остальные кажутся выполняемыми до или после нее.
- Устойчивость. После успешного завершения транзакции измененные данные закрепляются в базе данных и становятся устойчивыми к последующим сбоям. Начатая транзакция либо фиксируется командой
COMMIT
, либо отменяется командойROLLBACK
. В любом случае будут освобождены заблокированные транзакцией ресурсы (командаROLLBACK TO
может снять только часть блокировок). Затем сеанс, как правило, начинает новую транзакцию. По умолчанию в PL/SQL неявно определяется одна транзакция на весь сеанс, и все выполняемые в ходе этого сеанса изменения данных являются частью транзакции. Однако применение технологии автономных транзакций позволяет определять вложенные транзакции, выполняемые внутри главной транзакции уровня сеанса.
Из блока кода PL/SQL можно выполнять MDL-команды (INSERT
, UPDATE
, DELETE
и MERGE
), оперирующие любыми доступными таблицами и представлениями.
При использовании модели разрешений создателя права доступа к этим структурам определяются во время компиляции, если вы используете модель прав определяющей стороны. Если же используется модель разрешений вызывающей стороны с конструкцией AUTHID CURRENT_USER
, то права доступа определяются во время выполнения программы.
Краткое введение в DML
Полное описание всех возможностей DML в языке Oracle SQL выходит за рамки моей статьи, поэтому мы ограничимся кратким обзором базового синтаксиса, а затем изучим специальные возможности PL/SQL, относящиеся к DML, включая:
- примеры команд MDL;
- атрибуты курсоров команд DML;
- синтаксис PL/SQL, относящийся к DML, например конструкция RETURNING.
За более подробной информацией обращайтесь к документации Oracle и другим описаниям SQL.
Формально команда SELECT считается командой DML. Однако разработчики под термином «DML» почти всегда понимают команды, изменяющие содержимое таблицы базы данных (то есть не связанные с простым чтением данных).
В языке SQL определены четыре команды DML:
- INSERT — вставляет в таблицу одну или несколько новых строк.
- UPDATE — обновляет в одной или нескольких существующих строках таблицы значения одного или нескольких столбцов.
- DELETE — удаляет из таблицы одну или несколько строк.
- MERGE — если строка с заданными значениями столбцов уже существует, выполняет обновление. В противном случае выполняется вставка.
Команда INSERT
Существует две базовые разновидности команды INSERT:
О Вставка одной строки с явно заданным списком значений:
INSERT INTO таблица [(столбец_1, столбец_2, ..., столбец_п)]
VALUES (значение_1, значение_2, ..., значение_п);
О Вставка в таблицу одной или нескольких строк, определяемых командой SELECT, которая извлекает данные из других таблиц:
INSERT INTO таблица [(столбец_1, столбец_2, ..., столбец_п)]
SELECT ...;
Рассмотрим несколько примеров команд INSERT в блоке PL/SQL. Начнем со вставки новой строки в таблицу books. Обратите внимание: если в секции VALUES заданы значения всех столбцов, то список столбцов можно опустить:
BEGIN
INSERT INTO books
VALUES ('1-56592-335-9',
'Oracle PL/SQL Programming',
'Reference for PL/SQL developers,' ||
'including examples and best practice ' ||
'recommendations.',
'Feuerstein,Steven, with Bill Pribyl',
TO_DATE ('01-SEP-1997','DD-MON-YYYY'),
987);
END;
Можно также задать список имен столбцов, а их значения указать в виде переменных, а не литералов:
DECLARE
l_isbn books.isbn%TYPE := '1-56592-335-9';
... другие объявления локальных переменных
BEGIN
INSERT INTO books (
book_id, isbn, title, summary, author,
date_published, page_count)
VALUES (
book_id_sequence.NEXTVAL, l_isbn, l_title, l_summary, l_author,
l_date_published, l_page_count);
ВСТРОЕННАЯ ПОДДЕРЖКА ПОСЛЕДОВАТЕЛЬНОСТЕЙ В ORACLE11G
До выхода Oracle11 программисту, желавшему получить следующее значение из последовательности, приходилось вызывать функцию NEXTVAL в команде SQL. Это
можно было сделать прямо в команде INSERT, которой требовалось значение:
INSERT INTO table_name VALUES (sequence_name.NEXTVAL, ...);
или в команде SELECT из старой доброй таблицы dual:
SELECT sequence_name.NEXTVAL INTO l_primary_key FROM SYS.dual;
Начиная с Oracle11g, следующее (и текущее) значение можно получить при помощи оператора присваивания — например:
l_primary_key := sequence_name.NEXTVAL;
Команда UPDATE
Команда UPDATE обновляет один или несколько столбцов или одну или несколько строк таблицы. Ее синтаксис выглядит так:
UPDATE таблица
SET столбец_1 = значение_1
[, столбец_2 = значение_2, ... столбец_N = значение_N]
[WHERE условие];
Предложение WHERE не обязательно; если оно не задано, обновляются все строки таблицы.
Несколько примеров команды UPDATE:
- Перевод названий книг таблицы books в верхний регистр:
UPDATE books SET title = UPPER (title);
- Выполнение процедуры, удаляющей компонент времени из даты издания книг, которые были написаны заданным автором, и переводящей названия этих книг в символы верхнего регистра. Как видно из примера, команду UPDATE можно выполнять как саму по себе, так и в блоке PL/SQL:
PROCEDURE remove_time (author_in IN VARCHAR2)
IS
BEGIN
UPDATE books
SET title = UPPER (title),
date_published = TRUNC (date_published)
WHERE author LIKE author_in;
END;
Команда DELETE
Команда DELETE удаляет одну, несколько или все строки таблицы. Базовый синтаксис:
DELETE FROM таблица
[WHERE условие];
Условие WHERE не обязательно; если оно не задано, удаляются все строки таблицы. Несколько примеров команды DELETE:
- Удаление всей информации из таблицы books:
DELETE FROM books;
- Удаление из таблицы books всей информации о книгах, изданных до определенной даты, с возвратом их общего количества:
PROCEDURE remove_books (
date_in IN DATE,
removal_count_out OUT PLS_INTEGER)
IS
BEGIN
DELETE FROM books WHERE date_published < date_in; removal_count_out := SQL%ROWCOUNT;
END;
Конечно, все эти команды DML в реальных приложениях обычно бывают гораздо сложнее. Например, команда может обновлять сразу несколько столбцов с данными, сгенерированными вложенным запросом. Начиная с Oracle9i, имя таблицы можно заменить табличной функцией, возвращающей результирующий набор строк, с которыми работает команда DML.
Команда MERGE
В команде MERGE задается условие проверки, а также два действия для его выполнения (matched) или невыполнения (not matched). Пример:
PROCEDURE time_use_merge (dept_in IN employees.department_id%TYPE )
IS
BEGIN
MERGE INTO bonuses d
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = dept_in) s ON (d.employee_id = s.employee_id)
WHEN MATCHED THEN
UPDATE SET d.bonus = d.bonus + s.salary * .01 WHEN NOT MATCHED THEN
INSERT (d.employee_id, d.bonus)
VALUES (s.employee_id, s.salary * 0.2
END;
Атрибуты курсора для операций DML
Для доступа к информации о последней операции, выполненной командой SQL, Oracle предоставляет несколько атрибутов курсоров, неявно открываемых для этой операции. Атрибуты неявных курсоров возвращают информацию о выполнении команд INSERT, UPDATE, DELETE, MERGE или SELECT INTO. В этом разделе речь пойдет об использовании атрибутов SQL% для команд DML.
бедует помнить, что значения атрибутов неявного курсора всегда относятся к последней выполненной команде SQL, независимо от того, в каком блоке выполнялся неявный курсор. До открытия первого SQL-курсора сеанса значения всех неявных атрибутов равны NULL. (Исключение составляет атрибут %ISOPEN, который возвращает FALSE.) Значения, возвращаемые атрибутами неявных курсоров, описаны в табл. 1.
Таблица 1. Атрибуты неявных курсоров для команд DML
Давайте посмотрим, как эти атрибуты используются.
- С помощью атрибута SQL%FOUND можно определить, обработала ли команда DML хотя бы одну строку. Допустим, автор издает свои произведения под разными именами, а записи с информацией обо всех книгах данного автора необходимо время от времени обновлять. Эту задачу выполняет процедура, обновляющая данные столбца author и возвращающая логический признак, который сообщает, было ли произведено хотя бы одно обновление:
PROCEDURE change_author_name (
old_name_in IN books.author%TYPE,
new_name_in IN books.author%TYPE,
changes_made_out OUT BOOLEAN)
IS
BEGIN
UPDATE books
SET author = new_name_in
WHERE author = old_name_in;
changes_made_out := SQL%FOUND;
END;
- Атрибут SQL%ROWCOUNT позволяет выяснить, сколько строк обработала команда DML. Новая версия приведенной выше процедуры возвращает более полную информацию:
PROCEDURE change_author_name (
old_name_in IN books.author%TYPE,
new_name_in IN books.author%TYPE,
rename_count_out OUT PLS_INTEGER)
IS
BEGIN
UPDATE books
SET author = new_name_in
WHERE author = old_name_in;
rename_count_out := SQL%ROWCOUNT;
END;
Секция RETURNING в командах DML
Допустим, вы выполнили команду UPDATE или DELETE и хотите получить ее результаты для дальнейшей обработки. Вместо того чтобы выполнять отдельный запрос, можно включить в команду условие RETURNING, с которым нужная информация будет записана непосредственно в переменные программы. Это позволяет сократить сетевой трафик и затраты ресурсов сервера, а также свести к минимуму количество курсоров, открываемых и используемых приложением.
Рассмотрим несколько примеров, демонстрирующих эту возможность.
В следующем блоке секция RETURNING записывает в переменную новый оклад работника, вычисляемый командой UPDATE:
DECLARE
myname employees.last_name%TYPE; mysal employees.salary%TYPE;
BEGIN
FOR rec IN (SELECT * FROM employees)
LOOP
UPDATE employees
SET salary = salary * 1.5 WHERE employee_id = rec.employee_id RETURNING salary, last_name INTO mysal, myname;
DBMS_OUTPUT.PUT_LINE ('Новый оклад ' || myname || ' = ' || mysal);
END LOOP;
END;
Допустим, команда UPDATE изменяет более одной строки. В этом случае можно не просто сохранить возвращаемые значения в переменных, а записать их как элементы коллекции при помощи синтаксиса BULK COLLECT. Этот прием продемонстрирован на примере команды FORALL:
DECLARE
names name_varray; new_salaries number_varray;
BEGIN
populate_arrays (names, new_salaries);
FORALL indx IN names.FIRST .. names.LAST UPDATE compensation
SET salary = new_salaries ( indx)
WHERE last_name = names (indx)
RETURNING salary BULK COLLECT INTO new_salaries;
END;
DML и обработка исключений
Если в блоке PL/SQL инициируется исключение, Oracle не выполняет откат изменений, внесенных командами DML этого блока. Логическими транзакциями приложения должен управлять программист, который и определяет, какие действия следует выполнять в этом случае. Рассмотрим следующую процедуру:
PROCEDURE empty_library (
pre_empty_count OUT PLS_INTEGER)
IS
BEGIN
/* Реализация tabcount содержится в файле ch14_code.sql */
pre_empty_count := tabcount ('books');
DELETE FROM books;
RAISE NO_DATA_FOUND;
END;
Обратите внимание: перед инициированием исключения задается значение параметра OUT. Давайте запустим анонимный блок, вызывающий эту процедуру, и проанализируем результаты :
DECLARE
table_count NUMBER := -1;
BEGIN
INSERT INTO books VALUES (...); empty_library (table_count);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (tabcount ('books'));
DBMS_OUTPUT.put_line (table_count);
END;
Код выводит следующие значения:
0
-1
Как видите, исключение было инициировано, но строки из таблицы книг при этом остались удаленными; дело в том, что Oracle не выполняет автоматического отката изменений. С другой стороны, переменная table_count сохранила исходное значение. Таким образом, в программах, выполняющих операции DML, вы сами отвечаете за откат транзакции — а вернее, решаете, хотите ли вы выполнить откат. Принимая решение, примите во внимание следующие соображения:
- Если для блока выполняется автономная транзакция, в обработчике исключения необходимо произвести ее откат или закрепление (чаще откат).
- Для определения области отката используются точки сохранения. Можно произвести откат транзакции до конкретной точки сохранения, тем самым оставив часть изменений, внесенных в течение сеанса.
Если исключение передается за пределы «самого внешнего» блока (то есть остается необработанным), то в среде выполнения PL/SQL, и в частности в SQL*Plus, автоматически осуществляется откат транзакции, и все изменения отменяются.
DML и записи
В командах INSERT и DELETE можно использовать записи PL/SQL. Пример:
PROCEDURE set_book_info (book_in IN books%ROWTYPE)
IS
BEGIN
INSERT INTO books VALUES book_in;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE books SET ROW = book_in WHERE isbn = book_in.isbn;
END;
Это важное нововведение облегчает работу программиста по сравнению с работой на уровне отдельных переменных или полей записи. Во-первых, код становится более компактным — с уровня отдельных значений вы поднимаетесь на уровень записей. Нет
необходимости объявлять отдельные переменные или разбивать запись на поля при передаче данных команде DML. Во-вторых, повышается надежность кода — если вы работаете с записями типа %ROWTYPE и обходитесь без явных манипуляций с полями, то в случае модификации базовых таблиц и представлений вам придется вносить значительные изменения в программный код.
В разделе «Ограничения, касающиеся операций вставки и обновления» приведен список ограничений на использование записей в командах DML. Но сначала мы посмотрим, как использовать DML на основе записей в командах INSERT и UPDATE.
Вставка на основе записей
В командах INSERT записи можно использовать как для добавления единственной строки, так и для пакетной вставки (с использованием команды FORALL). Также возможно создание записей с помощью объявления %ROWTYPE на основе таблицы, в которую производится вставка, или явного объявления командой TYPE на основе типа данных, совместимого со структурой таблицы.
Приведем несколько примеров.
- Вставка в таблицу книг данных из записи с объявлением %ROWTYPE
DECLARE
my_book books%ROWTYPE;
BEGIN
my_book.isbn := '1-56592-335-9'; my_book.title := 'ORACLE PL/SQL PROGRAMMING';
my_book.summary := 'General user guide and reference'; my_book.author := 'FEUERSTEIN,
STEVEN AND BILL PRIBYL'; my_book.page_count := 1000;
INSERT INTO books VALUES my_book;
END;
Обратите внимание: имя записи не заключается в скобки. Если мы используем запись вида
INSERT INTO books VALUES (my_book);
Oracle выдаст сообщение об ошибке.
Также можно выполнить вставку данных, взятых из записи, тип которой определен программистом, но этот тип должен быть полностью совместим с определением %ROWTYPE
. Другими словами, нельзя вставить в таблицу запись, содержащую подмножество столбцов таблицы.
- Вставка с помощью команды FORALL — этим способом в таблицу вставляются коллекции записей.
Обновление на основе записей
Также существует возможность обновления целой строки таблицы по данным записи PL/SQL. В следующем примере для обновления строки таблицы books используется запись, созданная со спецификацией %ROWTYPE. Обратите внимание на ключевое слово ROW, которое указывает, что вся строка обновляется данными из записи:
/* Файл в Сети: record_updates.sql */
DECLARE
my_book books%ROWTYPE;
BEGIN
my_book.isbn := '1-56592-335-9'; my_book.title := 'ORACLE PL/SQL PROGRAMMING';
my_book.summary := 'General user guide and reference';
my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL'; my_book.page_count := 1000;
UPDATE books
SET ROW = my_book WHERE isbn = my_book.isbn;
END;
Существует несколько ограничений, касающихся обновления строк на основе записей.
- При использовании ключевого слова ROW должна обновляться вся строка. Возможность обновления подмножества столбцов пока отсутствует, но не исключено, что она появится в следующих версиях Oracle. Для любых полей, значения которых остались равными NULL, соответствующему столбцу будет присвоено значение NULL.
- Обновление не может выполняться с использованием вложенного запроса.
Использование записей с условием RETURNING
В команду DML может включаться секция RETURNING, возвращающая значения столбцов (и основанных на них выражений) из обработанных строк. Возвращаемые данные могут помещаться в запись и даже в коллекцию записей:
/* Файл в Сети: record_updates.sql */
DECLARE
my_book_new_info books%ROWTYPE; my_book_return_info books%ROWTYPE;
BEGIN
my_book_new_info.isbn := '1-56592-335-9'; my_book_new_info.title := 'ORACLE PL/SQL
PROGRAMMING'; my_book_new_info.summary := 'General user guide and reference';
my_book_new_info.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';
my_book_new_info.page_count := 1000;
UPDATE books
SET ROW = my_book_new_info WHERE isbn = my_book_new_info.isbn
RETURNING isbn, title, summary, author, date_published, page_count INTO
my_book_return_info;
END;
Заметьте, что в предложении RETURNING перечисляются все столбцы таблицы. К сожалению, Oracle пока не поддерживает синтаксис с символом *.
Ограничения, касающиеся операций вставки и обновления
Если вы захотите освоить операции вставки и обновления с использованием записей, имейте в виду, что на их применение существуют определенные ограничения.
- Переменная типа записи может использоваться либо (1) в правой части секции SET команды UPDATE; (2) в предложении VALUES команды INSERT; (3) в подразделе INTO секции RETURNING.
- Ключевое слово ROW используется только в левой части приложения SET. В этом случае других предложений SET быть не может (то есть нельзя задать предложение SET со строкой, а затем предложение SET с отдельным столбцом).
- При вставке записи не следует задавать значения отдельных столбцов.
- Нельзя указывать в команде INSERT или UPDATE запись, содержащую вложенную запись (или функцию, возвращающую вложенную запись).
- Записи не могут использоваться в динамически выполняемых командах DML (execute immediate). Это потребовало бы от Oracle поддержки динамической привязки типа записи PL/SQL с командой SQL. Oracle же поддерживает динамическую привязку только для типов данных SQL.