SQL in PL/SQL - Oracle PL/SQL by Example, Fifth Edition (2015)

Oracle PL/SQL by Example, Fifth Edition (2015)

Chapter 3. SQL in PL/SQL


In this chapter, you will learn about

Image DML Statements in PL/SQL

Image Transaction Control in PL/SQL


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 to maintain data integrity. Each application (SQL*Plus, SQL Developer, and various third-party PL/SQL tools) 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, however, work within that transaction cannot be rolled back. Note that those SQL statements should be either committed or rejected as a group.

To exert transaction control, a SAVEPOINT statement can be used to break down large PL/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 through use of the COMMIT, ROLLBACK, and (principally) SAVEPOINT statement.

Lab 3.1: DML Statements in PL/SQL


After this lab, you will be able to

Image Initialize Variables with SELECT INTO

Image Use the SELECT INTO Syntax for Variable Initialization

Image Use DML in a PL/SQL Block

Image Make Use of a Sequence in a PL/SQL Block


Initialize Variables with SELECT INTO

In PL/SQL, there are two main methods of giving values to variables in a PL/SQL block. The first one, which you learned in Chapter 1, is initialization with the “:=” syntax. In this lab we will learn how to initialize a variable with a select statement by making use of the SELECT INTOsyntax.

A variable that has been declared in the declaration section of the PL/SQL block can later be given a value with a select statement. The correct syntax is as follows:

SELECT item_name
INTO variable_name
FROM table_name;

Note that any single row function can be performed on the item to give the variable a calculated value.

For Example ch03_1a.sql

SET SERVEROUTPUT ON
DECLARE
v_average_cost VARCHAR2(10);
BEGIN
SELECT TO_CHAR(AVG(cost), '$9,999.99')
INTO v_average_cost
FROM course;
DBMS_OUTPUT.PUT_LINE('The average cost of a '||
'course in the CTA program is '||
v_average_cost);
END;

In this example, a variable is given the value of the average cost of a course in the course table. First, the variable must be declared in the declaration section of the PL/SQL block. In this example, the variable is given the data type of VARCHAR2(10) because of the functions used on the data. The select statement that would produce this outcome in SQL*Plus would be

SELECT TO_CHAR(AVG(cost), '$9,999.99')
FROM course;

The TO_CHAR function is used to format the cost; in doing this, the number data type is converted to a character data type. Once the variable has a value, it can be displayed to the screen using the PUT_LINE procedure of the DBMS_OUTPUT package. The output of this PL/SQL block would be:

The average cost of a course in the CTA program
is $1,198.33
PL/SQL procedure successfully completed.

In the declaration section of the PL/SQL block, the variable v_average_cost is declared as a varchar2. In the executable section of the block, this variable is given the value of the average cost from the course table by means of the SELECT INTO syntax. The SQL functionTO_CHAR is issued to format the number. The DBMS_OUTPUT package is then used to show the result to the screen.

Using the SELECT INTO Syntax for Variable Initialization

The previous PL/SQL block may be rearranged so the DBMS_OUTPUT section is placed before the SELECT INTO statement.

For Example ch03_1a.sql

SET SERVEROUTPUT ON
DECLARE
v_average_cost VARCHAR2(10);
BEGIN
DBMS_OUTPUT.PUT_LINE('The average cost of a '||
'course in the CTA program is '||
v_average_cost);
SELECT TO_CHAR(AVG(cost), '$9,999.99')
INTO v_average_cost
FROM course;
END;

You will then see the following result:

The average cost of a course in the CTA program is
PL/SQL procedure successfully completed.

The variable v_average_cost will be set to NULL when it is first declared. Because the DBMS_OUTPUT section precedes the point at which the variable is given a value, the output for the variable will be NULL. After the SELECT INTO statement, the variable will be given the same value as in the original block, but it will not be displayed because there is not another DBMS_OUTPUT line in the PL/SQL block.

Data Definition Language (DDL) statements are not valid in a simple PL/SQL block (more advanced techniques such as procedures in the DBMS_SQL package will enable you to make use of DDL), yet data manipulation (using Data Manipulation Language [DML]) is easily achieved either by using variables or by simply putting a DML statement into a PL/SQL block. Here is an example of a PL/SQL block that updates an existing entry in the zipcode table.

For Example ch03_2a.sql

SET SERVEROUTPUT ON
DECLARE
v_city zipcode.city%TYPE;
BEGIN
SELECT 'COLUMBUS'
INTO v_city
FROM dual;
UPDATE zipcode
SET city = v_city
WHERE ZIP = 43224;
END;

