Course Overview
The primary objective of this Oracle 11g training course is to provide an introduction to the PL/SQL database programming language covering syntax, structure, and features of the language within the context of database applications and programming. This Oracle 11g course will prepare students for the Oracle certification exams (OCP).
Course Objectives
Preparing Your Environment, Working with Oracle Database Tools, Language Features, The DECLARE Clause, The BEGIN Clause, The EXCEPTION Clause, Explicit Cursors, Nested Blocks, Declared Subprograms, Preparing Your Environment, Working with Oracle Database Tools, Language Features, The DECLARE Clause, The BEGIN Clause, The EXCEPTION Clause, Explicit Cursors, Nested Blocks, Declared Subprograms
Agenda
1 - Introduction
2 - Preparing Your Environment
3 - Working with Oracle Database Tools
- Selecting the Appropriate Database Tool
4 - Language Features
- Understanding PL SQL
- PL SQL Blocks
- Working with SQL Plus
5 - The DECLARE Clause
- DECLARE Clause Objects
- PL SQL Available Data Types
- Declaring Simple Data Types and Assignments
- Declaring Complex Data Types and Assignments
- Declaring Complex RowTypes and Assignments
- Declaring Complex Tables
- Declaring Complex Records
- Creating and Declaring User-Defined Types
6 - The BEGIN Clause
- What Can Be Included in the BEGIN Clause
- Working with CHAR and VARCHAR2 Variables
- Handling String Literals
- Working with Numeric Variables
- Working with Date Variables
- Assigning and Using Boolean Variables
- Using Comparison Operators
- Using SQL Functions
- SQL DML within PL SQL
- Using SELECT
- Exceptions and Embedded SQL
- Using Sequences
- Logic Control and Branching
- Using GOTO
- Working with LOOPS
- Creating and Using the Indefinite Loop
- Creating and Using the Conditional Loop
- Using Nested Loops
- Using the If-Then-Else
- Using the CASE Statement
7 - The EXCEPTION Clause
- What Are the Types of Exceptions
- Trapping Exceptions
- Identify Exception Details
- Creating User-Defined Exceptions
- Using SQLCODE and SQLERRM Objects
- Understand the Implicit Cursor
- Creating User-Defined Events
8 - Explicit Cursors
- Understanding the Concept and Purpose of Explicit Cursors
- Learn about Cursor Attributes
- Using the Continue Statement
- Working with Updateable Cursors
- Creating and Using Cursor Parameters
- FOR...LOOP Cursors
9 - Nested Blocks
- Understanding Nested Blocks
- Understanding the Difference between Global vs Local Objects and Exceptions
10 - Declared Subprograms
- What Are Subprograms and How Are They Different from Nested Blocks
11 - Conclusion