Oracle9i PL/SQL: A Developer’s Guide

668 Pages·2003·7.942 MB·English

by  Bulusu Lakshman (auth.)

Oracle9i PLISOL :
-
A Developer's Guide
BULUSU LAKSHMAN
Apress'"
Oracle9i PL/SQL: A Developer's Guide
Copyright © 2003 by Bulusu Lakshman
Originally published by Apress in 2003.
All rights reserved. No part ofthis work may be reproduced or transmitted in any form or byany
means, electronic or mechanical, induding 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-59059-049-2 ISBN 978-1-4302-1154-9 (eBook)
DOI 10.1007/978-1-4302-1154-9
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Technical Reviewer: Martin Reid
Editorial Directors: Dan Appleman, Gary Cornell, Jason Gilmore, Simon Hayes, Karen Watterson,
John Zukowski
Managing Editor: Grace Wong
Project Manager and Developmental Editor: Tracy Brown Collins
Copy Editors: Nicole LeClerc, Ami Knox
Production Editor: Laura Cheu
Compositor and Artist: Impressions Book and Journal Services, Inc.
Cover Designer: Kurt Krames
Indexer: Valerie Robbins
Marketing Manager: Stephanie Rodriguez
Manufacturing Manager: Tom Debolski
Distributed to the book trade in the United States by Springer-Verlag NewYork, Inc., 233 Spring
Street, 6th Floor, NewYork, NY 10013 and outside the United States by Springer-Verlag GmbH &
Co. KG, Tiergartenstr. 17,69112 Heidelberg, Germany.
In the United States, phone I-BOO-SPRINGER, email [email protected]. or visit
http://www.springer-ny.com.
Outside the United States, fax [phone], email [email protected], or visit
http://www.springer.de.
For information on translations, please contactApress directly at 2560 9th Street, Suite 2l9,
Berkeley, CA 94710. Phone [phone], fax [phone], email [email protected], or visit
http://www.apress.com.
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 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 http://www.apress.comin the Source
Code/Downloads section.
With love to my parents, Professor B.S.KR. Somayajulu and Smt. B. Sita.
Contents at a Glance
Foreword .............................................................x iii
About the Author ................................................... .x v
About the Technical Reviewer .....................................x vi
About the Foreword Writer ........................................x vii
Acknowledgments .....................................................x ix
Introduction ........................................................x xi
Part One: Introduction ............................................ 1
Chapter 1 Introduction to PL/SQL ................................3
Part Two: Non-Object-Oriented Features in PL/SQL 9i ........3 1
Chapter 2 Cursors ................................................3 3
Chapter 3 PL/SQL Records and Index-by Tables ................. 95
Chapter 4 Error Message Handling .............................. 137
Chapter 5 Stored Subprograms
(Procedures, Functions, and Packages) ............ 175
Chapter 6 Database Triggers ................................... .255
Chapter 7 Native Dynamic SQL and Dynamic PL/SQL ............2 91
Chapter 8 Autonomous Transactions ........................... .315
Chapter 9 Native Bulk Binds ....................................3 33
Part Three: Object-Oriented Featuresin PLlSQL 9i ............ .359
Chapter 10 The World of Objects ................................3 61
Chapter 11 Collections (VARRAYS and Nested Tables) .......... 433
Chapter 12 Large Objects ....................................... .479
v
Part Four: PL/SQL with Java and the Web .......................5 07
Chapter 13 Floating in Java .....................................5 09
Chapter 14 PL/SQL and the Web .................................. 555
Part Five: PL/SQL Performance and Standards .................. 595
Chapter 15 Performance Considerations ......................... 597
Chapter 16 PL/SQL Coding Standards ............................ 611
Part Six: Appendix .. ............................................ 629
Appendix A Case Studies and Schema Objects ................... 631
Index ................................................................. 637
Contents
Foreword .............................................................x iii
About the Author ................................................... .x v
About the Technical Reviewer .....................................x vi
About the Foreword Writer ........................................x vii
Acknowledgments .....................................................x ix
Introduction ........................................................x xi
Part One: Introduction ........................................
1
Chapter 1: Introduction to PL/SQL ........................3
Advantages of Using PL/SQL .......................................... 5
Building Blocks of PL/SQL ........................................... 7
Procedural Constructs in PL/SQL ...................................1 0
PL/SQL Environments ................................................. 15
New Features of PL/SQL 9i ..........................................1 7
Summary ...............................................................3 0
Part Two: Non-Object-Oriented Features
in PL/SQL 9i .....................................................3 1
Chapter 2: Cursors .............................................3 3
Introducing Cursors ................................................3 3
Cursor Variables .................................................... 65
Cursor Expressions ..................................................8 0
Summary ............................................................... 94
vii
Contents
Chapter 3: PL/SQL Records and Index-by Tables ......
95
Records ............................................................... 97
Index-by Tables .................................................... 114
Summary .............................................................. 135
Chapter 4: Error Message Handling ......................
137
PL /SQL Exceptions : Types and Definition ........................ 137
Handling PL/SQL Exceptions ........................................ 143
User-Defined PL/SQL Error Messages .............................. 166
Tips for PL/SQL Error Message and Exception Handling ......... 172
Summary .............................................................. 173
Chapter 5: Stored Subprograms
(Procedures, Functions, and Packages) ................
175
Creating and Using Procedures and Functions ................... 177
PL/SQL Packages ....................................................2 01
Subprograms Returning Resultsets ................................2 17
Using Stored Functions in SQL Statements ...................... .220
Parameter Passing by Reference ................................. .231
Definer and Invoker Rights .......................................2 36
Additional Package Features ......................................2 42
Summary ............................................................. .253
Chapter 6: Database Triggers ..............................
255
PL/SQL Triggers: Types and Definition ......................... .256
Read-Only Views, Updateable Views,
and INSTEAD-OF Triggers .......................................2 75
New Database Triggers ............................................. .282
Summary ............................................................. .290
viii
Contents
Chapter 7: Native Dynamic SQL
and Dynamic PL/SQL ...........................................
291
DBMS_SQL vs. Native Dynamic SQL ................................ .292
Native Dynamic SQL Statements ....................................2 95
Dynamic DDL .........................................................2 96
Dynamic SE LECTS ....................................................3 00
Dynamic INSERT, UPDATE, and DE LETE ..............................3 07
Using Bind Variables ..............................................3 08
Dynamic PL/SQL .....................................................3 10
Exception Handling in Native Dynamic SQL ...................... .312
Native Dynamic SQL and Objects ..................................3 13
Summary ..............................................................3 13
Chapter 8: Autonomous Transactions ...................
.315
Transaction Management Prior to
Autonomous Transactions .......................................3 15
Autonomous Transactions : Definition and Use .................. .316
Summary ..............................................................3 31
Chapter 9: Native Bulk Binds .............................
333
Bulk DML .............................................................3 34
Bulk Query ..........................................................3 46
Bulk Dynamic SQL ...................................................3 54
Summary ..............................................................3 57
Part Three: Object-Oriented Features
in PL/SQL 9i ....................................................
359
Chapter 10: The World of Objects ...................... .361
Object Types and Object Tables ..................................3 62
Type Inheritance ...................................................3 99
Dynamic Method Dispatch .......................................... .409
Type Evolution .................................................... .411
ix
Contents
Java Object Storage: SQL Types of Language Java
or SQLJ Object Types ......................................... .417
User-Defined Operators ........................................... .423
Summary ............................................................. .431
Chapter 11: Collections
(VARRAYS and Nested Tables) .............................
.433
Creating and Using Collections in PL/SQL ...................... .433
Table Functions ................................................... .464
Multilevel Collections ........................................... .473
Summary ............................................................. .478
Chapter 12: Large Objects ................................ .479
LOBs Overview ...................................................... .479
BLOBs ............................................................... .480
CLOBs ............................................................... .482
BFILEs .............................................................. .485
DBMS _ LOB Package .................................................. .487
TO LOB Function ....................................................5 02
Summary ..............................................................5 05
Part Four: PLISQL with Java and the Web .............
507
Chapter 13: Floating in Java .............................
509
Using Java in the Database .......................................5 09
PL/SQL Calling Java: Java Stored Procedures ................... 511
Creating Java Stored Procedures ................................. 513
Executing Java Stored Procedures ................................ 544
Java Stored Procedures Returning Resul tsets .................... 549
Summary .............................................................. 554
x