Learning Oracle PL/SQL

524 Pages·2001·5.865 MB·English

by  Bill Pribyl, Steven Feuerstein

Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
Database > Learning Oracle PL/SQL
Learning Oracle PL/SQL
Bill Pribyl
Steven Feuerstein
Publisher: O'Reilly
First Edition December 2001
ISBN: 0-596-00180-0, 424 pages
Summary
Designed for both new programmers and those experienced in
Table of Contents
other languages, this book presents the core features of
Index
Oracle's PL/SQL language in an easy-to-read format. Learning
Examples
Oracle PL/SQL will bring programmers up to speed on the
Errata
most important aspects of PL/SQL, including web and Internet
The Editor's Website programming. Updated through Oracle 9i, includes sample
programs downloadable from http://oracle.oreilly.com.
About the Authors
Full Description
Reviews
Reader reviews Full Description
Colophon
Copyright
PL/SQL, Oracle's programming language for stored
procedures, delivers a world of possibilities for your database
programs. PL/SQL supplements the standard relational
database language, SQL, with a wide range of procedural
features, including loops, IF-THEN statements, advanced data
structures, and rich transactional control--all closely integrated
with the Oracle database server.
Knowing where to start with Oracle's procedural language is
not always obvious to a newcomer, especially considering the
language's feature set and the sheer size of the official
documentation (not to mention Oracle's ever-increasing
number of pre-built PL/SQL programs). But Learning Oracle
PL/SQL offers the signposts and guidance you need to come
up to speed on the language, delivered in a manageable
number of pages while covering all the essentials.
Topics include:
l PL/SQL--what is it, and why use it? Why use PL/SQL
instead of Java?
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/default.html (1 of 4) [15/05/2002 22:43:01]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
l Syntax and examples of all core language constructs
l Creating, using, and reusing stored procedures,
functions, and packages
l Building web-based applications using PL/SQL features
available "out of the box" (such as PL/SQL Server
Pages)
l Securing PL/SQL programs against attack
l Benefits of third-party developer tools and integrated
development environments
l Connecting PL/SQL to email, Java, and the Internet
Meticulously crafted with all-new examples downloadable from
examples.oreilly.com/learnoracle, the book addresses
language features available in all versions of Oracle, from
Oracle7 to Oracle8i to Oracle9i.
Learning Oracle PL/SQL was written by PL/SQL experts Bill
Pribyl and Steven Feuerstein, whose easy-to-read style and
attention to detail has made other O'Reilly books (such as the
bestselling Oracle PL/SQL Programming) very popular among
Oracle developers worldwide. Learning Oracle PL/SQL is
meant for a wide range of target audiences, including both
beginning programmers and those already experienced with
other programming languages. Whether you are a new
developer, a crossover programmer from another database
system, or a new database administrator who needs to learn
PL/SQL, this book will get you well on your way. It is the perfect
introduction to Oracle PL/SQL Programming, also by Pribyl and
Feuerstein.
About the Authors
Bill Pribyl Bio to be posted soon
Steven Feuerstein is considered one of the world's leading
experts on the Oracle PL/SQL language. He is the author or
coauthor of six books on PL/SQL, including the now-classic
Oracle PL/SQL Programming and Oracle PL/SQL Best
Practices, all from O'Reilly & Associates. Steven is a Senior
Technology Advisor with Quest Software, has been developing
software since 1980, and worked for Oracle Corporation from
1987 to 1992. Steven is president of the Board of Directors of
the Crossroads Fund, which makes grants to Chicagoland
organizations working for social, racial and economic justice.
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/default.html (2 of 4) [15/05/2002 22:43:01]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
You can reach Steven at [email protected].
Reviews
"As someone who knows C, Perl, and Java, I found this to be a
great starter book in PL/SQL. It gave me the core knowledge I
needed for a jump-start into PL/SQL programming. This is a
great book for anybody wanting to learn PL/SQL programming
for Oracle!"
--Bill Phillips, System Engineer, Diverse Networks
"As a project manager for an IT consulting firm, I needed to go
beyond basic SQL and leverage the efficiency and versatility of
PL/SQL. After searching in vain for an introduction to PL/SQL
that required no prior programming experience, I've finally
found a book that is truly for the beginner. This book provides
thorough explanations of the sample code in plain English,
written so that I can understand why the programs work. It's the
next best thing to sitting down with someone for a private
tutorial."
--Corrie Nettles, former consultant at Baker Robbins & Co., and
Oracle Certified Professional in database administration
Readers Reviews
December 06, 2001 Rating:
No one can write PL/SQL better than Steven Feuerstein. If you
can not understand/program PL/SQL after reading this book
you can consider a carrier change. Highly recommended for
anyone who is interested in learning Oracle Programming .
K Gopalakrishnan
Copyright
Copyright © 2002 O'Reilly & Associates, Inc. All rights
reserved.
Printed in the United States of America.
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/default.html (3 of 4) [15/05/2002 22:43:01]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
Published by O'Reilly & Associates, Inc., 1005 Gravenstein
Highway North, Sebastopol, CA 95472.
O'Reilly & Associates books may be purchased for educational,
business, or sales promotional use. Online editions are also
available for most titles (http://safari.oreilly.com). For more
information contact our corporate/institutional sales
department: 800-998-9938 or [email protected].
The O'Reilly logo is a registered trademark of O'Reilly &
Associates, Inc. Many of the designations used by
manufacturers and sellers to distinguish their products are
claimed as trademarks. Where those designations appear in
this book, and O'Reilly & Associates, Inc. was aware of a
trademark claim, the designations have been printed in caps or
initial caps.
While every precaution has been taken in the preparation of
this book, the publisher assumes no responsibility for errors or
omissions, or for damages resulting from the use of the
information contained herein.
Last updated on 12/4/2001
Learning Oracle PL/SQL, © 2002 O'Reilly
Brought to you by KnowledgeLiberation
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/default.html (4 of 4) [15/05/2002 22:43:01]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
Database > Learning Oracle PL/SQL
Learning Oracle PL/SQL - Index
Dedication
Preface
Is This Book for You?
Other Books in This Series
Why This Book?
Which Oracle and PL/SQL Versions?
Organization of This Book
Conventions Used in This Book
Comments and Questions
Acknowledgments
1. PL/SQL: What, When, and Where
1.1 What Is PL/SQL?
1.2 Why Use PL/SQL?
1.3 What You Need to Get Started with PL/SQL
2. Fundamentals
2.1 PL/Lingo
2.2 Running Your First PL/SQL Program
2.3 Introduction to Program Structure
2.4 Variables
2.5 Common Operators
2.6 Conditional Logic
2.7 Executing in Circles: Loop Statements
2.8 Code Formatting: Requirements and Guidelines
2.9 Some Advanced Fundamentals
3. Let's Code!
3.1 Some Background on the Example
3.2 A First Programming Exercise
3.3 Retrieving a Book Count with a Function
3.4 Make Your Code Resilient
3.5 Using PL/SQL Packages to Organize Code
3.6 Going to the Next Level
3.7 Now What?
4. Go Web, Young Man
4.1 Introduction to HTML
4.2 Using PL/SQL to Create Web Pages
4.3 What Else?
5. Fetch!
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/toc.html (1 of 2) [15/05/2002 22:44:24]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
5.1 What's the Big Deal?
5.2 A Simple-Minded Approach to Retrieving One Row
5.3 Retrieving More than One Row Using a Cursor
5.4 Presenting Query Results via a Web Page
5.5 Building a Web-Based Search Page Using Dynamic SQL
5.6 Advanced Data Retrieval Topics
6. Keeping House
6.1 Organize Your Code
6.2 Use Tools to Write Code Effectively
7. Security: Keep the Bad Guys Out
7.1 Oracle Security Primer
7.2 Organizing Accounts to Improve Security
7.3 Analyzing the Library System's Requirements
7.4 Keeping a Trail of Database Changes
7.5 Special Security Topics for PL/SQL Developers
8. Communicating with the Outside World
8.1 Sending Internet Email from PL/SQL
8.2 Using the Mail Sender in the Library System
8.3 Receiving Email Inside the Database
8.4 Fetching Data from a Remote Web Site
8.5 Integration with Other Languages
9. Intermediate Topics and Other Diversions
9.1 Riding the Software Lifecycle
9.2 Lists o' Stuff (Collections) in PL/SQL
9.3 Exception-Handling Packages
9.4 Transaction Control
9.5 The PL/SQL Compiler
9.6 Managing Patron and Librarian Privileges
9.7 Still More PL/SQL Features
10. Afterword: "Making Good" of Database Programming
10.1 The Evidence
10.2 The Problem
10.3 Answering the Objections
10.4 What to Do
Glossary
Colophon
Last updated on 12/4/2001
Learning Oracle PL/SQL, © 2002 O'Reilly
Brought to you by KnowledgeLiberation
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/toc.html (2 of 2) [15/05/2002 22:44:24]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
Database > Learning Oracle PL/SQL > Dedication
Start | Table of Contents | Index | Examples CONTINUE >
Dedication
To my wife, Norma
—Bill Pribyl
To my newest neice, Lianne Belle Rosenthall
—Steven Feuerstein
Last updated on 12/4/2001
Learning Oracle PL/SQL, © 2002 O'Reilly
Brought to you by KnowledgeLiberation
Start | Table of Contents | Index | Examples CONTINUE >
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/page1.html [15/05/2002 22:45:27]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
Database > Learning Oracle PL/SQL > Preface
< BACK Start | Table of Contents | Index | Examples CONTINUE >
Preface
So you'd like to learn PL/SQL. Hooray! Let me welcome you to a worldwide community of
hundreds of thousands of PL/SQL programmers. By learning PL/SQL, you will gain command of a
great language for programming the Oracle database: a language long on practicality and short
on annoyances. Before the show begins, though, let's take a look at where we're going and how
we're going to get there.
Last updated on 12/4/2001
Learning Oracle PL/SQL, © 2002 O'Reilly
Brought to you by KnowledgeLiberation
< BACK Start | Table of Contents | Index | Examples CONTINUE >
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/page3.html [15/05/2002 22:47:09]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
Database > Learning Oracle PL/SQL > Preface > Is This Book for You?
< BACK Start | Table of Contents | Index | Examples CONTINUE >
Is This Book for You?
I am going to assume that most readers are using, or will soon be using, the Oracle database
server, probably a relatively recent version that is still supported by Oracle. Beyond that, how
many of the following apply to you?
l You are a new Oracle application developer who can spell PL/SQL but that's about it.
l You are a new Oracle database administrator (DBA), and you need to review PL/SQL
written by application developers.
l You are a new DBA and you want to automate many of your tasks.
l You need to use one of Oracle's options that requires PL/SQL knowledge (such as the
Spatial Data Option, used for storing and retrieving geographic information in the
database).
l You are a programmer familiar with another database like SQL Server, and your job now
requires you to deal with Oracle.
If even one of those descriptions is true, this book is for you. Whether you already know another
programming language like Java or Transact-SQL[1] , or this is your first exposure to
programming, this book should get you off the ground. If, on the other hand, you are proficient in
C++ and you eat new languages for breakfast, you might want to skim (or even skip) this book
and jump into one of the other books in O'Reilly's series of books on Oracle development.
[1] Transact-SQL, or T-SQL, is a language similar to PL/SQL that is used with two other database
management systems: Microsoft's SQL Server and Sybase.
Last updated on 12/4/2001
Learning Oracle PL/SQL, © 2002 O'Reilly
Brought to you by KnowledgeLiberation
< BACK Start | Table of Contents | Index | Examples CONTINUE >
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/page5.html [15/05/2002 22:47:50]
Learning Oracle PL/SQL | © 2002 O'Reilly | by KnowledgeLiberation www.it-ebooks.info
Database > Learning Oracle PL/SQL > Preface > Other Books in This Series
< BACK Start | Table of Contents | Index | Examples CONTINUE >
Other Books in This Series
This is the first O'Reilly book for you if you're a new PL/SQL developer, but when you are ready to
go to the next level, you may want to have a look at some of O'Reilly's other books in the Oracle
series:
Oracle PL/SQL Programming
A thousand-page tome that is the desk-side companion of a great many professional
PL/SQL programmers. This book is designed to cover every feature in the core PL/SQL
language, but does not go gently with beginners. The second edition covers Oracle
versions through Oracle8, but the third edition targets Oracle9i.
Oracle PL/SQL Programming: Guide to Oracle8i Features
A companion to the previous book that presents an overview of the great new PL/SQL
features that appeared in Oracle8i.
Oracle Built-in Packages
A reference guide to all of the pre-built packages that Oracle supplies with the core
database server. The use of these packages can sometimes simplify the difficult and tame
the impossible. Covers versions through Oracle8.
Oracle Web Applications: PL/SQL Developer's Introduction
A good book to get Oracle developers started building database-driven web applications.
Includes some introductory material on both PL/SQL and programming for the Web.
Covers versions through Oracle8i.
Advanced Oracle PL/SQL Programming with Packages
A book designed to communicate the rationale and means of improving your programs by
writing your own PL/SQL packages. Covers Oracle7.
Oracle PL/SQL Language Pocket Reference (covers versions through Oracle8i) and Oracle
PL/SQL Built-ins Pocket Reference (covers versions through Oracle8)
Two tiny "quick reference" books that might actually fit in your coat pocket.
The Oracle PL/SQL CD Bookshelf
file:///E|/O'Reilly/O'Reilly%20-%20Learning%20Oracle%20PLSQL/page6.html (1 of 2) [15/05/2002 22:47:53]