Oracle 12c SQL: Data Manipulation and Transaction Control Study Guide

Chapter 5: Data Manipulation and Transaction Control Learning Objectives

• Use the $INSERT$ command to add a record to an existing table. • Manage virtual columns in data manipulations. • Use quotes in data values appropriately. • Use a subquery to copy records from an existing table. • Use the $UPDATE$ command to modify the existing rows of a table. • Use substitution variables with an $UPDATE$ command. • Delete records using the $DELETE$ command. • Manage transactions with transaction control commands: $COMMIT$, $ROLLBACK$, and $SAVEPOINT$. • Differentiate between a shared lock and an exclusive lock. • Use the $SELECT…FOR UPDATE$ command to create a shared lock.

The INSERT Command

• Definition: The $INSERT$ command is a Data Manipulation Language ($DML$) command used to add rows to existing tables. • Usage Constraints:   • It can only add one row at a time to a table.   • Non-numeric data must be enclosed in single quotes (' ').   • If a column list is not provided in the $INSERT INTO$ clause, a value must be assigned to every column in the table in the exact order they were defined. • Command Clauses:   • $INSERT INTO$: Identifies the target table.   • $VALUES$: Specifies the data to be added. • Example 1 (No Column List):   • Statement: $INSERT INTO acctmanager VALUES ('T500', 'NICK', 'TAYLOR', '05-SEP-09', 42000, 3500, 'NE')$   • Result: $1$ rows inserted. • Example 2 (With Column List):   • Statement: $INSERT INTO acctmanager (amid, amfirst, amlast, amsal, amcomm, region) VALUES ('J500', 'Sammie', 'Jones', 39500, 2000, 'NW')$   • Note: This allows skipping columns or providing them in a different order than table definition.

Handling Special Data Entry Scenarios

• Inserting NULL Values: There are three methods to input a $NULL$ value:   • Omit the column name from the $INSERT INTO$ clause column list.   • Use the $NULL$ keyword in the $VALUES$ clause.   • Substitute two single quotation marks (' ') with no space between them. • Oracle 12c ON NULL Clause: This is an option used with a $DEFAULT$ setting to ensure a default value is used when an attempt is made to insert a $NULL$ value. • Virtual Columns: $INSERT$ operations are disallowed on virtual columns because the database system generates these values automatically. Attempting to insert into a virtual column (e.g., $amearn$) results in $SQL Error: ORA-54013: INSERT operation disallowed on virtual columns$. • DEFAULT Option: To use a column's defined $DEFAULT$ value:   • Include a column list in the $INSERT$ statement but ignore the specific column.   • Explicitly use the $DEFAULT$ keyword as the value for the column in the $VALUES$ clause. • Quotes in Data: When a data value contains a literal single quote, two single quotes together must be used to represent it in the $DML$ statement.

Advanced Insertion and Subqueries

• Copying Records: Records can be copied from an existing table and entered into another existing table by substituting a subquery for the $VALUES$ clause. • Syntax: $INSERT INTO tablename (columnlist) (subquery)$. • Rule: When using a subquery, the $VALUES$ clause must be omitted as it is irrelevant.

The UPDATE Command

• Function: The $UPDATE$ command is used to modify existing rows in a table. It can be used to add values to an existing row (replacing $NULL$ values) or change existing values. • Syntax:   • $UPDATE tablename SET columnname = new_datavalue, … [WHERE condition]$; • Components:   • $UPDATE$: Identifies the table.   • $SET$: Identifies the specific column(s) being changed and their new values.   • $WHERE$ (Optional): Specifies which row(s) to change. Warning: If the $WHERE$ clause is omitted, all rows in the table will be updated. • Example:   • Statement: $UPDATE acctmanager SET amedate = '10-OCT-09', region = 'Z' WHERE amid = 'G500'$;   • Result: $1$ rows updated.

Substitution Variables

• Definition: Substitution variables allow for the creation of interactive scripts by prompting the user for a value during execution. • Syntax: Identified by an ampersand (&$) preceding the variable name. • Example:   • Statement: UPDATE customers SET region = '&Region' WHERE state = '&State';   • Interaction: The system will open a prompt asking for the value of REGION$(e.g., user enters$W$) and$STATE$.</p><h3 id="edebddc5-9eee-41ca-b1e3-aabaadb4a30b" data-toc-id="edebddc5-9eee-41ca-b1e3-aabaadb4a30b" collapsed="false" seolevelmigrated="true">The DELETE Command</h3><p>• <strong>Function</strong>: The$DELETE command removes specific rows from a table. • Execution:   • The WHERE clause determines which row(s) are removed.   • Omitting the WHERE$clause results in the removal of all rows within the table. • <strong>Example</strong>:$DELETE FROM acctmanager2;$(This removes all records from the table).</p><h3 id="986e9404-0472-4695-a069-06115dfdc6c7" data-toc-id="986e9404-0472-4695-a069-06115dfdc6c7" collapsed="false" seolevelmigrated="true">Transaction Control</h3><p>• <strong>Transaction Definition</strong>: A set of$DML$operations treated as a single logical block of work. • <strong>Persistence</strong>:$DML$results are not permanent until an explicit or implicit$COMMIT occurs. • Key Commands:   • COMMIT: Permanently updates the table and allows other users to see the changes.     • Explicit: Execution of the COMMIT; command.     • Implicit: Occurs when a Data Definition Language (DDL) command is executed or the user properly exits the system.   • ROLLBACK$: Undoes changes that have not yet been committed. Occurs when executing$ROLLBACK; or if the system restarts after a crash.   • SAVEPOINT: Marks a specific spot within a transaction. Allows the user to perform a partial rollback to that specific point. • Transaction Example:   1. UPDATE acctmanager SET region = 'E' WHERE amid = 'M500';   2. COMMIT; (Permanent save).   3. UPDATE acctmanager SET region = 'E' WHERE amid = 'T500';   4. UPDATE acctmanager SET region = 'E' WHERE amid = 'L500';   5. SAVEPOINT ONE; (Marker created).   6. UPDATE acctmanager SET amcomm = 6600 WHERE amid = 'T500';   7. ROLLBACK TO ONE; (Undo only the specific commission update after the savepoint; previous updates remain pending).

Table Locks

• Purpose: To prevent multiple users from chang

ing the same data or objects simultaneously, ensuring data consistency. • Lock Types:   • Shared Lock:     • Prevents DML operations on the specific portion of the table locked.     • Implicitly occurs during UPDATE$or$DELETE operations.     • Explicitly set via LOCK TABLE tablename IN SHARE MODE;     • Allows other users to place shared locks on other portions but prevents exclusive locks.     • Released upon COMMIT$or$ROLLBACK.   • Exclusive Lock:     • The most restrictive lock.     • Prevents other users from placing any shared or exclusive locks on the table.     • Implicitly occurs during DDL$operations ($CREATE$or$ALTER TABLE).     • Explicitly set via LOCK TABLE tablename IN EXCLUSIVE MODE;     • Released after DDL execution or user exit. • SELECT…FOR UPDATE:   • Creates a shared lock on the retrieved portion of the table.   • Prevents other users from changing a row while a user is selecting rows intended for change.   • Syntax: SELECT columnnames FROM tablename [WHERE condition] FOR UPDATE;   • Release: Lock is not released until a transaction control statement is issued, a DDL$command is executed, or the user exits via$EXIT$$.