Oracle PL/SQL Course Layout

Oracle 11g PL/SQL Fundamentals I

• Introduction
• Selecting the appropriate database tool
• Understanding PL/SQL
• PL/SQL blocks
• Working with SQL Plus
• DECLARE Clause objects
• PL/SQL available data types
• Declaring simple data types and assignments
• Declaring complex data types and assignments
• Declaring complex row types and assignments
• Declaring complex tables
• Declaring complex records
• Creating and declaring user-defined types
• What can be included in the BEGIN clause
• Working with CHAR and VARCHAR2 variables
• Handling string literals
• Working with numeric variables
• Working with date variables
• Assigning and using boolean variables
• Using comparison operators
• Using SQL functions
• SQL DML within PL/SQL
• Using SELECT
• Exceptions and embedded SQL
• Using sequences
• Logic control and branching
• Using GOTO
• Working with LOOPS
• Creating and using the indefinite loop
• Creating and using the conditional loop
• Using nested loops
• Using the If-Then-Else
• Using the CASE statement
• What are the types of exceptions
• Trapping exceptions
• Identify exception details
• Creating user-defined exceptions
• Using SQLCODE and SQLERRM objects
• Understand the implicit cursor
• Creating user-defined events
• Understanding the concept and purpose of explicit cursors
• Learn about cursor attributes
• Using the continue statement
• Working with updateable cursors
• Creating and using cursor parameters
• FOR…LOOP cursors
• Understanding nested blocks
• Difference between global vs local objects and exceptions
• Subprograms: how they differ from nested blocks
• Conclusion

Oracle 11g PL/SQL Fundamentals II

• Introduction
• Install Oracle XE
• SQL developer installation
• Workstation setup
• Introducing the database program unit
• Creating stored procedures and functions
• Understanding the parameter specification
• Executing procedures and functions
• Calling functions from within SQL
• Handling compilation errors
• Recompiling and dropping programs
• Data dictionary storage
• Managing dependencies
• Tracking dependencies
• Using the dependency tracking utility
• What is a package?
• Creating a package
• Package definition information
• Advanced programming techniques
• Using persistent global objects
• Including initialization logic
• Object oriented support within packages
• Package maintenance
• Learning to use cursor variables
• Weak cursor definitions
• REFCURSOR cursor definition
• Using cursor expression
• Using the DBMS OUTPUT() package
• Using the UTL FILE() package
• Understanding the database trigger
• Statement-Level triggers
• Row-Level triggers
• Row Level trigger example
• Working with the INSTEAD OF trigger
• Using triggers within an application
• Using the CALL statement
• Trigger maintenance tasks
• Work with multiple triggers for a single table
• Handling mutating table issues
• Using the compound trigger
• What Is a system event trigger
• Defining scope
• Identifying available system events
• Conclusion