PL SQL Student Guide v-3

Unauthorized reproduction or distribution prohibited Copyright 2012, Oracle and/or its affiliates

nd
a
g

in
r
e
Oracle Database 10g:
neDevelop
i
g
n
PL/SQL Program
f E Units
o
te
u
t
i
t
Volume 3s Additional
y Practices
n
l
I
n
i
o
h
d
e
n
s
Ga gy u
d
n nolo
a
y
sit Tech
r
e
iv

U
cle
Ora
D17169GC21
Edition 2.1
December 2006
D48232
Authors
Copyright 2006, Oracle. All rights reserved.
Tulika Srivastava
Glenn Stokol
Disclaimer
Technical Contributors
and Reviewers
Chaitanya Koratamaddi
Dr. Christoph Burandt
Zarko Cesljas
Yanti Chang
Kathryn Cunningham
Burt Demchick
Laurent Dereac
Peter Driver
Bryan Roberts
Bryn Llewellyn
Nancy Greenberg
Craig Hollister
Thomas Hoogerwerf
Taj-Ul Islam
Inger Joergensen
Eric Lee
Malika Marghadi
Hildegard Mayr
Nagavalli Pataballa
Sunitha Patel
Srinivas Putrevu
Denis Raphaely
Helen Robertson
Grant Spencer
Glenn Stokol
Tone Thomas
Priya Vennapusa
Lex Van Der Werff
le
c
a
r
Graphic Designer
Satish Bettegowda
Nita Pavitran
Richard Wallis
Publisher
Sheryl Domingue
The information contained in this document is subject to change without notice. If you
find any problems in the document, please report them in writing to: Oracle University,
500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not
warranted to be error-free.
Restricted Rights Notice
If this documentation is delivered to the United States Government or anyone using
the documentation on behalf of the United States Government, the following notice is
applicable:
U.S. GOVERNMENT RIGHTS
The U.S. Governments rights to use, modify, reproduce, release, perform, display, or
disclose these training materials are restricted by the terms of the applicable Oracle
license agreement and/or the applicable U.S. Government contract.
nd
a
g
in
r
e
ne
Trademark Notice
gi
n
E
Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle

Corporation and/or its affiliates. Other names may be trademarks of their respective
owners.
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
Editors
This document contains proprietary information and is protected by copyright and

other intellectual property laws. You may copy and print this document solely for your
own use in an Oracle training course. The document may not be modified or altered in
any way. Except where your use constitutes "fair use" under copyright law, you may
not use, share, download, upload, copy, print, display, perform, reproduce, publish,
license, post, transmit, or distribute this document in whole or in part without the
express authorization of Oracle.
Contents
Preface
I
Introduction
Lesson Objectives I-2
Course Objectives I-3
Course Agenda I-4
Human Resources (HR) Schema I-7
Creating a Modularized and Layered Subprogram Design I-8
Modularizing Development with PL/SQL Blocks I-9
Review of Anonymous Blocks I-10
Introduction to PL/SQL Procedures I-11
Introduction to PL/SQL Functions I-12
Introduction to PL/SQL Packages I-13
Introduction to PL/SQL Triggers I-14
PL/SQL Execution Environment I-15
PL/SQL Development Environments I-16
Coding PL/SQL in iSQL*Plus I-17
Coding PL/SQL in SQL*Plus I-18
Coding PL/SQL in Oracle JDeveloper I-19
Summary I-20
Practice I: Overview I-21
le
c
a
r
1
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
Creating Stored Procedures

Objectives 1-2
What Is a Procedure? 1-3
Syntax for Creating Procedures 1-4
Developing Procedures 1-5
What Are Parameters? 1-6
Formal and Actual Parameters 1-7
Procedural Parameter Modes 1-8
Using IN Parameters: Example 1-9
Using OUT Parameters: Example 1-10
Viewing OUT Parameters with iSQL*Plus 1-11
Calling PL/SQL Using Host Variables 1-12
Using IN OUT Parameters: Example 1-13
iii
gi
n
E
in
r
e
ne
nd
a
g
Syntax for Passing Parameters 1-14

Parameter Passing: Examples 1-15
Using the DEFAULT Option for Parameters 1-16
Summary of Parameter Modes 1-18
Invoking Procedures 1-19
Handled Exceptions 1-20
Handled Exceptions: Example 1-21
Exceptions Not Handled 1-22
Exceptions Not Handled: Example 1-23
Removing Procedures 1-24
Viewing Procedures in the Data Dictionary 1-25
Benefits of Subprograms 1-26
Summary 1-27
Practice 1: Overview 1-29
2
in
r
e
ne
nd
a
g
Creating Stored Functions

Objectives 2-2
Overview of Stored Functions 2-3
Syntax for Creating Functions 2-4
Developing Functions 2-5
Stored Function: Example 2-6
Ways to Execute Functions 2-7
Advantages of User-Defined Functions in SQL Statements 2-8
Function in SQL Expressions: Example 2-9
Locations to Call User-Defined Functions 2-10
Restrictions on Calling Functions from SQL Expressions 2-11
Controlling Side Effects When Calling Functions from SQL Expressions 2-12
Restrictions on Calling Functions from SQL: Example 2-13
Removing Functions 2-14
Viewing Functions in the Data Dictionary 2-15
Procedures Versus Functions 2-16
Summary 2-17
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
iv
gi
n
E
Creating Packages
Objectives 3-2
PL/SQL Packages: Overview 3-3
Components of a PL/SQL Package 3-4
Visibility of Package Components 3-5
Developing PL/SQL Packages 3-6
Creating the Package Specification 3-7
Example of Package Specification: comm_pkg 3-8
Creating the Package Body 3-9
Example of Package Body: comm_pkg 3-10
Invoking Package Subprograms 3-11
Creating and Using Bodiless Packages 3-12
Removing Packages 3-13
Viewing Packages in the Data Dictionary 3-14
Guidelines for Writing Packages 3-15
Advantages of Using Packages 3-16
Summary 3-18
nd
a
g
in
r
e
ne
o
e
t
itu
t
s
4 Using More Package Concepts
n
ly
I
n
i
o
h
Objectives 4-2
nd use
Overloading Subprograms G
4-3a
gy
d
o
Overloading: Example
4-5
n
l
a hno
y
t
i
Overloading and
the
STANDARD
c Package 4-7
s T
r
e
e
Using Forward
iv Declarations 4-8
n
U Initialization Block 4-10
ePackage
l
c
Ora Using Package Functions in SQL and Restrictions 4-11
Package Function in SQL: Example 4-12
Persistent State of Packages 4-13
Persistent State of Package Variables: Example 4-14
Persistent State of a Package Cursor 4-15
Executing CURS_PKG 4-16
Using PL/SQL Tables of Records in Packages 4-17
PL/SQL Wrapper 4-18
Running the Wrapper 4-19
Results of Wrapping 4-20
Guidelines for Wrapping 4-21
Summary 4-22
gi
n
E
Using Oracle-Supplied Packages in Application Development

Objectives 5-2
Using Oracle-Supplied Packages 5-3
List of Some Oracle-Supplied Packages 5-4
How the DBMS_OUTPUT Package Works 5-5
Interacting with Operating System Files 5-6
File Processing Using the UTL_FILE Package 5-7
Exceptions in the UTL_FILE Package 5-8
FOPEN and IS_OPEN Function Parameters 5-9
Using UTL_FILE: Example 5-10
Generating Web Pages with the HTP Package 5-12
Using the HTP Package Procedures 5-13
Creating an HTML File with iSQL*Plus 5-14
Using UTL_MAIL 5-15
Installing and Using UTL_MAIL 5-16
Sending E-Mail with a Binary Attachment 5-17
Sending E-Mail with a Text Attachment 5-19
DBMS_SCHEDULER Package 5-21
Creating a Job 5-23
Creating a Job with In-Line Parameters 5-24
Creating a Job Using a Program 5-25
Creating a Job for a Program with Arguments 5-26
Creating a Job Using a Schedule 5-27
Setting the Repeat Interval for a Job 5-28
Creating a Job Using a Named Program and Schedule 5-29
Managing Jobs 5-30
Data Dictionary Views 5-31
Summary 5-32
le
c
a
r
in
r
e
ne
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
O
6
nd
a
g
Dynamic SQL and Metadata

Objectives 6-2
Execution Flow of SQL 6-3
Dynamic SQL 6-4
Native Dynamic SQL 6-5
Using the EXECUTE IMMEDIATE Statement 6-6
Dynamic SQL with a DDL Statement 6-7
Dynamic SQL with DML Statements 6-8
Dynamic SQL with a Single-Row Query 6-9
Dynamic SQL with a Multirow Query 6-10
vi
gi
n
E
Declaring Cursor Variables 6-11

Dynamically Executing a PL/SQL Block 6-12
Using Native Dynamic SQL to Compile PL/SQL Code 6-13
Using the DBMS_SQL Package 6-14
Using DBMS_SQL with a DML Statement 6-15
Using DBMS_SQL with a Parameterized DML Statement 6-16
Comparison of Native Dynamic SQL and the DBMS_SQL Package 6-17
DBMS_METADATA Package 6-18
Metadata API 6-19
Subprograms in DBMS_METADATA 6-20
FETCH_xxx Subprograms 6-21
SET_FILTER Procedure 6-22
Filters 6-23
Examples of Setting Filters 6-24
Programmatic Use: Example 1 6-25
Programmatic Use: Example 2 6-27
Browsing APIs 6-29
Browsing APIs: Examples 6-30
Summary 6-32
nd
a
g
in
r
e
ne
o
e
t
itu
t
s
In only
i
h
nd use
a
y
7 Design Considerations forG
PL/SQLgCode
d
o
n
l
a hno
Objectives 7-2
y
t
i
cand Exceptions 7-3
Standardizing
rsConstants
e
e
T
v
Standardizing
ni Exceptions 7-4
U
leStandardizing Exception Handling 7-5
c
a
Or Standardizing Constants 7-6
gi
n
E
Local Subprograms 7-7

Definers Rights Versus Invokers Rights 7-8
Specifying Invokers Rights 7-9
Autonomous Transactions 7-10
Features of Autonomous Transactions 7-11
Using Autonomous Transactions 7-12
RETURNING Clause 7-13
Bulk Binding 7-14
Using Bulk Binding 7-15
Bulk Binding FORALL: Example 7-16
Using BULK COLLECT INTO with Queries 7-18

Using BULK COLLECT INTO with Cursors 7-19
Using BULK COLLECT INTO with a RETURNING Clause 7-20
vii
Using the NOCOPY Hint 7-21

Effects of the NOCOPY Hint 7-22
NOCOPY Hint Can Be Ignored 7-23

PARALLEL_ENABLE Hint 7-24
Summary 7-25
8
Managing Dependencies
Objectives 8-2
Understanding Dependencies 8-3
Dependencies 8-4
Local Dependencies 8-5
A Scenario of Local Dependencies 8-7
Displaying Direct Dependencies by Using USER_DEPENDENCIES 8-8
Displaying Direct and Indirect Dependencies 8-9
Displaying Dependencies 8-10
Another Scenario of Local Dependencies 8-11
A Scenario of Local Naming Dependencies 8-12
Understanding Remote Dependencies 8-13
Concepts of Remote Dependencies 8-15
REMOTE_DEPENDENCIES_MODE Parameter 8-16
Remote Dependencies and Time Stamp Mode 8-17
Remote Procedure B Compiles at 8:00 a.m. 8-19
Local Procedure A Compiles at 9:00 a.m. 8-20
Execute Procedure A 8-21
Remote Procedure B Recompiled at 11:00 a.m. 8-22
Execute Procedure A 8-23
Signature Mode 8-24
Recompiling a PL/SQL Program Unit 8-25
Unsuccessful Recompilation 8-26
Successful Recompilation 8-27
Recompilation of Procedures 8-28
Packages and Dependencies 8-29
Summary 8-31
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
viii
gi
n
E
in
r
e
ne
nd
a
g
Manipulating Large Objects

Objectives 9-2
What Is a LOB? 9-3
Contrasting LONG and LOB Data Types 9-5

Anatomy of a LOB 9-6
Internal LOBs 9-7
Managing Internal LOBs 9-8
What Are BFILEs? 9-9
Securing BFILEs 9-10
A New Database Object: DIRECTORY 9-11
Guidelines for Creating DIRECTORY Objects 9-12
Managing BFILEs 9-13
Preparing to Use BFILEs 9-14
nd
a
g
Populating BFILE Columns with SQL 9-15
in
r
e
ne
Populating a BFILE Column with PL/SQL 9-16

Using DBMS_LOB Routines with BFILEs 9-17
Migrating from LONG to LOB 9-18
o
e
t
itu
t
DBMS_LOB.READ and DBMS_LOB.WRITE 9-23
s
In 9-24only
i
Initializing LOB Columns Added to a
Table
h
nd use
a
Populating LOB Columns 9-25
G gy
d
n
loin PL/SQL 9-26
Updating LOB by Using
DBMS_LOB
a
o
n
y
t ebycUsing
h SQL 9-27
Selecting CLOB
siValues
r
e
ivCLOB ValuesT by Using DBMS_LOB 9-28
Selecting
n
U CLOB Values in PL/SQL 9-29
leSelecting
DBMS_LOB Package 9-20
Ora
Removing LOBs 9-30

Temporary LOBs 9-31
Creating a Temporary LOB 9-32
Summary 9-33
10 Creating Triggers
Objectives 10-2
Types of Triggers 10-3
Guidelines for Designing Triggers 10-4
Creating DML Triggers 10-5
Types of DML Triggers 10-6
Trigger Timing 10-7
Trigger-Firing Sequence 10-8
ix
gi
n
E
Trigger Event Types and Body 10-10

