CHAPTER 2: Essential SQL
by Matt Arena, Josh Juneau
Oracle and PL/SQL Recipes: A Problem-Solution Approach
- Title Page
- Contents at a Glance
- Contents
- About the Authors
- About the Technical Reviewer
- Acknowledgments
- Introduction
- CHAPTER 1: PL/SQL Fundamentals
- 1-1. Creating a Block of Code
- 1-2. Executing a Block of Code in SQL*Plus
- 1-3. Storing Code in a Script
- 1-4. Executing a Stored Script
- 1-5. Accepting User Input from the Keyboard
- 1-6. Displaying Results in SQL*Plus
- 1-7. Commenting Your Code
- 1-8. Referencing a Block of Code
- 1-9. Referring to Variables from Nested Blocks
- 1-10. Ignoring Substitution Variables
- 1-11. Changing the Substitution Variable Character
- 1-12. Creating a Variable to Match a Database Column Type
- CHAPTER 2: Essential SQL
- 2-1. Retrieving a Single Row from the Database
- 2-2. Qualifying Column and Variable Names
- 2-3. Declaring Variable Types That Match Column Types
- 2-4. Returning Queried Data into a PL/SQL Record
- 2-5. Creating Your Own Records to Receive Query Results
- 2-6. Looping Through Rows from a Query
- 2-7. Obtaining Environment and Session Information
- 2-8. Formatting Query Results
- 2-9. Updating Rows Returned by a Query
- 2-10. Updating Rows Returned by a Cursor
- 2-11. Deleting Rows Returned by a Cursor
- 2-12. Performing a Transaction
- 2-13. Ensuring That Multiple Queries “See” the Same Data
- 2-14. Executing One Transaction from Within Another
- 2-15. Finding and Removing Duplicate Table Rows
- CHAPTER 3: Looping and Logic
- 3-1. Choosing When to Execute Code
- 3-2. Choosing Between Two Mutually Exclusive Conditions
- 3-3. Evaluating Multiple Mutually Exclusive Conditions
- 3-4. Driving from an Expression Having Multiple Outcomes
- 3-5. Looping Until a Specified Condition Is Met
- 3-6. Iterating Cursor Results Until All Rows Have Been Returned
- 3-7. Iterating Until a Condition Evaluates to FALSE
- 3-8. Bypassing the Current Loop Iteration
- 3-9. Iterating a Fixed Number of Times
- 3-10. Iterating Backward Through a Range
- 3-11. Iterating in Increments Other Than One
- 3-12. Stepping Through a Loop Based on Odd-Numbered Increments
- 3-13. Exiting an Outer Loop Prematurely
- 3-14. Jumping to a Designated Location in Code
- CHAPTER 4: Functions, Packages, and Procedures
- 4-1. Creating a Stored Function
- 4-2. Executing a Stored Function from a Query
- 4-3. Optimizing a Function That Will Always Return the Same Result for a Given Input
- 4-4. Creating a Stored Procedure
- 4-5. Executing a Stored Procedure
- 4-6. Creating Functions Within a Procedure or Code Block
- 4-7. Passing Parameters by Name
- 4-8. Setting Default Parameter Values
- 4-9. Collecting Related Routines into a Single Unit
- 4-10. Writing Initialization Code for a Package
- 4-11. Granting the Ability to Create and Execute Stored Programs
- 4-12. Executing Packaged Procedures and Functions
- 4-13. Creating a Public Name for a Stored Program
- 4-14. Executing Package Programs in Sequence
- 4-15. Implementing a Failure Flag
- 4-16. Forcing Data Access to Go Through Packages
- 4-17. Executing Stored Code Under Your Own Privilege Set
- 4-18. Accepting Multiple Parameter Sets in One Function
- 4-19. Listing the Functions, Procedures, and Packages in a Schema
- 4-20. Viewing Source Code for Stored Programs
- CHAPTER 5: Triggers
- 5-1. Automatically Generating Column Values
- 5-2. Keeping Related Values in Sync
- 5-3. Responding to an Update of a Specific Table Column
- 5-4. Making a View Updatable
- 5-5. Altering the Functionality of Applications
- 5-6. Validating Input Data
- 5-7. Scrubbing Input Data
- 5-8. Replacing a Column's Value
- 5-9. Triggering on a System Event
- 5-10. Triggering on a Schema-Related Event
- 5-11. Firing Two Triggers on the Same Event
- 5-12. Creating a Trigger That Fires on Multiple Events
- 5-13. Creating a Trigger in a Disabled State
- CHAPTER 6: Type Conversion
- CHAPTER 7: Numbers, Strings, and Dates
- 7-1. Concatenating Strings
- 7-2. Adding Some Number of Days to a Date
- 7-3. Adding a Number of Months to a Date
- 7-4. Adding Years to a Date
- 7-5. Determining the Interval Between Two Dates
- 7-6. Adding Hours, Minutes, Seconds, or Days to a Given Date
- 7-7. Returning the First Day of a Given Month
- 7-8. Returning the Last Day of a Given Month
- 7-9. Rounding a Number
- 7-10. Rounding a Datetime Value
- 7-11. Tracking Time to a Millisecond
- 7-12. Associating a Time Zone with a Date and Time
- 7-13. Finding a Pattern Within a String
- 7-14. Determining the Position of a Pattern Within a String
- 7-15. Finding and Replacing Text Within a String
- CHAPTER 8: Dynamic SQL
- 8-1. Executing a Single Row Query That Is Unknown at Compile Time
- 8-2. Executing a Multiple Row Query That Is Unknown at Compile Time
- 8-3. Writing a Dynamic INSERT Statement
- 8-4. Writing a Dynamic Update Statement
- 8-5. Writing a Dynamic Delete Statement
- 8-6. Returning Data from a Dynamic Query into a Record
- 8-7. Executing a Dynamic Block of PL/SQL
- 8-8. Creating a Table at Runtime
- 8-9. Altering a Table at Runtime
- 8-10. Finding All Tables That Include a Specific Column Value
- 8-11 Storing Dynamic SQL in Large Objects
- 8-12. Passing NULL Values to Dynamic SQL
- 8-13. Switching Between DBMS_SQL and Native Dynamic SQL
- 8-14. Guarding Against SQL Injection Attacks
- CHAPTER 9: Exceptions
- 9-1. Trapping an Exception
- 9-2. Catching Unknown Exceptions
- 9-3. Creating and Raising Named Programmer-Defined Exceptions
- 9-4. Determining Which Error Occurred Inside the OTHERS Handler
- 9-5. Raising User-Defined Exceptions Without an Exception Handler
- 9-6. Redirecting Control After an Exception Is Raised
- 9-7. Raising Exceptions and Continuing Processing
- 9-8. Associating Error Numbers with Exceptions That Have No Name
- 9-9. Tracing an Exception to Its Origin
- 9-10. Displaying PL/SQL Compiler Warnings
- CHAPTER 10: PL/SQL Collections and Records
- 10-1. Creating and Accessing a VARRAY
- 10-2. Creating and Accessing an Indexed Table
- 10-3. Creating Simple Records
- 10-4. Creating and Accessing Record Collections
- 10-5. Creating and Accessing Hash Array Collections
- 10-6. Creating and Accessing Complex Collections
- 10-7. Passing a Collection As a Parameter
- 10-8. Returning a Collection As a Parameter
- 10-9. Counting the Members in a Collection
- 10-10. Deleting a Record from a Collection
- 10-11. Checking Whether an Element Exists
- 10-12. Increasing the Size of a Collection
- 10-13. Navigating Collections
- 10-14. Trimming a Collection
- CHAPTER 11: Automating Routine Tasks
- CHAPTER 12: Oracle SQL Developer
- 12-1. Creating Standard and Privileged Database Connections
- 12-2. Obtaining Information About Tables
- 12-3. Enabling Output to Be Displayed
- 12-4. Writing and Executing PL/SQL
- 12-5. Creating and Executing a Script
- 12-6. Accepting User Input for Substitution Variables
- 12-7. Saving Pieces of Code for Quick Access
- 12-8. Creating a Function
- 12-9. Creating a Stored Procedure
- 12-10. Creating a Package Header and Body
- 12-11. Creating a Trigger
- 12-12. Debugging Stored Code
- 12-13. Compiling Code Within the Navigator
- CHAPTER 13: Analyzing and Improving Performance
- CHAPTER 14: Using PL/SQL on the Web
- 14-1. Running a PL/SQL Procedure on the Web
- 14-2. Creating a Common Set of HTML Page Generation Procedures
- 14-3 Creating an Input Form
- 14-4. Creating a Web–based Report Using PL/SQL Procedures
- 14-5. Displaying Data from Tables
- 14-6. Creating a Web Form Dropdown List from a Database Query
- 14-7. Creating a Sortable Web Report
- 14-8. Passing Data Between Web Pages
- 14-9. Viewing Errors for Debugging Web Apps
- 14-10. Generating JavaScript via PL/SQL
- 14-11. Generating XML Output
- 14-12. Creating an Input Form with AJAX
- CHAPTER 15: Java in the Database
- 15-1. Creating a Java Database Class
- 15-2. Loading a Java Database Class into a Database
- 15-3. Loading a Compiled Java Class Into the Database
- 15-4. Exposing a Java Class As a Stored Procedure
- 15-5. Executing a Java Stored Procedure
- 15-6. Calling a Java Stored Procedure from PL/SQL
- 15-7. Passing Parameters Between PL/SQL and Java
- 15-8. Creating and Calling a Java Database Function
- 15-9. Creating a Java Database Trigger
- 15-10. Passing Data Objects from PL/SQL to Java
- 15-11. Embedding a Java Class Into a PL/SQL Package
- 15-12. Loading Java Libraries Into the Database
- 15-13. Removing a Java Class
- 15-14. Retrieving Database Metadata with Java
- 15-15. Querying the Database to Help Resolve Java Compilation Issues
- CHAPTER 16: Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython
- CHAPTER 17: Unit Testing With utPLSQL
- 17-1. Testing Stored PL/SQL Code Without Unit Tests
- 17-2. Installing the utPLSQL Unit Testing Framework
- 17-3. Building a utPLSQL Test Package
- 17-4. Writing a utPLSQL Unit Test Procedure
- 17-5. Running a utPLSQL Test
- 17-6. Building a utPLSQL Test Suite
- 17-7. Running a utPLSQL Test Suite
- 17-8. Reconfiguring utPLSQL Parameters
- 17-9. Redirecting utPLSQL Test Results to a File
- 17-10. Automating Unit Tests for PL/SQL and Java Stored Procedures Using Ant
- Index
C H A P T E R 2
Essential SQL
SQL is an essential part of any database application. From queries to update statements to inserts and deletes, database transactions consume much of a database application developer's time. The PL/SQL language is unmatched by any other in providing seamless integration between SQL and procedural language for the Oracle Database. PL/SQL is based around database transactions, so the seamless language characteristics help to provide ease of use and increased developer productivity.
This chapter will focus on some of the more widely used PL/SQL techniques for working directly with the database. If you are looking for some great ways to insert, update, create, or delete records with your application, then this is the chapter that you'll want to read. The recipes will begin with showing how to retrieve data and work with it. After that, you will find some recipes for updating data, deleting rows, and more advanced topics such as removing duplicate rows from the database.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.