Oracle PL SQL Recipes A Problem-Solution Approach (Josh Juneau, Matt Arena) (Z-Library)

📄 File Format: PDF

💾 File Size: 5.7 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

Books for professionals By professionals® Oracle PL/SQL Recipes Oracle PL/SQL Recipes provides quick and concise programming solutions for busy professionals who are developing solutions built on Oracle’s market-lead- ing, database technology. The book’s example-based approach respects your time by presenting you with an easy-to-read, problem/solution format. Scan the table-of-contents for the programming problem you need to solve, open the book to the indicated page, see an example solution in code that you can immediately copy and adapt to your needs. Solutions in the book are grouped into chapters by topic area for ease of reference. You’ll find coverage showing how to: • Write stored procedures and functions • Trap exceptions and errors • Diagnose and improve performance • Integrate PL/SQL with web applications • Debug and Unit-test your code • and more! PL/SQL is an important language to learn. It opens the door to centralizing business logic in the database. It is the venue in which many of Oracle’s new- est features are exposed. It is the “rock” underlying the wildly successful Oracle Application Express. Anyone who does anything with Oracle should learn something of the language in this book. And the code examples in this book make that learning fast and easy. You don’t need to read pages and pages of prose to get to the point. We begin with the point, which is the code. Open the book. Read the code. Copy. Adapt. It’s that simple. Josh Juneau, Author of The Definitive Guide to Jython: Python for the Java Platform US $59.99 Shelve in: Databases / Oracle User level: Beginning–Intermediate THE APRESS ROADMAP Troubleshooting Oracle Performance Expert Oracle Database Architecture Oracle PL/SQL Recipes Pro Oracle SQL Beginning Oracle SQL Beginning Oracle PL/SQL www.apress.com Companion eBook Matt Arena Juneau Arena Companion eBook AvailableOracle PL/SQL Recipes The eXperT’s Voice® in oracle Josh Juneau and Matt Arena Quick and reliable solutions for developers and database administrators Oracle PL/SQL Recipes A Problem-Solution Approach

📄 Page 2

D ow nl oa d fr om W ow ! e B oo k < w w w .w ow eb oo k. co m >

📄 Page 3

Oracle and PL/SQL Recipes A Problem-Solution Approach         Josh Juneau Matt Arena

📄 Page 4

Oracle and PL/SQL Recipes: A Problem-Solution Approach Copyright © 2010 by Josh Juneau and Matt Arena All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-4302-3207-0 ISBN-13 (electronic): 978-1-4302-3208-7 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. President and Publisher: Paul Manning Lead Editor: Jonathan Gennick Development Editor: Jonathan Gennick Technical Reviewer: Bob Bryla Editorial Board: Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes, Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Coordinating Editor: Adam Heath Copy Editor: Kim Wimpsett Compositor: Bytheway Publishing Services Indexer: BIM Indexing & Proofreading Services Artist: April Milne Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders- ny@springer-sbm.com, or visit www.springeronline.com. For information on translations, please e-mail rights@apress.com, or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/info/bulksales. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at www.apress.com.

📄 Page 5

 CONTENTS AT A GLANCE iii Contents at a Glance  About the Authors............................................................................................ xxxiv  About the Technical Reviewer .......................................................................... xxxv  Acknowledgments ........................................................................................... xxxvi  Introduction ................................................................................................... xxxviii  Chapter 1: PL/SQL Fundamentals ...........................................................................1  Chapter 2: Essential SQL.......................................................................................15  Chapter 3: Looping and Logic ...............................................................................43  Chapter 4: Functions, Packages, and Procedures ...............................................63  Chapter 5: Triggers ...............................................................................................93  Chapter 6: Type Conversion................................................................................119  Chapter 7: Numbers, Strings, and Dates ............................................................133  Chapter 8: Dynamic SQL .....................................................................................155  Chapter 9: Exceptions.........................................................................................187  Chapter 10: PL/SQL Collections and Records .....................................................215  Chapter 11: Automating Routine Tasks ..............................................................233  Chapter 12: Oracle SQL Developer ......................................................................247  Chapter 13: Analyzing and Improving Performance...........................................281  Chapter 14: Using PL/SQL on the Web ................................................................291  Chapter 15: Java in the Database.......................................................................319  Chapter 16: Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython...............345  Chapter 17: Unit Testing With utPLSQL...............................................................361  Index ...................................................................................................................391