Creating a DML Statement Trigger 10-11
Testing SECURE_EMP 10-12
Using Conditional Predicates 10-13
Creating a DML Row Trigger 10-14
Using OLD and NEW Qualifiers 10-15
Using OLD and NEW Qualifiers: Example Using AUDIT_EMP 10-16
Restricting a Row Trigger: Example 10-17
Summary of the Trigger Execution Model 10-18
Implementing an Integrity Constraint with a Trigger 10-19
INSTEAD OF Triggers 10-20
Creating an INSTEAD OF Trigger 10-21
Comparison of Database Triggers and Stored Procedures 10-24
Comparison of Database Triggers and Oracle Forms Triggers 10-25
Managing Triggers 10-26
Removing Triggers 10-27
Testing Triggers 10-28
Summary 10-29
o
e
t
itu
t
s
In only
i
h
11 Applications for Triggers
nd use
a
Objectives 11-2
G
y
nd nolog
Creating Database Triggersa11-3
h 11-4
ityStatements
Creating Triggers onrs
DDL
c
e
e
iv on SystemTEvents 11-5
Creating Triggers
n
U
LOGONle
and LOGOFF Triggers: Example 11-6
c
ra Statements 11-7
OCALL
Reading Data from a Mutating Table 11-8

Mutating Table: Example 11-9
Benefits of Database Triggers 11-11
Managing Triggers 11-12
Business Application Scenarios for Implementing Triggers 11-13
Viewing Trigger Information 11-14
Using USER_TRIGGERS 11-15
Listing the Code of Triggers 11-16
Summary 11-17
gi
n
E
in
r
e
ne
nd
a
g
12 Understanding and Influencing the PL/SQL Compiler

Objectives 12-2
Native and Interpreted Compilation 12-3
Features and Benefits of Native Compilation 12-4
Considerations When Using Native Compilation 12-5
Parameters Influencing Compilation 12-6
Switching Between Native and Interpreted Compilation 12-7
Viewing Compilation Information in the Data Dictionary 12-8
Using Native Compilation 12-9
Compiler Warning Infrastructure 12-10
Setting Compiler Warning Levels 12-11
Guidelines for Using PLSQL_WARNINGS 12-12
DBMS_WARNING Package 12-13
nd
a
g
Using DBMS_WARNING Procedures 12-14

Using DBMS_WARNING Functions 12-15
Using DBMS_WARNING: Example 12-16
Summary 12-18
in
r
e
ne
o
e
t
itu
t
s
Appendix A: Practice Solutions
In only
i
h
Appendix B: Table Descriptions and
ndData use
a
G gTriggers
y
Appendix C: Studies for Implementing
d
o
n
l
a hno
y
Appendix D: Review
of PL/SQL
t
i
rs Tec
e
Appendix E:
JDeveloper
v
ni
U
Appendix
le F: Using SQL Developer
c
a
Or Index
Additional Practices
Additional Practice: Solutions
Additional Practices: Table Descriptions and Data
xi
gi
n
E
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Preface
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Profile
Before You Begin This Course
Before you begin this course, you should have thorough knowledge of SQL and iSQL*Plus, as
well as working experience in developing applications. Prerequisites are any of the following
Oracle University courses or combinations of courses:
Oracle Database 10g: Introduction to SQL
Oracle Database 10g: SQL Fundamentals I and Oracle Database 10g: SQL
Fundamentals II
Oracle Database 10g: SQL and PL/SQL Fundamentals
Oracle Database 10g: PL/SQL Fundamentals
How This Course Is Organized
Oracle Database 10g: Develop PL/SQL Program Units is an instructor-led course featuring
lectures and hands-on exercises. Online demonstrations and practice sessions reinforce the
concepts and skills that are introduced.
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
Preface-3
gi
n
E
Related Publications
Oracle Publications
Title
Oracle Database Application Developers Guide Fundamentals
(10g Release 1)
Oracle Database Application Developers Guide Large Objects
(10g Release 1)
PL/SQL Packages and Types Reference (10g Release 1)
PL/SQL Users Guide and Reference (10g Release 1)
Part Number
B10795-01
B10796-01
B10802-01
B10807-01
Additional Publications
System release bulletins
Installation and users guides
Read-me files
International Oracle Users Group (IOUG) articles
Oracle Magazine
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
Preface-4
gi
n
E
Typographic Conventions
Typographic Conventions in Text

Convention
Element
Example
Bold
Emphasized words and phrases To navigate within this application, do

in Web content only
not click the Back and Forward buttons.
Bold italic
Glossary terms (if there is a

glossary)
The algorithm inserts the new key.
Brackets
Key names
Press [Enter].
Caps and
lowercase
Buttons,
check boxes,
triggers,
windows
Click the Executable button.

Select the Registration Required check
box.
in
r
e
ne
Assign a When-Validate-Item trigger.
gi
n
E
Open the Master Schedule window.

Carets
f
o
e
ut
t
i
t
ns nly
I
i
o these keys one at a
h and erelease
Key sequences
Press
d
n
s
u
Ga gtime:
y
d
n nolo[Alt], [F], [D]
a
y
sit Tech
r
e
iv
n
U
Menu paths
Commas
cl
a
r
O
nd
a
g
Select File > Save.
Preface-5
Typographic Conventions (continued)
Typographic Conventions in Text (continued)

Convention
Object or Term
Example
Courier New,
case sensitive
Code output,
SQL and PL/SQL
code elements, Java
code elements,
directory names,
filenames,
passwords,
pathnames, URLs,
user input,
usernames
Code output: debug.seti (I,300);

SQL code elements: Use the SELECT command to view
information stored in the last_name column of the emp
table.
Java code elements: Java programming involves the
String and StringBuffer classes.
Directory names: bin (DOS), $FMHOME (UNIX)
File names: Locate the init.ora file.
Passwords: Use tiger as your password.
nd
a
g
Path names: Open c:\my_docs\projects.
in
r
e
ne
URLs: Go to http://www.oracle.com.
i
g
n
Usernames: Log on as scott. E
f
o
e
Graphics labels
Customer address (but
Oracle
t Payables)
u
t
i
t
(unless the term is a
ns nly
I
proper noun)
i
o
h
d
e
n
s
Emphasized words
changes
a not save
u to the database.
GDo
y
and phrases in print
g
d
o information, see Oracle7 Server SQL
n nForolfurther
a
publications,
titles
Language
Reference Manual.
y
itand ech
of books
s
r
T
Enter [email protected], where user_id is
ve variables
icourses,
User input: Enter 300.
Initial cap
Italic
U
e
l
c
Plus signs
Ora
Quotation
marks
the name of the user.
Key combinations
Press and hold these keys simultaneously:

[Control] + [Alt] + [Delete]
Lesson and chapter

titles in cross
references, interface
elements with long
names that have
only initial caps
This subject is covered in Unit II, Lesson 3, Working

with Objects.
Select the Include a reusable module component and
click Finish.
Use the WHERE clause of query property.
Preface-6
Typographic Conventions (continued)

Typographic Conventions in Navigation Paths
This course uses simplified navigation paths to direct you through Oracle applications, as in
the following example.
Invoice Batch Summary
(N) Invoice > Entry > Invoice Batches Summary (M) Query > Find (B) Approve
This simplified path translates to the following sequence of steps:
1. (N) From the Navigator window, select Invoice > Entry > Invoice Batches Summary.
2. (M) From the menu, select Query > Find.
3. (B) Click the Approve button.
Notation:
(N) = Navigator
(I) = icon
(M) = menu
(H) = hyperlink
(T) = tab
(B) = button
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
Preface-7
gi
n
E
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Additional
Practices
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Additional Practices: Overview

