Data Manipulation Language Statements and modifying Object Tables
Throughout this course, you have had the opportunity to execute queries for the various types of object tables.
This module will focus on the Data Manipulation Language (DML)
statements such as INSERT, UPDATE, and DELETE for object tables with and without the reference (REF) operator. DML statements on object tables are similar to statements on normal relational tables. However, there are some subtle differences, which we will review in the following lessons.
When you have completed this module, you will be able to:
- Define important concepts for modifying object tables
- Write Structured Query Language (SQL) to insert a row into an object table
- Insert rows into sub queries
- Write SQL to update object tables
- Delete rows using SQL
Executing Past Exceptions
When an exception is raised in a PL/SQL block, normal execution is halted and control is transferred to the exception section. You can never return to the execution section once an exception is raised in that block. In some cases, however, the ability to continue past exceptions is exactly the desired behavior. Consider the following scenario: I need to write a procedure that performs a series of DML statements against a variety of tables
(delete from one table, update another, insert into a final table). My first pass at writing this procedure might produce code like the following:
PROCEDURE change_data IS
DELETE FROM employees WHERE ... ;
UPDATE company SET ... ;
INSERT INTO company_history SELECT * FROM company WHERE ... ;
In the next lesson, we will describe some key concepts for modifying object tables.
DML - Data Manipulation Language:
INSERT, UPDATE, and DELETE statements are DML statements.