📄 Page 6

 CONTENTS iv Contents  About the Authors............................................................................................ xxxiv  About the Technical Reviewer .......................................................................... xxxv  Acknowledgments ........................................................................................... xxxvi  Introduction ................................................................................................... xxxviii  Chapter 1: PL/SQL Fundamentals ...........................................................................1 1-1. Creating a Block of Code............................................................................................1 Problem .................................................................................................................................................... 1 Solution .................................................................................................................................................... 1 How It Works ............................................................................................................................................ 2 1-2. Executing a Block of Code in SQL*Plus ......................................................................2 Problem .................................................................................................................................................... 2 Solution .................................................................................................................................................... 2 How It Works ............................................................................................................................................ 2 1-3. Storing Code in a Script .............................................................................................3 Problem .................................................................................................................................................... 3 Solution .................................................................................................................................................... 3 How It Works ............................................................................................................................................ 4 1-4. Executing a Stored Script...........................................................................................4 Problem .................................................................................................................................................... 4 Solution .................................................................................................................................................... 4 How It Works ............................................................................................................................................ 5 1-5. Accepting User Input from the Keyboard ...................................................................5 Problem .................................................................................................................................................... 5

📄 Page 7

 CONTENTS v Solution .................................................................................................................................................... 5 How It Works ............................................................................................................................................ 5 1-6. Displaying Results in SQL*Plus ..................................................................................7 Problem .................................................................................................................................................... 7 Solution .................................................................................................................................................... 7 How It Works ............................................................................................................................................ 8 1-7. Commenting Your Code..............................................................................................8 Problem .................................................................................................................................................... 8 Solution .................................................................................................................................................... 8 How It Works ............................................................................................................................................ 9 1-8. Referencing a Block of Code ......................................................................................9 Problem .................................................................................................................................................... 9 Solution .................................................................................................................................................... 9 How It Works .......................................................................................................................................... 10 1-9. Referring to Variables from Nested Blocks ..............................................................10 Problem .................................................................................................................................................. 10 Solution .................................................................................................................................................. 10 How It Works .......................................................................................................................................... 11 1-10. Ignoring Substitution Variables ..............................................................................12 Problem .................................................................................................................................................. 12 Solution #1 ............................................................................................................................................. 12 Solution #2 ............................................................................................................................................. 12 How It Works .......................................................................................................................................... 12 1-11. Changing the Substitution Variable Character .......................................................13 Problem .................................................................................................................................................. 13 Solution .................................................................................................................................................. 13 How It Works .......................................................................................................................................... 14 1-12. Creating a Variable to Match a Database Column Type .........................................14

📄 Page 8

 CONTENTS vi Problem .................................................................................................................................................. 14 Solution .................................................................................................................................................. 14 How It Works .......................................................................................................................................... 14  Chapter 2: Essential SQL.......................................................................................15 2-1. Retrieving a Single Row from the Database.............................................................15 Problem .................................................................................................................................................. 15 Solution #1 ............................................................................................................................................. 15 Solution #2 ............................................................................................................................................. 16 How It Works .......................................................................................................................................... 17 2-2. Qualifying Column and Variable Names ...................................................................18 Problem .................................................................................................................................................. 18 Solution .................................................................................................................................................. 19 How It Works .......................................................................................................................................... 19 2-3. Declaring Variable Types That Match Column Types ...............................................20 Problem .................................................................................................................................................. 20 Solution .................................................................................................................................................. 20 How It Works .......................................................................................................................................... 21 2-4. Returning Queried Data into a PL/SQL Record .........................................................21 Problem .................................................................................................................................................. 21 Solution .................................................................................................................................................. 21 How It Works .......................................................................................................................................... 22 2-5. Creating Your Own Records to Receive Query Results ............................................22 Problem .................................................................................................................................................. 22 Solution .................................................................................................................................................. 22 How It Works .......................................................................................................................................... 23 2-6. Looping Through Rows from a Query.......................................................................24 Problem .................................................................................................................................................. 24 Solution #1 ............................................................................................................................................. 24

