Introduction to PL/SQL

  • Overview of PL/SQL
  • Identify the benefits of PL/SQL Subprograms
  • Overview of the types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • How to generate output from a PL/SQL Block?

Declare PL/SQL Identifiers

  • List the different Types of Identifiers in a PL/SQL subprogram
  • Usage of the Declarative Section to Define Identifiers
  • Use variables to store data
  • Identify Scalar Data Types
  • The %TYPE Attribute
  • What are Bind Variables?
  • Sequences in PL/SQL Expressions

Write Executable Statements

  • Describe Basic PL/SQL Block Syntax Guidelines
  • Learn to Comment the Code
  • Deployment of SQL Functions in PL/SQL
  • How to convert Data Types?
  • Describe Nested Blocks
  • Identify the Operators in PL/SQL

Interaction with the Oracle Server

  • Invoke SELECT Statements in PL/SQL
  • Retrieve Data in PL/SQL
  • SQL Cursor concept
  • Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
  • Data Manipulation in the Server using PL/SQL
  • Understand the SQL Cursor concept
  • Use SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions

Control Structures

  • Conditional processing using IF Statements
  • Conditional processing using CASE Statements
  • Describe simple Loop Statement
  • Describe While Loop Statement
  • Describe For Loop Statement

Composite Data Types

  • Use PL/SQL Records
  • The %ROWTYPE Attribute

Explicit Cursors

  • What are Explicit Cursors?
  • Declare the Cursor
  • Open the Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • Cursor FOR loop
  • The %NOTFOUND and %ROWCOUNT Attributes
  • Describe the FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

  • Understand Exceptions
  • Handle Exceptions with PL/SQL
  • Trap Predefined Oracle Server Errors
  • Trap Non-Predefined Oracle Server Errors
  • Trap User-Defined Exceptions
  • Propagate Exceptions
  • RAISE_APPLICATION_ERROR Procedure

Stored Procedures and Functions

  • Create, Call, and Remove Stored Procedures
  • Implement Procedures Parameters and Parameters Modes
  • View Procedure Information
  • Create, Call, and Remove a Stored Function
  • Invoke User-Defined Functions in SQL Statements
  • How to debug Functions and Procedures?

Packages

  • Listing the advantages of Packages
  • Describe Packages
  • What are the components of a Package?
  • Develop a Package
  • How to enable visibility of a Package’s Components?
  • Create the Package Specification and Body
  • Invoke the Package Constructs
  • View the PL/SQL Source Code using the Data Dictionary

Implement Oracle-Supplied Packages in Application Development

  • What are Oracle-Supplied Packages?
  • Examples of some of the Oracle-Supplied Packages
  • How does the DBMS_OUTPUT Package work?
  • Use the UTL_FILE Package to Interact with Operating System Files

Dynamic SQL

  • The Execution Flow of SQL
  • What is Dynamic SQL?
  • Declare Cursor Variables
  • Dynamically Executing a PL/SQL Block
  • Configure Native Dynamic SQL to Compile PL/SQL Code
  • How to invoke DBMS_SQL Package?
  • Implement DBMS_SQL with a Parameterized DML Statement
  • Dynamic SQL Functional Completeness

Triggers

  • Describe Triggers
  • Identify the Trigger Event Types and Body
  • Business Application Scenarios for Implementing Triggers
  • Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
  • Identify the Trigger Event Types, Body, and Firing (Timing)
  • Differences between Statement Level Triggers and Row Level Triggers
  • Create Instead of and Disabled Triggers
  • How to Manage, Test and Remove Triggers?