Getting Started with PL/SQL
This class will teach you how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. You will learn the critical and fundamental aspects of PL/SQL language and progress to more advanced subjects like bulk-processing, packages and triggers.
Learning how to create efficient, maintainable, and scalable PL/SQL programs are important objectives 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.
Enrollment includes Steven Feuerstein’s top rated text book “Oracle PL/SQL Best Practices“.
Hands-on workshops constitute approximately 50% of the class.
For customer-site training, as with all of our courses, this class is highly customizable to your specific training requirements.
Prerequisites
Knowledge of Structured Query Language (SQL) is required.
Any experience with procedural or structured programming is also recommended.
Technical Requirements
Nothing is required for labs (workshops). SkillBuilders will provide everything you need for all hands-on labs. Modest PC or Mac specs are required to connect to the online class. Detailed specs can be found here.
Also, we strongly recommend using two monitors for this class. This will allow to you to view the instructors display on one monitor and your code on the second.
Course Features
- Lecture 0
- Quiz 0
- Duration 5 Half Days (~20 hours)
- Skill level All levels
- Language English
- Students 90
- Assessments Yes
Introduction 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
- Introduction to Functions
- Introduction to Packages
- Querying the Data Dictionary
- Introduction to Triggers
- Tools for PL/SQL Development
- Working in SQL*Plus
- Workshop
Language 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
- SQL Functions and PL/SQL Error-Reporting 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
PL/SQL and SQL, Part I Basics
- SELECT INTO Statement
- Implicit Cursor Loops
- Workshop
- DML in PL/SQL
- Cursor Attributes
- Native Dynamic SQL (NDS)
- Embedding DDL
- Workshop
PL/SQL and SQL, Part II Cursors
- What is a Cursor?
- Explicit Cursor Processing
- Explicit Cursor Loop
- 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
PL/SQL and SQL, Part III Bulk Processing
- Bulk Processing
- Bulk Collect
- Bulk Collect: Fetch with Limit
- Workshop
- Bulk Delete: FORALL
- Bulk Insert: FORALL
- Bulk Update: FORALL
- Advanced FORALL: IN INDICIES OF; IN VALUES OF
- Returning into Arrays
- Workshop
Stored 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
PL/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
Error 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
PL/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
DML Triggers
- Trigger Concepts
- DML Trigger Uses
- Trigger Execution and 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
File I/O Using UTL_FILE
- UTL_FILE Concepts
- Setup for UTL_FILE
- Permissions
- Opening Files
- Closing Files
- Reading Files
- Writing Files
- Workshop
Sending Email with UTL_MAIL
- Introduction to UTL_MAIL
- Setup for Emailing from the Database
- UTL_MAIL Coding
- Other Oracle Supplied Packages
- Workshop
