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