Oracle PL/SQL Recipes: A Problem-Solution Approach

457 Pages·2010·5.9 MB·English

by  Josh Juneau

Books for professionals By professionals® The eXperT’s Voice® in oracle
Companion
eBook Available
Josh Juneau, Author of
Oracle PL/SQL Recipes
The Definitive Guide to
Jython: Python for the Java O
Platform
Oracle PL/SQL Recipes provides quick and concise programming solutions for
r
busy professionals who are developing solutions built on Oracle’s market-lead-
ing, database technology. The book’s example-based approach respects your a
time by presenting you with an easy-to-read, problem/solution format. Scan
c
Matt Arena the table-of-contents for the programming problem you need to solve, open
Oracle PL/SQL
the book to the indicated page, see an example solution in code that you can
l
immediately copy and adapt to your needs. e
Solutions in the book are grouped into chapters by topic area for ease of
reference. You’ll find coverage showing how to:
P
• Write stored procedures and functions
• Trap exceptions and errors L
• Diagnose and improve performance Recipes
• Integrate PL/SQL with web applications /
• Debug and Unit-test your code S

• and more!
Q
PL/SQL is an important language to learn. It opens the door to centralizing
business logic in the database. It is the venue in which many of Oracle’s new-
L
est features are exposed. It is the “rock” underlying the wildly successful Oracle
Application Express. Anyone who does anything with Oracle should learn
A Problem-Solution Approach
something of the language in this book. And the code examples in this book R
make that learning fast and easy. You don’t need to read pages and pages of
prose to get to the point. We begin with the point, which is the code. Open the e
book. Read the code. Copy. Adapt. It’s that simple.
c
i
THE APRESS ROADMAP p
Quick and reliable solutions for developers
Beginning Oracle Expert e
Companion eBook Oracle PL/SQL PL/SQL Recipes Oracle Database and database administrators
Architecture
s
Beginning Pro Troubleshooting
Oracle SQL Oracle SQL Oracle Performance

J Josh Juneau Matt Arena
Au and
rn
www.apress.com ee
na
au
US $59.99
Shelve in:
Databases / Oracle
User level:
Beginning–Intermediate
www.it-ebooks.info
www.it-ebooks.info
Oracle and PL/SQL
Recipes
A Problem-Solution Approach







  
Josh Juneau

Matt Arena

www.it-ebooks.info
Oracle and PL/SQL Recipes: A Problem-Solution Approach
Copyright © 2010 by Josh Juneau and Matt Arena
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-4302-3207-0
ISBN-13 (electronic): 978-1-4302-3208-7
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol
with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only
in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of
the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are
not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject
to proprietary rights.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Development Editor: Jonathan Gennick
Technical Reviewer: Bob Bryla
Editorial Board: Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan Gennick,
Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes, Jeffrey Pepper, Frank
Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh
Coordinating Editor: Adam Heath
Copy Editor: Kim Wimpsett
Compositor: Bytheway Publishing Services
Indexer: BIM Indexing & Proofreading Services
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring Street,
6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail orders-
[email protected], or visit www.springeronline.com.
For information on translations, please e-mail [email protected], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our
Special Bulk Sales–eBook Licensing web page at www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have
any liability to any person or entity with respect to any loss or damage caused or alleged to be caused
directly or indirectly by the information contained in this work.
The source code for this book is available to readers at www.apress.com.
www.it-ebooks.info
 CONTENTS AT A GLANCE
Contents at a Glance
 About the Authors............................................................................................xxxiv
 About the Technical Reviewer..........................................................................xxxv
 Acknowledgments...........................................................................................xxxvi
 Introduction...................................................................................................xxxviii
 Chapter 1: PL/SQL Fundamentals...........................................................................1
 Chapter 2: Essential SQL.......................................................................................15
 Chapter 3: Looping and Logic...............................................................................43
 Chapter 4: Functions, Packages, and Procedures...............................................63
 Chapter 5: Triggers...............................................................................................93
 Chapter 6: Type Conversion................................................................................119
 Chapter 7: Numbers, Strings, and Dates ............................................................133
 Chapter 8: Dynamic SQL.....................................................................................155
 Chapter 9: Exceptions.........................................................................................187
 Chapter 10: PL/SQL Collections and Records.....................................................215
 Chapter 11: Automating Routine Tasks..............................................................233
 Chapter 12: Oracle SQL Developer......................................................................247
 Chapter 13: Analyzing and Improving Performance...........................................281
 Chapter 14: Using PL/SQL on the Web................................................................291
 Chapter 15: Java in the Database.......................................................................319
 Chapter 16: Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython...............345
 Chapter 17: Unit Testing With utPLSQL...............................................................361
 Index...................................................................................................................391

iii
www.it-ebooks.info
 CONTENTS
Contents
 About the Authors............................................................................................xxxiv
 About the Technical Reviewer..........................................................................xxxv
 Acknowledgments...........................................................................................xxxvi
 Introduction...................................................................................................xxxviii
 Chapter 1: PL/SQL Fundamentals...........................................................................1
1-1. Creating a Block of Code............................................................................................1
Problem....................................................................................................................................................1
Solution....................................................................................................................................................1
How It Works............................................................................................................................................2
1-2. Executing a Block of Code in SQL*Plus......................................................................2
Problem....................................................................................................................................................2
Solution....................................................................................................................................................2
How It Works............................................................................................................................................2
1-3. Storing Code in a Script.............................................................................................3
Problem....................................................................................................................................................3
Solution....................................................................................................................................................3
How It Works............................................................................................................................................4
1-4. Executing a Stored Script...........................................................................................4
Problem....................................................................................................................................................4
Solution....................................................................................................................................................4
How It Works............................................................................................................................................5
1-5. Accepting User Input from the Keyboard...................................................................5
Problem....................................................................................................................................................5
iv
www.it-ebooks.info
 CONTENTS