These additional practices are provided as a supplement to the course Oracle Database 10g:
Develop PL/SQL Program Units. In these practices, you apply the concepts that you learned
in the course.
The additional practices comprise two parts:
Part A provides supplemental exercises to create stored procedures, functions, packages, and
triggers, and to use the Oracle-supplied packages with iSQL*Plus as the development
environment. The tables used in this portion of the additional practice include EMPLOYEES,
JOBS, JOB_HISTORY, and DEPARTMENTS.
Part B is a case study that can be completed at the end of the course. This part supplements
the practices for creating and managing program units. The tables used in the case study are
based on a video database and contain the TITLE, TITLE_COPY, RENTAL,
RESERVATION, and MEMBER tables.
An entity relationship diagram is provided at the start of part A and part B. Each entity
relationship diagram displays the table entities and their relationships. More detailed
and
definitions of the tables and the data contained in them is provided in the appendixgtitled
Additional Practices: Table Descriptions and Data.
erin
n
ngi
le
c
a
r
fE
o
ute
t
i
t
ns nly
I
i
o
h
d
e
n
s
Ga gy u
d
n nolo
a
y
sit Tech
r
e
niv
Oracle Database 10g: Develop PL/SQL Program Units AP-2
Part A
Entity Relationship Diagram
Human Resources:
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part A (continued)
Note: These exercises can be used for extra practice when discussing how to create
procedures.
1. In this exercise, create a program to add a new job into the JOBS table.
a. Create a stored procedure called NEW_JOB to enter a new order into the JOBS
table. The procedure should accept three parameters. The first and second
parameters supply a job ID and a job title. The third parameter supplies the
minimum salary. Use the maximum salary for the new job as twice the minimum
salary supplied for the job ID.
b. Invoke the procedure to add a new job with job ID 'SY_ANAL', job title
'System Analyst', and minimum salary of 6000.
c. Check whether a row was added and note the new job ID for use in the next
exercise. Commit the changes.
2. In this exercise, create a program to add a new row to the JOB_HISTORY table, for an
existing employee.
a. Create a stored procedure called ADD_JOB_HIST to add a new row into the
ndID
JOB_HISTORY table for an employee who is changing his job to the newajob
ing
('SY_ANAL') that you created in exercise 1b.
r
e
The procedure should provide two parameters, one for the employee
ne ID who is
i
g
changing the job, and the second for the new job ID. fRead
Enthe employee ID from
o
the EMPLOYEES table and insert it into the JOB_HISTORY
table. Make the hire
e
t
u
t
date of this employee as start date and todays
ti date
as end date for this row in the
s
y
n
l
I
JOB_HISTORY table.
i
on
h
d
e
Change the hire date of this employee
an y uinsthe EMPLOYEES table to todays date.
G
Update the job ID of this
g to the job ID passed as parameter (use the
d employee
o
n
l
a
o
'SY_ANAL' job
n equal to the minimum salary for that job ID +
ty ID)eand
hsalary
i
c
s
r
500.
e
T
v
i
n
Note:
Include
exception
handling to handle an attempt to insert a nonexistent
U
e
l
c employee.
Orab. Disable all triggers on the EMPLOYEES, JOBS, and JOB_HISTORY tables
before invoking the ADD_JOB_HIST procedure.
c. Execute the procedure with employee ID 106 and job ID 'SY_ANAL' as
parameters.
d. Query the JOB_HISTORY and EMPLOYEES tables to view your changes for
employee 106, and then commit the changes.
e. Re-enable the triggers on the EMPLOYEES, JOBS, and JOB_HISTORY tables.
3. In this exercise, create a program to update the minimum and maximum salaries for a
job in the JOBS table.
a. Create a stored procedure called UPD_JOBSAL to update the minimum and
maximum salaries for a specific job ID in the JOBS table. The procedure should
provide three parameters: the job ID, a new minimum salary, and a new
maximum salary. Add exception handling to account for an invalid job ID in the
JOBS table. Raise an exception if the maximum salary supplied is less than the
minimum salary, and provide a message that will be displayed if the row in the
JOBS table is locked.
Hint: The resource locked/busy error number is 54.
Part A (continued)
b. Execute the UPD_JOBSAL procedure by using a job ID of 'SY_ANAL', a
minimum salary of 7000 and a maximum salary of 140.
Note: This should generate an exception message.
c. Disable triggers on the EMPLOYEES and JOBS tables.
d. Execute the UPD_JOBSAL procedure using a job ID of 'SY_ANAL', a
minimum salary of 7000, and a maximum salary of 14000.
e. Query the JOBS table to view your changes, and then commit the changes.
f. Enable the triggers on the EMPLOYEES and JOBS tables.
4. In this exercise, create a procedure to monitor whether employees have exceeded their
average salaries for their job type.
a. Disable the SECURE_EMPLOYEES trigger.
b. In the EMPLOYEES table, add an EXCEED_AVGSAL column to store up to three
characters and a default value of NO. Use a check constraint to allow the values
YES or NO.
c. Write a stored procedure called CHECK_AVGSAL which checks whether eachd
an
employees salary exceeds the average salary for the JOB_ID. The average
g
inIf the
r
salary for a job is calculated from the information in the JOBS table.
e
netheir
i
employees salary exceeds the average for their job, then update
g
Eanvalue of YES;
EXCEED_AVGSAL column in the EMPLOYEES tablef to
o
otherwise, set the value to NO. Use a cursor touselect
te the employees rows using
t
i
t
the FOR UPDATE option in the query. n
Add
s exception
y handling to account for a
l
I
n
i
o
record being locked.
h
d
e
n
s
Hint: The resource locked/busy
number
is 54. Write and use a local
a error
u
G
y
g
d
function called GET_JOB_AVGSAL
n nolo to determine the average salary for a job ID
a
y
specified as asparameter.
it ech
r
e
d. Executeivthe CHECK_AVGSAL
procedure. Then, to view the results of your
T
n
U
modifications,
write a query to display the employees ID, job, the average salary
efor
l
c
the
job,
the
employees salary and the exceed_avgsal indicator column
Ora for employees whose salaries exceed the average for their job, and finally
commit the changes.
functions.
5. Create a subprogram to retrieve the number of years of service for a specific employee.
a. Create a stored function called GET_YEARS_SERVICE to retrieve the total
number of years of service for a specific employee. The function should accept
the employee ID as a parameter and return the number of years of service. Add
error handling to account for an invalid employee ID.
b. Invoke the GET_YEARS_SERVICE function in a call to
DBMS_OUTPUT.PUT_LINE for an employee with ID 999.
c. Display the number of years of service for employee 106 with
DBMS_OUTPUT.PUT_LINE invoking the GET_YEARS_SERVICE function.
d. Query the JOB_HISTORY and EMPLOYEES tables for the specified employee to
verify that the modifications are accurate. The values represented in the results
on this page may differ from those you get when you run these queries.
Part A (continued)
6. In this exercise, create a program to retrieve the number of different jobs that an
employee worked on during his or her service.
a. Create a stored function called GET_JOB_COUNT to retrieve the total number of
different jobs on which an employee worked.
The function should accept the employee ID in a parameter, and return the
number of different jobs that the employee worked on until now, including the
present job. Add exception handling to account for an invalid employee ID.
Hint: Use the distinct job IDs from the JOB_HISTORY table, and exclude the
current job ID, if it is one of the job IDs on which the employee has already
worked. Write a UNION of two queries and count the rows retrieved into a
PL/SQL table. Use a FETCH with BULK COLLECT INTO to obtain the unique
jobs for the employee.
b. Invoke the function for the employee with the ID of 176.
packages.
nd
a
7. Create a package called EMPJOB_PKG that contains your NEW_JOB,
ng
i
r
e
ADD_JOB_HIST, UPD_JOBSAL procedures, as well as your
ne
GET_YEARS_SERVICE and GET_JOB_COUNT functions. ngi
f E constructs as public.
a. Create the package specification with all the subprogram
o
e package specification.
Move any subprogram local-defined types into
utthe
t
i
t
b. Create the package body with the subprogram
remember to
y
ns implementation;
l
I
n
i
o
h
remove, from the subprogram n
implementations,
d
e any types that you moved into
s
a
u
the package specification.G
gy procedure to create a new job with the
d
o
n
l
c. Invoke your EMPJOB_PKG.NEW_JOB
a hno
y
t
i
ID PR_MAN,
the
job
c Public Relations Manager, and the salary
s Ttitle
r
e
e
v
6,250.
i
n
U
d. le
Invoke your EMPJOB_PKG.ADD_JOB_HIST procedure to modify the job of
c
a
employee ID 110 to job ID PR_MAN.
Or Note:
You need to disable the UPDATE_JOB_HISTORY trigger before you
execute the ADD_JOB_HIST procedure, and re-enable the trigger after you have
executed the procedure.
e. Query the JOBS, JOB_HISTORY, and EMPLOYEES tables to verify the results.
Note: These exercises can be used for extra practice when discussing how to create database
triggers.
8. In this exercise, create a trigger to ensure that the minimum and maximum salaries of a
job are never modified such that the salary of an existing employee with that job ID is
out of the new range specified for the job.
a. Create a trigger called CHECK_SAL_RANGE that is fired before every row that is
updated in the MIN_SALARY and MAX_SALARY columns in the JOBS table.
For any minimum or maximum salary value that is changed, check whether the
salary of any existing employee with that job ID in the EMPLOYEES table falls
within the new range of salaries specified for this job ID. Include exception
handling to cover a salary range change that affects the record of any existing
employee.
Part A (continued)
b. Test the trigger using the SY_ANAL job, setting the new minimum salary to
5,000, and the new maximum salary to 7,000. Before you make the change,
write a query to display the current salary range for the SY_ANAL job ID, and
another query to display the employee ID, last name, and salary for the same job
ID. After the update, query the change (if any) to the JOBS table for the specified
job ID.
c. Using the SY_ANAL job, set the new minimum salary to 7,000, and the new
maximum salary to 18,000. Explain the results.
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B
Entity Relationship Diagram
TITLE
for
RESERVATION
#* reservation date
the subject
of
set up for
#* ID
* title
* description
o rating
o category
o release date
available as
nd
a
g
a copy
in
r
e
ne
gi
n
E
f
o
TITLE_COPY
e
ut #* ID
t
i
t
ns nly * status
I
i
o
h
d
e
n
s
Ga gy u
d
the subject of
n nolo
a
y
responsible
sit Tech
for iver
n
U
e
cl MEMBER
made against
a
r
O
#* ID
* last name
o first name
o address
o city
o phone
* join date
responsible
for
created
for
RENTAL
#* book date
o act ret date
o exp ret date
Part B (continued)
In this case study, you create a package named VIDEO_PKG that contains procedures and
functions for a video store application. This application enables customers to become a
member of the video store. Any member can rent movies, return rented movies, and reserve
movies. Additionally, you create a trigger to ensure that any data in the video tables is
modified only during business hours.
Create the package by using iSQL*Plus and use the DBMS_OUTPUT Oracle-supplied
package to display messages.
The video store database contains the following tables: TITLE, TITLE_COPY, RENTAL,
RESERVATION, and MEMBER. The entity relationship diagram is shown on the previous
page.
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B (continued)
1. Load and execute the E:\labs\PLPU\labs\buildvid1.sql script to create all
the required tables and sequences that are needed for this exercise.
2. Load and execute the E:\labs\PLPU\labs\buildvid2.sql script to populate
all the tables created through the buildvid1.sql script.
3. Create a package named VIDEO_PKG with the following procedures and functions:
a. NEW_MEMBER: A public procedure that adds a new member to the MEMBER
table. For the member ID number, use the sequence MEMBER_ID_SEQ; for the
join date, use SYSDATE. Pass all other values to be inserted into a new row as
parameters.
b. NEW_RENTAL: An overloaded public function to record a new rental. Pass the
title ID number for the video that a customer wants to rent, and either the
customers last name or his member ID number into the function. The function
should return the due date for the video. Due dates are three days from the date
the video is rented. If the status for a movie requested is listed as AVAILABLE in
the TITLE_COPY table for one copy of this title, then update this TITLE_COPY
nd
a
table and set the status to RENTED. If there is no copy available, the function
ng
iidentifying
r
must return NULL. Then, insert a new record into the RENTAL table
e
ne ID number,
i
the booked date as todays date, the copy ID number, the member
g
En of multiple
the title ID number, and the expected return date. Befaware
o
customers with the same last name. In this case,
tehave the function return NULL,
u
t
i
t
and display a list of the customers names
s that match
y and their ID numbers.
n
l
I
n
i
c. RETURN_MOVIE: A public procedure
o the status of a video
h thateupdates
d
n
s
(available, rented, or damaged)
a andysetsu the return date. Pass the title ID, the
G
d
procedure.
Check whether there are reservations
copy ID, and the status
og
n to this
l
a
o
n
y
for that title and
ahmessage if it is reserved. Update the RENTAL table
it display
c
s
r
e
e
T date to todays date. Update the status in the
and setithe
v actual return
n
table based on the status parameter passed into the procedure.
U
eTITLE_COPY
l
c
d.
RESERVE_MOVIE:
A private procedure that executes only if all the video
Ora copies requested in the NEW_RENTAL procedure have a status of RENTED. Pass
the member ID number and the title ID number to this procedure. Insert a new
record into the RESERVATION table and record the reservation date, member
ID number, and title ID number. Print a message indicating that a movie is
reserved and its expected date of return.
e. EXCEPTION_HANDLER: A private procedure that is called from the exception
handler of the public programs. Pass the SQLCODE number to this procedure,
and the name of the program (as a text string) where the error occurred. Use
RAISE_APPLICATION_ERROR to raise a customized error. Start with a
unique key violation (-1) and foreign key violation (-2292). Allow the
exception handler to raise a generic error for any other errors.
Part B (continued)
4. Use the following scripts located in the E:\labs\PLPU\soln directory to test your
routines:
a. Add two members using sol_apb_04_a_new_members.sql.
b. Add new video rentals using sol_apb_04_b_new_rentals.sql.
c. Return movies using the sol_apb_04_c_return_movie.sql script.
5. The business hours for the video store are 8:00 a.m. to 10:00 p.m., Sunday through
Friday, and 8:00 a.m. to 12:00 a.m. on Saturday. To ensure that the tables can be
modified only during these hours, create a stored procedure that is called by triggers on
the tables.
a. Create a stored procedure called TIME_CHECK that checks the current time
against business hours. If the current time is not within business hours, use the
RAISE_APPLICATION_ERROR procedure to give an appropriate message.
b. Create a trigger on each of the five tables. Fire the trigger before data is inserted,
updated, and deleted from the tables. Call your TIME_CHECK procedure from
each of these triggers.
d
a
g
n
i
er
n
ngi
le
c
a
r
fE
o
ute
t
i
t
ns nly
I
i
o
h
d
e
n
s
Ga gy u
d
n nolo
a
y
sit Tech
r
e
niv
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
_________
Additional
Practice:
Solutions
_________
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part A: Additional Practice 1 Solutions
1. In this exercise, create a program to add a new job into the JOBS table.
a. Create a stored procedure called NEW_JOB to enter a new order into the JOBS table.
The procedure should accept three parameters. The first and second parameters supply a
job ID and a job title. The third parameter supplies the minimum salary. Use the
maximum salary for the new job as twice the minimum salary supplied for the job ID.
CREATE OR REPLACE PROCEDURE new_job(
jobid IN jobs.job_id%TYPE,
title IN jobs.job_title%TYPE,
minsal IN jobs.min_salary%TYPE) IS
maxsal jobs.max_salary%TYPE := 2 * minsal;
BEGIN
INSERT INTO jobs(job_id, job_title, min_salary, max_salary)
VALUES (jobid, title, minsal, maxsal);
DBMS_OUTPUT.PUT_LINE ('New row added to JOBS table:');
DBMS_OUTPUT.PUT_LINE (jobid || ' ' || title ||' '||
minsal || ' ' || maxsal);
END new_job;
/
SHOW ERRORS
nd
a
g
in
r
e
ne
gi
n
E
f
o
e
ut
t
i
Procedure created.
t
ns nly
I
i
o
h
No errors.
d
e
n
s
Ga gy u
d
na newnjob
b. Invoke the procedure to a
add
lowith job ID 'SY_ANAL', job title
o
y
h
'System Analyst',
salary 6,000.
sit Tandecminimum
r
e
iv ON
SET SERVEROUTPUT
n
EXECUTE lnew_job
e U ('SY_ANAL', 'System Analyst', 6000)
c
Ora
New row added to JOBS table:
SY_ANAL System Analyst 6000 12000
PL/SQL procedure successfully completed.
c. Verify that a row was added, and note the new job ID for use in the next exercise.
Commit the changes.
SELECT *
FROM
jobs
WHERE job_id = 'SY_ANAL';
COMMIT;
Commit complete.
Oracle Database 10g: Develop PL/SQL Program Units APS-2
2. In this exercise, create a program to add a new row to the JOB_HISTORY table for an
existing employee.
a. Create a stored procedure called ADD_JOB_HIST to add a new row into the
JOB_HISTORY table for an employee who is changing his job to the new job ID
('SY_ANAL') that you created in exercise1b.
The procedure should provide two parameters: one for the employee ID who is changing
the job, and the second for the new job ID. Read the employee ID from the EMPLOYEES
table and insert it into the JOB_HISTORY table. Make the hire date of this employee as
the start date and todays date as the end date for this row in the JOB_HISTORY table.
Change the hire date of this employee in the EMPLOYEES table to todays date. Update
the job ID of this employee to the job ID passed as parameter (use the 'SY_ANAL' job
ID) and salary equal to the minimum salary for that job ID plus 500.
nd
a
g
in
r
e
Note: Include exception handling to handle an attempt to insert a nonexistent
ne employee.
i
g
En
CREATE OR REPLACE PROCEDURE add_job_hist(
f
o
emp_id
IN employees.employee_id%TYPE,
ute
t
new_jobid IN jobs.job_id%TYPE) IS
i
t
BEGIN
ns nly
I
i
o
h
INSERT INTO job_history
d
e
n
s
SELECT employee_id, hire_date,
u job_id, department_id
Ga gSYSDATE,
y
FROM
employees
d
nemp_id;
lo
a
o
WHERE employee_id
=
n
y
h
UPDATE employeesrsit
c
e
e
T
SET hire_date
= SYSDATE,
iv = new_jobid,
n
job_id
U
esalary
l
= (SELECT min_salary + 500
c
FROM
jobs
Ora
WHERE job_id = new_jobid)
WHERE employee_id = emp_id;
DBMS_OUTPUT.PUT_LINE ('Added employee ' || emp_id ||
' details to the JOB_HISTORY table');
DBMS_OUTPUT.PUT_LINE ('Updated current job of employee ' ||
emp_id|| ' to '|| new_jobid);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20001, 'Employee does not exist!');
END add_job_hist;
/
SHOW ERRORS
Procedure created.
No errors.
Part A: Additional Practice 2 Solutions (continued)
b. Disable all triggers on the EMPLOYEES, JOBS, and JOB_HISTORY tables before
invoking the ADD_JOB_HIST procedure.
ALTER TABLE employees DISABLE ALL TRIGGERS;
ALTER TABLE jobs DISABLE ALL TRIGGERS;
ALTER TABLE job_history DISABLE ALL TRIGGERS;
Table altered.
Table altered.
Table altered.
c. Execute the procedure with employee ID 106 and job ID 'SY_ANAL' as parameters.
nd
a
g
EXECUTE add_job_hist(106, 'SY_ANAL')

Added employee 106 details to the JOB_HISTORY table
Updated current job of employee 106 to SY_ANAL
o
e
t
itu
t
s
n
ly
I
n
i
o
h
SELECT *
FROM
job_history
nd use
WHERE employee_id = 106;
a
G gy
d
n
lo
o
SELECT job_id, salary aFROM nemployees
y
WHERE employee_idrs=it106; ech
T
ve
i
n
COMMIT;
eU
l
c
Ora
in
r
e
ne
gi
n
E
d. Query the JOB_HISTORY and EMPLOYEES tables to view your changes for employee
106, and then commit the changes.
Commit complete.
e. Re-enable the triggers on the EMPLOYEES, JOBS, and JOB_HISTORY tables.

