Oracle Database: PL/SQL Fundamentals (D80182) – Outline

Detailed Course Outline

Introduction

• Course Objectives, Course Agenda and Class Account Information
• The Human Resources (HR) Schema
• Appendices Used in this Course
• PL/SQL Development Environments

Introduction to PL/SQL

• Understanding the benefits and structure of PL/SQL
• Understanding PL/SQL Blocks
• Generating output messages in PL/SQL

Declaring PL/SQL Variables

• Identifying valid and invalid identifiers
• Declaring and initializing variables
• Various data types
• Identifying the benefits of using the %TYPE attribute
• Using bind variables

Writing Anonymous PL/SQL blocks

• Lexical Units in a PL/SQL Block
• Using SQL Functions in PL/SQL
• Data Type Conversion
• Using Nested Blocks as Statements
• Referencing an Identifier Value in a Nested Block
• Qualifying an Identifier with a Label
• Operators in PL/SQL
• Using Sequences in PL/SQL Expressions

Using SQL Statements within a PL/SQL Block

• Using SQL Statements in PL/SQL
• Retrieving Data in PL/SQL with the SELECT statement
• Using Naming Conventions in DML Statements and data retrieval
• Manipulating Data on the Server Using PL/SQL
• SQL Cursors
• Using SQL Cursor Attributes to Obtain Feedback on DML
• Saving and Discarding Transactions

Writing Control Structures

• Controlling PL/SQL Flow of Execution
• Using IF and CASE Statements for conditional processing
• Handling Nulls
• Building Boolean Conditions with Logical Operators
• Using Iterative Control with Loop Statements

Working with Composite Data Types

• Composite Data Types of PL/SQL Records and Tables
• Using PL/SQL Records
• Inserting and Updating PL/SQL Records
• Using INDEX BY Tables
• Using Associative arrays
• Declaring and Using VArrays

Using Explicit Cursors

• Cursors
• Explicit Cursor Operations
• Controlling Explicit Cursors
• Using Explicit Cursors to Process Rows
• Cursors and Records
• Cursor FOR Loops Using Subqueries
• Explicit Cursor Attributes
• %NOTFOUND and %ROWCOUNT Attributes

Handling Exceptions

• Concept of Exception
• Handling Exceptions with PL/SQL
• Predefined Exceptions
• Trapping Predefined and Non-predefined Oracle Server Errors
• Functions that Return Information on Exceptions
• Usage of PRAGMA keyword
• Trapping User-Defined Exceptions
• Propagating Exceptions

Creating Stored Procedures and Functions

• Overview of Stored Procedures and Functions
• Differentiating between anonymous blocks and subprograms
• CREATE OR REPLACE PROCEDURE | FUNCTION
• Understanding the Header Area of a Stored Procedure and Function
• Creating Simple Procedures and Functions
• Creating a Simple Procedure with an IN Parameter
• Executing a Procedure and a Function