📄 Page 9

 CONTENTS vii Solution #2 ............................................................................................................................................. 24 How It Works .......................................................................................................................................... 25 2-7. Obtaining Environment and Session Information .....................................................25 Problem .................................................................................................................................................. 25 Solution .................................................................................................................................................. 25 How It Works .......................................................................................................................................... 26 2-8. Formatting Query Results.........................................................................................29 Problem .................................................................................................................................................. 29 Solution .................................................................................................................................................. 29 How It Works .......................................................................................................................................... 30 2-9. Updating Rows Returned by a Query........................................................................31 Problem .................................................................................................................................................. 31 Solution .................................................................................................................................................. 31 How It Works .......................................................................................................................................... 32 2-10. Updating Rows Returned by a Cursor ....................................................................33 Problem .................................................................................................................................................. 33 Solution .................................................................................................................................................. 33 How It Works .......................................................................................................................................... 33 2-11. Deleting Rows Returned by a Cursor .....................................................................34 Problem .................................................................................................................................................. 34 Solution .................................................................................................................................................. 34 How It Works .......................................................................................................................................... 35 2-12. Performing a Transaction.......................................................................................35 Problem .................................................................................................................................................. 35 Solution .................................................................................................................................................. 35 How It Works .......................................................................................................................................... 36 2-13. Ensuring That Multiple Queries “See” the Same Data ...........................................37 Problem .................................................................................................................................................. 37

📄 Page 10

 CONTENTS viii Solution .................................................................................................................................................. 37 How It Works .......................................................................................................................................... 37 2-14. Executing One Transaction from Within Another ...................................................38 Problem .................................................................................................................................................. 38 Solution .................................................................................................................................................. 38 How It Works .......................................................................................................................................... 39 2-15. Finding and Removing Duplicate Table Rows ........................................................40 Problem .................................................................................................................................................. 40 Solution .................................................................................................................................................. 40 How It Works .......................................................................................................................................... 41  Chapter 3: Looping and Logic ...............................................................................43 3-1. Choosing When to Execute Code..............................................................................43 Problem .................................................................................................................................................. 43 Solution .................................................................................................................................................. 43 How It Works .......................................................................................................................................... 43 3-2. Choosing Between Two Mutually Exclusive Conditions ...........................................44 Problem .................................................................................................................................................. 44 Solution .................................................................................................................................................. 44 How It Works .......................................................................................................................................... 45 3-3. Evaluating Multiple Mutually Exclusive Conditions ..................................................45 Problem .................................................................................................................................................. 45 Solution #1 ............................................................................................................................................. 45 Solution #2 ............................................................................................................................................. 46 How It Works .......................................................................................................................................... 47 3-4. Driving from an Expression Having Multiple Outcomes ...........................................48 Problem .................................................................................................................................................. 48 Solution .................................................................................................................................................. 48 How It Works .......................................................................................................................................... 49

📄 Page 11

 CONTENTS ix 3-5. Looping Until a Specified Condition Is Met...............................................................49 Problem .................................................................................................................................................. 49 Solution .................................................................................................................................................. 50 How It Works .......................................................................................................................................... 50 3-6. Iterating Cursor Results Until All Rows Have Been Returned...................................51 Problem .................................................................................................................................................. 51 Solution .................................................................................................................................................. 51 How It Works .......................................................................................................................................... 52 3-7. Iterating Until a Condition Evaluates to FALSE .........................................................52 Problem .................................................................................................................................................. 52 Solution .................................................................................................................................................. 52 How It Works .......................................................................................................................................... 53 3-8. Bypassing the Current Loop Iteration.......................................................................53 Problem .................................................................................................................................................. 53 Solution .................................................................................................................................................. 53 How It Works .......................................................................................................................................... 54 3-9. Iterating a Fixed Number of Times...........................................................................55 Problem .................................................................................................................................................. 55 Solution .................................................................................................................................................. 55 How It Works .......................................................................................................................................... 55 3-10. Iterating Backward Through a Range.....................................................................56 Problem .................................................................................................................................................. 56 Solution .................................................................................................................................................. 56 How It Works .......................................................................................................................................... 56 3-11. Iterating in Increments Other Than One .................................................................57 Problem .................................................................................................................................................. 57 Solution .................................................................................................................................................. 57 How It Works .......................................................................................................................................... 57