It is also possible to insert data into a database table in a PL/SQL block, as shown in the following example.

For Example ch03_3a.sql

DECLARE
v_zip zipcode.zip%TYPE;
v_user zipcode.created_by%TYPE;
v_date zipcode.created_date%TYPE;
BEGIN
SELECT 43438, USER, SYSDATE
INTO v_zip, v_user, v_date
FROM dual;
INSERT INTO zipcode
(ZIP, CREATED_BY ,CREATED_DATE, MODIFIED_BY,
MODIFIED_DATE
)
VALUES(v_zip, v_user, v_date, v_user, v_date);
END;


By the Way

SELECT statements in PL/SQL that return no rows or too many rows will cause an error to occur that can be trapped by using an exception. You will learn more about handling exceptions in Chapters 8, 9, and 10.


Using DML in a PL/SQL Block

This section demonstrates how DML is used in PL/SQL. The following PL/SQL block inserts a new student into the student table.

For Example ch03_4a.sql

BEGIN
SELECT MAX(student_id)
INTO v_max_id
FROM student;
INSERT into student
(student_id, last_name, zip,
created_by, created_date,
modified_by, modified_date,
registration_date
)
VALUES (v_max_id + 1, 'Rosenzweig',
11238, 'BROSENZ ', '01-JAN-2014',
'BROSENZ', '10-JAN-2014', '15-FEB-2014'
);
END;

To generate a unique ID, the maximum student_id is selected into a variable and then incremented by 1. In this example, there is a foreign key on the zip item in the student table, which means that the ZIP code you choose to enter must be in the zipcode table.

Using an Oracle Sequence

An Oracle sequence is an Oracle database object that can be used to generate unique numbers. You can use sequences to generate primary key values automatically.

Accessing and Incrementing Sequence Values

Once a sequence is created, you can access its values in SQL statements with these pseudocolumns:

Image CURRVAL: Returns the current value of the sequence.

Image NEXTVAL: Increments the sequence and returns the new value.

The following example creates the sequence eseq.

For Example

CREATE SEQUENCE eseq
INCREMENT BY 10

The first reference to ESEQ.NEXTVAL returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.

(Even though you will be guaranteed unique numbers, you are not guaranteed contiguous numbers. In some systems this may be a problem—for example, when generating invoice numbers.)

Drawing Numbers from a Sequence

A sequence value can be inserted directly into a table without first selecting it. (In very old versions of Oracle prior to Oracle 7.3, it was necessary to use the SELECT INTO syntax and put the new sequence number into a variable; you could then insert the variable.)

For this example, a table called test01 will be used. The table test01 is first created, followed by the sequence test_seq. Then the sequence is used to populate the table.

For Example ch03_5a.sql

CREATE TABLE test01 (col1 number);
CREATE SEQUENCE test_seq
INCREMENT BY 5;
BEGIN
INSERT INTO test01
VALUES (test_seq.NEXTVAL);
END;
/
Select * FROM test01;

Using a Sequence in a PL/SQL Block

In this example, a PL/SQL block is used to insert a new student in the student table. The PL/SQL code makes use of two variables, USER and SYSDATE, that are used in the select statement. The existing student_id_seq sequence is used to generate a unique ID for the new student.

For Example ch03_6a.sql

DECLARE
v_user student.created_by%TYPE;
v_date student.created_date%TYPE;
BEGIN
SELECT USER, sysdate
INTO v_user, v_date
FROM dual;
INSERT INTO student
(student_id, last_name, zip,
created_by, created_date, modified_by,
modified_date, registration_date
)
VALUES (student_id_seq.nextval, 'Smith',
11238, v_user, v_date, v_user, v_date,
v_date
);
END;

In the declaration section of the PL/SQL block, two variables are declared. They are both set to be data types within the student table using the %TYPE method of declaration. This ensures the data types match the columns of the tables into which they will be inserted. The two variablesv_user and v_date are given values from the system by means of SELECT INTO statements. The value of the student_id is generated by using the next value of the student_id_seq sequence.

Lab 3.2: Transaction Control in PL/SQL


After this lab, you will be able to

Image Use the COMMIT, ROLLBACK, and SAVEPOINT Statements

Image Put Together DML and Transaction Control


Using COMMIT, ROLLBACK, and SAVEPOINT

