Course Outline
Introduction
- Goals and Learning Outcomes
- Training Schedule
- Participant Introductions
- Prerequisites
- Roles and Responsibilities
SQL Tools
- Learning Objectives
- SQL Developer Overview
- Connecting via SQL Developer
- Inspecting Table Metadata
- Querying with SQL in SQL Developer
- SQL*Plus Authentication
- Establishing Direct Connections
- Working with SQL*Plus
- Terminating the Session
- SQL*Plus Command Set
- The SQL*Plus Environment
- SQL*Plus Prompt Usage
- Retrieving Table Information
- Accessing Help Resources
- Executing SQL Scripts
- iSQL*Plus and Entity Modelling
- The ORDERS Table Structure
- The FILM Table Structure
- Course Tables Reference Sheet
- SQL Statement Syntax
- SQL*Plus Commands
Understanding PL/SQL
- Definition of PL/SQL
- Benefits of Using PL/SQL
- Block Architecture
- Outputting Messages
- Code Samples
- Configuring SERVEROUTPUT
- Update Example and Coding Style Guide
Variables
- Variable Concepts
- Data Types
- Assigning Values to Variables
- Constants
- Local vs. Global Scope
- %Type Variables
- Substitution Variables
- Using Comments with &
- Verify Option
- && Variables
- Define and Undefine Commands
SELECT Statement
- The SELECT Clause
- Populating Variables with Data
- %Rowtype Variables
- The CHR Function
- Self-Study Module
- PL/SQL Records
- Declaration Examples
Conditional Statements
- IF Logic
- SELECT within Conditionals
- Self-Study Module
- CASE Expressions
Error Handling
- Exception Mechanisms
- Internal Errors
- Error Codes and Messages
- Handling No Data Found
- Custom User Exceptions
- Raising Application Errors
- Capturing Undefined Errors
- Using PRAGMA EXCEPTION_INIT
- Commit and Rollback Strategies
- Self-Study Module
- Nested Blocks
- Workshop Exercise
Iteration - Looping
- Basic Loop Statements
- While Loops
- For Loops
- Goto Statements and Labels
Cursors
- Cursor Fundamentals
- Cursor Attributes
- Explicit Cursors
- Explicit Cursor Demonstration
- Cursor Declaration
- Variable Declaration for Cursors
- Opening and Fetching the First Row
- Fetching Subsequent Rows
- Exit Condition with %Notfound
- Closing the Cursor
- For Loop Implementation I
- For Loop Implementation II
- Update Scenarios
- FOR UPDATE Clause
- FOR UPDATE OF Clause
- WHERE CURRENT OF Clause
- Committing with Cursors
- Validation Example I
- Validation Example II
- Cursor Parameters
- Workshop Exercise
- Workshop Solution
Procedures, Functions and Packages
- CREATE Statement
- Parameter Definitions
- Procedure Body Structure
- Error Display
- Describing a Procedure
- Invoking Procedures
- Calling Procedures in SQL*Plus
- Leveraging Output Parameters
- Invocation with Output Parameters
- Creating Functions
- Function Examples
- Error Display
- Describing a Function
- Invoking Functions
- Calling Functions in SQL*Plus
- Modular Programming Principles
- Procedure Examples
- Function Invocation
- Functions within IF Statements
- Creating Packages
- Package Examples
- Advantages of Packaging
- Public and Private Sub-programs
- Error Display
- Describing a Package
- Calling Packages in SQL*Plus
- Calling Packages from Sub-programs
- Dropping Sub-programs
- Locating Sub-programs
- Creating a Debug Package
- Invoking the Debug Package
- Positional vs. Named Notation
- Default Parameter Values
- Recompiling Procedures and Functions
- Workshop Exercise
Triggers
- Creating Triggers
- Statement-Level Triggers
- Row-Level Triggers
- WHEN Clauses
- Conditional Triggers via IF
- Error Display
- Commit Behavior in Triggers
- Usage Restrictions
- Mutating Triggers
- Locating Triggers
- Dropping Triggers
- Auto-Numbering Generation
- Disabling Triggers
- Enabling Triggers
- Naming Conventions for Triggers
Sample Data
- ORDER Table Structure
- FILM Table Structure
- EMPLOYEE Table Structure
Dynamic SQL
- Embedding SQL in PL/SQL
- Binding Variables
- Dynamic SQL Concepts
- Native Dynamic SQL
- DDL and DML Operations
- The DBMS_SQL Package
- Dynamic SQL for SELECT
- SELECT Procedure Implementation
File Management
- Working with Text Files
- The UTL_FILE Package
- Write and Append Operations
- Read Operations
- Trigger-Based File Examples
- DBMS_ALERT Package
- DBMS_JOB Package
COLLECTIONS
- %Type Variables
- Record Variables
- Collection Data Types
- Index-By Tables
- Assigning Values
- Handling Missing Elements
- Nested Tables
- Nested Table Initialization
- Using Constructors
- Extending Nested Tables
- Variable-Size Arrays (Varrays)
- Varray Initialization
- Adding Elements to Varrays
- Multilevel Collections
- Bulk Bind Operations
- Bulk Bind Demonstrations
- Transactional Considerations
- BULK COLLECT Clause
- RETURNING INTO Clause
Ref Cursors
- Cursor Variables
- Defining REF CURSOR Types
- Declaring Cursor Variables
- Constrained vs. Unconstrained Types
- Utilising Cursor Variables
- Cursor Variable Demonstrations
Requirements
This course is best suited for individuals who possess a foundational understanding of SQL.
Prior experience with interactive computer systems is recommended, though not strictly mandatory.
Testimonials (7)
I liked the hands-on experience and the opportunity to work on actual coding activities
Kristine - Isuzu Philippines Corporation
Course - ORACLE PL/SQL Fundamentals
Relate each topic to a real world application case.
Ferdinand - National Grid Corporation of the Philippines
Course - ORACLE PL/SQL Fundamentals
the practices and the trainer notes
Hamda AlMahri - Dubai Courts
Course - ORACLE PL/SQL Fundamentals
Mr. Khobeib was a great lecturer and trainer. As a beginner to PL/SQL, Khobeib explained the basics and was patient with us while going through the training material. He answered all our questions thoroughly and showed a lot of examples when we asked him to. I definitely learned a lot and can start doing tasks with PL/SQL.
Abdulrahman Alsalami - Dubai Courts
Course - ORACLE PL/SQL Fundamentals
the trainer helpful all the time
Maitha Alselais - Dubai Courts
Course - ORACLE PL/SQL Fundamentals
The trainer was fantastic in all aspects. He was very interactive and engaging. Most importantly, the topics were taught very clearly and at a perfect pace to complete the course. I really appreciate it and would like to give a huge thank you to the trainer.
Vivek Thomas - Estee Lauder BV
Course - ORACLE PL/SQL Fundamentals
It was quite hands-on, not too much theory.