Chapter 4. SQL in PLSQL

Chapter 4. SQL in PL/SQL

Chapter Objectives

In this Chapter, you will learn about:

Making Use of DML in PL/SQL

Making Use of SAVEPOINT

This chapter is a collection of some fundamental elements of using SQL statements in PL/SQL blocks. In the previous chapter, you initialized variables with the ":=" syntax; in this chapter, we will introduce the method of using a SQL select statement to update the value of a variable. These variables can then be used in DML statements (INSERT, DELETE, or UPDATE). Additionally, we will demonstrate how you can use a sequence in your DML statements within a PL/SQL block much as you would in a stand-alone SQL statement.

A transaction in Oracle is a series of SQL statements that have been grouped together into a logical unit by the programmer. A programmer chooses to do this in order to maintain data integrity. Each application (SQL*Plus, Procedure Builder, and so forth) maintains a single database session for each instance of a user login. The changes to the database that have been executed by a single application session are not actually "saved" into the database until a COMMIT occurs. Work within a transaction up to and just prior to the commit can be rolled back; once a commit has been issued, work within that transaction cannot be rolled back.

In order to exert transaction control, a SAVEPOINT can be used to break down large SQL statements into individual units that are easier to manage. In this chapter, we will cover the basic elements of transaction control so you will know how to manage your PL/SQL code by use of COMMIT, ROLLBACK, and principally SAVEPOINT.