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.
Instructor(s):Self-Study
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.
Hide Syllabus
Lesson 1
- PL/SQL Basics
- What is PL/SQL?
- Blocks
- Outputting Information
- Variables and Constants
- Constants
- Data Types
- Naming Variables and Other Elements
- Embedding SQL in PL/SQL
- SELECT...INTO and RETURNING...INTO
- PL/SQL Features
- Subprograms
- Introduction to Subprograms
- Procedures
- Variable Declarations
- Dropping a Procedure
- Parameters
- Parameters with Default Values
- Parameter Modes
- IN Mode
- OUT Mode
- IN OUT Mode
- Named Notation
- Using SQL in a Subprogram
- %TYPE
- Functions
- Using PL/SQL Functions in SQL Queries
- Conditional Processing
- Conditions and Booleans
- IF-ELSIF-ELSE Conditions
- ELSIF
- BOOLEAN Values and Standard SQL
- The CASE Statement
- CASE Expressions
- Exceptions
- Introduction to Exceptions
- Predefined Exceptions
- The EXCEPTION Part of the Block
- User-defined Exceptions
- User-defined Exceptions in Subprograms
- Re-raising Exceptions
- Naming Unnamed Predefined Exceptions
- WHILE Loops
- When to Use Exceptions
- Cursors
- Implicit Cursors
- Explicit Cursors
- %ROWTYPE
- Explicit Cursor Use Case
- Cursor FOR LOOP
- Cursor Parameters
- Packages
- Package Basics
- The Package Specification
- The Package Body
- Building an Employee Package
- Overloading Subprograms
- Auditing
- Validation Procedures
- Package Cursors
- Benefits of Packages
- Triggers
- What are triggers?
- Trigger Parts
- Validation Triggers
- The WHEN Clause
- Audit Triggers
- Statement-level Triggers
- Compound Triggers
- Trigger Warning
Hide Syllabus
Login/Create account
checkout
view cart (
0
)
