Beginning Oracle 11g PL/SQL

Beginning Oracle 11g PL/SQL

This Oracle 11g PL/SQL training teaches attendees beginning and advanced Oracle PL/SQL database programming skills.

ORACLE 11G PL/SQL TRAINING OBJECTIVES

• Understand the environment and context in which PL/SQL operates.
• Consider the advantages and benefits of PL/SQL within a database environment.
• Declare program variables and complex data types.
• Develop logic within PL/SQL program blocks.
• Fetch data from the database into program variables.
• Return program output to users.
• Handle program exceptions.
• Handle program exceptions.
• Use explicit and implicit database cursors.
• Maintain and implement triggers.

ORACLE 11G PL/SQL TRAINING PREREQUISITES

Prior Oracle SQL experience is presumed.

ORACLE 11G PL/SQL TRAINING MATERIALS

All students receive comprehensive courseware and a related textbook.

SOFTWARE NEEDED FOR EACH PC:

• A complete installation of Oracle Express Edition 11g
• Oracle SQL Developer
• For classes delivered online, all participants need either dual monitors or a separate device logged into the online session so that they can do their work on one screen and watch the instructor on the other. A separate computer connected to a projector or large screen TV would be another way for students to see the instructor’s screen simultaneously with working on their own.

ORACLE 11G PL/SQL TRAINING OUTLINE

• Choosing a SQL & PL/SQL Interface
  • Using SQL Developer
  • Using SQL*PLUS
  • Using Application Express
  • Using JDeveloper Studio
• Language Features
  • Understanding PL/SQL
  • Structure of A PL/SQL Program Block
  • Using PL/SQL from SQL*PLUS
• Declare Clause
  • About the Declare Clause
  • Declare Simple Types
  • Declare Complex Types
  • Type … Table
  • Type … Record
• Begin Clause
  • About the Begin Clause
  • Performing Data Manipulation
  • Logic Control & Branching
  • Loop
  • If-Then-Else
  • Case
• Exception Clause
  • About the Exception Clause
  • Isolating the Specific Exception
  • Pragma Exception_Init
  • SQL%Rowcount & Select…Into
• Explicit Cursors
  • About Explicit Cursors
  • Advanced Cursor Techniques
• Introducing Database Program Units
  • About Database Program Units
  • Types of PL/SQL Program Units
  • Types of Stored Program Units
  • Advantages of Using Stored Program Units
• Creating Stored Procedures & Functions
  • About Stored Procedures & Functions
  • Creating Procedures & Functions
  • Executing Procedures & Functions
• Maintaining Stored Procedures & Functions
  • Recompiling & Dropping Programs
  • Data Dictionary Storage
  • Managing Dependencies
• Creating & Maintaining Packages
  • About Packages
  • Creating Packages
  • Advanced Programming Techniques
  • Maintaining Packages
• Advanced Cursor Techniques
  • Using Cursor Variables
  • Using Cursor Expressions
• Using System-Supplied Packages
  • DBMS_OUTPUT()
  • UTL_FILE()
• Creating Database Triggers
  • About Database Triggers
  • Statement-Level Triggers
  • Row-Level Triggers
  • Examples of Triggers
  • Instead of Triggers
  • Employing Triggers within an Application
• Maintaining Database Triggers
  • Call Syntax
  • Trigger Maintenance Tasks
  • Show Errors Trigger
  • Drop Trigger
  • Alter Trigger
  • Handling Multiple Triggers for A Table
  • Handling Mutating Table Issues
• Implementing System Event Triggers
  • What Are System Event Triggers?
  • Defining the Scope
  • Available System Events
  • System Event Attributes