Oracle Database 12c/18c/19c PL/SQL Fundamentals

Day 1

• Introduction
• Lesson 1 : Introduction to PL/SQL
• Lesson 2 : Declaring PL/SQL Variables
• Lesson 3 : Writing Executable Statements
• Lesson 4 : Interacting with the Oracle Database Server
• Lesson 5 : Writing Control Structures

Day 2

• Lesson 6 : Working with Composite Data Types
• Lesson 7 : Using Explicit Cursors
• Lesson 8 : Handling Exceptions
• Lesson 9 : Creating Stored Procedures and Functions

Lesson 1 : Introduction to PL/SQL

• Explain the need for PL/SQL
• Explain the benefits of PL/SQL
• Identify the different types of PL/SQL blocks
• Output messages in PL/SQL

Lesson 2 : Declaring PL/SQL Variables

• Recognize valid and invalid identifiers
• List the uses of variables
• Declare and initialize variables
• List and describe various data types
• Identify the benefits of using the %TYPE attribute
• Declare, use, and print bind variables

Lesson 3 : Writing Executable Statements

• Identify lexical units in a PL/SQL block
• Use built-in SQL functions in PL/SQL
• Describe when implicit conversions take place and when explicit conversions have to be dealt with
• Write nested blocks and qualify variables with labels
• Write readable code with appropriate indentations
• Use sequences in PL/SQL expressions

Lesson 4 : Interacting with the Oracle Server

• Determine which SQL statements can be directly included in a PL/SQL executable block
• Manipulate data with DML statements in PL/SQL
• Use transaction control statements in PL/SQL
• Make use of the INTO clause to hold the values returned by a SQL statement
• Differentiate between implicit cursors and explicit cursors
• Use SQL cursor attributes

Lesson 5 : Writing Control Structures

• Identify the uses and types of control structures
• Construct an IF statement
• Use CASE statements and CASE expressions
• Construct and identify different loop statements
• Use guidelines when using conditional control structures

Lesson 6 : Working with Composite Data Types

• Create user-defined PL/SQL records
• Create a record with the %ROWTYPE attribute
• Create an INDEX BY table
• Create an INDEX BY table of records
• Describe the differences among records, tables, and tables of records

Lesson 7 : Using Explicit Cursors

• Distinguish between implicit and explicit cursors
• Discuss the reasons for using explicit cursors
• Declare and control explicit cursors
• Use simple loops and cursor FOR loops to fetch data
• Declare and use cursors with parameters
• Lock rows with the FOR UPDATE clause
• Reference the current row with the WHERE CURRENT OF clause

Lesson 8 : 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

Lesson 9 : Creating 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