Oracle PL SQL Course

ORACLE

ORACLE – PL SQL COURSE CONTENT

1. 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
2. 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
3. Error checking ? exception handling
   •  Defining exceptions
   •  Using the when others clause
   •  Ensuring complete error checking
   • Passing error messages to calling routine
4. 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
5. Cursors in PL/SQL
   • Cursor basics
   • Using a cursor for a multi-row SQL query
6. Iteration in PL/SQL
   •  For loop
   •  While loop
7. PL/SQL tables
   •  Defining PL/SQL tables
   •  Reasons to use PL/SQL tables
   •  Populating a PL/SQL table
   •  Retrieving from a PL/SQL table
8. Dynamic SQL in PL/SQL
   •  Introduction to the dbms_sql package
   • Creating a dynamic SQL statement
9. Nested blocks in PL/SQL
   •  Introduction to the dbms_sql package
   •  Creating a dynamic SQL statement
10. Triggers in PL/SQL
    •  Triggers and database events
    •  Defining a trigger
    •  Timing a trigger
    •  Enabling and disabling a trigger
11. 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
12. Bulking in PL/SQL
    • Bulk queries
    •  Bulk DML (forall statement)
    •  Using cursor attributes
    •  Analyzing impact of bulk operations