Sumário Itens Encontrados: 2191-1. Creating a Block of Code 11-2. Executing a Block of Code in SQL*Plus 21-3. Storing Code in a Script 31-4. Executing a Stored Script 41-5. Accepting User Input from the Keyboard 51-6. Displaying Results in SQL*Plus 71-7. Commenting Your Code 81-8. Referencing a Block of Code 91-9. Referring to Variables from Nested Blocks 101-10. Ignoring Substitution Variables 121-11. Changing the Substitution Variable Character 131-12. Creating a Variable to Match a Database Column Type 14 Chapter 2: Essential SQL 152-1. Retrieving a Single Row from the Database 152-2. Qualifying Column and Variable Names 182-3. Declaring Variable Types That Match Column Types 202-4. Returning Queried Data into a PL/SQL Record 212-5. Creating Your Own Records to Receive Query Results 222-6. Looping Through Rows from a Query 242-7. Obtaining Environment and Session Information 252-8. Formatting Query Results 292-9. Updating Rows Returned by a Query 312-10. Updating Rows Returned by a Cursor 332-11. Deleting Rows Returned by a Cursor 342-12. Performing a Transaction 352-13. Ensuring That Multiple Queries âSeeâ the Same Data 372-14. Executing One Transaction from Within Another 382-15. Finding and Removing Duplicate Table Rows 40 Chapter 3: Looping and Logic 433-1. Choosing When to Execute Code 433-2. Choosing Between Two Mutually Exclusive Conditions 443-3. Evaluating Multiple Mutually Exclusive Conditions 453-4. Driving from an Expression Having Multiple Outcomes 483-5. Looping Until a Specified Condition Is Met 493-6. Iterating Cursor Results Until All Rows Have Been Returned 513-7. Iterating Until a Condition Evaluates to FALSE 523-8. Bypassing the Current Loop Iteration 533-9. Iterating a Fixed Number of Times 553-10. Iterating Backward Through a Range 563-11. Iterating in Increments Other Than One 573-12. Stepping Through a Loop Based on Odd-Numbered Increments. 583-13. Exiting an Outer Loop Prematurely. 593-14. Jumping to a Designated Location in Code. 60 Chapter 4: Functions, Packages, and Procedures . 634-1. Creating a Stored Function. 634-2. Executing a Stored Function from a Query. 674-3. Optimizing a Function That Will Always Return the Same Result for aGiven Input 684-4. Creating a Stored Procedure. 694-5. Executing a Stored Procedure 714-6. Creating Functions Within a Procedure or Code Block 734-7. Passing Parameters by Name 744-8. Setting Default Parameter Values 754-9. Collecting Related Routines into a Single Unit 764-10. Writing Initialization Code for a Package 794-11. Granting the Ability to Create and Execute Stored Programs 804-12. Executing Packaged Procedures and Functions 814-13. Creating a Public Name for a Stored Program 824-14. Executing Package Programs in Sequence 834-15. Implementing a Failure Flag 854-16. Forcing Data Access to Go Through Packages 874-17. Executing Stored Code Under Your Own Privilege Set 884-18. Accepting Multiple Parameter Sets in One Function 894-19. Listing the Functions, Procedures, and Packages in a Schema 904-20. Viewing Source Code for Stored Programs 91 Chapter 5: Triggers 935-1. Automatically Generating Column Values 935-2. Keeping Related Values in Sync 955-3. Responding to an Update of a Specific Table Column 975-4. Making a View Updatable 985-5. Altering the Functionality of Applications 1015-6. Validating Input Data 1035-7. Scrubbing Input Data 1045-8. Replacing a Columnâs Value 1055-9. Triggering on a System Event 1075-10. Triggering on a Schema-Related Event 1095-11. Firing Two Triggers on the Same Event 1115-12. Creating a Trigger That Fires on Multiple Events 1135-13. Creating a Trigger in a Disabled State 115 Chapter 6: Type Conversion 1196-1. Converting a String to a Number 1196-2. Converting a String to a Date 1216-3. Converting a Number to a String 1236-4. Converting a Date to a String 1246-5. Converting Strings to Timestamps 1276-6. Writing ANSI-Compliant Conversions 1296-7. Implicitly Converting Between PLS_INTEGER and NUMBER 131 Chapter 7: Numbers, Strings, and Dates 1337-1. Concatenating Strings 1337-2. Adding Some Number of Days to a Date 1347-3. Adding a Number of Months to a Date 1357-4. Adding Years to a Date 1377-5. Determining the Interval Between Two Dates 1387-6. Adding Hours, Minutes, Seconds, or Days to a Given Date 1407-7. Returning the First Day of a Given Month 1427-8. Returning the Last Day of a Given Month 1437-9. Rounding a Number 1447-10. Rounding a Datetime Value 1457-11. Tracking Time to a Millisecond 1467-12. Associating a Time Zone with a Date and Time 1477-13. Finding a Pattern Within a String 1487-14. Determining the Position of a Pattern Within a String 1507-15. Finding and Replacing Text Within a String 151 Chapter 8: Dynamic SQL 1558-1. Executing a Single Row Query That Is Unknown at Compile Time 1558-2. Executing a Multiple Row Query That Is Unknown at Compile Time 1598-3. Writing a Dynamic INSERT Statement 1618-4. Writing a Dynamic Update Statement 1658-5. Writing a Dynamic Delete Statement 1698-6. Returning Data from a Dynamic Query into a Record 1708-7. Executing a Dynamic Block of PL/SQL 1728-8. Creating a Table at Runtime 1748-9. Altering a Table at Runtime 1758-10. Finding All Tables That Include a Specific Column Value 1768-11 Storing Dynamic SQL in Large Objects. 1798-12. Passing NULL Values to Dynamic SQL. 1818-13. Switching Between DBMS_SQL and Native Dynamic SQL. 1828-14. Guarding Against SQL Injection Attacks. 185 Chapter 9: Exceptions. 1879-1. Trapping an Exception. 1879-2. Catching Unknown Exceptions. 1929-3. Creating and Raising Named Programmer-Defined Exceptions 1949-4. Determining Which Error Occurred Inside the OTHERS Handler 1979-5. Raising User-Defined Exceptions Without an Exception Handler 2009-6. Redirecting Control After an Exception Is Raised 2029-7. Raising Exceptions and Continuing Processing 2049-8. Associating Error Numbers with Exceptions That Have No Name 2069-9. Tracing an Exception to Its Origin 2079-10. Displaying PL/SQL Compiler Warnings 211 Chapter 10: PL/SQL Collections and Records 21510-1. Creating and Accessing a VARRAY 21510-2. Creating and Accessing an Indexed Table 21610-3. Creating Simple Records 21710-4. Creating and Accessing Record Collections 21810-5. Creating and Accessing Hash Array Collections 21910-6. Creating and Accessing Complex Collections 22010-7. Passing a Collection As a Parameter 22310-8. Returning a Collection As a Parameter 22410-9. Counting the Members in a Collection 22610-10. Deleting a Record from a Collection 22710-11. Checking Whether an Element Exists 22810-12. Increasing the Size of a Collection 22910-13. Navigating Collections 23010-14. Trimming a Collection 232 Chapter 11: Automating Routine Tasks 23311-1. Scheduling Recurring Jobs 23311-2. E-mailing Output from a Scheduled Job 23411-3. Using E-mail for Job Status Notification 23511-4. Refreshing a Materialized View on a Timed Interval 23611-5. Synchronizing Data with a Remote Data Source 23811-6. Scheduling a Job Chain 240 Chapter 12: Oracle SQL Developer 24712-1. Creating Standard and Privileged Database Connections 24712-2. Obtaining Information About Tables 24912-3. Enabling Output to Be Displayed 25112-4. Writing and Executing PL/SQL 25312-5. Creating and Executing a Script 25612-6. Accepting User Input for Substitution Variables 25812-7. Saving Pieces of Code for Quick Access 25912-8. Creating a Function 26112-9. Creating a Stored Procedure 26512-10. Creating a Package Header and Body 26812-11. Creating a Trigger 27212-12. Debugging Stored Code 27612-13. Compiling Code Within the Navigator 278 Chapter 13: Analyzing and Improving Performance 28113-1. Installing DBMS_PROFILER 28113-2. Identifying Bottlenecks 28313-3. Speeding Up Read/Write Loops 28513-4. Passing Large or Complex Collections as OUT Parameters 28713-5. Optimizing Computationally Intensive Code 28813-6. Improving Initial Execution Running Time 290 Chapter 14: Using PL/SQL on the Web 29114-1. Running a PL/SQL Procedure on the Web 29114-2. Creating a Common Set of HTML Page Generation Procedures 29214-3 Creating an Input Form 29514-4. Creating a Webâbased Report Using PL/SQL Procedures 29914-5. Displaying Data from Tables 30214-6. Creating a Web Form Dropdown List from a Database Query 30314-7. Creating a Sortable Web Report 30514-8. Passing Data Between Web Pages 30814-9. Viewing Errors for Debugging Web Apps 31014-10. Generating JavaScript via PL/SQL 31114-11. Generating XML Output 31414-12. Creating an Input Form with AJAX 315 Chapter 15: Java in the Database 31915-1. Creating a Java Database Class 31915-2. Loading a Java Database Class into a Database 32115-3. Loading a Compiled Java Class Into the Database 32315-4. Exposing a Java Class As a Stored Procedure . 32515-5. Executing a Java Stored Procedure . 32515-6. Calling a Java Stored Procedure from PL/SQL . 32615-7. Passing Parameters Between PL/SQL and Java . 32815-8. Creating and Calling a Java Database Function. 33015-9. Creating a Java Database Trigger . 33215-10. Passing Data Objects from PL/SQL to Java. 33415-11. Embedding a Java Class Into a PL/SQL Package 33615-12. Loading Java Libraries Into the Database 33815-13. Removing a Java Class 34015-14. Retrieving Database Metadata with Java 34115-15. Querying the Database to Help Resolve Java Compilation Issues 343 Chapter 16: Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython 34516-1. Accessing a PL/SQL Stored Procedure via JDBC 34516-2. Accessing a PL/SQL Stored Function from JDBC 34816-3. Accessing PL/SQL Web Procedures with HTTP 35016-4. Accessing PL/SQL from Jython 35516-5. Accessing PL/SQL from Groovy 358 Chapter 17: Unit Testing With utPLSQL 36117-1. Testing Stored PL/SQL Code Without Unit Tests 36117-2. Installing the utPLSQL Unit Testing Framework 36317-3. Building a utPLSQL Test Package 36517-4. Writing a utPLSQL Unit Test Procedure 36717-5. Running a utPLSQL Test 36917-6. Building a utPLSQL Test Suite 37317-7. Running a utPLSQL Test Suite 37417-8. Reconfiguring utPLSQL Parameters 38117-9. Redirecting upPLSQL Test Results to a File 38417-10. Automating Unit Tests for PL/SQL and Java Stored Procedures Using Ant 385 Index 391