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.


What is displayed on the SQL*Plus screen? Explain the results.


Take the same PL/SQL block and place the line with the DBMS_OUTPUT before the SELECT INTO statement. What is displayed on the SQL*Plus screen? Explain what the value of the variable is at each point in the PL/SQL block.

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.


 --  ch04_2a.sql DECLARE    v_city; 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.


 --  ch04_3a.sql DECLARE    v_zip;    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


Write a PL/SQL block that will insert a new student in the student table. Use your own information for the data.

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 :


Returns the current value of the sequence


Increments the sequence and returns the new value.


This statement creates the sequence ESEQ:


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


Write a PL/SQL block that will insert a new student in the student table. Use your own information for the data. Create two variables that are used in the select statement. Get the USER and SYSDATE for the variables. Finally, use the existing student_id_seq sequence to generate a unique id for the new student.

Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
Year: 2003
Pages: 289 © 2008-2017.
If you may any questions please contact us: