Oracle PL-SQL

PL/SQL is a combination of SQL along with the procedural features of programming languages. PL/SQL stands for Procedural Language extension of SQL. When you want to write complex conditional logic or specialized loops over the rows in a data set, you may cannot able to do so. Languages like PL/SQL allow you to do this.

We will help you to learn PL/SQL from scratch to pursue a career as a PL/SQL developer, database administrators & architects, data analysts.

Course Content:

Introduction to Programming Languages

• Introduction & Advantages of PL/SQL
• PL/SQL Architecture
• PL/SQL Datatypes
• Variable and Constants
• Composite Datatypes
• Using Built_in Functions

Conditional and Unconditional Statements

• Simple if, if-else, Nested if-else, if-else Ladder
• Selection Case, Simple Case, GOTO Label and EXIT
• Iterations in PL/SQL
• Basic LOOP, WHILE, FOR, NESTED LOOPS
• SQL within PL/SQL

Cursor Management in PL/SQL

• Implicit Cursors
• Explicit Cursors
• Cursor Attributes
• Cursor with Parameters
• Cursors with LOOPs Nested Cursors
• Cursors with Sub Queries
• Ref. Cursors

Record in PL/SQL

• Table types
• Cursor type
• User define

Procedures in PL/SQL

• STORED PROCEDURES
• PROCEDURE with Parameters (IN, OUT and IN OUT)
• POSITIONAL Notation and NAMED Notation
• Procedure with Cursors
• Dropping a Procedure

Functions in PL/SQL

• Difference between Procedures and Functions
• User-Defined Functions
• Nested Functions
• Using the stored function in SQL statements

Packages in PL/SQL

• Creating PACKAGE
• Specification and PACKAGE Body
• Private and Public Objects in PACKAGE

Exceptions in PL/SQL

• Exception: what & why?
• Types of exceptions
• RAISE_APPLICATION_ERROR
• SQL Error Code Values
• SQLERRM & SQLCODE

Database Triggers in PL/SQL

• Triggers: what & why?
• Types of Triggers
• Example of after, before, instead of triggers
• DDL Triggers
• Trigger Auditing

File Input/Output

• PL/SQL file I/O (Input/Output)
• Using UTL_FILE Package