Transactions are a means to break programming code into manageable units. Grouping transactions into smaller elements is a standard practice that ensures an application will save only correct data. Initially, any application will have to connect to the database to access the data. When a user is issuing DML statements in an application, however, these changes are not visible to other users until a COMMIT or ROLLBACK has been issued. The Oracle platform guarantees a read-consistent view of the data. Until that point, all data that have been inserted or updated will be held in memory and will be available only to the current user. The rows that have been changed will be locked by the current user and will not be available for updating to other users until the locks have been released. A COMMIT or ROLLBACK statement will release these locks. Transactions can be controlled more readily by marking points of the transaction with the SAVEPOINT command.

Image COMMIT: Makes events within a transaction permanent.

Image ROLLBACK: Erases events within a transaction.

Additionally, you can use a SAVEPOINT to control transactions. Transactions are defined in the PL/SQL block from one SAVEPOINT to another. The use of the SAVEPOINT command allows you to break your SQL statements into units so that in a given PL/SQL block, some units can be committed (saved to the database), others can be rolled back (undone), and so forth.


By the Way

The Oracle platform makes a distinction between a transaction and a PL/SQL block. The start and end of a PL/SQL block do not necessarily mean the start and end of a transaction.


To demonstrate the need for transaction control, we will examine a two-step data manipulation process. Suppose that the fees for all courses in the CTA database that have a prerequisite course need to be increased by 10 percent; at the same time, all courses that do not have a prerequisite need to be decreased by 10 percent. This is a two-step process. If the first step is successful but the second step is not, then the data concerning course cost would be inconsistent in the database. Because this adjustment is based on a change in percentage, there would be no way to track which part of this course adjustment was successful and which part was not.

In the following example, one PL/SQL block performs two updates on the cost item in the course table. In the first step (this code is commented for the purpose of emphasizing each update), the cost is updated with a cost that is 10 percent less whenever the course does not have a prerequisite. In the second step, the cost is increased by 10 percent whenever the course has a prerequisite.

For Example ch03_7a.sql

BEGIN
-- STEP 1
UPDATE course
SET cost = cost - (cost * 0.10)
WHERE prerequisite IS NULL;
-- STEP 2
UPDATE course
SET cost = cost + (cost * 0.10)
WHERE prerequisite IS NOT NULL;
END;

Let’s assume that the first update statement succeeds, but the second update statement fails because the network went down. The data in the course table is now inconsistent because courses with no prerequisite have had their cost reduced but courses with prerequisites have not been adjusted. To prevent this sort of situation, statements must be combined into a transaction. Thus either both statements will succeed or both statements will fail.

A transaction usually combines SQL statements that represent a logical unit of work. The transaction begins with the first SQL statement issued after the previous transaction, or with the first SQL statement issued after connecting to the database. The transaction ends with the COMMIT orROLLBACK statement.

COMMIT

When a COMMIT statement is issued to the database, the transaction has ended, and the following results are true:

Image All work done by the transaction becomes permanent.

Image Other users can see changes in data made by the transaction.

Image Any locks acquired by the transaction are released.

A COMMIT statement has the following syntax:

COMMIT [WORK];

The word WORK is optional and is used to improve readability. Until a transaction is committed, only the user executing that transaction can see changes in the data made by his or her session.

Suppose User A issues the following command on a student table that exists in another schema but has a public synonym of student:

For Example ch03_8a.sql

BEGIN
INSERT INTO student
(student_id, last_name, zip, registration_date,
created_by, created_date, modified_by,
modified_date
)
VALUES (student_id_seq.nextval, 'Tashi', 10015,
'01-JAN-99', 'STUDENTA', '01-JAN-99',
'STUDENTA', '01-JAN-99'
);
END;

Then User B enters the following command to query the table known by its public synonym student, while logged on to his session.

SELECT *
FROM student
WHERE last_name = 'Tashi';

Then User A issues the following command:

COMMIT;

Now if User B enters the same query again, he will not see the same results.

In this example, there are two sessions: User A and User B. User A inserts a record into the student table. User B queries the student table, but does not get the record that was inserted by User A. User B cannot see the information because User A has not committed the work. When User A commits the transaction, User B, upon resubmitting the query, sees the records inserted by User A.

ROLLBACK

When a ROLLBACK statement is issued to the database, the transaction has ended, and the following results are true:

Image All work done by the transaction is undone, as if it hadn’t been issued.

Image Any locks acquired by the transaction are released.

