Programming with PL/SQL 2 DAY TRAINING COURSE - £650 plus VAT

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

£650 plus VAT

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 or location?
This course is also available as a one on one course or as a private course.
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?
    • Please arrive for a 9.30am start on the first day of your course. Subsequent days will usually start at 9am but check with your instructor. The course will end at 5pm 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. The invoice is payable within 30 days and payment can be made by bank transfer (BACS etc), cheque or card.

  • Is there a dress code?
    • There is no dress code for any of our courses.

  • Are refreshments provided?
    • Mid-morning and mid-afternoon refreshments will be provided. Lunch is not provided but all our venues are in central locations.

  • 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.


Other courses