Oracle PL/SQL Programming : Covers Versions Through Oracle Database 11g Release 2

by Feuerstein, Steven; Pribyl, Bill

• ISBN13: 9780596003814
• ISBN10: 0596003811
• Edition: 3rd
• Format: Paperback
• Copyright: 2002-09-01
• Publisher: Oreilly & Associates Inc

Summary

Oracle PL/SQL Programming has been an indispensable reference for both novice and experienced PL/SQL developers since its first edition. Now in its third edition, the book has been thoroughly revised and updated to include the latest Oracle releases--Oracle8i and Oracle9i--the ""Internet databases"" that communicate with other technologies, such as Java, to run powerful e-commerce applications. Oracle has made substantial changes to the PL/SQL language, and developers who want to take full advantage of its power will find the information they need in this book. Authored by renowned PL/SQL experts Steven Feuerstein and Bill Pribyl, the new edition of the book includes all PL/SQL language features up to and including Oracle 9i Release 2 (9.2). Regardless of the version you are using, from Oracle 7.3.4 to Oracle 9i, this book will answer your questions.

Author Biography

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is the author or coauthor of Oracle PL/SQL Programming, Oracle PL/SQL Best Practices, Oracle PL/SQL Programming: Guide to Oracle8i Features, Oracle PL/SQL Developer's Workbook, Oracle Built-in Packages, Advanced Oracle PL/SQL Programming with Packages, and several pocket reference books (all from O'Reilly & Associates). Steven is a Senior Technology Advisor with Quest Software, has been developing software since 1980, and worked for Oracle Corporation from 1987 to 1992.

Bill Pribyl, author, teacher, and software consultant,is the primary author of Learning Oracle PL/SQL and the coauthor of Oracle PL/SQL Programming and its companion pocket reference, all published by O'Reilly & Associates. An Oracle user since 1986, Bill has consulted on many aspects of using Oracle products. He recently spearheaded PLNet.org, a Web-based repository where developers can share open source PL/SQL.

Table of Contents

