Databases & SQL - Oracle SQL / PL
The Oracle PL/SQL course is designed to introduce Oracle DBAs, software development personnel and database support staff to the primary features and constructs of the PL/SQL Programming Language.
The delegates will practise:
Writing and running PL/SQL programs from SQL*Plus or SQL Developer
Declaring and using variables and constants
Assigning and manipulating values
Using SQL statements within PL/SQL
Implementing conditional execution and loop constructs
Declaring and using cursors with and without parameters
Updating data with cursors
Trapping and handling errors and exceptions in code
Implementing nested and labelled loops and blocks
Handling PL/SQL records and tables
Using PL/SQL table built-in functions
This is a hands-on, tutor-led course running from 10 - 5.
Lunch and refreshments are provided.
This course can be run as a company specific event at your premises.
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions
Session 1: PL/SQL FUNDAMENTALS
What is PL/SQL?
Variables and Constants
Initialising Variables and Assigning Values
Using SQL Statements in Code
Generating Output to SQL or SQL Developer
Session 2: PROGRAM LOGIC
IF THEN ELSIF ELSE Statements
The Basic Loop Construct
WHILE and FOR Loops
Nested and Labelled Loops
The GOTO Statement
The CONTINUE Statement
Session 3: USING CURSORS
What is a Cursor?
Implicit and Explicit Cursors
Declaring, Opening and Closing Cursors
Using Cursors FOR UPDATE
The Cursor FOR Loop
Session 4: EXCEPTIONS AND NESTED BLOCKS
The EXCEPTION Section
Types of Exception
Handling Named System-Raised Exceptions
Handling Un-named System-Raised Exceptions
User-Declared Exceptions and Application Errors
WHEN OTHERS THEN NULL
Nested and Labelled Blocks
Propagation of Exceptions
Scope of Variables and Cursors
Scope of Goto Statements
Session 5: PL/SQL RECORDS AND INDEX-BY TABLES
Declaring Record Types
Handling PL/SQL Records
Declaring PL/SQL Index-By Tables or Associative Arrays
PL/SQL Table Built-in Functions
Manipulating PL/SQL Tables or Associative Arrays
This course will benefit Oracle database administrators, software development personnel and database support staff who need to write PL/SQL scripts to enhance their productivity in manipulating data.
Delegates will require a working knowledge of SQL*Plus or SQL Developer and Oracle SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite course. Some previous programming experience will also prove advantageous.
Together with the PL/SQL Stored Program Units course, the Oracle PL/SQL course is suitable for delegates intending to sit the #1Z0-147 - Oracle Program with PL/SQL & #1Z0-144 - Oracle Database 11g: Program wih PL/SQL examinations.
Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.
Exams are not included as part of the course.