ORACLE ORACLE PRESS*— EXCLUSIVELY FROM M t G R A W * H I L L 0 S B 0 R N E ORACLE DATABASE Wg PL/SQL M Programming ™ Develop Database-Driven PI7SQL Applications SCOTT URMAN Best-Selling Auihor and Principal Member, Ofeanostit and CfeFect (tesfllutiortTeam, OrdC 5 Corporator RON HARD MAN, OCP SenisrTochnicil Anaiysl, Orfcfite Support Services michael Mclaughlin, dcs. Senior AppiicaSi on Upgrade Manager Oracle Application Development * M'THI \TI<" Oracle Press* 0\ll FROM (t>,K(>R S T ORACLG Oracle Press™ Oracle Database 1 0g PL/SQL Programming This page intentionally left blank ORACLG Oracle Press™ Oracle Database 1 0g PL/SQL Programming Scott Urman Ron Hardman Michael McLaughlin McGraw-Hill/Osborne New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto The McGraw-Hill Companies Copyright © 2004 by The McGraw-Hill Companies, Inc. All rights reserved. Manufactured in the United States of America. Except as permitted under the United States Copyright Act of 1 976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. 0-07-150228-9 The material in this eBook also appears in the print version of this title: 0-07-223066-5. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occur- rence of a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use incorporate training programs. For more information, please contact George Hoare, Special Sales, at george_hoare@mcgraw-hill.com or (212) 904-4069. TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. ("McGraw-Hill") and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1 976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill's prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED "AS IS." McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WAR- RANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MER- CHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not war- rant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inac- curacy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the pos- sibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise. DOI: 10.1036/0072230665 About the Authors Scott Urman is a Principal Member of Technical Staff in the Diagnostics and Defect Resolution (DDR) team in Oracle's Server Technology division. He currently focuses on the internals of Oracle Text and Oracle Ultrasearch, and has worked with JSP, JDBC, PL/SQL, and OCI. Prior to joining DDR, he was a Senior Analyst in the Languages division of Oracle Worldwide Technical Support, focusing on all of Oracle's language tools. He has been with Oracle since 1989. He is also the best- selling author of Oracle8i Advanced PL/SQL Programming, Oracle8 PL/SQL Programming, and Oracle9i PL/SQL Programming. Ron Hardman, OCP, is a Senior Technical Specialist with Oracle Worldwide Technical Support. Prior to joining Oracle Corporation, he was an Oracle Database Developer and Consultant. He is a frequent presenter on the topics of Oracle Text and Ultrasearch at Oracle User Group conferences, teaches classes on SQL and PL/ SQL, and has published articles with Oracle Magazine and other online magazines on subjects related to information retrieval. Michael McLaughlin, D. CS., is the Senior Application Upgrade Manager for Oracle Applications Release Engineering. He is currently working on the upgrade architecture for the next release of Oracle Applications. He has worked with PL/SQL since its first version in Oracle 6, and has authored customer support notes on customizing Oracle Applications with Pro*C and PL/SQL. He has taught computer science and information technology at Regis University and Colorado Technical University, including courses in database development, SQL, PL/SQL, and Java. About the Technical Editor Cheryl Riniker is a Senior Technical Specialist with Oracle Worldwide Support in Oracle's Bug Diagnosis and Escalation division. She currently works with Oracle Applications' Financials Suite of products. She has used PL/SQL in development projects since 1997, and received her DBA OCP in 2001 . She graduated magna cum laude with an M.A. in English/ESL from Utah State University. Copyright © 2004 by The McGraw-Hill Companies, Inc. Click here for terms of use. This page intentionally left blank This book is dedicated to our daughter Almarah Rose Urman, born May 1 st, 2004. — Scott Urman To my wife Susan, you inspire me. Thank you for your patience and encouragement. To my daughter Jessica, and son Joshua, thank you for your hugs and kisses. They never ran out. To my parents, thank you for your example. — Ron Hardman To my wife Lisa, who is my constant, and our children Sarah, Joseph, Elise, Ian, Ariel, Callie, Nathan, and Spencer. Thank you for your inspiration, patience, and sacrifice that made my efforts on this book possible. — Michael McLaughlin This page intentionally left blank PART I Introduction 1 Introduction to PL/SQL 3 2 Using SQL*Plus and JDeveloper 23 3 PL/SQL Basics 45 4 Using SQL with PL/SQL 117 5 Records 181 6 Collections 213 7 Error Handling 303 8 Creating Procedures, Functions, and Packages 335 9 Using Procedures, Functions, and Packages 381 1 0 Database Triggers 443 PART II Advanced PL/SQL Features 1 1 Intersession Communication 499 1 2 External Routines 543 13 Dynamic SQL 583 14 Introduction to Objects 635 1 5 Objects in the Database 673 1 6 Large Objects 711 1 7 Scheduling Tasks 767 ix X Oracle Database 10g PL/SQL Programming PART III Appendixes A PL/SQL Reserved Words 791 B Guide to Supplied Packages 795 Index 839 PART I Introduction 1 Introduction to PL/SQL 3 Introduction to Programming Languages 4 Note to Beginning Programmers 5 PL/What? 6 Structured Query Language (SQL) 6 Relational Database Overview 7 PL/SQL vs. SQL 9 PL/SQL vs. Java 11 PL/SQL History and Features 12 Language Fundamentals 15 Anonymous Blocks 15 Procedures 16 Functions 16 Packages 16 Object Types 16 PL/SQL Statement Processing 17 Interpreted 17 Native Compilation 17 Getting the Most from This Book 18 Audience 18 Objective 18 Scope 18 Assumptions 19 Conventions 20 Examples 20 Summary 21 xi xii Oracle Database lOg PL/SQL Programming 2 Using SQL*Plus and JDeveloper 23 SQL*Plus 24 Connecting to the Instance 25 Testing the Connection 26 Using SQL*Plus 28 Changing SQL*Plus Session Settings 31 Running a Script from a File 31 Output to the Screen Using SQL*Plus and PL/SQL 33 JDeveloper 34 Installing JDeveloper 34 Working with PL/SQL in JDeveloper 36 Summary 43 3 PL/SQL Basics 45 The PL/SQL Block 46 The Basic Structure 46 Anonymous Blocks 49 Named Blocks 52 Nested Blocks 62 Triggers 63 Object Types 64 Language Rules and Conventions 64 Lexical Units 64 PL/SQL Data Types 77 Scalar 77 Character/String 77 NUMBER Data Type 81 Boolean 83 Date/Time 84 Composite 87 Reference 87 LOB 89 Using Variables 89 %TYPE 90 %ROWTYPE 90 Variable Scope 91 Bind Variables 93 Hiding Code 97 Expressions 100 Assignment Operator 101 Concatenation Operator 102 Controlling Program Flow 102 Conditional Evaluation 103 Circular Execution 110 Sequential Navigation using GOTO 113 Summary 115 Contents xiii 4 Using SQL with PL/SQL 117 Transaction Processing 118 Transactions and Locking 119 Autonomous Transactions 125 Set Transaction 128 Retrieving Data 130 SQL SELECT Statement 130 Pattern Matching 134 Information Retrieval 138 Cursors 142 How Cursors Work 143 Explicit Cursors 146 Implicit Cursors 153 Cursor Variables 1...
Amiga789