PL SQL User's Guide and Reference

Contents......Page 3
Send Us Your Comments......Page 15
Preface......Page 17
What's New in PL/SQL?......Page 23
Tight Integration with SQL......Page 31
Better Performance......Page 32
Support for Object-Oriented Programming......Page 33
Block Structure......Page 34
Variables and Constants......Page 35
Declaring PL/SQL Variables......Page 36
Control Structures......Page 37
Writing Reusable PL/SQL Code......Page 39
Data Abstraction......Page 40
PL/SQL Architecture......Page 42
In the Oracle Database Server......Page 43
In Oracle Tools......Page 44
Lexical Units......Page 45
Delimiters......Page 46
Identifiers......Page 47
Literals......Page 48
Comments......Page 51
Declarations......Page 52
Using the %TYPE Attribute......Page 53
Using the %ROWTYPE Attribute......Page 54
PL/SQL Naming Conventions......Page 56
Scope and Visibility of PL/SQL Identifiers......Page 58
Assigning Values to Variables......Page 60
PL/SQL Expressions and Comparisons......Page 61
Logical Operators......Page 62
Boolean Expressions......Page 65
CASE Expressions......Page 68
Handling Null Values in Comparisons and Conditional Statements......Page 69
Summary of PL/SQL Built-In Functions......Page 72
Overview of Predefined PL/SQL Datatypes......Page 75
PL/SQL Number Types......Page 76
PL/SQL Character and String Types......Page 78
PL/SQL National Character Types......Page 82
PL/SQL LOB Types......Page 84
PL/SQL Boolean Types......Page 85
PL/SQL Date, Time, and Interval Types......Page 86
Datetime and Interval Arithmetic......Page 89
Defining Subtypes......Page 90
Using Subtypes......Page 91
Implicit Conversion......Page 92
RAW and LONG RAW Values......Page 94
Overview of PL/SQL Control Structures......Page 95
Using the IF-THEN-ELSE Statement......Page 96
Using the CASE Statement......Page 97
Guidelines for PL/SQL Conditional Statements......Page 99
Using the LOOP Statement......Page 100
Labeling a PL/SQL Loop......Page 101
Using the WHILE-LOOP Statement......Page 102
Using the FOR-LOOP Statement......Page 103
Using the GOTO Statement......Page 106
Using the NULL Statement......Page 107
What Is a Collection?......Page 109
Understanding Varrays......Page 110
Understanding Associative Arrays (Index-By Tables)......Page 111
Choosing Which PL/SQL Collection Types to Use......Page 112
Choosing Between Nested Tables and Varrays......Page 113
Defining Collection Types......Page 114
Defining SQL Types Equivalent to PL/SQL Collection Types......Page 115
Declaring PL/SQL Collection Variables......Page 116
Initializing and Referencing Collections......Page 118
Referencing Collection Elements......Page 120
Assigning Collections......Page 121
Comparing Collections......Page 124
Using PL/SQL Collections with SQL Statements......Page 125
Using PL/SQL Varrays with INSERT, UPDATE, and SELECT Statements......Page 128
Using Multilevel Collections......Page 129
Using Collection Methods......Page 131
Checking the Maximum Size of a Collection (LIMIT Method)......Page 132
Finding the First or Last Collection Element (FIRST and LAST Methods)......Page 133
Looping Through Collection Elements (PRIOR and NEXT Methods)......Page 134
Increasing the Size of a Collection (EXTEND Method)......Page 135
Decreasing the Size of a Collection (TRIM Method)......Page 136
Deleting Collection Elements (DELETE Method)......Page 137
Avoiding Collection Exceptions......Page 138
Defining and Declaring Records......Page 140
Using Records as Procedure Parameters and Function Return Values......Page 141
Assigning Values to Records......Page 142
Comparing Records......Page 143
Updating the Database with PL/SQL Record Values......Page 144
Restrictions on Record Inserts/Updates......Page 145
Querying Data into Collections of Records......Page 146
Data Manipulation......Page 147
SQL Pseudocolumns......Page 148
SQL Operators......Page 150
Performing DML Operations from PL/SQL (INSERT, UPDATE, and DELETE)......Page 151
Overview of Implicit Cursor Attributes......Page 152
Selecting At Most One Row: SELECT INTO Statement......Page 153
Performing Complicated Query Processing: Explicit Cursors......Page 154
Querying Data with PL/SQL: Explicit Cursor FOR Loops......Page 155
Overview of Explicit Cursors......Page 156
Using Subqueries......Page 159
Writing Maintainable PL/SQL Queries......Page 161
Overview of Explicit Cursor Attributes......Page 162
Why Use Cursor Variables?......Page 165
Declaring REF CURSOR Types and Cursor Variables......Page 166
Controlling Cursor Variables: OPEN-FOR, FETCH, and CLOSE......Page 168
Avoiding Errors with Cursor Variables......Page 172
Using Cursor Expressions......Page 173
Example of Cursor Expressions......Page 174
Using COMMIT, SAVEPOINT, and ROLLBACK in PL/SQL......Page 175
Ending Transactions......Page 177
Overriding Default Locking......Page 178
Defining Autonomous Transactions......Page 181
Controlling Autonomous Transactions......Page 183
Using Autonomous Triggers......Page 184
Calling Autonomous Functions from SQL......Page 185
What Is Dynamic SQL?......Page 187
Using the EXECUTE IMMEDIATE Statement......Page 188
Building a Dynamic Query with Dynamic SQL......Page 190
Examples of Dynamic SQL for Records, Objects, and Collections......Page 191
Using Dynamic SQL with Bulk SQL......Page 192
Examples of Dynamic Bulk Binds......Page 193
Improving Performance of Dynamic SQL with Bind Variables......Page 194
Using Duplicate Placeholders with Dynamic SQL......Page 195
Using Database Links with Dynamic SQL......Page 196
Using Pragma RESTRICT_REFERENCES with Dynamic SQL......Page 197
Backward Compatibility of the USING Clause......Page 198
What Are Subprograms?......Page 199
Advantages of PL/SQL Subprograms......Page 200
Understanding PL/SQL Functions......Page 201
Using the RETURN Statement......Page 202
Declaring Nested PL/SQL Subprograms......Page 203
Actual Versus Formal Subprogram Parameters......Page 204
Specifying Subprogram Parameter Modes......Page 205
Overloading Subprogram Names......Page 207
Restrictions on Overloading......Page 209
How Subprogram Calls Are Resolved......Page 210
How Overloading Works with Inheritance......Page 211
Advantages of Invoker's Rights......Page 213
How External References Are Resolved in Invoker's Rights Subprograms......Page 214
Granting Privileges on Invoker's Rights Subprograms......Page 215
Using Database Links with Invoker's Rights Subprograms......Page 216
Using Object Types with Invoker's Rights Subprograms......Page 217
What Is a Recursive Subprogram?......Page 218
Calling External Subprograms......Page 219
Controlling Side Effects of PL/SQL Subprograms......Page 220
Understanding Subprogram Parameter Aliasing......Page 221
9 Using PL/SQL Packages......Page 223
What Goes In a PL/SQL Package?......Page 224
Advantages of PL/SQL Packages......Page 225
Understanding The Package Specification......Page 226
Referencing Package Contents......Page 227
Understanding The Package Body......Page 228
Some Examples of Package Features......Page 229
Overloading Packaged Subprograms......Page 233
About the DBMS_OUTPUT Package......Page 234
Guidelines for Writing Packages......Page 235
Separating Cursor Specs and Bodies with Packages......Page 236
Overview of PL/SQL Runtime Error Handling......Page 237
Advantages of PL/SQL Exceptions......Page 239
Summary of Predefined PL/SQL Exceptions......Page 240
Scope Rules for PL/SQL Exceptions......Page 242
Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT......Page 243
Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR......Page 244
Raising Exceptions with the RAISE Statement......Page 245
How PL/SQL Exceptions Propagate......Page 246
Handling Raised PL/SQL Exceptions......Page 248
Handling Exceptions Raised in Declarations......Page 249
Retrieving the Error Code and Error Message: SQLCODE and SQLERRM......Page 250
Continuing after an Exception Is Raised......Page 251
Retrying a Transaction......Page 252
Overview of PL/SQL Compile-Time Warnings......Page 253
Controlling PL/SQL Warning Messages......Page 254
Using the DBMS_WARNING Package......Page 255
When to Tune PL/SQL Code......Page 257
Avoiding CPU Overhead in PL/SQL Code......Page 258
Avoiding Memory Overhead in PL/SQL Code......Page 261
Using The Profiler API: Package DBMS_PROFILER......Page 262
Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)......Page 263
Using the FORALL Statement......Page 264
Retrieving Query Results into Collections with the BULK COLLECT Clause......Page 271
Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables......Page 275
Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint......Page 276
Restrictions on NOCOPY......Page 277
Compiling PL/SQL Code for Native Execution......Page 278
Overview of Table Functions......Page 284
Using Pipelined Table Functions for Transformations......Page 286
Returning Results from Table Functions......Page 287
Querying Table Functions......Page 288
Passing Data with Cursor Variables......Page 289
Performing DML Operations on Table Functions......Page 291
Handling Exceptions in Table Functions......Page 292
Overview of PL/SQL Object Types......Page 293
What Is an Object Type?......Page 294
Structure of an Object Type......Page 295
Components of an Object Type......Page 297
How Object Types Handle the SELF Parameter......Page 298
Overloading......Page 299
Defining Object Types......Page 301
Overview of PL/SQL Type Inheritance......Page 302
Declaring Objects......Page 303
How PL/SQL Treats Uninitialized Objects......Page 304
Defining Object Constructors......Page 305
Calling Object Constructors......Page 306
Calling Object Methods......Page 307
Sharing Objects through the REF Modifier......Page 308
Manipulating Objects through SQL......Page 309
Selecting Objects......Page 310
Inserting Objects......Page 313
Deleting Objects......Page 314
13 PL/SQL Language Elements......Page 315
Assignment Statement......Page 317
AUTONOMOUS_TRANSACTION Pragma......Page 320
Blocks......Page 322
CASE Statement......Page 328
CLOSE Statement......Page 330
Collection Methods......Page 331
Collections......Page 335
Comments......Page 340
COMMIT Statement......Page 341
Constants and Variables......Page 342
Cursor Attributes......Page 345
Cursor Variables......Page 348
Cursors......Page 352
DELETE Statement......Page 355
EXCEPTION_INIT Pragma......Page 358
Exceptions......Page 359
EXECUTE IMMEDIATE Statement......Page 361
EXIT Statement......Page 364
Expressions......Page 366
FETCH Statement......Page 374
FORALL Statement......Page 378
Functions......Page 381
GOTO Statement......Page 385
IF Statement......Page 386
INSERT Statement......Page 388
Literals......Page 390
LOCK TABLE Statement......Page 392
LOOP Statements......Page 393
MERGE Statement......Page 398
NULL Statement......Page 399
Object Types......Page 400
OPEN Statement......Page 407
OPEN-FOR Statement......Page 409
OPEN-FOR-USING Statement......Page 411
Packages......Page 413
Procedures......Page 418
RAISE Statement......Page 422
Records......Page 424
RESTRICT_REFERENCES Pragma......Page 427
RETURN Statement......Page 429
ROLLBACK Statement......Page 431
%ROWTYPE Attribute......Page 433
SAVEPOINT Statement......Page 435
SCN_TO_TIMESTAMP Function......Page 436
SELECT INTO Statement......Page 437
SERIALLY_REUSABLE Pragma......Page 441
SET TRANSACTION Statement......Page 443
SQL Cursor......Page 445
SQLCODE Function......Page 449
SQLERRM Function......Page 450
TIMESTAMP_TO_SCN Function......Page 452
%TYPE Attribute......Page 453
UPDATE Statement......Page 455
Exercises for the Reader......Page 459
Assigning Character Values......Page 461
Inserting Character Values......Page 462
Selecting Character Values......Page 463
Running the PL/SQL Wrap Utility......Page 465
Input and Output Files for the PL/SQL Wrap Utility......Page 466
Limitations of the PL/SQL Wrap Utility......Page 467
What Is Name Resolution?......Page 469
Examples of Qualified Names and Dot Notation......Page 470
Inner Capture......Page 471
Avoiding Inner Capture in DML Statements......Page 472
Calling Parameterless Subprograms and Methods......Page 473
Name Resolution for SQL Versus PL/SQL......Page 474
E PL/SQL Program Limits......Page 475
F List of PL/SQL Reserved Words......Page 477
How Can I Use Regular Expressions with PL/SQL?......Page 479
How Do I Specify Different Kinds of Names with PL/SQL's Dot Notation?......Page 480
How Do I Create a PL/SQL Procedure?......Page 481
How Do I Perform a Case-Insensitive Query?......Page 482
B......Page 483
C......Page 484
E......Page 485
I......Page 486
M......Page 487
P......Page 488
R......Page 489
S......Page 490
T......Page 491
Z......Page 492