• Decrease font size
  • Default font size
  • Increase font size

Oracle PL/SQL Self-Paced Training Course

 Print E-mail

Try a Self-Paced Training Module Now!

Start Oracle PL/SQL Training Today!

 

  • Set your own training schedule
  • Watch as many times as you want 24/7 for 60 days following purchase
  • Find out what Dashcourses' self-paced training is all about with no obligation - download Silverlight and View Module 1
  • Our self-paced training modules are viewable in Internet Explorer, Firefox and Safari

Training Description

This course introduces students to PL/SQL and helps them understand the benefits of Oracle’s proprietary database language. Students learn to create PL/SQL blocks of application code that can be shared by multiple users, forms, reports, and data management applications. Students learn to create anonymous PL/SQL blocks as well as named stored procedures, functions, packages and triggers. Students learn to develop, execute, and manage PL\SQL stored program units, subprograms, triggers, declaring identifiers and both user-defined and pre-defined exceptions. Students are introduced to the utilization of some of the more useful Oracle-supplied packages.

Training Objectives

As a result of taking this course, you will be able to:

  • Describe the features and syntax of PL/SQL
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Handle runtime errors
  • Design PL/SQL anonymous block that execute efficiently
  • Create and use stored procedures and functions
  • Design and use PL/SQL packages to group and contain related constructs
  • Manage dependencies between PL/SQL subprograms
  • Use conditional compilation to customize functionality in a PL/SQL block
  • Create overloaded package subprograms
  • Use the Oracle supplied PL/SQL packages to generate screen output, file output, and schedule jobs.
  • Write dynamic SQL for coding flexibility
  • Understand security for stored PL/SQL units
  • Design PL/SQL code for predefined data types, local subprograms, additional pragmas and standardized constants and exceptions
  • Create triggers to solve business requirements including 11g compound triggers
  • Use the 11g PL/SQL function cache

Prerequisites

SQL and previous programming experience

Modular Outline

Module 00: Course Introduction

Module 01: PL/SQL Overview

  • Define PL/SQL
  • Identify advantages of using PL/SQL
  • Basic PL/SQL commands
  • Discuss where PL/SQL is used
  • Exercise

Module 02: PL/SQL Overview (cont)

  • Understand PL/SQL block structure
  • List the four main parts of a PL/SQL block
  • State the purpose of each part
  • Write Anonymous Blocks
  • Nested blocks
  • Use DBMS_OUTPUT.PUT_LINE procedure
  • Exercise

Module 03: I/O Within PL/SQL and Overview of sample environment

  • I/O Within PL/SQL
  • Overview of sample environment

Module 04: Exercise: PL/SQL Overview

Module 05: Datatypes and scalar variables

  • Datatypes
  • Declare scalar variables

Module 06: Chapter 2 – Mod 2: Composite variables

  • Create composite variables
  • Assign and update values for variables
  • Reference PL/SQL table variables

Module 07: Exercise: Datatypes and scalar variables

Module 08: Exercise Review

Module 09: What should I know about PL/SQL Variables?

Module 10: SQL Statements in PL/SQL

  • Select data from the database into PL/SQL variables
  • The INTO Clause

Module 11: Exercise: Using SQL in PL/SQL: SELECT

Module 12: Exercise Review

Module 13: DML Commands and Transaction Processing

  • DML Commands
  • Using RETURNING Clause
  • Transaction Processing Statements: COMMIT, ROLLBACK, and SAVEPOINT

Module 14: Exercise DML Commands and Transaction Processing

Module 15: Exercise Review

Module 16: What Should I know about Using SQL in PL/SQL?

Module 17: IF and CASE statements

  • Use IF..THEN..ELSE logic in PL/SQL
  • Use the CASE statement

Module 18: Exercise: IF and CASE

Module 19: Exercise Review

Module 20: Loops and Labels

  • Labels and GOTO Statement
  • Looping Statements
  • Simple loops, Numeric FOR Loops, WHILE Loops

Module 21: Exercise: Loops

Module 22: Exercise Review

Module 23: What should I know about conditional logic and loops?

Module 24: Exception Basics and Pre-Defined Exceptions

  • Exception Handling
  • Define and code exceptions
  • the OTHERS Exception
  • Pre-defined exceptions

Module 25: Exercise: Pre-Defined Exceptions

Module 26: Exercise Review

Module 27: User-Defined Exceptions

  • SQLCODE and SQLERRM
  • User-Defined Exceptions
  • Map an existing Oracle error to an exception handler

Module 28: Exercise: User-Defined Exceptions

Module 29: Exercise Review

Module 30: Exception Propagation and Displaying Errors

  • Exception Propagation
  • Using nested blocks
  • Using RAISE_APPLICATION_ERROR
  • Display Errors with DBMS_UTILITY

Module 31: Exercise: Exception Propagation and Displaying Errors

Module 32: Exercise Review

Module 33: What Should I Know about exceptions?

Module 34: Cursor Basics

  • Learn how Oracle uses cursors to process SQL
  • Identify when to use an explicit cursor
  • Describe an implicit cursor
  • Use cursor OPEN, FETCH, and CLOSE statements
  • Identify and use cursor attributes
  • Use parameters with cursors
  • Use cursor FOR loops

Module 35: Exercise: Cursor Basics

Module 36: Advanced Cursor topics

  • Using %ROWTYPE
  • Cursor FOR Loops
  • Cursors with Parameters
  • Using “Where Current of” Option

Module 37: Exercise Cursors

Module 38: Exercise Review

Module 39: What Should I Know about cursor processing?

Module 40: Stored Procedures and Functions Basics

  • Create stored procedures
  • Create stored functions
  • Use a function in a SQL statement
  • Shared SQL Area
  • Pass parameters to procedures and functions
  • Locate source code in the database

Module 41: Exercise: Stored Procedures and Functions Basics

Module 42: Exercise Review

  • Exercise Review

Module 43: Passing Parameters to Stored Procedures and Functions

  • Using Parameters
  • Formal Parameter Modes

Module 44: Exercise: Passing Parameters to Stored Procedures and Functions

Module 45: Exercise Review

Module 46: Dependencies and Security

  • Source Code and Dependencies
  • Security for Stored Program Units
  • Implementing Invoker's Rights
  • DETERMINISTIC Functions

Module 47: Exercise Stored Procedure and Functions

Module 48: Exercise Review

Module 49: What Should I Know about stored procedures and functions?

Module 50: Trigger basics

  • Implement triggers
  • Define characteristics of database triggers
  • Row vs. Statement Triggers
  • Trigger Types

Module 51: Advanced Trigger Concepts

  • Use trigger predicates
  • Understand the difference between statement level and row level triggers
  • Identify restrictions for triggers
  • Retrieve trigger definitions from the data dictionary
  • Enable and disable triggers
  • Execution Order of Triggers- Using FOLLOWS clause Compound Triggers (11g)
  • Other Events and Triggers

Module 52: Exercise: Triggers

Module 53: Exercise Review

Module 54: What should I know about triggers?

Module 55: PL/SQL Packages

  • Identify advantages of using packages
  • Define a package specification
  • Define a package body
  • Implement public and private elements
  • Understand persistent state of packaged objects
  • Execute procedures and functions in a package

Module 56: Exercise: Packages

Module 57: Exercise Review Packages

Module 58: What should I know about packages?

Module 59: Course Summary