Keyword and parameter description - delete statement, PL-SQL Programming

Keyword and Parameter Description:

table_reference:

This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must have DELETE privileges.

Subquery:

This is a SELECT statement which provides a set of rows for the processing. Its syntax is such that of select_into_statement without the INTO clause.

TABLE (subquery2):

The operand of the TABLE is a SELECT statement which returns a single column value, that should be a nested table or a varray cast as a nested table. The Operator TABLE informs the Oracle that the value is a collection, but not a scalar value.

Alias:

This is another (generally short) name for the referenced table or view and is usually used in the WHERE clause.

WHERE search_condition:

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

WHERE CURRENT OF cursor_name:

This clause refers to the most recent row processed by the FETCH statement related with the cursor identified by the cursor_name. The cursor should be FOR UPDATE and should be open and placed on a row. When the cursor is not open, the CURRENT OF clause causes an error.

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

returning_clause:

This clause returns values from the deleted rows, by this means eliminating the need to SELECT the rows ahead of time. You can retrieve the column values into the variables and/or host variables, or into the collections and/or host arrays. Though, you cannot use the RETURNING clause for remote or parallel deletes.

BULK COLLECT:

These clauses instruct the SQL engine to bulk-bind the output collections before returning them to the PL/SQL engine. The SQL engine bulk-binds all the collections referenced in the RETURNING INTO list. The corresponding columns should store scalar (and not the composite) values. 

Posted Date: 10/8/2012 6:01:57 AM | Location : United States







Related Discussions:- Keyword and parameter description - delete statement, Assignment Help, Ask Question on Keyword and parameter description - delete statement, Get Answer, Expert's Help, Keyword and parameter description - delete statement Discussions

Write discussion on Keyword and parameter description - delete statement
Your posts are moderated
Related Questions
Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illu

Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke

Parameter & Keyword Description: function_name: The user-defined function is identifying by that keyword. parameter_name: This identifies the formal parameter that

LEVEL You use the LEVEL with the SELECT CONNECT BY statement to categorize rows from a database table into a tree structure. The LEVEL returns the level number of a node in a

Example of Alternative formulation as a table constraint Example: Alternative formulation as a table constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_

Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Example of Null operator - NiNo Rule If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is sho

Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

THEORY OF BIOGENESIS - This theory explains that the existing living organisms originated from pre-existing living beings not from non living entities. This concept of or

Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b