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;

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.