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 2/6/09
[Link]
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: Developing Sophisticated Packages........................................................................... 80
Writing Triggers............................................................................................................................ 82
Using DML Triggers............................................................................................................... 82
Designing DML Triggers........................................................................................................ 83
Developing Statement-Level DML Triggers .......................................................................... 83
Creating Row-Level DML Triggers ....................................................................................... 88
Creating INSTEAD OF Triggers ............................................................................................ 93
Creating Database Triggers..................................................................................................... 95
Exercise 7: Writing Triggers......................................................................................................... 97
Managing Your PL/SQL Applications ......................................................................................... 99
Using the DESCRIBE Command ........................................................................................... 99
Using Data Dictionaries.......................................................................................................... 99
Understanding Privileges ...................................................................................................... 102
Exercise 8: Managing PL/SQL Program Units........................................................................... 104
Appendix A: Fads Through the Decades Museum ..................................................................... 105
Appendix B: SQL*Plus Commands and SQL Developer .......................................................... 107
Appendix C: Solutions to Exercises 1-8 ..................................................................................... 108
Oracle Programming Using PL/SQL: Level 2
[Link]