Course Code: 1173

Oracle Database 12c: PL/SQL Fundamentals I

Class Dates:
1/2/2020
12/30/2019
Length:
2 Days
Cost:
$1295
Class Time:
Technology:
Database
Delivery:

Overview

  • Course Overview
  • This Oracle Database: PL/SQL Fundamentals training introduces you to PL/SQL. It helps you understand the benefits of this powerful programming language, while teaching you real-world scenarios where these lessons can be applied.

    Learn To:
    - Describe the features and syntax of PL/SQL.
    - Design a PL/SQL anonymous block that executes 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.
    - Describe stored procedures and functions.
    - Apply declaring identifiers and trapping exceptions.
    - Create PL/SQL blocks of application code that can be shared by multiple forms, reports and data management applications.

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

Prerequisites

  • Required Prerequisites:

    Familiarity with data processing concepts and technique
    Familiarity with programming languages
    Oracle Database 12c: Introduction for Experienced SQL Users

    Suggested Prerequisites:

    Familiarity with data processing concepts and techniques
    Familiarity with programming languages

Course Details

  • 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 Expressions
  • 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