Oracle PL/SQL Training

Oracle PL/SQL Training (ORA200)

Course Length: 5 days
Delivery Methods: Multiple delivery options
Course Overview

This course provides a comprehensive introduction to PL/SQL, a powerful procedural language extension for SQL used in Oracle databases. It is designed for individuals who want to gain a solid understanding of PL/SQL programming, including database developers, administrators, and anyone looking to enhance their SQL skills with advanced procedural capabilities.

The course begins with the basics of PL/SQL, including an overview of the HR Schema, the structure of PL/SQL blocks, and how to output information. You will learn about variables, constants, data types, and how to embed SQL within PL/SQL code. As you progress, you will explore subprograms, including procedures and functions, and understand how to manage parameters and handle various modes of operation.

Advanced topics covered in this course include conditional processing with IF-ELSIF-ELSE structures, Boolean logic, and CASE statements. You’ll also dive into exception handling, learning how to catch predefined and user-defined exceptions effectively. The course provides in-depth coverage of cursors, including implicit and explicit cursors, and how to use them within PL/SQL programs.

Additionally, the course covers packages, an essential feature of PL/SQL that allows you to group related procedures, functions, and other program elements together. You’ll also learn about triggers, which are automated actions executed in response to specific events on a table or view.

By the end of this course, you’ll be equipped with the skills to write efficient PL/SQL code, handle complex database operations, and utilize advanced features like packages and triggers to enhance the functionality of your database applications.

Course Benefits
  • Learn what PL/SQL is.
  • Learn how to declare, name, assign and output variables in PL/SQL.
  • Learn about data types in PL/SQL.
  • Learn about the main features of PL/SQL.
  • Learn how to write a simple block of PL/SQL code.
  • Learn about subprograms in PL/SQL.
  • Learn to create and call procedures.
  • Learn to create and call functions.
  • Understand BOOLEAN values and expressions.
  • Learn to write IF conditions.
  • Learn to use the CASE statement.
  • Learn to handle exceptions in PL/SQL.
  • Learn about how cursors works.
  • Learn to use implicit cursors.
  • Learn to use explicit cursors.
  • Learn to use packages to group related PL/SQL objects.
  • Learn to use triggers for validating data and auditing changes to the database.
Available Delivery Methods
Public Class
Public expert-led online training from the convenience of your home, office or anywhere with an internet connection. Guaranteed to run .
Private Class
Private classes are delivered for groups at your offices or a location of your choice.
Self-Paced
Learn at your own pace with 24/7 access to an On-Demand course.
Course Outline
  1. PL/SQL Basics
    1. What is PL/SQL?
    2. Blocks
    3. Outputting Information
    4. Variables and Constants
    5. Constants
    6. Data Types
    7. Naming Variables and Other Elements
    8. Embedding SQL in PL/SQL
    9. SELECT...INTO and RETURNING...INTO
    10. PL/SQL Features
  2. Subprograms
    1. Introduction to Subprograms
    2. Procedures
    3. Variable Declarations
    4. Dropping a Procedure
    5. Parameters
    6. Parameters with Default Values
    7. Parameter Modes
    8. IN Mode
    9. OUT Mode
    10. IN OUT Mode
    11. Named Notation
    12. Using SQL in a Subprogram
    13. %TYPE
    14. Functions
    15. Using PL/SQL Functions in SQL Queries
  3. Conditional Processing
    1. Conditions and Booleans
    2. IF-ELSIF-ELSE Conditions
    3. ELSIF
    4. BOOLEAN Values and Standard SQL
    5. The CASE Statement
    6. CASE Expressions
  4. Exceptions
    1. Introduction to Exceptions
    2. Predefined Exceptions
    3. The EXCEPTION Part of the Block
    4. User-defined Exceptions
    5. User-defined Exceptions in Subprograms
    6. Re-raising Exceptions
    7. Naming Unnamed Predefined Exceptions
    8. WHILE Loops
    9. When to Use Exceptions
  5. Cursors
    1. Implicit Cursors
    2. Explicit Cursors
    3. %ROWTYPE
    4. Explicit Cursor Use Case
    5. Cursor FOR LOOP
    6. Cursor Parameters
  6. Packages
    1. Package Basics
    2. The Package Specification
    3. The Package Body
    4. Building an Employee Package
    5. Overloading Subprograms
    6. Auditing
    7. Validation Procedures
    8. Package Cursors
    9. Benefits of Packages
  7. Triggers
    1. What are triggers?
    2. Trigger Parts
    3. Validation Triggers
    4. The WHEN Clause
    5. Audit Triggers
    6. Statement-level Triggers
    7. Compound Triggers
    8. Trigger Warning
Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Prerequisite Courses

Courses that can help you meet these prerequisites:

Live Public Class
$2,875.00 / student
Please select a class.
Live Private Class
  • Private Class for your Team
  • Live training
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Proposal
Self-Paced Course
5 coupons
  • On Demand 24/7
  • Readings
  • Presentations
  • Exercises
  • Quizzes
  • Full Year of Access
  • Learn more
Register Now