Introduction to Oracle 11G Programming

This course has been superseded

We suggest the following instead:
Oracle Database - SQL I Introduction

Course Description

In this class, students will learn how to create, retrieve, and manipulate objects in Oracle 11g Structured Query Language (SQL). Students will also be introduced to Oracle 10g database features and tools. The Oracle 11g release has greatly enhanced the features and functionality of PL/SQL. Students will learn the fundamentals of the PL/SQL programming language. Students will write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle. Students will learn programming, management, and security issues of working with PL/SQL program units. Programming topics will include the built-in packages that come with Oracle, the creation of triggers, and stored procedure features.
5 Days
Contact us for pricing
 

Prerequisites

Familiarity with relational database concepts as well as a solid understanding of 3GL programming are required

Relational Database and SQL Overview

Review of Relational Database Terminology
Relational Database Management Systems
Introduction to SQL
Oracle Versioning and History
Logical and Physical Storage Structures
Connecting to a SQL Database
Datatypes
Sample Database

Using Oracle SQL*Plus

SQL*Plus
The SQL Buffer
Buffer Manipulation Commands
Running SQL*Plus Scripts
Tailoring Your SQL*Plus Environment
Viewing Table Characteristics
SQL*Plus Substitution Variables
Interactive SQL*Plus Scripts
SQL*Plus LOB Support
Graphical Clients

SQL Queries – The SELECT Statement

The SELECT Statement
The CASE...WHEN Expression
Choosing Rows with the WHERE Clause
NULL Values
Compound Expressions
IN and BETWEEN
Pattern Matching: LIKE and REGEXP_LIKE
Creating Some Order

Scalar Functions

SQL Functions
Using SQL Functions
String Functions
Regular Expression Functions
Numeric Functions
Date Functions
Date Formats
Conversion Functions
Literal Values
Intervals
Oracle Pseudocolumns

SQL Queries - Joins

Selecting from Multiple Tables
Joining Tables
Self Joins
Outer Joins

Aggregate Functions and Advanced Techniques

Subqueries
Correlated Subqueries
The EXISTS Operator
The Aggregate Functions
Nulls and DISTINCT
Grouping Rows
Combining SELECT Statements

Data Manipulation and Transactions

The INSERT Statement
The UPDATE Statement
The DELETE Statement
Transaction Management
Concurrency
Explicit Locking
Data Inconsistencies
Loading Tables From External Sources

Data Definition and Control Statements

Datatypes
Defining Tables
Constraints
Inline Constraints
Modifying Table Definitions
Deleting a Table Definition
Controlling Access to Your Tables

Other Database Objects

Views
Creating Views
Updatable Views
Sequences
Synonyms

Triggers

Beyond Declarative Integrity
Triggers
Types of Triggers
Trigger Sequencing
Row-Level Triggers
Trigger Predicates
Trigger Conditions
Using Sequences
Cascading Triggers and Mutating Tables
Generating an Error
Maintaining Triggers

PL/SQL Variables and Datatypes

Anonymous Blocks
Declaring Variables
Datatypes
Subtypes
Character Data
Dates and Timestamps
Date Intervals
Anchored Types
Assignment and Conversions
Selecting into a Variable
Returning into a Variable

PL/SQL Syntax and Logic

Conditional Statements – IF/THEN
Conditional Statements – CASE
Comments and Labels
Loops
WHILE and FOR Loops
SQL in PL/SQL
Local Procedures and Functions

Stored Procedures and Functions

Stored Subprograms
Creating a Stored Procedure
Procedure Calls and Parameters
Parameter Modes
Named Parameter Notation
Default Arguments
Creating a Stored Function
Stored Functions and SQL
Invoker’s Rights

Exception Handling

SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Scope and Name Resolution
Declaring and Raising Named Exceptions
User-Defined Exceptions

Records, Collections, and User-Defined Types

Record Variables
Using the %ROWTYPE Attribute
User-Defined Object Types
VARRAY and Nested TABLE Collections
Using Nested TABLEs
Using VARRAYs
Collections in Database Tables
Associative Array Collections
Collection Methods
Iterating Through Collections

Cursors

Multi-Row Queries
Declaring and Opening Cursors
Fetching Rows
Closing Cursors
The Cursor FOR Loop
FOR UPDATE Cursors
Cursor Parameters
The Implicit (SQL) Cursor

Bulk Operations

Bulk Binding
BULK COLLECT Clause
FORALL Statement
FORALL Variations
Bulk Returns
Bulk Fetching with Cursors

Using Packages

Packages
Oracle-Supplied Packages
The DBMS_OUTPUT Package
The DBMS_UTILITY Package
The UTL_FILE Package
Creating Pipes with DBMS_PIPE
Writing to and Reading from a Pipe
The DBMS_METADATA Package
XML Packages
Networking Packages
Other Supplied Packages

Creating Packages

Structure of a Package
The Package Interface and Implementation
Package Variables and Package State
Overloading Package Functions and Procedures
Forward Declarations
Strong REF CURSOR Variables
Weak REF CURSOR Variables

Working with LOBs

Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
LOB Storage and SECUREFILEs
External LOBs
Temporary LOBs
The DBMS_LOB Package

Maintaining PL/SQL Code

Privileges for Stored Programs
Data Dictionary
PL/SQL Stored Program Compilation
Conditional Compilation
Compile-Time Warnings
The PL/SQL Execution Environment
Dependencies and Validation
Maintaining Stored Programs

Appendix A - The Data Dictionary

Introducing the Data Dictionary
DBA, ALL, and USER Data Dictionary Views
Some Useful Data Dictionary Queries

Appendix B: Dynamic SQL

Generating SQL at Runtime
Native Dynamic SQL vs. DBMS_SQL Package
The EXECUTE IMMEDIATE Statement
Using Bind Variables
Multi-row Dynamic Queries
Bulk Operations with Dynamic SQL
Using DBMS_SQL
DBMS_SQL Subprograms

Appendix C: PL/SQL Versions, Datatypes, and Language

Appendix D: Oracle 11g Supplied Packages

OracleOracle 11gOracle Database 11gOracle PL/SQLPL/SQL