You are previewing Oracle PL/SQL For Dummies.
O'Reilly logo
Oracle PL/SQL For Dummies

Book Description

Find tips for creating efficient PL/SQL code

If you know a bit about SQL, this book will make PL/SQL programming painless!

The Oracle has spoken—you need to get up to speed on PL/SQL programming, right? We predict it'll be a breeze with this book! You'll find out about code structures, best practices, and code naming standards, how to use conditions and loops, where to place PL/SQL code in system projects, ways to manipulate data, and more.

Discover how to

  • Write efficient, easy-to-maintain code

  • Test and debug PL/SQL routines

  • Integrate SQL and PL/SQL

  • Apply PL/SQL best practices

  • Use new features introduced in Oracle 9i and 10g

  • Table of Contents

    1. Title
    2. Contents
    3. Introduction
      1. About This Book
      2. Foolish Assumptions
      3. How This Book Is Organized
      4. Icons Used in This Book
      5. Where to Go from Here
    4. Part I : Basic PL/SQL Concepts
      1. Chapter 1: PL/SQL and Your Database
        1. Knowing Just Enough about Relational Databases
        2. The Scoop on SQL and PL/SQL
        3. What Is PL/SQL Good For?
      2. Chapter 2: The PL/SQL Environment
        1. Setting Up an Oracle Environment
        2. Hardware and Software Requirements
        3. Accessing the Oracle Technology Network
        4. Installing the Database
        5. Working with Code
        6. Establishing a Database Connection
        7. The Basics of PL/SQL Programs
        8. Examining the Sample Data
    5. Part II : Getting Started with PL/SQL
      1. Chapter 3: Laying the Groundwork: PL/SQL Fundamentals
        1. PL/SQL As a Programming Language
        2. Anonymous PL/SQL Blocks
        3. Introducing the Lexical Set of Elements
        4. Working with Constants and Variables
        5. Building Expressions with Operators
        6. Running Anonymous Blocks of Code
        7. Creating Reusable PL/SQL Code
        8. Storing PL/SQL in the Database
        9. Checking Out PL/SQL Extras
      2. Chapter 4: Controlling Program Flow
        1. Creating Condition Statements
        2. Looping the Loop
      3. Chapter 5: Handling Exceptions
        1. Understanding Exception Basics
        2. Adding Exception Handlers to Your Code
        3. Understanding Different Exception Types
        4. Predefined Exceptions in PL/SQL Code
        5. Adding User-Defined Exceptions
        6. Propagation of Exceptions
        7. Writing Exceptional Exceptions
      4. Chapter 6: PL/SQL and SQL Working Together
        1. Cursors: What They Are and How to Use Them
        2. Knowing Where to Declare Cursors
        3. Being Explicitly Smart with Implicit Cursors
        4. Accessing Status Info by Using Cursor Variables
        5. Updating Records Fetched from Cursors
        6. Taking a Shortcut with CURSOR FOR Loops
        7. Referencing Functions in SQL
    6. Part III : Standards and Structures
      1. Chapter 7: Putting Your Code in the Right Place
        1. Putting Code in the Database
        2. Putting Code in the Application Server (Middle-Tier Approach)
        3. Where Should You Place the Business Logic?
      2. Chapter 8: Creating Naming Standards
        1. What’s in a Naming Standard?
        2. Setting Naming Standards for Common Code Elements
        3. Making Sure Your Organization Follows Standards
      3. Chapter 9: Creating Coding Standards
        1. Why Standards Are Important
        2. Universal Truths
        3. Developing SQL Code Consistently
    7. Part IV : PL/SQL Data Manipulations
      1. Chapter 10: Basic Datatypes
        1. Introducing the Main Datatype Groups
        2. Working with Numeric Datatypes
        3. Keeping Track of Date and Time
        4. Storing Logical Values with BOOLEAN
        5. Processing Characters and Strings
      2. Chapter 11: Advanced Datatypes
        1. Handling Large Objects in the Database
        2. Working with Large Objects
        3. Keeping Code Consistent with User-Defined Subtypes
        4. Defining Your Own Datatypes
        5. Grouping Sets of Data into Collections
        6. Speeding Up Data Collection with Bulk Operations
    8. Part V : Taking PL/SQL to the Next Level
      1. Chapter 12: Transaction Control
        1. Using Transactions to Maintain Data Consistency
        2. Committing or Rolling Back Changes to the Database
        3. Autonomous Transactions
      2. Chapter 13: Dynamic SQL and PL/SQL
        1. Taking Advantage of Dynamic SQL
        2. A Simple EXECUTE IMMEDIATE
        3. Building SQL on the Fly
        4. Building DDL on the Fly
        5. Using Quoted Strings with Dynamic SQL
        6. Working with Advanced Datatypes
      3. Chapter 14: PL/SQL Best Practices
        1. Why Are Best Practices Important?
        2. Laying the Groundwork for Good Coding
        3. Writing Code with Best Practices in Mind
        4. Testing Your Code
        5. Coding the Agile Way
        6. Keeping Up-to-Date with Oracle
    9. Part VI : The Part of Tens
      1. Chapter 15: Ten PL/SQL Tips
        1. Use SELECT INTO Correctly
        2. Don’t Assume Column Order in DML Statements
        3. Use the NOCOPY Command
        4. Be Careful of Function Calls That Impact Performance
        5. Beware of Implicit Commits
        6. Use Only One RETURN Statement per Function
        7. Save Debugging Time with WHEN OTHERS
        8. Know When to Pass Parameters or Use Global Variables
        9. Use Implicit Cursors When Appropriate
        10. Remember That Dates Are Numbers
      2. Chapter 16: Ten Common Mistakes to Avoid in PL/SQL
        1. Catching an Exception with WHEN OTHERS THEN NULL
        2. Forgetting to Handle NULL Values
        3. Creating Unintended Boolean Expressions
        4. Forgetting to Close an Explicit Cursor
        5. Starting Endless Loops
        6. Reinventing the Wheel
        7. Converting Datatypes Implicitly
        8. Cutting and Pasting Code
        9. Ignoring Code Readability
        10. Assuming Code Doesn’t Need Comments