Oracle Database PLSQL Users Guide and Reference 10g Release 2 (10.2) b14261

Contents......Page 3
Send Us Your Comments......Page 17
Documentation Accessibility......Page 19
Structure......Page 20
PL/SQL Sample Programs......Page 21
Conventions......Page 22
New Features in PL/SQL for Oracle Database 10g Release 2 (10.2)......Page 25
New Features in PL/SQL for Oracle Database 10g Release 1 (10.1)......Page 26
Advantages of PL/SQL......Page 29
Better Performance......Page 30
Tight Security......Page 31
Understanding PL/SQL Block Structure......Page 32
Declaring Variables......Page 33
Assigning Values to a Variable......Page 34
Declaring Constants......Page 35
%TYPE......Page 36
Understanding PL/SQL Control Structures......Page 37
Iterative Control......Page 38
Understanding Conditional Compilation......Page 40
Packages: APIs Written in PL/SQL......Page 41
Inputting and Outputting Data with PL/SQL......Page 43
Records......Page 44
Object Types......Page 45
PL/SQL Architecture......Page 46
Stored Subprograms......Page 47
Database Triggers......Page 48
In Oracle Tools......Page 49
Character Sets and Lexical Units......Page 51
Delimiters......Page 52
Identifiers......Page 53
Literals......Page 54
Numeric Literals......Page 55
String Literals......Page 56
Single-Line Comments......Page 57
Declarations......Page 58
Using NOT NULL......Page 59
Using the %TYPE Attribute......Page 60
Using the %ROWTYPE Attribute......Page 61
Using Aliases......Page 62
PL/SQL Naming Conventions......Page 63
Scope and Visibility of PL/SQL Identifiers......Page 65
Assigning Values to Variables......Page 68
PL/SQL Expressions and Comparisons......Page 69
Logical Operators......Page 70
Short-Circuit Evaluation......Page 71
Relational Operators......Page 72
Concatenation Operator......Page 73
BOOLEAN Character Expressions......Page 74
Guidelines for PL/SQL BOOLEAN Expressions......Page 75
Simple CASE expression......Page 76
Handling Null Values in Comparisons and Conditional Statements......Page 77
NULLs and the NOT Operator......Page 78
Conditional Compilation......Page 80
Using Conditional Compilation Inquiry Directives......Page 81
Using Predefined Inquiry Directives With Conditional Compilation......Page 82
Using Static Expressions with Conditional Compilation......Page 83
Setting the PLSQL_CCFLAGS Initialization Parameter......Page 84
Using Conditional Compilation to Specify Code for Database Versions......Page 85
Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text......Page 86
Conditional Compilation Restrictions......Page 87
Summary of PL/SQL Built-In Functions......Page 88
Overview of Predefined PL/SQL Datatypes......Page 91
BINARY_FLOAT and BINARY_DOUBLE Datatypes......Page 92
NUMBER Datatype......Page 93
CHAR Datatype......Page 94
LONG and LONG RAW Datatypes......Page 95
ROWID and UROWID Datatype......Page 96
VARCHAR2 Datatype......Page 98
NCHAR Datatype......Page 99
PL/SQL LOB Types......Page 100
CLOB Datatype......Page 101
PL/SQL Date, Time, and Interval Types......Page 102
TIMESTAMP Datatype......Page 103
TIMESTAMP WITH TIME ZONE Datatype......Page 104
TIMESTAMP WITH LOCAL TIME ZONE Datatype......Page 105
INTERVAL DAY TO SECOND Datatype......Page 106
Overview of PL/SQL Subtypes......Page 107
Using Subtypes......Page 108
Type Compatibility With Subtypes......Page 109
Constraints and Default Values With Subtypes......Page 110
Implicit Conversion......Page 111
Assigning Character Values......Page 113
Comparing Character Values......Page 114
Inserting Character Values......Page 115
Selecting Character Values......Page 116
Overview of PL/SQL Control Structures......Page 117
Using the IF-THEN-ELSE Statement......Page 118
Using the IF-THEN-ELSIF Statement......Page 119
Using CASE Statements......Page 120
Searched CASE Statement......Page 121
Guidelines for PL/SQL Conditional Statements......Page 122
Using the EXIT Statement......Page 123
Labeling a PL/SQL Loop......Page 124
Using the FOR-LOOP Statement......Page 125
How PL/SQL Loops Iterate......Page 126
Scope of the Loop Counter Variable......Page 127
Using the EXIT Statement in a FOR Loop......Page 128
Using the GOTO Statement......Page 129
Using the NULL Statement......Page 131
What are PL/SQL Collections and Records?......Page 133
Understanding Nested Tables......Page 134
Understanding Associative Arrays (Index-By Tables)......Page 135
How Globalization Settings Affect VARCHAR2 Keys for Associative Arrays......Page 136
Choosing Between Nested Tables and Varrays......Page 137
Defining Collection Types and Declaring Collection Variables......Page 138
Declaring PL/SQL Collection Variables......Page 139
Initializing and Referencing Collections......Page 142
Referencing Collection Elements......Page 143
Assigning Collections......Page 144
Comparing Collections......Page 148
Using Multilevel Collections......Page 150
Checking If a Collection Element Exists (EXISTS Method)......Page 151
Checking the Maximum Size of a Collection (LIMIT Method)......Page 152
Finding the First or Last Collection Element (FIRST and LAST Methods)......Page 153
Looping Through Collection Elements (PRIOR and NEXT Methods)......Page 154
Increasing the Size of a Collection (EXTEND Method)......Page 155
Decreasing the Size of a Collection (TRIM Method)......Page 156
Deleting Collection Elements (DELETE Method)......Page 157
Applying Methods to Collection Parameters......Page 158
Avoiding Collection Exceptions......Page 159
Defining and Declaring Records......Page 161
Using Records as Procedure Parameters and Function Return Values......Page 163
Assigning Values to Records......Page 164
Inserting PL/SQL Records into the Database......Page 165
Updating the Database with PL/SQL Record Values......Page 166
Restrictions on Record Inserts and Updates......Page 167
Querying Data into Collections of Records......Page 168
Data Manipulation......Page 169
SQL Pseudocolumns......Page 171
Implicit Cursors......Page 174
Attributes of Implicit Cursors......Page 175
Declaring a Cursor......Page 176
Fetching with a Cursor......Page 177
Fetching Bulk Data with a Cursor......Page 179
Attributes of Explicit Cursors......Page 180
Selecting At Most One Row: SELECT INTO Statement......Page 182
Performing Complicated Query Processing: Explicit Cursors......Page 183
Querying Data with PL/SQL: Explicit Cursor FOR Loops......Page 184
Using Subqueries......Page 185
Using Correlated Subqueries......Page 186
Writing Maintainable PL/SQL Queries......Page 187
Declaring REF CURSOR Types and Cursor Variables......Page 188
Opening a Cursor Variable......Page 190
Using a Cursor Variable as a Host Variable......Page 192
Fetching from a Cursor Variable......Page 193
Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL......Page 194
Restrictions on Cursor Variables......Page 195
Restrictions on Cursor Expressions......Page 196
Constructing REF CURSORs with Cursor Subqueries......Page 197
Using COMMIT in PL/SQL......Page 198
Using SAVEPOINT in PL/SQL......Page 199
How Oracle Does Implicit Rollbacks......Page 200
Setting Transaction Properties with SET TRANSACTION......Page 201
Overriding Default Locking......Page 202
Defining Autonomous Transactions......Page 205
Controlling Autonomous Transactions......Page 207
Using Autonomous Triggers......Page 209
Calling Autonomous Functions from SQL......Page 210
Why Use Dynamic SQL with PL/SQL?......Page 211
Using the EXECUTE IMMEDIATE Statement in PL/SQL......Page 212
Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings......Page 214
Using Dynamic SQL with Bulk SQL......Page 215
Examples of Dynamic Bulk Binds......Page 216
When to Use or Omit the Semicolon with Dynamic SQL......Page 217
Passing Schema Object Names As Parameters......Page 218
Using Cursor Attributes with Dynamic SQL......Page 219
Using Database Links with Dynamic SQL......Page 220
Avoiding Deadlocks with Dynamic SQL......Page 221
Using Dynamic SQL With PL/SQL Records and Collections......Page 222
What Are Subprograms?......Page 223
Advantages of PL/SQL Subprograms......Page 224
Understanding PL/SQL Procedures......Page 225
Understanding PL/SQL Functions......Page 226
Declaring Nested PL/SQL Subprograms......Page 227
Actual Versus Formal Subprogram Parameters......Page 228
Using the IN Mode......Page 229
Using the IN OUT Mode......Page 230
Using Default Values for Subprogram Parameters......Page 231
Guidelines for Overloading with Numeric Types......Page 232
Restrictions on Overloading......Page 233
How Subprogram Calls Are Resolved......Page 234
How Overloading Works with Inheritance......Page 236
Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)......Page 237
Who Is the Current User During Subprogram Execution?......Page 238
Overriding Default Name Resolution in Invoker's Rights Subprograms......Page 239
Granting Privileges on an Invoker's Rights Subprogram: Example......Page 240
Using Database Links with Invoker's Rights Subprograms......Page 241
Using Object Types with Invoker's Rights Subprograms......Page 242
What Is a Recursive Subprogram?......Page 243
Calling External Subprograms......Page 244
Controlling Side Effects of PL/SQL Subprograms......Page 245
Understanding Subprogram Parameter Aliasing......Page 246
What Is a PL/SQL Package?......Page 249
What Goes In a PL/SQL Package?......Page 250
Understanding The Package Specification......Page 251
Referencing Package Contents......Page 252
Understanding The Package Body......Page 253
Some Examples of Package Features......Page 254
How Package STANDARD Defines the PL/SQL Environment......Page 256
About the DBMS_OUTPUT Package......Page 257
Guidelines for Writing Packages......Page 258
Separating Cursor Specs and Bodies with Packages......Page 259
Overview of PL/SQL Runtime Error Handling......Page 261
Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions......Page 262
Advantages of PL/SQL Exceptions......Page 263
Summary of Predefined PL/SQL Exceptions......Page 264
Defining Your Own PL/SQL Exceptions......Page 265
Scope Rules for PL/SQL Exceptions......Page 266
Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR......Page 267
Redeclaring Predefined Exceptions......Page 268
How PL/SQL Exceptions Propagate......Page 269
Reraising a PL/SQL Exception......Page 271
Handling Raised PL/SQL Exceptions......Page 272
Branching to or from an Exception Handler......Page 273
Retrieving the Error Code and Error Message: SQLCODE and SQLERRM......Page 274
Continuing after an Exception Is Raised......Page 275
Retrying a Transaction......Page 276
PL/SQL Warning Categories......Page 277
Controlling PL/SQL Warning Messages......Page 278
Using the DBMS_WARNING Package......Page 279
Initialization Parameters for PL/SQL Compilation......Page 281
When to Tune PL/SQL Code......Page 282
Make Function Calls as Efficient as Possible......Page 283
Make Loops as Efficient as Possible......Page 284
Minimize Datatype Conversions......Page 285
Group Related Subprograms into Packages......Page 286
Using The Profiler API: Package DBMS_PROFILER......Page 287
Reducing Loop Overhead for DML Statements and Queries with Bulk SQL......Page 288
Using the FORALL Statement......Page 289
Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute......Page 292
Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute......Page 294
Retrieving Query Results into Collections with the BULK COLLECT Clause......Page 295
Examples of Bulk-Fetching from a Cursor......Page 297
Retrieving DML Results into a Collection with the RETURNING INTO Clause......Page 298
Using Host Arrays with Bulk Binds......Page 299
Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables......Page 300
Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint......Page 301
Compiling PL/SQL Code for Native Execution......Page 302
Determining Whether to Use PL/SQL Native Compilation......Page 303
Dependencies, Invalidation and Revalidation......Page 304
Setting up Initialization Parameters for PL/SQL Native Compilation......Page 305
PLSQL_CODE_TYPE Initialization Parameter......Page 306
Setting Up and Testing PL/SQL Native Compilation......Page 307
Setting Up a New Database for PL/SQL Native Compilation......Page 308
Modifying the Entire Database for PL/SQL Native or Interpreted Compilation......Page 309
Setting Up Transformations with Pipelined Functions......Page 311
Writing a Pipelined Table Function......Page 312
Using Pipelined Table Functions for Transformations......Page 313
Returning Results from Pipelined Table Functions......Page 314
Fetching from the Results of Pipelined Table Functions......Page 315
Passing Data with Cursor Variables......Page 316
Performing DML Operations on Pipelined Table Functions......Page 318
Handling Exceptions in Pipelined Table Functions......Page 319
Declaring and Initializing Objects in PL/SQL......Page 321
Declaring Objects in a PL/SQL Block......Page 322
How PL/SQL Treats Uninitialized Objects......Page 323
Calling Object Constructors and Methods......Page 324
Updating and Deleting Objects......Page 325
Defining SQL Types Equivalent to PL/SQL Collection Types......Page 326
Manipulating Individual Collection Elements with SQL......Page 328
Using PL/SQL Collections with SQL Object Types......Page 329
Using Dynamic SQL With Objects......Page 331
13 PL/SQL Language Elements......Page 333
Assignment Statement......Page 335
AUTONOMOUS_TRANSACTION Pragma......Page 338
Block Declaration......Page 340
CASE Statement......Page 346
CLOSE Statement......Page 348
Collection Definition......Page 349
Collection Methods......Page 352
Comments......Page 355
COMMIT Statement......Page 356
Constant and Variable Declaration......Page 357
Cursor Attributes......Page 360
Cursor Variables......Page 362
Cursor Declaration......Page 365
DELETE Statement......Page 368
EXCEPTION_INIT Pragma......Page 370
Exception Definition......Page 371
EXECUTE IMMEDIATE Statement......Page 373
EXIT Statement......Page 376
Expression Definition......Page 377
FETCH Statement......Page 385
FORALL Statement......Page 388
Function Declaration......Page 391
GOTO Statement......Page 395
IF Statement......Page 396
INSERT Statement......Page 398
Literal Declaration......Page 400
LOCK TABLE Statement......Page 403
LOOP Statements......Page 404
MERGE Statement......Page 408
NULL Statement......Page 409
Object Type Declaration......Page 410
OPEN Statement......Page 412
OPEN-FOR Statement......Page 414
Package Declaration......Page 417
Procedure Declaration......Page 422
RAISE Statement......Page 426
Record Definition......Page 427
RESTRICT_REFERENCES Pragma......Page 430
RETURN Statement......Page 432
RETURNING INTO Clause......Page 433
ROLLBACK Statement......Page 435
%ROWTYPE Attribute......Page 436
SAVEPOINT Statement......Page 438
SELECT INTO Statement......Page 439
SERIALLY_REUSABLE Pragma......Page 443
SET TRANSACTION Statement......Page 445
SQL Cursor......Page 447
SQLCODE Function......Page 449
SQLERRM Function......Page 450
%TYPE Attribute......Page 451
UPDATE Statement......Page 453
Tips When Obfuscating PL/SQL Units......Page 457
Obfuscating PL/SQL Code With the wrap Utility......Page 458
Running the wrap Utility......Page 459
Using the DBMS_DDL create_wrapped Procedure......Page 460
What Is Name Resolution?......Page 463
Examples of Qualified Names and Dot Notation......Page 464
Differences in Name Resolution Between PL/SQL and SQL......Page 465
Inner Capture......Page 466
Avoiding Inner Capture in DML Statements......Page 467
References to Attributes and Methods......Page 468
References to Row Expressions......Page 469
C PL/SQL Program Limits......Page 471
D PL/SQL Reserved Words and Keywords......Page 473
A......Page 475
B......Page 476
C......Page 477
D......Page 479
E......Page 480
I......Page 482
L......Page 483
N......Page 484
P......Page 486
R......Page 489
S......Page 490
T......Page 493
V......Page 494
Z......Page 495