Portfolio T., Russell J.
Table of contents :
Contents……Page 3
Send Us Your Comments……Page 19
Preface……Page 21
What’s New in PL/SQL?……Page 31
1 Overview of PL/SQL……Page 37
Block Structure……Page 38
Variables and Constants……Page 39
Cursors……Page 41
Cursor Variables……Page 42
Attributes……Page 43
Control Structures……Page 45
Modularity……Page 48
Data Abstraction……Page 50
Error Handling……Page 52
PL/SQL Architecture……Page 53
In the Oracle Database Server……Page 54
Support for SQL……Page 56
Better Performance……Page 57
Higher Productivity……Page 58
Tight Security……Page 59
2 Fundamentals of PL/SQL……Page 61
Lexical Units……Page 62
Delimiters……Page 63
Identifiers……Page 64
Literals……Page 67
Comments……Page 70
Declarations……Page 71
Using DEFAULT……Page 72
Using %TYPE……Page 73
Using %ROWTYPE……Page 74
Naming Conventions……Page 77
Name Resolution……Page 78
Scope and Visibility……Page 79
Boolean Values……Page 82
Expressions and Comparisons……Page 83
Operator Precedence……Page 84
Logical Operators……Page 85
Comparison Operators……Page 86
Boolean Expressions……Page 88
CASE Expressions……Page 91
Handling Null Values in Comparisons and Conditional Statements……Page 93
Built-In Functions……Page 95
3 PL/SQL Datatypes……Page 97
Predefined Datatypes……Page 98
Number Types……Page 99
Character Types……Page 101
National Character Types……Page 106
LOB Types……Page 109
Datetime and Interval Types……Page 111
Datetime and Interval Arithmetic……Page 116
User-Defined Subtypes……Page 117
Using Subtypes……Page 118
Implicit Conversion……Page 120
DATE Values……Page 122
RAW and LONG RAW Values……Page 123
4 PL/SQL Control Structures……Page 125
Conditional Control: IF and CASE Statements……Page 126
IF-THEN-ELSE Statement……Page 127
IF-THEN-ELSIF Statement……Page 128
CASE Statement……Page 129
Guidelines for PL/SQL Conditional Statements……Page 132
LOOP……Page 133
WHILE-LOOP……Page 135
FOR-LOOP……Page 137
GOTO Statement……Page 141
NULL Statement……Page 145
5 PL/SQL Collections and Records……Page 147
Understanding Nested Tables……Page 148
Nested Tables Versus Index-By Tables……Page 149
Varrays Versus Nested Tables……Page 150
Defining and Declaring Collections……Page 151
Declaring Collections……Page 153
Initializing and Referencing Collections……Page 154
Referencing Collection Elements……Page 156
Assigning and Comparing Collections……Page 157
Some Nested Table Examples……Page 159
Some Varray Examples……Page 162
Manipulating Individual Collection Elements……Page 164
Manipulating Local Collections……Page 166
Using Multilevel Collections……Page 167
Using Collection Methods……Page 169
Using the COUNT Collection Method……Page 170
Using the FIRST and LAST Collection Methods……Page 171
Using the PRIOR and NEXT Collection Methods……Page 172
Using the TRIM Collection Method……Page 173
Using the DELETE Collection Method……Page 174
Applying Methods to Collection Parameters……Page 175
Avoiding Collection Exceptions……Page 176
Reducing Loop Overhead for Collections with Bulk Binds……Page 178
How Do Bulk Binds Improve Performance?……Page 179
Using the FORALL Statement……Page 180
How FORALL Affects Rollbacks……Page 181
Counting Rows Affected by FORALL Iterations with the %BULK_ROWCOUNT Attribute……Page 182
Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute……Page 184
Restrictions on FORALL……Page 185
Retrieving Query Results into Collections with the BULK COLLECT Clause……Page 187
Example of a Bulk Fetch from a Cursor……Page 188
Retrieving DML Results into a Collection with the RETURNING INTO Clause……Page 189
Restrictions on BULK COLLECT……Page 190
Using Host Arrays with Bulk Binds……Page 191
Defining and Declaring Records……Page 192
Initializing Records……Page 194
Referencing Records……Page 195
Assigning Records……Page 197
Manipulating Records……Page 199
6 Interaction Between PL/SQL and Oracle……Page 203
SQL Functions……Page 204
SQL Pseudocolumns……Page 205
SQL Operators……Page 207
Overview of Explicit Cursors……Page 208
Overview of Implicit Cursors……Page 213
Separating Cursor Specs and Bodies with Packages……Page 214
Using Cursor FOR Loops……Page 215
Defining Aliases for Expression Values in a Cursor FOR Loop……Page 216
Passing Parameters to a Cursor FOR Loop……Page 217
Why Use Cursor Variables?……Page 218
Defining REF CURSOR Types……Page 219
Declaring Cursor Variables……Page 220
Controlling Cursor Variables……Page 221
Cursor Variable Example: Master Table and Details Tables……Page 227
Cursor Variable Example: Pro*C Program……Page 228
Cursor Variable Example: Manipulating Host Variables in SQL*Plus……Page 231
Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL……Page 232
Avoiding Errors with Cursor Variables……Page 233
Restrictions on Cursor Variables……Page 235
Overview of Explicit Cursor Attributes……Page 236
Overview of Implicit Cursor Attributes……Page 240
Restrictions on Cursor Expressions……Page 243
Example of Cursor Expressions……Page 244
Overview of Transaction Processing in PL/SQL……Page 245
Making Changes Permanent with COMMIT……Page 246
Undoing Changes with ROLLBACK……Page 247
Undoing Partial Changes with SAVEPOINT……Page 248
How Oracle Does Implicit Rollbacks……Page 249
Setting Transaction Properties with SET TRANSACTION……Page 250
Overriding Default Locking……Page 251
Advantages of Autonomous Transactions……Page 255
Defining Autonomous Transactions……Page 256
Controlling Autonomous Transactions……Page 259
Using Autonomous Triggers……Page 261
Calling Autonomous Functions from SQL……Page 263
Ensuring Backward Compatibility of PL/SQL Programs……Page 264
7 Handling PL/SQL Errors……Page 265
Overview of PL/SQL Error Handling……Page 266
Advantages of PL/SQL Exceptions……Page 267
Predefined PL/SQL Exceptions……Page 268
Scope Rules for PL/SQL Exceptions……Page 271
Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT……Page 272
Defining Your Own PL/SQL Error Messages: Procedure RAISE_APPLICATION_ERROR……Page 273
Redeclaring Predefined Exceptions……Page 274
Raising Exceptions with the RAISE Statement……Page 275
How PL/SQL Exceptions Propagate……Page 276
Reraising a PL/SQL Exception……Page 278
Handling Raised PL/SQL Exceptions……Page 279
Handling Exceptions Raised in Declarations……Page 280
Branching to or from an Exception Handler……Page 281
Retrieving the Error Code and Error Message: SQLCODE and SQLERRM……Page 282
Catching Unhandled Exceptions……Page 283
Continuing after an Exception Is Raised……Page 284
Retrying a Transaction……Page 285
Using Locator Variables to Identify Exception Locations……Page 286
8 PL/SQL Subprograms……Page 289
What Are Subprograms?……Page 290
Understanding PL/SQL Procedures……Page 291
Understanding PL/SQL Functions……Page 294
Using the RETURN Statement……Page 296
Controlling Side Effects of PL/SQL Subprograms……Page 297
Declaring PL/SQL Subprograms……Page 298
Packaging PL/SQL Subprograms Together……Page 299
Actual Versus Formal Subprogram Parameters……Page 300
Using Mixed Notation……Page 301
Using the OUT Mode……Page 302
Summary of Subprogram Parameter Modes……Page 304
Passing Large Data Structures with the NOCOPY Compiler Hint……Page 305
The Trade-Off for Better Performance with NOCOPY……Page 306
Using Default Values for Subprogram Parameters……Page 307
Understanding Subprogram Parameter Aliasing……Page 309
Overloading Subprogram Names……Page 311
Restrictions on Overloading……Page 312
How Subprogram Calls Are Resolved……Page 314
How Overloading Works with Inheritance……Page 316
Overview of Table Functions……Page 318
What Are Table Functions?……Page 319
What Are Pipelined Table Functions?……Page 320
Using Pipelined Table Functions for Transformations……Page 321
Writing a Pipelined Table Function……Page 322
Returning Results from Table Functions……Page 323
Querying Table Functions……Page 324
Optimizing Multiple Calls to Table Functions……Page 325
Passing Data with Cursor Variables……Page 326
Performing DML Operations on Table Functions……Page 327
Parallelizing Table Functions……Page 328
Parallel Execution of Table Functions……Page 329
Input Data Partitioning……Page 330
Parallel Execution of Leaf-level Table Functions……Page 332
How Table Functions Stream their Input Data……Page 333
Choosing Between Partitioning and Clustering for Parallel Execution……Page 334
Invoker Rights Versus Definer Rights……Page 335
Advantages of Invoker Rights……Page 336
Specifying the Privileges for a Subprogram with the AUTHID Clause……Page 338
How External References Are Resolved in Invoker-Rights Subprograms……Page 339
Granting Privileges on Invoker-Rights Subprograms……Page 341
Using Views and Database Triggers with Invoker-Rights Subprograms……Page 343
Using Object Types with Invoker-Rights Subprograms……Page 344
What Is a Recursive Subprogram?……Page 346
Using Mutual Recursion……Page 349
Recursion Versus Iteration……Page 350
Calling External Subprograms……Page 351
Creating Dynamic Web Pages with PL/SQL Server Pages……Page 352
9 PL/SQL Packages……Page 353
What Is a PL/SQL Package?……Page 354
Example of a PL/SQL Package……Page 356
Advantages of PL/SQL Packages……Page 357
Understanding The Package Spec……Page 358
Referencing Package Contents……Page 359
Understanding The Package Body……Page 360
Some Examples of Package Features……Page 361
Private Versus Public Items in Packages……Page 366
How Package STANDARD Defines the PL/SQL Environment……Page 367
About the DBMS_OUTPUT Package……Page 368
About the UTL_FILE Package……Page 369
Guidelines for Writing Packages……Page 370
10 PL/SQL Object Types……Page 371
The Role of Abstraction……Page 372
What Is an Object Type?……Page 373
Structure of an Object Type……Page 375
Attributes……Page 377
Methods……Page 378
Changing Attributes and Methods of an Existing Object Type (Type Evolution)……Page 382
Defining Object Types……Page 383
Overview of PL/SQL Type Inheritance……Page 384
Object Type Example: Stack……Page 386
Object Type Example: Ticket_Booth……Page 388
Object Type Example: Bank_Account……Page 390
Object Type Example: Rational Numbers……Page 392
Declaring and Initializing Objects……Page 394
Initializing Objects……Page 395
How PL/SQL Treats Uninitialized Objects……Page 396
Accessing Attributes……Page 397
Calling Methods……Page 398
Sharing Objects……Page 399
Forward Type Definitions……Page 401
Manipulating Objects……Page 402
Selecting Objects……Page 403
Inserting Objects……Page 408
Deleting Objects……Page 409
11 Native Dynamic SQL……Page 411
The Need for Dynamic SQL……Page 412
Using the EXECUTE IMMEDIATE Statement……Page 413
Some Examples of Dynamic SQL……Page 415
Backward Compatibility of the USING Clause……Page 416
Specifying Parameter Modes……Page 417
Opening the Cursor Variable……Page 418
Closing the Cursor Variable……Page 419
Examples of Dynamic SQL for Records, Objects, and Collections……Page 420
Syntax for Dynamic Bulk Binds……Page 422
Examples of Dynamic Bulk Binds……Page 423
Making Procedures Work on Arbitrarily Named Schema Objects……Page 425
Using Duplicate Placeholders……Page 426
Passing Nulls……Page 427
Using Invoker Rights……Page 428
Avoiding Deadlocks……Page 429
12 Tuning PL/SQL Applications……Page 431
Reasons for PL/SQL Performance Problems……Page 432
The Profiler API: Package DBMS_PROFILER……Page 438
The Trace API: Package DBMS_TRACE……Page 439
Improving PL/SQL Performance with Native Dynamic SQL……Page 440
Improving PL/SQL Performance with Bulk Binds……Page 441
Improving PL/SQL Performance with the RETURNING Clause……Page 442
Improving PL/SQL Performance with Object Types and Collections……Page 443
Compiling PL/SQL Code for Native Execution……Page 444
13 PL/SQL Language Elements……Page 447
Assignment Statement……Page 450
AUTONOMOUS_TRANSACTION Pragma……Page 454
Blocks……Page 457
CASE Statement……Page 464
CLOSE Statement……Page 467
Collection Methods……Page 469
Collections……Page 474
Comments……Page 481
COMMIT Statement……Page 482
Constants and Variables……Page 484
Cursor Attributes……Page 488
Cursor Variables……Page 493
Cursors……Page 499
DELETE Statement……Page 503
EXCEPTION_INIT Pragma……Page 507
Exceptions……Page 509
EXECUTE IMMEDIATE Statement……Page 512
EXIT Statement……Page 516
Expressions……Page 518
FETCH Statement……Page 529
FORALL Statement……Page 534
Functions……Page 536
GOTO Statement……Page 543
IF Statement……Page 545
INSERT Statement……Page 548
Literals……Page 551
LOCK TABLE Statement……Page 554
LOOP Statements……Page 556
MERGE Statement……Page 563
NULL Statement……Page 565
Object Types……Page 566
OPEN Statement……Page 575
OPEN-FOR Statement……Page 577
OPEN-FOR-USING Statement……Page 580
Packages……Page 583
Procedures……Page 589
RAISE Statement……Page 594
Records……Page 596
RESTRICT_REFERENCES Pragma……Page 600
RETURN Statement……Page 603
ROLLBACK Statement……Page 605
%ROWTYPE Attribute……Page 607
SAVEPOINT Statement……Page 609
SELECT INTO Statement……Page 610
SERIALLY_REUSABLE Pragma……Page 615
SET TRANSACTION Statement……Page 617
SQL Cursor……Page 619
SQLCODE Function……Page 622
SQLERRM Function……Page 624
%TYPE Attribute……Page 626
UPDATE Statement……Page 628
A Sample PL/SQL Programs……Page 633
Running the Programs……Page 634
PL/SQL Block……Page 635
Input Table……Page 636
Output Table……Page 637
PL/SQL Block……Page 638
Sample 4. Batch Transaction Processing……Page 639
PL/SQL Block……Page 640
Output Tables……Page 642
PL/SQL Block in a C Program……Page 643
Interactive Session……Page 645
Output Tables……Page 646
Stored Procedure……Page 647
Interactive Session……Page 650
B CHAR versus VARCHAR2 Semantics……Page 651
Comparing Character Values……Page 652
Selecting Character Values……Page 654
Advantages of Wrapping PL/SQL Procedures……Page 657
Running the Wrap Utility……Page 658
Input and Output Files for the Wrap Utility……Page 659
Guidelines……Page 660
D PL/SQL Name Resolution……Page 661
What Is Name Resolution?……Page 662
Various Forms of References……Page 663
Name-Resolution Algorithm……Page 664
Finding the Basis……Page 665
Inner Capture……Page 667
Same-Scope Capture……Page 668
Accessing Attributes and Methods……Page 669
Calling Subprograms and Methods……Page 670
Name Resolution for SQL Versus PL/SQL……Page 672
E PL/SQL Program Limits……Page 673
F PL/SQL Reserved Words……Page 677
Index……Page 679
Reviews
There are no reviews yet.