📄 Page 12

 CONTENTS x 3-12. Stepping Through a Loop Based on Odd-Numbered Increments . ..........................58 Problem . ................................................................................................................................................ 58 Solution . ................................................................................................................................................ 58 How It Works . ........................................................................................................................................ 58 3-13. Exiting an Outer Loop Prematurely. ........................................................................59 Problem . ................................................................................................................................................ 59 Solution . ................................................................................................................................................ 59 How It Works . ........................................................................................................................................ 59 3-14. Jumping to a Designated Location in Code. ...........................................................60 Problem . ................................................................................................................................................ 60 Solution . ................................................................................................................................................ 60 How It Works . ........................................................................................................................................ 61  Chapter 4: Functions, Packages, and Procedures . ..............................................63 4-1. Creating a Stored Function. ......................................................................................63 Problem . ................................................................................................................................................ 63 Solution . ................................................................................................................................................ 64 How It Works . ........................................................................................................................................ 65 4-2. Executing a Stored Function from a Query. ..............................................................67 Problem . ................................................................................................................................................ 67 Solution . ................................................................................................................................................ 67 How It Works . ........................................................................................................................................ 67 4-3. Optimizing a Function That Will Always Return the Same Result for a Given Input. ..............................................................................................................68 Problem . ................................................................................................................................................ 68 Solution . ................................................................................................................................................ 68 How It Works . ........................................................................................................................................ 69 4-4. Creating a Stored Procedure . ...................................................................................69 Problem . ................................................................................................................................................ 69 Solution . ................................................................................................................................................ 69 D ow nl oa d fr om W ow ! e B oo k < w w w .w ow eb oo k. co m >

📄 Page 13

 CONTENTS xi How It Works .......................................................................................................................................... 70 4-5. Executing a Stored Procedure..................................................................................71 Problem .................................................................................................................................................. 71 Solution .................................................................................................................................................. 71 How It Works .......................................................................................................................................... 72 4-6. Creating Functions Within a Procedure or Code Block.............................................73 Problem .................................................................................................................................................. 73 Solution .................................................................................................................................................. 73 How It Works .......................................................................................................................................... 74 4-7. Passing Parameters by Name ..................................................................................74 Problem .................................................................................................................................................. 74 Solution .................................................................................................................................................. 74 How It Works .......................................................................................................................................... 75 4-8. Setting Default Parameter Values ............................................................................75 Problem .................................................................................................................................................. 75 Solution .................................................................................................................................................. 75 How It Works .......................................................................................................................................... 76 4-9. Collecting Related Routines into a Single Unit .........................................................76 Problem .................................................................................................................................................. 76 Solution .................................................................................................................................................. 76 How It Works .......................................................................................................................................... 78 4-10. Writing Initialization Code for a Package ...............................................................79 Problem .................................................................................................................................................. 79 Solution .................................................................................................................................................. 79 How It Works .......................................................................................................................................... 80 4-11. Granting the Ability to Create and Execute Stored Programs.................................80 Problem .................................................................................................................................................. 80 Solution .................................................................................................................................................. 81

📄 Page 14

 CONTENTS xii How It Works .......................................................................................................................................... 81 4-12. Executing Packaged Procedures and Functions ....................................................81 Problem .................................................................................................................................................. 81 Solution .................................................................................................................................................. 81 How It Works .......................................................................................................................................... 81 4-13. Creating a Public Name for a Stored Program .......................................................82 Problem .................................................................................................................................................. 82 Solution .................................................................................................................................................. 82 How It Works .......................................................................................................................................... 83 4-14. Executing Package Programs in Sequence............................................................83 Problem .................................................................................................................................................. 83 Solution .................................................................................................................................................. 84 How It Works .......................................................................................................................................... 85 4-15. Implementing a Failure Flag...................................................................................85 Problem .................................................................................................................................................. 85 Solution .................................................................................................................................................. 85 How It Works .......................................................................................................................................... 87 4-16. Forcing Data Access to Go Through Packages.......................................................87 Problem .................................................................................................................................................. 87 Solution .................................................................................................................................................. 87 How It Works .......................................................................................................................................... 88 4-17. Executing Stored Code Under Your Own Privilege Set ...........................................88 Problem .................................................................................................................................................. 88 Solution .................................................................................................................................................. 88 How It Works .......................................................................................................................................... 89 4-18. Accepting Multiple Parameter Sets in One Function..............................................89 Problem .................................................................................................................................................. 89 Solution .................................................................................................................................................. 89

