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: (800) 998-9938 [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