PL/SQL Course

Value Beyond Technology

Ultimo Software Solutions is a valued Enterprise Application service provider that assists leading business organizations to deploy IT solutions ensuring long-term success in SOA, EAI, B2B, BPM, AJAX Rich Internet Applications, and User Interfaces.

This course begins with an introduction to PL/SQL explaining its block structure. It expounds about the architecture of the PL/SQL Engine and how data is manipulated using this powerful Language. This course elucidates the concepts of Cursors and Records. The participant will get a lucid view of the control structure which provides conditional tests, loops and branches that lets us produce a well-structured program. It presents a sound understanding of Exception Handling to trap runtime errors.

This course bestows profound knowledge of administering the object dependencies. Building stored procedures and functions to solve a specific problem or perform a set of related tasks. It aids in turning the sets of statements into reusable subprograms for other applications. It illustrates implicit firing of triggers, its types and how they are used. It also explains the outline of collections and Records.

Prerequisites

Basic SQL Knowledge

Objectives
  • Obtain an overview of PL/SQL
  • Finding the Block Structure of PL/SQL
  • Summarizes PL/SQL Architecture
  • Concepts of Cursors and Records
  • Review the control structure
  • Achieve Exception Handling
  • Administering the Object Dependencies
  • Understanding the Main features of PL/SQL
  • Discovering the concepts of subprograms, Triggers and Packages
  • Recognizing collections and records
Course Content

Introduction to PL/SQL

  • Benefits of PL/SQL
  • Understanding the Main Features of PL/SQL
  • Processing queries with PL/SQL
  • Writing Reusable PL/SQL Code

Data Types, Variables, Functions, Operators

  • Create Data Types
  • Declaring Variables
  • Bind Variables
  • Managing Functions and Operators

PL/SQL Block

  • Block  Structure
  • Block Types
  • Syntax and Guidelines
  • Nested Blocks

PL/SQL Architecture

  • In the Oracle Database Server
  • In Oracle Tools

Data Manipulation using PL/SQL

  • Inserting Data
  • Updating Data
  • Deleting Data

PL/SQL Records and Cursors

  • Implicit Cursors
  • Explicit Cursors
  • Cursor Attributes
  • PL/SQL Records
  • PL/SQL Tables
  • PL/SQL Table of Records

Control Structures

  • Conditional Control
  • Iterative Control
  • Sequential Control
  • Testing Conditions
  • Nested Loops

Exception Handling

  • Define Exceptions
  • Recognize unhandled exceptions
  • Types of Exception Handlers
  • Customize Exception Messages

Managing Object Dependencies

  • Dependencies among Schema Objects
  • Manually Recompiling Views and Packages
  • Manually Recompiling Procedures and Functions

PL/SQL Stored Procedures and Stored Functions

  • Understanding Stored Procedures
  • Benefits and types of Stored Procedures
  • Knowing about Functions

Subprograms and Triggers

  • Advantages of Subprograms
  • Declaring Nested Subprograms
  • Database Triggers
  • Autonomous Triggers

Packages and Anonymous Blocks

  • Advantages of PL/SQL Packages
  • Understanding the Package Body
  • Overview of Product-Specific Packages

PL/SQL Collections and Records

  • Associative arrays
  • Nested Tables
  • Variable arrays
  • Choosing which PL/SQL Collection Types to use
  • Managing Collection Types
Course Duration

2 days