Section
Contents
Page
One
PLSQL Introduction
2
- Procedural Language for SQL
3
Two
PLSQL Structure
5
- Basic Structure of PLSQL
6
- The Declaration Section in PLSQL
7
- Boolean Datatypes
11
- Knowledge Check
14
- The Processing Block in PLSQL
15
- Error Handling in PLSQL
16
- Populating Variables in PLSQL
17
- Running Scripts in SQL*PLUS
19
- Exercise One
20
- Using Substitution Values in PLSQL
23
- Declaring Variables Using %Type
27
- Conditional Tests (if statements)
29
- Messaging Via PLSQL
37
- Exercise Two
39
Three
Implicit Cursors
40
- Using Implicit Cursors in PLSQL
41
- Using Implicit Cursors to Select Records
43
- Exceptions in PLSQL
44
- Attributes of Implicit Cursors
52
- SQL%FOUND
53
- SQL%NOTFOUND
54
- SQL%ISOPEN
55
- SQL%ROWCOUNT
56
- Exercise Three
57
Four
Explicit Cursors
58
- Using Explicit Cursors in PLSQL
59
- How Explicit Cursors Work
61
- Attributes of an Explicit Cursor
64
- Passing Parameters to Explicit Cursors
67
- Explicit Cursors Versus Implicit Cursors
70
Five
User Defined Exceptions
71
- Raising Exceptions in PLSQL
72
- Example of Raising Exceptions
74
- Using Labels in PLSQL
78
- Labels versus Exceptions
79
- Exercise Four
81
Six
Loop Processing
82
- Iterative Processing in PLSQL
83
- Basic Looping in PLSQL
84
- While Looping in PLSQL
87
- For Looping in PLSQL
89
- Implicit Cursor Looping in PLSQL
93
- Explicit Cursor Looping in PLSQL
95
- Using Exit When in PLSQL
98
- Cursor Exiting Using Exit When
100
- Loop Labels in PLSQL
101
- Exercise Five
105
Seven
PL/SQL Database Objects
112
- PLSQL Objects
113
- Compiling PLSQL Objects
114
- Metadata for PLSQL Objects
117
- Re-Compiling PLSQL Objects
118
- Dropping PLSQL Objects
120
- Passing Parameters in PLSQL
121
- Metadata
125
Eight
Functions
127
- Syntax of Functions in Oracle
128
- Using Functions in Oracle
133
- Exercise Six
138
Nine
Procedures
137
- Syntax of Procedures in Oracle
138
- Usage of Procedures in Oracle
141
- Exercise Seven
143
Ten
Packages
145
- Syntax of Packages in Oracle
146
- Compiling of Packages in Oracle
151
- Usage of Packages in Oracle
152
- Exercise Eight
153
Eleven
Triggers
154
- Syntax of Triggers in Oracle
155
- Timing of Triggers in Oracle
160
- Conditions in Triggers in Oracle
161
- Trigger Metadata
162
- Maintaining Triggers in Oracle
163
- Mutating Triggers in Oracle
165
- Referencing Records in Triggers
169
- Exercise Nine
173
Twelve
Advanced Techniques
177
- Using %Rowtype in PLSQL
178
- Using a Cursor in %Rowtype
187
- Exercise Ten
190
- Case Statements in PLSQL
194
- Updating with Explicit Cursors
196
- Savepoints in PLSQL
203
- Sub-programming in PLSQL
205
- Packages without Bodies
207
Thirteen
Error Handling
209
- Techniques for Error Handling in PLSQL
210
- Exceptions
214
- Exceptions Between Called Processes
217
- Metadata
218
Fourteen
Standards in PLSQL
219
- Introduction
220
- Naming Conventions
221
- Commenting
224
- Programming Style
226
- Version Control
227
- Exercise Eleven
228
Fifteen
File I/O
231
- Reading and Writing Files in PLSQL
232
- Simple Write in PLSQL
234
- Simple Read in PLSQL
235
- UTL_FILE in PLSQL
236
- Exercise Twelve
241
Sixteen
Dynamic SQL
243
- Creating Dynamic SQL in PLSQL
244
- Execute Immediate in NDS
246
- The DBMS_SQL Package in PLSQL
256
- Exercise Thirteen
261