Oracle PL/SQL

Faculty profile:

Real time professionals with 10+ years of experience.

Duration:

25 Hours

Audience:

Database Administrators, Oracle Developers

Course Objectives:

  • Understand the environment and context in which PL/SQL operates.
  • Consider the advantages and benefits of PL/SQL within a database environment.
  • Declare program variables and complex data types.
  • Develop logic within PL/SQL program blocks.
  • Fetch data from the database into program variables.
  • Return program output to users.
  • Handle program exceptions.
  • Use explicit and implicit database cursors.
  • Maintain and implement triggers.

 

Course Topics:

Choosing a SQL & PL/SQL Interface

  • Using SQL Developer
  • Using SQL*PLUS

Language Features

  • Understanding PL/SQL
  • Structure of A PL/SQL Program Block
  • Using PL/SQL from SQL*PLUS

Declare Clause

  • About the Declare Clause
  • Declare Simple Types
  • Declare Complex Types
  • Type ... Table
  • Type ... Record

Begin Clause

  • About the Begin Clause
  • Performing Data Manipulation
  • Logic Control & Branching
  • Loop
  • If-Then-Else
  • Case

Exception Clause

  • About the Exception Clause
  • Isolating the Specific Exception
  • Pragma Exception_Init
  • SQL%Rowcount & Select...Into

Explicit Cursors

  • About Explicit Cursors
  • Advanced Cursor Techniques

Introducing Database Program Units

  • About Database Program Units
  • Types of PL/SQL Program Units
  • Types of Stored Program Units
  • Advantages of Using Stored Program Units

Dynamic SQL

  • Advantages & Disadvantages
  • Native Dynamic SQL
  • Dynamic Update...Returning
  • Dynamic SQL Using DBMS_SQL()

Creating Stored Procedures & Functions

  • About Stored Procedures & Functions
  • Creating Procedures & Functions
  • Executing Procedures & Functions

Maintaining Stored Procedures & Functions

  • Recompiling & Dropping Programs
  • Data Dictionary Storage
  • Managing Dependencies

Creating & Maintaining Packages

  • About Packages
  • Creating Packages
  • Advanced Programming Techniques
  • Maintaining Packages

Advanced Cursor Techniques

  • Using Cursor Variables
  • Using Cursor Expressions

Using System-Supplied Packages

  • DBMS_OUTPUT()
  • UTL_FILE()
  • DBMS_PARALLEL_EXECUTE().

Creating Database Triggers

  • About Database Triggers
  • Statement-Level Triggers
  • Row-Level Triggers
  • Examples of Triggers
  • Instead of Triggers
  • Employing Triggers within an Application

Maintaining Database Triggers

  • Call Syntax
  • Trigger Maintenance Tasks
  • Show Errors Trigger
  • Drop Trigger
  • Alter Trigger
  • Handling Multiple Triggers for A Table
  • Handling Mutating Table Issues

Implementing System Event Triggers

  • What Are System Event Triggers?
  • Defining the Scope
  • Available System Events
  • System Event Attributes