ALTER TABLE employees ENABLE ALL TRIGGERS;
ALTER TABLE jobs ENABLE ALL TRIGGERS;
ALTER TABLE job_history ENABLE ALL TRIGGERS;
Table altered.
Table altered.
Table altered.
3. In this exercise, create a program to update the minimum and maximum salaries for a job in
the JOBS table.
a. Create a stored procedure called UPD_JOBSAL to update the minimum and maximum
salaries for a specific job ID in the JOBS table. The procedure should provide three
parameters: the job ID, a new minimum salary, and a new maximum salary. Add
exception handling to account for an invalid job ID in the JOBS table. Raise an exception
if the maximum salary supplied is less than the minimum salary. Provide a message that
will be displayed if the row in the JOBS table is locked.
Hint: The resource locked/busy error number is 54.
CREATE OR REPLACE PROCEDURE upd_jobsal(
jobid
IN jobs.job_id%type,
new_minsal IN jobs.min_salary%type,
new_maxsal IN jobs.max_salary%type) IS
dummy
PLS_INTEGER;
e_resource_busy EXCEPTION;
sal_error
EXCEPTION;
PRAGMA
EXCEPTION_INIT (e_resource_busy , -54);
BEGIN
IF (new_maxsal < new_minsal) THEN
RAISE sal_error;
END IF;
SELECT 1 INTO dummy
FROM jobs
WHERE job_id = jobid
FOR UPDATE OF min_salary NOWAIT;
UPDATE jobs
SET min_salary = new_minsal,
max_salary = new_maxsal
WHERE job_id = jobid;
EXCEPTION
WHEN e_resource_busy THEN
RAISE_APPLICATION_ERROR (-20001,
'Job information is currently locked, try later.');
RAISE_APPLICATION_ERROR(-20001, 'This job ID does not exist');
WHEN sal_error THEN
RAISE_APPLICATION_ERROR(-20001,
'Data error: Max salary should be more than min salary');
END upd_jobsal;
/
SHOW ERRORS
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Procedure created.
No errors.
b. Execute the UPD_JOBSAL procedure by using a job ID of 'SY_ANAL', a minimum

salary of 7000, and a maximum salary of 140.
Note: This should generate an exception message.
EXECUTE upd_jobsal('SY_ANAL', 7000, 140)
BEGIN upd_jobsal('SY_ANAL', 7000, 140); END;
*
ERROR at line 1:
ORA-20001: Data error: Max salary should be more than min salary
ORA-06512: at "ORA1.UPD_JOBSAL", line 28
ORA-06512: at line 1
nd
a
g
c. Disable triggers on the EMPLOYEES and JOBS tables.

ALTER TABLE employees DISABLE ALL TRIGGERS;
ALTER TABLE jobs DISABLE ALL TRIGGERS;
in
r
e
ne
gi
n
E
f
o
e
ut
t
Table altered.
i
t
ns nly
I
i
h a jobeIDoof 'SY_ANAL', a minimum
d
d. Execute the UPD_JOBSAL procedurenusing
a of y14000.
us
salary of 7000, and a maximumG
salary
d olog
n
a
EXECUTE upd_jobsal('SY_ANAL',
ity echn7000, 14000)
s
r
T
PL/SQL procedure
completed.
ivesuccessfully
n
U
cle the JOBS table to view your changes, and then commit the changes.
e.raQuery
O
Table altered.
SELECT *
FROM jobs
f. Enable the triggers on the EMPLOYEES and JOBS tables.

ALTER TABLE employees ENABLE ALL TRIGGERS;
ALTER TABLE jobs ENABLE ALL TRIGGERS;
Table altered.
Table altered.
4. In this exercise, create a procedure to monitor whether employees have exceeded their
average salaries for their job type.
a. Disable the SECURE_EMPLOYEES trigger.
ALTER TRIGGER secure_employees DISABLE;
Trigger altered.
b. In the EMPLOYEES table, add an EXCEED_AVGSAL column for storing up to three

characters and a default value of NO. Use a check constraint to allow the values YES or
NO.
ALTER TABLE employees (
ADD (exceed_avgsal VARCHAR2(3) DEFAULT 'NO'
CONSTRAINT employees_exceed_avgsal_ck
CHECK (exceed_avgsal IN ('YES', 'NO')));
nd
a
g
in
r
e
ne
Table altered.
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
c. Write a stored procedure called CHECK_AVGSAL that checks whether each employees
salary exceeds the average salary for the JOB_ID. The average salary for a job is
calculated from information in the JOBS table. If the employees salary exceeds the
average for his or her job, then update his or her EXCEED_AVGSAL column in the
EMPLOYEES table to a value of YES; otherwise, set the value to NO. Use a cursor to
select the employees rows using the FOR UPDATE option in the query. Add exception
handling to account for a record being locked.
Hint: The resource locked/busy error number is 54. Write and use a local function
called GET_JOB_AVGSAL to determine the average salary for a job ID specified as a
parameter.
le
c
a
r
CREATE OR REPLACE PROCEDURE check_avgsal IS

avgsal_exceeded employees.exceed_avgsal%type;
CURSOR emp_csr IS
SELECT employee_id, job_id, salary
FROM employees
FOR UPDATE;
PRAGMA EXCEPTION_INIT(e_resource_busy, -54);

FUNCTION get_job_avgsal (jobid VARCHAR2) RETURN NUMBER IS
avg_sal employees.salary%type;
BEGIN
SELECT (max_salary + min_salary)/2 INTO avg_sal
FROM jobs
RETURN avg_sal;
END;
BEGIN
FOR emprec IN emp_csr
LOOP
avgsal_exceeded := 'NO';
IF emprec.salary >= get_job_avgsal(emprec.job_id) THEN
avgsal_exceeded := 'YES';
END IF;
UPDATE employees
SET exceed_avgsal = avgsal_exceeded
WHERE CURRENT OF emp_csr;
END LOOP;
EXCEPTION
ROLLBACK;
RAISE_APPLICATION_ERROR (-20001, 'Record is busy, try later.');
END check_avgsal;
/
SHOW ERRORS
nd
a
g
in
r
e
ne
gi
n
E
f
o
e
ut
t
i
t
ns nly
I
i
o
h
d
e
n
s
Ga gy u
d
n nolo
a
y
Procedure created. it
s Tech
r
e
v
No errors. Uni
le
c
a
Od.r Execute the CHECK_AVGSAL procedure. Then, to view the results of your modifications,
write a query to display the employees ID, job, the average salary for the job, the
employees salary, and the exceed_avgsal indicator column for employees whose
salaries exceed the average for their job, and finally commit the changes.
EXECUTE check_avgsal
SELECT e.employee_id, e.job_id, (j.max_salary-j.min_salary/2) job_avgsal,
e.salary, e.exceed_avgsal avg_exceeded
FROM
employees e, jobs j
WHERE e.job_id = j.job_id
and e.exceed_avgsal = 'YES';
COMMIT;
nd
a
g
in
r
e
ne
o
e
t
itu
t
Commit complete.
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
iv
n
eU
l
c
Ora
31 rows selected.
gi
n
E
5. Create a subprogram to retrieve the number of years of service for a specific employee.
a. Create a stored function called GET_YEARS_SERVICE to retrieve the total number of
years of service for a specific employee. The function should accept the employee ID as a
parameter and return the number of years of service. Add error handling to account for an
invalid employee ID.
CREATE OR REPLACE FUNCTION get_years_service(
emp_id IN employees.employee_id%TYPE) RETURN NUMBER IS
CURSOR jobh_csr IS
SELECT MONTHS_BETWEEN(end_date, start_date)/12) years_in_job
FROM
job_history
years_service NUMBER(2) := 0;
years_in_job NUMBER(2) := 0;
BEGIN
FOR jobh_rec IN jobh_csr
LOOP
EXIT WHEN jobh_csr%NOTFOUND;
years_service := years_service + job_rec.years_in_job;
END LOOP;
SELECT MONTHS_BETWEEN(SYSDATE, hire_date)/12 INTO years_in_job
FROM
employees
years_service := years_service + years_in_job;
RETURN ROUND(years_service);
EXCEPTION
'Employee with ID '|| emp_id ||' does not exist.');
END get_years_service;
/
SHOW ERRORS
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Function created.
No errors.
b. Invoke the GET_YEARS_SERVICE function in a call to DBMS_OUTPUT.PUT_LINE

for an employee with ID 999.
EXECUTE DBMS_OUTPUT.PUT_LINE(get_years_service (999))
BEGIN DBMS_OUTPUT.PUT_LINE(get_years_service (999)); END;
*
ERROR at line 1:
ORA-20348: Employee with ID 999 does not exist.
ORA-06512: at "ORA1.GET_YEARS_SERVICE", line 22
c. Display the number of years of service for employee 106 with

DBMS_OUTPUT.PUT_LINE
invoking the GET_YEARS_SERVICE function.
BEGIN
DBMS_OUTPUT.PUT_LINE (
'Employee 106 has worked ' || get_years_service(106) || ' years');
END;
/
Employee 106 has worked 6 years
d. Query the JOB_HISTORY and EMPLOYEES tables for the specified employee to verify
that the modifications are accurate.
Note: The values represented in the results on this page may differ from those you get
when you run these queries.
nd
a
g
gi
n
E
SELECT employee_id, job_id,

MONTHS_BETWEEN(end_date, start_date)/12 duration
FROM
job_history;
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
in
r
e
ne
11 rows selected.
SELECT job_id, MONTHS_BETWEEN(SYSDATE, hire_date)/12 duration
FROM
employees
WHERE employee_id = 106;
6. In this exercise, create a program to retrieve the number of different jobs that an employee
worked on during his or her service.
a. Create a stored function called GET_JOB_COUNT to retrieve the total number of
different jobs on which an employee worked.
The function should accept the employee ID in a parameter, and return the number of
different jobs that the employee worked on until now, including the present job. Add
exception handling to account for an invalid employee ID.
Hint: Use the distinct job IDs from the JOB_HISTORY table, and exclude the current
job ID, if it is one of the job IDs on which the employee has already worked. Write a
UNION of two queries and count the rows retrieved into a PL/SQL table. Use a FETCH
with BULK COLLECT INTO to obtain the unique jobs for the employee.
CREATE OR REPLACE FUNCTION get_job_count(
TYPE jobs_tabtype IS TABLE OF jobs.job_id%type;
jobtab jobs_tabtype;
CURSOR empjob_csr IS
SELECT job_id
FROM job_history
WHERE employee_id = emp_id
UNION
SELECT job_id
FROM employees
BEGIN
OPEN empjob_csr;
FETCH empjob_csr BULK COLLECT INTO jobtab;
CLOSE empjob_csr;
RETURN jobtab.count;
EXCEPTION
'Employee with ID '|| emp_id ||' does not exist!');
END get_job_count;
/
SHOW ERRORS
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
in
r
e
ne
nd
a
g
gi
n
E
Function created.
No errors.
b. Invoke the function for an employee with ID 176.

BEGIN
DBMS_OUTPUT.PUT_LINE('Employee 176 worked on ' ||
get_job_count(176) || ' different jobs.');
END;
/
Employee 176 worked on 2 different jobs.
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
7. Create a package called EMPJOB_PKG that contains your NEW_JOB, ADD_JOB_HIST, and
UPD_JOBSAL procedures, as well as your GET_YEARS_SERVICE and GET_JOB_COUNT
functions.
a. Create the package specification with all the subprogram constructs public. Move any
subprogram local-defined types into the package specification.
CREATE OR REPLACE PACKAGE empjob_pkg IS
TYPE jobs_tabtype IS TABLE OF jobs.job_id%type;
PROCEDURE add_job_hist(
emp_id IN employees.employee_id%TYPE,
new_jobid IN jobs.job_id%TYPE);
FUNCTION get_job_count(
emp_id IN employees.employee_id%TYPE) RETURN NUMBER;
FUNCTION get_years_service(
emp_id IN employees.employee_id%TYPE) RETURN NUMBER;
PROCEDURE new_job(
minsal IN jobs.min_salary%TYPE);
PROCEDURE upd_jobsal(
jobid IN jobs.job_id%type,
new_maxsal IN jobs.max_salary%type);
END empjob_pkg;
/
SHOW ERRORS
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
iv
Package created.
n
eU
l
c
No errors.
Ora
in
r
e
ne
nd
a
g
gi
n
E
b. Create the package body with the subprogram implementation; remember to remove
(from the subprogram implementations) any types that you moved into the package
specification.
CREATE OR REPLACE PACKAGE BODY empjob_pkg IS
PROCEDURE add_job_hist(
emp_id IN employees.employee_id%TYPE,
new_jobid IN jobs.job_id%TYPE) IS
BEGIN
INSERT INTO job_history
SELECT employee_id, hire_date, SYSDATE, job_id, department_id
FROM employees
UPDATE employees
SET hire_date = SYSDATE,
job_id = new_jobid,
salary = (SELECT min_salary + 500
FROM jobs
WHERE job_id = new_jobid)
DBMS_OUTPUT.PUT_LINE ('Added employee ' || emp_id ||
' details to the JOB_HISTORY table');
DBMS_OUTPUT.PUT_LINE ('Updated current job of employee ' ||
emp_id|| ' to '|| new_jobid);
EXCEPTION
RAISE_APPLICATION_ERROR (-20001, 'Employee does not exist!');
END add_job_hist;
nd
a
g
in
r
e
ne
gi
n
E
f
o
e
ut
t
i
t
ns nly
I
i
o
h
d
e
n
s
Ga gy u
d
n nolo
a
y
sit Tech
r
e
FUNCTION get_job_count(
iv
emp_id U
INn employees.employee_id%TYPE) RETURN NUMBER
le jobs_tabtype;
jobtab
c
a
empjob_csr IS
OrCURSOR
SELECT job_id
IS
FROM job_history
WHERE employee_id = emp_id
UNION
SELECT job_id
FROM employees
BEGIN
OPEN empjob_csr;
FETCH empjob_csr BULK COLLECT INTO jobtab;
CLOSE empjob_csr;
RETURN jobtab.count;
EXCEPTION
'Employee with ID '|| emp_id ||' does not exist!');
END get_job_count;

