Programming with PL/SQL 2 day training course from $885.00 USD

We are currently only offering Programming with PL/SQL as a live instructor-led online course
Remote live public course $885.00 USD Remotely accessed over the internet, max 6 delegates - click here for details
Remote live one on one course $1,185.00 USD Remotely accessed over the internet, no other delegates - click here for details

Synopsis

This PL/SQL training course will give you an introduction to Oracle Corporation’s proprietary procedural extension to the SQL database language, PL/SQL. PL/SQL (Procedural Language/Structured Query Language) is embedded in the Oracle Database, and allows the creation of powerful programming blocks - procedures and functions - which interact with SQL to dramatically increase the functionality of your Oracle database.

Duration

2 days

Cost

£695 plus VAT

See options at top of the page

Prerequisites

A basic level of SQL knowledge, and some understanding of relational database (RDBMS) concepts is assumed. For students who do not have experience in these areas the Introduction to SQL class is recommended.

Detailed course contents

Introduction: PL/SQL

What is PL/SQL?

Advantages of PL/SQL

Integration with SQL

Access to built-in packages

PL/SQL blocks

PL/SQL basics

A Simple PL/SQL Block

Displaying the User Messages on the Screen

Adding comments

Variables

Variables in PL/SQL

Declaring variables

Variable name rules

Assigning default values to variables

Assigning values to variables

The := assignment operator

Literals

Constants

The %TYPE attribute

Record variables

Declaring a record type

Referring to values to record variables

Record type variables and %ROWTYPE

Control of flow

Conditional control

The Simple CASE statement

The Searched CASE statement

Iteration

Looping

The GOTO statement

Cursors

Explicit Cursors

Declaring a cursor

Opening a cursor

Fetching into a cursor

Closing a cursor

Cursor parameters

Explicit Cursor Attributes

Cursor FOR loops

Cursor FOR loop with parameters

Locking records with a cursor

Implicit Cursors

Implicit Cursor Attributes

Collections

Collections in PL/SQL

Associative arrays (index-by tables)

Nested tables

Initialising nested tables

Variable-size array (varray)

Multidimensional collections

Collection methods

Using methods to loop through a collection

Assigning cursor results into a collection

Error handling

Internal Oracle Errors

Handling errors in the section

Named internal Oracle exceptions

Accessing error details in

Handling non-defined internal errors

User defined exceptions

Exception propagation

Subprograms

Introduction to subprograms

Benefits of subprograms

Features of subprograms

Security and subprograms

Stored Procedures

Creating a Procedure

Formal parameter list

Creating a Procedure

Executing a Procedure

Executing a procedure with parameters

Returning OUT parameters

Errors during compilation

Deleting the Procedure

Stored Functions

Creating a Function

Calling a function from PL/SQL

Calling a function in SQL statements

Rules for calling a function in SQL statements

Deleting a Function

Packages

What are packages?

Compilation rules

The package specification

Declaring the package specification

The package body

Declaring the package body

Forward declaration

Referencing the package

Packaged cursors

Cursor defined in package spec

Cursor defined in package body

Dynamic SQL and PL/SQL

What is dynamic SQL and PL/SQL?

Binding

Advantages of binding

Guard against SQL injection with binding

Triggers

What are triggers?

Uses of Triggers

Trigger scope

Applying a Database trigger

Triggering event or statement

Trigger Action

Types of table triggers

Creating a table trigger

Accessing old and new values

Trigger restrictions

Additional conditional predicates

Trigger execution errors

More trigger DDL

View Triggers

Built-in Packages

Oracle's built-in packages

Availability

Click on a date to book it

Can't see a suitable date?
Click here to register your interest in this course and we will keep you informed when any new dates are added.

FAQs

  • What are the course times?

    • Courses start at 9AM Central/10AM Eastern start on the first day of your course. Subsequent days will usually start 30 minutes earlier but check with your instructor. The course will end by 4PM Central/5PM Eastern each day.

  • Do I need to bring anything?

    • No, everything is provided, you only have to bring yourself.

  • How can I pay?

    • Once you have booked you will receive a confirmation email, and shortly after that, an invoice. We are a UK company, so you can pay us by international bank transfer, credit or debit card. Payment is required 21 days before the start of the course, in order to gve us time to post your course materials to you.

  • What is the cancellation policy?

    • Cancellations received within 7 days of making your booking or more than 21 days before the scheduled start date will not incur any charge. For cancellations received between 7 and 21 days before the scheduled start date a 50 percent charge will apply. For cancellations received less than 7 days before the scheduled start date a 100 percent charge will apply. For online courses where you have already been sent course materials, any refund is conditional on the return of the materials at your own expense.


Other courses