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
• Ref. Cursors
• Record and PL/SQL Table Types
• 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
• Ref. 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
• 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
• 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
• Row Level Triggers
• Statement Level Triggers
• DDL Triggers
• Trigger Auditing
File Input/Output
• PL/SQL file I/O (Input/Output)
• Using UTL_FILE Package
• 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 Abstract Data type Attributes
• 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 Abstract 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
• 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
• 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
• 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 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