SIXTH EDITION Oracle PL/SQL Programming Steven Feuerstein with Bill Pribyl Oracle PL/SQL Programming, Sixth Edition by Steven Feuerstein with Bill Pribyl Copyright © 2014 Steven Feuerstein, Bill Pribyl. All rights reserved. Printed in the United States of America. 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 (http://my.safaribooksonline.com). For more information, contact our corporate/ institutional sales department: 800-998-9938 or [email protected]. Editor: Ann Spencer Indexer: Ellen Troutman Production Editor: Nicole Shelby Cover Designer: Randy Comer Copyeditor: Rachel Monaghan Interior Designer: David Futato Proofreader: Rachel Head Illustrator: Rebecca Demarest September 1995: First Edition September 1997: Second Edition September 2002: Third Edition August 2005: Fourth Edition September 2009: Fifth Edition January 2014: Sixth Edition Revision History for the Sixth Edition: 2014-01-22: First release See http://oreilly.com/catalog/errata.csp?isbn=9781449324452 for release details. Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of O’Reilly Media, Inc. Oracle PL/SQL Programing, the image of ants, and related trade dress are trademarks of O’Reilly Media, Inc. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O’Reilly Media, Inc., was aware of a trade‐ mark claim, the designations have been printed in caps or initial caps. 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: 978-1-449-32445-2 [QG] To my wife, Veva Silva, whose intelligence, strength, beauty, and art have greatly enriched my life. —Steven Feuerstein To my wife, Norma. Still melting my heart after a quarter century. —Bill Pribyl Table of Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Part I. Programming in PL/SQL 1. Introduction to PL/SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 What Is PL/SQL? 3 The Origins of PL/SQL 4 The Early Years of PL/SQL 4 Improved Application Portability 5 Improved Execution Authority and Transaction Integrity 5 Humble Beginnings, Steady Improvement 6 So This Is PL/SQL 7 Integration with SQL 7 Control and Conditional Logic 8 When Things Go Wrong 9 About PL/SQL Versions 11 Oracle Database 12c New PL/SQL Features 12 Resources for PL/SQL Developers 14 The O’Reilly PL/SQL Series 15 PL/SQL on the Internet 16 Some Words of Advice 17 Don’t Be in Such a Hurry! 17 Don’t Be Afraid to Ask for Help 18 Take a Creative, Even Radical Approach 19 2. Creating and Running PL/SQL Code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Navigating the Database 21 Creating and Editing Source Code 22 SQL*Plus 23 v Starting Up SQL*Plus 24 Running a SQL Statement 26 Running a PL/SQL Program 27 Running a Script 29 What Is the “Current Directory”? 30 Other SQL*Plus Tasks 31 Error Handling in SQL*Plus 36 Why You Will Love and Hate SQL*Plus 36 Performing Essential PL/SQL Tasks 37 Creating a Stored Program 37 Executing a Stored Program 41 Showing Stored Programs 41 Managing Grants and Synonyms for Stored Programs 42 Dropping a Stored Program 43 Hiding the Source Code of a Stored Program 44 Editing Environments for PL/SQL 44 Calling PL/SQL from Other Languages 45 C: Using Oracle’s Precompiler (Pro*C) 46 Java: Using JDBC 47 Perl: Using Perl DBI and DBD::Oracle 48 PHP: Using Oracle Extensions 49 PL/SQL Server Pages 51 And Where Else? 51 3. Language Fundamentals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 PL/SQL Block Structure 53 Anonymous Blocks 55 Named Blocks 57 Nested Blocks 57 Scope 58 Qualify All References to Variables and Columns in SQL Statements 59 Visibility 62 The PL/SQL Character Set 65 Identifiers 67 Reserved Words 68 Whitespace and Keywords 70 Literals 70 NULLs 71 Embedding Single Quotes Inside a Literal String 72 Numeric Literals 73 Boolean Literals 74 The Semicolon Delimiter 74 vi | Table of Contents Comments 75 Single-Line Comment Syntax 75 Multiline Comment Syntax 76 The PRAGMA Keyword 76 Labels 77 Part II. PL/SQL Program Structure 4. Conditional and Sequential Control. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 IF Statements 83 The IF-THEN Combination 84 The IF-THEN-ELSE Combination 86 The IF-THEN-ELSIF Combination 87 Avoiding IF Syntax Gotchas 89 Nested IF Statements 90 Short-Circuit Evaluation 91 CASE Statements and Expressions 93 Simple CASE Statements 93 Searched CASE Statements 95 Nested CASE Statements 98 CASE Expressions 98 The GOTO Statement 100 The NULL Statement 101 Improving Program Readability 101 Using NULL After a Label 102 5. Iterative Processing with Loops. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 Loop Basics 105 Examples of Different Loops 106 Structure of PL/SQL Loops 107 The Simple Loop 108 Terminating a Simple Loop: EXIT and EXIT WHEN 109 Emulating a REPEAT UNTIL Loop 110 The Intentionally Infinite Loop 111 The WHILE Loop 112 The Numeric FOR Loop 114 Rules for Numeric FOR Loops 114 Examples of Numeric FOR Loops 115 Handling Nontrivial Increments 116 The Cursor FOR Loop 117 Example of Cursor FOR Loops 118 Table of Contents | vii Loop Labels 119 The CONTINUE Statement 120 Tips for Iterative Processing 123 Use Understandable Names for Loop Indexes 123 The Proper Way to Say Goodbye 124 Obtaining Information About FOR Loop Execution 126 SQL Statement as Loop 126 6. Exception Handlers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Exception-Handling Concepts and Terminology 129 Defining Exceptions 132 Declaring Named Exceptions 132 Associating Exception Names with Error Codes 133 About Named System Exceptions 136 Scope of an Exception 139 Raising Exceptions 140 The RAISE Statement 140 Using RAISE_APPLICATION_ERROR 141 Handling Exceptions 143 Built-in Error Functions 144 Combining Multiple Exceptions in a Single Handler 149 Unhandled Exceptions 149 Propagation of Unhandled Exceptions 150 Continuing Past Exceptions 153 Writing WHEN OTHERS Handling Code 155 Building an Effective Error Management Architecture 157 Decide on Your Error Management Strategy 158 Standardize Handling of Different Types of Exceptions 159 Organize Use of Application-Specific Error Codes 162 Use Standardized Error Management Programs 163 Work with Your Own Exception “Objects” 165 Create Standard Templates for Common Error Handling 167 Making the Most of PL/SQL Error Management 169 Part III. PL/SQL Program Data 7. Working with Program Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Naming Your Program Data 173 Overview of PL/SQL Datatypes 175 Character Data 176 Numbers 177 viii | Table of Contents