execute.out

Oracle 9i PL/SQL程序设计的随书源码

REM execute.out
REM Chapter 10, Oracle9i PL/SQL Programming by Scott Urman
REM This file shows the output from running the execute.sql script
REM in SQL*Plus.

SQL> @execute
SQL> REM execute.sql
SQL> REM Chapter 5, Oracle8i Advanced PL/SQL Programming
SQL> REM by Scott Urman
SQL> 
SQL> REM This script demonstrates the behavior of the EXECUTE system
SQL> REM privilege.
SQL> 
SQL> set echo on
SQL> set serveroutput on
SQL> 
SQL> -- First create the users userA and userB, with the necessary
SQL> -- objects in each.  We need to connect to an account with the
SQL> -- necessary privileges, such as SYSTEM, to do this.
SQL> -- You may also want to change the UNLIMITED TABLESPACE
SQL> -- privilege below to grant explicit limits on tablespaces in your
SQL> -- database.
SQL> connect system/manager
Connected.
SQL> DROP USER UserA CASCADE;

User dropped.

SQL> CREATE USER UserA IDENTIFIED BY UserA;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE,
  2  	   UNLIMITED TABLESPACE, CREATE ROLE, DROP ANY ROLE TO UserA;

Grant succeeded.

SQL> 
SQL> DROP USER UserB CASCADE;

User dropped.

SQL> CREATE USER UserB IDENTIFIED BY UserB;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE,
  2  	   UNLIMITED TABLESPACE TO UserB;

Grant succeeded.

SQL> 
SQL> -- ***********************************
SQL> -- Scenario illustrated by Figure 5-14: All objects owned by UserA.
SQL> -- ***********************************
SQL> connect UserA/UserA
Connected.
SQL> 
SQL> -- First create the classes table.
SQL> CREATE TABLE classes (
  2    department	CHAR(3),
  3    course		NUMBER(3),
  4    description	VARCHAR2(2000),
  5    max_students	NUMBER(3),
  6    current_students NUMBER(3),
  7    num_credits	NUMBER(1),
  8    room_id		NUMBER(5));

Table created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('HIS', 101, 'History 101', 30, 11, 4, 20000);

1 row created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('HIS', 301, 'History 301', 30, 0, 4, 20004);

1 row created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('CS', 101, 'Computer Science 101', 50, 0, 4, 20001);

1 row created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('ECN', 203, 'Economics 203', 15, 0, 3, 20002);

1 row created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('CS', 102, 'Computer Science 102', 35, 3, 4, 20003);

1 row created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('MUS', 410, 'Music 410', 5, 4, 3, 20005);

1 row created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('ECN', 101, 'Economics 101', 50, 0, 4, 20007);

1 row created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('NUT', 307, 'Nutrition 307', 20, 2, 4, 20008);

1 row created.

SQL> 
SQL> INSERT INTO classes(department, course, description, max_students,
  2  			  current_students, num_credits, room_id)
  3    VALUES ('MUS', 100, 'Music 100', 100, 0, 3, NULL);

