What you'll Learn

[wpseo_breadcrumb]

Oracle PL/SQL Advanced

Oracle PL/SQL Advanced is designed for those who wish to learn the basics to advanced PL/SQL concepts at expert level. In this course, participants learn how to identify the programming extensions that PL/SQL provides to SQL and how to write PL/SQL code to interface with the database. Designing PL/SQL anonymous blocks that execute efficiently, using PL/SQL programming constructs and conditional control statements, handling run-time errors, and creating stored procedures and functions are also covered.

Duration :  40 Hrs
Prerequisites: Oracle Database: SQL Fundamentals

Oracle PL/SQL Advanced​ Course Content

  • 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
  • Declaring PL/SQL Variables
    • Recognize valid and invalid identifiers
    • List the uses of variables, declare and initialize variables, use bind variables
    • List and describe various data types using the %TYPE attribute
  • Writing Executable Statements
    • Identify lexical units in a PL/SQL block
    • Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions
    • 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 indentation
  • Interacting with the Oracle Database Server
    • Create PL/SQL executable blocks using DML and transaction control statements
    • Make use of the INTO clause to hold the values returned by a SQL statement
  • Writing Control Structures
    • Identify the uses and types of control structures (IF, CASE statements and expressions)
    • Construct and identify loop statements
    • Apply guidelines when using conditional control structures
  • Working with Composite Data Types
    • Create user-defined PL/SQL records
    • Create a record with the %ROWTYPE attribute
    • Create an INDEX BY table and INDEX BY table of records
    • Describe the differences among records, tables, and tables of records
  • Using Explicit Cursors
    • Distinguish between usage of implicit and explicit cursors, use SQL cursor attributes
    • 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 and reference the current row with the WHERE CURRENT OF clause
  • Handling Exceptions
    • Define PL/SQL exceptions
    • Recognize unhandled exceptions
    • Handle different types of exceptions (pre-defined exceptions, non-predefined exceptions and user-defined exceptions)
    • Propagate exceptions in nested blocks and call applications
  • 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
  • Creating Procedures
    • Differentiate between anonymous blocks and subprograms, use a modularized and layered subprogram design, and identify the benefits of subprograms
    • Create a simple procedure and invoke it from an anonymous block
    • Work with procedures
    • Handle exceptions in procedures, remove a procedure, and display a procedure’s information
Enquiry - Course Pages

Need more details?

Please submit below inquiry form

(974)

You may also interested in