FUNCTION get_years_service(
CURSOR jobh_csr IS
SELECT MONTHS_BETWEEN(end_date, start_date)/12 years_in_job
FROM job_history
years_service NUMBER(2) := 0;
years_in_job NUMBER(2) := 0;
BEGIN
FOR jobh_rec IN jobh_csr
LOOP
EXIT WHEN jobh_csr%NOTFOUND;
years_service := years_service + jobh_rec.years_in_job;
END LOOP;
SELECT MONTHS_BETWEEN(SYSDATE, hire_date)/12 INTO years_in_job
FROM employees
years_service := years_service + years_in_job;
RETURN ROUND(years_service);
EXCEPTION
'Employee with ID '|| emp_id ||' does not exist.');
END get_years_service;
nd
a
g
in
r
e
ne
f
o
e
ut
t
i
t
ns nly
I
i
o
h
d
e
PROCEDURE new_job(
n
s
Ga gy u
d
n nolo
a
y
minsal IN jobs.min_salary%TYPE)
sit Tech := 2IS* minsal;
r
maxsal jobs.max_salary%TYPE
e
iv
n
BEGIN
INSERT
e UINTO jobs(job_id, job_title, min_salary,
l
c
a
(jobid, title, minsal, maxsal);
OrVALUES
gi
n
E
max_salary)
DBMS_OUTPUT.PUT_LINE ('New row added to JOBS table:');

DBMS_OUTPUT.PUT_LINE (jobid || ' ' || title ||' '||
minsal || ' ' || maxsal);
END new_job;

PROCEDURE upd_jobsal(
jobid IN jobs.job_id%type,
new_maxsal IN jobs.max_salary%type) IS
dummy PLS_INTEGER;
sal_error EXCEPTION;
PRAGMA EXCEPTION_INIT (e_resource_busy , -54);
BEGIN
IF (new_maxsal < new_minsal) THEN
RAISE sal_error;
END IF;
SELECT 1 INTO dummy
FROM jobs
WHERE job_id = jobid
FOR UPDATE OF min_salary NOWAIT;
UPDATE jobs
SET min_salary = new_minsal,
max_salary = new_maxsal
EXCEPTION
RAISE_APPLICATION_ERROR (-20001,
'Job information is currently locked, try later.');
RAISE_APPLICATION_ERROR(-20001, 'This job ID does not exist');
WHEN sal_error THEN
'Data error: Max salary should be more than min salary');
END upd_jobsal;
END empjob_pkg;
/
SHOW ERRORS
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Package body created.

No errors.
c. Invoke your EMPJOB_PKG.NEW_JOB procedure to create a new job with ID

PR_MAN, job title Public Relations Manager, and salary 6250.
EXECUTE empjob_pkg.new_job('PR_MAN', 'Public Relations Manager', 6250)
New row added to JOBS table:
PR_MAN Public Relations Manager 6250 12500

d. Invoke your EMPJOB_PKG.ADD_JOB_HIST procedure to modify the job of employee
ID 110 to job ID PR_MAN.
Note: You need to disable the UPDATE_JOB_HISTORY trigger before you execute the
ADD_JOB_HIST procedure, and re-enable the trigger after you have executed the
procedure.
ALTER TRIGGER update_job_history DISABLE;
EXECUTE empjob_pkg.add_job_hist(110, 'PR_MAN')
ALTER TRIGGER update_job_history ENABLE;
Trigger altered.
Added employee 110 details to the JOB_HISTORY table
Updated current job of employee 110 to PR_MAN
nd
a
g
in
r
e
ne
Trigger altered.
gi
n
E
e. Query the JOBS, JOB_HISTORY, and EMPLOYEES tables to verify the results.
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
SELECT * FROM jobs WHERE job_id = 'PR_MAN';

SELECT * FROM job_history WHERE employee_id = 110;
SELECT job_id, salary FROM employees WHERE employee_id = 110;
le
c
a
r
8. In this exercise, create a trigger to ensure that the minimum and maximum salaries of a job
are never modified such that the salary of an existing employee with that job ID is outside the
new range specified for the job.
a. Create a trigger called CHECK_SAL_RANGE that is fired before every row that is
updated in the MIN_SALARY and MAX_SALARY columns in the JOBS table. For any
minimum or maximum salary value that is changed, check whether the salary of any
existing employee with that job ID in the EMPLOYEES table falls within the new range of
salaries specified for this job ID. Include exception handling to cover a salary range
change that affects the record of any existing employee.
CREATE OR REPLACE TRIGGER check_sal_range
BEFORE UPDATE OF min_salary, max_salary ON jobs
FOR EACH ROW
DECLARE
minsal employees.salary%TYPE;
maxsal employees.salary%TYPE;
e_invalid_salrange EXCEPTION;
BEGIN
SELECT MIN(salary), MAX(salary) INTO minsal, maxsal
FROM employees
WHERE job_id = :NEW.job_id;
IF (minsal < :NEW.min_salary) OR (maxsal > :NEW.max_salary) THEN
RAISE e_invalid_salrange;
END IF;
EXCEPTION
WHEN e_invalid_salrange THEN
'Employees exist whose salary is out of the specified range. '||
'Therefore the specified salary range cannot be updated.');
END check_sal_range;
/
SHOW ERRORS
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Trigger created.
No errors.

b. Test the trigger using the SY_ANAL job, setting the new minimum salary to 5000 and
the new maximum salary to 7000. Before you make the change, write a query to display
the current salary range for the SY_ANAL job ID, and another query to display the
employee ID, last name, and salary for the same job ID. After the update, query the
change (if any) to the JOBS table for the specified job ID.
SELECT * FROM jobs
SELECT employee_id, last_name, salary

FROM employees
nd
a
g
in
r
e
ne
o
e
t
itu
t
s
In only
i
h
nd use
1 row updated.
a
G gy
d
n
lo
a
SELECT * FROM jobs
o
n
y
sit Tech
r
e
iv
n
eU
l
c
Ora
UPDATE jobs
SET min_salary = 5000, max_salary = 7000
gi
n
E
c. Using the job SY_ANAL, set the new minimum salary to 7000 and the new maximum salary
to 18000. Explain the results.
UPDATE jobs
SET min_salary = 7000, max_salary = 18000
UPDATE jobs
*
ERROR at line 1:
ORA-20550: Employees exist whose salary is out of the specified range.
Therefore the specified salary range cannot be updated.
ORA-06512: at "ORA1.CHECK_SAL_RANGE", line 14
ORA-04088: error during execution of trigger 'ORA1.CHECK_SAL_RANGE'

The update fails to change the salary range due to the functionality provided by the
CHECK_SAL_RANGE trigger because the employee 106 who has the SY_ANAL job
ID has a salary of 6500, which is less than the minimum salary for the new salary
range specified in the UPDATE statement.
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B: Entity Relationship Diagram
TITLE
for
RESERVATION
#* reservation date
the subject
of
set up
#* ID
* title
* description
o rating
o category
o release date
available as
nd
a
g
in
r
e
ne
gi
n
E
TITLE_COP
Y
#* ID
f
o
e
ut
t
i
t
ns nly the subject
I
i
o
h
d
e
n
s
f
a yu
responsible
G
g
d
for
n nolo
a
y
sit Tech
made against
MEMBERver
i
n
#* ID U
*le
last name
c
RENTAL
a
responsible
r
O o first name
#* book date
for
o address
o city
o phone
* join date
created
for
o act ret date

o exp ret date
Part B (continued)
In this case study, create a package named VIDEO_PKG that contains procedures and functions
for a video store application. This application enables customers to become a member of the
video store. Any member can rent movies, return rented movies, and reserve movies.
Additionally, create a trigger to ensure that any data in the video tables is modified only during
business hours.
Create the package by using iSQL*Plus and use the DBMS_OUTPUT Oracle-supplied package to
display messages.
The video store database contains the following tables: TITLE, TITLE_COPY, RENTAL,
RESERVATION, and MEMBER. The entity relationship diagram is shown on the previous page.
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B: Additional Practice 1 Solutions
1. Load and execute the E:\labs\PLPU\labs\buildvid1.sql script to create all the

required tables and sequences that are needed for this exercise.
SET ECHO OFF
/* Script to build the Video Application (Part 1 - buildvid1.sql)
for the Oracle Introduction to Oracle with Procedure Builder course.
Created by: Debby Kramer Creation date: 12/10/95
Last upated: 2/13/96
Modified by Nagavalli Pataballa on 26-APR-2001
For the course Introduction to Oracle9i: PL/SQL
This part of the script creates tables and sequences that are used
by Part B of the Additional Practices of the course.
Ignore the errors which appear due to dropping of table.
*/
DROP
DROP
DROP
DROP
DROP
TABLE
TABLE
TABLE
TABLE
TABLE
rental CASCADE CONSTRAINTS;

reservation CASCADE CONSTRAINTS;
title_copy CASCADE CONSTRAINTS;
title CASCADE CONSTRAINTS;
member CASCADE CONSTRAINTS;
nd
a
g
in
r
e
ne
gi
n
E
f
o
e
ut
t
i
t
CREATE TABLE MEMBER
ns nly
I
i
omember_id_pk
(member_id NUMBER (10)
CONSTRAINT
h
d
e
n
s
, last_name VARCHAR2(25)
a yu
GNOT
CONSTRAINT member_last_nn
g
d
n noloNULL
, first_name VARCHAR2(25)
a
ity ech
, address
VARCHAR2(100)
s
r
T
, city
VARCHAR2(30)
ve
i
n
, phone U VARCHAR2(25)
le DATE DEFAULT SYSDATE
, join_date
c
a
OrCONSTRAINT join_date_nn NOT NULL)
/
PROMPT Please wait while tables are created....
PRIMARY KEY
CREATE TABLE TITLE

(title_id
NUMBER(10)
CONSTRAINT title_id_pk PRIMARY KEY
, title
VARCHAR2(60)
CONSTRAINT title_nn NOT NULL
, description VARCHAR2(400)
CONSTRAINT title_desc_nn NOT NULL
, rating
VARCHAR2(4)
CONSTRAINT title_rating_ck CHECK (rating IN
('G','PG','R','NC17','NR'))
, category
VARCHAR2(20) DEFAULT 'DRAMA'
CONSTRAINT title_categ_ck CHECK (category IN
('DRAMA','COMEDY','ACTION', 'CHILD','SCIFI','DOCUMENTARY'))
, release_date DATE)
/
Part B: Additional Practice 1 Solutions (continued)

CREATE TABLE TITLE_COPY
(copy_id
NUMBER(10)
, title_id NUMBER(10)
CONSTRAINT copy_title_id_fk
REFERENCES title(title_id)
, status
VARCHAR2(15)
CONSTRAINT copy_status_nn NOT NULL
CONSTRAINT copy_status_ck CHECK (status IN ('AVAILABLE',
'DESTROYED',
'RENTED', 'RESERVED'))
, CONSTRAINT copy_title_id_pk PRIMARY KEY(copy_id, title_id))
/
CREATE TABLE RENTAL
(book_date DATE DEFAULT SYSDATE
, copy_id
NUMBER(10)
, member_id NUMBER(10)
CONSTRAINT rental_mbr_id_fk REFERENCES member(member_id)
, act_ret_date DATE
, exp_ret_date DATE DEFAULT SYSDATE+2
, CONSTRAINT rental_copy_title_id_fk FOREIGN KEY (copy_id, title_id)
REFERENCES title_copy(copy_id,title_id)
, CONSTRAINT rental_id_pk PRIMARY KEY(book_date, copy_id, title_id,
member_id))
/
CREATE TABLE RESERVATION
(res_date DATE
, member_id NUMBER(10)
, CONSTRAINT res_id_pk PRIMARY KEY(res_date, member_id, title_id))
/
nd
a
g
in
r
e
ne
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
eU
l
c
PROMPT
ra Tables
O
DROP SEQUENCE
created.
title_id_seq;
DROP SEQUENCE member_id_seq;
PROMPT Creating Sequences...
CREATE SEQUENCE member_id_seq
START WITH 101
NOCACHE
CREATE SEQUENCE title_id_seq
START WITH 92
NOCACHE
/
PROMPT Sequences created.
PROMPT Run buildvid2.sql now to populate the above tables.
2. Load and execute the E:\labs\PLPU\labs\buildvid2.sql script to populate all the

tables created by the buildvid1.sql script.
/* Script to build the Video Application (Part 2 - buildvid2.sql)
This part of the script populates the tables that are created using
buildvid1.sql
These are used by Part B of the Additional Practices of the course.
You should run the script buildvid1.sql before running this script to
create the above tables.
*/
INSERT INTO member
VALUES (member_id_seq.NEXTVAL, 'Velasquez', 'Carmen',
'283 King Street', 'Seattle', '587-99-6666', '03-MAR-90');
INSERT INTO member
VALUES
(member_id_seq.NEXTVAL, 'Ngao', 'LaDoris',
'5 Modrany', 'Bratislava', '586-355-8882', '08-MAR-90');
INSERT INTO member
VALUES (member_id_seq.NEXTVAL,'Nagayama', 'Midori',
'68 Via Centrale', 'Sao Paolo', '254-852-5764', '17-JUN-91');
INSERT INTO member
VALUES (member_id_seq.NEXTVAL,'Quick-To-See','Mark',
'6921 King Way', 'Lagos', '63-559-777', '07-APR-90');
INSERT INTO member
VALUES (member_id_seq.NEXTVAL, 'Ropeburn', 'Audry',
'86 Chu Street', 'Hong Kong', '41-559-87', '04-MAR-90');
INSERT INTO member
VALUES (member_id_seq.NEXTVAL, 'Urguhart', 'Molly',
'3035 Laurier Blvd.', 'Quebec', '418-542-9988','18-JAN-91');
INSERT INTO member
VALUES (member_id_seq.NEXTVAL, 'Menchu', 'Roberta',
'Boulevard de Waterloo 41', 'Brussels', '322-504-2228', '14-MAY-90');
INSERT INTO member
VALUES (member_id_seq.NEXTVAL, 'Biri', 'Ben',
'398 High St.', 'Columbus', '614-455-9863', '07-APR-90');
INSERT INTO member
VALUES (member_id_seq.NEXTVAL, 'Catchpole', 'Antoinette',
'88 Alfred St.', 'Brisbane', '616-399-1411', '09-FEB-92');
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
COMMIT;

