AntDB-Oracle Compatibility Developer's Manual P3–14
Published: 2023-09-01 08:02:44

#### DELETE

**Name**

DELETE -- delete a record from a table.

**Summary**

DELETE FROM table

\[ WHERE condition ]

\[ RETURNING return\_expression \[, ...]

{ INTO { record \| variable \[, ...] }

\| BULK COLLECT INTO collection \[, ...] } ]

**Description**

The DELETE command is used to delete the rows from the specified table that meet the conditions of the WHERE clause. If the WHERE clause is not used, then all rows in the table are deleted. The result of the command is valid but clears the table of all its contents.

Note: The TRUNCATE command provides a faster mechanism for deleting all rows from a table.

RETURNING INTO { record \|variable \[, ...] can be specified only if the DELETE command is used in an SPL program. } clause, except that the result set of the DELETE command can have only one record, otherwise an exception is thrown. If the result set is empty, then the content of the target record or variable will be set to null.

Only use the DELETE command in an SPL program to specify the RETURNING BULK COLLECT INTO collection \[, ...] clause. If more than one collection is specified as the target of the BULK COLLECT INTO clause, then each collection must consist of a separate scalar field and the collection cannot be a record. The result set of the DELETE command can either contain no records or one to more records. The return\_expression that is computed for each record in the result set becomes a member of a collection, and the position of this collection starts with the first member. Any records that already exist in the set are deleted. If the result set is empty, then the collection will also be empty.

To delete a record from a table, you must have DELETE permission on the table and SELECT permission in order to read the value in the table in the conditional expression.

**argument**

table

The name of an existing table (which can be referenced in a schema-qualified manner).

condition

An expression that returns a Boolean type value to determine which records need to be deleted.

return\_expression

An expression that contains one or more columns in a table. If the column name of the table is specified in return\_expression, then when return\_expression is calculated, the value that is substituted is the value in the deleted record.

record

This is a record where the value of return\_expression is assigned to the fields of the record after the calculation. The order is such that the first return\_expression is assigned to the first field in the record, the second to the second field in the record, and so on. The number of fields in the record must match the number of expressions, and the field types must be compatible with the types of expressions assigned to them.

variable

A variable to which the result value is assigned after the parameter return\_expression is computed. If multiple expressions and variables are specified, then they are assigned to the first variable in the order of the first return\_expression and the second return\_expression to the second variable. The number of variables following the keyword INTO must match the number following the keyword RETURNING, and the variables must be of a type compatible with the expressions assigned to them.

collection

The members of a collection are created by a computed return\_expression. Here it can be either a single collection, belonging to a single field or a collection of record types, or there can be multiple collections, where each collection consists of a single field. The return expression must match the fields of all the specified collections in number and order. Each corresponding return\_expression and the fields of the collection must be type-compatible.

**Example**

Delete all records with employee number 7900 from table jobhist.

```
DELETE FROM jobhist WHERE empno = 7900; 
```

Empty all the contents of table jobhist.

```
DELETE FROM jobhist; 
```

Use delete in SPL language.

```
CREATE OR REPLACE FUNCTION delete_sal (eno in int) return void
IS
type t_table is table of emp;
var_sal t_table;
temp int;
BEGIN
delete from emp where empno = eno returning * bulk collect into var_sal ;
for i in 1..var_sal.count loop
    DBMS_OUTPUT.PUT_LINE(var_sal[i].empno); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].ENAME); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].JOB);
	DBMS_OUTPUT.PUT_LINE(var_sal[i].HIREDATE);
end loop;

END;
/
```