CS 422 - Database Programming I
Oracle 2 Day Developer Guide, Chapter 9: Developing a Simple Oracle
Database Application
Objectives:
This lesson discusses triggers, program code that runs when specific
events occur. Objectives important to this lesson:
- About the Application
- Creating the Schemas for the Application
- Granting Privileges to the Schemas
- Creating the Schema Objects and Loading the Data
- Creating the employees_pkg Package
- Creating the admin_pkg Package
Concepts:
About the Application
The text provides a longer chapter on creating an application in Oracle.
It begins by defining the two groups of users the application will have,
and the typical tasks each group will perform. The text also outlines
the objects the application will use:
- four tables
- four editioning views
- two triggers
- two sequences that generate primary keys
- two packages for the two user groups
Creating the Schemas for the Application
This application also uses five schemas, which can be imagined
as users who have specific rights to part of the database. On page
9-4, the text explains how we should create the five schemas, but first
we should delete them and their objects if they exist. Read
the list of schemas on page 9-4, and substitute each in the command
given at the top of page 9-5 in step 2. This will delete any existing
schemas with the names we are using. Then create the five schemas,
using the appropriate command from the two version shown in step 3.
Granting Privileges to the Schemas
Follow the instructions in the next section to grant privileges
to each schema. Note that the privileges are all generic
in this step. No objects have been created to allow privileges to be granted
to them yet.
Creating the Schema Objects and Loading the Data
The text has detailed steps on the next several pages to create the tables,
editioning views, triggers, and sequences. This is
on pages 9-8 through 9-13. Loading data is covered on pages 9-13
through 9-15. You also create a foreign key constraint and grant
several specific privileges on pages 9-15-and 9-16.
Creating the employees_pkg Package
The real work in the chapter begins on page 9-16, with the creation of
the first package. To make the code easier to understand, the text has
us create four synonyms for four tables. The tutorial continues
with the creation of a package specification, which is a declaration
of the package and the five procedures that are included
in the package. At the bottom on page 9-17, the text shows us how to create
the package body, which is the actual code for the package
and its procedures.
This is followed by a tutorial on how the package works, followed
by two commands on page 9-22 to grant execute privileges for this
package to two schemas. The text then demonstrates how these users can
use the package.
Creating the admin_pkg Package
The text goes through a similar sequence to create the second package,
beginning on page 9-23. This continues through the end of the chapter
on page 9-28.
|
