Oracle PL/SQL [STA_OPI]

OVERVIEW

Oracle PL/SQL Course Overview

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.

Similarly,the course is targeted to closely follow the official Oracle Database curriculum for certification.

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Versions supported 12cR2,18c and 19c.

OBJECTIVES

Course Objectives

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

AUDIENCE

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.

NEXT STEP

Further Learning

• Oracle PL/SQL Stored Program Units
• Oracle SQL Advanced
• Oracle Database 19c Administration

CONTENT

Oracle PL/SQL Training Course

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
• 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

Oracle PL/SQL Training Course

Course Contents - 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