PL SQL Online Training

About the PL SQL Training

PL/SQL is an abbreviation for the Procedural Training/Structured Query Language that involves the procedural language components like conditions and loops. The loops and conditions in this training inhibit declaration of variables and constants, functions and procedures and helps in handling the run-time errors. With PL SQL online training, you can create and define, functions, variables, constants, procedures, triggers and sorts for different applications which are utilized by the Oracle Database informatics interfaces.

Objectives of the Course

• Declaration of character Functions
• Learn ways to process Hierarchies
• Working in Oracle Database Environment
• Ordering the Output
• Conditional Retrieval of Data
• Defining and working with Pseudo Columns and Functions

Who can prefer this course:

It is an ideal course for

• SQL and Analytics Professionals
• Database Administrators
• Software Developers
• BI and Data Warehousing Professionals
• Those are showing interest towards a career in Oracle Database PL/SQL

PL SQL Course Content

 PL-SQL (Procedure Language – SQL) :

• Introduction to Programming Languages
• Introduction to PL/SQL
• The Advantages of PL/SQL
• PL/SQL Architecture
• PL/SQL Data types
• Variable and Constants
• Using Built_in Functions
• Conditional and Unconditional Statements
• Simple if, if… else, nested if..else, if..else Ladder
• Selection Case, Simple Case, GOTO Label   and EXIT
• Iterations in PL/SQL

• Simple LOOP,WHILE LOOP,FOR LOOP   and NESTED LOOPS
• SQL within PL/SQL
• Composite Data types (Complete)
• Cursor Management in PL/SQL
• Implicit Cursors
• Explicit Cursors
• Cursor Attributes
• Cursor with Parameters
• Cursors with LOOPs Nested Cursors
• Cursors with Sub Queries
• Cursors
  • Record and PL/SQL Table Types

 Advanced PL/SQL

• Procedures in PL/SQL :
• STORED PROCEDURES
• PROCEDURE with Parameters (IN,OUT  and IN OUT)
• POSITIONAL Notation and NAMED Notation
• Procedure with Cursors
• Dropping a Procedure

• Functions in PL/SQL :
• Difference between Procedures and Functions
• User Defined Functions
• Nested Functions
• Using stored function in SQL statements
  • Packages in PL/SQL :
  • Creating PACKAGE Specification and  PACKAGE Body
  • Private and Public Objects in PACKAGE

 EXCEPTIONS in PL/SQL :

• Types of exceptions :
• User Defined Exceptions
• Pre Defined Exceptions
• RAISE_APPLICATION_ERROR
• PRAGMA_AUTONOMOUS_TRANSACTION
• SQL Error Code Values

 Database Triggers in PL/SQL :

• Types of Triggers
• Row Level Triggers
• Statement Level Triggers
• DDL Triggers
• Trigger Auditing

 File Input/Output :

• PL/SQL file I/O (Input/Output)
• Using UTL_FILE Package

 Implementing Object Technology

• What is Object Technology ?
• OOPS-Object Instances
• Creation of objects
• Creating User Defined Data Types
• Creating Object Tables
• Inserting rows in a table using Objects
• Retrieving data from Object based Tables
• Calling a Method
• Indexing Abstact Data type Attributes

 Using LOBS

• Large Objects (LOBS)
• Creating Tables-LOB
• Working with LOB values
• Inserting, Updating & Deleting Values in LOBs
• Populating Lobis DBMS_LOB Routines
• Using B-FILE

 Using Collections

• Advantages of collection
• Ref cursor (Dynamic Cursor)
• Weak ref cursor
• Strong ref cursor
• Nested Tables VARRAYS or VARYING arrays
• Creating tables using nested tables
• Inserting, updating & deleting Nested
• Table records
• Nested table in PL/SQL

Oracle Database Architecture

• Introduction to Oracle Database Architecture
• Physical structures Logical structures
• DB Memory Structures Background Process
• 2 Tire, 3 Tire, N-Tier Architecture

 Advanced Features

• 9i Joines
• New Date function
• Rename column
• Inner Join/Natural Join
• Left Outer Join/Right Outer Join
• Full Outer Join
• Multiple Inserts
• Insert All Command
• Merge statement
• NVL2(), NULLIF(), COALESCE()
• CASE expression of Select Command
• Temporary Tables/Global Tables
• New Function EXTRACT()
• Autonomous Traction
• Pragma _ Autonomous_ Transaction()
• Bulk Collect
• About Flash Back Queries
• Dynamic SQL
• New data types, Flash back Command
• Purge Command, Recyclebin
• Regular expressions, DML Error Logging
• Data Pump, Virtual Columns
• Read only tables, Cross tab Views using
• Pivot/Unpivot operators, Follows Clause
• Compound triggers, New data types

 DBA CONCEPTS

• Database
• Tablespace
• Types of tablespaces
• Data files /Se