OSQLFU: Oracle Database 12c R2: PL/SQL Fundamentals

Oracle Database: Introduction to SQL; Familiarity with data processing concepts and technique;Familiarity with programming languages

• Describe the features and syntax of PL/SQL
• Design PL/SQL anonymous block that execute efficiently
• Write PL/SQL code to interface with the database
• Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
• Handle runtime errors
• Create and execute simple stored procedures and functions

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

Introduction

• Course Objectives, Course Agenda and Class Account Information
• Describe the Human Resources (HR) Schema
• Identify the Appendices Used in this Course
• Introduction to PL/SQL Development Environments
• Oracle Database 12c SQL Documentation and Additional Resources

Introduction to PL/SQL

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

Declaring PL/SQL Variables

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

Writing Executable Statements

• List Lexical Units in a PL/SQL Block
• Use SQL Functions in PL/SQL
• Use Data Type Conversion
• Use Nested Blocks as Statements
• Reference an Identifier Value in a Nested Block
• Qualify an Identifier with a Label
• Use Operators in PL/SQL
• Using Sequences in PL/SQL Expression

Using SQL Statements within a PL/SQL Block

• Identify and use the SQL Statements in PL/SQL
• Retrieve Data in PL/SQL with the SELECT statement
• Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
• Manipulate Data in the Server Using PL/SQL
• The SQL Cursor concept
• Use SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions

Writing Control Structures

• Control PL/SQL Flow of Execution
• Conditional processing Using IF and CASE Statements
• Handle Nulls to Avoid Common Mistakes
• Build Boolean Conditions with Logical Operators
• Use Iterative Control with Looping Statements

Working with Composite Data Types

• Learn the Composite Data Types of PL/SQL Records and Tables
• Use PL/SQL Records to Hold Multiple Values of Different Type
• Inserting and Updating with PL/SQL Records
• Use INDEX BY Tables to Hold Multiple Values of the Same Data Type

Using Explicit Cursors

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

Handling Exceptions

• Define Exception
• Handling Exceptions with PL/SQL
• Predefined Exceptions
• Trapping Predefined and Non-predefined Oracle Server Errors
• Functions that Return Information on Encountered Exceptions
• Trapping User-Defined Exceptions
• Propagate Exceptions
• Basic RAISE Statement

Creating Stored Procedures and Functions

• Overview of Stored Procedures and Functions
• Differentiate between anonymous blocks and subprograms
• Show the CREATE OR REPLACE PROCEDURE | FUNCTION
• Understand the Header Area of a Stored Procedure and Function
• Create Simple Procedures and Functions
• Create a Simple Procedure with an IN Parameter
• Execute a Procedure and a Function