Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 4.1 Exercises

4.1.1 Use the SELECT INTO Syntax for Variable Initialization

Run the PL/SQL block from the pre-exercise example.

Data definition language (DDL) is 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 (DML) is easily achieved either by use of 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 exiting entry in the zipcode table.

FOR EXAMPLE

-- ch04_2a.sql
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

-- ch04_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;

SELECT statements 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 7, 10, and 11 .

4.1.2 Use DML in a PL/SQL Block

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 automatically generate primary key values.

Accessing and Incrementing Sequence Values

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

FOR EXAMPLE

This statement creates the sequence ESEQ:

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

Beginning with Oracle v7.3, a sequence value can be inserted directly into a table without first selecting it. (Previously it was necessary to use the SELECT INTO syntax and put the new sequence number into a variable and then you can insert the variable.)

FOR EXAMPLE

For this example, a table called test01 will be used: First the table test01 is created and then the sequence test_seq, then the sequence is used to populate the table.

-- ch04_3a.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;

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

In this last exercise for this lab, you will make use of all the material covered so far in this chapter.