Course Description

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.

The course closely follows the official Oracle Database curriculum for certification.

Exercises and examples provide practical hands-on experience.

Versions supported: 12cR2, 18c, 19c.

Target Student

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

Pre-requisites

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

Course Objectives

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

Course Content

Course Contents - 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
  • Initializing 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
  • Parameterized Cursors

Course Contents - DAY 2

• Session 4: EXCEPTIONS AND NESTED BLOCKS
  • The EXCEPTION Section
  • Types of Exception
  • Handling Named System-Raised Exceptions
  • Handling Unnamed 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