Oracle PL/SQL STA_OPI

COURSE 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

TARGET 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.

COURSE OBJECTIVE:

Course Objectives

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

COURSE PREREQUISITES:

Requirements

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 pre-requisite Oracle SQL course. Some previous programming experience will also prove advantageous.

FOLLOW ON COURSES:

Further Learning

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