Oracle PL/SQL Fundamentals

Request info

Course duration: 3 Days

Overview

This 3 day course gives an introduction to ORACLE PL/SQL, an application development environment that enables the writing of stored procedures, functions and triggers using both SQL and PL/SQL commands.

It may be based on ORACLE version 9i, 10g or 11g of the relational database and the principles learnt may be applied to versions 8 and 8i.

Full course notes are provided along with sample database files, example PL/SQL files and free software tools for use in accessing an ORACLE database.

Pre-requisites

It is recommended that the delegate has attended the Oracle SQL Fundamentals course or equivalent or has extensive experience of the use of SQL with another Relational Database, such as MySQL, Informix or Sybase.

Follow-up

For those who will be undertaking the task of Database Administration, the follow-up to this course would be the ORACLE Database Administration course.
On-site Requirements

Course Length

The course takes the format of a workshop, with a mix of lecture, working examples and practical exercises.

Although the content may be customised, at least 2 days are needed to cover the core elements.

Course Content

Introduction

Overview, Aims and Objectives, Schedule, Introductions, Pre-requisites, Bibliography, Responsibilities

SQL*Plus
Objectives, What is SQL*Plus?, Starting SQL*Plus, iSQL*Plus, Using SQLTools, SQL*Plus Commands, SQL*Plus Environment, LOGIN.SQL File, Documentation, Entity Models, The ORDERS Tables, The FILM Tables, Course Tables Handout

PL/SQL

Objectives, What is PL/SQL?, Why Use PL/SQL?, Block Structure, Displaying a Message, Setting SERVEROUTPUT, Sample Code, PL/SQL Code, Update Example, Variables

Variables

Objectives, Datatypes, Setting Variables, Constants, Local and Global Variables, %Type Variables, Substitution Variables, Comments with &, Verify Option, && Variables, Define and Undefine

Select Statement

Objectives, SELECT Statement, Populating Variables, %Rowtype Variables, Record Types

Conditional Statement

Objectives, IF Statement, SELECT Statement, Case Statement

Iteration – Looping

Objectives, Loop Statement, While Statement, For Statement, Goto Statement and Labels

Trapping Errors

Objectives, Exception, Internal Errors, Error Code and Message, User Exceptions, Using No Data Found, Nested Blocks, EXCEPTION, EXCEPTION, Trapping Non-defined Errors, Using PRAGMA EXCEPTION_INIT

SELECT wp_posts.*, wp_p2p.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_p2p WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'start_date' AND CAST(wp_postmeta.meta_value AS DATE) >= '2024-11-12' ) ) AND ((wp_posts.post_type = 'schedule' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'))) AND (wp_p2p.p2p_type = 'schedule_to_courses' AND wp_posts.ID = wp_p2p.p2p_from AND wp_p2p.p2p_to IN (SELECT wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID IN (706) AND ((wp_posts.post_type = 'courses' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'))) ORDER BY wp_posts.post_date DESC )) GROUP BY wp_posts.ID ORDER BY CAST(wp_postmeta.meta_value AS DATE) ASC