Menu

Tel: 01423 534630    Email: info@tts-uk.com

Oracle PL/SQL

The Oracle PL/SQL course is designed to introduce Oracle DBAs, software development personnel and database support staff to the primary features and constructs of the PL/SQL Programming Language.

Versions supported 11gR2, 12cR2, 18c, 19c.

Course Objectives

To provide the skills needed to write stand-alone programs using the main features of Oracle’s PL/SQL Programming Language.

Who will the Course Benefit?

Oracle database administrators, software development personnel and database support staff who need to write PL/SQL scripts to enhance their productivity in manipulating data.

Course Information

The 2 Day Oracle PL/SQL course can be combined with the 3 Day Stored Program Units course, this is equivalent to attending the 5 Day Oracle Program with PL/SQL course.

Skills Gained

The delegate will learn and acquire skills as follows:

Writing and running PL/SQL programs from SQL*Plus or SQL Developer
Declaring and using variables and constants
Assigning and manipulating values
Using SQL statements within PL/SQL
Implementing conditional execution and loop constructs
Declaring and using cursors with and without parameters
Updating data with cursors
Trapping and handling errors and exceptions in code
Implementing nested and labelled loops and blocks
Raising exceptions
Handling PL/SQL records and tables
Using PL/SQL table built-in functions

Prerequisites

A working knowledge of SQL*Plus or SQL Developer and Oracle SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite course. Some previous programming experience will also prove advantageous.

Pre-Requisite Courses
Oracle SQL


  • Course content

    Day 1

    Course Introduction
    Administration and Course Materials
    Course Structure and Agenda
    Delegate and Trainer Introductions
    Session 1: PL/SQL FUNDAMENTALS
    What is PL/SQL?
    Basic Elements
    Variables and Constants
    Data Types
    Initialising Variables and Assigning Values
    Using SQL Statements in Code
    Generating Output to SQL or SQL Developer
    Session 2: PROGRAM LOGIC
    IF THEN ELSIF ELSE Statements
    CASE Statements
    The Basic Loop Construct
    WHILE and FOR Loops
    Nested and Labelled Loops
    The GOTO Statement
    The CONTINUE Statement
    Session 3: USING CURSORS
    What is a Cursor?
    Implicit and Explicit Cursors
    Cursor Operations
    Declaring, Opening and Closing Cursors
    Fetching Rows
    Status Checking
    Using Cursors FOR UPDATE
    The Cursor FOR Loop
    Parameterised Cursors

    Day 2

    Session 4: EXCEPTIONS AND NESTED BLOCKS
    The EXCEPTION Section
    Types of Exception
    Handling Named System-Raised Exceptions
    Handling Un-named System-Raised Exceptions
    User-Declared Exceptions and Application Errors
    WHEN OTHERS THEN NULL
    Nested and Labelled Blocks
    Propagation of Exceptions
    Scope of Variables and Cursors
    Scope of Goto Statements
    Session 5: PL/SQL RECORDS AND INDEX-BY TABLES
    Declaring Record Types
    Handling PL/SQL Records
    Nested Records
    Declaring PL/SQL Index-By Tables or Associative Arrays
    PL/SQL Table Built-in Functions
    Manipulating PL/SQL Tables or Associative Arrays

Duration: 2 days
Price: £845 + VAT

Payment logos


Book Now Enquire now

Share Course:

Register to attend

    All fields with * are mandatory.