Oracle PL/SQL Training (ORA200)

This Oracle PL/SQL Training course is designed to provide a strong foundation for both individuals and teams looking to enhance their skills in Oracle database programming. Whether you are a complete beginner or someone who wishes to strengthen their understanding of PL/SQL concepts, this course offers a comprehensive look into the core features and functionalities of PL/SQL, equipping you with the necessary skills to effectively manage and manipulate database structures and data.

We start with PL/SQL Basics, introducing you to the fundamentals of PL/SQL and its unique characteristics. You'll learn about constructing blocks of code and effectively outputting information, along with managing variables and constants, understanding various data types, and embedding SQL within PL/SQL scripts. These initial lessons provide an essential groundwork for beginners by walking you through SELECT...INTO and RETURNING...INTO statements, and showcasing how PL/SQL features enhance your programming capabilities.

Moving forward, the course covers Subprograms, where you will explore the creation and management of procedures, including variable declarations, parameters, and various parameter modes such as IN, OUT, and IN OUT. You'll discover the benefits of using named notation and how SQL can be leveraged within subprograms, strengthening your understanding of functions and their integration into SQL queries.

The Conditional Processing module equips you with the ability to influence program flow using conditions and Booleans. You will master IF-ELSIF-ELSE conditions and the use of CASE statements and expressions, allowing you to implement sophisticated logic within your PL/SQL programs.

In the Exceptions section, you will gain insights into predefined and user-defined exceptions, learning how to handle errors effectively within your code. This includes understanding the EXCEPTION part of PL/SQL blocks, re-raising exceptions, and employing exceptions in subprograms, ensuring robust and error-tolerant database applications.

You will then learn to use of implicit and explicit cursors, the implementation of %ROWTYPE and cursor FOR loops, and how to utilize cursor parameters. These tools will enable you to efficiently manage result sets and navigate through data within your PL/SQL applications.

In the Packages lesson, you will explore package basics, the distinction between package specifications and bodies, and the advantages of using packages. The creation of meaningful packages, such as an employee package, and the practice of overloading subprograms will allow you to organize and modularize your code effectively. Additionally, you'll uncover auditing and validation procedures, and the use of package cursors.

Finally, the course addresses Triggers, explaining their purpose and components, and offering guidance on implementing validation and audit triggers, statement-level triggers, and compound triggers. You'll gain an understanding of the WHEN clause and potential implications of using triggers, further solidifying your ability to manage complex database tables and applications.

Upon completing this Oracle PL/SQL Training course, you and your teams will possess a robust skill set in PL/SQL, enabling you to construct, manipulate, and maintain dynamic and responsive database-driven applications. This course offers both a practical and theoretical understanding of PL/SQL that will pave the way for further exploration of advanced database programming techniques.

1. PL/SQL Basics
   1. What is PL/SQL?
   2. Blocks
   3. Outputting Information
   4. Variables and Constants
   5. Constants
   6. Data Types
   7. Naming Variables and Other Elements
   8. Embedding SQL in PL/SQL
   9. SELECT...INTO and RETURNING...INTO
   10. PL/SQL Features
2. Subprograms
   1. Introduction to Subprograms
   2. Procedures
   3. Variable Declarations
   4. Dropping a Procedure
   5. Parameters
   6. Parameters with Default Values
   7. Parameter Modes
   8. IN Mode
   9. OUT Mode
   10. IN OUT Mode
   11. Named Notation
   12. Using SQL in a Subprogram
   13. %TYPE
   14. Functions
   15. Using PL/SQL Functions in SQL Queries
3. Conditional Processing
   1. Conditions and Booleans
   2. IF-ELSIF-ELSE Conditions
   3. ELSIF
   4. BOOLEAN Values and Standard SQL
   5. The CASE Statement
   6. CASE Expressions
4. Exceptions
   1. Introduction to Exceptions
   2. Predefined Exceptions
   3. The EXCEPTION Part of the Block
   4. User-defined Exceptions
   5. User-defined Exceptions in Subprograms
   6. Re-raising Exceptions
   7. Naming Unnamed Predefined Exceptions
   8. WHILE Loops
   9. When to Use Exceptions
5. Cursors
   1. Implicit Cursors
   2. Explicit Cursors
   3. %ROWTYPE
   4. Explicit Cursor Use Case
   5. Cursor FOR LOOP
   6. Cursor Parameters
6. Packages
   1. Package Basics
   2. The Package Specification
   3. The Package Body
   4. Building an Employee Package
   5. Overloading Subprograms
   6. Auditing
   7. Validation Procedures
   8. Package Cursors
   9. Benefits of Packages
7. Triggers
   1. What are triggers?
   2. Trigger Parts
   3. Validation Triggers
   4. The WHEN Clause
   5. Audit Triggers
   6. Statement-level Triggers
   7. Compound Triggers
   8. Trigger Warning

Each student receives a comprehensive set of materials, including course notes and all class examples.