Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c
1380Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c
1380Paperback(Sixth Edition)
-
PICK UP IN STORECheck Availability at Nearby Stores
Available within 2 business hours
Related collections and offers
Overview
Hundreds of thousands of PL/SQL developers have benefited from this book over the last twenty years; this edition continues that tradition. With extensive code examples and a lively sense of humor, this book explains language fundamentals, explores advanced coding techniques, and offers best practices to help you solve real-world problems.
- Get PL/SQL programs up and running quickly, with clear instructions for executing, tracing, testing, debugging, and managing code
- Understand new 12.1 features, including the ACCESSIBLE_BY clause, WITH FUNCTION and UDF pragma, BEQUEATH CURRENT_USER for views, and new conditional compilation directives
- Take advantage of extensive code samples, from easy-to-follow examples to reusable packaged utilities
- Optimize PL/SQL performance with features like the function result cache and Oracle utilities such as PL/Scope and the PL/SQL hierarchical profiler
- Build modular, easy-to-maintain PL/SQL applications using packages, procedures, functions, and triggers
Product Details
ISBN-13: | 9781449324452 |
---|---|
Publisher: | O'Reilly Media, Incorporated |
Publication date: | 02/17/2014 |
Edition description: | Sixth Edition |
Pages: | 1380 |
Product dimensions: | 7.00(w) x 9.20(h) x 2.50(d) |
About the Author
Bill Pribyl is the primary author of Learning Oracle PL/SQL and the coauthor of Oracle PL/SQL Programming and its companion pocket reference, all from O'Reilly Media. He is oddly proud of having used PL/SQL to write TCP/IP networking clients, tnsping callouts, near-realtime commodity price loaders, and transcendental functions. Bill, who holds a degree in physics from Rice University, is the former editor of the IOUG technical journal (Select) and former president of the South Central Oracle Users Group. At home with his family in Houston, Texas, Bill's current nonbillable titles include Webmaster for his neighborhood civic club, "IT Guy" for his son's Boy Scout troop, and Eucharistic Adoration Coordinator for his church. Visit Bill's firm at http://www.datacraft.com.
Read an Excerpt
From Chapter: Variables and Program Data
...The LONG datatype
A variable declared LONG can store variable-length strings of up to 32760 bytes--this is actually seven fewer bytes than allowed in VARCHAR2 type variables! The LONG datatype for PL/SQL variables is quite different from the LONG datatype for columns in the Oracle Server. The LONG datatype in Oracle7 can store character strings of up to two gigabytes or 231-1 bytes; this large size makes the LONG column a possible repository of mulitmedia information, such as graphics images.
As a result of these maximum length differences, you can always insert a PL/SQL LONG variable value into a LONG database column, but you cannot select a LONG database value larger than 32760 bytes into a PL/SQL LONG variable.
In the Oracle database, there are many restrictions on how the LONG column can be used in a SQL statement; for example:
- A tale may not contain more than one single LONG column.
- Yoiu may not use the LONG column in a GROUP BY, ORDER BY, WHERE, or CONNECT BY clause.
- You may not apply character functions (such as SUBSTR, INSTR, or LENGTH), to the LONG column.
Given the fact that aVARCHAR2 variable actually has a higher maximum length than the LONG and has no restrictions attached to it, I recommend that you always use the VARCHAR2 datatype in PL/SQL programs. LONGs have a place in the RDBMS, but that role is not duplicated in PL/SQL. This makes some sense since you will very rarely want to manipulate truly enormous strings within your program using such functions as SUBSTR or LENGTH or INSTR.
The RAW datatype
The RAW datatype is used to store binary data or other kinds of raw data, such as a digitized picture or image. A RAW variable has the same maximum length as VARCHAR2 (32767 bytes), which must also be specified when the variable is declared. The difference between RAW and VARCHAR2 is that PL/SQL will not try to interpret raw data. Within the Oracle RDBMS this means that Oracle will not perform character set conversions on RAW data when it is moved from one system (based, for example, on 7-bit ASCII) to another system.
Once again, there is an inconsistency between the PL/SQL maximum length for a RAW variable (32767) and the RDBMS maximum length (255). As a result, you cannot insert more than 255 bytes of your PL/SQL RAW variable's value into a database column. You can, on the other hand, insert the full value of a PL/SQL RAW variable into a column with type LONG RAW, which is a two-gigabyte container for raw data in the database.
The LONG RAW datatype
The LONG RAW datatype stores raw data of up to 32760 bytes and is just like the LONG datatype excerpt that the data in a LONG RAW variable is not interpreted by PL/SQL.
Given the fact that a RAW variable actually has a higher maximum length than the LONG RAW and has no restrictions attached to it, I recommended that you always use the RAW datatype in PL/SQL programs. LONG RAWs have a place in the RDBMS, but that role is not duplicated in PL/SQL.
The ROWID datatype
In the Oracle RDBMS, ROWID is a pseudocolumn that is part of every table you create. the rowid is an internally generated and maintained binary value which identifies a row of data in your table. It is called a pseudocolumn because a SQL statement includes it in places where you would normally use a column. However, it is not a column that you create for the table. Instead, the RDBMS generates the rowid for each row as it is inserted into the database. The information in the rowid provides the exact physical location of the row in the database. You cannot change the value of a rowid.
You can use the ROWID datatype to store rowids from the database in your PL/SQL program. You can SELECT or FETCH the rowid for a row into a ROWID variable. To manipulate rowids in Oracle8, you will want to use the built-in package, DBMS_ROWID (see Appendix C, Built-In Packages). In Oracle7, you will use the ROWIDTOCHAR function to convert the rowid to a fixed-length string and then perform operations against that string.
In Oracle7, for format of the fixed-length rowid is as follows:
BBBBBBB.RRRR.FFFFF
Components of this format have the following meanings:
BBBBBBB
The block in the database file
RRRR
The row in the clock (where the first row is zero, not one)
FFFFF
The database file
All these numbers are hexadecimal; the database file is a number which you would then use to look up the actual name of the database file through the data dictionary.
In Oracle8, rowid have been "extended" to support partitioned tables and indexes. The new, extended rowids include a data object number, identifying the database segment. Any schema object found in the same segment, such as a cluster of tables, will have the same object number. In Oracle8, then, a rowid contains the following information:
- The data object number
- The data file (where the first file is 1)
- The data block within the data file
- The row in the data block (where the first row is 0)
Table of Contents
ForewordPreface
I. Programming in PL/SQL
1. Introduction to PL/SQL
What Is PL/SQL?
The Concept of Programming in Oracle Applications
The Origins of PL/SQL
PL/SQL Versions
Advice for Oracle Programmers
A Few of My Favorite (PL/SQL) Things
Best Practices for PL/SQL Excellence
2. PL/SQL Language Fundamentals
The PL/SQL Character Set
Identifiers
Literals
The Semicolon Delimiter
Comments
The PRAGMA Keyword
Block Structure
3. Effective Coding Style
Fundamentals of Effective Layout
Formatting SQL Statements
Formatting Control Structures
Formatting PL/SQL Blocks
Formatting Packages
Using Comments Effectively
Documenting the Entire Package
II. PL/SQL Language Elements
4. Variables and Program Data
Identifiers
Scalar Datatypes
NULLs in PL/SQL
Variable Declarations
Anchored Declarations
Programmer-Defined Subtypes
Tips for Creating and Using Variables
5. Conditional and Sequential Control
Conditional Control Statements
Sequential Control Statements
6. Database Interaction and Cursors
Transaction Management
Cursors in PL/SQL
Implicit and Explicit Cursors
Declaring Cursors
Opening Cursors
Fetching from Cursors
Column Aliases in Cursors
Closing Cursors
Cursor Attributes
Cursor Parameters
SELECT FOR UPDATE in Cursors
Cursor Variables
Working with Cursors
7. Loops
LoopBasics
The Simple Loop
The Numeric FOR Loop
The Cursor FOR Loop
The WHILE Loop
Managing Loop Execution
Tips for PL/SQL Loops
8. Exception Handlers
Why Exception Handling?
The Exception Section
Types of Exceptions
Determining Exception-Handling Behavior
Raising an Exception
Handling Exceptions
Client-Server Error Communication
NO_DATA_FOUND: Multipurpose Exception
Exception Handler as IF Statement
RAISE Nothing but Exceptions
9. Records in PL/SQL
Record Basics
Table-Based Records
Cursor-Based Records
Programmer-Defined Records
Assigning Values to and from Records
Record Types and Record Compatibility
Nested Records
10. PL/SQL Tables
PL/SQL Tables and Other Collections
Characteristics of PL/SQL Tables
PL/SQL Tables and DML Statements
Declaring a PL/SQL Table
Referencing and Modifying PL/SQL Table Rows
Filling the Rows of a PL/SQL Table
Clearing the PL/SQL Table
PL/SQL Table Enhancements in PL/SQL Release 2.3
Working with PL/SQL Tables
III. Built-In Functions
11. Character Functions
Character Function Descriptions
Character Function Examples
12. Date Functions
Date Function Descriptions
Date Function Examples
13. Numeric, LOB, and Miscellaneous Functions
Numeric Function Descriptions
LOB Function Descriptions
Miscellaneous Function Descriptions
14. Conversion Functions
Conversion Formats
Conversion Function Descriptions
Conversion Function Examples
IV. Modular Code
15. Procedures and Functions
Modular Code
Review of PL/SQL Block Structure
The Anonymous PL/SQL Block
Procedures
Functions
Parameters
Local Modules
Module Overloading
Forward Declarations
Go Forth and Modularize!
16. Packages
The Benefits of Packages
Overview of Package Structure
The Package Specification
The Package Body
Package Data
Package Initialization
17. Calling PL/SQL Functions in SQL
Looking at the Problem
Syntax for Calling Stored Functions inSQL
Requirements for Stored Functions inSQL
Restrictions on PL/SQL Functions in SQL
Calling Packaged Functions in SQL
Column/Function Name Precedence
Realities: Calling PL/SQL Functions in SQL
Examples of Embedded PL/SQL
V. New PL/SQL8 Features
18. Object Types
Introduction to Oracle8 Objects
Oracle Objects Example
Syntax for Creating Object Types
Manipulating Objects in PL/SQL and SQL
Modifying Persistent Objects
Object Housekeeping
Making the Objects Option Work
19. Nested Tables and VARRAYs
Types of Collections
Creating the New Collections
Syntax for Declaring Collection Datatypes
Using Collections
Collection Pseudo-Functions
Collection Built-Ins
Example: PL/SQL-to-Server Integration
Collections Housekeeping
Which Collection Type Should I Use?
20. Object Views
Example: Using Object Views
INSTEAD OF Triggers
Syntax for Object Views
Differences Between Object Views and Object Tables
Not All Views with Objects Are Object Views
Schema Evolution
Object Views Housekeeping
Postscript: Using the BFILE Datatype
21. External Procedures
Introduction to External Procedures
Steps in Creating an External Procedure
Syntax for External Procedures
Mapping Parameters
OCI Service Routines
External Procedure Housekeeping
Examples
VI. Making PL/SQL Programs Work
22. Code Design Tips
Select Meaningful Module and Parameter Names
Build the Most Functional Functions
Take Full Advantage of Local Modularization
Be Wary of Modules Without Any Parameters
Create Independent Modules
Construct Abstract Data Types (ADTs)
Tips for Parameter Design
23. Managing Code in the Database
Executing Stored Code
Transaction Integrity and Execute Authority
Module Validation and Dependency Management
Remote Procedure Calls
Managing Stored Objects with SQL*Plus
Using SQL to Examine Stored Objects
Encrypting Stored Code
24. Debugging PL/SQL
The Wrong Way to Debug
Debugging Tips and Strategies
25. Tuning PL/SQL Applications
Analyzing Program Performance
Tuning Access to Compiled Code
Tuning Access to Your Data
Tuning Your Algorithms
Overview of PL/SQL8 Enhancements
26. Tracing PL/SQL Execution
The PL/SQL Trace Facility
Tracing for Production Support
Free Format Filtering
Structured Interface Filtering
Quick-and-Dirty Tracing
VII. Appendixes
A. What's on the Companion Disk?
B. Calling Stored Procedures from PL/SQL Version 1.1
C. Built-In Packages
Index