ebook img

Oracle9i PL/SQL: A Developer’s Guide PDF

668 Pages·2003·7.942 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Oracle9i PL/SQL: A Developer’s Guide

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 +496221345229, 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 510-549-5930, fax 510-549-5939, 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

See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.