Oracle PL/SQL 10g: Programming Level 1

Course Overview

This course is highly interactive and consists of theory coupled with plenty of hands on practical sessions. It provides an in-depth look at PL/SQL in the SQL*Plus environment in order to write Stored Procedures and Stored Functions. This course is suitable for Oracle 9i & 10G users.

Audience

This course is for programmers and support staff who wish to gain a comprehensive understanding of how the Oracle Procedural Language / Structured Query Language (PL/SQL) is used.

Skills Gained

On completion, delegates will be able to:

• Write PL/SQL Control Structures.
• Edit, store & recall PL/SQL blocks.

Prerequisites

Delegates should be computer literate with a good knowledge of Oracle SQL and SQL*Plus and the standard editor available on their target hardware platform. Attendance of our course Oracle SQL Part 1 is highly recommended.

Course Outline

PL/SQL Overview

• SQL*Plus
• Client/server architecture
• Internet/intranet architecture
• ISO ( Incorporating ANSI and BSI) standards
• Quiz

Programming

• Local variables
• Database derived variables User defined records
• User defined variables
• Variable assignment
• If ... Else
• Oracle9i/10G PL/SQL case statements
• Oracle9i/10G PL/SQL case expressions
• Oracle9i/10G searched case statements
• General statement considerations
• For loop
• While
• (repeat until) loop
• Goto and labels
• Quiz and practical session on programming

Exceptional Handling

• User defined
• Oracle predefined
• Oracle defined
• Sqlcode and sqlerrm
• Nested blocks
• Quiz practical session on exception handling

Cursors

• Introduction
• Cursor
• Open
• Fetch
• Close
• Cursor for loop
• Data modifications
• Implicit cursors
• Cursor variables
• Quiz and practical session on cursors

Stored Procedures and Stored Functions

• Advantages
• Stored procedures
• Input parameters
• Output parameters
• I/O parameters
• Stored functions
• Local procedures and functions
• Compilation
• User defined errors
• External programs
• Quiz and practical session on stored procedures

Database DML Triggers

• Trigger roles
• Trigger restrictions
• Trigger execution
• Trigger creation
• Old and new data & update trigger
• A delete trigger
• An insert trigger
• Multiple event triggers
• An instead of trigger
• Quiz and practical session on database DML triggers

Oracle PL/SQL 10g: Programming Level 1 / ORPL1