Oracle Programming Using PL/SQL

Oracle Programming Using PL/SQL: Level 2 What You Will Learn...................................................................................................................... 1 Working with Subprograms............................................................................................................ 2 Comparing Subprograms and Anonymous Blocks................................................................... 2 Writing Modularized Code ....................................................................................................... 3 Understanding a Subprogram’s Structure................................................................................. 3 Writing Stored Procedures.............................................................................................................. 5 Creating a New Procedure ........................................................................................................5 Compiling the Code .................................................................................................................. 7 Invoking the Procedure............................................................................................................. 9 Modifying and Recompiling the Procedure.............................................................................. 9 Exercise 1: Writing Basic Stored Procedures ............................................................................... 11 Using Parameters .......................................................................................................................... 13 Using IN Parameters............................................................................................................... 14 Passing Parameters to a Procedure ......................................................................................... 15 Assigning Default

Values for IN Parameters.......................................................................... 17 Using OUT Parameters........................................................................................................... 18 Using IN OUT Parameters...................................................................................................... 21 Comparing Parameter Modes ................................................................................................. 23 Streamlining Your Procedure ................................................................................................. 23 Exercise 2: Writing Stored Procedures with Parameters .............................................................. 26 Writing Functions ......................................................................................................................... 29 Developing Functions ............................................................................................................. 29 Writing a New Function.......................................................................................................... 30 Invoking a Function ................................................................................................................ 32 Applying What You’ve Learned............................................................................................. 35 Exercise 3: Writing Functions ...................................................................................................... 37 Writing Local Modules................................................................................................................. 39 Declaring Local Modules........................................................................................................ 39 Modularizing Complex Procedures ........................................................................................ 40 Working with Composite Data Structures .................................................................................... 43 Classifying PL/SQL’s Composite Data Structures................................................................. 43 Working with Records ............................................................................................................ 43 Working with Associative Arrays........................................................................................... 47 Exercise 4: Working with Composite Data Structures ................................................................. 53 Oracle Programming Using PL/SQL: Level 2 www.micros.umsl.edu 2/6/09 Working with Packages ................................................................................................................ 55 Understanding the Package Structure ..................................................................................... 56 Creating and Using Packages.................................................................................................. 57 Developing a Package Specification....................................................................................... 58 Developing a Package Body ................................................................................................... 61 Referencing Packaged Elements............................................................................................. 66 Testing Your Procedures......................................................................................................... 67 Modifying Packages................................................................................................................ 68 Exercise 5: Organizing Your Code in Packages........................................................................... 69 Developing More Sophisticated Packages.................................................................................... 71 Understanding Package Variables .......................................................................................... 71 Initializing a Package.............................................................................................................. 72 Making Forward Declarations ................................................................................................ 73 Overloading Subprograms ...................................................................................................... 75 Using Packaged Cursors ......................................................................................................... 76 Working with Built-In Packages............................................................................................. 78 Designing Effective Packages................................................................................................. 79 Exercise 6:...

Website: www.micros.umsl.edu | Filesize: 124kb
No of Page(s): 2
Download Course Title.pdf