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.
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 INTO
syntax.
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 function TO_CHAR
is issued to format the number. The DBMS_OUTPUT
package is then used to show the result to the screen.
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;
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.
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.
Once a sequence is created, you can access its values in SQL statements with these pseudocolumns:
CURRVAL
: Returns the current value of the sequence.
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.)
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;
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 variables v_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.
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.
COMMIT
: Makes events within a transaction permanent.
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.
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
or ROLLBACK
statement.
When a COMMIT
statement is issued to the database, the transaction has ended, and the following results are true:
All work done by the transaction becomes permanent.
Other users can see changes in data made by the transaction.
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.
When a ROLLBACK
statement is issued to the database, the transaction has ended, and the following results are true:
All work done by the transaction is undone, as if it hadn’t been issued.
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.
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:
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.
Any locks and resources acquired by the SQL statements since the SAVEPOINT
will be released.
The transaction is not finished, because SQL statements are still pending.
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 SAVEPOINT
s.
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.
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.
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 SAVEPOINT
s are used. The final examples demonstrated how committed data could be reversed by using ROLLBACK
s in conjunction with SAVEPOINT
s.