Oracle PL/SQL Programming 6th Editionby Steven Feuerstein, Bill PribylSIXTH EDITIONOracle Pl/sQl ProgrammingSteven feuersteinwith Bill PribylBeijing. Cambridge. Farnham·Kh· Sebastopol, Tokyo O REILLY°Oracle pl/sql programming Sixth Editionby Steven Feuerstein with Bill PribylCopyright O 2014 Steven Feuerstein, Bill Pribyl. All rights reservedPrinted in the United States of AmericaPublished by o reilly Media, Inc, 1005 Gravenstein Highway North, Sebastopol, CA 95472OReilly books may be purchased for educational, business, or sales promotional use. Online editions arealsoavailableformosttitles(http://my.safaribooksonline.com).Formoreinformationcontactourcorporateinstitutionalsalesdepartment800-998-9938orcorporate@oreilly.comEditor: Ann SpencerIndexer: Ellen troutmanProduction editor: nicole shelbyCover Designer: Randy ceCopyeditor: Rachel MonaghanInterior Designer: David FutatoProofreader: Rachel headllustrator: Rebecca demarestSeptember 1995: First Editioncond editicSeptember 2002: Third EditionAugust 2005Fourth editionSeptember 2009: Fifth EditionJanuary 2014Sixth editionRevision History for the Sixth Edition2014-01-22. First releaseSeehttp://oreilly.com/catalog/errata.csp?isbn=9781449324452forreleasedetailsNutshell Handbook, the Nutshell Handbooklogo, and the O reilly logo are registered trademarks ofo reillyMedia, Inc. Oracle PL/SQL Programing, the image of ants, and related trade dress are trademarks of o ReillyMedia, IncMany of the designations used by manufacturers and sellers to distinguish their products are claimed astrademarks. Where those designations appear in this book, and OReilly media, Inc, was aware of a trademark claim, the designations have been printed in caps or initial capsWhile every precaution has been taken in the preparation of this book, the publisher and authors assumeno responsibility for errors or omissions, or for damages resulting from the use of the information containedhereinISBN:978-1-449-32445-2LQGwife, Veva Silva, whose intelligence, strength, beauty, and arthave greatly enriched my lifeSteven feuersteinTo my wife, Norma. Still melting my heart after a quarter centuryBill PribylTable of contentsPrefacePart L. Programming in PL/SQLIntroduction to PL/SQL.What IS PL/SQL?The Origins of PL/SQLThe Early Years of PL/SQLImproved Application PortabilityImproved Execution Authority and Transaction IntegrityHumble beginnings, Steady ImprovementSo This Is PL/sQl3344556778Integration with SQLControl and Conditional logicWhen Things Go WrongAbout PL/SQL Versions11Oracle Database 12c New PL/SQL FeaturesResources for PL/SQL Developers14The O Reilly Pl/sQl Series15PL/SQL on the Internet16Some Words of advice17Dont Be in Such a hurry!Dont Be afraid to Ask for Help18Take a Creative, Even Radical Approach192. Creating and Running Pl/SQL Code21Navigating the database21Creating and Editing Source Code22SQL Plus23Starting Up SQL*Plus24Running a sQl Statement26Running a Pl/sQl Program27Running a Script29What Is the Current DirectoryOther SQL Plus Tasks31Error Handling in SQL Plus36Why You Will Love and Hate SQL Plus36Performing Essential PL/SQL Tasks7Creating a Stored Program37Executing a Stored Program41Showing stored programs41Managing Grants and Synonyms for Stored Programs42Dropping a Stored ProgramHiding the Source Code of a Stored ProgramEditing Environments for PL/SQLCalling PL/SQL from Other Languages45C: USing Oracle's Precompiler(pro*C)Java: USing DBcPerl: Using Perl DBI and DBD: Oracle48PHP: Using Oracle Extensions49PL/SQL Server Pages51And where else?513. Language Fundamentals53PL/SQL Block Structure53Anonymous blocksamed blocks57Nested blocks57copeQualify all references to Variables and Columns in SQL Statements59Visibili62The PL/SQL Character SetIdentifiers67Reserved Words68Whitespace and Keywords70LiteralsNULLS71Embedding Single Quotes Inside a Literal StringNumeric LiteralsBoolean literals74The Semicolon delimiterTable of contentsCommentsSingle-Line Comment SyntaxMultiline comment syntax76The Pragma KeywordLabelsPart I. PL/SQL Program Structure4. Conditional and Sequential Control鲁·鲁。鲁鲁83IF Statements83The If-then Combination84The IF-THEN-ELSE CombinationThe If-THEN-ElSIF Combination87Avoiding if Syntax gotchas89Nested If statements90Short-Circuit Evaluation91CASE Statements and Expressions93Simple case statements93Searched case statements95Nested CASE Statements98CASE Expressions98The GoTo Statement100The nulL Statement101Improving program Readability101Using NULL After a Label1025. Iterative Processing with Loops.105Loop Basics105Examples of Different Loops106Structure of PL/SQL Loops107The Simple Loop108Terminating a Simple Loop: EXIT and EXIT WHEN109Emulating a REPEAt UNtIL Loop110The Intentionally Infinite LoopThe WhIle Loop112The Numeric FOR Loop114Rules for Numeric FOR Loops114Examples of Numeric FOR Loops115Handling Nontrivial Increments116The Cursor FOR Loop117Example of cursor FOR Loops118Table of contents|ⅶi00Labels119The CONTINUE Statement120Tips for Iterative Processing123Use Understandable Names for Loop indexes123The Proper Way to Say goodbye124Obtaining Information About FOR Loop Execution126SQL Statement as Loop1266. Exception Handlers.129Exception-Handling Concepts and Terminology129Defining exceptions132Declaring Named Exceptions132Associating Exception Names with Error Codes133About Named System Exceptions136Scope of an Exception139Raising exceptions140The raiSe Statement140Using RAISE_ APPLICATION_ ERROR141Handling Exceptions143Built-in error functions144Combining Multiple Exceptions in a Single Handler149Unhandled Exceptions149Propagation of Unhandled Exceptions150Continuing past exceptions153Writing when others handling Code155Building an Effective Error Management architecture157Decide on Your Error Management Strategy158Standardize Handling of Different Types of Exceptions159Organize Use of Application-Specific Error Codes162Use Standardized Error Management Programs163Work with Your Own Exception"Objects165Create Standard Templates for Common Error Handling167Making the most of Pl/sql error Management169Part Il. PL/SQL Program Data7. Working with Program Data.Naming your program DataOverview of PL/SQL Datatypes175Character Dat176Numbers177I Table of Contents