Course Content

• 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