Oracle sql удалить строку

Обновлено: 04.07.2024

The DELETE statement removes entire rows of data from a specified table or view. For a full description of the DELETE statement, see Oracle Database SQL Reference.

Syntax

Keyword and Parameter Description

Another (usually short) name for the referenced table or view. Typically referred to later in the WHERE clause.

BULK COLLECT

Returns columns from the deleted rows into PL/SQL collections, as specified by the RETURNING INTO list. The corresponding columns must store scalar (not composite) values. For more information, see "Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)".

returning_clause

Returns values from the deleted rows, eliminating the need to SELECT the rows first. You can retrieve the column values into individual variables or into collections. You cannot use the RETURNING clause for remote or parallel deletes. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined.

subquery

A SELECT statement that provides a set of rows for processing. Its syntax is like the select_into_statement without the INTO clause. See "SELECT INTO Statement".

table_reference

A table or view, which must be accessible when you execute the DELETE statement, and for which you must have DELETE privileges.

TABLE (subquery2)

The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table. Operator TABLE informs Oracle that the value is a collection, not a scalar value.

WHERE CURRENT OF cursor_name

Refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name . The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error.

If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND .

WHERE search_condition

Conditionally chooses rows to be deleted from the referenced table or view. Only rows that meet the search condition are deleted. If you omit the WHERE clause, all rows in the table or view are deleted.

Usage Notes

You can use the DELETE WHERE CURRENT OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FOR UPDATE . This statement deletes the current row; that is, the one just fetched.

The implicit cursor SQL and the cursor attributes %NOTFOUND , %FOUND , and %ROWCOUNT let you access useful information about the execution of a DELETE statement.

Examples

The following statement deletes the rows that match a condition:

The following statement returns two column values from a deleted row into local variables:

You can combine the BULK COLLECT clause with a FORALL statement, in which case, the SQL engine bulk-binds column values incrementally. In the following example, if collection depts has 3 elements, each of which causes 5 rows to be deleted, then collection enums has 15 elements when the statement completes:

The column values returned by each execution are added to the values returned previously.

В Oracle PL/SQL метод DELETE удаляет все элементы из коллекции любого типа. Эта операция немедленно освобождает память, выделенную для удаляемых элементов.

Синтаксис

Синтаксис метода коллекций DELETE в Oracle PL/SQL.

Параметры или аргументы

collection_name - имя коллекции один из следующих типов associative arrays или nested tables.
DELETE(n) - удаляет элемент, индекс которого равен n , если этот элемент существует; в противном случае он ничего не делает.
DELETE(m,n) удаляет все элементы, индексы которых находятся в диапазоне m..n , если существуют как m , так и n , а m ; в противном случае он ничего не делает.

Примечание

Пример

Рассмотрим некоторые пример, чтобы понять как использовать метод коллекций DELETE в Oracle PL/SQL.

Пример метода DELETE с Nested Tables

В этом примере мы используем Nested Tables и делаем следующее:

объявляем переменную Nested Tables
инициализируем ее шестью элементами
удаляем и затем восстанавливаем второй элемент
удаляем ряд элементов, а затем восстанавливаем один из них
и в конце удаляем все элементы

Восстановленные элементы занимают ту же память, что и соответствующие удаленные элементы. Процедура print_nt печатает переменную Nested Tables после инициализации и после каждой операции DELETE.

CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS

Пример метода DELETE с Associative Arrays

Следующий пример заполняет Associative Arrays, индексированный строкой, и удаляет все элементы, что освобождает выделенную им память. Затем пример заменяет удаленные элементы, то есть добавляет новые элементы, которые имеют те же индексы, что и удаленные элементы. Новые элементы замены не занимают ту же память, что и соответствующие удаленные элементы. Наконец, пример удаляет один элемент, а затем ряд элементов. Процедура print_aa_str показывает эффекты операций.

SQL оператор DELETE используется для удаления одной или нескольких записей из таблицы.

Синтаксис

Синтаксис оператора DELETE в SQL:

Параметры или аргументы

table Таблица, из которой вы хотите удалить записи. WHERE conditions Необязательный. Условия, которые должны быть выполнены для удаления записей. Если никаких условий не предусмотрено, все записи в таблице будут удалены.

Примечание

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

Пример оператора DELETE с одним условием

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

Давайте начнем с простого примера запроса DELETE, который имеет одно условие в предложении WHERE.

В этом примере у нас есть таблица suppliers со следующими данными:

supplier_id	supplier_name	city	state
100	Yandex	Moscow	Moscow
200	Google	Lansing	Michigan
300	Oracle	Redwood City	California
400	Bing	Redmond	Washington
500	Yahoo	Sunnyvale	Washington
600	DuckDuckGo	Paoli	Pennsylvania
700	Qwant	Paris	Ile de France
800	Facebook	Menlo Park	California
900	Electronic Arts	San Francisco	California

Введите следующий оператор DELETE:

Будет удалена 1 запись. Снова выберите данные из таблицы поставщиков:

Вот результаты, которые вы должны получить:

supplier_id	supplier_name	city	state
200	Google	Lansing	Michigan
300	Oracle	Redwood City	California
400	Bing	Redmond	Washington
500	Yahoo	Sunnyvale	Washington
600	DuckDuckGo	Paoli	Pennsylvania
700	Qwant	Paris	Ile de France
800	Facebook	Menlo Park	California
900	Electronic Arts	San Francisco	California

В этом примере удаляются все записи из таблицы suppliers , где supplier_name - Yandex.

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

Этот запрос вернет количество записей, которые будут удалены при выполнении оператора DELETE.

Пример - оператор DELETE с более чем одним условием

Вы можете иметь более одного условия в инструкции DELETE в SQL, используя либо условие AND, либо условие OR. Условие AND позволяет вам удалить запись, если все условия выполнены. Условие OR удаляет запись, если выполняется одно из условий.

Давайте рассмотрим пример использования оператора DELETE с двумя условиями с использованием условия AND.
В этом примере у нас есть таблица products со следующими данными:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Введите следующий оператор DELETE:

Будет удалены 3 записи. Снова выберите данные из таблицы products :

Вот результаты, которые вы получите:

product_id	product_name	category_id
1	Pear	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

В этом примере удаляются все записи из таблицы products , у которых category_id равен 50, а product_name НЕ 'Pear'.

Пример - использование EXISTS с оператором DELETE

Вы также можете выполнять более сложные удаления.

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

For you to delete rows from a table, the table must be in your own schema or you must have the DELETE object privilege on the table.

For you to delete rows from an updatable materialized view, the materialized view must be in your own schema or you must have the DELETE object privilege on the materialized view.

For you to delete rows from the base table of a view, the owner of the schema containing the view must have the DELETE object privilege on the base table. Also, if the view is in a schema other than your own, you must have the DELETE object privilege on the view.

The DELETE ANY TABLE system privilege also allows you to delete rows from any table or table partition or from the base table of any view.

You must also have the SELECT object privilege on the object from which you want to delete if:

The object is on a remote database or

The SQL92_SECURITY initialization parameter is set to TRUE and the DELETE operation references table columns, such as the columns in a where_clause

Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.

Use the FROM clause to specify the database objects from which you are deleting rows.

The ONLY syntax is relevant only for views. Use the ONLY clause if the view in the FROM clause belongs to a view hierarchy and you do not want to delete rows from any of its subviews.

Use this clause to specify the objects from which data is being deleted.

Specify the schema containing the table or view. If you omit schema , then Oracle Database assumes the table or view is in your own schema.

table | view | materialized view | subquery

Specify the name of a table, view, materialized view, or the column or columns resulting from a subquery, from which the rows are to be deleted.

When you delete rows from an updatable view, Oracle Database deletes rows from the base table.

You cannot delete rows from a read-only materialized view. If you delete rows from a writable materialized view, then the database removes the rows from the underlying container table. However, the deletions are overwritten at the next refresh operation. If you delete rows from an updatable materialized view that is part of a materialized view group, then the database also removes the corresponding rows from the master table.

If table or the base table of view or the master table of materialized_view contains one or more domain index columns, then this statements executes the appropriate indextype delete routine.

Oracle Data Cartridge Developer's Guide for more information on these routines

Issuing a DELETE statement against a table fires any DELETE triggers defined on the table.

All table or index space released by the deleted rows is retained by the table and index.

Specify the name of the partition or subpartition targeted for deletes within the object.

You need not specify the partition name when deleting values from a partitioned object. However, in some cases, specifying the partition name is more efficient than a complicated where_clause .

Specify the complete or partial name of a database link to a remote database where the object is located. You can delete rows from a remote object only if you are using Oracle Database distributed functionality.

If you omit dblink , then the database assumes that the object is located on the local database.

The subquery_restriction_clause lets you restrict the subquery in one of the following ways:

