Oracle PL/SQL (STA_OPI)

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