DELETE (Embedded SQL)

Purpose

Prerequisites

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

The DELETE ANY TABLE system privilege also allows you to delete rows from any table or any view's base table.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view or you must meet one of the following criteria:

• If the creation label of the table or view is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

• If the creation label of your table or view is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

• If the row's label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

• If the row's label is lower than your DBMS label, you must have WRITEDOWN system privilege.

• If the row's label is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

AT Keywords and Parameters

identifies the database to which the DELETE statement is issued. The database can be identified by either:

db_name is a database identifier declared in a previous DECLARE DATABASE statement.

is a host variable whose value is a previously declared db_name.

If you omit this clause, the DELETE statement is issued to your default database.

FOR :host_integer

limits the number of times the statement is executed if the WHERE clause contains array host variables. If you omit this clause, Oracle7 executes the statement once for each component of the smallest array.

schema

is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.

table view

is the name of a table from which the rows are to be deleted. If you specify view, Oracle7 deletes rows from the view's base table.

dblink

is the complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see the section "Referring to Objects in Remote Databases" . You can only delete rows from a remote table or view if you are using Oracle7 with the distributed option.

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

subquery

is a subquery from which data is selected for deletion. For the syntax of subquery, see . Oracle executes the subquery and then uses the resulting rows as a table in the FROM clause. The subquery cannot query a table that appears in the same FROM clause as the subquery.

alias

is an alias assigned to the table. Aliases are generally used in DELETE statements with correlated queries.

WHERE

specifies which rows are deleted:

condition deletes only rows that satisfy the condition. This condition can contain host variables and optional indicator variables. See the syntax description of condition .

CURRENT OF deletes only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join, unless its FOR UPDATE clause specifically locks only one table.

If you omit this clause entirely, Oracle7 deletes all rows from the table or view.

Usage Notes

Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:

• the size of the smallest array

• the value of the :host_integer in the optional FOR clause

The cumulative number of rows deleted is returned through the SQLCA. If the WHERE clause contains array host variables, this value reflects the total number of rows deleted for all components of the array processed by the DELETE statement.

If no rows satisfy the condition, Oracle7 returns an error through the SQLCODE of the SQLCA. If you omit the WHERE clause, Oracle7 raises a warning flag in the 5th component of SQLWARN in the SQLCA. For more information on this command and the SQLCA, see Programmer's Guide to the Oracle Precompilers.

You can use comments in a DELETE statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.

Example

This example illustrates the use of the DELETE statement within a Pro*C embedded SQL program:

EXEC SQL DELETE FROM emp 
	WHERE deptno = :deptno 
	AND job = :job; ... 
EXEC SQL DECLARE emp_cursor CURSOR 
	FOR SELECT empno, comm 
		FROM emp; 
EXEC SQL OPEN emp_cursor; 
EXEC SQL FETCH c1 
	INTO :emp_number, :commission; 
EXEC SQL DELETE FROM emp 
	WHERE CURRENT OF emp_cursor; 

Related Topics