1044-1046

Previous Table of Contents Next

Page 1044

Once you declare host variables , establish a communication channel, and connect to the database, you can start to manipulate data.

Data Manipulation Area

The data manipulation area is where SQL statements are executed. This section is often referred to as the program body. This section of the chapter focuses on the types of SQL statements, the logical unit of work, controlling transactions, and locking data.

Types of Executable SQL Statements

You can execute different types of SQL statements in the data manipulation area: data manipulation statements, data definition statements, and data control statements.

Data manipulation language (DML) statements are used to change the data. The following is a list of commands that are considered DML statements:

• UPDATE column values in existing rows
• DELETE rows from a table
• COMMIT WORK data to the table
• ROLLBACK WORK changes made to the data
• LOCK the row or table exclusively for the user

Data definition language (DDL) statements are used to define and maintain database objects. Some common uses of the DDL statements are creating tables or views. The following is a list of commands that are considered DDL:

• CREATE TABLE
• CREATE VIEW
• ALTER table or view
• DROP table, view, grant, or sequence number

Data control language (DCL) statements are used to access tables and the data associated with them. There are two types of access that these statements control. The first type of statement controls connecting to the database; the CONNECT and GRANT commands enable a user to do this. The second type of statement controls access to the data; the GRANT SELECT and REVOKE DELETE commands are examples of this.

Pulling together a combination of all three of these statements creates what is known as a logical unit of work.

Page 1045

Logical Unit of Work

A logical unit of work is defined as a group of SQL statements treated as a single transaction to the Oracle kernel. This unit of work begins with any valid SQL DML statement and ends with either an implicit or explicit release of work. An implicit commit release is performed by the execution of any DDL statement, whereas an implicit rollback release is performed upon abnormal termination of your program. A program can explicitly release the logical unit of work.

Controlling Transactions

Because Oracle is transaction oriented and processes information in logical units of work, controlling these statements is essential to data integrity. A transaction begins with the first EXEC SQL statement issued in your program. When one transaction ends, the next begins. You can end a transaction with one of two statements: COMMIT or ROLLBACK. If you do not subdivide your program with a COMMIT or ROLLBACK statement, Oracle treats the whole program as one transaction.

CAUTION

Remember to commit what you are not willing to recreate. Losing one large transaction could mean significant loss of data; smaller transaction losses are easier to recover.

To make changes to the database permanent, use the COMMIT command. The COMMIT command does the following:

• Makes permanent all changes to the databases during the current transactions
• Makes these changes visible to other users
• Erases all savepoints
• Releases all row and table locks but not parse locks
• Closes cursors referenced in a CURRENT OF clause
• Ends the transaction

The COMMIT statement has no effect on the values of host variables or on the flow of control in your program. This statement should be placed in the main path through your program. The following example shows the syntax of the COMMIT command:

 EXEC SQL COMMIT WORK RELEASE; 

The RELEASE option on COMMIT or ROLLBACK releases all process resources and provides a clean exit from Oracle. After the release, there is no further access to the Oracle database until another connect is issued.

Page 1046

TIP

You should always include an explicit COMMIT or ROLLBACK with RELEASE at the end of your program. If the RELEASE option is not specified, any locks or resources are not released until Oracle recognizes that the process is no longer active.

NOTE

The keyword WORK provides ANSI compatibility, whereas the optional RELEASE parameter frees all Oracle resources.

To undo pending changes, use the ROLLBACK statement. This statement reverts all the changes made to the database since the last commit, as well as frees locks and savepoints such as the COMMIT statement.

The ROLLBACK statement has no effect on the values of host variables or on the flow of control in your program. The following example shows the syntax for the ROLLBACK statement:

 EXEC SQL ROLLBACK WORK RELEASE; 

TIP

The ROLLBACK statement is useful when you accidentally delete rows from the table. Do a ROLLBACK before the COMMIT. Remember, only use RELEASE when you are done processing and ready to exit because it disconnects you from the database.

Committing changes that are made to the database gives the program enormous control over data, but what happens when someone else executes a program and it tries to manipulate the same data? Oracle provides a mechanism that enables you to reserve the data just for your use.

Locking

Oracle uses locks (sometimes called enqueues) to control access to the data. When a row of data is sought, Oracle locks the row until the user completes the current transaction. A user can get hung up in an application when two or more sources attempt to access the data at the same time. Because of data-integrity constraints, Oracle gives temporary ownership of a row to whomever completes the most amount of work or gets to the row first. You should follow these guidelines when using locks:

Previous Table of Contents Next