Programming with PL/SQL

Programming with PL/SQL

Lesson-by-Lesson Topics

Fundamentals

• Introduction to PL/SQL
• Benefits of PL/SQL
• Creating PL/SQL Blocks

Defining Variables and Datatypes

• Using Variables in PL/SQL
• Recognizing PL/SQL Lexical Units
• Recognizing Data Types
• Using Scalar Data Types
• Writing PL/SQL Executable Statements
• Nested Blocks and Variable Scope
• Good Programming Practices

Using SQL in PL/SQL

• Review of SQL DML
• Retrieving Data in PL/SQL
• Manipulating Data in PL/SQL
• Using Transaction Control Statements

Program Structures to Control Execution Flow

• Conditional Control: IF Statements
• Conditional Control: CASE Statements
• Iterative Control: Basic Loops
• Iterative Control: WHILE and FOR Loops
• Iterative Control: Nested Loops

Using Composite Datatypes

• User-Defined Records
• Indexing Tables of Records

Using Cursors and Parameters

• Introduction to Explicit Cursors
• Using Explicit Cursor Attributes
• Cursor FOR Loops
• Cursors with Parameters
• Using Cursors for UPDATE
• Using Multiple Cursors

Exception Handling

• Handling Exceptions
• Trapping Oracle Server Exceptions
• Trapping User-Defined Exceptions
• Recognizing the Scope of Exceptions

Using and Managing Procedures

• Creating Procedures
• Using Parameters in Procedures
• Passing Parameters

Using and Managing Functions

• Creating Functions
• Using Functions in SQL Statements
• Review of the Data Dictionary
• Managing Procedures and Functions
• Review of Object Privileges
• Using Invoker’s Rights and Autonomous Transactions

Using and Managing Packages

• Creating Packages
• Managing Package Concepts
• Advanced Package Concepts

Getting the Best out of Packages

• Persistent State of Package Variables
• Using Oracle-Supplied Packages

Improving PL/SQL Performance

• Using Dynamic SQL
• Improving PL/SQL Performance

Using and Managing Triggers

• Introduction To Triggers
• Creating DML Triggers, Part I
• Creating DML Triggers, Part II
• Creating DDL and Database Event Triggers
• Managing Triggers

Recognizing and Managing Dependencies

• Introduction to Dependencies
• Understanding Remote Dependencies

Using the PL/SQL Compiler

• Using PL/SQL Initialization Parameters
• Displaying Compiler Warning Messages
• Using Conditional Compilation
• Hiding Your Source Code