Lab 4.1 Exercises

[ Team LiB ]

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. a) What is displayed on the SQL*Plus screen? Explain the results. b) 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. 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 a) 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: CURRVAL Returns the current value of the sequence NEXTVAL Increments the sequence and returns the new value. 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. a) 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.

[ Team LiB ]