Lab 12.1 Exercises

12.1.1 Create Procedures

In this exercise, you will run a script that creates a procedure. Using a text editor such as Notepad, create a file with the following script.

 -- ch12_01a.sql CREATE OR REPLACE PROCEDURE Discount AS   CURSOR c_group_discount   IS     SELECT distinct s.course_no, c.description       FROM section s, enrollment e, course c      WHERE s.section_id = e.section_id        AND c.course_no = s.course_no      GROUP BY s.course_no, c.description,               e.section_id, s.section_id      HAVING COUNT(*) >=8; BEGIN    FOR r_group_discount IN c_group_discount    LOOP       UPDATE course          SET cost = cost * .95        WHERE course_no = r_group_discount.course_no;       DBMS_OUTPUT.PUT_LINE         ('A 5% discount has been given to'          r_group_discount.course_no' '          r_group_discount.description         );    END LOOP; END; 

At the SQL*Plus session, run the script.

a)

What did you see on your screen? Explain what happened .

In order to execute in SQL*Plus use the following syntax:

 EXECUTE Procedure_name 

b)	Execute the Discount procedure. How did you accomplish this? What are the results that you see in your SQL*Plus screen?
c)	The script did not contain a COMMIT. Discuss the issues involved with placing a COMMIT in the procedure and indicate where the COMMIT could be placed.

12.1.2 Query the Data Dictionary for Information on Procedures

There are two main views in the data dictionary that provide information on stored code. They are the USER_OBJECTS view, to give information about the objects, and the USER_SOURCE, to give the text of the source code. Remember, the data dictionary also has an ALL_ and DBA_ version of these views.

a)	Write the select statement to get pertinent information from the USER_OBJECTS view about the Discount procedure you just wrote. Run the query and describe the results.
b)	Write the SELECT statement to display the source code from the USER_SOURCE view for the Discount procedure.