Get in Touch
 Duration 21 hours

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.

Number of participants


Price per participant

Testimonials (7)

Upcoming Courses

Related Categories