Oracle PL/SQL Training   

Take your SQL skills to the next level and learn to write queries with PL/SQL in this online SQL training course.

This self-paced SQL training course is perfect for advancing your current SQL skills. While Oracle uses a standard SQL like other major databases, PL/SQL is specific to Oracle. PL/SQL is a procedural language with variables, conditions, loops, subprograms, stored procedures, and functions. By course completion, you will understand and know how to use PL/SQL's main features, including declaring, naming, and assigning variables.

Requirements:

Hardware Requirements:

• This course must be taken on a PC. Macs are not compatible.

Software Requirements:

• PC: Windows 10 or later.
• Oracle Database Express Edition (XE) 12c or 18c - Free edition of Oracle RDBMS.
• Oracle SQL Developer - The client interface for connecting to Oracle (software not included).
• Browser: The latest version of Google Chrome or Mozilla Firefox are preferred. Microsoft Edge and Safari are also compatible.
• Adobe Acrobat Reader.

Other:

• Software must be installed and fully operational before the course begins.

Instructional Material Requirements:

The instructional materials required for this course are included in enrollment and will be available online.

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

What you will learn

• How to write a simple block of PL/SQL code and about subprograms in PL/SQL
• To create and call procedures and functions
• To write IF conditions, use the CASE statement, and handle exceptions in PL/SQL
• To use implicit and explicit cursors
• To use triggers for validating data and auditing changes to the database

How you will benefit

• Gain skills that every developer needs
• Bring your advanced SQL/PL skills to any web development job
• Advance your career as an Oracle developer