1 row created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> -- And now temp_table.
SQL> CREATE TABLE temp_table (
  2    num_col	  NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL> 
SQL> -- We also need AlmostFull:
SQL> CREATE OR REPLACE FUNCTION AlmostFull (
  2    p_Department classes.department%TYPE,
  3    p_Course     classes.course%TYPE)
  4    RETURN BOOLEAN IS
  5  
  6    v_CurrentStudents NUMBER;
  7    v_MaxStudents	 NUMBER;
  8    v_ReturnValue	 BOOLEAN;
  9    v_FullPercent	 CONSTANT NUMBER := 80;
 10  BEGIN
 11    -- Get the current and maximum students for the requested
 12    -- course.
 13    SELECT current_students, max_students
 14  	 INTO v_CurrentStudents, v_MaxStudents
 15  	 FROM classes
 16  	 WHERE department = p_Department
 17  	 AND course = p_Course;
 18  
 19    -- If the class is more full than the percentage given by
 20    -- v_FullPercent, return TRUE. Otherwise, return FALSE.
 21    IF (v_CurrentStudents / v_MaxStudents * 100) >= v_FullPercent THEN
 22  	 v_ReturnValue := TRUE;
 23    ELSE
 24  	 v_ReturnValue := FALSE;
 25    END IF;
 26  
 27    RETURN v_ReturnValue;
 28  END AlmostFull;
 29  /

Function created.

SQL> 
SQL> -- And now RecordFullClasses:
SQL> CREATE OR REPLACE PROCEDURE RecordFullClasses AS
  2    CURSOR c_Classes IS
  3  	 SELECT department, course
  4  	   FROM UserA.classes;
  5  BEGIN
  6    FOR v_ClassRecord IN c_Classes LOOP
  7  	 -- Record all classes which don't have very much room left
  8  	 -- in temp_table.
  9  	 IF AlmostFull(v_ClassRecord.department,
 10  			     v_ClassRecord.course) THEN
 11  	   INSERT INTO temp_table (char_col) VALUES
 12  	     (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
 13  	      ' is almost full!');
 14  	 END IF;
 15    END LOOP;
 16  END RecordFullClasses;
 17  /

Procedure created.

SQL> 
SQL> -- Now that all of the objects have been created, grant EXECUTE on
SQL> -- RecordFullClasses to UserB.  Note that UserB has no other
SQL> -- privilegs on UserA's objects.
SQL> GRANT EXECUTE ON RecordFullClasses to UserB;

Grant succeeded.

SQL> 
SQL> -- connect as UserB, and run RecordFullClasses.  The results will
SQL> -- be stored in temp_table owned by UserA, since that is the only
SQL> -- copy of temp_table.
SQL> connect UserB/UserB
Connected.
SQL> BEGIN
  2    UserA.RecordFullClasses;
  3    COMMIT;
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> -- Query temp_table as UserA to verify the results.
SQL> connect UserA/UserA
Connected.
SQL> SELECT * FROM temp_table;

   NUM_COL CHAR_COL                                                             
---------- ------------------------------------------------------------         
           MUS 410 is almost full!                                              

SQL> 
SQL> -- ***********************************
SQL> -- Scenario illustrated by Figure 5-15: UserB also owns a copy of
SQL> -- 				     temp_table.
SQL> -- ***********************************
SQL> 
SQL> -- Create UserB.temp_table.
SQL> connect UserB/UserB
Connected.
SQL> CREATE TABLE temp_table (
  2    num_col	  NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL> 
SQL> -- Now if we call UserA.RecordFullClasses, UserA's copy of
SQL> -- temp_table gets modified.
SQL> BEGIN
  2    UserA.RecordFullClasses;
  3    COMMIT;
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> -- Query UserB's table: this should return no rows.
SQL> SELECT * FROM temp_table;

no rows selected

SQL> 
SQL> -- Query UserA's table: this should return two rows - one for
SQL> -- each of the executions.
SQL> connect UserA/UserA
Connected.
SQL> SELECT * FROM temp_table;

   NUM_COL CHAR_COL                                                             
---------- ------------------------------------------------------------         
           MUS 410 is almost full!                                              
           MUS 410 is almost full!                                              

SQL> 
SQL> -- ***********************************
SQL> -- Scenario illustrated by Figure 5-16: UserB owns temp_table and
SQL> -- 				     RecordFullClasses, GRANTs
SQL> -- 				     done directly.
SQL> -- ***********************************
SQL> 
SQL> -- First drop them from UserA:
SQL> connect UserA/UserA
Connected.
SQL> DROP TABLE temp_table;

Table dropped.

SQL> DROP PROCEDURE RecordFullClasses;

Procedure dropped.

SQL> 
SQL> -- We now need to GRANT privileges on AlmostFull and Classes
SQL> -- to UserB:
SQL> GRANT SELECT ON classes TO UserB;

Grant succeeded.

SQL> GRANT EXECUTE ON AlmostFull TO UserB;

Grant succeeded.

SQL> 
SQL> -- UserB already owns temp_table, so we just need to create the
SQL> -- procedure.  Note that this refers to AlmostFull in UserA's
SQL> -- schema through dot notation.  If the above GRANTs were not done,
SQL> -- this would not compile.
SQL> connect UserB/UserB
Connected.
SQL> CREATE OR REPLACE PROCEDURE RecordFullClasses AS
  2    CURSOR c_Classes IS
  3  	 SELECT department, course
  4  	   FROM UserA.classes;
  5  BEGIN
  6    FOR v_ClassRecord IN c_Classes LOOP
  7  	 -- Record all classes which don't have very much room left
  8  	 -- in temp_table.
  9  	 IF UserA.AlmostFull(v_ClassRecord.department,
 10  			     v_ClassRecord.course) THEN
 11  	   INSERT INTO temp_table (char_col) VALUES
 12  	     (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
 13  	      ' is almost full!');
 14  	 END IF;
 15    END LOOP;
 16  END RecordFullClasses;
 17  /

Procedure created.

SQL> 
SQL> -- If we execute RecordFullClasses now, the results are stored in
SQL> -- temp_table owned by UserB.
SQL> BEGIN
  2    RecordFullClasses;
  3    COMMIT;
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> -- This should return one row.
SQL> SELECT * FROM temp_table;

   NUM_COL CHAR_COL                                                             
---------- ------------------------------------------------------------         
           MUS 410 is almost full!                                              

SQL> 
SQL> -- ***********************************
SQL> -- Scenario illustrated by Figure 5-17: UserB owns temp_table and
SQL> -- 				     RecordFullClasses, GRANTs
SQL> -- 				     done via a role.
SQL> -- ***********************************
SQL> 
SQL> connect UserA/UserA
Connected.
SQL> -- First revoke the earlier GRANTs
SQL> REVOKE SELECT ON classes FROM UserB;

Revoke succeeded.

SQL> REVOKE EXECUTE ON AlmostFull FROM UserB;

Revoke succeeded.

SQL> 
SQL> -- Now create the role
SQL> DROP ROLE UserA_Role;

Role dropped.

SQL> CREATE ROLE UserA_Role;

Role created.

SQL> GRANT SELECT ON classes TO UserA_Role;

Grant succeeded.

SQL> GRANT EXECUTE ON AlmostFull TO UserA_Role;

Grant succeeded.

SQL> GRANT UserA_Role TO UserB;

Grant succeeded.

SQL> 
SQL> -- Attempting to create RecordFullClasses in UserB will now result
SQL> -- in PLS-201 errors:
SQL> connect UserB/UserB
Connected.
SQL> CREATE OR REPLACE PROCEDURE RecordFullClasses AS
  2    CURSOR c_Classes IS
  3  	 SELECT department, course
  4  	   FROM UserA.classes;
  5  BEGIN
  6    FOR v_ClassRecord IN c_Classes LOOP
  7  	 -- Record all classes which don't have very much room left
  8  	 -- in temp_table.
  9  	 IF UserA.AlmostFull(v_ClassRecord.department,
 10  			     v_ClassRecord.course) THEN
 11  	   INSERT INTO temp_table (char_col) VALUES
 12  	     (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
 13  	      ' is almost full!');
 14  	 END IF;
 15    END LOOP;
 16  END RecordFullClasses;
 17  /

Warning: Procedure created with compilation errors.

SQL> 
SQL> show errors
Errors for PROCEDURE RECORDFULLCLASSES:

LINE/COL ERROR                                                                  
-------- -----------------------------------------------------------------      
3/5      PL/SQL: SQL Statement ignored                                          
4/12     PLS-00201: identifier 'USERA.CLASSES' must be declared                 
9/5      PL/SQL: Statement ignored                                              
9/8      PLS-00201: identifier 'USERA.ALMOSTFULL' must be declared              
SQL> 
SQL> exit