Categories
Oracle DatabaseProgramming with PL/SQL
Programming with PL/SQL
Students are introduced to PL/SQL and explore how it extends and automates SQL in administering the Oracle database, and create projects to design, implement, and demonstrate a database solution for a business.
180 Hours
For students: Who wish to learn the techniques and tools to design, build and extract information from a database. This foundational course is suitable for computer science majors and non-majors alike.
Educator prerequisites: High level knowledge of database design concepts and of programming with SQL
Suggested: Database Design & Programming with 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