PL/SQL Fundamentals I

 Click here

Course Overview

This course introduces students to the fundamentals of the PL/SQL programming language in the Oracle database environment. The course consists of a series of concise lessons, with each lesson preparing students to interact with the Oracle RDBMS.

Students learn how to develop, execute and manage PL/SQL program units (anonymous blocks, procedures and functions) that can be shared among Oracle forms, reports and applications. They also learn about declaring variables, using explicit cursors, and handling exceptions.

This course allows students to expand their knowledge of the presented concepts through several demonstrations and hands-on exercises based on real-world applications.

Course Goals

• To provide students with a solid understanding of Oracle RDBMS and the PL/SQL programming language.
• To assist students with developing the coding skills necessary for building PL/SQL program units that can be in a Oracle database environment.
• To complete hands-on exercises in an interactive, instructor-led environment.

Prerequisites

• Previous programming experience is suggested.

Who Should Attend?

• Application Developers
• PL/SQL Developers
• Forms Developers
• Database Administrators
• System Analysts
• Technical Consultants
• Portal Developers
• Functional Implementers

Course Materials

• Student Handbook
• PL/SQL Reference Book

Duration

• Three (3) days

Course Outline

• Introduction
  • Oracle PL/SQL Developer
  • Creating a Database Connection
  • Creating Schema Objects
  • Using the SQL Worksheet
  • Executing PL/SQL Statements
• Introduction to PL/SQL
  • What is PL/SQL ?
  • Benefits of PL/SQL
  • PL/SQL Block Structure
  • Anonymous Blocks
• PL/SQL Variables
  • Variables in PL/SQL
  • Naming Conventions
  • Variable Declarations
  • Scalar Data Types
  • Scalar Variables
  • Bind Variables
  • Composite Data Types
  • LOB Variables
• Executable Statements
  • Lexical Units
  • Coding Guidelines
  • SQL Functions in PL/SQL
  • Nested Blocks
  • Variable Scope and Visibility
  • Operators in PL/SQL
• Implicit Cursors
  • SELECT Statements in PL/SQL
  • Inserting Data
  • Updating Data
  • Deleting Data
  • Merging Rows
  • Implicit SQL Cursors
  • Cursor Attributes
• Control Structures
  • IF Statements
  • CASE Expressions
  • CASE Statement
  • Handling Nulls
  • Boolean Logic
  • Basic Loops
  • WHILE Loops
  • FOR Loops
  • Nested Loops and Labels
• Explicit Cursors
  • Declaring the Cursor
  • Opening the Cursor
  • Fetching Data from the Cursor
  • Closing the Cursor
  • Cursor FOR Loops
  • Cursor Attributes
• Handling Exceptions
  • Trapping Exceptions
  • Predefined Exceptions
  • Non-Predefined Exceptions
  • User-Defined Exceptions
  • Propagating Exceptions
  • The RAISE_APPLICATION_ERROR Procedure
• Stored Procedures
  • What Is a Procedure?
  • Developing Procedures
  • What Are Parameters?
  • Procedural Parameter Modes
  • Syntax for Passing Parameters
  • Invoking Procedures
  • Handled Exceptions
  • Exceptions Not Handled
• User-Defined Functions
  • Developing Functions
  • Ways to Execute Functions
  • Procedures vs Functions
• Creating Packages
  • PL/SQL Packages
  • Developing PL/SQL Packages
  • Invoking Package Subprograms
  • Using Bodiless Packages
  • Package Guidelines
  • Advantages of Using Packages
  • Overloading Subprograms
  • Using Forward Declarations
  • Package Initialization Block
• DML Triggers
  • Creating DML Triggers
  • Conditional Predicates
  • OLD and NEW Qualifiers
  • Restricting a Row Trigger
  • Trigger Execution
  • INSTEAD OF Triggers