ORACLE PL/SQL TRAINING

PL/SQL Overview

• Features and Advantages of PL/SQL
• Basic Syntax of PL/SQL
• The PL/SQL Identifiers
• Delimiters
• Comments and PL/SQL Program Units

Data Types

• PL/SQL Scalar Data Types and Subtypes
• Numeric Data Types and Subtypes
• Character Data Types and Subtypes
• Boolean Data Types
• Date time and Interval Types
• Large Object (LOB) Data Types
• User-Defined Subtypes and NULLs in PL/SQL

Variables & Constants

• Variable Declaration
• Initializing Variables and Variable Scope in PL/SQL
• Assigning SQL Query Results to PL/SQL Variables
• Declaring a Constant and The PL/SQL Literals

Operators

• Different Operators in PL/SQL are Arithmetic
• Different Operators in PL/SQL are Arithmetic
• Relational
• Comparison
• Logical
• LIKE
• BETWEEN
• IN and IS NULL Operators
• PL/SQL Operator Precedence

Conditions

• Loops and Reverse of LOOP Statements
• Labeling a PL/SQL Loop and The Loop Control Statements
• Syntax: The EXIT WHEN Statement.
• Syntax: Restrictions with GOTO Statement

Strings & Arrays

• Declaring String Variables PL/SQL String Functions and Operators Creating a Varray Type

Procedures

• Parts of a PL/SQL Subprogram
• Creating a Procedure, Executing and Deleting a Standalone Procedure
• Parameter Modes in PL/SQL Subprograms
• IN & OUT Mode Example 1 and IN & OUT Mode Example 2
• Methods for Passing Parameters are POSITIONA, NAMED and MIXED NOTATION

Functions & Records

• Calling a Function and PL/SQL Recursive Functions
• Cursors, Implicit and Explicit Cursors
• Declaring, Opening, Fetching and Closing the Cursor
• Defining a Record
• Table-Based, Cursor-Based and User-Defined Records
• Accessing Fields and Records as Subprogram Parameters

Exceptions & Triggers

• Syntax for Exception Handling
• Raising, User-defined and Pre-defined Exceptions
• Creating Triggers and Benefits of Triggers
• Triggering a Trigger

Packages & Collections

• Package Specification, Package Body
• Using the Package Elements:
• THE PACKAGE SPECIFICATION
• CREATING THE PACKAGE BODY
• USING THE PACKAGE
• Index-By Table and Nested Tables
• Collection Methods and Exceptions

Transactions

• Starting and Ending a Transaction, Committing and Rolling Back Transactions
• Save points, Automatic Transaction Control
• Date & Time
• Field Values for Date time and Interval Data Types
• The Date time Data Types and Functions
• The Interval Data Types and Functions
• DBMS Output and DBMS_OUTPUT Subprograms
• Object Oriented and Instantiating an Object
• Member Methods, Using Map method and Using Order method
• Inheritance for PL/SQL Objects and Abstract Objects in PL/SQL