Course duration
- 5 days
Course Benefits
- Learn what PL/SQL is.
- Learn how to declare, name, assign and output variables in PL/SQL.
- Learn about data types in PL/SQL.
- Learn about the main features of PL/SQL.
- Learn how to write a simple block of PL/SQL code.
- Learn about subprograms in PL/SQL.
- Learn to create and call procedures.
- Learn to create and call functions.
- Understand BOOLEAN values and expressions.
- Learn to write IF conditions.
- Learn to use the CASE statement.
- Learn to handle exceptions in PL/SQL.
- Learn about how cursors works.
- Learn to use implicit cursors.
- Learn to use explicit cursors.
- Learn to use packages to group related PL/SQL objects.
- Learn to use triggers for validating data and auditing changes to the database.
Public expert-led online training from the convenience of your home, office or anywhere with an internet connection. Guaranteed to run .
Private classes are delivered for groups at your offices or a location of your choice.
Learn at your own pace with 24/7 access to an On-Demand course.
Course Outline
- PL/SQL Basics
- What is PL/SQL?
- Blocks
- Outputting Information
- Variables and Constants
- Constants
- Data Types
- Naming Variables and Other Elements
- Embedding SQL in PL/SQL
- SELECT...INTO and RETURNING...INTO
- PL/SQL Features
- Subprograms
- Introduction to Subprograms
- Procedures
- Variable Declarations
- Dropping a Procedure
- Parameters
- Parameters with Default Values
- Parameter Modes
- IN Mode
- OUT Mode
- IN OUT Mode
- Named Notation
- Using SQL in a Subprogram
- %TYPE
- Functions
- Using PL/SQL Functions in SQL Queries
- Conditional Processing
- Conditions and Booleans
- IF-ELSIF-ELSE Conditions
- ELSIF
- BOOLEAN Values and Standard SQL
- The CASE Statement
- CASE Expressions
- Exceptions
- Introduction to Exceptions
- Predefined Exceptions
- The EXCEPTION Part of the Block
- User-defined Exceptions
- User-defined Exceptions in Subprograms
- Re-raising Exceptions
- Naming Unnamed Predefined Exceptions
- WHILE Loops
- When to Use Exceptions
- Cursors
- Implicit Cursors
- Explicit Cursors
- %ROWTYPE
- Explicit Cursor Use Case
- Cursor FOR LOOP
- Cursor Parameters
- Packages
- Package Basics
- The Package Specification
- The Package Body
- Building an Employee Package
- Overloading Subprograms
- Auditing
- Validation Procedures
- Package Cursors
- Benefits of Packages
- Triggers
- What are triggers?
- Trigger Parts
- Validation Triggers
- The WHEN Clause
- Audit Triggers
- Statement-level Triggers
- Compound Triggers
- Trigger Warning
Class Materials
Each student will receive a comprehensive set of materials, including course notes and all the class examples.
Courses that can help you meet these prerequisites:
Instructor-led courses are offered via a live Web connection, at client sites throughout Europe, and at our Geneva Training Center.