About This Book

The Teach Yourself series from Sams Publishing is designed to carefully instruct readers at the beginning level of its individual topics with step-by-step examples and easy-to-follow instructions. Some topics, of course, are more complex than others and imply some requisite knowledge or experience. This book, Teach Yourself
PL/SQL in 21 Days, is aimed particularly at readers who have either some knowledge of Oracle (such as power users or database designers) or some knowledge of programming (such as non-Oracle, SQL programmers or nondatabase, C++ programmers, and so on). The goal of this book is to instruct readers how to use Oracle's proprietary extension of the Structured Query Language (SQL) to take full advantage of PL/SQL's specifically designed capabilities for interacting with data in the Oracle environment.

With the recent release of Oracle8, Oracle programmers are in increasing demand, both for new and updated systems. Understanding and using PL/SQL is at the heart of programming for any Oracle system, and this book gives you a strong, solid start in creating scripts that improve the efficiency of adding, modifying, retrieving, and generally working with data—in single, multiple-user, and online database systems.

In Teach Yourself PL/SQL in 21 Days, you'll find out the basis of PL/SQL in SQL; how to build programs and scripts—block by block; many new features of Oracle8 for PL/SQL; how to package queries for efficiency; how to use specific responses from the database (or events) to create messages for alerting purposes; how to incorporate PL/SQL into a Java program, and much more. By the time you finish your three-week course, you will know the structure, definition, and theory of the components of PL/SQL, such as statements and loops, and how they work together. You also will have on hand complete, reusable scripts to adapt for your own real-world programming needs.

Teach
Yourself
PL/SQL‰ in 21 Days

Teach Yourself PL/SQL‰ in 21 Days

Tom Luers
Timothy Atwood
Jonathan Gennick

201 West 103rd Street
Indianapolis, Indiana 46290

To my wife Cathy, and children Jon, Sarah, and Matthew
—Tom Luers

To my wife, Michelle
—Tim Atwood

To my daughter Jenny who lost her father for three months while he worked on this book. I love you, and let's schedule a "Daddy Day" soon.
—Jonathan Gennick

Copyright © 1997 by Sams Publishing

FIRST EDITION

All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein. For information, address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290.

International Standard Book Number: 0-672-31123-2

Library of Congress Catalog Card Number: 97-66672

2000 99 98 97 4 3 2 1

Interpretation of the printing code: The rightmost double-digit number is the year of the book's printing; the rightmost single digit, the number of the book's printing. For example, a printing code of 97-1 shows that the first printing of the book occurred in 1997.

Composed in AGaramond and MCPdigital by Macmillan Computer Publishing

Printed in the United States of America

Trademarks

All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark. PL/SQL is a trademark of Oracle Corporation.

President Richard K. Swadley

Publisher and Director of Acquisitions Jordan Gold

Director of Product Development Dean Miller

Executive Editor Rosemarie Graham

Managing Editor Jodi Jensen

Indexing Manager Johnna L. VanHoose

Director of Marketing Kelli S. Spencer

Product Marketing Manager Wendy Gilbride

Marketing Coordinator Linda B. Beckwith

Acquisitions Editor

Elaine Brush

Development Editor

Marla Reece

Software Development Specialist

John Warriner

Production Editor

Dana Rhodes Lesh

Copy Editor

Margaret Berson

Indexer

Christine Nelsen

Technical Reviewer

Jeff Shockley

Editorial Coordinators

Mandie Rowell

Katie Wise

Technical Edit Coordinator

Lorraine E. Schaffer

Resource Coordinators

Charlotte Clapp

Deborah Frisby

Editorial Assistants

Carol Ackerman

Andi Richter

Rhonda Tinch-Mize

Cover Designer

Tim Amrhein

Cover Illustration

Eric Lindley

Cover Production

Aren Howell

Book Designer

Gary Adair

Copy Writer

David Reichwein

Production Team Supervisors

Brad Chinn
Andrew Stone

Production

Michael Henry
Tim Osborn
Gene Redding
Ian Smith

Acknowledgments

I wish to thank my lovely wife, Cathy, and my great kids, Jon, Sarah, and Matt. Without their support, patience, and dedication, this project would not have been possible. I also wish to acknowledge the LSO sports organization and KI for providing many hours of fun for my family while they left me alone to work on this book.

—Tom Luers

I would like to thank Elaine Brush, Dana Rhodes Lesh, Marla Reece, and everyone else at Sams who worked hard to bring this book to print. Also thanks to my coauthors, Jonathan Gennick and Tom Luers, for their hard work and contributions.

I would also like to thank my wife who was my inspiration, my children Rebecca and Andrew, and my parents, Ken and Geri, for their support. Thanks to Dianne Warren for accommodating me while working on this book. I would like to thank my cat Dutchess, who kept me company the many times I saw the sunrise while working on this book. Finally, I would like to thank my good friends Glenn Koniewicz, Tom Dunk, and Greg Hodgson who still wonder if I even exist.

—Tim Atwood

Many people have put a lot of effort into making this a quality book. You can read their names on the copyright page, and they all have my heartfelt thanks. I would particularly like to thank Elaine Brush for convincing me to work on this book and Marla Reece for her always encouraging comments about my writing.

—Jonathan Gennick

Tell Us What You Think!

As a reader, you are the most important critic and commentator of our books. We value your opinion and want to know what we're doing right, what we could do better, what areas you'd like to see us publish in, and any other words of wisdom you're willing to pass our way. You can help us make strong books that meet your needs and give you the computer guidance you require.

Do you have access to the World Wide Web? Then check out our Web site at http://www.mcp.com.

As the team leader of the group that created this book, I welcome your comments. You can fax, e-mail, or write me directly to let me know what you did or didn't like about this book—as well as what we can do to make our books stronger. Here's the information: