
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
- Declaring variables
- Executable statements
- Global variables
- Substitution variables
- Interacting with the Database Engine
- Using nested blocks
- SELECT INTO statement
- DML with PL/SQL blocks
- Using Implicit Cursors
- Using IF, CASE, LOOP, WHILE and FOR
- exit and continue
- Nested loops
- Using records
- %rowtype
- Table Of, Table Of Methods – NEXT, PRIOR, FIRST, LAST, Table of Records
- Single FETCH vs Multiple FETCH
- Looping through a Cursor
- %rowtype, %found
- Cursor For Loop
- Cursor with Parameters
- Bulk Collect
- Predefined exceptions
- Non predefined exceptions
- SQLCODE, SQLERRM
- User defined errors
- Procedures with IN parameter
- Using OUT parameters
- Using IN/OUT parameters
- Nesting procedures
- Using RETURN
- Error handling
- Creating Package SPEC
- Creating package BODY
- Overloading – Procedures and Functions
- Forward Declaration
- Oracle Supplied Packages
- BEFORE Triggers
- Using OLD/NEW
- Using AFTER triggers
- Using INSTEAD OF Triggers
- Autonomous transactions
Using PL/SQL Dynamic SQL
Handling database dependencies.