Oracle Fundamentals and PL SQL is designed for beginners in Database programming world. This training will cover the basic concept to writing PL/SQL Code to writing professional PL/SQL code for complex business problems. It also explains about writing optimized PL/SQL Code, cover many real time case studies in various scenarios. Implementing the PL/SQL code for business rules, Data Quality Checks, working in enterprise Data Warehousing and data transformation rules.
Course Contents
Day 1
Brief Introduction to PL/SQL
What is PL/SQL
SQL Versus PL/SQL
PL/SQL Architecture
Advantages of PL/SQL
Working with different type of Data types
Date, Time and Interval Types
Boolean Types
Characters, String and National Character Types
Number Types
LOB Types
Composite data types
Exception Handler
What is Exception Handling
How to Handle Exception
Structure of Exception Handling
Types of Exception Handling
SAVE Exception in Bulk Collect
PRAGMA init Exception
Cursors in PL/SQL
Why cursor is used
Declaring Cursors
Different Types of cursor
Cursor Attributes
Sending parameter to cursor
Different ways of using cursor
Cursor Variable (Reference Cursor)
Advantage of using cursor variable over normal cursor
Passing Cursor variable as parameter
Restriction on cursor Variables
Day 2
Working with Procedures, Functions and Packages
Basic of stored procedure
Basics of functions
Basics of packages
Advantage of using procedures , function and packages
Passing cursor variables to procedures , function and packages
Recompiling functions and stored procedures
Forward declaration
Declaring and using persistent global variables in packages
Creating table Function
Function overloading
Restrict Reference Pragma
Pinning packages in the SGA with dbms_shared_pool.keep
Overview of some advanced PL/SQL topics
Table Function
Pipelined table function
Error log tables
Hints in PL/SQL
Writing PL/SQL blocks using tools
PL/SQL Developers
Toad
SQL Plus
SQL Developers etc.
Control Structures
IF-THEN-ELSE Statement
Case Statement
GOTO Statement
NULL Statement
Day 3
Working with Objects & Collection
Describing Object
Constructor method on objects
Nested Table
VARRAY
Associative Array
Collection Methods
Manipulate Collection
Distinguish between the different types of collections and when to use them
Dynamic SQL and PL/SQL
When to use
Declaring dynamic SQL and PL/SQL by using DBMS_SQL package
Declaring dynamic SQL and PL/SQL by using Execute Immediate
Using Dynamic SQL in FORALL statement
Dynamic statement using binding variables
Advantage of using Dynamic SQL and PL/SQL
Debugging and Error tracking of PL/SQL code
Using Error log message tables
Using some built in packages
Using tools like PL/SQL developers
Tuning of PL/SQL program
Language fundamentals
identifiers
literals
Executable and Non Executable statement
Anonymous Block Structure
Nested Block
Named Block
PRAGMA
Scope & Visibility of variables
Day 4
Different types of loops
Simple Loop
WHILE- Loop
FOR- Loop
Labels in Loop
Scope Rules
Exit Statement
Working with Records
Declaring Records
Benefit of using record
Passing Values To and From Record
Comparing two records
Bulk processing of data using Bulk Collect
What is Bulk collect operation
How does it impact performance
Declaring BULK COLLECT operation with simple select statement
BULK COLLECT with cursors
BULK COLLECT using FORALL statement
Catching exception in Bulk collect using SAVE EXCEPTION
Working with Triggers.
Describe Triggers
Identify the Trigger Event Types and Body
Business Application Scenarios for Implementing Triggers
Create DML Triggers using the CREATE TRIGGER Statement
Identify the Trigger Event Types, Body, and Firing (Timing)
Differences between Statement Level Triggers and Row Level Triggers
Create Instead of and Disabled Triggers
How to Manage, Test and Remove Triggers?
Case Studies
Implement Data Transformation Using PL/SQL code Part I
Implement Data Transformation Using PL/SQL code Part II
Implement Data Transformation Using PL/SQL code Part III
Enforce Data Quality Check with PL/SQL Code I
Enforce Data Quality Check with PL/SQL Code II
Apps2Fusion Articles 