INSERT INTO TITLE (title_id, title, description, rating, category,
release_date)
VALUES (TITLE_ID_SEQ.NEXTVAL, 'Willie and Christmas Too',
'All of Willie''s friends made a Christmas list for Santa, but Willie
has yet to create his own wish list.', 'G', 'CHILD', '05-OCT-95');
release_date)
VALUES (TITLE_ID_SEQ.NEXTVAL, 'Alien Again', 'Another installment of
science fiction history. Can the heroine save the planet from the alien
life form?', 'R', 'SCIFI',
'19-MAY-95');
release_date)
VALUES (TITLE_ID_SEQ.NEXTVAL, 'The Glob', 'A meteor crashes near a
small American town and unleashes carivorous goo in this classic.', 'NR',
'SCIFI', '12-AUG-95');
release_date)
VALUES (TITLE_ID_SEQ.NEXTVAL, 'My Day Off', 'With a little luck and a
lot of ingenuity, a teenager skips school for a day in New York.', 'PG',
'COMEDY', '12-JUL-95');
release_date)
VALUES (TITLE_ID_SEQ.NEXTVAL, 'Miracles on Ice', 'A six-year-old has
doubts about Santa Claus. But she discovers that miracles really do
exist.', 'PG', 'DRAMA', '12-SEP-95');
release_date)
VALUES (TITLE_ID_SEQ.NEXTVAL, 'Soda Gang', 'After discovering a cached
of drugs, a young couple find themselves pitted against a vicious gang.',
'NR', 'ACTION', '01-JUN-95');
INSERT INTO title (title_id, title, description, rating, category,
release_date)
VALUES (TITLE_ID_SEQ.NEXTVAL, 'Interstellar Wars', 'Futuristic
interstellar action movie. Can the rebels save the humans from the evil
Empire?', 'PG', 'SCIFI','07-JUL-77');
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
COMMIT;
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
title_copy
title_copy
title_copy
title_copy
title_copy
title_copy
title_copy
title_copy
title_copy
title_copy
title_copy
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(1,92,
(1,93,
(2,93,
(1,94,
(1,95,
(2,95,
(3,95,
(1,96,
(1,97,
(1,98,
(2,98,
'AVAILABLE');
'AVAILABLE');
'RENTED');
'AVAILABLE');
'AVAILABLE');
'AVAILABLE');
'RENTED');
'AVAILABLE');
'AVAILABLE');
'RENTED');
'AVAILABLE');
COMMIT;

INSERT INTO reservation VALUES (sysdate-1, 101, 93);
INSERT INTO reservation VALUES (sysdate-2, 106, 102);
COMMIT;
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
rental
rental
rental
rental
rental
VALUES
VALUES
VALUES
VALUES
VALUES
(sysdate-1,
(sysdate-2,
(sysdate-3,
(sysdate-4,
(sysdate-3,
2,
3,
1,
1,
1,
101,
102,
101,
106,
101,
93,
95,
98,
97,
92,
null, sysdate+1);
null, sysdate);
null, sysdate-1);
sysdate-2, sysdate-2);
sysdate-2, sysdate-1);
COMMIT;
PROMPT ** Tables built and data loaded **
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
3. Create a package named VIDEO_PKG with the following procedures and functions:
a. NEW_MEMBER: A public procedure that adds a new member to the MEMBER table. For
the member ID number, use the sequence MEMBER_ID_SEQ. For the join date, use
SYSDATE. Pass all other values to be inserted into a new row as parameters.
b. NEW_RENTAL: An overloaded public function to record a new rental. Pass the title ID
number for the video that a customer wants to rent, and either the customers last name or
his or her member ID number into the function. The function should return the due date
for the video. Due dates are three days from the date the video is rented. If the status for a
movie requested is listed as AVAILABLE in the TITLE_COPY table for one copy of this
title, then update this TITLE_COPY table and set the status to RENTED. If there is no
copy available, the function must return NULL. Then, insert a new record into the
RENTAL table identifying the booked date as todays date, the copy ID number, the
member ID number, the title ID number, and the expected return date. Be aware of
multiple customers with the same last name. In this case, have the function return NULL,
and display a list of the customers names that match and their ID numbers.
nd
a
g
in
r
e
ne
gi
n
E
f
o
e
ut
t
i
t
ns nly
I
i
o
h
d
e
n
s
Ga gy u
d
n nolo
a
y
h
c
sitA private
r
e
d. RESERVE_MOVIE:
procedure
that executes only if all the video copies
e
T
ivthe NEW_RENTAL
n
requested
in
procedure
have a status of RENTED. Pass the member ID
U
e
number
cl and the title ID number to this procedure. Insert a new record into the
a
r
O RESERVATION table and record the reservation date, member ID number, and title ID
c. RETURN_MOVIE: A public procedure that updates the status of a video (available,
rented, or damaged) and sets the return date. Pass the title ID, the copy ID, and the status
to this procedure. Check whether there are reservations for that title, and display a
message, if it is reserved. Update the RENTAL table and set the actual return date to
todays date. Update the status in the TITLE_COPY table based on the status parameter
passed into the procedure.
number. Print a message indicating that a movie is reserved and its expected date of
return.
e. EXCEPTION_HANDLER: A private procedure that is called from the exception handler

of the public programs. Pass the SQLCODE number to this procedure, and the name of the
program (as a text string) where the error occurred. Use RAISE_APPLICATION_ERROR
to raise a customized error. Start with a unique key violation (-1) and foreign key
violation (-2292). Allow the exception handler to raise a generic error for any other
errors.
VIDEO_PKG Package Specification

CREATE OR REPLACE PACKAGE video_pkg IS
PROCEDURE new_member
(lname
IN member.last_name%TYPE,
fname
IN member.first_name%TYPE
address
IN member.address%TYPE
city
IN member.city%TYPE
phone
IN member.phone%TYPE
DEFAULT
DEFAULT
DEFAULT
DEFAULT
NULL,
NULL,
NULL,
NULL);
FUNCTION new_rental
(memberid
IN rental.member_id%TYPE,
titleid
IN rental.title_id%TYPE)
RETURN DATE;
nd
a
g
FUNCTION new_rental
(membername IN member.last_name%TYPE,
titleid
IN rental.title_id%TYPE)
RETURN DATE;
PROCEDURE return_movie
(titleid
IN rental.title_id%TYPE,
copyid
IN rental.copy_id%TYPE,
sts
IN title_copy.status%TYPE);
END video_pkg;
/
SHOW ERRORS
in
r
e
ne
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
h
Package created. sit
c
r
e
e
T
iv
No errors. Un
le
c
a
VIDEO_PKG
Package Body
Or
gi
n
E
CREATE OR REPLACE PACKAGE BODY video_pkg IS

PROCEDURE exception_handler(errcode IN NUMBER, context IN VARCHAR2) IS
BEGIN
IF errcode = -1 THEN
'The number is assigned to this member is already in use, '||
'try again.');
ELSIF errcode = -2291 THEN
RAISE_APPLICATION_ERROR(-20002, context ||
' has attempted to use a foreign key value that is invalid');
ELSE
RAISE_APPLICATION_ERROR(-20999, 'Unhandled error in ' ||
context || '. Please contact your application '||
'administrator with the following information: '
|| CHR(13) || SQLERRM);
END IF;
END exception_handler;

PROCEDURE reserve_movie
(memberid IN reservation.member_id%TYPE,
titleid
IN reservation.title_id%TYPE) IS
CURSOR rented_csr IS
SELECT exp_ret_date
FROM rental
WHERE title_id = titleid
AND act_ret_date IS NULL;
BEGIN
INSERT INTO reservation (res_date, member_id, title_id)
VALUES (SYSDATE, memberid, titleid);
COMMIT;
FOR rented_rec IN rented_csr LOOP
DBMS_OUTPUT.PUT_LINE('Movie reserved. Expected back on: '
|| rented_rec.exp_ret_date);
EXIT WHEN rented_csr%found;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
exception_handler(SQLCODE, 'RESERVE_MOVIE');
END reserve_movie;
in
r
e
ne
nd
a
g
o
e
t
PROCEDURE return_movie(
itu
t
titleid IN rental.title_id%TYPE,
s
n
nly
copyid IN rental.copy_id%TYPE, hi I
o
sts IN title_copy.status%TYPE)
ndIS use
a
v_dummy VARCHAR2(1);
d G ology
CURSOR res_csr IS an
SELECT *
ity echn
s
r
FROM reservation
e
T
niv
WHEREUtitle_id
= titleid;
BEGIN le
ac '' INTO v_dummy
OrSELECT
gi
n
E
FROM title
WHERE title_id = titleid;
UPDATE rental
SET act_ret_date = SYSDATE
AND copy_id = copyid AND act_ret_date IS NULL;
UPDATE title_copy
SET status = UPPER(sts)
WHERE title_id = titleid AND copy_id = copyid;
FOR res_rec IN res_csr LOOP
IF res_csr%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Put this movie on hold -- '||
'reserved by member #' || res_rec.member_id);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
exception_handler(SQLCODE, 'RETURN_MOVIE');
END return_movie;

FUNCTION new_rental(
memberid IN rental.member_id%TYPE,
titleid
IN rental.title_id%TYPE) RETURN DATE IS
CURSOR copy_csr IS
SELECT * FROM title_copy
FOR UPDATE;
flag
BOOLEAN := FALSE;
BEGIN
FOR copy_rec IN copy_csr LOOP
IF copy_rec.status = 'AVAILABLE' THEN
UPDATE title_copy
SET status = 'RENTED'
WHERE CURRENT OF copy_csr;
INSERT INTO rental(book_date, copy_id, member_id,
title_id, exp_ret_date)
VALUES (SYSDATE, copy_rec.copy_id, memberid,
titleid, SYSDATE + 3);
flag := TRUE;
EXIT;
END IF;
END LOOP;
COMMIT;
IF flag THEN
RETURN (SYSDATE + 3);
ELSE
reserve_movie(memberid, titleid);
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
exception_handler(SQLCODE, 'NEW_RENTAL');
END new_rental;
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
in
r
e
ne
nd
a
g
gi
n
E
FUNCTION new_rental(
membername IN member.last_name%TYPE,
titleid
IN rental.title_id%TYPE) RETURN DATE IS
CURSOR copy_csr IS
SELECT * FROM title_copy
FOR UPDATE;
flag BOOLEAN := FALSE;
memberid member.member_id%TYPE;
CURSOR member_csr IS
SELECT member_id, last_name, first_name
FROM member
WHERE LOWER(last_name) = LOWER(membername)
ORDER BY last_name, first_name;