📄 Page 15

 CONTENTS xiii How It Works .......................................................................................................................................... 90 4-19. Listing the Functions, Procedures, and Packages in a Schema.............................90 Problem .................................................................................................................................................. 90 Solution .................................................................................................................................................. 90 How It Works .......................................................................................................................................... 91 4-20. Viewing Source Code for Stored Programs ............................................................91 Problem .................................................................................................................................................. 91 Solution .................................................................................................................................................. 91 How It Works .......................................................................................................................................... 92  Chapter 5: Triggers ...............................................................................................93 5-1. Automatically Generating Column Values ................................................................93 Problem .................................................................................................................................................. 93 Solution .................................................................................................................................................. 93 How It Works .......................................................................................................................................... 94 5-2. Keeping Related Values in Sync...............................................................................95 Problem .................................................................................................................................................. 95 Solution .................................................................................................................................................. 95 How It Works .......................................................................................................................................... 96 5-3. Responding to an Update of a Specific Table Column .............................................97 Problem .................................................................................................................................................. 97 Solution .................................................................................................................................................. 97 How It Works .......................................................................................................................................... 97 5-4. Making a View Updatable.........................................................................................98 Problem .................................................................................................................................................. 98 Solution .................................................................................................................................................. 98 How It Works ........................................................................................................................................ 100 5-5. Altering the Functionality of Applications...............................................................101 Problem ................................................................................................................................................ 101

📄 Page 16

 CONTENTS xiv Solution ................................................................................................................................................ 101 How It Works ........................................................................................................................................ 102 5-6. Validating Input Data ..............................................................................................103 Problem ................................................................................................................................................ 103 Solution ................................................................................................................................................ 103 How It Works ........................................................................................................................................ 104 5-7. Scrubbing Input Data..............................................................................................104 Problem ................................................................................................................................................ 104 Solution ................................................................................................................................................ 104 How It Works ........................................................................................................................................ 104 5-8. Replacing a Column’s Value...................................................................................105 Problem ................................................................................................................................................ 105 Solution ................................................................................................................................................ 105 How It Works ........................................................................................................................................ 106 5-9. Triggering on a System Event ................................................................................107 Problem ................................................................................................................................................ 107 Solution ................................................................................................................................................ 107 How It Works ........................................................................................................................................ 108 5-10. Triggering on a Schema-Related Event................................................................109 Problem ................................................................................................................................................ 109 Solution ................................................................................................................................................ 110 How It Works ........................................................................................................................................ 110 5-11. Firing Two Triggers on the Same Event ...............................................................111 Problem ................................................................................................................................................ 111 Solution ................................................................................................................................................ 111 How It Works ........................................................................................................................................ 112 5-12. Creating a Trigger That Fires on Multiple Events .................................................113 Problem ................................................................................................................................................ 113

📄 Page 17

 CONTENTS xv Solution ................................................................................................................................................ 113 How It Works ........................................................................................................................................ 114 5-13. Creating a Trigger in a Disabled State .................................................................115 Problem ................................................................................................................................................ 115 Solution ................................................................................................................................................ 116 How It Works ........................................................................................................................................ 116  Chapter 6: Type Conversion ................................................................................119 6-1. Converting a String to a Number............................................................................119 Problem ................................................................................................................................................ 119 Solution ................................................................................................................................................ 119 How It Works ........................................................................................................................................ 120 6-2. Converting a String to a Date .................................................................................121 Problem ................................................................................................................................................ 121 Solution ................................................................................................................................................ 121 How It Works ........................................................................................................................................ 121 6-3. Converting a Number to a String............................................................................123 Problem ................................................................................................................................................ 123 Solution ................................................................................................................................................ 123 How It Works ........................................................................................................................................ 123 6-4. Converting a Date to a String .................................................................................124 Problem ................................................................................................................................................ 124 Solution ................................................................................................................................................ 124 How It Works ........................................................................................................................................ 125 6-5. Converting Strings to Timestamps .........................................................................127 Problem ................................................................................................................................................ 127 Solution ................................................................................................................................................ 127 How It Works ........................................................................................................................................ 128 6-6. Writing ANSI-Compliant Conversions .....................................................................129

