IT Technical & End User
Course Listings

For further information, or to make a telephone booking, or to receive a competitive quotation for an in-house course, you can call Pentland Training now on 0800 328 2766.

All of our training courses can be delivered on your own site, at Pentland Training facilities, or at an off-site venue.

Hot Topics

3-day ITIL Foundation

5-day PRINCE2™ Practitioner

4-day Business Analysis

3-day Project Management Fundamentals

Course Title:
Fundamentals of PL/SQL

Duration: 5 days

Course Overview
This course covers the fundamentals and key features of Oracle's procedural language, PL/SQL. The course covers PL/SQL syntax, anonymous blocks, procedures, functions, packages and control structures it then moves on to more complicated subjects such as explicit cursors, composite data types, exception handling and triggers.
The course is geared towards providing attendees with maximum hands-on experience of using and writing PL/SQL blocks of code under the expert guidance of an experienced tutor.

Target Audience


Systems Developers and DBA’s who need to know PL/SQL techniques.

Course Objectives


By the end of the course attendees will be able to:

• Create anonymous blocks of PL/SQL code
• Enter values for placeholders within anonymous blocks
• Understand naming conventions, datatypes etc.
• Create and use cursors for manipulating large numbers of records
• Control program flow using iteration and loops
• Create various structures for holding data, including cursors, variables, record variables and PL/SQL tables.
• Use various error handling techniques
• Create and maintain procedures
• Create and maintain functions
• Create and maintain packages
• Use calling and return arguments in procedures and functions
• Understand the different types of trigger available
• Create triggers to enforce business rules

Prerequisites


It is assumed that attendees will have some knowledge of Oracle SQL and of the Oracle database. Preferably, delegates will have attended the Fundamentals of SQL and SQL*Plus course.

Course Content


Introduction to PL/SQL
• SQL Refresher
• Using SQL Functions and Constructs Within PL/SQL Blocks of Code
• The Basic Concepts of PL/SQL
• PL/SQL Rules and Guidelines
• Data Types
• Variables
• Naming Conventions
• Declaring Variables
• Populating Variables by Assignment
• Populating Variables Using the SELECT INTO Method

Built in Functions
• DBMS_OUTPUT Package

Anonymous Blocks
• Block Structure
• Implicit Cursors

Control Structures and Iteration
• IF, ELSE, ELSIF Statement
• Basic Loop
• The EXIT Statement
• FOR Loops
• Using the NULL Statement

Explicit Cursors in PL/SQL
• Defining an Explicit Cursor
• OPEN – FETCH - CLOSE
• Passing Parameters to Cursors
• Cursor FOR Loops
• The WHERE CURRENT of Clause

Composite Data Types
• PL/SQL Record Variables
• PL/SQL Tables

Transaction Control
• Using the COMMIT, ROLLBACK and SAVEPOINT in PL/SQL
• Locking Records Using the FOR UPDATE
• Locking Tables

Error Handling
• Definition and Advantages of Exception Handling
• User-defined Exceptions
• Unnamed Programmer-defined Exceptions
• Named System Exceptions
• Unnamed System Exceptions
• Continuing Execution After an Error
• Tips and Techniques When Error Handling
• Handling Errors With WHEN OTHERS
• Using SQLCODE and SQL ERRM
• Populating Log Tables With Error Messages

Procedures and Functions
• Creating Procedures With no Arguments
• Creating Procedures With Arguments
• Creating Functions
• Error Handling
• Dropping Procedures and Functions

Packages
• How Packages are Used
• The Package Body and Specification
• Creating, Using and Dropping Packages
• Initialising Packages
• Overloading Procedures
• Viewing Source Code
• Compiling Functions, Procedures and Packages

Advanced Package Concepts
• Overload procedure and function definitions
• Use forward declarations
• Create a one-time package initialization block
• Follow the persistent state of constructs in packages
• Use PL/SQL tables and records in packages
• Wrap code to hide the source

Triggers
• Trigger Components
• Statement and Row Triggers
• Multi-purpose Triggers
• Customising Error Conditions
• DDL Triggers
• Database Event Triggers
• Enabling and Disabling Triggers

Applications for Triggers
• Create database and system event triggers
• Create triggers on DDL statements
• Use the CALL statement in triggers to invoke procedures
• Explain the rules for reading and writing to tables with triggers
• Describe business application scenarios for implementing with triggers
• Manage trigger code

Dynamic SQL and Metadata
• Describe using native dynamic SQL
• List the execution flow of SQL
• Write dynamic SQL using the EXECUTE IMMEDIATE syntax
• Write dynamic SQL with the DBMS_SQL package
• Generate DDL from metadata using the DBMS_METADATA package

Managing Dependencies
• Describe dependent and referenced objects
• Track procedural dependencies with dictionary views
• Predict the effect of changing a database object upon stored procedures and functions
• Manage local and remote procedural dependencies

Enter your email address to register for our newsletter: