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.
In order to execute in SQL*Plus use the following syntax:
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.