Oracle PL/SQL language : pocket reference

178 Pages·2008·0.405 MB·English

by  Steven Feuerstein, Bill Pribyl, Chip Dawes

FOURTH EDITION
Oracle PL/SQL Language
Pocket Reference
Steven Feuerstein, Bill Pribyl,
and Chip Dawes
Beijing • Cambridge • Farnham • Köln • Paris • Sebastopol • Taipei • Tokyo
Oracle PL/SQL Language Pocket Reference, Fourth Edition
by Steven Feuerstein, Bill Pribyl, and Chip Dawes
Copyright © 2008 Chip Dawes, Steven Feuerstein, and Bill Pribyl.
All rights reserved.
Printed in Canada.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North,
Sebastopol, CA 95472.
O’Reilly books may be purchased for educational, business, or sales
promotional use. Online editions are also available for most titles
(safari.oreilly.com). For more information, contact our corporate/
institutional sales department: [phone] [email protected].
Editors: Deborah Russell Indexer: Johnna VanHoose Dinse
and Mary Treseler Cover Designer: Karen Montgomery
Production Editor: Mary Brady Interior Designer: David Futato
Proofreader: Mary Brady Illustrator: Robert Romano
Printing History:
April 1999: First Edition.
February 2003: Second Edition.
April 2004: Third Edition.
October 2007: Fourth Edition.
NutshellHandbook,theNutshellHandbooklogo,andtheO’Reillylogoare
registered trademarks of O’Reilly Media, Inc. ThePocket Reference series
designations,OraclePL/SQLLanguagePocketReference,theimageofants,
and related trade dress are trademarks of O’Reilly Media, Inc. Many of the
designationsusedbymanufacturersandsellerstodistinguishtheirproducts
are claimed as trademarks. Where those designations appear in this book,
and O’Reilly Media, Inc. was aware of a trademark claim, the designations
have been printed in caps or initial caps. Oracle® and all Oracle-based
trademarks and logos are trademarks or registered trademarks of Oracle
Corporation,Inc.intheUnitedStatesandothercountries.O’ReillyMedia,
Inc. is independent of Oracle Corporation. Java and all Java-based
trademarks and logos are trademarks or registered trademarks of Sun
Microsystems,Inc.intheUnitedStatesandothercountries.O’ReillyMedia,
Inc. is independent of Sun Microsystems, Inc.
While every precaution has been taken in the preparation of this book, the
publisher and authors assume no responsibility for errors or omissions, or
for damages resulting from the use of the information contained herein.
ISBN-10: 0-596-51404-2
ISBN-13: 978-0-596-51404-4
[TM]
Contents
Introduction 1
Acknowledgments 1
Conventions 2
PL/SQL Language Fundamentals 2
PL/SQL Character Set 2
Identifiers 3
Boolean, Numeric, and String Literals 4
Numeric Literals 5
Datetime Interval Literals 6
Delimiters 6
Comments 8
Pragmas 8
Statements 9
Block Structure 9
Variables and Program Data 11
Scalar Datatypes 12
LOB Datatypes 18
Implicit Datatype Conversions 19
NULLs in PL/SQL 19
Declaring Variables 19
Anchored Declarations 22
Programmer-Defined Subtypes 23
iii
Conditional and Sequential Control 23
Conditional Control Statements 23
Sequential Control Statements 27
Loops 28
Simple Loop 29
Numeric FOR Loop 29
Cursor FOR Loop 30
WHILE Loop 30
REPEAT UNTIL Loop Emulation 31
EXIT Statement 31
CONTINUE Statement (Oracle Database 11g) 31
Loop Labels 33
Database Interaction 34
Sequences in PLSQL 34
Transaction Management 34
Autonomous Transactions 37
Cursors in PL/SQL 38
Explicit Cursors 38
Implicit Cursors 42
Dynamic Cursors 45
DBMS_SQL 46
SQL Injection and Bind Variables 47
Cursor Variables 48
Cursor Expressions 49
Exception Handling 50
Declaring Exceptions 51
Raising Exceptions 53
Scope 54
Propagation 54
iv | Contents
Records in PL/SQL 56
Declaring Records 57
Referencing Fields of Records 58
Assigning Records 58
Records and DML 59
Nested Records 60
Collections in PL/SQL 61
Declaring a Collection 63
Initializing a Collection 64
Adding and Removing Elements 65
Nested Table Functions 65
Collection Methods 68
Collections and Privileges 71
Nested Collections 71
Bulk Binds 71
Built-in Functions and Packages 75
Built-in Functions 75
Built-in Regular Expression Functions 87
Built-in Packages 93
Stored Procedures and Functions 97
Procedures 98
Functions 99
Parameters 100
Local Programs 103
Program Overloading 104
Forward Declarations 106
Table Functions 106
Function Result Cache 107
Privileges and Stored PL/SQL 109
Contents | v
Triggers 109
Creating Triggers 110
Trigger Predicates 114
DML Events 114
Compound DML Triggers 115
DDL Events 117
Database Events 117
Packages 117
Package Structure 118
Referencing Package Elements 120
Package Data 120
SERIALLY_REUSABLE Pragma 120
Package Initialization 121
Calling PL/SQL Functions in SQL 122
Calling a Function 123
Calling Packaged Functions in SQL 124
Column/Function Name Precedence 125
Object-Oriented Features 125
Object Types 126
Type Inheritance 128
Methods 128
Methods in Subtypes 132
Manipulating Objects in PL/SQL and SQL 133
Upcasting and Downcasting 135
Changing Object Types 138
Compilation 139
Compiling Stored PL/SQL Programs 140
Conditional Compilation 142
Compiler Warnings 145
Optimizing Compiler 147
Performing Native Compilation of PL/SQL 149
vi | Contents
Java Language Integration 152
Example 153
Publishing Java to PL/SQL 154
Data Dictionary 155
Index 157
Contents | vii

