PL/SQL in Oracle 19c

• Basic understanding of SQL
• Understanding of database schemas, tables, and relationships

Audience

• Database administrators
• Database developers
• Software developers
• Data analysts

PL/SQL (Procedural Language/Structured Query Language) in Oracle 19c is Oracle Corporation's procedural extension for SQL and the Oracle relational database. It provides the ability to perform procedural logic in addition to SQL's declarative logic, allowing for the development of complex and efficient database applications.

This instructor-led, live training (online or onsite) is aimed at intermediate-level developers and administrators who wish to have a solid PL/SQL programming foundation to develop efficient and robust database applications using Oracle 19c.

By the end of this training, participants will be able to:

• Gain a comprehensive understanding of what PL/SQL is and its advantages over standard SQL.
• Learn how to declare and use variables and different data types within PL/SQL blocks.
• Apply control structures like IF-THEN-ELSE, CASE statements, and loops to create robust PL/SQL programs.
• Understand and implement explicit and implicit cursors for data retrieval.
• Handle exceptions effectively using predefined and user-defined exceptions.
• Develop and manage triggers to automate and enforce business rules.
• Create and use PL/SQL packages to encapsulate and modularize code for reuse and maintainability.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Introduction to PL/SQL

• What is PL/SQL?
• Advantages of using PL/SQL

Basic Structure of a PL/SQL Block

• Anonymous blocks
• Named blocks

Variables and Data Types

• Declaration of variables
• Syntax and rules
• Data types in PL/SQL
• Using variables in PL/SQL blocks

Control Structures

• IF-THEN-ELSE
• CASE statements
• FOR loops
• WHILE loops

Stored Procedures and Functions

• Creating and running stored procedures
• Syntax and examples
• Input and output parameters
• Creating and executing functions in PL/SQL
• Syntax and examples
• Usage in SQL statements

Cursors

• Explicit cursors
• Implicit cursors
• Fetching data
• Cursor attributes

Exception Handling

• Predefined exceptions
• User-defined exceptions
• Using TRY-CATCH blocks

Transaction Control

• Transaction control in PL/SQL
• Using nested blocks and transactions
• Managing nested transactions
• Creating and using savepoints

Advanced Cursor Techniques

• Strong vs. weak REF CURSOR
• Usage scenarios

Triggers

• Creating triggers
• Types of triggers
• Using triggers for auditing

Packages in PL/SQL

• Creation
• Specification
• Implementation
• Execution
• Invocation
• Real-world application
• Code walkthrough

Summary and Next Steps