📄 Page 18

 CONTENTS xvi Problem ................................................................................................................................................ 129 Solution ................................................................................................................................................ 129 How It Works ........................................................................................................................................ 130 6-7. Implicitly Converting Between PLS_INTEGER and NUMBER ..................................131 Problem ................................................................................................................................................ 131 Solution ................................................................................................................................................ 131 How It Works ........................................................................................................................................ 131  Chapter 7: Numbers, Strings, and Dates.............................................................133 7-1. Concatenating Strings............................................................................................133 Problem ................................................................................................................................................ 133 Solution ................................................................................................................................................ 133 How It Works ........................................................................................................................................ 134 7-2. Adding Some Number of Days to a Date ................................................................134 Problem ................................................................................................................................................ 134 Solution ................................................................................................................................................ 134 How It Works ........................................................................................................................................ 134 7-3. Adding a Number of Months to a Date ...................................................................135 Problem ................................................................................................................................................ 135 Solution ................................................................................................................................................ 135 How It Works ........................................................................................................................................ 136 7-4. Adding Years to a Date...........................................................................................137 Problem ................................................................................................................................................ 137 Solution ................................................................................................................................................ 137 How It Works ........................................................................................................................................ 137 7-5. Determining the Interval Between Two Dates........................................................138 Problem ................................................................................................................................................ 138 Solution ................................................................................................................................................ 139 How It Works ........................................................................................................................................ 139

📄 Page 19

 CONTENTS xvii 7-6. Adding Hours, Minutes, Seconds, or Days to a Given Date ....................................140 Problem ................................................................................................................................................ 140 Solution ................................................................................................................................................ 140 How It Works ........................................................................................................................................ 141 7-7. Returning the First Day of a Given Month ..............................................................142 Problem ................................................................................................................................................ 142 Solution ................................................................................................................................................ 142 How It Works ........................................................................................................................................ 143 7-8. Returning the Last Day of a Given Month...............................................................143 Problem ................................................................................................................................................ 143 Solution ................................................................................................................................................ 144 How It Works ........................................................................................................................................ 144 7-9. Rounding a Number ...............................................................................................144 Problem ................................................................................................................................................ 144 Solution ................................................................................................................................................ 144 How It Works ........................................................................................................................................ 145 7-10. Rounding a Datetime Value ..................................................................................145 Problem ................................................................................................................................................ 145 Solution ................................................................................................................................................ 145 How It Works ........................................................................................................................................ 145 7-11. Tracking Time to a Millisecond ............................................................................146 Problem ................................................................................................................................................ 146 Solution ................................................................................................................................................ 146 How It Works ........................................................................................................................................ 147 7-12. Associating a Time Zone with a Date and Time ...................................................147 Problem ................................................................................................................................................ 147 Solution ................................................................................................................................................ 147 How It Works ........................................................................................................................................ 148

📄 Page 20

 CONTENTS xviii 7-13. Finding a Pattern Within a String .........................................................................148 Problem ................................................................................................................................................ 148 Solution ................................................................................................................................................ 148 How It Works ........................................................................................................................................ 149 7-14. Determining the Position of a Pattern Within a String..........................................150 Problem ................................................................................................................................................ 150 Solution ................................................................................................................................................ 150 How It Works ........................................................................................................................................ 151 7-15. Finding and Replacing Text Within a String .........................................................151 Problem ................................................................................................................................................ 151 Solution ................................................................................................................................................ 151 How It Works ........................................................................................................................................ 152  Chapter 8: Dynamic SQL .....................................................................................155 8-1. Executing a Single Row Query That Is Unknown at Compile Time.........................155 Problem ................................................................................................................................................ 155 Solution #1 ........................................................................................................................................... 155 Solution #2 ........................................................................................................................................... 156 How It Works #1 ................................................................................................................................... 158 How It Works #2 ................................................................................................................................... 158 8-2. Executing a Multiple Row Query That Is Unknown at Compile Time......................159 Problem ................................................................................................................................................ 159 Solution #1 ........................................................................................................................................... 159 Solution #2 ........................................................................................................................................... 160 How It Works ........................................................................................................................................ 161 8-3. Writing a Dynamic INSERT Statement....................................................................161 Problem ................................................................................................................................................ 161 Solution ................................................................................................................................................ 161 Solution #2 ........................................................................................................................................... 163