Loading Course Schedule...
PT4692
Summary
PL/SQL is an important skill to have in an Oracle environment. This class will teach key skills that PL/SQL programmers need to know. Students will write stored procedures, functions, packages and triggers so they can implement complex business rules in Oracle. Students will learn good programming techniques, management and security issues of working with PL/SQL program units. Students will learn how to leverage the features of the Oracle server using PL/SQL. Numerous exercises and labs will help students develop skill with the PL/SQL language.
Prerequisites
A solid understanding of SQL and 3GL programming is required.
Duration
3 Days/Lecture & Lab
Audience
This course is designed for Developers.
Topics
- Features of PL/SQL
- The Block Structure
- Language Rules
- Executing PL/SQL from SQL*Plus
- Variables and Constants
- Identifiers
- Customizing SQL*Plus for PL/SQL Scripts
- SELECT INTO Statement
- Using Bind Variables
- Interactive PL/SQL Programs
- Oracle Datatypes are supported in PL/SQL
- Numeric Datatypes
- Datatype Conversion
- Implicit and Explicit Conversion
- %TYPE and %ROWTYPE
- Subtypes
- Subtype Declarations
- Labels
- Variable Scope and Visibility Rules
- SQL and PL/SQL Reserved Words
- PL/SQL Reserved Words
- Direction of PL/SQL
- External Routines and Java Stored Procedures
- Control Flow
- Operators
- Built-In Functions
- Handling Exceptions
- Working with Exceptions
- SQLCODE and SQLERRM Functions
- Predefined PL/SQL Exceptions
- User-Defined Exceptions
- Working with Exceptions
- The Procedure Raise_Application_Error
- Generic LOOP
- Working with NULL
- LOOP Labels
- FOR LOOP
- WHILE LOOP
- GOTO Statement
- Working with Cursors
- Working with Cursors
- Implicit Cursors and Attributes
- Explicit Cursors
- Explicit Cursor Attributes
- Using Subqueries with Cursors
- Using Cursor FOR LOOPs
- Using %ROWTYPE with Cursors
- Using Aliases and Subqueries
- Using Aliases and Subqueries (FOR LOOP)
- Using Parameters with Explicit Cursors
- Modifying Data using Explicit Cursors
- Managing Transactions in PL/SQL Programs
- Procedures and Functions
- Working with Subprograms
- Named Subprograms
- Software Evolution
- Stored Procedures
- Parameters
- NOCOPY Compiler Directive
- Parameter Default Values
- Positional and Named Notation
- Executing a Stored Procedure
- Compiling and Debugging Stored Procedures
- Compiling a Stored Procedure
- Stored Function Syntax
- Executing a Function from SQL*Plus
- The RETURN Statement
- Advantages of Packages
- Packages
- What can a Package Contain?
- Components of a Package
- Package Specification
- Package Body
- Defining Package Components
- Package Scope
- Public Scope
- Private Scope
- Accessing Package Components
- Privileges
- Managing Side Effects
- Defining Subprograms within another Subprogram
- Forward Declarations
- Overloading
- Restrictions on Overloading
- Restrictions on Packaged Variables
- Triggers
- Purpose of Triggers
- Trigger Guidelines
- Table Triggers
- Firing Order of Table Triggers
- Table Trigger Syntax
- ALTER and DROP Trigger Commands
- Boolean Functions
- Reference Variables for ROW Triggers
- ROW Trigger Examples
- Information about Triggers
- WHEN Clause
- Restrictions
- Database Triggers
- Event Functions