Oracle PL / SQL For Dummies E-Book

Michael Rosenblum

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 524

Veröffentlichungsjahr: 2011

Beschreibung

Find tips for creating efficient PL/SQL code If you know a bit about SQL, this book will make PL/SQL programming painless! The Oracle has spoken--you need to get up to speed on PL/SQL programming, right? We predict it'll be a breeze with this book! You'll find out about code structures, best practices, and code naming standards, how to use conditions and loops, where to place PL/SQL code in system projects, ways to manipulate data, and more. Discover how to * Write efficient, easy-to-maintain code * Test and debug PL/SQL routines * Integrate SQL and PL/SQL * Apply PL/SQL best practices * Use new features introduced in Oracle 9i and 10g

Leseprobe

Oracle® PL/SQL For Dummies®

by Michael Rosenblum and Dr. Paul Dorsey

Oracle® PL/SQL For Dummies®

Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.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 permitted 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 trademark 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 representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained 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 further information does not mean that the author or the publisher endorses the information 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 877-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

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 conferences. 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 volunteer 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 unacknowledged “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 product. 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 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

Contents

Title

Introduction

About This Book

Foolish Assumptions

How This Book Is Organized

Icons Used in This Book

Where to Go from Here

Part I : Basic PL/SQL Concepts

Chapter 1: PL/SQL and Your Database

Knowing Just Enough about Relational Databases

The Scoop on SQL and PL/SQL

What Is PL/SQL Good For?

Chapter 2: The PL/SQL Environment

Setting Up an Oracle Environment

Hardware and Software Requirements

Accessing the Oracle Technology Network

Installing the Database

Working with Code

Establishing a Database Connection

The Basics of PL/SQL Programs

Examining the Sample Data

Part II : Getting Started with PL/SQL

Chapter 3: Laying the Groundwork: PL/SQL Fundamentals

PL/SQL As a Programming Language

Anonymous PL/SQL Blocks

Introducing the Lexical Set of Elements

Working with Constants and Variables

Building Expressions with Operators

Running Anonymous Blocks of Code

Creating Reusable PL/SQL Code

Storing PL/SQL in the Database

Checking Out PL/SQL Extras

Chapter 4: Controlling Program Flow

Creating Condition Statements

Looping the Loop

Chapter 5: Handling Exceptions

Understanding Exception Basics

Adding Exception Handlers to Your Code

Understanding Different Exception Types

Predefined Exceptions in PL/SQL Code

Adding User-Defined Exceptions

Propagation of Exceptions

Writing Exceptional Exceptions

Chapter 6: PL/SQL and SQL Working Together

Cursors: What They Are and How to Use Them

Knowing Where to Declare Cursors

Being Explicitly Smart with Implicit Cursors

Accessing Status Info by Using Cursor Variables

Updating Records Fetched from Cursors

Taking a Shortcut with CURSOR FOR Loops

Referencing Functions in SQL

Part III : Standards and Structures

Chapter 7: Putting Your Code in the Right Place

Putting Code in the Database

Putting Code in the Application Server (Middle-Tier Approach)

Where Should You Place the Business Logic?

Chapter 8: Creating Naming Standards

What’s in a Naming Standard?

Setting Naming Standards for Common Code Elements

Making Sure Your Organization Follows Standards

Chapter 9: Creating Coding Standards

Why Standards Are Important

Universal Truths

Developing SQL Code Consistently

Part IV : PL/SQL Data Manipulations

Chapter 10: Basic Datatypes

Introducing the Main Datatype Groups

Working with Numeric Datatypes

Keeping Track of Date and Time

Storing Logical Values with BOOLEAN

Processing Characters and Strings

Chapter 11: Advanced Datatypes

Handling Large Objects in the Database

Working with Large Objects

Keeping Code Consistent with User-Defined Subtypes

Defining Your Own Datatypes

Grouping Sets of Data into Collections

Speeding Up Data Collection with Bulk Operations

Part V : Taking PL/SQL to the Next Level

Chapter 12: Transaction Control

Using Transactions to Maintain Data Consistency

Committing or Rolling Back Changes to the Database

Autonomous Transactions

Chapter 13: Dynamic SQL and PL/SQL

Taking Advantage of Dynamic SQL

A Simple EXECUTE IMMEDIATE

Building SQL on the Fly

Building DDL on the Fly

Using Quoted Strings with Dynamic SQL

Working with Advanced Datatypes

Chapter 14: PL/SQL Best Practices

