PL SQL Course Details

Basics of PL/SQL

• PL/SQL architecture
• PL/SQL and SQL*Plus
• PL/SQL Basics, Variables, Constants, data types & error handling
• PL/SQL wrapper utility

PL/SQL structures

• Simple blocks
• Control structures
• PL/SQL records
• Recognizing the Basic PL/SQL Block and Its Sections
• Describing the Significance of Variables in PL/SQL
• Distinguishing Between PL/SQL and Non-PL/SQL Variables
• Declaring Variables and Constants
• Executing a PL/SQL Block

Error checking – exception handling

• Defining exceptions
• Using the when others clause
• Ensuring complete error checking
• Passing error messages to calling routine

Boolean logic in PL/SQL

• Identifying the Uses and Types of Control Structures
• Constructing an IF Statement
• Constructing and Identifying Different Loop Statements
• Controlling Block Flow Using Nested Loops and Labels
• Using Logic Tables
• If-then-else structure
• Testing for numbers characters and Booleans

Cursors in PL/SQL

• Cursor basics
• Using a cursor for a multi-row SQL query

Iteration in PL/SQL

• For loop
• While loop

PL/SQL tables

• Defining PL/SQL tables
• Reasons to use PL/SQL tables
• Populating a PL/SQL table
• Retrieving from a PL/SQL table

Dynamic SQL in PL/SQL

• Introduction to the dbms_sql package
• Creating a dynamic SQL statement

Nested blocks in PL/SQL

• Introduction to the dbms_sql package
• Creating a dynamic SQL statement

Triggers in PL/SQL

• Triggers and database events
• Defining a trigger
• Timing a trigger
• Enabling and disabling a trigger

Stored procedures, functions and packages

• Basics of stored procedures
• Basics of functions
• Basics of packages
• Defining stored procedures & functions
• Function and stored procedures prototypes
• Passing arguments to functions and stored procedures
• Recompiling functions and stored procedures
• Pinning packages in the SGA with dbms_shared_pool.keep
• Package forward declaration
• Package dependency
• Package overloading
• Listing package information

Bulking in PL/SQL

• Bulk queries
• Bulk DML (forall statement)
• Using cursor attributes
• Analyzing impact of bulk operations