A ROLLBACK statement has the following syntax:

ROLLBACK [WORK];

The WORK keyword is optional and provides for increased readability.

SAVEPOINT

The ROLLBACK statement undoes all work done by the user in a specific transaction. With the SAVEPOINT command, however, only part of the transaction can be undone. A SAVEPOINT command has the following syntax:

SAVEPOINT name;

The word name is the SAVEPOINT statement’s name. Once a SAVEPOINT is defined, the program can roll back to that SAVEPOINT. A ROLLBACK statement, then, has the following syntax:

ROLLBACK [WORK] to SAVEPOINT name;

When a ROLLBACK to SAVEPOINT statement is issued to the database, the following results are true:

Image Any work done since the SAVEPOINT is undone. The SAVEPOINT remains active, however, until a full COMMIT or ROLLBACK is issued. It can be rolled back again, if desired.

Image Any locks and resources acquired by the SQL statements since the SAVEPOINT will be released.

Image The transaction is not finished, because SQL statements are still pending.

Putting Together DML and Transaction Control

This section combines all the elements of transaction control that have been covered in this chapter. The following piece of code is an example of a PL/SQL block with three SAVEPOINTs.

For Example ch03_9a.sql

BEGIN
INSERT INTO student
( student_id, Last_name, zip, registration_date,
created_by, created_date, modified_by,
modified_date
)
VALUES ( student_id_seq.nextval, 'Tashi', 10015,
'01-JAN-99', 'STUDENTA', '01-JAN-99',
'STUDENTA','01-JAN-99'
);
SAVEPOINT A;
INSERT INTO student
( student_id, Last_name, zip, registration_date,
created_by, created_date, modified_by,
modified_date
)
VALUES (student_id_seq.nextval, 'Sonam', 10015,
'01-JAN-99', 'STUDENTB','01-JAN-99',
'STUDENTB', '01-JAN-99'
);
SAVEPOINT B;
INSERT INTO student
( student_id, Last_name, zip, registration_date,
created_by, created_date, modified_by,
modified_date
)
VALUES (student_id_seq.nextval, 'Norbu', 10015,
'01-JAN-99', 'STUDENTB', '01-JAN-99',
'STUDENTB', '01-JAN-99'
);
SAVEPOINT C;
ROLLBACK TO B;
END;

If you were to run the following SELECT statement immediately after running the preceding example, you would not be able to see any data because the ROLLBACK to (SAVEPOINT) B has undone the last insert statement where the student Norbu was inserted.

SELECT *
FROM student
WHERE last_name = 'Norbu';

The result would be “no rows selected.”

Three students were inserted in this PL/SQL block: first Tashi in SAVEPOINT A, then Sonam in SAVEPOINT B, and finally Norbu in SAVEPOINT C. When the command to roll back to B was issued, the insert of Norbu was undone.

If the following command was entered after the script ch03_9a.sql, then the insert in SAVEPOINT B would be undone—that is, the insert of Sonam:

ROLLBACK to SAVEPOINT A;

Tashi was the only student that was successfully entered into the database. The ROLLBACK to SAVEPOINT A undid the insert statements for Norbu and Sonam.


By the Way

SAVEPOINT is often used before a complicated section of the transaction. If this part of the transaction fails, it can be rolled back, allowing the earlier part to continue.



Did You Know?

It is important to note the distinction between transactions and PL/SQL blocks. When a block starts, it does not mean that the transaction starts. Likewise, the start of the transaction need not coincide with the start of a block.


Here is an example of a single PL/SQL block with multiple transactions.

For Example ch03_10a.sql

DECLARE
v_Counter NUMBER;
BEGIN
v_counter := 0;
FOR i IN 1..100
LOOP
v_counter := v_counter + 1;
IF v_counter = 10
THEN
COMMIT;
v_counter := 0;
END IF;
END LOOP;
END;

In this example, as soon as the value of v_counter becomes equal to 10, the work is committed. Thus there will be a total of 10 transactions contained in this one PL/SQL block.

Summary

In this chapter, you learned how to make use of variables and the various ways to populate variables. Use of DML (Data Manipulation Language) within a PL/SQL block was illustrated in examples with insert statements. These examples also made use of sequences to generate unique numbers.

The last section of the chapter covered transactional control in PL/SQL by explaining what it means to commit data as well as how SAVEPOINTs are used. The final examples demonstrated how committed data could be reversed by using ROLLBACKs in conjunction with SAVEPOINTs.


By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.