_2015991714548.jpg)
Oracle Pl/SQL Programming （6TH）
[Book Description]
This book is the definitive reference on PL/SQL, considered throughout the database community to be the best Oracle programming book available. Like its predecessors, this 6th edition of Oracle PL/SQL Programming covers language fundamentals, advanced coding techniques, and best practices for using Oracle's powerful procedural language. Thoroughly updated for Oracle Database 12c, this edition reveals new PL/SQL features and provides extensive code samples, ranging from simple examples to complex and complete applications, in the book and on the companion website. It's an indispensable reference for both novices and experienced Oracle programmers.
[Table of Contents]
Preface xxv
Part I Programming in PL/SQL
1 Introduction to PL/SQL 3 (18)
What Is PL/SQL? 3 (1)
The Origins of PL/SQL 4 (3)
The Early Years of PL/SQL 4 (1)
Improved Application Portability 5 (1)
Improved Execution Authority and 5 (1)
Transaction Integrity
Humble Beginnings, Steady Improvement 6 (1)
So This Is PL/SQL 7 (4)
Integration with SQL 7 (1)
Control and Conditional Logic 8 (1)
When Things Go Wrong 9 (2)
About PL/SQL Versions 11 (3)
Oracle Database 12c New PL/SQL Features 12 (2)
Resources for PL/SQL Developers 14 (3)
The O'Reilly PL/SQL Series 15 (1)
PL/SQL on the Internet 16 (1)
Some Words of Advice 17 (4)
Don't Be in Such a Hurry! 17 (1)
Don't Be Afraid to Ask for Help 18 (1)
Take a Creative, Even Radical Approach 19 (2)
2 Creating and Running PL/SQL Code 21 (32)
Navigating the Database 21 (1)
Creating and Editing Source Code 22 (1)
SQL*Plus 23 (14)
Starting Up SQL*Plus 24 (2)
Running a SQL Statement 26 (1)
Running a PL/SQL Program 27 (2)
Running a Script 29 (1)
What Is the "Current Directory"? 30 (1)
Other SQL*Plus Tasks 31 (5)
Error Handling in SQL*Plus 36 (1)
Why You Will Love and Hate SQL*Plus 36 (1)
Performing Essential PL/SQL Tasks 37 (7)
Creating a Stored Program 37 (4)
Executing a Stored Program 41 (1)
Showing Stored Programs 41 (1)
Managing Grants and Synonyms for Stored 42 (1)
Programs
Dropping a Stored Program 43 (1)
Hiding the Source Code of a Stored Program 44 (1)
Editing Environments for PL/SQL 44 (1)
Calling PL/SQL from Other Languages 45 (8)
C: Using Oracle's Precompiler (Pro*C) 46 (1)
Java: Using JDBC 47 (1)
Perl: Using Perl DBI and DBD::Oracle 48 (1)
PHP: Using Oracle Extensions 49 (2)
PL/SQL Server Pages 51 (1)
And Where Else? 52 (1)
3 Language Fundamentals 53 (30)
PL/SQL Block Structure 53 (12)
Anonymous Blocks 55 (2)
Named Blocks 57 (1)
Nested Blocks 57 (1)
Scope 58 (1)
Qualify All References to Variables and 59 (3)
Columns in SQL Statements
Visibility 62 (3)
The PL/SQL Character Set 65 (2)
Identifiers 67 (3)
Reserved Words 68 (2)
Whitespace and Keywords 70 (1)
Literals 70 (4)
NULLs 71 (1)
Embedding Single Quotes Inside a Literal 72 (1)
String
Numeric Literals 73 (1)
Boolean Literals 74 (1)
The Semicolon Delimiter 74 (1)
Comments 75 (1)
Single-Line Comment Syntax 75 (1)
Multiline Comment Syntax 76 (1)
The PRAGMA Keyword 76 (1)
Labels 77 (6)
Part II PL/SQL Program Structure
4 Conditional and Sequential Control 83 (22)
IF Statements 83 (10)
The IF-THEN Combination 84 (2)
The IF-THEN-ELSE Combination 86 (1)
The IF-THEN-ELSIF Combination 87 (2)
Avoiding IF Syntax Gotchas 89 (1)
Nested IF Statements 90 (1)
Short-Circuit Evaluation 91 (2)
CASE Statements and Expressions 93 (7)
Simple CASE Statements 93 (2)
Searched CASE Statements 95 (3)
Nested CASE Statements 98 (1)
CASE Expressions 98 (2)
The GOTO Statement 100 (1)
The NULL Statement 101 (4)
Improving Program Readability 101 (1)
Using NULL After a Label 102 (3)
5 Iterative Processing with Loops 105 (24)
Loop Basics 105 (3)
Examples of Different Loops 106 (1)
Structure of PL/SQL Loops 107 (1)
The Simple Loop 108 (4)
Terminating a Simple Loop: EXIT and EXIT 109 (1)
WHEN
Emulating a REPEAT UNTIL Loop 110 (1)
The Intentionally Infinite Loop 111 (1)
The WHILE Loop 112 (2)
The Numeric FOR Loop 114 (3)
Rules for Numeric FOR Loops 114 (1)
Examples of Numeric FOR Loops 115 (1)
Handling Nontrivial Increments 116 (1)
The Cursor FOR Loop 117 (2)
Example of Cursor FOR Loops 118 (1)
Loop Labels 119 (1)
The CONTINUE Statement 120 (3)
Tips for Iterative Processing 123 (6)
Use Understandable Names for Loop Indexes 123 (1)
The Proper Way to Say Goodbye 124 (2)
Obtaining Information About FOR Loop 126 (1)
Execution
SQL Statement as Loop 126 (3)
6 Exception Handlers 129 (44)
Exception-Handling Concepts and Terminology 129 (3)
Defining Exceptions 132 (8)
Declaring Named Exceptions 132 (1)
Associating Exception Names with Error 133 (3)
Codes
About Named System Exceptions 136 (3)
Scope of an Exception 139 (1)
Raising Exceptions 140 (3)
The RAISE Statement 140 (1)
Using RAISE_APPLICATION_ERROR 141 (2)
Handling Exceptions 143 (14)
Built-in Error Functions 144 (5)
Combining Multiple Exceptions in a Single 149 (1)
Handler
Unhandled Exceptions 149 (1)
Propagation of Unhandled Exceptions 150 (3)
Continuing Past Exceptions 153 (2)
Writing WHEN OTHERS Handling Code 155 (2)
Building an Effective Error Management 157 (12)
Architecture
Decide on Your Error Management Strategy 158 (1)
Standardize Handling of Different Types 159 (3)
of Exceptions
Organize Use of Application-Specific 162 (1)
Error Codes
Use Standardized Error Management Programs 163 (2)
Work with Your Own Exception "Objects" 165 (2)
Create Standard Templates for Common 167 (2)
Error Handling
Making the Most of PL/SQL Error Management 169 (4)
Part III PUSQL Program Data
7 Working with Program Data 173 (26)
Naming Your Program Data 173 (2)
Overview of PL/SQL Datatypes 175 (6)
Character Data 176 (1)
Numbers 177 (1)
Dates, Timestamps, and Intervals 178 (1)
Booleans 178 (1)
Binary Data 179 (1)
ROWIDs 179 (1)
REF CURSORs 179 (1)
Internet Datatypes 180 (1)
"Any" Datatypes 180 (1)
User-Defined Datatypes 181 (1)
Declaring Program Data 181 (7)
Declaring a Variable 181 (1)
Declaring Constants 182 (1)
The NOT NULL Clause 183 (1)
Anchored Declarations 183 (2)
Anchoring to Cursors and Tables 185 (1)
Benefits of Anchored Declarations 186 (2)
Anchoring to NOT NULL Datatypes 188 (1)
Programmer-Defined Subtypes 188 (1)
Conversion Between Datatypes 189 (10)
Implicit Data Conversion 189 (3)
Explicit Datatype Conversion 192 (7)
8 Strings 199 (42)
String Datatypes 199 (4)
The VARCHAR2 Datatype 200 (1)
The CHAR Datatype 201 (1)
String Subtypes 202 (1)
Working with Strings 203 (28)
Specifying String Constants 203 (2)
Using Nonprintable Characters 205 (1)
Concatenating Strings 206 (1)
Dealing with Case 207 (3)
Traditional Searching, Extracting, and 210 (3)
Replacing
Padding 213 (2)
Trimming 215 (1)
Regular Expression Searching, Extracting, 216 (11)
and Replacing
Working with Empty Strings 227 (2)
Mixing CHAR and VARCHAR2 Values 229 (2)
String Function Quick Reference 231 (10)
9 Numbers 241 (36)
Numeric Datatypes 241 (16)
The NUMBER Type 242 (5)
The PLS_INTEGER Type 247 (1)
The BINARY_INTEGER Type 248 (1)
The SIMPLE_INTEGER Type 249 (2)
The BINARY_FLOAT and BINARY_DOUBLE Types 251 (5)
The SIMPLE_FLOAT and SIMPLE_DOUBLE Types 256 (1)
Numeric Subtypes 256 (1)
Number Conversions 257 (13)
The TO_NUMBER Function 258 (3)
The TO_CHAR Function 261 (6)
The CAST Function 267 (1)
Implicit Conversions 268 (2)
Numeric Operators 270 (1)
Numeric Functions 271 (6)
Rounding and Truncation Functions 271 (1)
Trigonometric Functions 272 (1)
Numeric Function Quick Reference 272 (5)
10 Dates and Timestamps 277 (46)
Datetime Datatypes 278 (4)
Declaring Datetime Variables 280 (1)
Choosing a Datetime Datatype 281 (1)
Getting the Current Date and Time 282 (2)
Interval Datatypes 284 (5)
Declaring INTERVAL Variables 286 (1)
When to Use INTERVALs 287 (2)
Datetime Conversions 289 (13)
From Strings to Datetimes 289 (3)
From Datetimes to Strings 292 (3)
Working with Time Zones 295 (3)
Requiring a Format Mask to Match Exactly 298 (1)
Easing Up on Exact Matches 299 (1)
Interpreting Two-Digit Years in a Sliding 299 (2)
Window
Converting Time Zones to Character Strings 301 (1)
Padding Output with Fill Mode 302 (1)
Date and Timestamp Literals 302 (2)
Interval Conversions 304 (3)
Converting from Numbers to Intervals 304 (1)
Converting Strings to Intervals 305 (1)
Formatting Intervals for Display 306 (1)
Interval Literals 307 (1)
CAST and EXTRACT 308 (3)
The CAST Function 308 (2)
The EXTRACT Function 310 (1)
Datetime Arithmetic 311 (8)
Date Arithmetic with Intervals and 311 (1)
Datetimes
Date Arithmetic with DATE Datatypes 312 (1)
Computing the Interval Between Two 313 (3)
Datetimes
Mixing DATEs and TIMESTAMPs 316 (1)
Adding and Subtracting Intervals 317 (1)
Multiplying and Dividing Intervals 317 (1)
Using Unconstrained INTERVAL Types 318 (1)
Date/Time Function Quick Reference 319 (4)
11 Records 323 (18)
Records in PL/SQL 323 (18)
Benefits of Using Records 324 (2)
Declaring Records 326 (1)
Programmer-Defined Records 327 (3)
Working with Records 330 (7)
Comparing Records 337 (1)
Trigger Pseudorecords 338 (3)
12 Collections 341 (74)
Collections Overview 342 (14)
Collections Concepts and Terminology 343 (2)
Types of Collections 345 (1)
Collection Examples 345 (4)
Where You Can Use Collections 349 (5)
Choosing a Collection Type 354 (2)
Collection Methods (Built-ins) 356 (9)
The COUNT Method 357 (1)
The DELETE Method 358 (1)
The EXISTS Method 359 (1)
The EXTEND Method 360 (1)
The FIRST and LAST Methods 361 (1)
The LIMIT Method 362 (1)
The PRIOR and NEXT Methods 362 (1)
The TRIM Method 363 (2)
Working with Collections 365 (41)
Declaring Collection Types 365 (4)
Declaring and Initializing Collection 369 (5)
Variables
Populating Collections with Data 374 (5)
Accessing Data Inside a Collection 379 (1)
Using String-Indexed Collections 380 (5)
Collections of Complex Datatypes 385 (4)
Multilevel Collections 389 (9)
Working with Collections in SQL 398 (8)
Nested Table Multiset Operations 406 (6)
Testing Equality and Membership of Nested 408 (1)
Tables
Checking for Membership of an Element in 409 (1)
a Nested Table
Performing High-Level Set Operations 409 (2)
Handling Duplicates in a Nested Table 411 (1)
Maintaining Schema-Level Collections 412 (3)
Necessary Privileges 412 (1)
Collections and the Data Dictionary 413 (2)
13 Miscellaneous Datatypes 415 (46)
The BOOLEAN Datatype 415 (2)
The RAW Datatype 417 (1)
The UROWID and ROWID Datatypes 417 (3)
Getting ROWIDs 418 (1)
Using ROWIDs 419 (1)
The LOB Datatypes 420 (2)
Working with LOBs 422 (25)
Understanding LOB Locators 423 (2)
Empty Versus NULL LOBs 425 (2)
Writing into a LOB 427 (3)
Reading from a LOB 430 (1)
BFILEs Are Different 431 (5)
SecureFiles Versus BasicFiles 436 (3)
Temporary LOBs 439 (3)
Native LOB Operations 442 (5)
LOB Conversion Functions 447 (1)
Predefined Object Types 447 (14)
The XMLType Type 448 (3)
The URI Types 451 (2)
The Any Types 453 (8)
Part IV SQL in PL/SQL
14 DML and Transaction Management 461 (24)
DML in PL/SQL 462 (11)
A Quick Introduction to DML 462 (4)
Cursor Attributes for DML Operations 466 (1)
RETURNING Information from DML Statements 467 (1)
DML and Exception Handling 468 (2)
DML and Records 470 (3)
Transaction Management 473 (4)
The COMMIT Statement 474 (1)
The ROLLBACK Statement 474 (1)
The SAVEPOINT Statement 475 (1)
The SET TRANSACTION Statement 476 (1)
The LOCK TABLE Statement 476 (1)
Autonomous Transactions 477 (8)
Defining Autonomous Transactions 478 (1)
Rules and Restrictions on Autonomous 479 (1)
Transactions
Transaction Visibility 480 (1)
When to Use Autonomous Transactions 481 (1)
Building an Autonomous Logging Mechanism 482 (3)
15 Data Retrieval 485 (52)
Cursor Basics 486 (8)
Some Data Retrieval Terms 487 (1)
Typical Query Operations 488 (1)
Introduction to Cursor Attributes 489 (3)
Referencing PL/SQL Variables in a Cursor 492 (1)
Choosing Between Explicit and Implicit 493 (1)
Cursors
Working with Implicit Cursors 494 (6)
Implicit Cursor Examples 495 (1)
Error Handling with Implicit Cursors 496 (2)
Implicit SQL Cursor Attributes 498 (2)
Working with Explicit Cursors 500 (15)
Declaring Explicit Cursors 501 (3)
Opening Explicit Cursors 504 (1)
Fetching from Explicit Cursors 505 (2)
Column Aliases in Explicit Cursors 507 (1)
Closing Explicit Cursors 508 (2)
Explicit Cursor Attributes 510 (2)
Cursor Parameters 512 (3)
SELECT...FOR UPDATE 515 (4)
Releasing Locks with COMMIT 516 (2)
The WHERE CURRENT OF Clause 518 (1)
Cursor Variables and REF CURSORs 519 (14)
Why Use Cursor Variables? 520 (1)
Similarities to Static Cursors 521 (1)
Declaring REF CURSOR Types 521 (1)
Declaring Cursor Variables 522 (1)
Opening Cursor Variables 523 (1)
Fetching from Cursor Variables 524 (3)
Rules for Cursor Variables 527 (3)
Passing Cursor Variables as Arguments 530 (2)
Cursor Variable Restrictions 532 (1)
Cursor Expressions 533 (4)
Using Cursor Expressions 534 (2)
Restrictions on Cursor Expressions 536 (1)
16 Dynamic SQL and Dynamic PL/SQL 537 (54)
NDS Statements 538 (12)
The EXECUTE IMMEDIATE Statement 538 (5)
The OPEN FOR Statement 543 (5)
About the Four Dynamic SQL Methods 548 (2)
Binding Variables 550 (5)
Argument Modes 551 (2)
Duplicate Placeholders 553 (1)
Passing NULL Values 554 (1)
Working with Objects and Collections 555 (2)
Dynamic PL/SQL 557 (4)
Build Dynamic PL/SQL Blocks 558 (2)
Replace Repetitive Code with Dynamic 560 (1)
Blocks
Recommendations for NDS 561 (8)
Use Invoker Rights for Shared Programs 561 (1)
Anticipate and Handle Dynamic Errors 562 (2)
Use Binding Rather than Concatenation 564 (2)
Minimize the Dangers of Code Injection 566 (3)
When to Use DBMS_SQL 569 (22)
Obtain Information About Query Columns 569 (2)
Meeting Method 4 Dynamic SQL Requirements 571 (7)
Minimizing Parsing of Dynamic Cursors 578 (1)
Oracle Database llg New Dynamic SQL 579 (5)
Features
Enhanced Security for DBMS_SQL 584 (7)
Part V PL/SQL Application Construction
17 Procedures, Functions, and Parameters 591 (60)
Modular Code 592 (1)
Procedures 593 (4)
Calling a Procedure 596 (1)
The Procedure Header 596 (1)
The Procedure Body 596 (1)
The END Label 597 (1)
The RETURN Statement 597 (1)
Functions 597 (10)
Structure of a Function 598 (3)
The RETURN Datatype 601 (1)
The END Label 602 (1)
Calling a Function 603 (1)
Functions Without Parameters 604 (1)
The Function Header 604 (1)
The Function Body 605 (1)
The RETURN Statement 605 (2)
Parameters 607 (12)
Defining Parameters 608 (1)
Actual and Formal Parameters 608 (1)
Parameter Modes 609 (4)
Explicit Association of Actual and Formal 613 (4)
Parameters in PL/SQL
The NOCOPY Parameter Mode Qualifier 617 (1)
Default Values 618 (1)
Local or Nested Modules 619 (5)
Benefits of Local Modularization 620 (3)
Scope of Local Modules 623 (1)
Sprucing Up Your Code with Nested 623 (1)
Subprograms
Subprogram Overloading 624 (6)
Benefits of Overloading 625 (3)
Restrictions on Overloading 628 (1)
Overloading with Numeric Types 629 (1)
Forward Declarations 630 (1)
Advanced Topics 631 (19)
Calling Your Function from Inside SQL 631 (6)
Table Functions 637 (10)
Deterministic Functions 647 (2)
Implicit Cursor Results (Oracle Database 649 (1)
12c)
Go Forth and Modularize! 650 (1)
18 Packages 651 (36)
Why Packages? 651 (7)
Demonstrating the Power of the Package 652 (3)
Some Package-Related Concepts 655 (2)
Diagramming Privacy 657 (1)
Rules for Building Packages 658 (8)
The Package Specification 658 (2)
The Package Body 660 (2)
Initializing Packages 662 (4)
Rules for Calling Packaged Elements 666 (1)
Working with Package Data 667 (10)
Global Within a Single Oracle Session 668 (1)
Global Public Data 669 (1)
Packaged Cursors 669 (5)
Serializable Packages 674 (3)
When to Use Packages 677 (8)
Encapsulate Data Access 677 (3)
Avoid Hardcoding Literals 680 (3)
Improve Usability of Built-in Features 683 (1)
Group Together Logically Related 683 (1)
Functionality
Cache Static Session Data 684 (1)
Packages and Object Types 685 (2)
19 Triggers 687 (62)
DML Triggers 688 (22)
DML Trigger Concepts 689 (2)
Creating a DML Trigger 691 (5)
DML Trigger Example: No Cheating Allowed! 696 (6)
Multiple Triggers of the Same Type 702 (1)
Who Follows Whom 703 (2)
Mutating Table Errors 705 (1)
Compound Triggers: Putting It All in One 706 (4)
Place
DDL Triggers 710 (10)
Creating a DDL Trigger 710 (3)
Available Events 713 (1)
Available Attributes 713 (2)
Working with Events and Attributes 715 (3)
Dropping the Undroppable 718 (1)
The INSTEAD OF CREATE Trigger 719 (1)
Database Event Triggers 720 (8)
Creating a Database Event Trigger 721 (1)
The STARTUP Trigger 722 (1)
The SHUTDOWN Trigger 723 (1)
The LOGON Trigger 723 (1)
The LOGOFF Trigger 723 (1)
The SERVERERROR Trigger 724 (4)
INSTEAD OF Triggers 728 (8)
Creating an INSTEAD OF Trigger 728 (2)
The INSTEAD OF INSERT Trigger 730 (2)
The INSTEAD OF UPDATE Trigger 732 (1)
The INSTEAD OF DELETE Trigger 733 (1)
Populating the Tables 733 (1)
INSTEAD OF Triggers on Nested Tables 734 (2)
AFTER SUSPEND Triggers 736 (7)
Setting Up for the AFTER SUSPEND Trigger 736 (2)
Looking at the Actual Trigger 738 (1)
The ORA_SPACE_ERROR INFO Function 739 (1)
The DBMS_RESUMABLE Package 740 (2)
Trapped Multiple Times 742 (1)
To Fix or Not to Fix? 743 (1)
Maintaining Triggers 743 (6)
Disabling, Enabling, and Dropping Triggers 743 (1)
Creating Disabled Triggers 744 (1)
Viewing Triggers 745 (1)
Checking the Validity of Triggers 746 (3)
20 Managing PUSQL Code 749 (76)
Managing Code in the Database 750 (12)
Overview of Data Dictionary Views 751 (2)
Display Information About Stored Objects 753 (1)
Display and Search Source Code 753 (2)
Use Program Size to Determine Pinning 755 (1)
Requirements
Obtain Properties of Stored Code 756 (1)
Analyze and Modify Trigger State Through 757 (1)
Views
Analyze Argument Information 758 (1)
Analyze Identifier Usage (Oracle Database 759 (3)
11g's PL/Scope)
Managing Dependencies and Recompiling Code 762 (15)
Analyzing Dependencies with Data 763 (4)
Dictionary Views
Fine-Grained Dependency (Oracle Database 767 (2)
11g)
Remote Dependencies 769 (3)
Limitations of Oracle's Remote Invocation 772 (1)
Model
Recompiling Invalid Program Units 773 (4)
Compile-Time Warnings 777 (11)
A Quick Example 777 (1)
Enabling Compile-Time Warnings 778 (2)
Some Handy Warnings 780 (8)
Testing PL/SQL Programs 788 (7)
Typical, Tawdry Testing Techniques 789 (4)
General Advice for Testing PL/SQL Code 793 (1)
Automated Testing Options for PL/SQL 794 (1)
Tracing PL/SQL Execution 795 (13)
DBMS_UTILITY.FORMAT_CALL_STACK 796 (2)
UTL_CALL_STACK (Oracle Database 12c) 798 (3)
DBMS_APPLICATION_INFO 801 (2)
Tracing with opp_trace 803 (1)
The DBMS_TRACE Facility 804 (4)
Debugging PL/SQL Programs 808 (8)
The Wrong Way to Debug 809 (2)
Debugging Tips and Strategies 811 (5)
Using Whitelisting to Control Access to 816 (2)
Program Units
Protecting Stored Code 818 (3)
Restrictions on and Limitations of 818 (1)
Wrapping
Using the Wrap Executable 819 (1)
Dynamic Wrapping with DBMS_DDL 819 (2)
Guidelines for Working with Wrapped Code 821 (1)
Introduction to Edition-Based Redefinition 821 (4)
(Oracle Database llg Release 2)
21 Optimizing PL/SQL Performance 825 (100)
Tools to Assist in Optimization 827 (11)
Analyzing Memory Usage 827 (1)
Identifying Bottlenecks in PL/SQL Code 827 (6)
Calculating Elapsed Time 833 (1)
Choosing the Fastest Program 834 (2)
Avoiding Infinite Loops 836 (1)
Performance-Related Warnings 837 (1)
The Optimizing Compiler 838 (6)
Insights on How the Optimizer Works 840 (3)
Runtime Optimization of Fetch Loops 843 (1)
Data Caching Techniques 844 (25)
Package-Based Caching 845 (5)
Deterministic Function Caching 850 (2)
THe Function Result Cache (Oracle 852 (16)
Database 11g)
Caching Summary 868 (1)
Bulk Processing for Repeated SQL Statement 869 (19)
Execution
High-Speed Querying with BULK COLLECT 870 (7)
High-Speed DML with FORALL 877 (11)
Improving Performance with Pipelined Table 888 (29)
Functions
Replacing Row-Based Inserts with 889 (7)
Pipelined Function-Based Loads
Tuning Merge Operations with Pipelined 896 (2)
Functions
Asynchronous Data Unloading with Parallel 898 (4)
Pipelined Functions
Performance Implications of Partitioning 902 (1)
and Streaming Clauses in Parallel
Pipelined Functions
Pipelined Functions and the Cost-Based 903 (6)
Optimizer
Tuning Complex Data Loads with Pipelined 909 (7)
Functions
A Final Word on Pipelined Functions 916 (1)
Specialized Optimization Techniques 917 (6)
Using the NOCOPY Parameter Mode Hint 917 (4)
Using the Right Datatype 921 (1)
Optimizing Function Performance in SQL 922 (1)
(12.1 and higher)
Stepping Back for the Big Picture on 923 (2)
Performance
22 I/O and PUSQL 925 (46)
Displaying Information 925 (4)
Enabling DBMS_OUTPUT 926 (1)
Write Lines to the Buffer 926 (1)
Read the Contents of the Buffer 927 (2)
Reading and Writing Files 929 (15)
The UTL_FILE_DIR Parameter 929 (2)
Working with Oracle Directories 931 (1)
Open Files 932 (2)
Is the File Already Open? 934 (1)
Close Files 934 (1)
Read from Files 935 (3)
Write to Files 938 (3)
Copy Files 941 (1)
Delete Files 942 (1)
Rename and Move Files 943 (1)
Retrieve File Attributes 943 (1)
Sending Email 944 (12)
Oracle Prerequisites 945 (1)
Configuring Network Security 946 (1)
Send a Short (32,767 Bytes or Less) 947 (1)
Plain-Text Message
Include "Friendly" Names in Email 948 (2)
Addresses
Send a Plain-Text Message of Arbitrary 950 (1)
Length
Send a Message with a Short (32,767 Bytes 951 (2)
or Less) Attachment
Send a Small File (32,767 Bytes or Less) 953 (1)
as an Attachment
Attach a File of Arbitrary Size 953 (3)
Working with Web-Based Data (HTTP) 956 (11)
Retrieve a Web Page in "Pieces" 956 (2)
Retrieve a Web Page into a LOB 958 (1)
Authenticate Using HTTP Username/Password 959 (1)
Retrieve an SSL-Encrypted Web Page (via 960 (1)
HTTPS)
Submit Data to a Web Page via GET or POST 961 (4)
Disable Cookies or Make Cookies Persistent 965 (1)
Retrieve Data from an FTP Server 966 (1)
Use a Proxy Server 966 (1)
Other Types of I/O Available in PL/SQL 967 (4)
Database Pipes, Queues, and Alerts 967 (1)
TCP Sockets 968 (1)
Oracle's Built-in Web Server 968 (3)
Part VI Advanced PL/SQL Topics
23 Application Security and PUSQL 971 (68)
Security Overview 971 (2)
Encryption 973 (26)
Key Length 974 (1)
Algorithms 975 (2)
Padding and Chaining 977 (1)
The DBMS_CRYPTO Package 977 (2)
Encrypting Data 979 (3)
Encrypting LOBs 982 (1)
SecureFiles 982 (1)
Decrypting Data 983 (1)
Performing Key Generation 984 (1)
Performing Key Management 985 (6)
Cryptographic Hashing 991 (2)
Using Message Authentication Codes 993 (1)
Using Transparent Data Encryption 994 (3)
Transparent Tablespace Encryption 997 (2)
Row-Level Security 999 (20)
Why Learn About RLS? 1002(1)
A Simple RLS Example 1003(4)
Static Versus Dynamic Policies 1007(5)
Using Column-Sensitive RLS 1012(3)
RLS Debugging 1015(4)
Application Contexts 1019(9)
Using Application Contexts 1020(2)
Security in Contexts 1022(1)
Contexts as Predicates in RLS 1022(4)
Identifying Nondatabase Users 1026(2)
Fine-Grained Auditing 1028(11)
Why Learn About FGA? 1029(1)
A Simple FGA Example 1030(2)
Access How Many Columns? 1032(1)
Checking the Audit Trail 1033(2)
Using Bind Variables 1035(1)
Using Handler Modules 1036(3)
24 PL/SQL Architecture 1039(58)
DIANA 1039(1)
How Oracle Executes PL/SQL Code 1040(5)
An Example 1041(3)
Compiler Limits 1044(1)
The Default Packages of PL/SQL 1045(3)
Execution Authority Models 1048(16)
The Definer Rights Model 1049(5)
The Invoker Rights Model 1054(2)
Combining Rights Models 1056(1)
Granting Roles to PL/SQL Program Units 1057(3)
(Oracle Database 12c)
"Who Invoked Me?" Functions (Oracle 1060(1)
Database 12c)
BEQUEATH CURRENT_USER for Views (Oracle 1061(2)
Database 12c)
Constraining Invoker Rights Privileges 1063(1)
(Oracle Database 12c)
Conditional Compilation 1064(12)
Examples of Conditional Compilation 1065(1)
The Inquiry Directive 1066(4)
The $1F Directive 1070(2)
The $ERROR Directive 1072(1)
Synchronizing Code with Packaged Constants 1072(1)
Program-Specific Settings with Inquiry 1073(1)
Directives
Working with Postprocessed Code 1074(2)
PL/SQL and Database Instance Memory 1076(17)
The SGA, PGA, and UGA 1076(1)
Cursors, Memory, and More 1077(2)
Tips on Reducing Memory Use 1079(11)
What to Do If You Run Out of Memory 1090(3)
Native Compilation 1093(2)
When to Run in Interpreted Mode 1094(1)
When to Go Native 1094(1)
Native Compilation and Database Release 1094(1)
What You Need to Know 1095(2)
25 Globalization and Localization in PL/SQL 1097(44)
Overview and Terminology 1099(1)
Unicode Primer 1100(11)
National Character Set Datatypes 1102(1)
Character Encoding 1102(2)
Globalization Support Parameters 1104(1)
Unicode Functions 1105(6)
Character Semantics 1111(4)
String Sort Order 1115(5)
Binary Sort 1116(1)
Monolingual Sort 1117(2)
Multilingual Sort 1119(1)
Multilingual Information Retrieval 1120(6)
IR and PL/SQL 1123(3)
Date/Time 1126(5)
Timestamp Datatypes 1126(1)
Date/Time Formatting 1127(4)
Currency Conversion 1131(2)
Globalization Development Kit for PL/SQL 1133(8)
UTL_118N Utility Package 1133(3)
UTL_LMS Error-Handling Package 1136(1)
GDK Implementation Options 1137(4)
26 Object-Oriented Aspects of PL/SQL 1141(64)
Introduction to Oracle's Object Features 1142(2)
Object Types by Example 1144(40)
Creating a Base Type 1144(2)
Creating a Subtype 1146(1)
Methods 1147(5)
Invoking Supertype Methods in Oracle 1152(2)
Database llg and Later
Storing, Retrieving, and Using Persistent 1154(8)
Objects
Evolution and Creation 1162(2)
Back to Pointers? 1164(7)
Generic Data: The ANY Types 1171(5)
I Can Do It Myself 1176(3)
Comparing Objects 1179(5)
Object Views 1184(13)
A Sample Relational System 1186(2)
Object View with a Collection Attribute 1188(3)
Object Subview 1191(1)
Object View with Inverse Relationship 1192(1)
INSTEAD OF Triggers 1193(3)
Differences Between Object Views and 1196(1)
Object Tables
Maintaining Object Types and Object Views 1197(4)
Data Dictionary 1197(2)
Privileges 1199(2)
Concluding Thoughts from a (Mostly) 1201(4)
Relational Developer
27 Calling Java from PUSQL 1205(38)
Oracle and Java 1205(2)
Getting Ready to Use Java in Oracle 1207(5)
Installing Java 1207(1)
Building and Compiling Your Java Code 1208(1)
Setting Permissions for Java Development 1209(3)
and Execution
A Simple Demonstration 1212(6)
Finding the Java Functionality 1212(1)
Building a Custom Java Class 1213(2)
Compiling and Loading into Oracle 1215(2)
Building a PL/SQL Wrapper 1217(1)
Deleting Files from PL/SQL 1217(1)
Using loadjava 1218(3)
Using dropjava 1221(1)
Managing Java in the Database 1221(2)
The Java Namespace in Oracle 1221(1)
Examining Loaded Java Elements 1222(1)
Using DBMS_JAVA 1223(5)
LONGNAME: Converting Java Long Names 1223(1)
GET_, SET_, and RESET_COMPILER_OPTION: 1224(1)
Getting and Setting (a Few) Compiler
Options
SET_OUTPUT: Enabling Output from Java 1225(1)
EXPORT_SOURCE, EXPORT_RESOURCE, and 1226(2)
EXPORT_CLASS: Exporting Schema Objects
Publishing and Using Java in PL/SQL 1228(15)
Call Specs 1228(1)
Some Rules for Call Specs 1229(1)
Mapping Datatypes 1230(2)
Calling a Java Method in SQL 1232(1)
Exception Handling with Java 1232(4)
Extending File I/O Capabilities 1236(4)
Other Examples 1240(3)
28 External Procedures 1243(32)
Introduction to External Procedures 1244(4)
Example: Invoking an Operating System 1244(2)
Command
Architecture of External Procedures 1246(2)
Oracle Net Configuration 1248(4)
Specifying the Listener Configuration 1248(3)
Security Characteristics of the 1251(1)
Configuration
Setting Up Multithreaded Mode 1252(2)
Creating an Oracle Library 1254(2)
Writing the Call Specification 1256(10)
The Call Spec: Overall Syntax 1257(1)
Parameter Mapping: The Example Revisited 1258(2)
Parameter Mapping: The Full Story 1260(2)
More Syntax: The PARAMETERS Clause 1262(1)
PARAMETERS Properties 1263(3)
Raising an Exception from the Called C 1266(3)
Program
Nondefault Agents 1269(3)
Maintaining External Procedures 1272(3)
Dropping Libraries 1272(1)
Data Dictionary 1272(1)
Rules and Warnings 1273(2)
A Regular Expression Metacharacters and 1275(6)
Function Parameters
B Number Format Models 1281(4)
C Date Format Models 1285(6)
Index 1291
新书报道