OOrraaccllee ®® PPLL//SSQQLL ™™ bbyy EExxaammppllee Third Edition THE PRENTICE HALL PTR ORACLE SERIES The Independent Voice on Oracle ORACLE8i AND UNIX PERFORMANCE TUNING Alomari ORACLE WEB APPLICATION PROGRAMMING FOR PL/SQL DEVELOPERS Boardman/Caffrey/Morse/Rosenzweig SOFTWARE ENGINEERING WITH ORACLE: BEST PRACTICES FOR MISSION-CRITICAL SYSTEMS Bonazzi ORACLE8i AND JAVA: FROM CLIENT/SERVER TO E-COMMERCE Bonazzi/Stokol ORACLE8 DATABASE ADMINISTRATION FOR WINDOWS NT Brown WEB DEVELOPMENT WITH ORACLE PORTAL El-Mallah JAVA ORACLE DATABASE DEVELOPMENT Gallardo ORACLE DESK REFERENCE Harrison ORACLE SQL HIGH PERFORMANCE TUNING, SECOND EDITION Harrison ORACLE DESIGNER: A TEMPLATE FOR DEVELOPING AN ENTERPRISE STANDARDS DOCUMENT Kramm/Graziano ORACLE DEVELOPER/2000 FORMS Lulushi ORACLE FORMS DEVELOPER’S HANDBOOK Lulushi ORACLE FORMS INTERACTIVE WORKBOOK Motivala ORACLE SQL INTERACTIVE WORKBOOK, SECOND EDITION Rischert ORACLE PL/SQL BY EXAMPLE, THIRD EDITION Rosenzweig/Silvestrova ORACLE DBA GUIDE TO DATA WAREHOUSING AND STAR SCHEMAS Scalzo ORACLE DBA INTERACTIVE WORKBOOK Scherer/Caffrey ORACLE DEVELOPER 2000 HANDBOOK, SECOND EDITION Stowe DATA WAREHOUSING WITH ORACLE Yazdani/Wong ORACLE CERTIFIED DBA EXAM QUESTION AND ANSWER BOOK Yazdani/Wong/Tong OOrraaccllee PPLL//SSQQLL bbyy EExxaammppllee Third Edition B R ENJAMIN OSENZWEIG E S LENA ILVESTROVA PRENTICE HALL Professional Technical Reference Upper Saddle River, New Jersey 07458 www.phptr.com Library of Congress Cataloging-in-Publication Data A catalog record for this book can be obtained from the Library of Congress. Editorial/production supervision: Jessica Balch (Pine Tree Composition, Inc.) Cover design director: Jerry Votta Cover design: Nina Scuderi Art director: Gail Cocker-Bogusz Interior design: Meg Van Arsdale Manufacturing buyer: Alexis Heydt-Long Publisher: Jeff Pepper Editorial assistant: Linda Ramagnano Full-service production manager: Anne R. Garcia © 2004, 2003 Pearson Education, Inc. Publishing as Prentice Hall Professional Technical Reference Upper Saddle River, NJ 07458 Prentice Hall PTR offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales. For more information, please contact: U.S. Corporate and Government Sales 1–800–382–3419 [email protected] For sales outside of the U.S., please contact: International Sales 1–317–581–3793 [email protected] Other company and product names mentioned herein are the trademarks or registered trademarks of their respective owners. All rights reserved. No part of this book may be reproduced, in any form or by any means, without permission in writing from the publisher. Printed in the United States of America First Printing ISBN 0-13-117261-1 Pearson Education Ltd., London Pearson Education Australia Pty, Limited, Sydney Pearson Education Singapore, Pte. Ltd. Pearson Education North Asia Ltd., Hong Kong Pearson Education Canada, Ltd., Toronto Pearson Educación de Mexico, S.A. de C.V. Pearson Education–Japan, Tokyo Pearson Education Malaysia, Pte. Ltd. CONTENTS Acknowledgments xiii About the Authors xv Introduction xvii CHAPTER 1 Programming Concepts 1 LAB 1.1 The Nature of a Computer Program and Programming Languages 2 1.1.1 Understand the Nature of Computer Programs and Programming Languages 4 1.1.2 Understand the Differences Between Interpreted and Compiled Languages 6 LAB 1.2 Good Programming Practices 9 1.2.1 Understand the Nature of Good Programming Practices 15 1.2.2 Understand Formatting Guidelines 16 CHAPTER 1 Test Your Thinking 20 CHAPTER 2 PL/SQL Concepts 21 LAB 2.1 PL/SQL in Client-Server Architecture 22 2.1.1 Use PL/SQL Anonymous Blocks 28 2.1.2 Understand How PL/SQL Gets Executed 30 LAB 2.2 PL/SQL in SQL*Plus 34 2.2.1 Use Substitution Variables 39 2.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement 40 CHAPTER 2 Test Your Thinking 44 CHAPTER 3 General Programming Language Fundamentals 45 LAB 3.1 PL/SQL Programming Fundamentals 46 3.1.1 Make Use of PL/SQL Language Components 47 3.1.2 Make Use of PL/SQL Variables 48 3.1.3 Handle PL/SQL Reserved Words 50 3.1.4 Make Use of Identifiers in PL/SQL 51 v vi Contents 3.1.5 Make Use of Anchored Data Types 52 3.1.6 Declare and Initialize Variables 52 3.1.7 Understand the Scope of a Block, Nested Blocks,and Labels 54 CHAPTER 3 Test Your Thinking 66 CHAPTER 4 SQL in PL/SQL 69 LAB 4.1 Making Use of DML in PL/SQL 70 4.1.1 Use the SELECT INTO Syntax for Variable Initialization 71 4.1.2 Use DML in a PL/SQL Block 72 4.1.3 Make Use of a Sequence in a PL/SQL Block 74 LAB 4.2 Making Use of SAVEPOINT 78 4.2.1 Make Use of COMMIT,ROLLBACK, and SAVEPOINT in a PL/SQL Block 82 CHAPTER 4 Test Your Thinking 87 CHAPTER 5 Conditional Control:IF Statements 89 LAB 5.1 IF Statements 90 5.1.1 Use the IF-THEN Statement 95 5.1.2 Use the IF-THEN-ELSE Statement 96 LAB 5.2 ELSIF Statements 105 5.2.1 Use the ELSIF Statement 109 LAB 5.3 Nested IF Statements 116 5.3.1 Use Nested IF Statements 119 CHAPTER 5 Test Your Thinking 124 CHAPTER 6 Conditional Control:Case Statements 127 LAB 6.1 CASE Statements 128 6.1.1 Use the CASE Statement 135 6.1.2 Use the Searched CASE Statement 136 LAB 6.2 CASE Expressions 146 6.2.1 Use the CASE Expression 150 LAB 6.3 NULLIF and COALESCE Functions 155 6.3.1 Use the NULLIF Function 159 6.3.2 Use the COALESCE Function 161 CHAPTER 6 Test Your Thinking 168 Contents vii CHAPTER 7 Error Handling and Built-In Exceptions 171 LAB 7.1 Handling Errors 172 7.1.1 Understanding the Importance of Error Handling 175 LAB 7.2 Built-In Exceptions 179 7.2.1 Use Built-In Exceptions 185 CHAPTER 7 Test Your Thinking 191 CHAPTER 8 Iterative Control 193 LAB 8.1 Simple Loops 194 8.1.1 Use Simple Loops with EXIT Conditions 198 8.1.2 Use Simple Loops with EXIT WHEN Conditions 200 LAB 8.2 WHILE Loops 208 8.2.1 Use WHILE Loops 213 LAB 8.3 Numeric FOR Loops 219 8.3.1 Use Numeric FOR Loops with the IN Option 224 8.3.2 Use Numeric FOR Loops with the REVERSE Option 225 LAB 8.4 Nested Loops 232 8.4.1 Use Nested Loops 235 CHAPTER 8 Test Your Thinking 241 CHAPTER 9 Introduction to Cursors 243 LAB 9.1 Cursor Manipulation 244 9.1.1 Make Use of Record Types 249 9.1.2 Processing an Explicit Cursor 250 9.1.3 Making Use of Cursor Attributes 255 9.1.4 Putting It All Together 256 LAB 9.2 Using Cursor FOR Loops and Nesting Cursors 266 9.2.1 Use Cursor FOR Loops 267 9.2.2 Process Nested Cursors 267 CHAPTER 9 Test Your Thinking 274 viii Contents CHAPTER 10 Exceptions 275 LAB 10.1 Exception Scope 276 10.1.1 Understand the Scope of an Exception 279 LAB 10.2 User-Defined Exceptions 287 10.2.1 Use User-Defined Exceptions 292 LAB 10.3 Exception Propagation 298 10.3.1 Understand How Exceptions Propagate 304 10.3.2 Re-raising Exceptions 305 CHAPTER 10 Test Your Thinking 315 CHAPTER 11 Exceptions:Advanced Concepts 317 LAB 11.1 RAISE_APPLICATION_ERROR 318 11.1.1 Use RAISE_APPLICATION_ERROR 322 LAB 11.2 EXCEPTION_INIT Pragma 329 11.2.1 Use EXCEPTION_INIT Pragma 331 LAB 11.3 SQLCODE and SQLERRM 336 11.3.1 Use SQLCODE and SQLERRM 339 CHAPTER 11 Test Your Thinking 343 CHAPTER 12 Procedures 345 LAB 12.1 Creating Procedures 346 12.1.1 Create Procedures 348 12.1.2 Query the Data Dictionary for Information on Procedures 349 LAB 12.2 Passing Parameters In and Out of Procedures 352 12.2.1 Use IN and OUT Parameters with Procedures 354 CHAPTER 12 Test Your Thinking 357 CHAPTER 13 Functions 359 LAB 13.1 Creating and Using Functions 360 13.1.1 Create Stored Functions 361 13.1.2 Make Use of Functions 362 13.1.3 Invoke Functions in SQL Statements 363 13.1.4 Write Complex Functions 363 CHAPTER 13 Test Your Thinking 368 Contents ix CHAPTER 14 Packages 369 LAB 14.1 The Benefits of Utilizing Packages 370 14.1.1 Create the Package Specification 371 14.1.2 Create the Package Body 373 14.1.3 Call Stored Packages 374 14.1.4 Create Private Objects 375 14.1.5 Create Package Variables and Cursors 376 CHAPTER 14 Test Your Thinking 383 CHAPTER 15 Advanced Cursors 385 LAB 15.1 Using Parameters with Cursors and FOR UPDATE Cursors 386 15.1.1 Use Parameters in a Cursor 387 15.1.2 Use a FOR UPDATE Cursor 389 15.1.3 WHERE CURRENT OF Clause 391 LAB 15.2 Cursor Variables 395 15.2.1 Make Use of Cursor Variables 399 CHAPTER 16 Stored Code 407 LAB 16.1 Gathering Stored Code Information 408 16.1.1 Get Stored Code Information from the Data Dictionary 408 16.1.2 Enforce Purity Level with RESTRICT_REFERENCES Pragma 410 16.1.3 Overload Modules 412 CHAPTER 16 Test Your Thinking 425 CHAPTER 17 Triggers 427 LAB 17.1 What Triggers Are 428 17.1.1 Understand What a Trigger Is 433 17.1.2 Use BEFORE and AFTER Triggers 435 LAB 17.2 Types of Triggers 441 17.2.1 Use ROW and STATEMENT Triggers 447 17.2.2 Use INSTEAD OF Triggers 448 LAB 17.3 Mutating Table Issues 456 17.3.1 Understand Mutating Tables 460 CHAPTER 17 Test Your Thinking 466