Consulting Training Blog Careers About Us Contact Us

Oracle PL/SQL Training

Upcoming Classes

Sorry, nothing scheduled right now. Click the Get Notified button below to be alerted when a class is scheduled.

Get Notified

On Site/Private

Can't find a class that fits right for you? Contact us to inquire about scheduling your own private class

Contact Us

Description

PL/SQL is Oracle's extension language for standard SQL.

In this PL/SQL training class, students who already know SQL, learn to use PL/SQL to write sophisticated queries against an Oracle database.

Bring This Course To You

For groups of 5 or more, let Intertech bring this course to your location. Customized versions tailored towards your objectives are also available.

Learn More

Course Outline

The Environment for PL/SQL Development
  • Prerequisites
  • File Naming Conventions
  • Connecting to Oracle
  • SQL*Developer
  • SQL*Plus
  • Executing PL/SQL Code
  • SQL Developer Configuration
  • The PL/SQL Development Cycle
PL/SQL Basics
  • PL/SQL Block
  • Anonymous Block Structure
  • Named Block Structure
  • Executing Blocks
  • Calling PL/SQL Functions
  • Executing PL/SQL Blocks and Functions
Declaring Variables
  • Variable Usage
  • Variable Data Types
  • Variable Naming
  • Variable Assignment
  • Variable Display
  • Complex Variable Types
  • More Information
  • Variable Declaration, Initialization and Display
Within the Block
  • Conditional Processing
  • The IF Statement
  • The CASE Statement
  • Iterative Processing
  • The LOOP Statement
  • The WHILE Statement
  • The FOR Statement
  • Salary Classification using Conditional Statements
  • Salary Increases using Loops
Handling Exceptions
  • Overview of Exceptions
  • Causing System Generated Exceptions
  • Handling System Generated Exceptions
  • Identifying System Generated Exceptions
  • OTHERS Exception Handler
  • User Defined Exceptions
  • Exception Handling for Invalid Salary
Use of SQL in PL/SQL
  • Implicit Cursors
  • %TYPE and %ROWTYPE Attributes
  • EXECUTE IMMEDIATE statement
  • Cursors Attributes
  • Implicit Cursor
  • Explicit Cursors
  • Explicit Cursor
  • Cursor FOR loop
Nested Blocks
  • Nesting Blocks
  • Scope of Variables
  • Scope of Exceptions
  • Nested Subprograms
  • Exception Propagation in Nested Blocks
  • Block Variable Visibility
Introducing Subprograms
  • Anonymous blocks
  • Types of Subprograms
  • Finding Subprograms through SQLDeveloper
  • Finding Subprograms in the Oracle Data Dictionary
  • Object Dependencies
  • Subprogram information in the Oracle Data Dictionary
Stored Procedures and Functions
  • Creating Subprograms
  • Modifying Subprograms
  • Removing Subprograms
  • Application Maintenance
  • Exercise Title
  • Parameters
  • Cursors as Parameters
  • Subprogram Development Techniques
  • Addressing Compilation Errors
  • Directives for Debugging
  • Issues with Booleans
  • Integrated Development Environments
  • PL/SQL Subprograms with Parameters
Packages
  • Structure of Packages
  • Purpose of Packages
  • Wrap Utility
  • Positional vs Named Parameter Notation
  • Subprograms Omitted from Specifications
  • Using PL/SQL Packages
Database Triggers
  • Purpose of Triggers
  • Invocation of Triggers
  • Coding Triggers
  • Validation Trigger
  • Modifying Triggers
  • Viewing Triggers
  • Enabling/Disabling Triggers
  • Trigger Errors
  • System and User Event Triggers
  • Logon Counter Trigger
Data Retrieval Techniques
  • Cursor Review
  • Cursor Parameters
  • Cursor Variables
  • Dynamic SQL
  • Employee Report by State/Province
Using Oracle Supplied Packages
  • Oracle Supplied Packages
  • Identifying Available Packages
  • Identifying Package Subprograms
  • Selected Oracle Supplied Package Demos
  • Packages related to Input/Output
  • Packages related to Networking
  • DBMS_SQL: Dynamic SQL
  • DBMS_URL: Working with URLS
  • The DBMS_APPLICATION_INFO
Specialized Topics
  • Application Partitioning
  • Subprograms in other Languages
  • Oracle Object Features
  • Statistical Analysis
  • Data Mining
  • Use Oracle Object Features

Free Resources from Intertech

2 Free Resources: Missed Issues in Software Estimation and Stakeholder Questionnaire

Free eBook: Introducing Microsoft SQL Server 2014

Step-by-Step Guide: Optimize Bulk Loading Data into Oracle

X