Preface	xiii
Part I. Programming in PL/SQL
Introduction to PL/SQL	3	(26)
What Is PL/SQL?	3	(1)
The Origins of PL/SQL	4	(3)
So This Is PL/SQL	7	(3)
About PL/SQL Versions	10	(11)
Resources for PL/SQL Developers	21	(4)
Some Words of Advice	25	(4)
Creating and Running PL/SQL Code	29	(31)
SQL *Plus	30	(14)
Performing Essential PL/SQL Tasks	44	(7)
Oracle's PL/SQL-Based Developer Tools	51	(2)
Calling PL/SQL from Other Languages	53	(6)
And What Else?	59	(1)
Language Fundamentals	60	(25)
PL/SQL Block Structure	60	(9)
The PL/SQL Character Set	69	(1)
Identifiers	70	(5)
Literals	75	(2)
The Semicolon Delimiter	77	(1)
Comments	78	(1)
The PRAGMA Keyword	79	(1)
Labels	80	(5)
Part II. PL/SQL Program Structure
Conditional and Sequential Control	85	(23)
IF Statements	85	(8)
CASE Statements	93	(7)
The GOTO Statement	100	(5)
The NULL Statement	105	(3)
Iterative Processing with Loops	108	(18)
Loop Basics	108	(3)
The Simple Loop	111	(2)
The WHILE Loop	113	(1)
The Numeric FOR Loop	114	(3)
The Cursor FOR Loop	117	(3)
Loop Labels	120	(1)
Tips for Iterative Processing	121	(5)
Exception Handlers	126	(27)
How PL/SQL Deals with Errors	126	(3)
Defining Exceptions	129	(8)
Raising Exceptions	137	(3)
Handling Exceptions	140	(13)
Part III. PL/SQL Program Data
Working with Program Data	153	(23)
Naming Your Program Data	153	(2)
Overview of PL/SQL Datatypes	155	(5)
Declaring Program Data	160	(7)
Programmer-Defined Subtypes	167	(1)
Conversion Between Datatypes	168	(8)
Strings	176	(50)
The Impact of Character Sets	176	(9)
String Datatypes	185	(5)
String Issues	190	(5)
String Functions	195	(27)
NLS Functions	222	(4)
Numbers	226	(30)
Numeric Datatypes	226	(7)
Number Conversions	233	(13)
Numeric Functions	246	(10)
Dates and Timestamps	256	(66)
Date and Time Datatypes	257	(9)
Date and Timestamp Conversions	266	(28)
Date/Time Arithmetic	294	(11)
Date/Time Functions	305	(17)
Records and Collections	322	(73)
Records in PL/SQL	322	(14)
Collections in PL/SQL	336	(5)
Declaring Collection Types and Collections	341	(4)
Where Collections Can Be Used	345	(4)
Collection Built-Ins (Methods)	349	(9)
Working with Collections	358	(26)
Collection Pseudo-Functions	384	(7)
Maintaining Collections	391	(1)
Choosing a Collection Type	392	(3)
Miscellaneous Datatypes	395	(44)
The BOOLEAN Datatype	395	(2)
The RAW Datatype	397	(1)
The UROWID and ROWID Datatypes	397	(5)
The LOB Datatypes	402	(3)
Working with LOBs	405	(23)
Predefined Object Types	428	(11)
Part IV. SQL in PL/SQL
DML and Transaction Management	439	(37)
DML in PL/SQL	440	(10)
Bulk DML with the FORALL Statement	450	(9)
Transaction Management	459	(4)
Autonomous Transactions	463	(13)
Data Retrieval	476	(58)
Cursor Basics	477	(9)
Working with Implicit Cursors	486	(6)
Working with Explicit Cursors	492	(15)
Bulk Collect	507	(5)
Select...for Update	512	(5)
Cursor Variables	517	(13)
Cursor Expressions (Oracle9i)	530	(4)
Dynamic SQL and Dynamic PL/SQL	534	(35)
NDS Statements	535	(4)
Multirow Queries with Cursor Variables	539	(7)
Binding Variables	546	(6)
Working with Objects and Collections	552	(2)
Building Applications with NDS	554	(8)
NDS Utility Package	562	(1)
Comparing NDS and DBMS_SQL	563	(6)
Part V. PL/SQL Application Construction
Procedures, Functions, and Parameters	569	(52)
Modular Code	570	(1)
Procedures	571	(4)
Functions	575	(7)
Parameters	582	(12)
Local Modules	594	(4)
Module Overloading	598	(5)
Forward Declarations	603	(1)
Advanced Topics	604	(15)
Go Forth and Modularize!	619	(2)
Packages	621	(32)
Why Packages?	621	(7)
Rules for Building Packages	628	(7)
Rules for Calling Packaged Elements	635	(1)
Working with Package Data	636	(9)
When to Use Packages	645	(6)
Packages and Object Types	651	(2)
Triggers	653	(57)
DML Triggers	654	(23)
DDL Triggers	677	(9)
Database Event Triggers	686	(7)
Instead of Triggers	693	(5)
After Suspend Triggers	698	(8)
Maintaining Triggers	706	(4)
Managing PL/SQL Applications	710	(45)
Managing and Analyzing Code in the Database	711	(5)
Protecting Stored Code	716	(2)
Using Native Compilation	718	(3)
Testing PL/SQL Programs	721	(7)
Debugging PL/SQL Programs	728	(8)
Tuning PL/SQL Programs	736	(7)
Improving Application Performance	743	(12)
Part VI. Advanced PL/SQL Topics
PL/SQL's Runtime Architecture	755	(60)
Looking Under the Hood	756	(10)
Dependency Management	766	(10)
PL/SQL's Use of Memory in the Oracle Server	776	(15)
The Processing of Server-Side PL/SQL	791	(3)
PL/SQL Code on the Client	794	(9)
Execution Authority Models	803	(8)
Hardware for PL/SQL: Bigger = Better?	811	(2)
What You Need to Know	813	(2)
Object-Oriented Aspects of PL/SQL	815	(59)
Introduction to Oracle's Object Features	815	(2)
An Extended Example	817	(39)
Object Views	856	(12)
Maintaining Object Types and Object Views	868	(3)
Pontifications	871	(3)
Calling Java from PL/SQL	874	(36)
Oracle and Java	874	(2)
Getting Ready to Use Java in Oracle	876	(3)
A Simple Demonstration	879	(6)
Using loadjava	885	(3)
Using dropjava	888	(1)
Managing Java in the Database	889	(2)
Using DBMS_JAVA	891	(5)
Publishing and Using Java in PL/SQL	896	(14)
External Procedures	910	(27)
Introduction to External Procedures	911	(3)
The Oracle Net Configuration	914	(3)
Creating an Oracle Library	917	(2)
Writing the Call Specification	919	(10)
Raising an Exception from the Called C Program	929	(3)
Nondefault Agents	932	(3)
Maintaining External Procedures	935	(2)
Index	937