Course Outline
Introduction
- Course Aims and Objectives
- Schedule Overview
- Introductions
- Pre-requisites
- Responsibilities
SQL Tools
- Learning Objectives
- SQL Developer
- SQL Developer - Connection Setup
- Viewing Table Information
- Using SQL in SQL Developer - Queries
- SQL*Plus Login
- Direct Connection
- Using SQL*Plus
- Ending the Session
- SQL*Plus Commands
- SQL*Plus Environment
- SQL*Plus Prompt
- Finding Information about Tables
- Getting Help
- Using SQL Files
- iSQL*Plus and Entity Models
- The ORDERS Tables
- The FILM Tables
- Course Tables Handout
- SQL Statement Syntax
- SQL*Plus Commands
What is PL/SQL?
- Overview of PL/SQL
- Benefits of Using PL/SQL
- Block Structure
- Displaying Messages
- Sample Code
- Setting SERVEROUTPUT
- Update Example and Style Guide
Variables
- Variables in PL/SQL
- Datatypes
- Assigning Variables
- Constants
- Local and Global Variables
- %Type Variables
- Substitution Variables
- Comments using &
- Verify Option
- && Variables
- Define and Undefine
SELECT Statement
- The SELECT Statement
- Populating Variables
- %Rowtype Variables
- CHR Function
- Self-Study
- PL/SQL Records
- Example Declarations
Conditional Statement
- IF Statement
- SELECT Statement
- Self-Study
- CASE Statement
Trapping Errors
- Exceptions
- Internal Errors
- Error Codes and Messages
- Using NO DATA FOUND
- User-Defined Exceptions
- Raise Application Error
- Handling Undefined Errors
- Using PRAGMA EXCEPTION_INIT
- Commit and Rollback
- Self-Study
- Nested Blocks
- Workshop
Iteration - Looping
- LOOP Statement
- WHILE Statement
- FOR Statement
- GOTO Statement and Labels
Cursors
- Introduction to Cursors
- Cursor Attributes
- Explicit Cursors
- Explicit Cursor Example
- Declaring the Cursor
- Declaring the Variable
- Opening and Fetching the First Row
- Fetching the Next Row
- Exit When %Notfound
- Closing the Cursor
- FOR Loop I
- FOR Loop II
- Update Example
- FOR UPDATE
- FOR UPDATE OF
- WHERE CURRENT OF
- Committing with Cursors
- Validation Example I
- Validation Example II
- Cursor Parameters
- Workshop
- Workshop Solution
Procedures, Functions and Packages
- CREATE Statement
- Parameters
- Procedure Body
- Displaying Errors
- Describing a Procedure
- Calling Procedures
- Calling Procedures in SQL*Plus
- Using Output Parameters
- Calling with Output Parameters
- Creating Functions
- Example Function
- Displaying Errors
- Describing a Function
- Calling Functions
- Calling Functions in SQL*Plus
- Modular Programming
- Example Procedure
- Calling Functions
- Calling Functions within an IF Statement
- Creating Packages
- Package Example
- Reasons for Using Packages
- Public and Private Sub-programs
- Displaying Errors
- Describing a Package
- Calling Packages in SQL*Plus
- Calling Packages from Sub-programs
- Dropping a Sub-program
- Finding Sub-programs
- Creating a Debug Package
- Calling the Debug Package
- Positional and Named Notation
- Parameter Default Values
- Recompiling Procedures and Functions
- Workshop
Triggers
- Creating Triggers
- Statement Triggers
- Row-Level Triggers
- WHEN Restriction
- Selective Triggers using IF
- Displaying Errors
- Commit in Triggers
- Restrictions
- Mutating Triggers
- Finding Triggers
- Dropping a Trigger
- Generating Auto-numbers
- Disabling Triggers
- Enabling Triggers
- Trigger Naming Conventions
Sample Data
- ORDER Tables
- FILM Tables
- EMPLOYEE Tables
Dynamic SQL
- SQL in PL/SQL
- Binding
- Dynamic SQL Overview
- Native Dynamic SQL
- DDL and DML
- DBMS_SQL Package
- Dynamic SQL - SELECT
- Dynamic SQL - SELECT Procedure
Using Files
- Working with Text Files
- UTL_FILE Package
- Write and Append Example
- Read Example
- Trigger Example
- DBMS_ALERT Packages
- DBMS_JOB Package
COLLECTIONS
- %Type Variables
- Record Variables
- Collection Types
- Index-By Tables
- Setting Values
- Handling Nonexistent Elements
- Nested Tables
- Nested Table Initialisation
- Using the Constructor
- Adding to a Nested Table
- Varrays
- Varray Initialization
- Adding Elements to a Varray
- Multilevel Collections
- Bulk Bind
- Bulk Bind Example
- Transactional Issues
- BULK COLLECT Clause
- RETURNING INTO
Ref Cursors
- Cursor Variables
- Defining REF CURSOR Types
- Declaring Cursor Variables
- Constrained and Unconstrained Cursors
- Using Cursor Variables
- Cursor Variable Examples
Requirements
This course is designed for individuals who possess a foundational understanding of SQL.
While prior experience with interactive computer systems is recommended, it is not strictly required.
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.
