Teach Yourself PL/SQL in 21 Days- P1

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
49
lượt xem
10
download

Teach Yourself PL/SQL in 21 Days- P1

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Teach Yourself PL/SQL in 21 Days- P1: Welcome to Sams Teach Yourself PL/SQL in 21 Days, Second Edition. The fact that you purchased this book indicates that you have an interest in learning the PL/SQL language, which is Oracle’s relational database procedural programming language. It allows you to develop powerful and complex programs to access and manipulate data in the Oracle8i database. We have attempted to include as many examples of PL/SQL code as possible to illustrate PL/SQL features....

Chủ đề:
Lưu

Nội dung Text: Teach Yourself PL/SQL in 21 Days- P1

  1. Teach Yourself  PL/SQL in 21 Days SECOND EDITION A Division of Macmillan USA 800 Easty 96th St., Indianapolis, Indiana, 46240 USA
  2. ASSOCIATE PUBLISHER Sams Teach Yourself PL/SQL in Bradley L. Jones 21 Days, Second Edition ACQUISITIONS EDITOR Copyright ©2000 by Sams Publishing Chris Webb All rights reserved. No part of this book shall be reproduced, stored in a DEVELOPMENT EDITOR retrieval system, or transmitted by any means, electronic, mechanical, photo- Steve Rowe copying, recording, or otherwise, without written permission from the publish- MANAGING EDITOR er. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation Lisa Wilson of this book, the publisher and author assume no responsibility for errors or PROJECT EDITOR omissions. Neither is any liability assumed for damages resulting from the use Heather Talbot of the information contained herein. COPY EDITOR International Standard Book Number: 0-672-31798-2 Kitty Jarrett Library of Congress Catalog Card Number: 99-65434 INDEXER Printed in the United States of America Chris Wilcox PROOFREADER First Printing: December 1999 Megan Wade 06 05 04 03 7 6 5 4 TECHNICAL EDITOR Jeff Shockley Trademarks All terms mentioned in this book that are known to be trademarks or service TEAM COORDINATOR marks have been appropriately capitalized. Sams Publishing cannot attest to Meggo Barthlow the accuracy of this information. Use of a term in this book should not be MEDIA DEVELOPER regarded as affecting the validity of any trademark or service mark. Dave Carson PL/SQL is a trademark of Oracle Corporation. INTERIOR DESIGN Gary Adair Warning and Disclaimer COVER DESIGN Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on Aren Howell an “as is” basis. The authors and the publisher shall have neither liability nor COPY WRITER responsibility to any person or entity with respect to any loss or damages Eric Borgert arising from the information contained in this book. PRODUCTION Cyndi Davis-Hubler
  3. Contents at a Glance Introduction 1 WEEK 1 At a Glance 5 Day 1 Learning the Basics of PL/SQL 7 2 Writing Declarations and Blocks 33 3 Writing PL/SQL Expressions 63 4 Using Functions, IF Statements, and Loops 99 5 Implementing Loops and GOTOs 127 6 Using Oracle’s Built-In Functions 155 7 Procedures, Packages, Errors, and Exceptions 189 WEEK 1 In Review 217 WEEK 2 At a Glance 219 8 Using SQL 221 9 Manipulating Data with Cursors 245 10 Collections 265 11 Writing Database Triggers 293 12 Using Oracle8i Objects for Object-Oriented Programming 333 13 Debugging Your Code and Preventing Errors 371 14 Leveraging Large Object Types 401 WEEK 2 In Review 433 WEEK 3 At a Glance 435 15 Managing Transactions and Locks 437 16 Generating Dynamic SQL 451 17 Writing to Files and the Display 483 18 Managing Database Jobs 509 19 Alerting and Communicating with Other Procedures: The DBMS_ALERT and DBMS_PIPE Packages 531 20 PL/SQL and Java 559 21 Advanced Queuing 585
  4. WEEK 3 In Review 613 A Answers 615 Index 659
  5. Contents Introduction 1 WEEK 1 At a Glance 5 DAY 1 Learning the Basics of PL/SQL 7 What Is PL/SQL? ....................................................................................................8 Why Learn PL/SQL? ..............................................................................................9 SQL, SQL*Plus, PL/SQL: What’s the Difference? ................................................9 SQL ....................................................................................................................9 PL/SQL ............................................................................................................10 SQL*Plus..........................................................................................................10 What You Need to Finish This Book................................................................12 Getting Started with PL/SQL ................................................................................13 PL/SQL Is Block Structured ............................................................................13 Compiling and Executing a Simple Block ......................................................15 What About Some Output? ..............................................................................17 Alternatives to Retyping ..................................................................................18 Writing Your First Function ............................................................................20 Finding Compilation Errors..............................................................................21 Displaying the Function’s Return Value ..........................................................23 Can Even This Simple Function Be Useful? ..................................................23 Executing PL/SQL Using Developer 2000’s Procedure Builder ..........................24 Starting Procedure Builder ..............................................................................24 Creating the SS_THRESH Function ....................................................................26 Connecting to a Database ................................................................................27 Using SQLPlus Worksheet ....................................................................................28 Executing a PL/SQL Block Using SQLPlus Worksheet..................................28 Summary ................................................................................................................29 Q&A ......................................................................................................................30 Workshop ..............................................................................................................30 Quiz ..................................................................................................................31 Exercises ..........................................................................................................31 DAY 2 Writing Declarations and Blocks 33 Exploring Datatypes ..............................................................................................34 VARCHAR2 ..........................................................................................................35 CHAR ..................................................................................................................36 NUMBER ..............................................................................................................38 BINARY_INTEGER ................................................................................................41
  6. vi Sams Teach Yourself PL/SQL in 21 Days, Second Edition PLS_INTEGER......................................................................................................43 DATE..................................................................................................................44 BOOLEAN ............................................................................................................45 LONG ..................................................................................................................46 RAW ....................................................................................................................46 LONG RAW ..........................................................................................................47 ROWID ................................................................................................................47 UROWID ..............................................................................................................48 MSLABEL ............................................................................................................48 Using Block Structure............................................................................................49 Anonymous Blocks ..........................................................................................49 Function and Procedure Blocks........................................................................51 Trigger Blocks ..................................................................................................53 Nested Blocks ..................................................................................................54 Nesting Functions and Procedures ..................................................................55 Scope Rules ......................................................................................................58 Summary ................................................................................................................59 Q&A ......................................................................................................................60 Workshop ..............................................................................................................60 Quiz ..................................................................................................................60 Exercises ..........................................................................................................61 DAY 3 Writing PL/SQL Expressions 63 Understanding Operators, Expressions, and Conversions ....................................64 Building Expressions with PL/SQL Operators......................................................64 Arithmetic Operators ........................................................................................65 Comparison Operators......................................................................................68 Logical Operators ............................................................................................76 String Operators................................................................................................78 Using Comparison Operators with Strings............................................................79 The Effect of Character Set on String Comparisons........................................79 The Datatype’s Effect on String Comparisons ................................................79 The Effect of Case on String Comparisons......................................................81 Use of Comparison Operators with Dates ............................................................82 Exploring Expressions ..........................................................................................83 Expressions Defined ........................................................................................83 Operator Precedence ........................................................................................84 Types of Expressions........................................................................................86 Null Values in Expressions ..............................................................................86 Converting Datatypes ............................................................................................92 Implicit Conversion ..........................................................................................92 Explicit Conversion ..........................................................................................94
  7. Contents vii Summary ................................................................................................................95 Q&A ......................................................................................................................95 Workshop ..............................................................................................................97 Quiz ..................................................................................................................97 Exercise ............................................................................................................97 DAY 4 Using Functions, IF Statements, and Loops 99 Exploring PL/SQL Functions ..............................................................................100 Defining Parameters: Formal and Actual ......................................................102 Return Types ..................................................................................................105 Function Violations ........................................................................................105 Exception Handling ........................................................................................106 Creating a Stored Function ............................................................................106 Finding Errors ................................................................................................108 The NULL Statement..............................................................................................110 Why Use the NULL Statement? ......................................................................110 How to Call a Function from PL/SQL with or Without Parameters..............110 Invoker and Definer Rights ............................................................................111 Using PL/SQL Statements ..................................................................................111 The IF Statement ............................................................................................112 The IF...THEN...ELSE Statement..................................................................113 Nested IF Statements......................................................................................114 Using IF...ELSIF ..........................................................................................117 Nested IF Versus ELSIF ..................................................................................118 Formatting IF Statements ..............................................................................118 Avoiding Common Mistakes When Using IF ................................................119 Looping Statements ........................................................................................120 Reversing the Loop ........................................................................................122 Incrementing Through a Loop Differently ....................................................122 Summary ..............................................................................................................123 Q&A ....................................................................................................................124 Workshop ............................................................................................................125 Quiz ................................................................................................................125 Exercises ........................................................................................................125 DAY 5 Implementing Loops and GOTOs 127 Exploring Labels and the GOTO Statement ..........................................................128 Statement Labels ............................................................................................128 The GOTO Statement ........................................................................................128 Scoping Rules for the GOTO Statement ..........................................................129 An Example of the GOTO Statement in Action................................................133 Why Use the GOTO Statement?........................................................................133
  8. viii Sams Teach Yourself PL/SQL in 21 Days, Second Edition WHILE Loops ........................................................................................................134 Examples of WHILE Loops ..............................................................................134 The EXIT and EXIT WHEN Statements ..................................................................138 Examples Using EXIT and EXIT WHEN ............................................................139 Using Labels and EXIT Statements with Loops ............................................142 Simple LOOPs ........................................................................................................143 Sample Simple LOOPs......................................................................................144 Creating a REPEAT...UNTIL Loop ..................................................................144 An Example of a Simulated REPEAT...UNTIL Loop ......................................145 What Loop Should I Use? ..................................................................................146 Loop Guidelines ..................................................................................................146 Style Tips for Loops ......................................................................................148 Recursion ............................................................................................................149 Guidelines ......................................................................................................149 Practicing Recursion ......................................................................................150 Why Use Recursion? ......................................................................................152 Summary ..............................................................................................................152 Q&A ....................................................................................................................153 Workshop ............................................................................................................153 Quiz ................................................................................................................153 Exercises ........................................................................................................154 DAY 6 Using Oracle’s Built-In Functions 155 Comparing SQL Functions and PL/SQL Functions............................................156 The Major Categories of Functions ....................................................................156 Experimenting with Functions ............................................................................161 Using Conversion Functions................................................................................161 Using TO_DATE ................................................................................................161 Using TO_CHAR for Dates ................................................................................166 Using TO_NUMBER ............................................................................................168 Using TO_CHAR for Numbers ..........................................................................174 DATE Functions ....................................................................................................175 SYSDATE ..........................................................................................................175 The TRUNC Function ........................................................................................176 The ADD_MONTHS Function ..............................................................................178 The NEXT_DAY Function ..................................................................................179 The LAST_DAY Function ..................................................................................181 The MONTHS_BETWEEN Function ......................................................................182 The NEW_TIME Function ..................................................................................183 The ROUND Function ........................................................................................184 The TRIM Function ..........................................................................................185
  9. Contents ix Summary ..............................................................................................................185 Q&A ....................................................................................................................186 Workshop ............................................................................................................186 Quiz ................................................................................................................186 Exercises ........................................................................................................187 DAY 7 Procedures, Packages, Errors, and Exceptions 189 Using Procedures ................................................................................................190 Why Use Procedures? ....................................................................................190 Procedures Versus Functions ..........................................................................190 Creating Procedures........................................................................................190 The RETURN Statement ....................................................................................192 Procedure Dependencies ................................................................................192 Recompiling Stored Procedures ....................................................................193 Re-creating and Modifying Procedures..........................................................194 Invoking Stored Procedures............................................................................194 Using Parameters............................................................................................195 Parameter Definitions ....................................................................................195 Listing Stored Procedure Information............................................................195 Dropping a Stored Procedure ........................................................................197 Overloading Procedures ................................................................................197 Using Recursive Procedures ..........................................................................197 Procedure Invocation Security ............................................................................198 Exploring Packages..............................................................................................199 Why Use Packages? ......................................................................................199 The Package Specification ............................................................................200 The Package Body..........................................................................................201 Creating Packages ..........................................................................................202 Calling Package Subprograms........................................................................203 Recompiling Packages....................................................................................204 Private Versus Public Package Objects ..........................................................205 Package Invocation Security ..........................................................................205 Variables, Cursors, and Constant Persistence ................................................205 Package States ................................................................................................205 Package Dependency......................................................................................206 Trapping Errors and Exceptions ..........................................................................206 Exception-Handling Structures ......................................................................207 SQLCODE and SQLERRM ......................................................................................211 Continuing Processing After an Exception ....................................................212 Retrying After an Exception ..........................................................................213 Re-raising Exceptions ....................................................................................213 Exception Scope Rules ..................................................................................214 Propagating Exceptions ..................................................................................214
  10. x Sams Teach Yourself PL/SQL in 21 Days, Second Edition Summary ..............................................................................................................215 Q&A ....................................................................................................................215 Workshop ............................................................................................................215 Quiz ................................................................................................................215 Exercises ........................................................................................................216 WEEK 1 In Review 217 WEEK 2 At a Glance 219 DAY 8 Using SQL 221 Types of SQL DML Statements ..........................................................................223 Creating Some Oracle Tables ..............................................................................223 Using the INSERT Statement ................................................................................226 Inserting Some Data ......................................................................................227 Singleton SELECT Statement ................................................................................229 Some SELECT Command Examples ................................................................229 Exploring the UPDATE and DELETE Statements ....................................................230 Handling Types of Exceptions ............................................................................232 Using the LOCK TABLE Statement ........................................................................233 Transaction Control Statements ..........................................................................234 Using Records......................................................................................................234 Declaring a Record Variable ..........................................................................234 Using the %TYPE Attribute ..............................................................................235 Using Record Variables Based on Database Tables ......................................236 Using Record Variables and SELECT Statements ............................................237 Using Record Variables Based on Cursor Definitions ..................................239 Declarations at the Package Level..................................................................240 Pseudocolumns ....................................................................................................241 CURRVAL and NEXTVAL Pseudocolumns............................................................241 ROWID Pseudocolumns ....................................................................................242 ROWNUM Pseudocolumns ..................................................................................242 Summary ..............................................................................................................242 Q&A ....................................................................................................................243 Workshop ............................................................................................................243 Quiz ................................................................................................................243 Exercises ........................................................................................................243 DAY 9 Manipulating Data with Cursors 245 What Is a Cursor? ................................................................................................245 Explicit Cursors ..................................................................................................246 Declaring a Cursor..........................................................................................246 Declaring Cursor Parameters..........................................................................248
  11. Contents xi Opening the Cursor ........................................................................................248 Fetching Data in a Cursor ..............................................................................250 Closing the Cursor..........................................................................................251 Explicit Cursor Attributes ..............................................................................251 Explicit Cursor Example......................................................................................253 Automated Explicit Cursors ..........................................................................253 Implicit Cursors ..................................................................................................254 Implicit Cursor Attributes ..............................................................................254 Scope of Cursor Parameters ................................................................................256 Cursor Return Clause and Packages....................................................................256 Cursor Variables ..................................................................................................257 The Cursor Variable Declaration ....................................................................257 Cursor Usage with Cursor Variables ..............................................................258 Cursor Variable Assignments..........................................................................260 Cursor Variables as Arguments ......................................................................260 The Current Row of Cursors ..............................................................................261 Cursor Scoping ....................................................................................................262 Cursor Alias ........................................................................................................263 Summary ..............................................................................................................263 Q&A ....................................................................................................................263 Workshop ............................................................................................................264 Quiz ................................................................................................................264 Exercise ..........................................................................................................264 DAY 10 Collections 265 Using PL/SQL Index-by Tables ..........................................................................266 Declaring an Index-by Table ..........................................................................266 Inserting Entries into an Index-by Table ........................................................267 Referencing Values in an Index-by Table ......................................................268 Changing Table Entries ..................................................................................270 Deleting Table Entries ....................................................................................270 PL/SQL Table Methods ..................................................................................271 Using Nested Tables ............................................................................................274 Declaring a Nested Table................................................................................275 Adding Entries to a Nested Table ..................................................................275 Removing Entries from a Nested Table ........................................................279 Using Variable-Sized Arrays................................................................................281 Declaring and Initializing a Varray ................................................................282 Adding and Removing Data from a Varray....................................................283 Taking Advantage of Bulk Binding ....................................................................284 Using BULK COLLECT ......................................................................................285 Using FORALL ..................................................................................................286
  12. xii Sams Teach Yourself PL/SQL in 21 Days, Second Edition Exception Handling for Collections ....................................................................288 Summary ..............................................................................................................289 Q&A ....................................................................................................................289 Workshop ............................................................................................................290 Quiz ................................................................................................................290 Exercise ..........................................................................................................291 DAY 11 Writing Database Triggers 293 What Is a Trigger? ..............................................................................................294 DML Triggers ......................................................................................................294 An Example of a DML Trigger......................................................................295 Types of DML Triggers..................................................................................296 The Syntax for Defining a Database Trigger ................................................298 Uses for Triggers..................................................................................................300 Maintaining Data Integrity ............................................................................300 Maintaining History........................................................................................304 Managing Triggers ..............................................................................................307 Listing Triggers ..............................................................................................308 Viewing Trigger Code ....................................................................................309 Enabling and Disabling Triggers....................................................................310 Nontraditional Triggers........................................................................................311 The Syntax for Defining a Trigger on a Database or Schema Event ............312 Event Attributes ..............................................................................................313 Writing a Trigger for a Database Event ........................................................314 Writing a Trigger for a DDL Event................................................................316 Using Instead-of Triggers ....................................................................................318 The Syntax for Defining an Instead-of Trigger..............................................318 Writing an Instead-of Trigger ........................................................................319 Exploring Trigger Limitations ............................................................................320 Triggers and Mutating Tables ........................................................................320 Summary ..............................................................................................................329 Q&A ....................................................................................................................329 Workshop ............................................................................................................330 Quiz ................................................................................................................330 Exercises ........................................................................................................331 DAY 12 Using Oracle8i Objects for Object-Oriented Programming 333 A Brief Primer on Object-Oriented Programming ..............................................334 Encapsulation..................................................................................................334 Inheritance ......................................................................................................335 Polymorphism ................................................................................................335 Classes, Objects, Attributes, and Methods ....................................................336 Advantages of OOP Over Traditional Methods ............................................336
  13. Contents xiii How Oracle8i Implements Objects......................................................................337 Object Types ..................................................................................................337 Object Tables ..................................................................................................337 Object Views ..................................................................................................337 Defining an Object Type......................................................................................337 Constructor Methods ......................................................................................341 Accessor Methods ..........................................................................................342 Mutator Methods ............................................................................................342 Instantiating and Using Objects ..........................................................................343 Storing and Retrieving Objects............................................................................345 Storing Objects as Table Columns ................................................................345 Retrieving and Updating Objects in a Table Column ....................................347 Using Object Tables ............................................................................................349 Storing Objects in an Object Table ................................................................351 Retrieving and Updating Objects in an Object Table ....................................352 Deleting Objects from an Object Table..........................................................356 Nesting and Sharing Objects ..............................................................................356 Dot Notation ..................................................................................................356 Object References ..........................................................................................358 Exploring the SELF Parameter..............................................................................361 Overloading..........................................................................................................363 Comparing Objects ..............................................................................................363 The ORDER Method ..........................................................................................364 The MAP Method..............................................................................................366 Limitations of Oracle’s Implementation..............................................................367 Summary ..............................................................................................................367 Q&A ....................................................................................................................368 Workshop ............................................................................................................369 Quiz ................................................................................................................369 Exercises ........................................................................................................369 DAY 13 Debugging Your Code and Preventing Errors 371 Locating and Eliminating Syntax Errors ............................................................372 Identifying Syntax Errors ..............................................................................372 An Example of Identifying Syntax Errors ....................................................373 Preventing Syntax Errors................................................................................377 Handling Logic Errors ........................................................................................378 Order of Operations........................................................................................379 Nonterminating Loops....................................................................................380 Debugging Approaches for Logic Errors ......................................................380 Using Tools to Help in Debugging a Program ....................................................384 Using DBMS_OUTPUT as a Debugging Tool ......................................................385 Writing a DEBUG Package ................................................................................385
  14. xiv Sams Teach Yourself PL/SQL in 21 Days, Second Edition Preventing Errors and Planning for Debugging in the Future ............................391 Defining Requirements and Planning Projects ..............................................391 Using a Modular Approach to Coding ..........................................................392 Commenting Code..........................................................................................392 Writing Assertions into Code ........................................................................394 Formatting Code ............................................................................................395 Summary ..............................................................................................................398 Q&A ....................................................................................................................398 Workshop ............................................................................................................399 Quiz ................................................................................................................399 Exercises ........................................................................................................400 DAY 14 Leveraging Large Object Types 401 Exploring Large Object Datatypes ......................................................................402 Limitations of LOBs ........................................................................................402 LOB Versus LONG Datatypes ............................................................................403 Converting LONGs to LOBs ..............................................................................404 Using External Files in an Oracle8i Database ....................................................404 Creating the Directory Object ........................................................................404 Limitations of BFILE ......................................................................................405 Using the DBMS_LOB Package with BFILE ............................................................406 The BFILENAME Function ................................................................................406 The COMPARE Function ....................................................................................407 The FILECLOSE Procedure..............................................................................408 The FILECLOSEALL Procedure ........................................................................408 The FILEEXISTS Function ..............................................................................408 The FILEGETNAME Procedure ..........................................................................409 The FILEISOPEN Function ..............................................................................409 The FILEOPEN Procedure ................................................................................410 The GETLENGTH Function ................................................................................410 The INSTR Function ........................................................................................410 The READ Procedure ........................................................................................411 The SUBSTR Function ......................................................................................411 Exploring BFILE Examples, Using the DBMS_LOB Package ..................................412 Accessing BFILEs............................................................................................412 Comparing Files ............................................................................................414 Working with Locators ........................................................................................416 Using the DBMS_LOB Package with Internal LOBs ................................................416 The APPEND Procedure ....................................................................................417 The COMPARE Function ....................................................................................417 The COPY Procedure ........................................................................................418 The EMPTY_BLOB Function ..............................................................................419 The EMPTY_CLOB Function ..............................................................................419 The ERASE Procedure ......................................................................................419
  15. Contents xv The GETLENGTH Function ................................................................................420 The INSTR Function ........................................................................................420 The READ Procedure ........................................................................................421 The SUBSTR Function ......................................................................................421 The TRIM Procedure ........................................................................................422 The WRITE Procedure ......................................................................................422 Exploring Internal LOB Examples, Using the DBMS_LOB Package ........................423 Creating a Table..............................................................................................423 Adding Data to the Table................................................................................423 Populating the LOBS Table with the COPY Procedure ......................................424 Manipulating Internal LOBs by Using APPEND and WRITE ..............................425 Analyzing the Contents of an Internal LOB ....................................................427 Using TRIM and ERASE to Edit CLOBs ..............................................................428 Temporary LOBs....................................................................................................430 Managing Temporary LOBs ............................................................................430 Creating Temporary LOBs................................................................................430 Summary ..............................................................................................................431 Q&A ....................................................................................................................431 Workshop ............................................................................................................432 Quiz ................................................................................................................432 Exercise ..........................................................................................................432 WEEK 2 In Review 433 WEEK 3 At a Glance 435 DAY 15 Managing Transactions and Locks 437 Types of Transactions ..........................................................................................438 Starting a Transaction ..........................................................................................438 Ending a Transaction ..........................................................................................439 Canceling a Transaction ......................................................................................441 Exploring the Two-Phase Commit ......................................................................441 Creating Bookmarks with Savepoints ................................................................442 Using Release Option ..........................................................................................443 Using Locking......................................................................................................443 Locking Tables ..............................................................................................443 Locking Rows ................................................................................................445 Explicit Locks ................................................................................................445 The DBMS_LOCK Package..................................................................................446 Using Other Locks..........................................................................................449 Monitoring Locks ..........................................................................................449
  16. xvi Sams Teach Yourself PL/SQL in 21 Days, Second Edition Summary ..............................................................................................................450 Q&A ....................................................................................................................450 Workshop ............................................................................................................450 Quiz ................................................................................................................450 Exercise ..........................................................................................................450 DAY 16 Generating Dynamic SQL 451 What Is Dynamic SQL and Why Use It? ............................................................452 Two Approaches to Dynamic SQL ......................................................................452 Using the DBMS_SQL Package................................................................................453 Using the DBMS_SQL Package with Non-Query DDL and DML Statements ....................................................................................................454 Using the DBMS_SQL Package with Queries ....................................................460 Using the DBMS_SQL Package with Anonymous PL/SQL Blocks ..................467 DBMS_SQL Error Handling................................................................................471 Using LAST_ROW_COUNT ..................................................................................471 Using LAST_ROW_ID ........................................................................................471 Using LAST_ERROR_POSITION ..........................................................................474 Using Native Dynamic SQL ................................................................................474 Executing DML and DDL..............................................................................474 Executing SQL Queries..................................................................................476 Executing PL/SQL Blocks ............................................................................479 Summary ..............................................................................................................480 Q&A ....................................................................................................................480 Workshop ............................................................................................................481 Quiz ................................................................................................................481 Exercise ..........................................................................................................481 DAY 17 Writing to Files and the Display 483 Exploring the DBMS_OUTPUT Package ..................................................................484 Enabling the DBMS_OUTPUT Package................................................................484 Disabling the DBMS_OUTPUT Package ..............................................................485 Writing Data to the Buffer ............................................................................486 Reading Data from the Buffer........................................................................487 Exceptions Raised from the DBMS_OUTPUT Package ......................................491 Reading and Writing Files with the UTL_FILE Package ......................................491 File Input ........................................................................................................492 Using UTL_FILE Procedures and Functions ....................................................493 An Example of Using UTL_FILE ....................................................................500 Exploring TEXT_IO with Client Files ..................................................................504 Summary ..............................................................................................................506 Q&A ....................................................................................................................506 Workshop ............................................................................................................507 Quiz ................................................................................................................507 Exercise ..........................................................................................................507
  17. Contents xvii DAY 18 Managing Database Jobs 509 Submitting Jobs by Using the DBMS_JOB Package ..............................................510 Using Background Processes ..............................................................................510 Executing Jobs ....................................................................................................512 Submitting Jobs to the Job Queue by Using SUBMIT ......................................512 Using RUN to Execute Jobs Immediately ........................................................514 Exploring the Job Environment......................................................................515 Viewing Jobs........................................................................................................517 Samples for Viewing Jobs ..............................................................................519 Exploring Job Management ................................................................................519 Removing a Job ..............................................................................................520 Altering a Job ................................................................................................520 Importing and Exporting Jobs ........................................................................521 Handling Broken Jobs ....................................................................................521 Hands-on Practice in Job Management ..............................................................522 Creating Procedures to Submit as Jobs ..........................................................522 Submitting All Jobs to the Job Queue............................................................524 Running All Three Jobs Immediately ............................................................524 Viewing Information About the Jobs ............................................................525 Altering the Job by Running the HELLO Job ..................................................525 Removing the Hello Job ................................................................................526 Summary ..............................................................................................................528 Q&A ....................................................................................................................528 Workshop ............................................................................................................529 Quiz ................................................................................................................529 Exercises ........................................................................................................529 DAY 19 Alerting and Communicating with Other Procedures: The DBMS_ALERT and DBMS_PIPE Packages 531 The DBMS_ALERT Package ....................................................................................532 Using SIGNAL to Issue an Alert ............................................................................533 Registering for an Alert ......................................................................................533 Waiting for a Specific Alert ................................................................................534 Waiting for Any Registered Alert ........................................................................535 Removing One Alert ............................................................................................535 Removing All Alerts ............................................................................................536 Polling and Events with the SET_DEFAULTS Procedure ........................................536 Demonstrating the DBMS_ALERT Package..............................................................536 Creating the Backup Database ......................................................................537 Creating the Trigger to Signal an Alert ..........................................................538 Waiting for the Alert ......................................................................................539 The DBMS_PIPE Package ......................................................................................543 Public Versus Private Pipes ............................................................................543 Using Pipes ....................................................................................................544
  18. xviii Sams Teach Yourself PL/SQL in 21 Days, Second Edition The Functions and Procedures of DBMS_PIPE ................................................546 An Example of Using Pipes ..........................................................................550 Other Pipe Functions and Procedures ............................................................553 Other Uses for Pipes ......................................................................................555 Alerts Versus Pipes ..............................................................................................555 Summary ..............................................................................................................556 Q&A ....................................................................................................................556 Workshop ............................................................................................................557 Quiz ................................................................................................................557 Exercises ........................................................................................................558 DAY 20 PL/SQL and Java 559 Using Oracle’s Java Engine ................................................................................560 Will PL/SQL Go Away? ......................................................................................561 Creating a Java Procedure....................................................................................561 Prerequisites Needed ......................................................................................561 The SS_CONSTANTS Class ................................................................................562 Loading the Class ..........................................................................................563 Publishing the SSThresh Method ..................................................................565 Calling SS_THRESH from SQL*Plus ................................................................566 Calling SS_THRESH from PL/SQL ..................................................................567 Dropping the SSConstants Class ..................................................................568 Calling Java from PL/SQL ..................................................................................569 The Employee Class ........................................................................................569 Publishing a Java Method ..............................................................................571 Datatype Compatibility ..................................................................................574 Using the OUT and IN OUT Arguments ............................................................575 Calling PL/SQL from Java Using SQLJ..............................................................578 Using SQLJ to Execute an Anonymous PL/SQL Block ................................578 Using SQLJ to Call a PL/SQL Procedure or Function ..................................579 Summary ..............................................................................................................582 Q&A ....................................................................................................................583 Workshop ............................................................................................................583 Quiz ................................................................................................................584 Exercise ..........................................................................................................584 DAY 21 Advanced Queuing 585 What Is AQ? ........................................................................................................586 Removing Some of the Mystery from AQ ....................................................586 Key Features of AQ ........................................................................................587 Configuring Your Server to Use AQ ....................................................................588 Exploring an AQ Scenario ..................................................................................590 Creating a Message Queue ..................................................................................591
  19. Contents xix Defining a Type for the Payload ....................................................................591 Creating the Queue Table ..............................................................................592 Creating the Queue ........................................................................................595 Starting and Stopping a Queue ............................................................................597 Placing Messages in a Queue ..............................................................................598 Enqueue-Related Record Types ....................................................................599 Enqueuing a Message ....................................................................................602 Retrieving Messages from a Queue ....................................................................604 Dequeue-Related Record Types ....................................................................604 Dequeuing a Message ....................................................................................606 Using Other AQ Procedures ..............................................................................608 Dropping a Queue ..........................................................................................608 Dropping a Queue Table ................................................................................609 Granting and Revoking Access to a Queue....................................................609 Summary ..............................................................................................................610 Q&A ....................................................................................................................611 Workshop ............................................................................................................611 Quiz ................................................................................................................611 Exercises ........................................................................................................612 WEEK 3 In Review 613 APPENDIX A Answers 615 Index 659
  20. About the Authors Jonathan Gennick is a manager in KPMG Consulting’s Public Services practice, where he specializes in Oracle technologies. He got his start with relational database systems in 1990, first working with Ingres, and then later with Digital’s Rdb software. In 1994 he made the leap to Oracle, and hasn’t looked back yet. Much of his time is spent providing database administration services, as well as server-side PL/SQL support, to clients and internal project teams. When he is not working with Oracle, Jonathan can often be found writing about it. Since 1997 he has been involved with a number of writing projects. He coauthored the first edition of this book, Sams Teach Yourself PL/SQL in 21 Days. He was the technical edi- tor for Oracle8 Server Unleashed (Sams, 1998), and the development editor for Sams Teach Yourself Access 2000 in 24 Hours (Sams, 1999). His second book, SQL*Plus, The Definitive Guide, was published in 1999 by O’Reilly & Associates. In addition to coauthoring the second edition of this book, Jonathan continues to be involved in various other writing and editing projects. Writing has given Jonathan the opportunity to pursue several interests outside his normal day-to-day work. He is a member of the technology committee for the Greater Lansing Adventist School, where he is currently helping to set up both a LAN and an Internet connection. This satisfies his gnawing need to learn new things, and also his desire to help others learn. Jonathan is a member of MENSA, and he holds a bachelor of arts degree in information and computer science from Andrews University in Berrien Springs, Michigan. Jonathan resides in Lansing, Michigan, with his wife, Donna, and their two children, Jenny and Jeff. Jonathan can be contacted by email at jonathan@gennick.com. Tom Luers, PMP, is a principle consultant with a leading international information tech- nology consulting firm. Over the past 16 years, he has worked with clients and business partners in Europe, North America, and Asia. Tom specializes in Project Management, Oracle technologies, and implementing IT solutions to meet business, manufacturing, and engineering needs. He is also the author of Sams Publishing’s Essential Oracle7.
Đồng bộ tài khoản