Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

1 Overview of Oracle Database and Development

• Roadmap
• Overview of Oracle Database Schemas
  • Introducing Schema Objects
  • Introducing the HR Schema
• Overview of Application Development with Oracle Database
  • Introducing SQL and PL/SQL Development Languages
  • Introducing SQL Developer
  • Introducing SQL*Plus
  • Connecting to the Database
    • Unlocking a User Account
    • Connecting to Oracle Database from SQL*Plus
    • Connecting to Oracle Database from SQL Developer
• Overview of Other Development Environments

2 Querying and Manipulating Data

• Exploring Database Objects
  • Looking at Schema Object Types
  • Exploring Tables and Viewing Data
• Retrieving Data with Queries
  • Selecting Data from a Table
  • Using Column Aliases
  • Restricting Data to Match Specific Conditions
  • Searching for Patterns in Data
  • Sorting Data
  • Using Built-In and Aggregate Functions
    • Using Arithmetic Operators
    • Using Numeric Functions
    • Using Character Functions
    • Using Datetime Functions
    • Using Data Type Conversion Functions
    • Using Aggregate Functions
    • Using NULL Value Functions
    • Using Conditional Functions
• Adding, Changing, and Deleting Data
  • Inserting Information
  • Updating Information
  • Deleting Information
• Controlling Transactions
  • Committing Transaction Changes
  • Rolling Back Transaction Changes
  • Setting Savepoints

3 Creating and Using Database Objects

• Using Data Types
• Creating and Using Tables
  • Creating a Table
  • Ensuring Data Integrity
    • Understanding Types of Data Integrity Constraints
    • Adding Integrity Constraints
  • Adding Data to a Table, Modifying, and Deleting
  • Indexing Tables
  • Dropping Tables
• Using Views
  • Creating a View
  • Updating a View
  • Dropping a View
• Using Sequences
  • Creating a Sequence
  • Dropping a Sequence
• Using Synonyms

4 Developing and Using Stored Procedures

• Overview of Stored Procedures
• Creating and Using Standalone Procedures and Functions
  • Creating Procedures and Functions
  • Modifying Procedures and Functions
  • Testing Procedures and Functions
  • Dropping Procedures and Functions
• Creating and Using Packages
  • Guidelines for Packages
  • Creating a Package
  • Modifying a Package
  • Dropping a Package
• Using Variables and Constants
  • PL/SQL Data Types
  • Using Variables and Constants
    • Using Comments
    • Using Identifiers
    • Declaring Variables and Constants
    • Declaring Variables with Structure Identical to Database Columns
  • Assigning Values to Variables
    • Assigning Values with the Assignment Operator
    • Assigning Values from the Database
• Controlling Program Flow
  • Using Conditional Selection Control
    • Using IF...THEN...ELSE Selection Control
    • Using CASE...WHEN Selection Control
  • Using Iterative Control
    • Using the FOR...LOOP
    • Using the WHILE...LOOP
    • Using the LOOP...EXIT WHEN
• Using Composite Data Structures; Records
• Retrieving Data from a Set Using Cursors and Cursor Variables
  • Using Explicit Cursors
  • Using Cursor Variables: REF Cursors
• Using Collections; Index-By Tables
  • Creating Cursors for Index-by Tables
  • Defining Index-by Tables
  • Populating Index-by PLS_INTEGER Tables; BULK COLLECT
  • Populating Index-by VARCHAR2 Tables
  • Iterating Through an Index-by Table
• Handling Errors and Exceptions
  • Existing PL/SQL and SQL Exceptions
  • Custom Exceptions

5 Using Triggers

• Designing Triggers
  • Types of Triggers
  • Timing Triggers
  • Guidelines and Restrictions for Trigger Design
• Creating and Using Triggers
  • Creating a Statement Trigger
  • Creating a Row Trigger
  • Creating an INSTEAD OF Trigger
  • Creating LOGON and LOGOFF Triggers
  • Modifying Triggers
  • Disabling and Enabling Triggers
  • Compiling Triggers
  • Dropping Triggers

6 Working in a Global Environment

• Overview of Globalization
  • Globalization Support Features
  • Viewing the Current NLS Parameter Values
• Using NLS Parameter Values in the SQL Developer Environment
  • Changing NLS Parameter Values for All Sessions
• Establishing a Globalization Support Environment
  • Choosing a Locale with the NLS_LANG Parameter
  • Setting NLS Parameters
  • Setting Language and Territory Parameters
    • Using the NLS_LANGUAGE Parameter
    • Using the NLS_TERRITORY Parameter
  • Setting Date and Time Parameters
    • Using Date Formats
    • Using Time Formats
  • Setting Calendar Definitions
    • Overview of Calendar Formats
    • Using the NLS_CALENDAR Parameter
  • Using Numeric Formats
    • Using the NLS_NUMERIC_CHARACTERS Parameter
  • Using Monetary Parameters
    • Overview of Currency Formats
    • Using the NLS_CURRENCY Parameter
    • Using the NLS_ISO_CURRENCY Parameter
    • Using the NLS_DUAL_CURRENCY Parameter
  • Using Linguistic Sort and Search
    • Using the NLS_SORT Parameter
    • Using the NLS_COMP Parameter
    • Using Case-Insensitive and Accent-Insensitive Search
  • Using Length Semantics
    • Using the NLS_LENGTH_SEMANTICS Parameter
• Developing Globalized Applications
  • Overview of Unicode
  • Using SQL Character Data Types
    • Using the NCHAR Data Type
    • Using the NVARCHAR2 Data Type
  • Using Unicode String Literals
  • NCHAR Literal Replacement
• Using Locale-Dependent Functions with NLS Parameters
  • Specifying NLS Parameters in SQL Functions
  • Unacceptable NLS Parameters in SQL Functions

7 Deploying a Database Application

• Overview of Deployment
• Deployment Environments
• Planning for Deployment
• Exporting the Database Objects
  • Using SQL Developer to Export Database Objects
  • Special Considerations for Exporting Sequences and Triggers
  • Generating a Script for Creating the Sequence and Tables
  • Generating a Script for Creating the PL/SQL Objects
  • Generating a Script for Creating a Synonym and a View
• Exporting the Data
• Performing the Installation
• Validating the Installation
• Archiving the Installation Scripts