Course Overview
This class will teach students how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. They will learn the critical and fundamental aspects of the PL/SQL language.
Learning how to create efficient, maintainable, and scalable PL/SQL programs is an important objective of this course. To meet these objectives, the course includes instruction on such things as the proper use of stored procedures and functions, cursors, bind variables, bulk processing, packages, triggers, error handling, and utilities.
Hands-on workshops constitute approximately 50% of the course.
Requirements
- Knowledge of SQL (Structured Query Language) is required. Experience with procedural or structured programming is also recommended. Technical Requirements Nothing is required for labs (workshops). We will provide everything you need for all hands-on labs. Modest PC or Mac specs are required to connect to the online class. Also, we strongly recommend using two monitors for this class. This will allow you to view the instructor’s display on one monitor and your code on the second.
Curriculum
- 1 Section
- 12 Lessons
- 3 Days
Expand all sectionsCollapse all sections
- Topics12
- 1.1Introduction to PL/SQL What is PL/SQL? Why Use PL/SQL? PL/SQL Program Structure Anonymous Blocks Using SQL Developer Compile Errors Server Output Introduction to Procedures Procedure Compile Errors Procedure Compile Warnings Introduction to Functions Introduction to Packages Querying the Data Dictionary Introduction to Triggers Tools for PL/SQL Development Working in SQL*Plus Workshop
- 1.2Language Fundamentals PL/SQL Statements PL/SQL Symbols Quoting Mechanism Common PL/SQL Datatypes Declaring Variables Assignment %TYPE Attribute PL/SQL Records %ROWTYPE Attribute Programmer Defined Records Workshop Variable Scope Nested Blocks Functions Regular Expressions IF Statement CASE Statement and Expression Simple CASE Searched CASE Simple Loops Nested Loops Numeric FOR Loop PL/SQL Arrays Simple Array Example Array Methods Using Arrays for Processing Speed Workshop
- 1.3PL/SQL and SQL, Part I Basics SELECT INTO Statement Implicit Cursor Loops Workshop DML in PL/SQL Cursor Attributes Embedding DDL Workshop
- 1.4PL/SQL and SQL, Part II Cursors What is a Cursor? Explicit Cursor Processing Cursor Attributes Workshop Dynamic Cursors Dynamic Cursor Example Variable Scope & Cursors Cursor Parameters Cursor Records Cursor FOR LOOP Workshop Referencing the Current Row FOR UPDATE Example Workshop
- 1.5PL/SQL and SQL, Part III Bulk Processing Bulk Processing Bulk Collect Bulk Collect: Fetch with Limit Workshop Bulk Delete: FORALL Bulk Insert – FORALL Advanced FORALL: IN INDICIES OF; IN VALUES OF Returning into Arrays Workshop
- 1.6Stored Procedures What is a Stored Procedure? CREATE PROCEDURE Syntax Compile Errors Compiler Warnings Optimizing Compiler Native Compilation Conditional Compilation Procedure Signatures Calling Procedures Procedure Synonyms Referencing Parameters by Name Returning Sets: REF CURSOR Dropping Stored Procedures Procedure Dependencies ALTER COMPILE Statement PL/SQL Procedure Privileges Granting Execute Privilege The Data Dictionary Workshop
- 1.7PL/SQL Functions What is a Function? CREATE FUNCTION Syntax Calling Functions The Data Dictionary Workshop Table Functions Building a Table Function Using a Table Function Pipelined Functions Pipelined Example Test Data Generator Example Workshop
- 1.8Error Handling Types of Errors Runtime Errors Exception Concepts Predefined Named Exceptions EXCEPTION Block Syntax Handling Exceptions Workshop Recovering from Errors Logging Exceptions Debugging with FORMAT_ERROR_BACKTRACE RAISE_APPLICATION_ERROR Statement Workshop Raising Exceptions Raising User Exceptions EXCEPTION_INIT Pragma
- 1.9PL/SQL Packages Concepts Package Benefits Package Contents Package Specification Package Body Package Privileges Calling Packaged Objects Initialization Code Session Variables Package Body Variables Workshop Package Overloading Workshop Compiling Packages DROP PACKAGE Statement Workshop Sample of Oracle Supplied Packages
- 1.10DML Triggers DML Trigger Concepts Trigger Execution Trigger Ordering CREATE TRIGGER Syntax Row Triggers :OLD and :NEW Reference Variables Trigger Attributes Audit Trigger Derived Value Trigger Trigger Restrictions Security Privileges Workshop Statement Level Triggers Workshop Autonomous Transactions Autonomous Triggers Workshop INSTEAD OF Triggers Workshop
- 1.11File I/O Using UTL_FILE UTL_FILE Concepts Setup for UTL_FILE Permissions Opening Files Closing Files Reading Files Writing Files – PUT Writing Files – PUT_LINE Writing Files – NEW_LINE Writing Files – PUTF Workshop
- 1.12Sending Email with UTL_MAIL Introduction to UTL_MAIL Setup for Emailing from the Database UTL_MAIL Coding Other Oracle Supplied Packages
Instructor