BEGIN
SELECT member_id INTO memberid
FROM member
WHERE lower(last_name) = lower(membername);
FOR copy_rec IN copy_csr LOOP
IF copy_rec.status = 'AVAILABLE' THEN
UPDATE title_copy
SET status = 'RENTED'
WHERE CURRENT OF copy_csr;
INSERT INTO rental (book_date, copy_id, member_id,
title_id, exp_ret_date)
VALUES (SYSDATE, copy_rec.copy_id, memberid,
titleid, SYSDATE + 3);
flag := TRUE;
EXIT;
END IF;
END LOOP;
COMMIT;
IF flag THEN
RETURN(SYSDATE + 3);
ELSE
reserve_movie(memberid, titleid);
RETURN NULL;
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(
'Warning! More than one member by this name.');
FOR member_rec IN member_csr LOOP
DBMS_OUTPUT.PUT_LINE(member_rec.member_id || CHR(9) ||
member_rec.last_name || ', ' || member_rec.first_name);
END LOOP;
RETURN NULL;
WHEN OTHERS THEN
exception_handler(SQLCODE, 'NEW_RENTAL');
END new_rental;
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
PROCEDURE new_member(
lname
IN member.last_name%TYPE,
fname
IN member.first_name%TYPE
DEFAULT NULL,
address
IN member.address%TYPE
DEFAULT NULL,
city
IN member.city%TYPE
DEFAULT NULL,
phone
IN member.phone%TYPE
DEFAULT NULL) IS
BEGIN
INSERT INTO member(member_id, last_name, first_name,
address, city, phone, join_date)
VALUES(member_id_seq.NEXTVAL, lname, fname,
address, city, phone, SYSDATE);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
exception_handler(SQLCODE, 'NEW_MEMBER');
END new_member;
END video_pkg;
/
SHOW ERRORS
Package body created.
No errors.
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
4. Use the following scripts located in the E:\labs\PLPU\soln directory to test your
routines:
a. Add two members using sol_apb_04_a.sql.
SET SERVEROUTPUT ON
EXECUTE video_pkg.new_member('Haas', 'James', 'Chestnut Street',
'Boston', '617-123-4567')
EXECUTE video_pkg.new_member('Biri', 'Allan', 'Hiawatha Drive', 'New
York', '516-123-4567')
nd
a
g
b. Add new video rentals using sol_apb_04_b.sql.
in
r
e
ne
EXEC DBMS_OUTPUT.PUT_LINE(video_pkg.new_rental(110, 98))

26-FEB-04
gi
n
E
f
o
e
ut
t
EXEC DBMS_OUTPUT.PUT_LINE(video_pkg.new_rental(109,
93))
i
t
s
y
In onl
i
h
26-FEB-04
nd use
a
G gy
d
n
lo
a
o
EXEC DBMS_OUTPUT.PUT_LINE(video_pkg.new_rental(107,
98))
n
y
t
h
i
c
rs Tback
e on: 21-FEB-04
eExpected
Movie reserved.
v
i
n successfully completed.
PL/SQL procedure
U
e
cl
a
r
EXEC
O DBMS_OUTPUT.PUT_LINE(video_pkg.new_rental('Biri', 97))
Warning! More than one member by this name.
112 Biri, Allan
108 Biri, Ben
EXEC DBMS_OUTPUT.PUT_LINE(video_pkg.new_rental(97, 97))
BEGIN DBMS_OUTPUT.PUT_LINE(video_pkg.new_rental(97, 97)); END;
*
ERROR at line 1:
ORA-20002: NEW_RENTAL has attempted to use a foreign key value that is
invalid
ORA-06512: at "ORA1.VIDEO_PKG", line 9

c. Return movies using the sol_apb_04_c.sql script.
EXECUTE video_pkg.return_movie(98, 1, 'AVAILABLE')

Put this movie on hold -- reserved by member #107
EXECUTE video_pkg.return_movie(95, 3, 'AVAILABLE')
EXECUTE video_pkg.return_movie(111, 1, 'RENTED')
BEGIN video_pkg.return_movie(111, 1, 'RENTED'); END;
nd
a
g
in
r
e
ne
ERROR at line 1:
ORA-20999: Unhandled error in RETURN_MOVIE. Please contact your
application administrator with the following information: ORA-01403: no
data found
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
5. The business hours for the video store are 8:00 a.m. to 10:00 p.m., Sunday through Friday,
and 8:00 a.m. to 12:00 a.m. on Saturday. To ensure that the tables can be modified only
during these hours, create a stored procedure that is called by triggers on the tables.
a. Create a stored procedure called TIME_CHECK that checks the current time against
business hours. If the current time is not within business hours, use the
RAISE_APPLICATION_ERROR procedure to give an appropriate message.
CREATE OR REPLACE PROCEDURE time_check IS
BEGIN
IF ((TO_CHAR(SYSDATE,'D') BETWEEN 1 AND 6) AND
(TO_DATE(TO_CHAR(SYSDATE, 'hh24:mi'), 'hh24:mi') NOT BETWEEN
TO_DATE('08:00', 'hh24:mi') AND TO_DATE('22:00', 'hh24:mi')))
OR ((TO_CHAR(SYSDATE, 'D') = 7)
AND (TO_DATE(TO_CHAR(SYSDATE, 'hh24:mi'), 'hh24:mi') NOT BETWEEN
TO_DATE('08:00', 'hh24:mi') AND TO_DATE('24:00', 'hh24:mi'))) THEN
'Data changes restricted to office hours.');
END IF;
END time_check;
/
SHOW ERRORS
nd
a
g
in
r
e
ne
gi
n
E
f
o
e
ut
t
i
t
ns nly
I
Procedure created.
i
o
h
d
e
n
s
No errors.
Ga gy u
d
n nolo
a
y
b. Create a trigger on each
thecfive
h tables. Fire the trigger before data is inserted, updated,
it of e
s
r
e
and deleted ifrom
T Call your TIME_CHECK procedure from each of these
v the tables.
n
triggers.
eU
l
c
CREATE
ra OR REPLACE TRIGGER member_trig
O
BEFORE INSERT OR UPDATE OR DELETE ON member
CALL time_check
/
CREATE OR REPLACE TRIGGER rental_trig
BEFORE INSERT OR UPDATE OR DELETE ON rental
CALL time_check
/
CREATE OR REPLACE TRIGGER title_copy_trig
BEFORE INSERT OR UPDATE OR DELETE ON title_copy
CALL time_check
/
CREATE OR REPLACE TRIGGER title_trig
BEFORE INSERT OR UPDATE OR DELETE ON title
CALL time_check
/
CREATE OR REPLACE TRIGGER reservation_trig

BEFORE INSERT OR UPDATE OR DELETE ON reservation
CALL time_check
/
Trigger created.
Trigger created.
Trigger created.
Trigger created.
Trigger created.
c. Test your triggers.
nd
a
g
in
r
e
ne
Note: In order for your trigger to fail, you may need to change the time to be outside the
range of your current time in class. For example, while testing, you may want valid video
hours in your trigger to be from 6:00 p.m. to 8:00 a.m.
gi
n
E
f
o
e
ut
t
i
t
ns nly
I
i
o
h
d
e
n
s
Ga gy u
d
n nolo
a
y
sit Tech
r
e
ivtime zone usinge [+|-]HH:MI format such
n
-- Change your
-- time lreturns
e U a time between 6pm and 8am
c
ALTER
OraSESSION SET TIME_ZONE='-07:00';
-- First determine current timezone and time

SELECT SESSIONTIMEZONE,
TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH24:MI') CURR_DATE
FROM DUAL;
that the current
Session altered.
SELECT SESSIONTIMEZONE,
TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH24:MI') CURR_DATE
FROM DUAL;
-- Add a new member (for a sample test)

EXECUTE video_pkg.new_member('Elias', 'Elliane',
'California', '789-123-4567')
BEGIN video_pkg.new_member('Elias', 'Elliane',
'California', '789-123-4567'); END;
'Vine Street',
'Vine Street',
*
ERROR at line 1:
ORA-20999: Unhandled error in NEW_MEMBER. Please contact your application
administrator with the following information: ORA-20999: Data changes
restricted to office hours.
ORA-06512: at "ORA1.TIME_CHECK", line 9
ORA-06512: at "ORA1.MEMBER_TRIG", line 1
ORA-04088: error during execution of trigger 'ORA1.MEMBER_TRIG'
nd
a
g
in
r
e
ne
-- Restore the original time zone for your session.

ALTER SESSION SET TIME_ZONE='-00:00';
o
e
t
itu
t
Session altered.
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
iv
n
eU
l
c
Ora
gi
n
E
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Additional
Practice
Solutions
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Additional Practice 1 and 2: Solutions
1. Evaluate each of the following declarations. Determine which of them are not legal
and explain why.
a. DECLARE
name,dept
VARCHAR2(14);
This is illegal because only one identifier per declaration is allowed.
b. DECLARE
test
NUMBER(5);
This is legal.
c. DECLARE
MAXSALARY
NUMBER(7,2) = 5000;
This is illegal because the assignment operator is wrong. It should be :=.

d. DECLARE
JOINDATE
BOOLEAN := SYSDATE;
nd
a
g
in
r
e
ne
This is illegal because there is a mismatch in the data types. A Boolean

data type cannot be assigned a date value. The data type should be date.
2. In each of the following assignments, determine the data type of the resulting
expression.
a. email := firstname || to_char(empno);
gi
n
E
Ora
f
o
e
ut
t
i
t
s
y
n
l
I
n
i
Character string
o
h
d
e
n
s
b. confirm := to_date('20-JAN-1999',
'DD-MON-YYYY');
Ga gy u
d
n nolo
Date
a
y
it ech + 500
c. sal :=rs(1000*12)
e
T
iv
Number
n
U
cled. test := FALSE;
Boolean
e. temp := temp1 < (temp2/ 3);
Boolean
f. var := sysdate;
Date
Additional Practice 3: Solutions

3. DECLARE
custid
NUMBER(4) := 1600;
custname
VARCHAR2(300) := 'Women Sports Club';
new_custid
NUMBER(3) := 500;
BEGIN
DECLARE
custid
NUMBER(4) := 0;
custname VARCHAR2(300) := 'Shape up Sports Club';
new_custid NUMBER(3) := 300;
new_custname VARCHAR2(300) := 'Jansports Club';
BEGIN
custid := new_custid;
custname := custname || ' ' || new_custname;
nd
a
g
in
r
e
ne
END;
gi
n
E
f
o
e
ut
t
i
t
2 END;
ns nly
I
i
o
h
d
e
n
s
/
a yu
G
g determine the data type and value of each
d
Evaluate the PL/SQL block given
oand
n above
l
a
o
of the following variables,
hnto the rules of scoping:
ity according
c
s
r
e
e of CUSTID
T at position 1 is:
ivvalue
a. The
n
e U300, and the data type is NUMBER
l
c
Ora b. The value of CUSTNAME at position 1 is:
custid := (custid *12) / 10;
Shape up Sports Club Jansports Club, and the data type is VARCHAR2
c. The value of NEW_CUSTID at position 2 is:
500, and the data type is NUMBER (or INTEGER)
d. The value of NEW_CUSTNAME at position 1 is:
Jansports Club, and the data type is VARCHAR2
e. The value of CUSTID at position 2 is:
1920, and the data type is NUMBER
f. The value of CUSTNAME at position 2 is:
Women Sports Club, and the data type is VARCHAR2

4. Write a PL/SQL block to accept a year and check whether it is a leap year. For
example, if the year entered is 1990, the output should be 1990 is not a leap
year.
Hint: The year should be exactly divisible by 4 but not divisible by 100, or it
should be divisible by 400.
Test your solution with the following years:
1990
2000
Not a leap year

Leap year
1996
Leap year
1886
1992
Not a leap year

Leap year
1824
Leap year
nd
a
g
SET SERVEROUTPUT ON
DECLARE
YEAR NUMBER(4) := &P_YEAR;
REMAINDER1 NUMBER(5,2);
BEGIN
REMAINDER1 := MOD(YEAR,4);
IF ((REMAINDER1 = 0 AND REMAINDER2 <> 0 )
OR REMAINDER3 = 0) THEN
DBMS_OUTPUT.PUT_LINE(YEAR || ' is a leap year');
ELSE
DBMS_OUTPUT.PUT_LINE (YEAR || ' is not a leap
year');
END IF;
END;
/
SET SERVEROUTPUT OFF
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
5. a. For the exercises below, you require a temporary table to store the results.
You can either create the table yourself or run the lab_ap_05.sql script
that will create the table for you. Create a table named TEMP with the
following three columns:
Column Name
NUM_STORE
CHAR_STORE
DATE_STORE
Data Type
Number
VARCHAR2
Date
Length
7,2
35
Key Type
Nulls/Unique
FK Table
FK Column
CREATE TABLE temp
nd
a
g
(num_store NUMBER(7,2),
char_store VARCHAR2(35),
date_store DATE);
in
r
e
ne
gi
n
E
f
o
e
itut MESSAGE
t
b. Write a PL/SQL block that contains two s
variables,
y data typeandwith a length
nas VARCHAR2
l
I
DATE_WRITTEN. Declare MESSAGE
n
i
o Assign the following values to
h dataetype.
d
of 35 and DATE_WRITTEN as
DATE
n
s
Ga gy u
the variables:
d
nContents
lo
a
o
Variable
n
y
chis my first PL/SQL program.
sit TThis
MESSAGE
r
e
e
iv
DATE_WRITTEN
Current date
n
U
lethe values in appropriate columns of the TEMP table. Verify your results by
Store
c
a
r
O querying the TEMP table.
SET SERVEROUTPUT ON
DECLARE
MESSAGE VARCHAR2(35);
DATE_WRITTEN DATE;
BEGIN
MESSAGE := 'This is my first PLSQL Program.';
DATE_WRITTEN := SYSDATE;
INSERT INTO temp(CHAR_STORE,DATE_STORE)
VALUES (MESSAGE,DATE_WRITTEN);
END;
/
SELECT * FROM TEMP;
Additional Practices 6 and 7 Solutions
6. a. Store a department number in an iSQL*Plus substitution variable.

DEFINE P_DEPTNO = 30
b. Write a PL/SQL block to print the number of people working in that
department.
Hint: Enable DBMS_OUTPUT in iSQL*Plus with SET SERVEROUTPUT
ON.
SET SERVEROUTPUT ON
DECLARE
HOWMANY NUMBER(3);
DEPTNO DEPARTMENTS.department_id%TYPE :=
&P_DEPTNO;
BEGIN
SELECT COUNT(*) INTO HOWMANY FROM employees
WHERE department_id = DEPTNO;
DBMS_OUTPUT.PUT_LINE (HOWMANY || ' employee(s)
work for department number ' ||DEPTNO);
END;
/
nd
a
g
in
r
e
ne
gi
n
E
f
o
e
ut
t
i
t
s sal
7. Write a PL/SQL block to declare a variable
yto store the salary of an
ncalled
l
I
n
i
operform the following tasks:
h program,
employee. In the executable part ofd
the
e
n
s
u
a. Store an employee name
substitution variable:
Gain angyiSQL*Plus
d
o
SET SERVEROUTPUT
an hnol ON
y
t
i
c
P_LASTNAME
= Pataballa
rsDEFINE
e
e
T
v
i
b. n
Store his or her salary in the sal variable.
U
e
c.
If the salary is less than 3,000, give the employee a raise of 500 and
l
c
a
r
display the message <Employee Name>s salary updated in the
window.
d. If the salary is more than 3,000, print the employees salary in the format,
<Employee Name> earns ...
e. Test the PL/SQL block for the last names.
LAST_NAME
SALARY
Pataballa
4800
Greenberg
12000
Ernst
6000
Note: Undefine the variable that stores the employees name at the end of
the script.
Additional Practices 7 and 8: Solutions

DECLARE
SAL NUMBER(7,2);
LASTNAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
SELECT salary INTO SAL
FROM employees
WHERE last_name = INITCAP('&&P_LASTNAME') FOR
UPDATE of salary;
LASTNAME := INITCAP('&P_LASTNAME');
IF SAL < 3000 THEN
UPDATE employees SET salary = salary + 500
WHERE last_name = INITCAP('&P_LASTNAME') ;
DBMS_OUTPUT.PUT_LINE (LASTNAME || '''s salary
updated');
ELSE
DBMS_OUTPUT.PUT_LINE (LASTNAME || ' earns '
nd
a
g
in
r
e
ne
||
TO_CHAR(SAL));
END IF;
gi
n
E
f
o
e
ut
END;
t
i
t
ns nly
/
I
i
o
h
d
e
n
s
u
UNDEFINE P_LASTNAME Ga
y
g
d
n nolo
a
y
t toestore
h the salary of an employee in an iSQL*Plus
8. Write a PL/SQL
c
siblock
r
e
substitution
iv variable. InT the executable part of the program, perform the following:
n
e UCalculate the annual salary as salary * 12.
l
c
Calculate the bonus as indicated below:
Ora
Annual Salary
Bonus
>= 20,000
2,000
19,999 - 10,000
1,000
<= 9,999
500
Display the amount of the bonus in the window in the following format:
The bonus is $..
Test the PL/SQL for the following test cases:
SALARY
BONUS
5000
2000
1000
1000
15000
2000

SET SERVEROUTPUT ON
DEFINE P_SALARY = 5000
DECLARE
SAL NUMBER(7,2) := &P_SALARY;
BONUS
NUMBER(7,2);
ANN_SALARY NUMBER(15,2);
BEGIN
ANN_SALARY := SAL * 12;
IF ANN_SALARY >= 20000 THEN
BONUS := 2000;
ELSIF ANN_SALARY <= 19999 AND ANN_SALARY >=10000 THEN
BONUS := 1000;
ELSE
BONUS := 500;
END IF;
DBMS_OUTPUT.PUT_LINE ('The Bonus is $ ' ||
TO_CHAR(BONUS));
END;
/
nd
a
g
in
r
e
ne
gi
n
E
9. a.
f
o
e
Execute the lab_ap_09_a.sql script toitcreate
ut a temporary table called
t
s
emp. Write a PL/SQL block to store an
the new department
ynumber,
nemployee
l
I
n
i
o
h
number, and the percentage increase
salary in iSQL*Plus substitution
nd inuthe
se
variables.
a
GON gy
d
SET SERVEROUTPUT
n
lo
a
o
n
DEFINE P_EMPNO
=
100
y
sit Tech = 10
r
DEFINE
P_NEW_DEPTNO
e
iv P_PER_INCREASE = 2
DEFINE
n
U
le
c
a
Or b. Update the department ID of the employee with the new department number,
and update the salary with the new salary. Use the emp table for the updates.
After the update is complete, display the message Update complete in the
window. If no matching records are found, display the message No Data
Found. Test the PL/SQL block for the following test cases.
EMPLOYEE_ID
NEW_DEPARTMENT_ID
% INCREASE
MESSAGE
100
20
10
30
126
40
Update
Complete
No Data
found
Update
Complete

DECLARE
EMPNO emp.EMPLOYEE_ID%TYPE := &P_EMPNO;
NEW_DEPTNO emp.DEPARTMENT_ID%TYPE :=
&P_NEW_DEPTNO;
PER_INCREASE NUMBER(7,2) := & P_PER_INCREASE;
BEGIN
UPDATE emp
SET department_id = NEW_DEPTNO,
salary = salary + (salary * PER_INCREASE/100)
WHERE employee_id = EMPNO;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('No Data Found');
ELSE
DBMS_OUTPUT.PUT_LINE ('Update Complete');
END IF;
END;
/
nd
a
g
in
r
e
ne
f
o
e
ut
t
i
t
ns nly
I
i
o
h
d
e
n
s
Ga gy u
d
SET SERVEROUTPUT
n ONnolo
a
y
h
DECLARE sit
c
r
e
e
T IS
CURSOR
EMP_CUR
iv
n
U SELECT last_name,salary,hire_date
cle ENAME VARCHAR2(25);
gi
n
E
10. Create a PL/SQL block to declare an EMP_CUR cursor to select the employee
name, salary, and hire date from the employees table. Process each row from the
cursor, and if the salary is greater than 15,000 and the hire date is greater than 01FEB-1988, display the employee name, salary, and hire date in the window.
Ora
FROM EMPLOYEES;
SAL
NUMBER(7,2);
HIREDATE DATE;
BEGIN
OPEN EMP_CUR;
FETCH EMP_CUR INTO ENAME,SAL,HIREDATE;
WHILE EMP_CUR%FOUND
LOOP
IF SAL > 15000 AND HIREDATE >= TO_DATE('01-FEB1988','DD-MONYYYY') THEN
DBMS_OUTPUT.PUT_LINE (ENAME || ' earns ' ||
TO_CHAR(SAL)||
' and joined the organization on ' ||
TO_DATE(HIREDATE,'DDMon-YYYY'));
END IF;

FETCH EMP_CUR INTO ENAME,SAL,HIREDATE;
END LOOP;
CLOSE EMP_CUR;
END;
/
11. Create a PL/SQL block to retrieve the last name and department ID of each
employee from the employees table for those employees whose EMPLOYEE_ID
is less than 114. From the values retrieved from the employees table, populate
two PL/SQL tables, one to store the records of the employee last names and the
other to store the records of their department IDs. Using a loop, retrieve the
employee name information and the salary information from the PL/SQL tables
and display them in the window, using DBMS_OUTPUT.PUT_LINE. Display these
details for the first 15 employees in the PL/SQL tables.
SET SERVEROUTPUT ON
DECLARE
TYPE Table_Ename is table of
employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE Table_dept is table of
employees.department_id%TYPE
INDEX BY BINARY_INTEGER;
Tename Table_Ename;
Tdept Table_dept;
i BINARY_INTEGER :=0;
CURSOR Namedept IS SELECT last_name,department_id
from employees WHERE employee_id < 115;
TRACK NUMBER := 15;
BEGIN
FOR emprec in Namedept
LOOP
i := i +1;
Tename(i) := emprec.last_name;
Tdept(i) := emprec.department_id;
END LOOP;
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E

FOR i IN 1..TRACK
LOOP
DBMS_OUTPUT.PUT_LINE ('Employee Name: ' ||
Tename(i) || ' Department_id: ' || Tdept(i));
END LOOP;
END;
/
12. a. Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a
parameter of the DATE data type to the cursor and print the details about all the
employees who have joined after that date.
SET SERVEROUTPUT ON
DEFINE P_HIREDATE = 08-MAR-00
nd
a
g
in
r
e
b. Test the PL/SQL block for the following hire dates: 08-MAR-00
n,e25-JUN-97,
i
g
n
28-SEP-98, 07-FEB-99.
fE
o
ute
t
DECLARE
i
t
y IS
ns DATE)
l
CURSOR DATE_CURSOR(JOIN_DATE
I
n
i
o
h
d
SELECT employee_id,last_name,hire_date
FROM
e
n
s
a
u
employees
G gy
d
n
lo
WHERE HIRE_DATE
>JOIN_DATE
;
a
o
n
y
t
h
i
EMPNOrs employees.employee_id%TYPE;
c
e
e
T
v
employees.last_name%TYPE;
i
nENAME
U
HIREDATE
employees.hire_date%TYPE;
e HDATE employees.hire_date%TYPE
l
c
:=
'&P_HIREDATE';
ra
BEGIN
OPEN DATE_CURSOR(HDATE);
LOOP
FETCH DATE_CURSOR INTO EMPNO,ENAME,HIREDATE;
EXIT WHEN DATE_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (EMPNO || ' ' || ENAME || '
' ||
HIREDATE);
END LOOP;
END;
/
SET SERVEROUTPUT OFF;

13. Execute the lab_ap_09_a.sql script to re-create the emp table. Create a
PL/SQL block to promote clerks who earn more than 3,000 to SR CLERK and
increase their salaries by 10%. Use the emp table for this practice. Verify the
results by querying on the emp table.
Hint: Use a cursor with FOR UPDATE and CURRENT OF syntax.
DECLARE
CURSOR Senior_Clerk IS
SELECT employee_id,job_id FROM emp
WHERE job_id = 'ST_CLERK' AND salary > 3000
FOR UPDATE OF job_id;
BEGIN
FOR Emrec IN Senior_Clerk
LOOP
UPDATE emp
SET job_id = 'SR_CLERK',
salary = 1.1 * salary
WHERE CURRENT OF Senior_Clerk;
END LOOP;
COMMIT;
END;
/
SELECT * FROM emp;
in
r
e
ne
nd
a
g
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E

14. a. For the following exercise, you require a table to store the results. You can
create the analysis table yourself or run the lab_ap_14_a.sql script
that creates the table for you. Create a table called analysis with the
following three columns:
ENAME
YEARS
SAL
Column Name
Key Type
Nulls/Unique
FK Table
FK Column
Data Type
VARCHAR2
Number
Number
Length
20
8,2
CREATE TABLE analysis

(ename Varchar2(20),
years Number(2),
sal Number(8,2));
nd
a
g
in
r
e
ne
gi
n
E
f
o
e
ut
t
i
t
with the information
s
b. Create a PL/SQL block to populate theIn
analysis
ytable
l
n
i
o substitution variable to store an
h iSQL*Plus
from the employees table. Usedan
e
n
s
u
employees last name. Ga
y
g
d
n nolo
a
y
sit TechON
SET SERVEROUTPUT
r
e
iv P_ENAME = Austin
n
DEFINE
eU
l
c
ra
c. Query the employees table to find if the number of years that the employee
has been with the organization is greater than five, and if the salary is less than
3,500, raise an exception. Handle the exception with an appropriate exception
handler that inserts the following values into the analysis table: employee
last name, number of years of service, and the current salary. Otherwise,
display Not due for a raise in the window. Verify the results by
querying the analysis table. Use the following test cases to test the PL/SQL
block.
LAST_NAME
MESSAGE
Austin
Not due for a raise
Nayer
Not due for a raise
Fripp
Not due for a raise
Khoo
Due for a raise
Additional Practice 14: Solutions (continued)

DECLARE
DUE_FOR_RAISE EXCEPTION;
HIREDATE EMPLOYEES.HIRE_DATE%TYPE;
ENAME EMPLOYEES.LAST_NAME%TYPE := INITCAP(
'&P_ENAME');
SAL EMPLOYEES.SALARY%TYPE;
YEARS NUMBER(2);
BEGIN
SELECT LAST_NAME,SALARY,HIRE_DATE
INTO ENAME,SAL,HIREDATE
FROM employees WHERE last_name = ENAME;
YEARS := MONTHS_BETWEEN(SYSDATE,HIREDATE)/12;
IF SAL < 3500 AND YEARS > 5 THEN
RAISE DUE_FOR_RAISE;
ELSE
DBMS_OUTPUT.PUT_LINE ('Not due for a raise');
END IF;
EXCEPTION
WHEN DUE_FOR_RAISE THEN
INSERT INTO ANALYSIS(ENAME,YEARS,SAL)
VALUES (ENAME, YEARS, SAL);
END;
/
in
r
e
ne
nd
a
g
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
nd
a
g
in
r
e
ne
O
le
c
a
r
U
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Additional Practices:
Table Descriptions
and Data
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part A
The tables and data used in part A are the same as those in Appendix B, Table
Descriptions and Data.
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Oracle Database 10g: Develop PL/SQL Program Units Table Descriptions-2
Part B: Tables Used
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B: MEMBER Table

DESCRIBE member
SELECT * FROM member;
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B: RENTAL Table

DESCRIBE rental
SELECT * FROM rental;
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B: RESERVATION Table

DESCRIBE reservation
SELECT * FROM reservation;
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B: TITLE Table

DESCRIBE title
SELECT * FROM title;
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E
Part B: TITLE_COPY Table

DESCRIBE title_copy
SELECT * FROM title_copy;
nd
a
g
in
r
e
ne
le
c
a
r
o
e
t
itu
t
s
In only
i
h
nd use
a
G gy
d
n
lo
a
o
n
y
sit Tech
r
e
niv
gi
n
E

PL SQL Student Guide v-3

Uploaded by

Copyright:

Available Formats

PL SQL Student Guide v-3

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

PL SQL Student Guide v-3

Uploaded by

Copyright:

Available Formats

Unauthorized reproduction or distribution prohibited Copyright 2012, Oracle and/or its affiliates

Copyright 2006, Oracle. All rights reserved.

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle

This document contains proprietary information and is protected by copyright and

Creating Stored Procedures

Syntax for Passing Parameters 1-14

Creating Stored Functions

Using Oracle-Supplied Packages in Application Development

Dynamic SQL and Metadata

Declaring Cursor Variables 6-11

Local Subprograms 7-7

Using BULK COLLECT INTO with Queries 7-18

Using the NOCOPY Hint 7-21

NOCOPY Hint Can Be Ignored 7-23

Manipulating Large Objects

Contrasting LONG and LOB Data Types 9-5

Populating BFILE Columns with SQL 9-15

Populating a BFILE Column with PL/SQL 9-16

Removing LOBs 9-30

Trigger Event Types and Body 10-10

Reading Data from a Mutating Table 11-8

12 Understanding and Influencing the PL/SQL Compiler

Using DBMS_WARNING Procedures 12-14

Typographic Conventions in Text

Emphasized words and phrases To navigate within this application, do

Glossary terms (if there is a

The algorithm inserts the new key.

Click the Executable button.

Assign a When-Validate-Item trigger.

Open the Master Schedule window.

Select File > Save.

Typographic Conventions (continued)

Typographic Conventions in Text (continued)

Code output: debug.seti (I,300);

Path names: Open c:\my_docs\projects.

the name of the user.

Press and hold these keys simultaneously:

Lesson and chapter

This subject is covered in Unit II, Lesson 3, Working

Typographic Conventions (continued)

Additional Practices: Overview

Oracle Database 10g: Develop PL/SQL Program Units AP-2

Oracle Database 10g: Develop PL/SQL Program Units AP-3

Oracle Database 10g: Develop PL/SQL Program Units AP-7

Oracle Database 10g: Develop PL/SQL Program Units AP-8

Oracle Database 10g: Develop PL/SQL Program Units AP-9

Oracle Database 10g: Develop PL/SQL Program Units AP-10

Oracle Database 10g: Develop PL/SQL Program Units AP-11

Part A: Additional Practice 1 Solutions

Part A: Additional Practice 2 Solutions

Oracle Database 10g: Develop PL/SQL Program Units APS-3

Part A: Additional Practice 2 Solutions (continued)

EXECUTE add_job_hist(106, 'SY_ANAL')

e. Re-enable the triggers on the EMPLOYEES, JOBS, and JOB_HISTORY tables.

Part A: Additional Practice 3 Solutions

Oracle Database 10g: Develop PL/SQL Program Units APS-5

Part A: Additional Practice 3 Solutions (continued)

b. Execute the UPD_JOBSAL procedure by using a job ID of 'SY_ANAL', a minimum

c. Disable triggers on the EMPLOYEES and JOBS tables.

f. Enable the triggers on the EMPLOYEES and JOBS tables.