WITH READ ONLY Specify WITH READ ONLY to indicate that the table or view cannot be updated.

WITH CHECK OPTION Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.

CONSTRAINT constraint Specify the name of the CHECK OPTION constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form SYS_C n , where n is an integer that makes the constraint name unique within the database.

The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting .

The optional plus (+) is relevant if you are joining the TABLE expression with the parent table. The + creates an outer join of the two, so that the query returns rows from the outer table even if the collection expression is null.

In earlier releases of Oracle, when collection_expression was a subquery, table_collection_expression was expressed as THE subquery . That usage is now deprecated.

You can use a table_collection_expression in a correlated subquery to delete rows with values that also exist in another table.

collection_expression Specify a subquery that selects a nested table column from the object from which you are deleting.

Restrictions on the dml_table_expression_clause Clause This clause is subject to the following restrictions:

You cannot execute this statement if table or the base or master table of view or materialized_view contains any domain indexes marked IN_PROGRESS or FAILED.

You cannot insert into a partition if any affected index partitions are marked UNUSABLE .

You cannot specify the ORDER BY clause in the subquery of the DML_table_expression_clause .

You cannot delete from a view except through INSTEAD OF triggers if the defining query of the view contains one of the following constructs:

If you specify an index, index partition, or index subpartition that has been marked UNUSABLE , the DELETE statement will fail unless the SKIP_UNUSABLE_INDEXES initialization parameter has been set to true .

Use the where_clause to delete only rows that satisfy the condition. The condition can reference the object from which you are deleting and can contain a subquery. You can delete rows from a remote object only if you are using Oracle Database distributed functionality. Please refer to Chapter 7, "Conditions" for the syntax of condition .

If this clause contains a subquery that refers to remote objects, then the DELETE operation can run in parallel as long as the reference does not loop back to an object on the local database. However, if the subquery in the DML_table_expression_clause refers to any remote objects, then the DELETE operation will run serially without notification. Please refer to the parallel_clause in the CREATE TABLE documentation for additional information.

If you omit dblink , then the database assumes that the table or view is located on the local database.

If you omit the where_clause , then the database deletes all rows of the object.

t_alias Provide a correlation name for the table, view, materialized view, subquery, or collection value to be referenced elsewhere in the statement. This alias is required if the DML_table_expression_clause references any object type attributes or object type methods. Table aliases are generally used in DELETE statements with correlated queries.

This clause lets you return values from deleted columns, and thereby eliminate the need to issue a SELECT statement following the DELETE statement.

The returning clause retrieves the rows affected by a DML statement. You can specify this clause for tables and materialized views and for views with a single base table.

When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.

When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays.

expr Each item in the expr list must be a valid expression syntax.

INTO The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.

data_item Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value.

For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.

Restrictions The following restrictions apply to the RETURNING clause:

The expr is restricted as follows:

For UPDATE and DELETE statements each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause . For INSERT statements, each expr must be a simple expression. Aggregate functions are not supported in an INSERT statement RETURNING clause.

Single-set aggregate function expressions cannot include the DISTINCT keyword.

If the expr list contains a primary key column or other NOT NULL column, then the update statement fails if the table has a BEFORE UPDATE trigger defined on it.

You cannot specify the returning_clause for a multitable insert.

You cannot use this clause with parallel DML or with remote objects.

You cannot retrieve LONG types with this clause.

You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.

PL/SQL User's Guide and Reference for information on using the BULK COLLECT clause to return multiple values to collection variables

The error_logging_clause has the same behavior in DELETE statement as it does in an INSERT statement. Please refer to the INSERT statement error_logging_clause for more information.

Deleting Rows: Examples The following statement deletes all rows from the sample table oe.product_descriptions where the value of the language_id column is AR :

The following statement deletes from the sample table hr.employees purchasing clerks whose commission rate is less than 10%:

The following statement has the same effect as the preceding example, but uses a subquery:

Deleting Rows from a Remote Database: Example The following statement deletes specified rows from the locations table owned by the user hr on a database accessible by the database link remote :

Deleting Nested Table Rows: Example For an example that deletes nested table rows, please refer to "Table Collections: Examples".

Deleting Rows from a Partition: Example The following example removes rows from partition sales_q1_1998 of the sh.sales table:

Using the RETURNING Clause: Example The following example returns column salary from the deleted rows and stores the result in bind variable :bnd1 . The bind variable must already have been declared.

Читайте также: