Oracle Database 10G: Develop Pl/Sql Program Units

Oracle Database 10g: Develop PL/SQL Program Units
Volume I • Student Guide

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Authors
Salome Clement Tulika Srivastava Glenn Stokol

Copyright © 2009, Oracle. All rights reserved. Disclaimer This document contains proprietary information and is protected bycopyright 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 documentin whole or in part without the express authorization of Oracle. 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 isdelivered 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. Government’s 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. Trademark Notice Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Technical Contributors and Reviewers
Don Bates Brian Boxx Dr. Christoph Burandt Zarko Cesljas Yanti Chang Kathryn Cunningham Brent Dayley Burt Demchick Laurent Dereac Peter Driver Laura Garza Nancy Greenberg Craig HollisterThomas Hoogerwerf Taj-Ul Islam Yash Jain Inger Joergensen Chaitanya Koratamaddi Eric Lee Bryn Llewellyn Malika Marghadi Hildegard Mayr Timothy Mcglue Anita Mukundan Nagavalli Pataballa Sunitha Patel Srinivas Putrevu Denis Raphaely Bryan Roberts Helen Robertson Grant Spencer Glenn Stokol Tone Thomas Priya Vennapusa Michael Versaci Lex Van Der Werff

Graphic Designer
Priya Saxena

Editors
JoyceRaftery Nita Pavitran

Publishers
Jobi Varghese Sheryl Domingue

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

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 ModularizingDevelopment 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 What Is Oracle SQL Developer? I-17 Creating a Database Connection I-18 Creating an Anonymous Block I-19 CodingPL/SQL in SQL*Plus I-20 Coding PL/SQL in Oracle JDeveloper I-21 Summary I-22 Practice I: Overview I-23 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 OUTParameters 1-11

1

iii THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED

Using IN OUT Parameters: Example 1-12 Syntax for Passing Parameters 1-13 Parameter Passing: Examples 1-14 Using the DEFAULT Option for Parameters 1-15 Summary of Parameter Modes 1-17 Invoking Procedures 1-18 Handled Exceptions 1-19 Handled...