Course Outline
Introduction to PL/SQL
- Defining PL/SQL and its role in Oracle
- Key benefits of adopting PL/SQL over standard SQL
Foundational PL/SQL Block Architecture
- Working with anonymous blocks
- Structuring named blocks
Variables and Data Type Management
- Rules for variable declaration
- Adhering to syntax conventions
- Overview of supported PL/SQL data types
- Integrating variables into PL/SQL logic
Control Flow Mechanisms
- Conditional logic using IF-THEN-ELSE
- Multi-branch decisions with CASE statements
- Iterative processing with FOR loops
- Conditional iteration with WHILE loops
Developing Stored Procedures and Functions
- Authoring and executing stored procedures
- Applying correct syntax and practical examples
- Managing input and output parameters
- Creating and invoking functions within PL/SQL
- Illustrative syntax and code samples
- Embedding functions in SQL queries
Cursor Implementation and Management
- Defining explicit cursors
- Leveraging implicit cursors
- Techniques for data fetching
- Utilizing cursor attributes for diagnostics
Robust Exception Handling
- Managing system-defined exceptions
- Creating custom user-defined exceptions
- Implementing error handling blocks
Transaction Management Strategies
- Controlling transactions within PL/SQL
- Utilizing nested blocks for isolation
- Managing complex nested transactions
- Implementing savepoints for partial rollbacks
Advanced Cursor Patterns
- Distinguishing between strong and weak REF CURSORS
- Identifying appropriate usage scenarios
Trigger Development
- Process for creating triggers
- Categorizing different trigger types
- Applying triggers for audit logging
Modularization with PL/SQL Packages
- Package creation workflow
- Writing package specifications
- Implementing package bodies
- Execution mechanics
- Procedures for invocation
- Application in real-world scenarios
- Step-by-step code analysis
Conclusions and Future Pathways
Requirements
- Fundamental knowledge of SQL syntax and concepts
- Proficiency in understanding database schemas, table structures, and inter-table relationships
Target Audience
- Database Administrators (DBAs)
- Database Developers
- Software Engineers
- Data Analysts
Testimonials (5)
The PL/SQL session basics. I used PL/SQL but never got the basics of it and this course helped me a lot understand PL/SQL queries.
Stefan-Andrei
Course - PL/SQL in Oracle 19c
Very balanced training and practice oriented, very glad that I had the virtual machine and was able to work in parallel with trainer the exercises
Robert
Course - PL/SQL in Oracle 19c
Trainer was patient with us and he made sure we understood each section before moving forward.
RONIKA - Igaye Projects (PTY) LTD
Course - PL/SQL in Oracle 19c
The way he explained everything
Alexandra G - DB Global Technology
Course - PL/SQL in Oracle 19c
The exercises were explained very good.
