Back to Search Results

Training Course Summary:

After an overview of the Oracle environment, participants will learn to use the Oracle extensions to the standard ANSI SQL. PL/SQL is Oracle’s block-structured procedural extension to SQL. Participants will learn how to create PL/SQL blocks of code which can use explicit cursors and handle runtime errors.

Pre-Requisites:

A thorough working knowledge of standard ANSI SQL, or SQL in another relational database environment. Familiarity with relational database concepts.

Suggested:
Some 3GL programming experience or awareness of programming constructs and techniques.

Training Course Overview/Content:

Overview of the Oracle Environment
The Oracle RDBMS and Object Relational Database Management System (ORDBMS)
Characteristics of Object systems
Describe the new features of Oracle8i
Describe how SQL is used in the Oracle product set
Describe the use of PL?SQL
Explain the benefits of PL/SQL

The Oracle SELECT Statement
Review of the basic SELECT statement
Oracle specific operators
Outer joins
SQL*Plus commands

Oracle Single-row Functions
Character, number and date functions
Conversion functions
Group Functions with ROLLUP and CUBE Operations
Review the Group Functions
Use ROLLUP and CUBE Operations
Nesting of Group Functions

Oracle SQL Set Operators
Sub-queries
Overview of sub-queries
Multi-column sub-queries
Correlated sub-queries
EXISTS operator
DML with sub-queries

Hierarchical Retrieval
Creating and Managing Oracle Tables
Oracle data structures
CREATE command
Data Dictionary views
Oracle8 Datatypes
ALTER, DROP, RENAME and TRUNCATE tables
Data integrity constraints

Other Database Objects
CREATE, maintain and use views
CREATE, maintain and use sequences
CREATE and maintain indexes
CREATE synonyms

Oracle Transaction Processing
Transaction processing and read consistency
Locking


Controlling User Access
Overview of SYSTEM and Object priveleges
Users, roles and privileges
GRANT and REVOKE object privileges
CREATE roles and GRANT privileges to roles

Producing Readable Output with SQL*Plus
Substitution variables
Customise the SQL*Plus environment
Format output
Create and execute script files
Save customisations

PL/SQL Blocks and Declaring Variables
Basic PL/SQL block
Declare variables, constants and bind variables
Execute a PL/SQL block

Writing PL/SQL Executable Statements
The executable section
Nested blocks

Interacting with Oracle
SQL in PL/SQL blocks
DML statements and transaction control statements in PL/SQL
Declare PL/SQL variables dynamically
Dertermine the outcome of DML statements

Writing Control Structures
Conditional control
Loops
Nested loops and labels
Use logic tables

Working with Composite Datatypes
User-defined PL/SQL records
PL/SQL tables

Writing Explicit Cursors
Implicit and explicit cursors
Cursor FOR loops

Advanced Explicit Cursor Concepts
Cursors with parameters
FOR UPDATE clause in cursors
Using PL/SQL tables and tables of records

Handling Exceptions
PL?SQL Exceptions
Exception propagation in nested blocks
Customise PL/SQL exception messages
Use exception handlers

Optional
Additional practices



Course Objectives

Understand the Oracle environment Perform complex Oracle queries Use SQL*Plus commands Set up and maintain Oracle database structures Understand the control of user access to the database Create PL/SQL anonymous blocks Use PL/SQL records and tables Use explicit cursors Handle runtime errors