DML in PL/SQL

From within any PL/SQL block of code you can execute DML statements (INSERTs, UPDATEs, DELETEs, and MERGEs) against any and all tables and views to which you have access.

Note

Access to these data structures is determined at the time of compilation when you’re using the definer rights model. If you instead use the invoker rights model with the AUTHID CURRENT_USER compile option, access privileges are determined at runtime. See Chapter 24 for more details.

A Quick Introduction to DML

It is outside the scope of this book to provide complete reference information about the features of DML statements in the Oracle SQL language. Instead, I present a quick overview of the basic syntax, and then explore special features relating to DML inside PL/SQL, including:

  • Examples of each DML statement

  • Cursor attributes for DML statements

  • Special PL/SQL features for DML statements, such as the RETURNING clause

For detailed information, I encourage you to peruse Oracle documentation or a SQL-specific text.

Note

Officially, the SELECT statement is considered a “DML” statement. Routinely, however, when developers refer to “DML” they almost always mean those statements that modify the contents of a database table. For the remainder of this chapter, DML will refer to the non-query statements of SQL.

There are four DML statements available in the SQL language:

INSERT

Inserts one or more new rows into a table.

UPDATE

Updates the values of one or more columns in one or more rows in a table.

DELETE

Removes one ...

Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.