Oracle PL/SQL Programming Part 2

 

Course Description

Oracle 11g PL/SQL Programming Part 2 online training picks up right where Part 1 completes and continues to guide you through more intermediate PL/SQL topics. Through hands-on exercises, you learn to handle all types of runtime errors, use explicit cursor processing including cursor FOR loops, create and use stored procedures and functions, and design and use PL/SQL packages as well as understand the benefits of using packages. This Oracle programming course teaches you to manage dependencies between these PL/SQL stored units, and understand and manage security for them as well. Also covered in this Oracle training is learning how to automate processing by creating triggers to solve business requirements including 11g compound triggers.

In order to take this course you need to have taken Oracle 11g PL/SQL Programming Part 1 and have experience in some high-level programming such as SQL.

 

In This Course You Will Learn How 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

You need to have equivalent experience and have taken Oracle 11g PL/SQL Programming Part 1 or taken the following courses:

• Oracle 10g/11g: Introduction to SQL

• Oracle 10g/11g: Advanced SQL

• Oracle 11g/12c: Introduction to SQL

• Oracle 11g/12c: Advanced SQL

• Oracle 11g PL/SQL Programming Part 1

 

Outline

Module 00: Oracle 11g PL/SQL Programming Part 2

Module 01: 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 02: Cursor Basics Exercise and Exercise Review

Module 03: Advanced Cursor Topics

• Using %ROWTYPE

• Cursor FOR Loops

• Cursors with Parameters

Module 04: Advanced Curser Topics Part 2

• Using "Where Current of" Option

• Exercise Cursors

• Exercise Review

• What Should I Know about Cursor Processing?

Module 05: 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 06: Stored Procedures and Functions Basics Exercise and Exercise Review

Module 07: Passing Parameters to Stored Procedures and Functions

• Using Parameters

• Formal Parameter Modes

Module 08: Passing Parameters to Stored Procedures and Functions Exercise and Exercise Review

Module 09: Dependencies and Security

• Source Code and Dependencies

• security for Stored Program Units

• Implementing Invoker's Rights

• DETERMINISTIC Functions

Module 10: Stored Procedure and Functions Exercise and Exercise Review

• What Should I Know about Stored Procedures and Functions?

Module 11: Trigger Basics

• Triggers

• Define Characteristics of Database Triggers

Module 12: Trigger Basics Part 2

• Row vs. Statement Triggers

• Trigger Types

• Understand the Difference between Statement Level and Row Level Triggers

• Identify Restrictions for Triggers

Module 13: Advanced Trigger Concepts

• Use Trigger Predicates

• 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 14: Triggers Exercise and Exercise Review

• What Should I Know about Triggers?

Module 15: PL/SQL Packages

• Identify Advantages of Using Packages

• Define a Package Specification

• Define a Package Body

Module 16: PL/SQL Packages Part 2

• Implement Public and Private Elements

• Understand Persistent State of Packaged Objects

• Execute Procedures and Functions in a Package

Module 17: Packages Exercise and Exercise Review

• What Should I Know about Packages?