Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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.

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

EXECUTE Procedure_name

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.