PL/SQL Programming

The Oracle PL/SQL language is a flexible extension to SQL and mainly intended to increase productivity, performance, scalability and security.

In this training, you will gain the essential knowledge allowing you to understand how to write PL/SQL programs. You will learn how to create Anonymous Blocks, Stored Procedures, Functions. In this course you’ll also learn how to design modular Packages, and increase the efficiency of automation using Triggers.

In this course, each module is reinforced with structured hands-on exercises.

Audience Profile

This course is mainly intended for Database Administrators, Database Developers, Business Intelligence professionals, QA professionals, Data Analysts, and other roles responsible for developing PL/SQL code.

Prerequisites

Previous knowledge in SQL

Module 1 - PL/SQL Variables

Declaring variables
Executable statements
Global variables
Substitution variables

Module 2 - PL/SQL Blocks

Interacting with the Database Engine
Using nested blocks

Module 3 - SELECT and DML within PL/SQL Blocks

SELECT INTO statement
DML with PL/SQL blocks
Using Implicit Cursors

Module 4 - Conditions and Loops

Using IF, CASE, LOOP, WHILE and FOR
exit and continue
Nested loops

Module 5 - Composite Data Types

Using records
%rowtype
Table Of, Table Of Methods – NEXT, PRIOR, FIRST, LAST, Table of Records

Module 6 - Using Cursors

Single FETCH vs Multiple FETCH
Looping through a Cursor
%rowtype, %found
Cursor For Loop
Cursor with Parameters
Bulk Collect

Module 7 - Error Handling

Predefined exceptions
Non predefined exceptions
SQLCODE, SQLERRM
User defined errors

Module 8 - Using Stored Procedures

Procedures with IN parameter
Using OUT parameters
Using IN/OUT parameters
Nesting procedures

Module 9 - Using Functions

Using RETURN
Error handling

Module 10 - PL/SQL Packages

Creating Package SPEC
Creating package BODY
Overloading – Procedures and Functions
Forward Declaration
Oracle Supplied Packages

Module 11 - PL/SQL Triggers

BEFORE Triggers
Using OLD/NEW
Using AFTER triggers
Using INSTEAD OF Triggers
Autonomous transactions

Module 12 - Dynamic SQL

Using PL/SQL Dynamic SQL

Module 13 - Dependencies

Handling database dependencies.

Contact Us

Latest courses

About Us

UpScale Analytics specializes in training solutions for organizations and individuals in the fields of data.

UpScale Analytics is also one of the largest platforms in the world for learning SQL by doing.

PL/SQL Programming

Audience Profile

Prerequisites

Contact Us

Contact Us

Latest courses

About Us

Popular Courses

Desicion Making with Basic SQL

PL/SQL Programming

Advanced Power BI

Quick Links