Oracle PL/SQL Fundamentals

Course Description

This course is designed to create PL/SQL blocks both anonymous and named. This course will cover PL/SQL objects and data types. It will also cover packages and how to debug and improve performance within PL/SQL. It will address deploying PL/SQL objects and using Oracle pre-define packages, procedures, and functions.

Lesson 1: Introduction

• PL/SQL development environments available in this course
• Introduction to SQL Developer
• Course Overview

Lesson 2: Introduction to PL/SQL

• Overview of PL/SQL
• PL/SQL Subprograms
• Types of PL/SQL blocks
• Create an Anonymous Block

Lesson 3: PL/SQL Terms and Types

• Different Types of Identifiers
• Using the Declarative Section
• Declare Variable
• Use Scalar Data Types
• The %TYPE Attribute
• Introduction to Bind Variables?
• Using Sequences in PL/SQL Expressions

Lesson 4: Basic PL/SQL Statements

• Basic PL/SQL Block Syntax Guidelines
• How to Comment PL/SQL Blocks
• Deployment of SQL Functions in PL/SQL
• Conversion of Data Types?
• Using Nested Blocks

Lesson 5: Conditional Structures

• Using IF Statements
• Using CASE Statements
• Using a simple Loop Statement
• Using a While Loop Statement
• Using a For Loop Statement

Lesson 6: PL/SQL Data Types

• Use PL/SQL Records
• The %ROWTYPE Attribute
• Insert and Update with PL/SQL Records
• INDEX BY Tables
• Examine INDEX BY Table Methods
• Use INDEX BY Table of Records

Lesson 7: Explicit Cursors

• Define an Explicit Cursor
• Declare the Cursor
• Open the Cursor
• Fetch data from the Cursor
• Close the Cursor
• Cursor FOR loop
• The %NOTFOUND and %ROWCOUNT Attributes
• Describe the FOR UPDATE Clause and WHERE CURRENT Clause

Lesson 8: Implicit Cursors

• Define an Implicit Cursor
• Use an Implicit Cursor
• Raise an Implicit Cursor
• Implicit Cursor Attributes

Lesson 9: PL/SQL Exception Handling

• Overview of Exceptions
• Define an Exception
• Raise an Exception
• Propagate Exceptions
• Use the RAISE_APPLICATION_ERROR Procedure

Lesson 10: Identify Stored Procedures

• Define a Stored Procedure
• Create a Stored Procedure
• Identify the differences between Anonymous Blocks and Subprograms
• Call, and Remove Stored Procedures
• Implement Procedures Parameters and Parameters Modes
• View the data dictionary about Stored Procedures

Lesson 11: Identify Stored Functions

• Create, Modify, Call, and Remove a Stored Function
• Identify the advantages of using Stored Functions
• Identify the steps to create a stored function
• Identify the differences between a stored procedure and function
• Restrictions with Functions
• Debug functions and procedures
• View data dictionary information regarding Functions

Lesson 12: PL/SQL Packages

• What is a Packages
• List the components of a Package?
• Create a Package
• Call a package and its components
• View package information using the Data Dictionary
• Identify Common Oracle supplied packages

Lesson 13: Deploying Packages

• Define Package overload
• Use the STANDARD Package
• Identify states of a Packages
• Persistent State of a Package Cursor
• Control errors within a package

Lesson 14: Using Dynamic SQL

• Using Dynamic SQL
• Using the execute immediate function
• Identify Dynamic SQL uses
• Identify Dynamic SQL limitations

Lesson 15: Improving performance with PL/SQL Code

• Using Autonomous Transactions
• Use the NOCOPY Hint
• Use the PARALLEL_ENABLE Hint
• Implement result cache
• Using bulk operations within PL/SQL

Lesson 16: Defining PL/SQL Triggers

• Overview of Triggers
• Identify Trigger Types
• Create database/server triggers
• Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
• Identify the Trigger Event Types and firing levels
• Differences between Statement Level Triggers and Row Level Triggers
• Create Instead of and Disabled Triggers
• Manage Triggers

Lesson 17: Creating Compound Triggers

• What is a Compound Triggers
• Identify Uses of a Compound Trigger
• What is a Mutating trigger
• Triggers and privileges
• Create DDL Triggers
• Create Database Triggers

Lesson 18: Manage Dependencies

• Identify Object Dependencies
• Identify privilege prorogations
• Use the USER_DEPENDENCIES View
• Query user_objects
• Identify Object invalidation