Why Are Best Practices Important?

Laying the Groundwork for Good Coding

Writing Code with Best Practices in Mind

Testing Your Code

Coding the Agile Way

Keeping Up-to-Date with Oracle

Part VI : The Part of Tens

Chapter 15: Ten PL/SQL Tips

Use SELECT INTO Correctly

Don’t Assume Column Order in DML Statements

Use the NOCOPY Command

Be Careful of Function Calls That Impact Performance

Beware of Implicit Commits

Use Only One RETURN Statement per Function

Save Debugging Time with WHEN OTHERS

Know When to Pass Parameters or Use Global Variables

Use Implicit Cursors When Appropriate

Remember That Dates Are Numbers

Chapter 16: Ten Common Mistakes to Avoid in PL/SQL

Catching an Exception with WHEN OTHERS THEN NULL

Forgetting to Handle NULL Values

Creating Unintended Boolean Expressions

Forgetting to Close an Explicit Cursor

Starting Endless Loops

Reinventing the Wheel

Converting Datatypes Implicitly

Cutting and Pasting Code

Ignoring Code Readability

Assuming Code Doesn’t Need Comments

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 contemplating 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 database. 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.

Foolish Assumptions

This book is written for people just getting started with the PL/SQL language but does assume that you have some programming language experience. You should understand the basics of computer programming and be familiar with SQL in order to benefit from the information contained in this book.

If you have some basic computer programming experience and are planning to work in the Oracle environment as an application developer, this book is for you. If your goal is to become a database administrator (DBA), it might be a good additional reference, but you should see Oracle 9i For Dummies (latest version as of this writing), by Carol McCullough-Dieter, published by Wiley, or other books about Oracle 10g for information relevant for DBAs.

When an author writes a book, he or she must try to imagine who the readers might be. For this book, we imagine that you might be an individual who recently graduated from college and who is working at a job where you’ll be creating programs in an Oracle environment. This book will also be useful as part of a database course if you are still in college. You might be an experienced Java developer who wants an easy way to get to know some of the basics of PL/SQL. This book includes the information that we would want to tell any new colleagues who came to work with us.

How This Book Is Organized

You might not need to read the chapters of this book in sequence. However, Parts I and II present the basic concepts you’ll need to understand later parts. If you’re already an experienced programmer, the basic concepts might be familiar, but you’ll need to see how to specifically apply them in PL/SQL.

Part I: Basic PL/SQL Concepts

This part provides an introduction to the Oracle environment as a whole and explains how PL/SQL fits into it. It includes an overview of how relational databases work as well as a review of some basic database concepts and terminology that you need to understand for later chapters. We tell you about the hardware and software needed to set up a simple Oracle database and some useful Integrated Development Environments (IDEs) available to help you practice using PL/SQL.

Part II: Getting Started with PL/SQL

In this part, you discover the different structures and ways to create code to accomplish common programming tasks. Chapter 3 includes descriptions of the datatypes and variables that you’ll need to work with, and extras to enhance your coding expertise. Chapter 4 delves into control structures and provides examples of how to use conditions and loops correctly. In Chapter 5, you find out how to structure code and handle errors and exceptions. And you can’t be a good PL/SQL programmer without understanding a bit about SQL, so Chapter 6 discusses how SQL and PL/SQL can work together and introduces cursors. (If you don’t know any SQL, this isn’t the book for you quite yet. First read SQL For Dummies, 5th Edition, by Allan G. Taylor.)

Part III: Standards and Structures

In this part, we discuss standards and guidelines for writing good code — some of the most important material in the book. Chapter 7 presents the various options for where to place PL/SQL code in a system project. In Chapters 8 and 9, you find out about the importance of creating and enforcing both naming and coding standards. The sample standards shown are based on our experiences in building large production systems.

Part IV: PL/SQL Data Manipulations

This part goes into more detail about different datatypes and how to use them. We cover basic datatypes in Chapter 10 and discuss more advanced constructs (using large objects, collections, and bulk operations) in Chapter 11.

Part V: Taking PL/SQL to the Next Level

For those who have some basic experience with PL/SQL, Part V discusses more complex concepts, including database interaction and transaction control in Chapter 12 and using dynamic SQL and PL/SQL in Chapter 13. Chapter 14 lists some PL/SQL coding best practices to follow based on our wide experiences in building working systems.

Part VI: The Part of Tens

