Learn PL/SQL Programming Fundamentals

This hands-on course on PL/SQL fundamentals will give you a thorough and complete understanding of programming using Oracle’s proprietary progrmming language. Lab assignments and exercises accompany all topics to help you master this programming language.

View Schedules

Course Details

Introduction to PL/SQL Programming

  • About PL/SQL and the Limitations of SQL
  • What is PL/SQL and its advantages
  • PL/SQL architecture and Statement processing
  • Creating Anonymous PL/SQL Block

    Learn PL/SQL programming

  • The PL/SQL Block Structure
  • Syntax and Guidelines
  • Types of blocks
  • Variable declaration and guidelines
  • Tools used to write PL/SQL programs

Understanding and Declaring Variables

  • PL/SQL Datatypes – Scalar Datatyples
  • Bind variables
  • Displaying output on the screen using PL/SQL
  • Variable syntax, initialization and their use in programs
  • Variable scope and Nested blocks
  • Commenting Code

Building the Body of the PL/SQL program

  • Including different types of operators in PL/SQL
  • Use of SQL functions in PL/SQL programs
  • SQL statements in PL/SQL blocks
  • Using the SELECT statement to retrieve data in PL/SQL
  • The %TYPE attribute and its advantages
  • Manipulating data (Insert/Update/Delete) in PL/SQL
  • Handling transactions in PL/SQL

Learn PL/SQL programming constructs

  • Conditional and logic statements
  • Different types of IF statements in PL/SQL programs
  • CASE Statement in PL/SQL programs
  • Iterative or loop statements
  • Use of NULL statements in PL/SQL programs

Cursors in PL/SQL

  • Implicit Cursors (The SQL cursor)
  • Implicit Cursors attributes
  • Understanding the need for Explicit Cursors
  • Explicit Cursor attributes
  • Cursor FOR loop
  • Performing Updates through a cursor

Composite Datatypes

  • Understanding the need for Composite datatypes
  • PL/SQL records
  • Associative arrays or Index-by-tables
  • Methods used with associative arrays
  • The %ROWTYPE attribute

Handling errors in a PL/SQL block

  • Understanding errors and the need for the Exception Section
  • Types of exceptions
  • Predefined Oracle errors
  • Non-Predefined Oracle errors
  • User-defined errors

Named PL/SQL Blocks – Procedures

  • The use of named or Stored PL/SQL blocks
  • Developing Procedures
  • Parameters and their modes
  • Declaring and Invoking procedures

Named PL/SQL Blocks – Functions

  • Creating Functions
  • Creating and Invoking functions
  • Understanding the difference between procedures and functions

Named PL/SQL Blocks – Packages

  • Overview of what are packages in Application development
  • Creating and Invoking Packages
  • Public and Private constructs in a package
  • Forward declaration of private constructs
  • Bodiless packages
  • Overloaded packages

Named PL/SQL Blocks – Database Triggers

  • Creating and Managing Triggers
  • Different Examples of triggers
  • Row and Statement triggers
  • DML triggers
  • System triggers