Chapter1
Oracle PL/SQL Language
Pocket Reference
Introduction
TheOraclePL/SQLLanguagePocketReferenceisaquickref-
erence guide to the PL/SQL programming language, which
provides procedural extensions to the SQL relational data-
base language and a range of Oracle development tools.
Whereapackage,program,orfunctionissupportedonlyfor
aparticularversionoftheOracledatabase(e.g.,OracleData-
base 11g), we indicate this in the text.
ThepurposeofthispocketreferenceistohelpPL/SQLusers
findthesyntaxofspecificlanguageelements.Itisnotaself-
contained user guide; basic knowledge of the PL/SQL pro-
gramming language is assumed. For more information, see
the following O’Reilly books:
Oracle PL/SQL Programming, Fourth Edition, by Steven
Feuerstein with Bill Pribyl
Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein
Oracle PL/SQL Best Practices, Second Edition, by Steven
Feuerstein
Oracle in a Nutshell, by Rick Greenwald and David C.
Kreines
Acknowledgments
Wearegratefultoallthosewhohelpedinthepreparationof
this book. In particular, thanks to Bryn Llewellyn for his
1
input on this latest revision as well as the third edition.
Thanks as well to first-edition reviewers Eric J. Givler and
Stephen Nelson and to second- and third-edition reviewer
Jonathan Gennick. In addition, we appreciate all the good
work by the O’Reilly crew in editing and producing this
book.
Conventions
UPPERCASE indicates PL/SQL keywords, as well as certain
identifiers used by Oracle Corporation as built-in function
and package names.
Italic indicates filenames and directories, as well as the first
use of a term.
Constant width is used for code examples, literals, and
identifiers.
Constant width bold indicates user input in examples show-
ing an interaction.
[] enclose optional items in syntax descriptions.
{} enclose a list of items in syntax descriptions; you must
choose one item from the list.
| separates bracketed list items in syntax descriptions.
PL/SQL Language Fundamentals
Thissectionsummarizesthefundamentalcomponentsofthe
PL/SQL language: characters, identifiers, literals, delimiters,
use of comments and pragmas, and construction of state-
ments and blocks.
PL/SQL Character Set
The PL/SQL language is constructed from letters, digits,
symbols, and whitespace, as defined in the following table:
2 | Oracle PL/SQL Language Pocket Reference