This part summarizes some critical do’s and don’ts to keep in mind as you create PL/SQL programs. Our advice provides useful tips to assist your first programming attempts as well as describes pitfalls to avoid.

Icons Used in This Book

These tips provide shortcuts or easier and better ways of doing things.

This icon notes information that’s important to keep in mind when writing code, or that we’ve found very useful in our work building real systems.

This icon appears when we discuss advanced concepts or additional information that isn’t essential to your understanding of PL/SQL but might be useful for doing additional reading or investigation.

This icon warns you about things in your code that can cause problems or even stop a system from working properly.

Where to Go from Here

There are many sources of information about computer programming and PL/SQL. This is merely a starting point for you to use as a list of additional helpful information about PL/SQL to expand your knowledge. You can’t program successfully in isolation. It is very important to be plugged into the broader Oracle community through local user groups, conferences, the Internet, and other PL/SQL programmers.

Many useful sources of information about PL/SQL and the Oracle Environ- ment are on the Internet. Keep in mind that not all information and code samples on the Internet are accurate and bug free. Always be aware of the source of the information and test any code carefully before deploying it. The following are two particularly useful Web sites for obtaining the latest information about PL/SQL:

Oracle Technology Network (OTN) (www.otn.oracle.com) is Oracle’s online resource for all its database and application products. See especially the special technology section devoted to PL/SQL (www.oracle.com/technology/tech/pl_sql/index.html). Chapter 2 has more details.

Steven Feuerstein writes often about PL/SQL. His Web site (www.stevenfeuerstein.com) is a great resource.

The many Oracle User Groups can provide lots of helpful information and events for Oracle professionals. By attending a user group conference, you can discover more about the Oracle environment.

Oracle Development Tools User Group (ODTUG) (www.odtug.com) is the leading Oracle user group for developers. It focuses on the tools that developers use to build Oracle systems (not just tools sold by Oracle).

Independent Oracle Users Group (IOUG) (www.ioug.org) is a worldwide organization that encompasses many regional user groups in the United States and abroad. To find a regional group in your area, do an online search for “Oracle User Groups”.

Be sure to join the user group nearest you and go to meetings, talk to other PL/SQL developers, and connect with other Oracle professionals. That is the best way to keep up with the rapidly changing Oracle environment.

Part I

Basic PL/SQL Concepts

In this part . . .

Part I includes two chapters to get you started with PL/SQL. Because you need to understand something about relational databases to be a good PL/SQL programmer, Chapter 1 provides a quick overview of the most important concepts. It also reviews some database terminology and explains some of the differences between SQL and PL/SQL. For those with very little programming experience, Chapter 1 includes a very brief explanation of the basic structure and syntax of PL/SQL and explains where it is most useful.

Chapter 2 describes the total PL/SQL environment and explains how to set up this environment so you can begin writing code. You discover how to set up a database, connect to it, and access the Oracle sample schemas that you can use to practice.

Chapter 1

PL/SQL and Your Database

In This Chapter

Getting to know relational databases

Understanding database terminology

Finding out about Oracle

Using SQL and PL/SQL

Discovering what PL/SQL is good for

PL/SQL is an extension to the industry-standard SQL language. Oracle Corporation developed PL/SQL and released the first version in 1991. PL/SQL is an easy-to-use procedural language that interacts seamlessly with the Oracle database. Server-side PL/SQL is part of the Oracle database and needs no explicit installation or licensing.

This chapter introduces you to PL/SQL and provides some basics about relational databases.

Knowing Just Enough about Relational Databases

Building a system in Oracle or some other relational database product does not automatically make it a relational database. Similarly, you can design a perfectly good relational database and implement it in something other than a relational database product. We discuss two important areas:

What do people mean by relational database?

What is the Oracle relational database product?

What makes a database “relational”?

When a database is described as relational, it has been designed to conform (at least mostly) to a set of practices called the rules of normalization. A normalized database is one that follows the rules of normalization.

For example, in an organization, you have employees who work in specific departments. Each employee and department has a number and a name. You could organize this information as shown in Table 1-1.

If you structure your data this way and make certain changes to it, you’ll have problems. For example, deleting all the employees in the Purchasing department will eliminate the department itself. If you change the name of the Marketing department to “Advertising,” you would need to change the record of each employee in that department.

Using the principles of relational databases, the Employee and Department data can be restructured into two separate tables (DEPT and EMP), as shown in Tables 1-2 and 1-3.

Table 1-2 A Sample Relational DEPT TableDeptNo DeptName10 Marketing20