What you will learn:
This course introduces students to PL/SQL and explains the benefits of this powerful programming language. Students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.
Students also learn to create anonymous PL/SQL blocks and are introduced to stored procedures and functions. Students learn about declaring identifiers and trapping exceptions. Demonstrations and hands-on practice reinforce the fundamental concepts.
Students use Oracle SQL Developer to develop these program units. SQL*Plus and JDeveloper are introduced as optional tools. This is appropriate for a 10g audience too. There are few minor changes between 10g and 11g features.
Learn To:
Declaring PL/SQL Variables
Working with Composite Data Types
Conditionally control code flow (loops, control structures)
Explicit cursors
Handle runtime errors
Describe stored procedures and functions
Course Topics:
Introduction
Course Objectives
Course Agenda
Describing the Human Resources (HR) Schema
PL/SQL development environments Available in this course
Introduction to SQL Developer
Introduction to PL/SQL
PL/SQL Overview
Benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Creating and Executing a Simple Anonymous Block
Generating Output from a PL/SQL Block
Declaring PL/SQL Identifiers
Different Types of Identifiers in a PL/SQL subprogram
Using the Declarative Section to Define Identifiers
Storing Data in Variables
Scalar Data Types
%TYPE Attribute
Bind Variables
Using Sequences in PL/SQL Expressions
Writing Executable Statements
Describing Basic PL/SQL Block Syntax Guidelines
Commenting Code
SQL Functions in PL/SQL
Data Type Conversion
Nested Blocks
Operators in PL/SQL
Interacting with the Oracle Server
Including SELECT Statements in PL/SQL to Retrieve data
Manipulating Data in the Server Using PL/SQL
The SQL Cursor concept
Using SQL Cursor Attributes to Obtain Feedback on DML
Saving and Discarding Transactions
Writing Control Structures
Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Simple Loop Statement
While Loop Statement
For Loop Statement
The Continue Statement
Working with Composite Data Types
Using PL/SQL Records
Using the %ROWTYPE Attribute
Inserting and Updating with PL/SQL Records
INDEX BY Tables
INDEX BY Table Methods
INDEX BY Table of Records
Using Explicit Cursors
Understanding Explicit Cursors
Declaring the Cursor
Opening the Cursor
Fetching data from the Cursor
Closing the Cursor
Cursor FOR loop
Explicit Cursor Attributes
FOR UPDATE Clause and WHERE CURRENT Clause
Handling Exceptions
Understanding Exceptions
Handling Exceptions with PL/SQL
Trapping Predefined Oracle Server Errors
Trapping Non-Predefined Oracle Server Errors
Trapping User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure
Understanding Stored Procedures and Functions
Differentiate between anonymous blocks and subprograms
Create a Simple Procedure
Create a Simple Procedure with IN parameter
Create a Simple Function
Execute a Simple Procedure
Execute a Simple Function