Database Programming with PL/SQL – Course Description
Overview
This course introduces students to PL/SQL, Oracle’s procedural extension language for SQL and the Oracle relational database. Participants explore the differences between SQL and PL/SQL. They also examine the characteristics of PL/SQL and how it is used to extend and automate SQL to administer the Oracle database. This course culminates with a project that challenges students to program, implement, and demonstrate a database solution for a business or organization.
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