Oracle and PL/SQL Recipes: A Problem-Solution Approach

442 Pages·2010·6.73 MB·English

by  Josh Juneau

Oracle and PL/SQL
Recipes
A Problem-Solution Approach

(cid:2)
(cid:2)
(cid:2)
(cid:2)
(cid:2)
(cid:3) (cid:3) (cid:3)
Josh Juneau

Matt Arena
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 978-1-4302-3207-0
ISBN 978-1-4302-3208-7 (eBook)
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-
[e-mail], or visit www.springeronline.com.
For information on translations, please e-mail [e-mail], 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.
(cid:3) CONTENTS AT A GLANCE
Contents at a Glance
(cid:3) About the Authors............................................................................................xxxiv
(cid:3) About the Technical Reviewer..........................................................................xxxv
(cid:3) Acknowledgments...........................................................................................xxxvi
(cid:3) Introduction...................................................................................................xxxviii
(cid:3) Chapter 1: PL/SQL Fundamentals...........................................................................1
(cid:3) Chapter 2: Essential SQL.......................................................................................15
(cid:3) Chapter 3: Looping and Logic...............................................................................43
(cid:3) Chapter 4: Functions, Packages, and Procedures...............................................63
(cid:3) Chapter 5: Triggers...............................................................................................93
(cid:3) Chapter 6: Type Conversion................................................................................119
(cid:3) Chapter 7: Numbers, Strings, and Dates ............................................................133
(cid:3) Chapter 8: Dynamic SQL.....................................................................................155
(cid:3) Chapter 9: Exceptions.........................................................................................187
(cid:3) Chapter 10: PL/SQL Collections and Records.....................................................215
(cid:3) Chapter 11: Automating Routine Tasks..............................................................233
(cid:3) Chapter 12: Oracle SQL Developer......................................................................247
(cid:3) Chapter 13: Analyzing and Improving Performance...........................................281
(cid:3) Chapter 14: Using PL/SQL on the Web................................................................291
(cid:3) Chapter 15: Java in the Database.......................................................................319
(cid:3) Chapter 16: Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython...............345
(cid:3) Chapter 17: Unit Testing With utPLSQL...............................................................361
(cid:3) Index...................................................................................................................391

iii
(cid:3) CONTENTS
Contents
(cid:3) About the Authors............................................................................................xxxiv
(cid:3) About the Technical Reviewer..........................................................................xxxv
(cid:3) Acknowledgments...........................................................................................xxxvi
(cid:3) Introduction...................................................................................................xxxviii
(cid:3) 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
(cid:3) 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
(cid:3) CONTENTS
Problem..................................................................................................................................................14
Solution..................................................................................................................................................14
How It Works..........................................................................................................................................14
(cid:3) 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
(cid:3) 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
(cid:3) 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
(cid:3) 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
(cid:3) CONTENTS
3-5. Looping Until a Specified Condition Is Met...............................................................49
Problem..................................................................................................................................................49
Solution..................................................................................................................................................50
How It Works..........................................................................................................................................50
3-6. Iterating Cursor Results Until All Rows Have Been Returned...................................51
Problem..................................................................................................................................................51
Solution..................................................................................................................................................51
How It Works..........................................................................................................................................52
3-7. Iterating Until a Condition Evaluates to FALSE.........................................................52
Problem..................................................................................................................................................52
Solution..................................................................................................................................................52
How It Works..........................................................................................................................................53
3-8. Bypassing the Current Loop Iteration.......................................................................53
Problem..................................................................................................................................................53
Solution..................................................................................................................................................53
How It Works..........................................................................................................................................54
3-9. Iterating a Fixed Number of Times...........................................................................55
Problem..................................................................................................................................................55
Solution..................................................................................................................................................55
How It Works..........................................................................................................................................55
3-10. Iterating Backward Through a Range.....................................................................56
Problem..................................................................................................................................................56
Solution..................................................................................................................................................56
How It Works..........................................................................................................................................56
3-11. Iterating in Increments Other Than One.................................................................57
Problem..................................................................................................................................................57
Solution..................................................................................................................................................57
How It Works..........................................................................................................................................57
ix
(cid:3) CONTENTS
3-12. Stepping Through a Loop Based on Odd-Numbered Increments...........................58
Problem..................................................................................................................................................58
Solution..................................................................................................................................................58
How It Works..........................................................................................................................................58
3-13. Exiting an Outer Loop Prematurely.........................................................................59
Problem..................................................................................................................................................59
Solution..................................................................................................................................................59
How It Works..........................................................................................................................................59
3-14. Jumping to a Designated Location in Code............................................................60
Problem..................................................................................................................................................60
Solution..................................................................................................................................................60
How It Works..........................................................................................................................................61
(cid:3) Chapter 4: Functions, Packages, and Procedures...............................................63
4-1. Creating a Stored Function.......................................................................................63
Problem..................................................................................................................................................63
Solution..................................................................................................................................................64
How It Works..........................................................................................................................................65
4-2. Executing a Stored Function from a Query...............................................................67
Problem..................................................................................................................................................67
Solution..................................................................................................................................................67
How It Works..........................................................................................................................................67
4-3. Optimizing a Function That Will Always Return the Same Result for a
Given Input...............................................................................................................68
Problem..................................................................................................................................................68
Solution..................................................................................................................................................68
How It Works..........................................................................................................................................69
4-4. Creating a Stored Procedure....................................................................................69
Problem..................................................................................................................................................69
Solution..................................................................................................................................................69
x