Solution....................................................................................................................................................5
How It Works............................................................................................................................................5
1-6. Displaying Results in SQL*Plus..................................................................................7
Problem....................................................................................................................................................7
Solution....................................................................................................................................................7
How It Works............................................................................................................................................8
1-7. Commenting Your Code..............................................................................................8
Problem....................................................................................................................................................8
Solution....................................................................................................................................................8
How It Works............................................................................................................................................9
1-8. Referencing a Block of Code......................................................................................9
Problem....................................................................................................................................................9
Solution....................................................................................................................................................9
How It Works..........................................................................................................................................10
1-9. Referring to Variables from Nested Blocks..............................................................10
Problem..................................................................................................................................................10
Solution..................................................................................................................................................10
How It Works..........................................................................................................................................11
1-10. Ignoring Substitution Variables..............................................................................12
Problem..................................................................................................................................................12
Solution #1.............................................................................................................................................12
Solution #2.............................................................................................................................................12
How It Works..........................................................................................................................................12
1-11. Changing the Substitution Variable Character.......................................................13
Problem..................................................................................................................................................13
Solution..................................................................................................................................................13
How It Works..........................................................................................................................................14
1-12. Creating a Variable to Match a Database Column Type.........................................14
v
www.it-ebooks.info
 CONTENTS
Problem..................................................................................................................................................14
Solution..................................................................................................................................................14
How It Works..........................................................................................................................................14
 Chapter 2: Essential SQL.......................................................................................15
2-1. Retrieving a Single Row from the Database.............................................................15
Problem..................................................................................................................................................15
Solution #1.............................................................................................................................................15
Solution #2.............................................................................................................................................16
How It Works..........................................................................................................................................17
2-2. Qualifying Column and Variable Names...................................................................18
Problem..................................................................................................................................................18
Solution..................................................................................................................................................19
How It Works..........................................................................................................................................19
2-3. Declaring Variable Types That Match Column Types...............................................20
Problem..................................................................................................................................................20
Solution..................................................................................................................................................20
How It Works..........................................................................................................................................21
2-4. Returning Queried Data into a PL/SQL Record.........................................................21
Problem..................................................................................................................................................21
Solution..................................................................................................................................................21
How It Works..........................................................................................................................................22
2-5. Creating Your Own Records to Receive Query Results............................................22
Problem..................................................................................................................................................22
Solution..................................................................................................................................................22
How It Works..........................................................................................................................................23
2-6. Looping Through Rows from a Query.......................................................................24
Problem..................................................................................................................................................24
Solution #1.............................................................................................................................................24
vi
www.it-ebooks.info
 CONTENTS
Solution #2.............................................................................................................................................24
How It Works..........................................................................................................................................25
2-7. Obtaining Environment and Session Information.....................................................25
Problem..................................................................................................................................................25
Solution..................................................................................................................................................25
How It Works..........................................................................................................................................26
2-8. Formatting Query Results.........................................................................................29
Problem..................................................................................................................................................29
Solution..................................................................................................................................................29
How It Works..........................................................................................................................................30
2-9. Updating Rows Returned by a Query........................................................................31
Problem..................................................................................................................................................31
Solution..................................................................................................................................................31
How It Works..........................................................................................................................................32
2-10. Updating Rows Returned by a Cursor....................................................................33
Problem..................................................................................................................................................33
Solution..................................................................................................................................................33
How It Works..........................................................................................................................................33
2-11. Deleting Rows Returned by a Cursor.....................................................................34
Problem..................................................................................................................................................34
Solution..................................................................................................................................................34
How It Works..........................................................................................................................................35
2-12. Performing a Transaction.......................................................................................35
Problem..................................................................................................................................................35
Solution..................................................................................................................................................35
How It Works..........................................................................................................................................36
2-13. Ensuring That Multiple Queries “See” the Same Data...........................................37
Problem..................................................................................................................................................37
vii
www.it-ebooks.info
 CONTENTS
Solution..................................................................................................................................................37
How It Works..........................................................................................................................................37
2-14. Executing One Transaction from Within Another...................................................38
Problem..................................................................................................................................................38
Solution..................................................................................................................................................38
How It Works..........................................................................................................................................39
2-15. Finding and Removing Duplicate Table Rows........................................................40
Problem..................................................................................................................................................40
Solution..................................................................................................................................................40
How It Works..........................................................................................................................................41
 Chapter 3: Looping and Logic...............................................................................43
3-1. Choosing When to Execute Code..............................................................................43
Problem..................................................................................................................................................43
Solution..................................................................................................................................................43
How It Works..........................................................................................................................................43
3-2. Choosing Between Two Mutually Exclusive Conditions...........................................44
Problem..................................................................................................................................................44
Solution..................................................................................................................................................44
How It Works..........................................................................................................................................45
3-3. Evaluating Multiple Mutually Exclusive Conditions..................................................45
Problem..................................................................................................................................................45
Solution #1.............................................................................................................................................45
Solution #2.............................................................................................................................................46
How It Works..........................................................................................................................................47
3-4. Driving from an Expression Having Multiple Outcomes...........................................48
Problem..................................................................................................................................................48
Solution..................................................................................................................................................48
How It Works..........................................................................................................................................49
viii
www.it-ebooks.info