SQL in PL/SQL
- Lab 3.1: DML Statements in PL/SQL
- Lab 3.2: Transaction Control in PL/SQL
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
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
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 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.
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;
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:
- 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.)
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 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.