Команды DDL языка PL/SQL на примерах

Антон Меринов

Антон Меринов

Автор статьи. Интересы, навыки: Профессиональное администрирование СУБД Oracle Database, веб-разработка, IT-World. Подробнее.

 
 
 

Команды DML языка PL/SQLЯзык 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

Таблица 14Атрибуты неявных курсоров для команд 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.

 

 

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 14738 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4634 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Значения NULL в языке SQL: отс...
Значения NULL в языке SQL: отс... 2590 просмотров Antoni Mon, 21 Jun 2021, 19:57:18
SQL: Правила выполнения однота...
SQL: Правила выполнения однота... 1442 просмотров Дэйзи ак-Макарова Sat, 31 Jul 2021, 06:47:05
Войдите чтобы комментировать

Myk аватар
Myk ответил в теме #9260 5 года 5 мес. назад
Качественное объяснение, да! Insert, Update, Delete, Merge - легко работаем с командами на PL/sQL!
Doc аватар
Doc ответил в теме #9140 5 года 7 мес. назад
Шикарная статейка! Здорово все описано до мелочей! Мой Вам лайк! ;-)