O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Oracle PL/SQL in 10 Minutes, Sams Teach Yourself

Oracle PL/SQL in 10 Minutes, Sams Teach Yourself

by Ben Forta
Publisher: Sams
Release Date: September 2015
ISBN: 9780768681987
View table of contents
Start reading

Book Description

Covers Oracle DBMS versions 10, 11, 12, and XE

Sams Teach Yourself Oracle PL/SQL in 10 Minutes offers straightforward, practical answers when you need fast results. By working through the book’s 26 lessons of 10 minutes or less, you’ll learn what you need to know to take advantage of Oracle’s PL/SQL language.

This handy pocket guide starts with simple data retrieval and moves on to more complex topics, including the use of joins, subqueries, regular expression and full text-based searches, stored procedures, cursors, triggers, table constraints, and much more.

Tips point out shortcuts and solutions.
Cautions help you avoid common pitfalls.
Notes explain additional concepts and provide additional information.

10 minutes is all you need to learn how to...

  • Use PL/SQL in the Oracle environments and tools

  • Construct complex PL/SQL statements using multiple clauses and operators

  • Retrieve, sort, and format database contents

  • Pinpoint the data you need using a variety of filtering techniques

  • Use string, date-time, and math functions to manipulate your data

  • Join two or more related tables

  • Insert, update, and delete data

  • Create and alter database tables

  • Work with views, stored procedures, cursors, triggers, and more

    • Table of Contents

    1. About This eBook
    2. Title Page
    3. Copyright Page
    4. Contents
    5. About the Author
    6. Acknowledgments
    7. We Want to Hear from You!
    8. Reader Services
    9. Introduction
      1. What Is This Book?
      2. Who Is This Book For?
      3. Companion Website
      4. Conventions Used in This Book
    10. Lesson 1. Understanding SQL
      1. Database Basics
        1. What Is a Database?
        2. Tables
        3. Columns and Datatypes
        4. Rows
        5. Primary Keys
      2. What Is SQL?
      3. Try It Yourself
      4. Summary
    11. Lesson 2. Getting Started with Oracle and PL/SQL
      1. What Is Oracle?
        1. Client-Server Software
        2. PL/SQL
        3. Client Tools
      2. Getting Set Up
        1. What Software Do You Need?
        2. Obtaining the Software
        3. Installing the Software
      3. Summary
    12. Lesson 3. Working with Oracle
      1. Creating a Working Environment
        1. Creating a Dedicated Oracle Instance
        2. Creating a Custom Workspace
      2. Making the Connection
      3. A Quick Introduction to Oracle SQL Developer
      4. Creating and Populating the Example Tables
        1. Obtaining the Same Table Scripts
        2. Create the Tables
        3. Populate the Tables
      5. One More Look at Oracle SQL Developer
      6. Summary
    13. Lesson 4. Retrieving Data
      1. The SELECT Statement
      2. Retrieving Individual Columns
      3. Retrieving Multiple Columns
      4. Retrieving All Columns
      5. Retrieving Distinct Rows
      6. Using Fully Qualified Table Names
      7. Using Comments
      8. Summary
    14. Lesson 5. Sorting Retrieved Data
      1. Sorting Data
      2. Sorting by Multiple Columns
      3. Specifying Sort Direction
      4. Summary
    15. Lesson 6. Filtering Data
      1. Using the WHERE Clause
      2. The WHERE Clause Operators
        1. Checking Against a Single Value
        2. Checking for Nonmatches
        3. Checking for a Range of Values
        4. Checking for No Value
      3. Summary
    16. Lesson 7. Advanced Data Filtering
      1. Combining WHERE Clauses
        1. Using the AND Operator
        2. Using the OR Operator
        3. Understanding Order of Evaluation
      2. Using the IN Operator
      3. Using the NOT Operator
      4. Summary
    17. Lesson 8. Using Wildcard Filtering
      1. Using the LIKE Operator
        1. Searching with the Percent Sign (%) Wildcard
        2. Searching with the Underscore (_) Wildcard
      2. Tips for Using Wildcards
      3. Summary
    18. Lesson 9. Searching Using Regular Expressions
      1. Understanding Regular Expressions
      2. Using Oracle PL/SQL Regular Expressions
        1. Basic Character Matching
        2. Performing OR Matches
        3. Matching One of Several Characters
        4. Matching Ranges
        5. Matching Special Characters
        6. Matching Character Classes
        7. Matching Multiple Instances
        8. Anchors
      3. Summary
    19. Lesson 10. Creating Calculated Fields
      1. Understanding Calculated Fields
      2. Concatenating Fields
        1. Using Aliases
      3. Performing Mathematical Calculations
      4. Summary
    20. Lesson 11. Using Data Manipulation Functions
      1. Understanding Functions
      2. Using Functions
        1. Text Manipulation Functions
        2. Date and Time Manipulation Functions
        3. Numeric Manipulation Functions
      3. Summary
    21. Lesson 12. Summarizing Data
      1. Using Aggregate Functions
        1. The AVG() Function
        2. The COUNT() Function
        3. The MAX() Function
        4. The MIN() Function
        5. The SUM() Function
      2. Aggregates on Distinct Values
      3. Combining Aggregate Functions
      4. Summary
    22. Lesson 13. Grouping Data
      1. Understanding Data Grouping
      2. Creating Groups
      3. Filtering Groups
      4. Grouping and Sorting
      5. SELECT Clause Ordering
      6. Summary
    23. Lesson 14. Working with Subqueries
      1. Understanding Subqueries
      2. Filtering by Subquery
      3. Using Subqueries as Calculated Fields
      4. Summary
    24. Lesson 15. Joining Tables
      1. Understanding Joins
        1. Understanding Relational Tables
        2. Why Use Joins?
      2. Creating a Join
        1. The Importance of the WHERE Clause
        2. Using Inner Joins
        3. Joining Multiple Tables
      3. Summary
    25. Lesson 16. Creating Advanced Joins
      1. Using Table Aliases
      2. Using Different Join Types
        1. Self Joins
        2. Natural Joins
        3. Outer Joins
      3. Using Joins with Aggregate Functions
      4. Using Joins and Join Conditions
      5. Summary
    26. Lesson 17. Combining Queries
      1. Understanding Combined Queries
      2. Creating Combined Queries
        1. Using UNION
        2. UNION Rules
        3. Including or Eliminating Duplicate Rows
        4. Sorting Combined Query Results
      3. Summary
    27. Lesson 18. Inserting Data
      1. Understanding Data Insertion
      2. Inserting Complete Rows
      3. Inserting Retrieved Data
      4. Summary
    28. Lesson 19. Updating and Deleting Data
      1. Updating Data
      2. Deleting Data
      3. Guidelines for Updating and Deleting Data
      4. Summary
    29. Lesson 20. Creating and Manipulating Tables
      1. Creating Tables
        1. The Basics of Table Creation
        2. Working with NULL Values
        3. Specifying Default Values
      2. Updating Tables
        1. Primary Keys Revisited
        2. Defining Foreign Keys
      3. Deleting Tables
      4. Renaming Tables
      5. Summary
    30. Lesson 21. Using Views
      1. Understanding Views
        1. Why Use Views
        2. View Rules and Restrictions
      2. Using Views
        1. Using Views to Simplify Complex Joins
        2. Using Views to Reformat Retrieved Data
        3. Using Views to Filter Unwanted Data
        4. Using Views with Calculated Fields
        5. Updating Views
      3. Summary
    31. Lesson 22. Working with Stored Procedures
      1. Understanding Stored Procedures
      2. Why Use Stored Procedures
      3. Using Stored Procedures
        1. Basic Stored Procedure Syntax
        2. Using Programming Constructs in Stored Procedures
        3. Building Intelligent Stored Procedures
        4. Dropping Stored Procedures
      4. Summary
    32. Lesson 23. Using Cursors
      1. Understanding Cursors
      2. Working with Cursors
        1. Creating Cursors
        2. Opening and Closing Cursors
        3. Fetching Cursor Data
        4. Using Cursor Data
      3. Summary
    33. Lesson 24. Using Triggers
      1. Understanding Triggers
      2. Creating Triggers
      3. Dropping Triggers
      4. Using Triggers
        1. INSERT Triggers
        2. DELETE Triggers
        3. UPDATE Triggers
        4. Multi-Event Triggers
        5. More on Triggers
      5. Summary
    34. Lesson 25. Managing Transaction Processing
      1. Understanding Transaction Processing
      2. Controlling Transactions
        1. Using ROLLBACK
        2. Using COMMIT
        3. Using Savepoints
      3. Summary
    35. Lesson 26. Managing Security
      1. Understanding Access Control
      2. Managing Users
        1. Creating User Accounts
        2. Deleting User Accounts
        3. Setting Access Rights
        4. Changing Passwords
      3. Summary
    36. Appendix A. The Example Tables
      1. Understanding the Sample Tables
        1. The vendors Table
        2. The products Table
        3. The customers Table
        4. The orders Table
        5. The orderitems Table
        6. The productnotes Table
    37. Appendix B. Oracle PL/SQL Datatypes
      1. String Datatypes
      2. Numeric Datatypes
        1. Date and Time Datatypes
        2. Binary Datatypes
    38. Appendix C. Oracle PL/SQL Reserved Words and Keywords
    39. Index
    40. Code Snippets