PL SQL Training - Course Details

 
Basics of PL/SQL
  • PL/SQL architecture
  • PL/SQL and SQL*Plus
  • PL/SQL Basics, Variables, Constants, data types & error handling
  • PL/SQL wrapper utility
 
PL/SQL structures
  • Simple blocks
  • Control structures
  • PL/SQL records
  • Recognizing the Basic PL/SQL Block and Its Sections
  • Describing the Significance of Variables in PL/SQL
  • Distinguishing Between PL/SQL and Non-PL/SQL Variables
  • Declaring Variables and Constants
  • Executing a PL/SQL Block
 
Error checking & exception handling
  • Defining exceptions
  • Using the when others clause
  • Ensuring complete error checking
  • Passing error messages to calling routine
 
Boolean logic in PL/SQL
  • Identifying the Uses and Types of Control Structures
  • Constructing an IF Statement
  • Constructing and Identifying Different Loop Statements
  • Controlling Block Flow Using Nested Loops and Labels
  • Using Logic Tables
  • If-then-else structure
  • Testing for numbers characters and Booleans
 
Cursors in PL/SQL
  • Cursor basics
  • Using a cursor for a multi-row SQL query
 
 
Iteration in PL/SQL
  • For loop
  • While loop
 
 
PL/SQL tables
  • Defining PL/SQL tables
  • Reasons to use PL/SQL tables
  • Populating a PL/SQL table
  • Retrieving from a PL/SQL table
 
 
Dynamic SQL in PL/SQL
  • Introduction to the dbms_sql package
  • Creating a dynamic SQL statement
 
 
Nested blocks in PL/SQL
  • Introduction to the dbms_sql package
  • Creating a dynamic SQL statement
 
 
Triggers in PL/SQL
  • Triggers and database events
  • Defining a trigger
  • Timing a trigger
  • Enabling and disabling a trigger
 
 
Stored procedures, functions and packages
  • Basics of stored procedures
  • Basics of functions
  • Basics of packages
  • Defining stored procedures & functions
  • Function and stored procedures prototypes
  • Passing arguments to functions and stored procedures
  • Recompiling functions and stored procedures
  • Pinning packages in the SGA with dbms_shared_pool.keep
  • Package forward declaration
  • Package dependency
  • Package overloading
  • Listing package information
 
Bulking in PL/SQL
  • Bulk queries
  • Bulk DML (forall statement)
  • Using cursor attributes
  • Analyzing impact of bulk operations