Oracle Database: PL/SQL Fundamentals Ed 2

Suggested Prerequisite

• Familiarity with data processing concepts and technique
• Familiarity with programming languages
• Familiarity with programming languages
• Familiarity with data processing concepts and techniques

Required Prerequisite

• Oracle Database 12c: Introduction for Experienced SQL Users

Audience

• Database Administrators
• System Analysts
• Forms Developer
• Technical Consultant
• Portal Developer
• PL/SQL Developer

Course Topics

1. Introduction

• Course Objectives, Course Agenda and Class Account Information
• PL/SQL Development Environments
• Appendices Used in this Course
• The Human Resources (HR) Schema

2. Introduction to PL/SQL

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

3. Declaring PL/SQL Variables

• Declaring and initializing variables
• Identifying the benefits of using the %TYPE attribute
• Using bind variables
• Various data types
• Identifying valid and invalid identifiers

4. Writing Anonymous PL/SQL blocks

• Operators in PL/SQL
• Referencing an Identifier Value in a Nested Block
• Using Nested Blocks as Statements
• Using Sequences in PL/SQL Expressions
• Using SQL Functions in PL/SQL
• Data Type Conversion
• Qualifying an Identifier with a Label
• Lexical Units in a PL/SQL Block

5. Using SQL Statements within a PL/SQL Block

• Using SQL Statements in PL/SQL
• Manipulating Data on the Server Using PL/SQL
• Using SQL Cursor Attributes to Obtain Feedback on DML
• Saving and Discarding Transactions
• Using Naming Conventions in DML Statements and data retrieval
• Retrieving Data in PL/SQL with the SELECT statement
• SQL Cursors

6. Writing Control Structures

• Building Boolean Conditions with Logical Operators
• Controlling PL/SQL Flow of Execution
• Using Iterative Control with Loop Statements
• Using IF and CASE Statements for conditional processing
• Handling Nulls

7. Working with Composite Data Types

• Using Associative arrays
• Inserting and Updating PL/SQL Records
• Composite Data Types of PL/SQL Records and Tables
• Declaring and Using VArrays
• Using PL/SQL Records
• Using INDEX BY Tables

8. Using Explicit Cursors

• Cursor FOR Loops Using Subqueries
• Explicit Cursor Attributes
• Using Explicit Cursors to Process Rows
• Explicit Cursor Operations
• Cursors and Records
• Cursors
• Controlling Explicit Cursors
• %NOTFOUND and %ROWCOUNT Attributes

9. Handling Exceptions

• Functions that Return Information on Exceptions
• Trapping User-Defined Exceptions
• Predefined Exceptions
• Usage of PRAGMA keyword
• Trapping Predefined and Non-predefined Oracle Server Errors
• Handling Exceptions with PL/SQL
• Concept of Exception
• Propagating Exceptions

10. Creating Stored Procedures and Functions

• Creating a Simple Procedure with an IN Parameter
• Understanding the Header Area of a Stored Procedure and Function
• Executing a Procedure and a Function
• Overview of Stored Procedures and Functions
• CREATE OR REPLACE PROCEDURE | FUNCTION
• Creating Simple Procedures and Functions
• Differentiating between anonymous blocks and subprograms