Oracle 19c PL/SQL Fundamentals

Course Overview

1. Getting Started

Quick Overview of Oracle Database 19c
Available PL/SQL development environments
Oracle documentation and additional resources

2. Introduction to PL/SQL

Understanding the benefits and structure of PL/SQL
Understanding PL/SQL blocks
Generating output messages in PL/SQL

3. Declaring PL/SQL Variables

Introducing variables
Handling variables of different types
Using the %TYPE attribute and composite data types
Using bind variables

4. Writing Executable Statements

Writing executable statements in a PL/SQL block
Writing nested blocks
Using operators and developing readable code

5. Using SQL Statements Within a PL/SQL Block

SQL statements in PL/SQL blocks
Manipulating data with PL/SQL
Introducing SQL cursors

6. Writing Control Structures

Using IF statements
Using CASE statements and CASE expressions
Constructing and identifying loop statements

7. Working with Composite Data Types

Understanding composite data types
Using PL/SQL records
Manipulating data with PL/SQL records
Advantages of the %ROWTYPE attribute
Using PL/SQL collections
Examining associative arrays
Introducing nested tables
Introducing VARRAY

8. Using Explicit Cursors

What are explicit cursors?
Using explicit cursors
Using cursors with parameters
Locking rows and referencing the current row

9. Handling Exceptions

Define PL/SQL exceptions
Recognize unhandled exceptions
List and use different types of PL/SQL exception handlers
Trap unanticipated errors
Describe the effect of exception propagation in nested blocks
Customize PL/SQL exception messages

10. Introducing Stored Procedures and Functions

Differentiate between anonymous blocks and subprograms
Create a simple procedure and invoke it from an anonymous block
Create a simple function
Create a simple function that accepts a parameter
Differentiate between procedures and functions

11. Using SQL*Plus

Log in to SQL*Plus
Edit SQL commands
Format the output by using SQL*Plus commands
Interact with script files

12. Using SQL Developer

Identify the menu items of Oracle SQL Developer
Create a database connection
Manage database objects
Use SQL Worksheet
Save and run SQL scripts
Create and save reports

13. Commonly Used SQL Commands

Execute a basic SELECT statement
Create, alter, and drop a table
Insert, update, and delete rows
Perform join operations

14. REF Cursors

Cursor Variables
Defining REF CURSOR Types
Using the OPEN-FOR, FETCH, and CLOSE Statements

Prerequisites

This course is geared for anyone needing to interface with an Oracle database such as end users, business analysts, application developers and database administrators / DBAs. Attendees should have incoming basic SQL experience, as well as an understanding of databases (required to manipulate it with PL/SQL).

 

Take Before: Attendees should have skills equivalent to those listed in the courses below, or should have attended one of these as a prerequisite: 

Introduction to SQL Programming Basics
Introduction to Writing SQL Queries

Course Comments

NOTE: This training is NOT Official Oracle University training. This is independent, adjustable content that aligns with current topics, skills and tools that participants need to excel in these areas.