Oracle PL - SQL For Dummies (ISBN - 0764599577)

435 Pages·2021·2.73 MB·English

by Michael Rosenblum and Dr.Paul Dorsey

Oracle

®

PL/SQL

FOR

DUMmIES

‰

01_599577 ffirs.qxp 5/1/06 12:08 PM Page i

01_599577 ffirs.qxp 5/1/06 12:08 PM Page iv

by Michael Rosenblum and Dr.Paul Dorsey

Oracle

®

PL/SQL

FOR

DUMmIES

‰

01_599577 ffirs.qxp 5/1/06 12:08 PM Page i

Oracle® PL/SQL For Dummies®
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com

Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or
by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-
ted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written
permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the
Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone].
Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing,
Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at
http://www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the
Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade
dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United
States and other countries, and may not be used without written permission. Oracle is a registered trade-
mark of Oracle Corporation. All other trademarks are the property of their respective owners. Wiley
Publishing, Inc., is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REP-
RESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CON-
TENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT
LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CRE-
ATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CON-
TAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE
UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR
OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A
COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE
AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION
OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FUR-
THER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFOR-
MATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE.
FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE
CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services, please contact our Customer Care
Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

For technical support, please visit www.wiley.com/techsupport.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may
not be available in electronic books.

Library of Congress Control Number: 2006922426

ISBN-13: 978-0-7645-9957-6

ISBN-10: 0-7645-9957-7

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1O/RX/QV/QW/IN

01_599577 ffirs.qxp 5/1/06 12:08 PM Page ii

About the Authors

Michael Rosenblum is originally from Kremenchuk, Ukraine. In 2000, he moved
to the United States, where he lives with his family in Edison, New Jersey. He
works as a Development DBA at Dulcian, Inc. Michael is responsible for system
tuning and application architecture. He also supports Dulcian developers
by writing complex PL/SQL routines and researching new features. He is a
frequent presenter at various regional and national Oracle user group confer-
ences. In his native Ukraine, he received the scholarship of the President of
Ukraine, a Masters Degree in Information Systems, and a Diploma with Honors
from the Kiev National University of Economics, Ukraine.

Dr. Paul Dorsey is the founder and President of Dulcian, Inc. (www.dulcian.
com), an Oracle consulting firm that specializes in business rules-based
Oracle Client-Server and Web custom application development. He is the
chief architect of Dulcian’s Business Rules Information Manager (BRIM®) tool.
Paul is the co-author of seven Oracle Press books that have been translated
into nine languages: Oracle JDeveloper 10g Handbook, Oracle9i JDeveloper
Handbook, Oracle JDeveloper 3 Handbook, Oracle Designer Handbook (2
editions), Oracle Developer Forms and Reports: Advanced Techniques and
Development Standards, Oracle8 Design Using UML Object Modeling. In 2003,
he was honored by ODTUG as volunteer of the year, in 2001 by IOUG as vol-
unteer of the year and by Oracle as one of the six initial honorary Oracle 9i
Certified Masters. Paul is an Oracle Fusion Middleware Regional Director. He
is the President of the New York Oracle Users’ Group and a Contributing
Editor of the International Oracle User Group’s SELECT Journal. He is also
the founder and chairperson of the ODTUG Business Rules Symposium
(now called Best Practices Symposium), currently in its sixth year, and
the J2EE SIG.

Dedications

Dedicated to the memory of my grandfather, Vladimir Zaguskin, who was
always able to give me a simple explanation of not-so-simple things.

— Michael Rosenblum, Edison, NJ, 2006

Dedicated to Dr. Robert Stafford Sterling (my mentor in graduate school and
no dummy) and to his namesake Robert Stefan Dorsey (born December 2005,
who I hope will also not be a dummy).

— Dr. Paul Dorsey, Colonia, NJ, 2006

01_599577 ffirs.qxp 5/1/06 12:08 PM Page iii

01_599577 ffirs.qxp 5/1/06 12:08 PM Page iv

Authors’ Acknowledgments

