Quick Navigation
Oracle 11g PL/SQL Fundamentals course
Download PDF version of Oracle 11g PL/SQL Fundamentals course outline
Overview
Oracle Application Server 11g (g stands for Grid), is an integrated, standards-based software platform for database management. This course is designed to introduce delegates to the fundamentals of Oracle's procedural language, PL/SQL, the engine behind the full range of Oracle technology.
The course deals with Oracle's PL/SQL programming language and provides practical experience in writing stand-alone programs, developing database triggers and implementing stored functions, procedures and packages. It also covers some of the Oracle-supplied packages.
Audience
This course is appropriate for Analysts, Programmers, Developers, Technicians and DBA’s.
Course Objectives
To provide the skills needed to develop, write and maintain PL/SQL scripts, triggers, stored program units and packages.
Prerequisites
Attendance of the Oracle SQL course or equivalent knowledge.
Course Contents (5 days)
- PL/SQL FUNDAMENTALS
- What is PL/SQL?
- Basic Elements
- Variables and Constants
- Data Types
- Initializing Variables and
Assigning Values - Using SQL Statements in Code
- Generating Output to SQL or
SQL Developer - PROGRAM LOGIC
- IF THEN ELSE Statements
- CASE Expressions
- The Basic Loop Construct
- WHILE and FOR Loops
- Nested and Labelled Loops
- The GOTO Statement
- The CONTINUE Statement
- USING CURSORS
- What is a Cursor?
- Implicit and Explicit Cursors
- Cursor Operations
- Declaring, Opening and
Closing Cursors - Fetching Rows
- Status Checking
- Using Cursors FOR UPDATE
- The Cursor FOR Loop
- Parameterised Cursors
- EXCEPTIONS AND NESTED BLOCKS
- The EXCEPTION Section
- Types of Exception
- Handling Named & Unnamed
System-Raised Exceptions - User-Declared Exceptions and
Application Errors - When others then Null
- Nested and Labelled Blocks
- Propagation of Exceptions
- Scope of Variables and Cursors
- PL/SQL RECORDS AND INDEX-BY TABLES
- Declaring Record Types
- Handling PL/SQL Records
- Nested Records
- Declaring PL/SQL Index-By Tables
or Associative Arrays - PL/SQL Table Built In Functions
- Manipulating PL/SQL Tables
or Associative Arrays - TRIGGERS
- DML Triggers
- The CREATE TRIGGER Statement
- Writing Trigger Code
- INSTEAD OF Triggers
- Calling Procedures from Triggers
- Coding Restrictions
- System Event and DDL Triggers
- Attribute Functions
- Compound Triggers
- Create Trigger Follows Clause
- Managing Triggers
- Privileges required for Triggers
- Dictionary Information Concerning triggers
- PROCEDURES
- What is a Procedure?
- The CREATE PROCEDURE Statement
- Procedure Parameters
- Invoking Procedures
- Local Subprograms
- Named Association Parameter Passing
- Definer's Right and Invoker's Rights
- Autonomous Transactions
- Managing Procedures
- Privileges Required for Procedures
- Dictionary Information Concerning Procedures
- The Call Statement
- FUNCTIONS
- What is a Function?
- The CREATE FUNCTION Statement
- Executing Functions
- Invoker's Rights
- Autonomous Transactions
- DBMS_OUTPUT
- Using Functions in SQL Statements
- Deterministic and Parallel-Enabled Functions
- Function Result Cache
- Managing Functions
- Privileges Required for Functions
- Dictionary Information Concerning Functions
- PACKAGES
- What is a Package?
- Public and Private Components
- Creating a Package
- Example Package
- Persistent States
- One-time-only Procedures
- Overloading
- Purity Level Checking
- Forward Declarations
- Wrapping Packages
- Managing Packages
- Privileges Required for Packages
- Dictionary Information Concerning
Packages Features - USING PL/SQL RECORDS AND
TABLES IN PACKAGES - Overview of PL/SQL Records,
Index-by Tables and Associative Arrays - Using PL/SQL Records and Tables
in Packages - Table Built-In Functions
- The NOCOPY Hint
- Bulk Collection
- Bulk Binding DML Statements
- CURSOR VARIABLES (REF CURSORS)
- Declare Cursor Variables
- Use Cursor Variables
- Open and Close Cursor Variables
- Fetch Rows
- Cursor Variable Attributes
- Pass Cursor Variables as Parameters
- Open and Close Cursor Variables
- Refcursor Datatype
- MANAGING DEPENDENCIES
- Dependent and Referenced Objects
- Invalidation and Recompilation
- Local and Remote Dependencies
- Recompilation Considerations
- NATIVE COMPILATION AND
COMPILE-TIME WARNINGS - Introduction
- Native Compilation
- Automatic Recompilation
- Automatic program Sublining
- WHEN OTHERS ... THEN NULL
- Data Dictionary Information
- Compiler Warning Categories
- Using the DBMS_WARNING Package
- ORACLE-SUPPLIED PACKAGES
- Overview of Oracle-Supplied Packages
- Using the DBMS_SQL Package
- Using Native Dynamic SQL
- The DBMS_METADATA Package
- The UTL_MAIL Package
- The DBMS_APPLICATION_INFO Package
- The DBMS_UTILITY Package
- Scheduling Jobs using the
DBMS_SCHEDULER Package