Oracle ®
PL/SQL
FOR
DUMmIES
‰
by Michael Rosenblum and Dr. Paul Dorsey
Oracle ®
PL/SQL
FOR
DUMmIES
‰
by Michael Rosenblum and Dr. Paul Dorsey
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, (978) 750-8400, fax (978) 646-8600.
Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing,
Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, 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 317-572-4002.
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
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
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.
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 Composition Services
Media Development Project Coordinator: Patrick Redmond
Project Editor: Rebecca Huehls Layout and Graphics: Claudia Bell, Carl Byers,
Acquisitions Editors: Tiffany Ma, Terri Varveris Denny Hager, Alicia B. South
Copy Editor: Virginia Sanders Proofreaders: Dwight Ramsey, Techbooks
Technical Editor: Leslie Tierstein Indexer: Techbooks
Editorial Manager: Leah P. Cameron
Media Development Manager: Laura VanWinkle
Editorial Assistant: Amanda Foxworth
Cartoons: Rich Tennant (www.the5thwave.com)
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
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
Table of Contents
Introduction ..................................................................1
About This Book...............................................................................................1
Foolish Assumptions .......................................................................................2
How This Book Is Organized...........................................................................2
Part I: Basic PL/SQL Concepts ..............................................................2
Part II: Getting Started with PL/SQL.....................................................3
Part III: Standards and Structures ........................................................3
Part IV: PL/SQL Data Manipulations ....................................................3
Part V: Taking PL/SQL to the Next Level .............................................3
Part VI: The Part of Tens .......................................................................4
Icons Used in This Book..................................................................................4
Where to Go from Here....................................................................................4
Part I: Basic PL/SQL Concepts........................................7
Chapter 1: PL/SQL and Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
Knowing Just Enough about Relational Databases......................................9
What makes a database “relational”? ................................................10
Understanding basic database terminology .....................................12
Introducing database normalization..................................................13
What is a DBMS? ...................................................................................16
The Scoop on SQL and PL/SQL.....................................................................16
The purpose of SQL and PL/SQL ........................................................17
The difference between SQL and PL/SQL..........................................18
What’s new in Oracle SQL and PL/SQL? ............................................18
What Is PL/SQL Good For? ............................................................................19
Using database triggers .......................................................................19
Scripting with speed ............................................................................20
Keeping code server-side ....................................................................20
Programming for Oracle Developer ...................................................21
Chapter 2: The PL/SQL Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23
Setting Up an Oracle Environment...............................................................23
Hardware and Software Requirements........................................................25
Accessing the Oracle Technology Network ................................................26
Installing the Database ..................................................................................27
Working with Code.........................................................................................28
SQL*Plus................................................................................................28
Oracle SQL Developer..........................................................................30
Third-party tools ..................................................................................30
x Oracle PL/SQL For Dummies
Establishing a Database Connection ...........................................................31
Checking the operating services ........................................................32
Connecting with your username ........................................................32
Unlocking (or locking) accounts ........................................................33
Resetting passwords ............................................................................33
Setting up the server to communicate ..............................................34
The Basics of PL/SQL Programs...................................................................34
Writing a simple program....................................................................34
Creating stored procedures ................................................................36
Passing parameters to procedures ....................................................36
Examining the Sample Data ..........................................................................37
The Scott/Tiger schema ......................................................................37
The Human Resources (HR) and Order Entry (OE) schemas.........38
Part II: Getting Started with PL/SQL.............................39
Chapter 3: Laying the Groundwork: PL/SQL Fundamentals . . . . . . . . .41
PL/SQL As a Programming Language ..........................................................41
Anonymous PL/SQL Blocks...........................................................................42
Introducing the Lexical Set of Elements......................................................43
Identifiers ..............................................................................................44
Delimiters ..............................................................................................44
Literals ...................................................................................................45
Comments .............................................................................................45
Working with Constants and Variables........................................................46
Declaring variables...............................................................................46
Assigning values to variables .............................................................48
Literals as variable values...................................................................49
Understanding the scope of variables...............................................51
Building Expressions with Operators..........................................................53
Running Anonymous Blocks of Code...........................................................56
Identifying common mistakes.............................................................56
Spotting compilation errors................................................................57
Recognizing semicolon-related errors ...............................................57
Creating Reusable PL/SQL Code ..................................................................59
Wrapping a task into a procedure ......................................................59
Returning values with functions.........................................................61
Parameters of subprograms ...............................................................63
Storing PL/SQL in the Database ...................................................................67
Database procedures and functions ..................................................68
Packages ................................................................................................69
Triggers..................................................................................................71
Interpreting and fixing compilation errors........................................73
Table of Contents xi
Checking Out PL/SQL Extras ........................................................................76
Overloading calls..................................................................................76
Resolving calls to subprograms .........................................................78
Recursion ..............................................................................................80
Compiler hints and directives ............................................................82
Built-in packages ..................................................................................83
Chapter 4: Controlling Program Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . .85
Creating Condition Statements ....................................................................85
IF...THEN statements ............................................................................86
IF...ELSE statements .............................................................................87
CASE statements...................................................................................89
Comparing with NULL..........................................................................91
Handling conditions .............................................................................95
Looping the Loop ...........................................................................................97
Simple loops..........................................................................................97
Nested loops .........................................................................................99
WHILE loop..........................................................................................100
FOR loop ..............................................................................................102
Chapter 5: Handling Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .105
Understanding Exception Basics ...............................................................106
Adding Exception Handlers to Your Code ................................................107
Understanding Different Exception Types................................................110
Predefined Exceptions in PL/SQL Code.....................................................111
Adding User-Defined Exceptions................................................................114
Assigning a code to a user-defined exception ................................115
Including error messages in user-defined exceptions ...................116
Propagation of Exceptions ..........................................................................118
Seeing propagation of exceptions in action....................................118
Handling exceptions without halting the program ........................122
Avoiding exceptions raised in declaration part
and exception handler ...................................................................124
Writing Exceptional Exceptions .................................................................126
Chapter 6: PL/SQL and SQL Working Together . . . . . . . . . . . . . . . . . . .127
Cursors: What They Are and How to Use Them ......................................128
Returning more than one piece of information ..............................129
Looping through multiple records ...................................................132
Placing cursors in nested loops .......................................................133
Passing parameters to cursors.........................................................134
Knowing Where to Declare Cursors...........................................................137
Defining cursors in the header of the program unit ......................138
Defining cursors in the local PL/SQL block.....................................138
Defining cursors in the package body .............................................139
Defining cursors in the package spec ..............................................140
xii Oracle PL/SQL For Dummies
Being Explicitly Smart with Implicit Cursors............................................142
Retrieving a single row: The basic syntax.......................................142
Handling exceptions in implicit cursors .........................................143
Returning an implicit cursor into a record .....................................143
Accessing Status Info by Using Cursor Variables.....................................144
Checking the status of explicit cursors ...........................................145
Checking the status of implicit cursors...........................................146
Updating Records Fetched from Cursors..................................................148
Using a simple UPDATE statement...................................................148
Updating with logical operators.......................................................148
Taking a Shortcut with CURSOR FOR Loops.............................................150
Comparing CURSOR FOR loops to cursors
with the LOOP command...............................................................150
When do CURSOR FOR loops simplify exception handling?.........152
When CURSOR FOR loops make your life harder...........................153
Knowing what record is processing.................................................155
Referencing Functions in SQL.....................................................................155
Important facts to remember............................................................157
Getting good performance with functions ......................................160
Part III: Standards and Structures ..............................163
Chapter 7: Putting Your Code in the Right Place . . . . . . . . . . . . . . . . .165
Putting Code in the Database .....................................................................165
Managing code....................................................................................166
Packaging code in the database .......................................................166
Placing triggers on tables..................................................................174
Building INSTEAD OF trigger views..................................................177
Advantages of putting code in the database ..................................182
Disadvantages of putting code in the database .............................182
Putting Code in the Application Server (Middle-Tier Approach) ..........183
Advantages of the middle-tier approach.........................................184
Disadvantages of the middle-tier approach....................................184
Placing code in the view layer ..........................................................185
Where Should You Place the Business Logic? ..........................................185
Chapter 8: Creating Naming Standards . . . . . . . . . . . . . . . . . . . . . . . . .187
What’s in a Naming Standard? ....................................................................187
Oracle database influences ...............................................................188
Java influences....................................................................................188
Modern application development tools
and their influences........................................................................189
Setting Naming Standards for Common Code Elements .........................189
Basic objects .......................................................................................190
Variables ..............................................................................................191
Table of Contents xiii
Program units: Procedures, packages, functions, triggers ...........193
Parameters in code objects ..............................................................194
Exceptions...........................................................................................196
User-defined datatypes......................................................................196
Collections...........................................................................................197
Filenames.............................................................................................198
Making Sure Your Organization Follows Standards .................................199
Chapter 9: Creating Coding Standards . . . . . . . . . . . . . . . . . . . . . . . . . .201
Why Standards Are Important....................................................................201
Universal Truths...........................................................................................202
Don’t hard-code any constant value ................................................202
Don’t make your program units too big or too small ....................205
Put each data element on its own line.............................................206
Too many comments are much better than
too few comments ..........................................................................207
Avoid global variables .......................................................................210
Indent carefully...................................................................................210
Be careful with capitalization ...........................................................211
Use generic variable datatype declarations ...................................211
Limit line length..................................................................................213
Use explicit data conversion for dates ............................................213
Use synonyms.....................................................................................213
Developing SQL Code Consistently ...........................................................214
Using a new line..................................................................................214
Using explicit column lists ................................................................214
Prefixing (and suffixing) column names
from multiple tables ......................................................................215
Giving columns aliases ......................................................................216
Using parentheses in complex mathematical
and logical expressions .................................................................216
Using white space intelligently.........................................................217
Writing save exception handlers ......................................................217
Packaging stored program units.......................................................217
Part IV: PL/SQL Data Manipulations...........................219
Chapter 10: Basic Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .221
Introducing the Main Datatype Groups.....................................................221
Working with Numeric Datatypes ..............................................................222
Using the NUMBER datatype ............................................................222
Boosting performance with BINARY_INTEGER ..............................225
Using BINARY_FLOAT and BINARY_DOUBLE
for complex calculations ...............................................................226
xiv Oracle PL/SQL For Dummies
Handling numeric datatypes in built-in functions..........................228
Keeping Track of Date and Time ................................................................229
Selecting the info you want from DATE ...........................................229
Using TIMESTAMP ..............................................................................234
Using TIMESTAMP WITH TIME ZONE ..............................................235
Storing elapsed time with INTERVAL ...............................................236
Working with dates and built-in functions ......................................237
Storing Logical Values with BOOLEAN ......................................................241
Processing Characters and Strings ............................................................242
Limiting variable length with CHAR versus VARCHAR2 ................242
Useful character built-in functions...................................................244
Extending your options with regular expressions .........................250
Chapter 11: Advanced Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .253
Handling Large Objects in the Database ...................................................253
Using internal large objects (CLOB, BLOB) ....................................254
Creating pointers with external large objects ................................254
Working with Large Objects........................................................................255
Populating BFILE ................................................................................255
Loading data to the CLOB by using BFILE.......................................256
Loading a page to a BLOB .................................................................257
Performing basic string operations on CLOBs ...............................258
Keeping Code Consistent with User-Defined Subtypes...........................259
Defining Your Own Datatypes.....................................................................260
Records................................................................................................261
Object types ........................................................................................267
Grouping Sets of Data into Collections......................................................271
Using VARRAYs ...................................................................................272
Nesting variable data sets in tables .................................................275
Associative arrays (index-by tables) ...............................................280
Speeding Up Data Collection with Bulk Operations ................................283
Using the BULK COLLECT command...............................................284
Adding a limit to BULK COLLECT.....................................................286
Part V: Taking PL/SQL to the Next Level .....................289
Chapter 12: Transaction Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .291
Using Transactions to Maintain Data Consistency ..................................292
Committing or Rolling Back Changes to the Database............................293
Firing explicit and implicit commits ................................................294
Rolling back changes .........................................................................294
Knowing when to use implicit rollbacks .........................................297
Resolving deadlocks ..........................................................................298
Table of Contents xv
Autonomous Transactions..........................................................................298
Setting up the syntax for an autonomous transaction ..................299
Handling auditing and security with autonomous
transactions.....................................................................................300
Autonomous transaction features....................................................302
Applying autonomous transactions to other real-world
situations .........................................................................................308
Chapter 13: Dynamic SQL and PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . .313
Taking Advantage of Dynamic SQL ............................................................313
A Simple EXECUTE IMMEDIATE .................................................................314
Building SQL on the Fly ...............................................................................316
Improve performance with bind variables......................................317
Return information using cursor variables.....................................324
Building DDL on the Fly...............................................................................325
Using Quoted Strings with Dynamic SQL ..................................................327
Working with Advanced Datatypes............................................................328
Using BULK COLLECT with dynamic SQL .......................................329
Dynamic OPEN...FOR .........................................................................330
Chapter 14: PL/SQL Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . .335
Why Are Best Practices Important?...........................................................335
Laying the Groundwork for Good Coding .................................................336
Understanding the big picture..........................................................336
Communicating effectively................................................................337
Creating a code specification............................................................337
Writing Code with Best Practices in Mind ................................................338
Stub out your code.............................................................................338
Check the architecture as you go.....................................................339
Prove code works with test cases....................................................340
Use code libraries...............................................................................340
Keep the code maintainable .............................................................341
Don’t forget about performance.......................................................341
Compile as you go ..............................................................................341
Debug the timesaving way ................................................................342
Testing Your Code ........................................................................................343
What are you testing? ........................................................................345
Creating a testing architecture .........................................................345
Performance and load testing...........................................................345
Tuning performance ..........................................................................346
“Good enough is best” .......................................................................348
Coding the Agile Way ...................................................................................349
Working together in Agile teams ......................................................349
Programming in pairs ........................................................................350
Delivering code quickly .....................................................................350
Test first...............................................................................................351
xvi Oracle PL/SQL For Dummies
Keeping Up-to-Date with Oracle .................................................................352
Conventional wisdom isn’t always right .........................................352
Buy books............................................................................................353
Go to conferences...............................................................................353
Join your local Oracle user group ....................................................354
Use online resources..........................................................................354
Part VI: The Part of Tens ............................................355
Chapter 15: Ten PL/SQL Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .357
Use SELECT INTO Correctly .......................................................................357
Don’t Assume Column Order in DML Statements ....................................360
Use the NOCOPY Command .......................................................................362
Be Careful of Function Calls That Impact Performance ..........................364
Beware of Implicit Commits........................................................................366
Use Only One RETURN Statement per Function ......................................369
Save Debugging Time with WHEN OTHERS ..............................................370
Know When to Pass Parameters or Use Global Variables.......................372
Use Implicit Cursors When Appropriate ...................................................373
Remember That Dates Are Numbers.........................................................375
Chapter 16: Ten Common Mistakes to Avoid in PL/SQL . . . . . . . . . . .377
Catching an Exception with WHEN OTHERS THEN NULL ......................377
Forgetting to Handle NULL Values .............................................................379
Creating Unintended Boolean Expressions ..............................................380
Forgetting to Close an Explicit Cursor ......................................................382
Starting Endless Loops................................................................................384
Reinventing the Wheel.................................................................................386
Converting Datatypes Implicitly ................................................................388
Cutting and Pasting Code............................................................................391
Ignoring Code Readability...........................................................................393
Assuming Code Doesn’t Need Comments.................................................395
Index........................................................................397
Introduction
P L/SQL (pronounced P-L-S-Q-L or P-L-see-quel) is a programming language
that was created by Oracle as a procedural language fully integrated
with SQL, to be used in working with Oracle databases. Anyone contemplat-
ing working in the Oracle environment needs to know PL/SQL.
In many ways, PL/SQL is a standard procedural programming language. It has
functions, procedures, variable declarations, loops, recursion, and so on. If
you’re familiar with the programming language Ada, you’ll find it’s similar to
PL/SQL. If you’ve used any standard programming language, such as C or
Pascal, you’ll find yourself quite at home with PL/SQL. If you’re an object-
oriented (OO) programmer who is used to languages like C++ or Java, you
can’t use your OO techniques as easily in PL/SQL, but all your procedural
experience will transfer quite easily.
What makes PL/SQL special is that it is fully integrated with SQL. This makes
it a wonderful language to use when programming applications that need to
interact with an Oracle database.
About This Book
This book doesn’t include everything you need to know about PL/SQL.
However, it does provide many examples of good coding practice, and it
warns you about what to avoid when writing code. We are experienced
Oracle professionals who have designed and built many working systems and
have written many thousands of lines of PL/SQL code. The information we
provide in this book should whet your appetite for discovering many of the
ways in which you can use PL/SQL to communicate with a relational data-
base. We also point you to numerous other handy resources that can help
you consolidate and expand your knowledge of this programming language.
This book gives you the core information that every PL/SQL developer
should know. When we started writing this book, we asked many of our
friends and colleagues in the industry what they thought PL/SQL developers
should know. If you practice everything we show you in this book, you’ll be
on your way to becoming an excellent developer.
To help you practice, we’ve posted the code examples that appear in this
book on our Web site, www.dulcian.com (click the publications link),
or at www.dummies.com/go/oracle_pl_sql.
Copyright by webtailieu.net