Michael Rosenblum: I would like to thank my co-author, Dr. Paul Dorsey (a
well-known guru in the Oracle world), for inviting me to take part in writing
this book and for all his patience working with me for the last five years. Also,
I would like to acknowledge the efforts of our project manager, Caryl Lee
Fisher. She not only kept the lazy authors on track, but even managed to
convert my not-exactly-native English into something people could read. Of
course, the book in the form you are reading it would not have been possible
without our wonderful technical editor Leslie Tierstein (you can’t imagine the
number of small “bugs” she discovered in the original drafts). And, last but
not least, love and special thanks to my wife Dora for withstanding the added
pressure on her while I was writing this book.

Dr. Paul Dorsey: I would first like to acknowledge my co-author Michael
(“Misha”) Rosenblum. It is a joy to work with someone possessing such drive
and intellect. His unwillingness to be sloppy in his code and thinking have
given not only this book, but all his work, an aspect of excellence all too
unusual in this industry. I would also like to thank Caryl Lee Fisher (our unac-
knowledged “co-author”). Caryl Lee kept Misha and me on track and helped
wordsmith the entire manuscript. This is my eighth such collaboration
involving Caryl Lee, and I can safely say that I am not sure whether I ever
would have published even my first book without her assistance. She acted
as the intermediary between the excellent editors at Wiley and the authors,
thereby averting virtually certain bloodshed. She helped to foster the illusion
that we are very easy authors to work with. Leslie Tierstein provided her
always impeccable technical edits. I have worked with her on a number of
projects, and she provides many valuable contributions to the finished prod-
uct. A special thank you goes to my lovely wife Ileana. She not only endured
all my time away from home (for the 3 months preceding and following my
son’s birth) working on this book, but she also provided serious technical
assistance, since she is a first-rate developer in her own right.

Both authors would like to thank their colleagues Mark Hernandez, Marc
Bacchus, John Rydzy, and Stephen Germany for their help in reviewing the
code samples and text for accuracy.

The authors would also like to thank the Wiley team of Rebecca Huehls,
Virginia Sanders, Tiffany Ma, and Terri Varveris for their help with this project.

01_599577 ffirs.qxp 5/1/06 12:08 PM Page v

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form
located at www.dummies.com/register/.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and
Media Development

Project Editor: Rebecca Huehls

Acquisitions Editors: Tiffany Ma, Terri Varveris

Copy Editor: Virginia Sanders

Technical Editor: Leslie Tierstein

Editorial Manager: Leah P. Cameron

Media Development Manager: Laura VanWinkle

Editorial Assistant: Amanda Foxworth

Cartoons: Rich Tennant (www.the5thwave.com)

Composition Services

Project Coordinator: Patrick Redmond

Layout and Graphics: Claudia Bell, Carl Byers,

Denny Hager, Alicia B. South

Proofreaders: Dwight Ramsey, Techbooks

Indexer: Techbooks

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

01_599577 ffirs.qxp 5/1/06 12:08 PM Page vi

Contents at a Glance

Introduction .................................................................1

Part I: Basic PL/SQL Concepts.......................................7

Chapter 1: PL/SQL and Your Database ............................................................................9
Chapter 2: The PL/SQL Environment.............................................................................23
Part II: Getting Started with PL/SQL............................39

Chapter 3: Laying the Groundwork: PL/SQL Fundamentals .......................................41
Chapter 4: Controlling Program Flow ............................................................................85
Chapter 5: Handling Exceptions ...................................................................................105
Chapter 6: PL/SQL and SQL Working Together...........................................................127
Part III: Standards and Structures .............................163

Chapter 7: Putting Your Code in the Right Place........................................................165
Chapter 8: Creating Naming Standards .......................................................................187
Chapter 9: Creating Coding Standards ........................................................................201
Part IV: PL/SQL Data Manipulations ..........................219

Chapter 10: Basic Datatypes.........................................................................................221
Chapter 11: Advanced Datatypes.................................................................................253
Part V: Taking PL/SQL to the Next Level.....................289

Chapter 12: Transaction Control..................................................................................291
Chapter 13: Dynamic SQL and PL/SQL ........................................................................313
Chapter 14: PL/SQL Best Practices .............................................................................335
Part VI: The Part of Tens ...........................................355

Chapter 15: Ten PL/SQL Tips ........................................................................................357
Chapter 16: Ten Common Mistakes to Avoid in PL/SQL............................................377
Index .......................................................................397

02_599577 ftoc.qxp 5/1/06 12:09 PM Page vii

02_599577 ftoc.qxp 5/1/06 12:09 PM Page viii