Oracle® PL/SQL® Interactive Workbook, Second Edition By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 4. SQL in PL/SQL
In the chapter discussion, you learned how to use numerous SQL techniques in a PL/SQL block. First, you learned how to use SELECT INTO to generate values for a variable. Then you learned the various DML methods, including the use of a sequence. Finally, you learned how to manage transactions by using savepoints. Complete the following projects by writing the code for each step and running it and then going on to the next step.
1)
Create a table called CHAP4 with two columns; one is ID (a number) and the second is NAME, which is a varchar2(20).
2)
Create a sequence called CHAP4_SEQ that increments by units of 5.
3)
Write a PL/SQL block that performs the following in this order:
Declares 2 variables, one for the v_name and one for v_id. The v_name variable can be used throughout the block for holding the name that will be inserted; realize that the value will change in the course of the block.
The block then inserts into the table the name of the student that is enrolled in the most classes and uses a sequence for the ID; afterward there is SAVEPOINT A.
Then the student with the least enrollments is inserted; afterward there is SAVEPOINT B.
Then the instructor who is teaching the maximum number of courses is inserted in the same way. Afterward there is SAVEPOINT C.
Using a SELECT INTO statement, hold the value of the instructor in the variable v_id.
Undo the instructor insert by use of rollback.
Insert the instructor teaching the least amount of courses but do not use the sequence to generate the ID; instead use the value from the first instructor whom you have since undone.
Now insert the instructor teaching the most number of courses and use the sequence to populate his ID.
Add DBMS_OUTPUT throughout the block to display the values of the variables as they change. (This is good practice for debugging.)
The answers to Test Your Thinking can be found in Appendix D and on the web site.
