Course Code: plsql
Duration: 21 hours
Prerequisites:

Recommended

Some knowledge of SQL

Overview:

This instructor-led, live training (online or onsite) is aimed at providing students with a good grounding of the Pl/SQL programme language to build relevant scripts

Format of the Course

  • Interactive lecture and discussion.
  • Lots of exercises and practice.
  • Hands-on implementation in a live-lab environment.

Course Customization Options

  • To request a customized training for this course, please contact us to arrange.
Course Outline:

Introduction to PL/SQL

  • What is PL/SQL
  • PL/SQL Environment
  • Benefits of PL/SQL
  • Declaring and using variables
  • Identify the different types of variables
  • Describe variables and their uses
  • Declare PL/SQL variables
  • Describe basic syntax
  • Use Literals in PL/SQL
  • Using SQL functions in PL/SQL

Interacting with the Oracle Server

  • Identify the SQL Statements you can use n PL/SQL
  • Retrieve Data in PL/SQL with the SELECT Statement
  • Manipulate Data in the Server Using PL/SQL
  • Describe and use the SQL Cursor

Writing Control Structures

  • Control PL/SQL Flow of Execution
  • Conditional processing Using IF Statements
  • Conditional Processing CASE Statements
  • Use Looping Statements

Working with Composite Data Types

  • Learn the Composite Data Types of PL/SQL Records and Associative Arrays
  • Use PL/SQL Records to Hold Multiple Values of Different Types
  • Inserting and Updating with PL/SQL Records
  • Use Associative Arrays to hold multiple values of the same datatype

Using Explicit Cursors

  • Cursor FOR Loops Using Sub-queries
  • Cursors using Parameters
  • Use the FOR UPDATE Clause to Lock Rows
  • Use the WHERE CURRENT Clause to Reference the Current Row
  • Use Explicit Cursors to Process Rows
  • Explicit Cursor Attributes
  • Cursors and Records

Handling Exceptions

  • Handling Exceptions with PL/SQL
  • Predefined Exceptions
  • Trapping Non-predefined Oracle Server Errors
  • Functions that Return Information on Encountered Exceptions
  • Trapping User-Defined Exceptions
  • Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To
  • Applications

Creating Stored Procedures

  • Creating, Calling, and Removing Stored Procedures using the CREATE
  • Command
  • Using Procedures Parameters and Parameters Modes

Creating Stored Functions

  • Creating, Calling, and Removing a Stored Function using the CREATE
  • Command
  • Identifying the Advantages of Using Stored Functions in SQL Statements
  • Identify the steps to create a stored function
  • Using User-Defined Functions in SQL Statements
  • Restrictions When Calling Functions from SQL statements

Creating Packages

  • Listing the Advantages of Packages
  • Describing Packages
  • The Components of a Package
  • Developing a Package
  • The Visibility of a Package’s Components
  • Creating the Package Specification and Body
  • Using the SQL CREATE Statement
  • Invoking the Package Constructs
  • Overloading Subprograms in PL/SQL
  • Using Package Functions in SQL and Restrictions
  • Persistent State of Packages
  • Using PL/SQL Tables of Records in Packages

Creating Triggers

  • Working With Triggers
  • Identifying the Trigger Event Types and Body
  • Business Application Scenarios for Implementing Triggers
  • Creating DML Triggers Using the CREATE TRIGGER Statement
  • Identifying the Trigger Event Types, Body, and Firing (Timing)
  • Statement Level Triggers Versus Row Level Triggers
  • Creating Instead of and Disabled Triggers
  • Managing, Testing, and Removing Triggers

Tuning and Performance

  • Understand and influence the compiler
  • Tune PL/SQL code
  • Using PL/SQL function cache