Programming with PL/SQL 2 day remote one on one training course - £450 plus VAT

Please note:

Due to the current coronovirus situation this course is currently only available as a live remote one on one course.

Once things are back to normal we will be running public dates in London, Manchester, Sheffield, Leeds, Nottingham and Birmingham, as well as in-person one on one courses and private (closed) courses.

Click here to register your interest in this course and we will keep you informed when any new dates are added.

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

£450 plus VAT (£540 inc VAT) for our remote live one on one course, accessed over the internet - click here for details

Dates

We can arrange dates to suit you - please contact us for further details or to book this course.

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




FAQs

  • What are the course times?
    • Most course days run from 9.30am to around 4pm, however as we are currently only offering one on one remote courses during the current crisis, we can be quite flexible if you would like to do different hours.

  • Do I need anything?
    • For our live remote one on one courses you only need a computer with the Chrome browser, and speakers or headphones to hear the instructor. When it comes time to do an exercises you can either connect to our computer to do them, or do them on your own computer. We will advise for your specific course what software you would need to do them on your own computer but don't worry, you can connect to ours if you prefer.

  • How can I pay?
    • Once you have booked you will receive a confirmation email, and shortly after that, an invoice. For online courses payment is required before the start of the course. Payment can be made by bank transfer (BACS etc), cheque or card.


Other courses