Oracle PL/SQL Programming

Name: Oracle PL/SQL Programming | Covers Versions Through Oracle Database 12c
Brand: O'Reilly
Price: 58.99 EUR
Availability: OnlineOnly

Covers Versions Through Oracle Database 12c

Steven Feuerstein(Author)

O'Reilly (Publisher)

6th Edition

Published on 23. January 2014

1392 pages

E-Book

PDF with Adobe-DRM

System requirements

978-1-4493-2443-8 (ISBN)

€58.99incl. 7% vat

System requirements

for PDF with Adobe-DRM

E-Book Single Licence

Available for download

Description

More details

Other editions

Content

Cover
Copyright
Table of Contents
Preface
Objectives of This Book
Structure of This Book
About the Contents
What This Book Does Not Cover
Conventions Used in This Book
Which Platform or Version?
About the Code
Using Code Examples
Safari® Books Online
How to Contact Us
Acknowledgments
Part I. Programming in PL/SQL
Chapter 1. Introduction to PL/SQL
What Is PL/SQL?
The Origins of PL/SQL
The Early Years of PL/SQL
Improved Application Portability
Improved Execution Authority and Transaction Integrity
Humble Beginnings, Steady Improvement
So This Is PL/SQL
Integration with SQL
Control and Conditional Logic
When Things Go Wrong
About PL/SQL Versions
Oracle Database 12c New PL/SQL Features
Resources for PL/SQL Developers
The O'Reilly PL/SQL Series
PL/SQL on the Internet
Some Words of Advice
Don't Be in Such a Hurry!
Don't Be Afraid to Ask for Help
Take a Creative, Even Radical Approach
Chapter 2. Creating and Running PL/SQL Code
Navigating the Database
Creating and Editing Source Code
SQL*Plus
Starting Up SQL*Plus
Running a SQL Statement
Running a PL/SQL Program
Running a Script
What Is the "Current Directory"?
Other SQL*Plus Tasks
Error Handling in SQL*Plus
Why You Will Love and Hate SQL*Plus
Performing Essential PL/SQL Tasks
Creating a Stored Program
Executing a Stored Program
Showing Stored Programs
Managing Grants and Synonyms for Stored Programs
Dropping a Stored Program
Hiding the Source Code of a Stored Program
Editing Environments for PL/SQL
Calling PL/SQL from Other Languages
C: Using Oracle's Precompiler (Pro*C)
Java: Using JDBC
Perl: Using Perl DBI and DBD::Oracle
PHP: Using Oracle Extensions
PL/SQL Server Pages
And Where Else?
Chapter 3. Language Fundamentals
PL/SQL Block Structure
Anonymous Blocks
Named Blocks
Nested Blocks
Scope
Qualify All References to Variables and Columns in SQL Statements
Visibility
The PL/SQL Character Set
Identifiers
Reserved Words
Whitespace and Keywords
Literals
NULLs
Embedding Single Quotes Inside a Literal String
Numeric Literals
Boolean Literals
The Semicolon Delimiter
Comments
Single-Line Comment Syntax
Multiline Comment Syntax
The PRAGMA Keyword
Labels
Part II. PL/SQL Program Structure
Chapter 4. Conditional and Sequential Control
IF Statements
The IF-THEN Combination
The IF-THEN-ELSE Combination
The IF-THEN-ELSIF Combination
Avoiding IF Syntax Gotchas
Nested IF Statements
Short-Circuit Evaluation
CASE Statements and Expressions
Simple CASE Statements
Searched CASE Statements
Nested CASE Statements
CASE Expressions
The GOTO Statement
The NULL Statement
Improving Program Readability
Using NULL After a Label
Chapter 5. Iterative Processing with Loops
Loop Basics
Examples of Different Loops
Structure of PL/SQL Loops
The Simple Loop
Terminating a Simple Loop: EXIT and EXIT WHEN
Emulating a REPEAT UNTIL Loop
The Intentionally Infinite Loop
The WHILE Loop
The Numeric FOR Loop
Rules for Numeric FOR Loops
Examples of Numeric FOR Loops
Handling Nontrivial Increments
The Cursor FOR Loop
Example of Cursor FOR Loops
Loop Labels
The CONTINUE Statement
Tips for Iterative Processing
Use Understandable Names for Loop Indexes
The Proper Way to Say Goodbye
Obtaining Information About FOR Loop Execution
SQL Statement as Loop
Chapter 6. Exception Handlers
Exception-Handling Concepts and Terminology
Defining Exceptions
Declaring Named Exceptions
Associating Exception Names with Error Codes
About Named System Exceptions
Scope of an Exception
Raising Exceptions
The RAISE Statement
Using RAISE_APPLICATION_ERROR
Handling Exceptions
Built-in Error Functions
Combining Multiple Exceptions in a Single Handler
Unhandled Exceptions
Propagation of Unhandled Exceptions
Continuing Past Exceptions
Writing WHEN OTHERS Handling Code
Building an Effective Error Management Architecture
Decide on Your Error Management Strategy
Standardize Handling of Different Types of Exceptions
Organize Use of Application-Specific Error Codes
Use Standardized Error Management Programs
Work with Your Own Exception "Objects"
Create Standard Templates for Common Error Handling
Making the Most of PL/SQL Error Management
Part III. PL/SQL Program Data
Chapter 7. Working with Program Data
Naming Your Program Data
Overview of PL/SQL Datatypes
Character Data
Numbers
Dates, Timestamps, and Intervals
Booleans
Binary Data
ROWIDs
REF CURSORs
Internet Datatypes
"Any" Datatypes
User-Defined Datatypes
Declaring Program Data
Declaring a Variable
Declaring Constants
The NOT NULL Clause
Anchored Declarations
Anchoring to Cursors and Tables
Benefits of Anchored Declarations
Anchoring to NOT NULL Datatypes
Programmer-Defined Subtypes
Conversion Between Datatypes
Implicit Data Conversion
Explicit Datatype Conversion
Chapter 8. Strings
String Datatypes
The VARCHAR2 Datatype
The CHAR Datatype
String Subtypes
Working with Strings
Specifying String Constants
Using Nonprintable Characters
Concatenating Strings
Dealing with Case
Traditional Searching, Extracting, and Replacing
Padding
Trimming
Regular Expression Searching, Extracting, and Replacing
Working with Empty Strings
Mixing CHAR and VARCHAR2 Values
String Function Quick Reference
Chapter 9. Numbers
Numeric Datatypes
The NUMBER Type
The PLS_INTEGER Type
The BINARY_INTEGER Type
The SIMPLE_INTEGER Type
The BINARY_FLOAT and BINARY_DOUBLE Types
The SIMPLE_FLOAT and SIMPLE_DOUBLE Types
Numeric Subtypes
Number Conversions
The TO_NUMBER Function
The TO_CHAR Function
The CAST Function
Implicit Conversions
Numeric Operators
Numeric Functions
Rounding and Truncation Functions
Trigonometric Functions
Numeric Function Quick Reference
Chapter 10. Dates and Timestamps
Datetime Datatypes
Declaring Datetime Variables
Choosing a Datetime Datatype
Getting the Current Date and Time
Interval Datatypes
Declaring INTERVAL Variables
When to Use INTERVALs
Datetime Conversions
From Strings to Datetimes
From Datetimes to Strings
Working with Time Zones
Requiring a Format Mask to Match Exactly
Easing Up on Exact Matches
Interpreting Two-Digit Years in a Sliding Window
Converting Time Zones to Character Strings
Padding Output with Fill Mode
Date and Timestamp Literals
Interval Conversions
Converting from Numbers to Intervals
Converting Strings to Intervals
Formatting Intervals for Display
Interval Literals
CAST and EXTRACT
The CAST Function
The EXTRACT Function
Datetime Arithmetic
Date Arithmetic with Intervals and Datetimes
Date Arithmetic with DATE Datatypes
Computing the Interval Between Two Datetimes
Mixing DATEs and TIMESTAMPs
Adding and Subtracting Intervals
Multiplying and Dividing Intervals
Using Unconstrained INTERVAL Types
Date/Time Function Quick Reference
Chapter 11. Records
Records in PL/SQL
Benefits of Using Records
Declaring Records
Programmer-Defined Records
Working with Records
Comparing Records
Trigger Pseudorecords
Chapter 12. Collections
Collections Overview
Collections Concepts and Terminology
Types of Collections
Collection Examples
Where You Can Use Collections
Choosing a Collection Type
Collection Methods (Built-ins)
The COUNT Method
The DELETE Method
The EXISTS Method
The EXTEND Method
The FIRST and LAST Methods
The LIMIT Method
The PRIOR and NEXT Methods
The TRIM Method
Working with Collections
Declaring Collection Types
Declaring and Initializing Collection Variables
Populating Collections with Data
Accessing Data Inside a Collection
Using String-Indexed Collections
Collections of Complex Datatypes
Multilevel Collections
Working with Collections in SQL
Nested Table Multiset Operations
Testing Equality and Membership of Nested Tables
Checking for Membership of an Element in a Nested Table
Performing High-Level Set Operations
Handling Duplicates in a Nested Table
Maintaining Schema-Level Collections
Necessary Privileges
Collections and the Data Dictionary
Chapter 13. Miscellaneous Datatypes
The BOOLEAN Datatype
The RAW Datatype
The UROWID and ROWID Datatypes
Getting ROWIDs
Using ROWIDs
The LOB Datatypes
Working with LOBs
Understanding LOB Locators
Empty Versus NULL LOBs
Writing into a LOB
Reading from a LOB
BFILEs Are Different
SecureFiles Versus BasicFiles
Temporary LOBs
Native LOB Operations
LOB Conversion Functions
Predefined Object Types
The XMLType Type
The URI Types
The Any Types
Part IV. SQL in PL/SQL
Chapter 14. DML and Transaction Management
DML in PL/SQL
A Quick Introduction to DML
Cursor Attributes for DML Operations
RETURNING Information from DML Statements
DML and Exception Handling
DML and Records
Transaction Management
The COMMIT Statement
The ROLLBACK Statement
The SAVEPOINT Statement
The SET TRANSACTION Statement
The LOCK TABLE Statement
Autonomous Transactions
Defining Autonomous Transactions
Rules and Restrictions on Autonomous Transactions
Transaction Visibility
When to Use Autonomous Transactions
Building an Autonomous Logging Mechanism
Chapter 15. Data Retrieval
Cursor Basics
Some Data Retrieval Terms
Typical Query Operations
Introduction to Cursor Attributes
Referencing PL/SQL Variables in a Cursor
Choosing Between Explicit and Implicit Cursors
Working with Implicit Cursors
Implicit Cursor Examples
Error Handling with Implicit Cursors
Implicit SQL Cursor Attributes
Working with Explicit Cursors
Declaring Explicit Cursors
Opening Explicit Cursors
Fetching from Explicit Cursors
Column Aliases in Explicit Cursors
Closing Explicit Cursors
Explicit Cursor Attributes
Cursor Parameters
SELECT...FOR UPDATE
Releasing Locks with COMMIT
The WHERE CURRENT OF Clause
Cursor Variables and REF CURSORs
Why Use Cursor Variables?
Similarities to Static Cursors
Declaring REF CURSOR Types
Declaring Cursor Variables
Opening Cursor Variables
Fetching from Cursor Variables
Rules for Cursor Variables
Passing Cursor Variables as Arguments
Cursor Variable Restrictions
Cursor Expressions
Using Cursor Expressions
Restrictions on Cursor Expressions
Chapter 16. Dynamic SQL and Dynamic PL/SQL
NDS Statements
The EXECUTE IMMEDIATE Statement
The OPEN FOR Statement
About the Four Dynamic SQL Methods
Binding Variables
Argument Modes
Duplicate Placeholders
Passing NULL Values
Working with Objects and Collections
Dynamic PL/SQL
Build Dynamic PL/SQL Blocks
Replace Repetitive Code with Dynamic Blocks
Recommendations for NDS
Use Invoker Rights for Shared Programs
Anticipate and Handle Dynamic Errors
Use Binding Rather than Concatenation
Minimize the Dangers of Code Injection
When to Use DBMS_SQL
Obtain Information About Query Columns
Meeting Method 4 Dynamic SQL Requirements
Minimizing Parsing of Dynamic Cursors
Oracle Database 11g New Dynamic SQL Features
Enhanced Security for DBMS_SQL
Part V. PL/SQL Application Construction
Chapter 17. Procedures, Functions, and Parameters
Modular Code
Procedures
Calling a Procedure
The Procedure Header
The Procedure Body
The END Label
The RETURN Statement
Functions
Structure of a Function
The RETURN Datatype
The END Label
Calling a Function
Functions Without Parameters
The Function Header
The Function Body
The RETURN Statement
Parameters
Defining Parameters
Actual and Formal Parameters
Parameter Modes
Explicit Association of Actual and Formal Parameters in PL/SQL
The NOCOPY Parameter Mode Qualifier
Default Values
Local or Nested Modules
Benefits of Local Modularization
Scope of Local Modules
Sprucing Up Your Code with Nested Subprograms
Subprogram Overloading
Benefits of Overloading
Restrictions on Overloading
Overloading with Numeric Types
Forward Declarations
Advanced Topics
Calling Your Function from Inside SQL
Table Functions
Deterministic Functions
Implicit Cursor Results (Oracle Database 12c)
Go Forth and Modularize!
Chapter 18. Packages
Why Packages?
Demonstrating the Power of the Package
Some Package-Related Concepts
Diagramming Privacy
Rules for Building Packages
The Package Specification
The Package Body
Initializing Packages
Rules for Calling Packaged Elements
Working with Package Data
Global Within a Single Oracle Session
Global Public Data
Packaged Cursors
Serializable Packages
When to Use Packages
Encapsulate Data Access
Avoid Hardcoding Literals
Improve Usability of Built-in Features
Group Together Logically Related Functionality
Cache Static Session Data
Packages and Object Types
Chapter 19. Triggers
DML Triggers
DML Trigger Concepts
Creating a DML Trigger
DML Trigger Example: No Cheating Allowed!
Multiple Triggers of the Same Type
Who Follows Whom
Mutating Table Errors
Compound Triggers: Putting It All in One Place
DDL Triggers
Creating a DDL Trigger
Available Events
Available Attributes
Working with Events and Attributes
Dropping the Undroppable
The INSTEAD OF CREATE Trigger
Database Event Triggers
Creating a Database Event Trigger
The STARTUP Trigger
The SHUTDOWN Trigger
The LOGON Trigger
The LOGOFF Trigger
The SERVERERROR Trigger
INSTEAD OF Triggers
Creating an INSTEAD OF Trigger
The INSTEAD OF INSERT Trigger
The INSTEAD OF UPDATE Trigger
The INSTEAD OF DELETE Trigger
Populating the Tables
INSTEAD OF Triggers on Nested Tables
AFTER SUSPEND Triggers
Setting Up for the AFTER SUSPEND Trigger
Looking at the Actual Trigger
The ORA_SPACE_ERROR_INFO Function
The DBMS_RESUMABLE Package
Trapped Multiple Times
To Fix or Not to Fix?
Maintaining Triggers
Disabling, Enabling, and Dropping Triggers
Creating Disabled Triggers
Viewing Triggers
Checking the Validity of Triggers
Chapter 20. Managing PL/SQL Code
Managing Code in the Database
Overview of Data Dictionary Views
Display Information About Stored Objects
Display and Search Source Code
Use Program Size to Determine Pinning Requirements
Obtain Properties of Stored Code
Analyze and Modify Trigger State Through Views
Analyze Argument Information
Analyze Identifier Usage (Oracle Database 11g's PL/Scope)
Managing Dependencies and Recompiling Code
Analyzing Dependencies with Data Dictionary Views
Fine-Grained Dependency (Oracle Database 11g)
Remote Dependencies
Limitations of Oracle's Remote Invocation Model
Recompiling Invalid Program Units
Compile-Time Warnings
A Quick Example
Enabling Compile-Time Warnings
Some Handy Warnings
Testing PL/SQL Programs
Typical, Tawdry Testing Techniques
General Advice for Testing PL/SQL Code
Automated Testing Options for PL/SQL
Tracing PL/SQL Execution
DBMS_UTILITY.FORMAT_CALL_STACK
UTL_CALL_STACK (Oracle Database 12c)
DBMS_APPLICATION_INFO
Tracing with opp_trace
The DBMS_TRACE Facility
Debugging PL/SQL Programs
The Wrong Way to Debug
Debugging Tips and Strategies
Using Whitelisting to Control Access to Program Units
Protecting Stored Code
Restrictions on and Limitations of Wrapping
Using the Wrap Executable
Dynamic Wrapping with DBMS_DDL
Guidelines for Working with Wrapped Code
Introduction to Edition-Based Redefinition (Oracle Database 11g Release 2)
Chapter 21. Optimizing PL/SQL Performance
Tools to Assist in Optimization
Analyzing Memory Usage
Identifying Bottlenecks in PL/SQL Code
Calculating Elapsed Time
Choosing the Fastest Program
Avoiding Infinite Loops
Performance-Related Warnings
The Optimizing Compiler
Insights on How the Optimizer Works
Runtime Optimization of Fetch Loops
Data Caching Techniques
Package-Based Caching
Deterministic Function Caching
THe Function Result Cache (Oracle Database 11g)
Caching Summary
Bulk Processing for Repeated SQL Statement Execution
High-Speed Querying with BULK COLLECT
High-Speed DML with FORALL
Improving Performance with Pipelined Table Functions
Replacing Row-Based Inserts with Pipelined Function-Based Loads
Tuning Merge Operations with Pipelined Functions
Asynchronous Data Unloading with Parallel Pipelined Functions
Performance Implications of Partitioning and Streaming Clauses in Parallel Pipelined Functions
Pipelined Functions and the Cost-Based Optimizer
Tuning Complex Data Loads with Pipelined Functions
A Final Word on Pipelined Functions
Specialized Optimization Techniques
Using the NOCOPY Parameter Mode Hint
Using the Right Datatype
Optimizing Function Performance in SQL (12.1 and higher)
Stepping Back for the Big Picture on Performance
Chapter 22. I/O and PL/SQL
Displaying Information
Enabling DBMS_OUTPUT
Write Lines to the Buffer
Read the Contents of the Buffer
Reading and Writing Files
The UTL_FILE_DIR Parameter
Working with Oracle Directories
Open Files
Is the File Already Open?
Close Files
Read from Files
Write to Files
Copy Files
Delete Files
Rename and Move Files
Retrieve File Attributes
Sending Email
Oracle Prerequisites
Configuring Network Security
Send a Short (32,767 Bytes or Less) Plain-Text Message
Include "Friendly" Names in Email Addresses
Send a Plain-Text Message of Arbitrary Length
Send a Message with a Short (32,767 Bytes or Less) Attachment
Send a Small File (32,767 Bytes or Less) as an Attachment
Attach a File of Arbitrary Size
Working with Web-Based Data (HTTP)
Retrieve a Web Page in "Pieces"
Retrieve a Web Page into a LOB
Authenticate Using HTTP Username/Password
Retrieve an SSL-Encrypted Web Page (via HTTPS)
Submit Data to a Web Page via GET or POST
Disable Cookies or Make Cookies Persistent
Retrieve Data from an FTP Server
Use a Proxy Server
Other Types of I/O Available in PL/SQL
Database Pipes, Queues, and Alerts
TCP Sockets
Oracle's Built-in Web Server
Part VI. Advanced PL/SQL Topics
Chapter 23. Application Security and PL/SQL
Security Overview
Encryption
Key Length
Algorithms
Padding and Chaining
The DBMS_CRYPTO Package
Encrypting Data
Encrypting LOBs
SecureFiles
Decrypting Data
Performing Key Generation
Performing Key Management
Cryptographic Hashing
Using Message Authentication Codes
Using Transparent Data Encryption
Transparent Tablespace Encryption
Row-Level Security
Why Learn About RLS?
A Simple RLS Example
Static Versus Dynamic Policies
Using Column-Sensitive RLS
RLS Debugging
Application Contexts
Using Application Contexts
Security in Contexts
Contexts as Predicates in RLS
Identifying Nondatabase Users
Fine-Grained Auditing
Why Learn About FGA?
A Simple FGA Example
Access How Many Columns?
Checking the Audit Trail
Using Bind Variables
Using Handler Modules
Chapter 24. PL/SQL Architecture
DIANA
How Oracle Executes PL/SQL Code
An Example
Compiler Limits
The Default Packages of PL/SQL
Execution Authority Models
The Definer Rights Model
The Invoker Rights Model
Combining Rights Models
Granting Roles to PL/SQL Program Units (Oracle Database 12c)
"Who Invoked Me?" Functions (Oracle Database 12c)
BEQUEATH CURRENT_USER for Views (Oracle Database 12c)
Constraining Invoker Rights Privileges (Oracle Database 12c)
Conditional Compilation
Examples of Conditional Compilation
The Inquiry Directive
The $IF Directive
The $ERROR Directive
Synchronizing Code with Packaged Constants
Program-Specific Settings with Inquiry Directives
Working with Postprocessed Code
PL/SQL and Database Instance Memory
The SGA, PGA, and UGA
Cursors, Memory, and More
Tips on Reducing Memory Use
What to Do If You Run Out of Memory
Native Compilation
When to Run in Interpreted Mode
When to Go Native
Native Compilation and Database Release
What You Need to Know
Chapter 25. Globalization and Localization in PL/SQL
Overview and Terminology
Unicode Primer
National Character Set Datatypes
Character Encoding
Globalization Support Parameters
Unicode Functions
Character Semantics
String Sort Order
Binary Sort
Monolingual Sort
Multilingual Sort
Multilingual Information Retrieval
IR and PL/SQL
Date/Time
Timestamp Datatypes
Date/Time Formatting
Currency Conversion
Globalization Development Kit for PL/SQL
UTL_118N Utility Package
UTL_LMS Error-Handling Package
GDK Implementation Options
Chapter 26. Object-Oriented Aspects of PL/SQL
Introduction to Oracle's Object Features
Object Types by Example
Creating a Base Type
Creating a Subtype
Methods
Invoking Supertype Methods in Oracle Database 11g and Later
Storing, Retrieving, and Using Persistent Objects
Evolution and Creation
Back to Pointers?
Generic Data: The ANY Types
I Can Do It Myself
Comparing Objects
Object Views
A Sample Relational System
Object View with a Collection Attribute
Object Subview
Object View with Inverse Relationship
INSTEAD OF Triggers
Differences Between Object Views and Object Tables
Maintaining Object Types and Object Views
Data Dictionary
Privileges
Concluding Thoughts from a (Mostly) Relational Developer
Chapter 27. Calling Java from PL/SQL
Oracle and Java
Getting Ready to Use Java in Oracle
Installing Java
Building and Compiling Your Java Code
Setting Permissions for Java Development and Execution
A Simple Demonstration
Finding the Java Functionality
Building a Custom Java Class
Compiling and Loading into Oracle
Building a PL/SQL Wrapper
Deleting Files from PL/SQL
Using loadjava
Using dropjava
Managing Java in the Database
The Java Namespace in Oracle
Examining Loaded Java Elements
Using DBMS_JAVA
LONGNAME: Converting Java Long Names
GET_, SET_, and RESET_COMPILER_OPTION: Getting and Setting (a Few) Compiler Options
SET_OUTPUT: Enabling Output from Java
EXPORT_SOURCE, EXPORT_RESOURCE, and EXPORT_CLASS: Exporting Schema Objects
Publishing and Using Java in PL/SQL
Call Specs
Some Rules for Call Specs
Mapping Datatypes
Calling a Java Method in SQL
Exception Handling with Java
Extending File I/O Capabilities
Other Examples
Chapter 28. External Procedures
Introduction to External Procedures
Example: Invoking an Operating System Command
Architecture of External Procedures
Oracle Net Configuration
Specifying the Listener Configuration
Security Characteristics of the Configuration
Setting Up Multithreaded Mode
Creating an Oracle Library
Writing the Call Specification
The Call Spec: Overall Syntax
Parameter Mapping: The Example Revisited
Parameter Mapping: The Full Story
More Syntax: The PARAMETERS Clause
PARAMETERS Properties
Raising an Exception from the Called C Program
Nondefault Agents
Maintaining External Procedures
Dropping Libraries
Data Dictionary
Rules and Warnings
Appendix A. Regular Expression Metacharacters and Function Parameters
Metacharacters
Functions and Parameters
Regular Expression Functions
Regular Expression Parameters
Appendix B. Number Format Models
Denoting Monetary Units
Appendix C. Date Format Models
Index
About the Authors

System requirements

Save as PDF Copy link into clipboard

Schweitzer Fachinformationen

Oracle PL/SQL Programming

Description

More details

Other editions

Additional editions

Previous edition

Content

System requirements