Oracle PL/SQL Course | DevelopIntelligence

Course Summary

The Oracle PL/SQL course is designed to introduce students to the procedural language extensions, how to effectively use PL/SQL for a wide range of use cases, and understand how to use various features of PL/SQL effectively. PL/SQL is a variant of ANSI SQL that is supported by Oracle databases to enable developers to create more dynamic SQL code and use concepts like loops and conditions.

The course begins with an introduction to Oracle SQL database and the use of variables and types within PL/SQL. Next, students will start exploring query capabilities using PL/SQL to be able to create complex programs using control structures, loops, and cursors. Students will get hands-on experience creating robust programs that can handle errors as well as implement and use reusable components like stored procedures, functions, and collections. Finally, students will learn use cases for triggers and bulk operations in order to be able to implement advanced functionality within the Oracle database.

Students should have working knowledge of the Oracle database and at least some prior knowledge of SQL.

Purpose

Learn how to use PL/SQL on Oracle databases and apply those skills to real world problems.

Audience

Software developers, system administrators, and technical managers who want to understand how to use Oracle PL/SQL.

Role

Software Developer - System Administrator - Web Developer

Skill Level

Introduction

Style

Workshops

Duration

3 Days

Related Technologies

SQL

Download

Productivity Objectives:

Develop efficient and effective PL/SQL code to utilize Oracle databases.
Create stored procedures and functions for reuse and maintenance.
Design modular applications using packages.
Improve your PL/SQL through optimizations to improve query runtimes.
Verify the accuracy of the data you are querying through PL/SQL.

Learn About Virtual Training

Request Information

Get your team upskilled or reskilled today. Chat with one of our experts to create a custom training proposal. Fully customized at no additional cost.

If you are not completely satisfied with your training class, we'll give you your money back.

about our training

Real-World Content

Project-focused demos and labs using your tool stack and environment, not some canned "training room" lab.
Expert Practitioners

Industry experts with 15+ years of industry experience that bring their battle scars into the classroom.
Experiential Learning

More coding than lecture, coupled with architectural and design discussions.
Fully Customized

One-size-fits-all doesn't apply to training teams. That's where we come in!

What You'll Learn

In the Oracle PL/SQL training course, you'll learn:

Overview of PL/SQL
- PL/SQL Programming Standards
Declaring PL/SQL Variables and Types

Data and Record Types
Working with Arrays and Collections
Using attributes to make declarations

Control Structures
- Using various IF THEN statements
- Working with Nested IF statements
- Control flow with simple and searched CASE statements
Looping Through Data
- The basic LOOP
- Using the FOR LOOP to execute code n number of times
- Conditional LOOP using WHILE
- Exiting LOOP using CONDITION statements
Embedding SQL within PL/SQL
- Using implicit cursors
- Fetch single rows using SELECT INTO
- Using INSERT, UPDATE AND DELETE
- Why do I need COMMIT and ROLLBACK?
- Using statement attributes SQL%FOUND, SQL%NOTFOUND and SQL%ROWTYPE
Explicit Cursors
- Execution cycle of an explicit cursor
- How to declare, open, fetch and close a cursor
- Choosing a Loop Structure
- Executing a FOR Loop Cursor
Understanding How to Handle Exceptions
- Constructing exception handlers
- Using predefined and user-defined exceptions
- Examples using DECLARE, RAISE and HANDLE
- Propagation of Exceptions
Creating Reusable Code with Stored Procedures
- Understanding the constructs of stored procedures
- Using Functions within your stored procedures
- Executing stored procedures
- What is the difference between a stored procedure and a function?
Using Packages to Compile Different Functionality
- Why would you need Packages?
- Package specifications
Working with Collections in PL/SQL
- Overview of types of Collections
  - Nested Tables
  - VARRAYs
  - Associative Arrays
- Why do I need Arrays?
Using Triggers to React to Events
- What use cases do triggers apply to?
- Creating triggers based on events
- Statement level triggers
- Row-level triggers
- Using the INSTEAD OF trigger
- How to enable, disable or drop triggers
Executing BULK Operations to Improve Performance
- Why do I need BULK operations?
- Using SELECT BULK COLLECT
- Creating FORALL statements
- Using INSERT, UPDATE and DELETE
- What options do I have with BULK operations?
Best Practices and Performance Tuning
- Using the PL/SQL Optimizer
- Minimizing CPU overhead
- Executing parallel updates
- Profiling and tracing