Dml команды в oracle
Data manipulation language (DML) statements add, change, and delete Oracle Database table data. A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are.
3.1 About Data Manipulation Language (DML) Statements
Data manipulation language (DML) statements access and manipulate data in existing tables.
In the SQL*Plus environment, you can enter a DML statement after the SQL> prompt.
In the SQL Developer environment, you can enter a DML statement in the Worksheet. Alternatively, you can use the SQL Developer Connections frame and tools to access and manipulate data.
To see the effect of a DML statement in SQL Developer, you might have to select the schema object type of the changed object in the Connections frame and then click the Refresh icon.
The effect of a DML statement is not permanent until you commit the transaction that includes it. A transaction is a sequence of SQL statements that Oracle Database treats as a unit (it can be a single DML statement). Until a transaction is committed, it can be rolled back (undone). For more information about transactions, see "About Transaction Control Statements" .
Oracle Database SQL Language Reference for more information about DML statements
3.1.1 About the INSERT Statement
The INSERT statement inserts rows into an existing table.
The simplest recommended form of the INSERT statement has this syntax:
Every column in list_of_columns must have a valid value in the corresponding position in list_of_values . Therefore, before you insert a row into a table, you must know what columns the table has, and what their valid values are. To get this information using SQL Developer, see "Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer" . To get this information using SQL*Plus, use the DESCRIBE statement. For example:
The INSERT statement in Example 3-1 inserts a row into the EMPLOYEES table for an employee for which all column values are known.
You need not know all column values to insert a row into a table, but you must know the values of all NOT NULL columns. If you do not know the value of a column that can be NULL , you can omit that column from list_of_columns . Its value defaults to NULL .
The INSERT statement in Example 3-2 inserts a row into the EMPLOYEES table for an employee for which all column values are known except SALARY. For now, SALARY can have the value NULL . When you know the salary, you can change it with the UPDATE statement (see Example 3-4).
The INSERT statement in Example 3-3 tries to insert a row into the EMPLOYEES table for an employee for which LAST_NAME is not known.
Example 3-1 Using the INSERT Statement When All Information Is Available
Example 3-2 Using the INSERT Statement When Not All Information Is Available
В любой многопользовательской среде неизбежно ситуация, когда два пользователя захотят работать с одними и теми же данными в одно и то же время. База данных должна обеспечивать отсутствие такой возможности. Принцип изоляции из ACID теста гласит что база данных должна гарантировать что сессии не должны видеть результаты выполнения транзакций других сессий до тех пор, пока транзакция не завершена. Для обеспечения такого результата БД должна упорядочить параллельный доступ к данным; т.е. убедиться, что даже если сессии затребовали доступ к одним и тем же строкам, в реальности запросы выстраиваются в очередь и ждут своей очереди.
Упорядочивание доступа происходит за счёт работы механизма табличных и строчных блокировок. Блокировка в Oracle происходит полностью автоматически. Грубо говоря, проблемы возникают только если программа пытается взаимодействовать с механизмом блокировок с помощью плохо написанного кода, или если бизнес аналитики и бизнес-модель разработана неправильно где сессии будут пересекаться.
Общие и исключительные блокировки
Стандартный уровень блокировок в Oracle гарантирует максимальный уровень параллелизма. Это значит что если сессия обновляет одну строку – блокируется одна строка, ничего больше. Более того, строка блокируется только для изменения – остальные сессии могут считывать эту строку. Блокировка существует пока транзакция не завершена, либо COMMIT либо ROLLBACK. Это исключительная (exclusive) блокировка: первая сессия затребовала блокировку строки на изменение, остальные сессии которые хотят заблокировать строку для изменения должны ждать. Доступ для чтения разрешён – несмотря на то что строка изменяется сессией заблокировавшей строку, операции чтения будут использовать данные отката чтобы гарантировать недоступность данных неподтверждённых транзакций.
Только одна сессия может блокировать строку или таблицу исключительной блокировкой – но общие (shared) блокировки могут накладываться на один объект многими сессиями. Нет никакого смысла использовать общую блокировку для строки, так как единственной целью блокировки стрки это получение уникального доступа к данным для изменения. Общие блокировки накладываются на всё таблицу, и многие сессии могут наложить общую блокировку на одну и ту же таблицу. Общие блокировки нужны для того, чтобы предотвратить исключительную блокировку таблицы другими сессииями: вы не можете получить исключительную блокировку если уже существует общая блокировка. Эксклюзивная блокировка таблицы нужна для выполнения DDL команд. Вы не можете выполнить запрос которые изменяет объект (к примеру удалить столбец из таблицы) если хотя бы одна другая сессия заблокировала таблицу общей блокировкой.
Для выполнения DML команда над строками, сессия должна получить исключительные блокировки всех строк которые будут изменяться и общую блокировку для таблицы. Если другая сессия уже наложила исключительную блокировку на строки, сессия будет висеть пока блокировки не будут убраны командой COMMIT или ROLLBACK. Если другая сессия заблокировала таблицу общей блокировкой но исключительная блокировка наложена на другие строки – то всё в порядке оба запроса могут работать. Исключительная блокировка для таблицы может быть, но по умолчанию механизм блокировок не блокирует всю таблицу пока это не необходимо для DDL команды.
Все DML команды требуют минимум две блокировки: исключительная блокировка строки которая изменяется и общая блокировка для таблицы содержащей строку. Исключительная блокировка предотвращает взаимодействие других сессий с изменяемой строкой а общая блокировка предотвращает другие сессии от изменения таблицы с помощью DDL команды. Эти блокировки запрашиваются автоматически. Если DML запрос не может получить необходимые блокировки – сессия будет висеть до момента получения этих блокировок.
Для выполнения DDL команды – необходимо исключительная блокировка всего объекта. Эта блокировка не может быть получена пока не завершены все DML транзакции к таблице и не освобождена исключительные блокировки строк и общие блокировки таблицы. Исключительная блокировка необходимая для DDL команды запрашивается автоматически, но если нельзя заблокировать объект в текущий момент – обычно потому что другие сессии наложили общую блокировку – то запрос прекратит выполнение с ошибкой вместо ожидания.
Механизм размещения в очереди
Запросы на блокировку помещаются в очередь. Если сессия запросила блокировку и не может получить её так как другая сессия уже заблокировала строку или объект, сессия будет ждать. Может случить что несколько сессий ждут доступа для одной и той же строки или объекта – в этом случае, Oracle будет отслеживать порядок в котором сессии запрашивали блокировку. Когда сессия заблокировавшая объек или строку освобождает его – блокировка разрешается следующей сессии и так далее. Это называется механизм размещения в очереди (enqueue mechanism).
Если вы не хотите чтобы сессия ждала в очереди если нет возможности заблокировать объект, то единственным способом избежать этого будет использование WAIT или NOWAIT директивы в команде SELECT … FOR UPDATE. Обычный SELECT всегда выполнится успешно так как SELECT не требует каких-либо блокировок – но DML команда будет висеть. Команда SELECT… FOR UPDATE вернёт набор строк и заблокирует их исключительной блокировкой. Если строки уже заблокированы – команда будет ждать в очереди пока не освободятся блокировки, как обычная DML команда. Для того чтобы избежать зависания сессии можно использовать SELECT… FOR UPDATE NOWAIT или SELECT… FOR UPDATE WAIT где n это количетсво секунд ожидания. После получения блокировок с помощью команды SELECT FOR UPDATE вы можете выполнять DML команды без возможности зависания сессии.
Возможно добавить директиву SKIP LOCKED к команде SELECT FOR UPDATE, тогда запрос вернёт только те строки которые не заблокированы другими сессиями. Эта команда существовала и раньше но поддерживается только с версии 11g
Конкуренция блокировок
Когда сессия запрашивает блокировку для строки или объекта и не может её получить потому что объекты уже заблокированы другой сессией – сессия зависает. Это конкуренция блокировок, и она может плохо сказыватьяс на производительности базы данных так как все сессии в очереди ожидают блокировки. Некоторые блокировки могут быть необходимыми: природа приложения предполагает доступ разными пользователями к одним и тем же данным в один и тот же момент времени в результате работы. Некоторые же параллельные блокировки могут быть вызваны неправильно написанной программой.
Oracle предоставляет способ для обнаружения конкурирующих блокировок и также возможность разрешить проблему конкуренции в случае необходимости. Отдельным видом конкурирующих транзакций является deadlock. В случае возникновения таких ситуаций БД устраняет их автоматически.
Причины конкурирующих транзакций
Иногда бывает что бизнес-пользователям необходимо изменять данные в одних и тех же строках одновременно. Если это тормозит производительность системы, единственных выходом будет разработка новой бизнес-модели и новых бизнес-процессов. Но несмотря на то, что часть конкурирующих блокировок это правильное поведение системы – иногда конкуренция возникает из-за плохо разработанного приложения, что усугубляет проблему.
Длительные транзакции всегда будут вызывать проблемы. Типичным примером является случай, когда пользователь обновляет строку и не подтверждает транзакцию. Возможно пользователь даже ушёл на обед и не подтвердил транзакцию. Вы не сможете контролировать это если пользователи работают с помощью таких инструментов как SQL *Plus, но этого никогда не должно происходить в хорошо продуманном приложении. Программа должна учитывать что блокировка должна запрашиваться непосредственно перед изменением данных и освобождаться немедленно после завершения работы.
Некоторые программы запрашивают больше блокировок чем им необходимо. Например в некоторых программах для разработки всегда используется SELECT.. FOR UPDATE чтобы избежать необходимости перечитывать данные и проверять их на изменения. Некоторые инструменты для разработки не используют блокировку строки: если пользователь хочет обновить всего одну строку – компонент запрашивает блокировку для нескольких десятков или даже сотен строк. Если ваша программа написана с использованием таких компонентов – база данных Oracle будет именно то что вы сказали ей делать: блокировть много строк вместо одной, что не необходимо с точки зрения бизнес-логики.
Обнаружение и разрешение проблем вызванных конкурирующими транзакциями
Конкуренция блокировок это обычное последствие одновременного доступа разных пользователей к одним и тем же данным. Эта проблема может усиливаться из-за плохого приложения, но в принципе конкуренция блокировок это часть нормальной работы базы данных. Поэтому DBA не может решить эту проблему полностью – он может только указать что существует проблема, и предложить учитывать эту информацию при проектировании системы: разработке структур данных и процессов.
В экстренных случаях, DBA может разрешить конкретную ситуацию – отключив сессию, или сессии которые задерживают много блокировок слишком долго. Когда сессия отключается принудительно – все блокировки вызванные этой сессией освобождаются и транзакция отменяется. Заблокированные сессии смогут продолжать работу. Для отключения сессии можно использовать Database Control или команду ALTER SYSTEM KILL SESSION. В нашем примере если мы решим отключить пользователя SCOTT, можно выбрать эту строку и нажать кнопку KILL SESSION.
Deadlock
Возможно возникновение ситуации, когда две сессии блокируют друг друга таким образом что обе будут висеть вечно, каждая будет ожидать освобождения блокировок другой. Это называется deadlock. Deadlock-и это не проблема DBA: они могут быть вызваны только плохой архитектурой системы и разрешаются автоматически базой данных. Информация о возникновении deadlock-ов записываетя в системный журнал, с подробным описание в файле трассировки – частью обычного мониторинга базы данных является проверка на возникновение deadlock-ов и предоставление разработчиками подробной информации при каких обстоятельствах это произошло.
Вы должны понимать что deadlock-и возникают только по вине неправильно спроектированной программы. Код пытается выполнить что-то, что логически невозможно. Хорошо написанное приложени всегда вызывает блокировки последовательно таким образом, чтобы исключить возникновение deadlock-ов или будет проверять наличие несовместимых блокировок перед тем как их запрашивать.
Язык 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 , то права доступа определяются во время выполнения программы.
3.1 About Data Manipulation Language (DML) Statements
Data manipulation language (DML) statements access and manipulate data in existing tables.
In the SQL*Plus environment, you can enter a DML statement after the SQL> prompt.
In the SQL Developer environment, you can enter a DML statement in the Worksheet. Alternatively, you can use the SQL Developer Connections frame and tools to access and manipulate data.
To see the effect of a DML statement in SQL Developer, you might have to select the schema object type of the changed object in the Connections frame and then click the Refresh icon.
The effect of a DML statement is not permanent until you commit the transaction that includes it. A transaction is a sequence of SQL statements that Oracle Database treats as a unit (it can be a single DML statement). Until a transaction is committed, it can be rolled back (undone). For more information about transactions, see "About Transaction Control Statements" .
Oracle Database SQL Language Reference for more information about DML statements
3.1.1 About the INSERT Statement
The INSERT statement inserts rows into an existing table.
The simplest recommended form of the INSERT statement has this syntax:
Every column in list_of_columns must have a valid value in the corresponding position in list_of_values . Therefore, before you insert a row into a table, you must know what columns the table has, and what their valid values are. To get this information using SQL Developer, see "Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer" . To get this information using SQL*Plus, use the DESCRIBE statement. For example:
The INSERT statement in Example 3-1 inserts a row into the EMPLOYEES table for an employee for which all column values are known.
You need not know all column values to insert a row into a table, but you must know the values of all NOT NULL columns. If you do not know the value of a column that can be NULL , you can omit that column from list_of_columns . Its value defaults to NULL .
The INSERT statement in Example 3-2 inserts a row into the EMPLOYEES table for an employee for which all column values are known except SALARY. For now, SALARY can have the value NULL . When you know the salary, you can change it with the UPDATE statement (see Example 3-4).
The INSERT statement in Example 3-3 tries to insert a row into the EMPLOYEES table for an employee for which LAST_NAME is not known.
Example 3-1 Using the INSERT Statement When All Information Is Available
Example 3-2 Using the INSERT Statement When Not All Information Is Available
В любой многопользовательской среде неизбежно ситуация, когда два пользователя захотят работать с одними и теми же данными в одно и то же время. База данных должна обеспечивать отсутствие такой возможности. Принцип изоляции из ACID теста гласит что база данных должна гарантировать что сессии не должны видеть результаты выполнения транзакций других сессий до тех пор, пока транзакция не завершена. Для обеспечения такого результата БД должна упорядочить параллельный доступ к данным; т.е. убедиться, что даже если сессии затребовали доступ к одним и тем же строкам, в реальности запросы выстраиваются в очередь и ждут своей очереди.
Упорядочивание доступа происходит за счёт работы механизма табличных и строчных блокировок. Блокировка в Oracle происходит полностью автоматически. Грубо говоря, проблемы возникают только если программа пытается взаимодействовать с механизмом блокировок с помощью плохо написанного кода, или если бизнес аналитики и бизнес-модель разработана неправильно где сессии будут пересекаться.
Общие и исключительные блокировки
Стандартный уровень блокировок в Oracle гарантирует максимальный уровень параллелизма. Это значит что если сессия обновляет одну строку – блокируется одна строка, ничего больше. Более того, строка блокируется только для изменения – остальные сессии могут считывать эту строку. Блокировка существует пока транзакция не завершена, либо COMMIT либо ROLLBACK. Это исключительная (exclusive) блокировка: первая сессия затребовала блокировку строки на изменение, остальные сессии которые хотят заблокировать строку для изменения должны ждать. Доступ для чтения разрешён – несмотря на то что строка изменяется сессией заблокировавшей строку, операции чтения будут использовать данные отката чтобы гарантировать недоступность данных неподтверждённых транзакций.
Только одна сессия может блокировать строку или таблицу исключительной блокировкой – но общие (shared) блокировки могут накладываться на один объект многими сессиями. Нет никакого смысла использовать общую блокировку для строки, так как единственной целью блокировки стрки это получение уникального доступа к данным для изменения. Общие блокировки накладываются на всё таблицу, и многие сессии могут наложить общую блокировку на одну и ту же таблицу. Общие блокировки нужны для того, чтобы предотвратить исключительную блокировку таблицы другими сессииями: вы не можете получить исключительную блокировку если уже существует общая блокировка. Эксклюзивная блокировка таблицы нужна для выполнения DDL команд. Вы не можете выполнить запрос которые изменяет объект (к примеру удалить столбец из таблицы) если хотя бы одна другая сессия заблокировала таблицу общей блокировкой.
Для выполнения DML команда над строками, сессия должна получить исключительные блокировки всех строк которые будут изменяться и общую блокировку для таблицы. Если другая сессия уже наложила исключительную блокировку на строки, сессия будет висеть пока блокировки не будут убраны командой COMMIT или ROLLBACK. Если другая сессия заблокировала таблицу общей блокировкой но исключительная блокировка наложена на другие строки – то всё в порядке оба запроса могут работать. Исключительная блокировка для таблицы может быть, но по умолчанию механизм блокировок не блокирует всю таблицу пока это не необходимо для DDL команды.
Все DML команды требуют минимум две блокировки: исключительная блокировка строки которая изменяется и общая блокировка для таблицы содержащей строку. Исключительная блокировка предотвращает взаимодействие других сессий с изменяемой строкой а общая блокировка предотвращает другие сессии от изменения таблицы с помощью DDL команды. Эти блокировки запрашиваются автоматически. Если DML запрос не может получить необходимые блокировки – сессия будет висеть до момента получения этих блокировок.
Для выполнения DDL команды – необходимо исключительная блокировка всего объекта. Эта блокировка не может быть получена пока не завершены все DML транзакции к таблице и не освобождена исключительные блокировки строк и общие блокировки таблицы. Исключительная блокировка необходимая для DDL команды запрашивается автоматически, но если нельзя заблокировать объект в текущий момент – обычно потому что другие сессии наложили общую блокировку – то запрос прекратит выполнение с ошибкой вместо ожидания.
Механизм размещения в очереди
Запросы на блокировку помещаются в очередь. Если сессия запросила блокировку и не может получить её так как другая сессия уже заблокировала строку или объект, сессия будет ждать. Может случить что несколько сессий ждут доступа для одной и той же строки или объекта – в этом случае, Oracle будет отслеживать порядок в котором сессии запрашивали блокировку. Когда сессия заблокировавшая объек или строку освобождает его – блокировка разрешается следующей сессии и так далее. Это называется механизм размещения в очереди (enqueue mechanism).
Если вы не хотите чтобы сессия ждала в очереди если нет возможности заблокировать объект, то единственным способом избежать этого будет использование WAIT или NOWAIT директивы в команде SELECT … FOR UPDATE. Обычный SELECT всегда выполнится успешно так как SELECT не требует каких-либо блокировок – но DML команда будет висеть. Команда SELECT… FOR UPDATE вернёт набор строк и заблокирует их исключительной блокировкой. Если строки уже заблокированы – команда будет ждать в очереди пока не освободятся блокировки, как обычная DML команда. Для того чтобы избежать зависания сессии можно использовать SELECT… FOR UPDATE NOWAIT или SELECT… FOR UPDATE WAIT где n это количетсво секунд ожидания. После получения блокировок с помощью команды SELECT FOR UPDATE вы можете выполнять DML команды без возможности зависания сессии.
Возможно добавить директиву SKIP LOCKED к команде SELECT FOR UPDATE, тогда запрос вернёт только те строки которые не заблокированы другими сессиями. Эта команда существовала и раньше но поддерживается только с версии 11g
Конкуренция блокировок
Когда сессия запрашивает блокировку для строки или объекта и не может её получить потому что объекты уже заблокированы другой сессией – сессия зависает. Это конкуренция блокировок, и она может плохо сказыватьяс на производительности базы данных так как все сессии в очереди ожидают блокировки. Некоторые блокировки могут быть необходимыми: природа приложения предполагает доступ разными пользователями к одним и тем же данным в один и тот же момент времени в результате работы. Некоторые же параллельные блокировки могут быть вызваны неправильно написанной программой.
Oracle предоставляет способ для обнаружения конкурирующих блокировок и также возможность разрешить проблему конкуренции в случае необходимости. Отдельным видом конкурирующих транзакций является deadlock. В случае возникновения таких ситуаций БД устраняет их автоматически.
Причины конкурирующих транзакций
Иногда бывает что бизнес-пользователям необходимо изменять данные в одних и тех же строках одновременно. Если это тормозит производительность системы, единственных выходом будет разработка новой бизнес-модели и новых бизнес-процессов. Но несмотря на то, что часть конкурирующих блокировок это правильное поведение системы – иногда конкуренция возникает из-за плохо разработанного приложения, что усугубляет проблему.
Длительные транзакции всегда будут вызывать проблемы. Типичным примером является случай, когда пользователь обновляет строку и не подтверждает транзакцию. Возможно пользователь даже ушёл на обед и не подтвердил транзакцию. Вы не сможете контролировать это если пользователи работают с помощью таких инструментов как SQL *Plus, но этого никогда не должно происходить в хорошо продуманном приложении. Программа должна учитывать что блокировка должна запрашиваться непосредственно перед изменением данных и освобождаться немедленно после завершения работы.
Некоторые программы запрашивают больше блокировок чем им необходимо. Например в некоторых программах для разработки всегда используется SELECT.. FOR UPDATE чтобы избежать необходимости перечитывать данные и проверять их на изменения. Некоторые инструменты для разработки не используют блокировку строки: если пользователь хочет обновить всего одну строку – компонент запрашивает блокировку для нескольких десятков или даже сотен строк. Если ваша программа написана с использованием таких компонентов – база данных Oracle будет именно то что вы сказали ей делать: блокировть много строк вместо одной, что не необходимо с точки зрения бизнес-логики.
Обнаружение и разрешение проблем вызванных конкурирующими транзакциями
Конкуренция блокировок это обычное последствие одновременного доступа разных пользователей к одним и тем же данным. Эта проблема может усиливаться из-за плохого приложения, но в принципе конкуренция блокировок это часть нормальной работы базы данных. Поэтому DBA не может решить эту проблему полностью – он может только указать что существует проблема, и предложить учитывать эту информацию при проектировании системы: разработке структур данных и процессов.
В экстренных случаях, DBA может разрешить конкретную ситуацию – отключив сессию, или сессии которые задерживают много блокировок слишком долго. Когда сессия отключается принудительно – все блокировки вызванные этой сессией освобождаются и транзакция отменяется. Заблокированные сессии смогут продолжать работу. Для отключения сессии можно использовать Database Control или команду ALTER SYSTEM KILL SESSION. В нашем примере если мы решим отключить пользователя SCOTT, можно выбрать эту строку и нажать кнопку KILL SESSION.
Deadlock
Возможно возникновение ситуации, когда две сессии блокируют друг друга таким образом что обе будут висеть вечно, каждая будет ожидать освобождения блокировок другой. Это называется deadlock. Deadlock-и это не проблема DBA: они могут быть вызваны только плохой архитектурой системы и разрешаются автоматически базой данных. Информация о возникновении deadlock-ов записываетя в системный журнал, с подробным описание в файле трассировки – частью обычного мониторинга базы данных является проверка на возникновение deadlock-ов и предоставление разработчиками подробной информации при каких обстоятельствах это произошло.
Вы должны понимать что deadlock-и возникают только по вине неправильно спроектированной программы. Код пытается выполнить что-то, что логически невозможно. Хорошо написанное приложени всегда вызывает блокировки последовательно таким образом, чтобы исключить возникновение deadlock-ов или будет проверять наличие несовместимых блокировок перед тем как их запрашивать.
Язык 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 , то права доступа определяются во время выполнения программы.
Секция RETURNING в командах DML
Допустим, вы выполнили команду UPDATE или DELETE и хотите получить ее результаты для дальнейшей обработки. Вместо того чтобы выполнять отдельный запрос, можно включить в команду условие RETURNING, с которым нужная информация будет записана непосредственно в переменные программы. Это позволяет сократить сетевой трафик и затраты ресурсов сервера, а также свести к минимуму количество курсоров, открываемых и используемых приложением.
Рассмотрим несколько примеров, демонстрирующих эту возможность.
В следующем блоке секция RETURNING записывает в переменную новый оклад работника, вычисляемый командой UPDATE:
Допустим, команда UPDATE изменяет более одной строки. В этом случае можно не просто сохранить возвращаемые значения в переменных, а записать их как элементы коллекции при помощи синтаксиса BULK COLLECT. Этот прием продемонстрирован на примере команды FORALL:
Конструкции циклов в PL/SQL
Конструкции циклов в PL/SQL позволяют обеспечивать итеративное выполнение кода либо заданное количество раз, либо до тех пор, пока определенное условие не станет истинным или ложным. В следующих подразделах описываются основные виды этих конструкций.
Простой цикл
Конструкция простого цикла подразумевает помещение набора SQL-операторов между ключевыми словами LOOP и END LOOP. Оператор EXIT завершает цикл. Конструкция простого цикла применяется тогда, когда точно неизвестно, сколько раз должен выполняться цикл. В случае ее применения решение о том, когда цикл должен завершаться, принимается на основании содержащейся между операторами LOOP и END LOOP логики.
В следующем примере цикл будет выполняться до тех пор, пока значение quality_grade не достигнет 6:
Еще один простой вид цикла позволяет выполнять конструкция LOOP. EXIT. WHEN, в которой длительность цикла регулируется оператором WHEN. Внутри WHEN указывается условие, и когда это условие становится истинным, цикл завершается. Ниже показан простой пример:
Цикл WHILE
Цикл WHILE указывает, что определенный оператор должен выполняться до тех пор, пока определенное условие остается истинным. Обратите внимание на то, что условие вычисляется за пределами цикла, и вычисляется оно всякий раз, когда выполняются операторы, указанные между операторами LOOP и END LOOP. Когда условие перестает быть истинным, происходит выход из цикла. Ниже приведен пример цикла WHILE:
Цикл FOR
Цикл FOR применяется тогда, когда требуется, чтобы оператор выполнялся определенное количество раз. Он имитирует классический цикл do, который существует в большинстве языков программирования. Ниже приведен пример цикла FOR:
Процедуры, функции и пакеты
Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:
В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:
Пакеты (packages) в Oracle представляют собой объекты, которые обычно состоят из нескольких взаимосвязанных процедур и функций и, как правило, применяются для выполнения какой-нибудь функции приложения путем вызова всех находящихся внутри пакета взаимосвязанных процедур и функций. Пакеты являются чрезвычайно мощным средством, поскольку могут содержать большие объемы функционального кода и многократно выполняться несколькими пользователями.
Каждый пакет обычно состоит из двух частей: спецификации и тела. В спецификации пакета объявляются все входящие в его состав переменные, курсоры и подпрограммы (процедуры и функции), а в теле пакета содержится фактический код этих курсоров и подпрограмм.
В листинге А.6 приведен пример простого пакета Oracle.
При желании использовать пакет emp_pkg для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду:
Data manipulation language (DML) statements add, change, and delete Oracle Database table data. A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are.
Topics:
Data manipulation language (DML) statements access and manipulate data in existing tables.
A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are. You need transactions to model business processes that require that several operations be performed as a unit.
Committing a transaction makes its changes permanent, erases its savepoints, and releases its locks.
Rolling back a transaction undoes its changes. You can roll back the entire current transaction, or you can roll it back only to a specified savepoint.
The SAVEPOINT statement marks a savepoint in a transaction—a point to which you can later roll back. Savepoints are optional, and a transaction can have multiple savepoints.
Команда MERGE
В команде MERGE задается условие проверки, а также два действия для его выполнения (matched) или невыполнения (not matched). Пример:
Условное управление
Главной разновидностью условной управляющей структуры в PL/SQL является оператор IF, который обеспечивает условное выполнение операторов. Он может применяться в одной из трех следующих форм: IF-THEN, IF-THEN-ELSE и IF-THEN-ELSEIF. Ниже приведен пример простого оператора IF-THEN-ELSEIF:
Использование DML-операторов в PL/SQL
Любые операторы INSERT, DELETE или UPDATE работают в PL/SQL точно так же, как в обычном SQL. Однако в PL/SQL после каждого из них можно также применять оператор COMMIT, как показано ниже:
Записи в PL/SQL
Записи (records) в PL/SQL позволяют воспринимать взаимосвязанные данные как одно целое. Они могут содержать поля, каждое из которых может представлять отдельный элемент. Можно использовать атрибут ROW%TYPE и с его помощью объявлять записью столбцы определенной таблицы, что подразумевает применение таблицы в качестве шаблона курсора, а можно создавать и свои собственные записи. Ниже приведен простой пример записи:
Для ссылки на отдельное поле внутри записи применяется точечное обозначение, как показано ниже:
Ограничения, касающиеся операций вставки и обновления
Если вы захотите освоить операции вставки и обновления с использованием записей, имейте в виду, что на их применение существуют определенные ограничения.
Хотя язык SQL и является легким в изучении и обладает массой мощных функциональных возможностей, он не позволяет создавать такие процедурные конструкции, которые возможны в языках третьего поколения вроде C. Язык PL/SQL является собственным расширением языка SQL от Oracle и предлагает функциональность серьезного языка программирования. Одно из главных его преимуществ состоит в том, что он позволяет использовать в базе данных такие программные единицы, как процедуры и пакеты, и тем самым увеличивать возможность повторного использования кода и его производительность.
Управляющие структуры в PL/SQL
В PL/SQL предлагается несколько видов управляющих структур (control structures), которые позволяют обеспечивать итерацию кода или условное выполнение определенных операторов. Все они кратко описаны в последующих разделах моего блога.
3.1 About Data Manipulation Language (DML) Statements
Data manipulation language (DML) statements access and manipulate data in existing tables.
In the SQL*Plus environment, you can enter a DML statement after the SQL> prompt.
In the SQL Developer environment, you can enter a DML statement in the Worksheet. Alternatively, you can use the SQL Developer Connections frame and tools to access and manipulate data.
To see the effect of a DML statement in SQL Developer, you might have to select the schema object type of the changed object in the Connections frame and then click the Refresh icon.
The effect of a DML statement is not permanent until you commit the transaction that includes it. A transaction is a sequence of SQL statements that Oracle Database treats as a unit (it can be a single DML statement). Until a transaction is committed, it can be rolled back (undone). For more information about transactions, see "About Transaction Control Statements" .
Topics:
The INSERT statement inserts rows into an existing table.
The UPDATE statement updates (changes the values of) a set of existing table rows.
The DELETE statement deletes rows from a table.
Oracle Database SQL Language Reference for more information about DML statements
3.1.1 About the INSERT Statement
The INSERT statement inserts rows into an existing table.
The simplest recommended form of the INSERT statement has this syntax:
Every column in list_of_columns must have a valid value in the corresponding position in list_of_values . Therefore, before you insert a row into a table, you must know what columns the table has, and what their valid values are. To get this information using SQL Developer, see "Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer" . To get this information using SQL*Plus, use the DESCRIBE statement. For example:
The INSERT statement in Example 3-1 inserts a row into the EMPLOYEES table for an employee for which all column values are known.
You need not know all column values to insert a row into a table, but you must know the values of all NOT NULL columns. If you do not know the value of a column that can be NULL , you can omit that column from list_of_columns . Its value defaults to NULL .
The INSERT statement in Example 3-2 inserts a row into the EMPLOYEES table for an employee for which all column values are known except SALARY. For now, SALARY can have the value NULL . When you know the salary, you can change it with the UPDATE statement (see Example 3-4).
The INSERT statement in Example 3-3 tries to insert a row into the EMPLOYEES table for an employee for which LAST_NAME is not known.
Example 3-1 Using the INSERT Statement When All Information Is Available
Example 3-2 Using the INSERT Statement When Not All Information Is Available
DML и обработка исключений
Если в блоке PL/SQL инициируется исключение, Oracle не выполняет откат изменений, внесенных командами DML этого блока. Логическими транзакциями приложения должен управлять программист, который и определяет, какие действия следует выполнять в этом случае. Рассмотрим следующую процедуру:
Обратите внимание: перед инициированием исключения задается значение параметра OUT. Давайте запустим анонимный блок, вызывающий эту процедуру, и проанализируем результаты :
Код выводит следующие значения:
Как видите, исключение было инициировано, но строки из таблицы книг при этом остались удаленными; дело в том, что Oracle не выполняет автоматического отката изменений. С другой стороны, переменная table_count сохранила исходное значение. Таким образом, в программах, выполняющих операции DML, вы сами отвечаете за откат транзакции — а вернее, решаете, хотите ли вы выполнить откат. Принимая решение, примите во внимание следующие соображения:
- Если для блока выполняется автономная транзакция, в обработчике исключения необходимо произвести ее откат или закрепление (чаще откат).
- Для определения области отката используются точки сохранения. Можно произвести откат транзакции до конкретной точки сохранения, тем самым оставив часть изменений, внесенных в течение сеанса.
Если исключение передается за пределы «самого внешнего» блока (то есть остается необработанным), то в среде выполнения PL/SQL, и в частности в SQL*Plus, автоматически осуществляется откат транзакции, и все изменения отменяются.
Атрибуты курсора для операций DML
Для доступа к информации о последней операции, выполненной командой SQL, Oracle предоставляет несколько атрибутов курсоров, неявно открываемых для этой операции. Атрибуты неявных курсоров возвращают информацию о выполнении команд INSERT, UPDATE, DELETE, MERGE или SELECT INTO. В этом разделе речь пойдет об использовании атрибутов SQL% для команд DML.
бедует помнить, что значения атрибутов неявного курсора всегда относятся к последней выполненной команде SQL, независимо от того, в каком блоке выполнялся неявный курсор. До открытия первого SQL-курсора сеанса значения всех неявных атрибутов равны NULL. (Исключение составляет атрибут %ISOPEN, который возвращает FALSE.) Значения, возвращаемые атрибутами неявных курсоров, описаны в табл. 1.
Таблица 1. Атрибуты неявных курсоров для команд DML
Давайте посмотрим, как эти атрибуты используются.
- С помощью атрибута SQL%FOUND можно определить, обработала ли команда DML хотя бы одну строку. Допустим, автор издает свои произведения под разными именами, а записи с информацией обо всех книгах данного автора необходимо время от времени обновлять. Эту задачу выполняет процедура, обновляющая данные столбца author и возвращающая логический признак, который сообщает, было ли произведено хотя бы одно обновление:
- Атрибут SQL%ROWCOUNT позволяет выяснить, сколько строк обработала команда DML. Новая версия приведенной выше процедуры возвращает более полную информацию:
Явные курсоры
Явные курсоры создаются разработчиком приложения и облегчают операции с набором строк, которые могут обрабатываться друг за другом. Они применяются всегда, когда известно, что SQL-оператор будет возвращать более одной строки. Обратите внимание, что явный курсор необходимо всегда объявлять в начале блока PL/SQL внутри раздела DECLARE, в отличие от неявного курсора, на который никогда не нужно ссылаться в коде.
После объявления явного курсора он будет проходить через следующие этапы обработки.
- Конструкция OPEN будет определять строки, которые находятся в курсоре, и делать их доступными для программы PL/SQL.
- Команда FETCH будет извлекать данные из курсора в указанную переменную.
- По завершении процесса обработки курсор должен всегда закрываться явным образом.
В листинге А.4 показан пример создания курсора и затем его использования внутри цикла.
Курсорный цикл FOR
Обычно при использовании явных курсоров требуется открывать курсор, извлекать данные и по завершении закрывать курсор. Курсорный цикл FOR позволяет выполнять эти процедуры по открытию, извлечению и закрытию автоматически, чем очень сильно упрощает дело. В листинге А.5 показан пример применения конструкции курсорного цикла FOR.
Написание исполняемых операторов PL/SQL
После оператора BEGIN можно начинать вводить все свои желаемые SQL-операторы. Выглядеть эти операторы должны точно так же, как обычные операторы SQL. При использовании операторов SELECT и INSERT в PL/SQL, правда, необходимо помнить об особенностях, о которых более подробно речь пойдет в следующих разделах.
Использование записей с условием RETURNING
В команду DML может включаться секция RETURNING, возвращающая значения столбцов (и основанных на них выражений) из обработанных строк. Возвращаемые данные могут помещаться в запись и даже в коллекцию записей:
Заметьте, что в предложении RETURNING перечисляются все столбцы таблицы. К сожалению, Oracle пока не поддерживает синтаксис с символом *.
Команда INSERT
Существует две базовые разновидности команды INSERT:
О Вставка одной строки с явно заданным списком значений:
О Вставка в таблицу одной или нескольких строк, определяемых командой SELECT, которая извлекает данные из других таблиц:
Рассмотрим несколько примеров команд INSERT в блоке PL/SQL. Начнем со вставки новой строки в таблицу books. Обратите внимание: если в секции VALUES заданы значения всех столбцов, то список столбцов можно опустить:
Можно также задать список имен столбцов, а их значения указать в виде переменных, а не литералов:
ВСТРОЕННАЯ ПОДДЕРЖКА ПОСЛЕДОВАТЕЛЬНОСТЕЙ В ORACLE11G
До выхода Oracle11 программисту, желавшему получить следующее значение из последовательности, приходилось вызывать функцию NEXTVAL в команде SQL. Это
можно было сделать прямо в команде INSERT, которой требовалось значение:
или в команде SELECT из старой доброй таблицы dual:
Начиная с Oracle11g, следующее (и текущее) значение можно получить при помощи оператора присваивания — например:
Использование курсоров
Курсором (cursor) в Oracle называется указатель на область в памяти, в которой содержится результирующий набор SQL-запроса, позволяющий индивидуально обрабатывать содержащиеся в результирующем наборе строки. Курсоры, которые используются Oracle при выполнении DML-операторов, называются неявными, а курсоры, которые создают и используют разработчики приложений — явными.
DML и записи
В командах INSERT и DELETE можно использовать записи PL/SQL. Пример:
Это важное нововведение облегчает работу программиста по сравнению с работой на уровне отдельных переменных или полей записи. Во-первых, код становится более компактным — с уровня отдельных значений вы поднимаетесь на уровень записей. Нет
необходимости объявлять отдельные переменные или разбивать запись на поля при передаче данных команде DML. Во-вторых, повышается надежность кода — если вы работаете с записями типа %ROWTYPE и обходитесь без явных манипуляций с полями, то в случае модификации базовых таблиц и представлений вам придется вносить значительные изменения в программный код.
В разделе «Ограничения, касающиеся операций вставки и обновления» приведен список ограничений на использование записей в командах DML. Но сначала мы посмотрим, как использовать DML на основе записей в командах INSERT и UPDATE.
Краткое введение в DML
Полное описание всех возможностей DML в языке Oracle SQL выходит за рамки моей статьи, поэтому мы ограничимся кратким обзором базового синтаксиса, а затем изучим специальные возможности PL/SQL, относящиеся к DML, включая:
- примеры команд MDL;
- атрибуты курсоров команд DML;
- синтаксис PL/SQL, относящийся к DML, например конструкция RETURNING.
За более подробной информацией обращайтесь к документации Oracle и другим описаниям SQL.
Формально команда SELECT считается командой DML. Однако разработчики под термином «DML» почти всегда понимают команды, изменяющие содержимое таблицы базы данных (то есть не связанные с простым чтением данных).
В языке SQL определены четыре команды DML:
- INSERT — вставляет в таблицу одну или несколько новых строк.
- UPDATE — обновляет в одной или нескольких существующих строках таблицы значения одного или нескольких столбцов.
- DELETE — удаляет из таблицы одну или несколько строк.
- MERGE — если строка с заданными значениями столбцов уже существует, выполняет обновление. В противном случае выполняется вставка.
Неявные курсоры
В приведенном ниже блоке кода PL/SQL оператор SELECT, например, предусматривает применение неявного курсора:
Курсорные переменные
Курсорные переменные указывают на текущую строку в многострочном результирующем наборе. В отличие от обычного курсора, однако, курсорная переменная является динамической, что позволяет присваивать ей новые значения и передавать ее другим процедурами и функциям. Создаются курсорные переменные в PL/SQL следующим образом.
Сначала определяется тип REF CURSOR, как показано ниже:
Затем объявляются сами курсорные переменные типа EmpCurType в анонимном блоке кода PL/SQL либо в процедуре (или функции):
Объявление переменных в PL/SQL
В операторе DECLARE можно объявлять как переменные, так и константы. Прежде чем использовать какую-либо переменную ее нужно обязательно объявить. Переменная в PL/SQL может представлять собой как переменную встроенного типа, такого как DATE, NUMBER, VARCHAR2 или CHAR, так и составного вроде VARRAY. Помимо этого, в PL/SQL еще применяются такие типы данных, как BINARY_INTEGER и BOOLEAN.
Ниже приведены некоторые типичные примеры объявления переменной в PL/SQL:
Помимо переменных также можно объявлять и константы, как показано в следующем примере:
Еще можно использовать атрибут %TYPE и с его помощью указывать при объявлении переменной, что ее тип данных должен совпадать с типом данных определенного столбца таблицы:
Посредством атрибута %ROWTYPE можно указывать, что тип данных записи (строки) должен совпадать с типом данных определенной таблицы базы данных. Например, в следующем коде указано, что запись DeptRecord должна содержать все те же столбцы, что и таблица department, а типы данных и длина этих столбцов в ней должны выглядеть абсолютно идентично:
Команда UPDATE
Команда UPDATE обновляет один или несколько столбцов или одну или несколько строк таблицы. Ее синтаксис выглядит так:
Предложение WHERE не обязательно; если оно не задано, обновляются все строки таблицы.
Несколько примеров команды UPDATE:
- Перевод названий книг таблицы books в верхний регистр:
- Выполнение процедуры, удаляющей компонент времени из даты издания книг, которые были написаны заданным автором, и переводящей названия этих книг в символы верхнего регистра. Как видно из примера, команду UPDATE можно выполнять как саму по себе, так и в блоке PL/SQL:
Обработка ошибок
В PL/SQL любая ошибка или предупреждение называется исключением (exception). В PL/SQL есть кое-какие определенные внутренне ошибки, но также допускается определять и свои собственные. При возникновении любой ошибки инициируется исключение, и управление переходит в отвечающий за обработку исключений раздел программы PL/SQL. В случае определения своих собственных ошибочных ситуаций необходимо обеспечивать инициирование исключений за счет применения специального оператора RAISE.
Ниже приведен пример использования оператора RAISE для обработки исключений:
Атрибуты курсоров
В примере, приведенном в листинге А.4, для указания того, когда цикл должен завершаться, используется специальный атрибут курсора %NOTFOUND. Атрибуты курсоров очень полезны при работе с явными курсорами. Наиболее важные из них перечислены ниже.
- %ISOPEN. Булевский атрибут, который после завершения выполнения SQL-оператора возвращает false. До тех пор, пока курсор остается открытым, он возвращает true.
- %FOUND. Булевский атрибут, который выполняет проверку на предмет наличия подходящих для SQL-оператора строк, т.е. остались ли у курсора еще какие-то строки для извлечения.
- %NOTFOUND. Булевский атрибут, который сообщает о том, что не удалось обнаружить ни одной подходящей для SQL-оператора строки, т.е. у курсора больше не осталось никаких строк для извлечения.
- %ROWCOUNT. Атрибут, который возвращает информацию о том, сколько курсору удалось извлечь строк на текущий момент.
Обновление на основе записей
Также существует возможность обновления целой строки таблицы по данным записи PL/SQL. В следующем примере для обновления строки таблицы books используется запись, созданная со спецификацией %ROWTYPE. Обратите внимание на ключевое слово ROW, которое указывает, что вся строка обновляется данными из записи:
Существует несколько ограничений, касающихся обновления строк на основе записей.
- При использовании ключевого слова ROW должна обновляться вся строка. Возможность обновления подмножества столбцов пока отсутствует, но не исключено, что она появится в следующих версиях Oracle. Для любых полей, значения которых остались равными NULL, соответствующему столбцу будет присвоено значение NULL.
- Обновление не может выполняться с использованием вложенного запроса.
Базовый блок PL/SQL
Блоком в PL/SQL называется исполняемая программа. Блок кода PL/SQL, независимо от того, инкапсулируется он внутри какой-то программной единицы наподобие процедуры или задается в виде анонимного блока в свободной форме, состоит из следующих структур, которые представляют собой четыре ключевых оператора, только два из которых являются обязательными.
- DECLARE . Этот оператор является необязательным и представляет собой то место, в котором при желании объявляются переменные и курсоры программы.
- BEGIN . Этот оператор является обязательным и указывает, что далее будут идти операторы SQL и PL/SQL, т.е. обозначает начало блока кода PL/SQL.
- EXCEPTION . Этот оператор является необязательным и описывает методы обработки ошибок.
- END . Этот оператор является обязательными и обозначает конец блока кода PL/SQL.
Ниже приведен пример простого блока кода PL/SQL:
Команда DELETE
Команда DELETE удаляет одну, несколько или все строки таблицы. Базовый синтаксис:
Условие WHERE не обязательно; если оно не задано, удаляются все строки таблицы. Несколько примеров команды DELETE:
- Удаление всей информации из таблицы books:
- Удаление из таблицы books всей информации о книгах, изданных до определенной даты, с возвратом их общего количества:
Конечно, все эти команды DML в реальных приложениях обычно бывают гораздо сложнее. Например, команда может обновлять сразу несколько столбцов с данными, сгенерированными вложенным запросом. Начиная с Oracle9i, имя таблицы можно заменить табличной функцией, возвращающей результирующий набор строк, с которыми работает команда DML.
Вставка на основе записей
В командах INSERT записи можно использовать как для добавления единственной строки, так и для пакетной вставки (с использованием команды FORALL). Также возможно создание записей с помощью объявления %ROWTYPE на основе таблицы, в которую производится вставка, или явного объявления командой TYPE на основе типа данных, совместимого со структурой таблицы.
Приведем несколько примеров.
- Вставка в таблицу книг данных из записи с объявлением %ROWTYPE
Обратите внимание: имя записи не заключается в скобки. Если мы используем запись вида
Также можно выполнить вставку данных, взятых из записи, тип которой определен программистом, но этот тип должен быть полностью совместим с определением %ROWTYPE . Другими словами, нельзя вставить в таблицу запись, содержащую подмножество столбцов таблицы.
- Вставка с помощью команды FORALL — этим способом в таблицу вставляются коллекции записей.
Использование оператора SELECT в PL/SQL
При использовании оператора SELECT в PL/SQL нужно сохранять извлекаемые значения в переменных, как показано ниже:
Читайте также: