Introduction to Oracle PL/SQL Programming

Duration: 3 Days

Method: Instructor led, Hands-on workshops

Price: $1800.00

Course Code: OR5460

View Related Video

Audience

Programmers and Database Administrators responsible for the development or maintenance of application systems which access an Oracle database.

Description

The course provides a comprehensive introduction to PL/SQL, Oracle’s procedural language extension to SQL. PL/SQL is used to write stored procedures, packages, and database triggers. PL/SQL will be of particular interest to individuals developing applications in a client-server or multi-tier environment.This course can be taught for any Oracle version.

Prerequisites

OR5450 Oracle SQL or equivalent experience

Topics

I. Overview of PL/SQL
II. Variables and Constants
- Identifiers
- Datatypes
- Variables
- Using the %TYPE Attribute
- Using the %ROWTYPE Attribute
- Declaring PL/SQL Records
- Declaring an Array
III. PL/SQL Executable Statements
- Control Structures
  - IF-THEN-ELSE
  - IF-THEN-ELSIF
  - Nested IF Statements
  - Simple CASE Statement
  - Searched CASE Statement
- Loops
  - Basic (Simple) Loop
  - FOR Loop
  - WHILE Loop
IV. Embedding SQL Within PL/SQL
- Implicit Cursors
- SELECT … INTO
- INSERT, UPDATE, DELETE
- COMMIT, ROLLBACK
- Using SQL Attributes
  - SQL%FOUND, SQL%NOTFOUND
  - SQL%ROWTYPE
V. Explicit Cursors
- Cursor Steps
  - DECLARE, OPEN, FETCH, CLOSE
- Choosing a Loop Structure
- The FOR Loop Cursor
VI. Handling Exceptions
- Predefined Exceptions
- User-Defined Exceptions
  - DECLARE, RAISE, HANDLE
- RAISE Statement
- Propagation of Exceptions
VII. PL/SQL Stored Programs
- Stored Procedures
- Using Parameters
- Functions
  - The RETURN Statement
- Executing Stored Programs
- Stored Procedures vs. Functions
- Packages
  - Benefits of Packages
  - The Package Specification
  - The Package Body
- Triggers
  - Trigger Events
  - Trigger Types
  - Row Level Triggers
  - Statement Level Triggers
  - Trigger Behavior
VIII. Oracle 18c New Features
- Non-Persistence Support for Object Types
- Polymorphic Table Functions
- PL/SQL Hierarchical Profiler (DBMS_HPROF) Enhancements
- PL/SQL Qualified Expressions
- Non-Persistable Data Types
- Private Temporary Tables
- SQL*Plus Changes
- SQL Developer Changes
- Miscellaneous PL/SQL Changes
- Deprecated Features
- Desupported Features