Instructor-led training

Oracle PL/SQL

Data training from Hartmann Software Group, delivered onsite or live online.

Request a Training Proposal View Course Outline

What you'll learn

This Oracle PL/SQL course is designed for people who are already familiar with Oracle SQL and want to learn the fundamentals of PL/SQL programming in Oracle Database. The course covers topics such as PL/SQL basics, subprograms, conditional processing, exceptions, cursors, packages, and triggers. Through practical exercises and real-world examples, students will gain a strong foundation in PL/SQL and learn how to create efficient, maintainable, and secure applications in Oracle databases.

Prerequisites

SQL

Course Outline

PL/SQL Basics
1. The HR Schema
2. What is PL/SQL?
3. Blocks
4. Outputting Information
5. Variables and Constants
6. Constants
7. Data Types
8. Naming Variables and Other Elements
9. Embedding SQL in PL/SQL
10. SELECT…INTO and RETURNING…INTO
11. Using Variables (Exercise)
12. PL/SQL Features
Subprograms
1. Introduction to Subprograms
2. Procedures
3. Variable Declarations
4. Parameters
5. Parameters with Default Values
6. Parameter Modes
7. IN Mode
8. OUT Mode
9. IN OUT Mode
10. Named Notation
11. Using SQL in a Subprogram
12. %TYPE
13. Creating a Procedure (Exercise)
14. Functions
15. Creating a Function (Exercise)
16. Using PL/SQL Functions in SQL Queries
17. Dropping a Subprogram
Conditional Processing
1. Conditions and Booleans
2. IF-ELSIF-ELSE Conditions
3. Creating a get_age() Function (Exercise)
4. ELSIF
5. Creating a check_rights() Procedure (Exercise)
6. Creating an is_manager() Function (Exercise)
7. BOOLEAN Values and Standard SQL
8. The CASE Statement
9. CASE Expressions
10. Replacing the Head Honcho (Exercise)
Exceptions
1. Introduction to Exceptions
2. Predefined Exceptions
3. The EXCEPTION Part of the Block
4. Catching NO_DATA_FOUND Exception (Exercise)
5. User-defined Exceptions
6. User-defined Exceptions in Subprograms
7. Re-raising Exceptions
8. Replacing the Head Honcho (revisited) (Exercise)
9. Adding Exceptions to update_employee_manager() (Exercise)
10. Naming Unnamed Predefined Exceptions
11. WHILE Loops
12. When to Use Exceptions
Cursors
1. Implicit Cursors
2. Using Implicit Cursor Attributes (Exercise)
3. Explicit Cursors
4. %ROWTYPE
5. Explicit Cursor Use Case
6. Cursor FOR LOOP
7. Using an Explicit Cursor (Exercise)
8. Cursor Parameters
Packages
1. Package Basics
2. The Package Specification
3. The Package Body
4. Modifying the Package (Exercise)
5. Building an Employee Package
6. Adding a get_manager() Function (Exercise)
7. Overloading Subprograms
8. Adding Overloaded Functions to the Package (Exercise)
9. Auditing
10. Validation Procedures
11. Adding a Validation Procedure (Exercise)
12. Package Cursors
13. Adding a Cursor to the Package (Exercise)
14. Benefits of Packages
Triggers
1. What are Triggers?
2. Trigger Parts
3. Validation Triggers
4. Creating a Trigger on the jobs Table (Exercise)
5. The WHEN Clause
6. Using the WHEN Clause (Exercise)
7. Audit Triggers
8. Statement-level Triggers
9. Compound Triggers
10. Trigger Warning

Training locations

Oracle PL/SQL training across the U.S. and beyond.

We provide Oracle PL/SQL training onsite and live online for organizations throughout the United States and internationally.

View all Oracle PL/SQL training locations

Ready to train your team?

Let's customize this training for your organization.

Tell us about your employees, your goals, and your preferred schedule. We'll prepare a training proposal covering scope, delivery format, and pricing.

Request a Training Proposal

or call 303.377.6176