• What is PL/SQL
• PL/SQL Environment
• Benefits of PL/SQL

• Identify the different types of variables
• Describe variables and their uses
• Declare PL/SQL variables
• Describe basic syntax
• Use Literals in PL/SQL
• Using SQL functions in PL/SQL

• Identify the SQL Statements you can use n PL/SQL
• Retrieve Data in PL/SQL with the SELECT Statement
• Manipulate Data in the Server Using PL/SQL
• Describe and use the SQL Cursor

• Control PL/SQL Flow of Execution
• Conditional processing Using IF Statements
• Conditional Processing CASE Statements 
• Use Looping Statements

• Learn the Composite Data Types of PL/SQL Records and Associative Arrays 
• Use PL/SQL Records to Hold Multiple Values of Different Types
• Inserting and Updating with PL/SQL Records
• Use Associative Arrays to hold multiple values of the same datatype

• Cursor FOR Loops Using Sub-queries
• Cursors using Parameters
• Use the FOR UPDATE Clause to Lock Rows
• Use the WHERE CURRENT Clause to Reference the Current Row
• Use Explicit Cursors to Process Rows
• Explicit Cursor Attributes
• Cursors and Records

• Handling Exceptions with PL/SQL
• Predefined Exceptions
• Trapping Non-predefined Oracle Server Errors
• Functions that Return Information on Encountered Exceptions
• Trapping User-Defined Exceptions
• Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications

• Creating, Calling, and Removing Stored Procedures Using the CREATE Command
• Using Procedures Parameters and Parameters Modes

• Creating, Calling, and Removing a Stored Function Using the CREATE Command Identifying the Advantages of Using • Stored Functions in SQL Statements
• Identify the steps to create a stored function
• Using User-Defined Functions in SQL Statements
• Restrictions When Calling Functions from SQL statements

• Listing the Advantages of Packages
• Describing Packages
• The Components of a Package
• Developing a Package
• The Visibility of a Package’s Components
• Creating the Package Specification and Body Using the SQL CREATE Statement Invoking the Package Constructs

• Overloading Subprograms in PL/SQL
• Using the STANDARD Package
• Using Forward Declarations to Solve Illegal Procedure Reference
• Using Package Functions in SQL and Restrictions
• Persistent State of Packages
• Persistent State of a Package Cursor
• Controlling Side Effects of PL/SQL Subprograms
• Using PL/SQL Tables of Records in Packages

• Working With Triggers
• Identifying the Trigger Event Types and Body
• Business Application Scenarios for Implementing Triggers
• Creating DML Triggers Using the CREATE TRIGGER Statement
• Identifying the Trigger Event Types, Body, and Firing (Timing)
• Statement Level Triggers Versus Row Level Triggers
• Creating Instead of and Disabled Triggers
• Managing, Testing, and Removing Triggers

• Working With Compound Triggers
• Identifying the Timing-Point Sections of a Table Compound Trigger
• Compound Trigger Structure for Tables and Views
• Creating Triggers on DDL Statements
• Creating Database-Event and System-Events Triggers

• Overview of collections
• Use Associative arrays
• Use Nested tables
• Use Varrays
• Write PL/SQL programs that use collections
• Use Collections effectively
• Creating Table Functions

• The Execution Flow of SQL
• Describe Dynamic SQL
• Declaring Cursor Variables
• Dynamically Executing a PL/SQL Block
• Using Native Dynamic SQL to Compile PL/SQL Code
• Using DBMS_SQL Package
• Using DBMS_SQL with a Parameterized DML Statement
• Dynamic SQL Functional Completeness

• Standardizing Constants and Exceptions
• Using Local Subprograms
• Using Autonomous Transactions
• Using the NOCOPY Compiler Hint
• Using the PARALLEL_ENABLE Hint
• Using the DETERMINISTIC Clause with Functions

Short Description

• Overview of Analytic Functions
• Ranking Functions
• Windowing Clause/Sliding Windows
• LAG and LEAD Functions
• LISTAGG Function
• FIRST_VALUE Function
• LAST_VALUE Function

• Overview of statement processing
• Explain Plan
• Using DBMS_XPLAN
• Running SQL Trace and tkprof
• Overview of reading execution plans and tkprof output

• Understand and influence the compiler
• Tune PL/SQL code
• Enable intra unit inlining

• Describe result caching
• Using PL/SQL function cache