PART Ⅰ Language Syntax 3
1 Overview of PL/SQL 3
An Introduction to the PL/SQL Language 4
PL/SQL Syntax Fundamentals 7
Statements 8
Identifiers 8
Comments 8
Literals 9
Declaration Section 10
Variables 10
Datatypes 11
Constants 14
Other Declared Elements 14
Processing Section 15
Expressions 15
Assignment Statements 21
Conditional Statements 22
Loops 25
Cursors 28
Implicit Cursors 28
Explicit Cursors 30
Cursor Attributes 32
Loops Revisited:The Cursor FOR Loop 33
Advanced Datatype Declaration 34
%TYPE 34
%ROWTYPE 35
Exception-Handling Section 37
System-Defined Exceptions 38
User-Defined Exceptions 39
Working with Blocks 40
Nested Blocks 41
Scope:Declared Elements 42
Exception Scope and Exception Propagation 44
Anonymous Blocks 47
Named Program Units 47
An Introduction to Program Units 47
Chapter Summary 49
Two-Minute Drill 51
Chapter Questions 53
Answers to Chapter Questions 57
2 Procedures 61
Uses of Procedures 62
What Can You Do with Procedures? 63
Where Can You Store Procedures? 64
Creating,Altering,and Dropping Procedures 66
Creating Procedures 66
Altering Procedures 70
Dropping Procedures 72
Executing a Procedure from a PL/SQL Block 73
Invoking Procedures 73
Executing a Procedure from the SQL Plus Command Line 74
Parameters 76
Parameter Declaration Syntax 76
Parameter Datatypes 77
Default Values 77
Parameter Types 78
Positional Notation versus Named Notation 83
Data Dictionary Resources for Procedures 85
USER_OBJECTS 86
USER_OBJECT_SIZE 88
USER_SOURCE 88
USER_ERRORS 89
SHOW ERRORS 91
Chapter Summary 93
Two-Minute Drill 95
Chapter Questions 96
Answers to Chapter Questions 100
3 Functions 103
Uses of Functions 104
Functions versus Procedures 105
Creating,Altering,and Dropping Functions 106
Creating Functions 106
Altering Functions 107
Dropping Functions 108
Invoking Functions 109
Functions Called from PL/QL Expressions 110
Functions Called from SQL Statements 112
Client-Side Functions 113
Parameters 115
Passing Parameters by Reference 117
RETURN 119
Two-Minute Drill 123
Chapter Summary 123
Chapter Questions 124
Answers to Chapter Questions 129
4 Packages 133
Uses of Packages 134
Creating,Altering,and Dropping Packages 137
Creating a Package Specification 137
Creating a Package Body 139
Public versus Private Constructs 140
Global Constructs 141
Altering a Package 143
Dropping a Package 147
Changes to a Package Specification that Require a Change to the Body 149
Invoking Packaged Constructs 151
Referencing Packaged Constructs 151
Using Packaged Constructs 151
Data Dictionary Resources for Packages 156
Chapter Summary 158
Two-Minute Drill 159
Chapter Questions 160
Answers to Chapter Questions 164
5 Triggers 167
Uses of Triggers 168
Creating,Altering,and Dropping Triggers 170
Creating Triggers 171
The:old and:new Qualifiers 175
Conditional Predicates 177
Firing Rules 178
Restrictions 179
Altering Triggers 181
Dropping Triggers 181
Enabling and Disabling Triggers 183
Disabling and Enabling a Named Trigger 183
Enabling and Disabling a Named Table's Triggers 184
lNSTEAD OF Triggers 185
Non-DML Triggers 188
DDL Triggers 189
Data Dictionary Resources for Triggers 190
USER_TRIGGERS 191
Chapter Summar 194
Two-Minute Drill 195
Chapter Questions 197
Answers to Chapter Questions 200
6 Working with Program Units 203
Client-Side versus Server-Side Program Units 204
Invoking Server-Side Program Units 205
Invoking Client-Side Program Units 206
The Trade-0f—Where to Put Them? 207
Local Subprograms 209
Forward Declarations 209
Overloading 214
Creating Overloaded Modules 215
Namespaces for Program Units 217
Initializing Variables with a One-Time-Only Procedure 219
Functions and Purity Levels 221
WNDS and RNDS 224
WNPS and RNPS 224
TRUST 225
Purity Levels for the Package Initialization Section 225
DEFAULT 226
The Benefits of PRAGMA 226
Persistent States 228
Persistent Variables and Constants 228
Persistent Tables,Records,and Types 231
Persistent Cursors 232
Chapter Summary 237
Two-Minute Drill 238
Chapter Questions 239
Answers to Chapter Questions 243
7 Working with Oracle's PL/SQL Packages 245
DBMS_OUTPUT 247
SET SERVEROUTPUT ON 247
ENABLE and DISABLE 248
PUT_LINE,PUT,and NEW_LINE 248
GET_LINE and GET_LINES 249
DBMS_JOB 251
JOB_QUEUE_PROCESSES and JOB_QUEUE_lNTERVAL 251
The SUBMIT Procedure 252
The REMOVE Procedure 253
The RUN Procedure 253
The CHANGE,NEXT_DATE,WHAT,INTERVAL,and INSTANCE Procedures 254
Other Procedures:USER_EXPORT,BROKEN,and ISUBMIT 254
Monitoring Batch Jobs with USER_JOBS 255
The ALTER_COMPILE Procedure 257
DBMS_DDL 257
The ANALYZE_OBJECT Procedure 258
Monitoring the Results of ALTER_COMPILE and ANALYZE_OBJECT 259
DBMS_PIPE 260
The PACK_MESSAGE Procedure 260
The SEND_MESSAGE Function 260
The RECEIVE_MESSAGE Function 261
The NEXT_ITEM_TYPE Function 262
The UNPACK_MESSAGE Procedure 262
Other Procedures 262
DBMS_SQL 263
The OPEN_CURSOR Function and the CLOSE_CURSOR Procedure 268
The PARSE Procedure 268
The DEFINE_COLUMN Procedure 269
The EXECUTE Function 270
The FETCH_ROWS Function 270
The BIND_VARIABLE Procedure 271
The COLUMN_VALUE Procedure 271
Native Dynamic SQL 272
Compilation Errors 273
Chapter Summary 274
Two-Minute Drill 275
Chapter Questions 276
Answers to Chapter Questions 278
PART Ⅱ Development Tools 283
8 Using Oracle PL/SQL Tools 283
SQL Plus 284
The SQL Plus Buffer Editor 287
Using Text Editors 289
Executing Text Files 291
PL/SQL Blocks and the Buffer 291
Procedure Builder 292
Object Navigator 294
Creating a Program Unit with Procedure Builder 298
Executing a Program Unit 305
Moving Program Units 306
Working with Libraries 307
Creating Database Triggers 308
Chapter Summary 310
Two-Minute Drill 311
Chapter Questions 312
Answers to Chapter Questions 315
9 Debugging PL/SQL 317
Debugging Concepts 318
Debugging Techniques 318
Code Formatting:An Example 320
Debugging Tools 321
Debugging with SQL Plus 323
Debugging Compilation Errors with SQL Plus 323
SHOW ERROR 328
Debugging Execution Errors in SQL Plus 329
Debugging with Procedure Builder 330
Starting Debugger:Setting Breakpoints 331
Chapter Summary 340
Two-Minute Drill 340
Chapter Questions 341
Answers to Chapter Questions 344
PART Ⅲ Privileges and Interdependence 349
10 Managing Privileges 349
System Privileges 350
Roles 351
System and Object Privilege Requirements for Program Units 353
System Privileges for Program Units 353
Object Privileges for Program Units 356
Owner and Invoker Rights 358
Owner Rights 358
Invoker Rights 360
Granting and Revoking Privileges 362
Data Dictionary Resources 364
SESSION_PRIVS 364
SESSION_ROLES 365
USER_SYS_PRIVS 365
USER_ROLE_PRIVS 365
DBA_SYS_PRIVS 366
USER_TAB_PRIVS,USER_TAB_PRIVS_MADE,and USER_TAB_PRIVS RECD 366
Chapter Summary 368
Two-Minute Drill 370
Chapter Questions 371
Answers to Chapter Questions 373
11 Managing Interdependencies 377
Tracking Dependencies 378
USER_DEPENDENCIES 378
DEPTREE and IDEPTREE 382
Dependency Issues Within a Single Database 386
Dependency Issues Across Multiple Databases 388
The Timestamp Mode 390
The Signature Mode 390
Avoiding Recompilation Errors 392
Chapter Summary 394
Two-Minute Drill 396
Chapter Questions 396
Answers to Chapter Questions 399
PART Ⅳ Practice Exams 403
12 Practice Exams 403
Practice Exam #1 404
Answers to Practice Exam #1 425
Practice Exam #2 436
Answers to Practice Exam #2 452
Practice Exam #3 462
Answers to Practice Exam #3 478