Best Oracle PLSQL Training In Chennai

Oracle PLSQL Syllabus:

What is PLSQL?

PLSQL is similar to writing code for C, C++. You have to deal with loops, conditional statements(mostly),array and much more. The backend communicates with the front-end, sending and receiving information to be displayed as a web page. Pl/SQL helps creating business logic that helps in backend work, main logic with with database which is created by PL/SQL and node JS(back end tech).

Advantages of PLSQL 

•     High Productivity
•     Strong Security
•     Portable
•     Reduces Network Traffic
•     Intermediate Calculations
•     Error Handling

Defining Variables and Data types 

•  PLSQL Block Structure, Block Type 
•  Using Variables in PLSQL
•  Types of Variables & Naming Rules Variable Initialization 
•  PLSQL variable Non PLSQL variable
•  Scalar variable, Reference Variable
•  Composite variable
•  Recognizing PLSQL Lexical Units 
•  Recognizing Data Types 
•  Using Scalar Data Types 
•  Writing PLSQL Executable Statements 
• Nested Blocks and Variable Scope 
• Good Programming Practices
• Boolean Variables and HANDLING Non-PLSQL Variables DBMS_OUTPUT.PUT_LINE
• %TYPE and %ROWTYPE

Program Structures to Control Execution Flow 

• Conditional Control: IF, IF ELSE, IF ELSE IF Statements
• Conditional Control: CASE Statements
• Iterative Control: Basic Loops 
• Nested loop and Lable
• Iterative Control: Nested Loops
• GOTO  Statement
• Iterative Control: WHILE and FOR Loops 
• Real time Example of program using Conditional statement and 
• Loops

Using Cursors and Parameters 

• Defining the cursor
• When we go for Cursor.
• Type of Cursor
• Defining Static and Dynamic Cursor
• Implicit Cursor, Explicit Cursor, Parameterized Cursor
• Introduction to Explicit Cursors 
• Cursor Attributes , Which attributes cannot used in Implicit cursor
• Declare the cursor, Open the Cursor, FETCH the Data from Cursor, Close the Cursor 
• Cursor FOR Loop
• Cursors with Parameters 
• ROWCOUNT using in DML operation inside the block.
• FOR CURSOR
• Using Cursors for UPDATE and WHERE CURRENT  OFF
• Using Multiple Cursors

Exception Handling

• Understand the Exception
• Handle exception in PLSQL Blocks.
• Type of Exception
• Implementation of Exception
• Describe Raise_application_error Proceduce
• Oracle Error range, User Error Range
• Pragma_exception_int Procedure

Create the Stored Procedure

• Syntax for creating, Dropping, Replace and Executing the Stored Procedure
• Procedure with parameter using (IN, OUT, IN OUT)
• Difference between Stored procedure and Anonymous block 
• Invoking Procedure with another procedure
• Ways to Execute the procedure
• Implementation with business logic
• Handling the Exception, Cursor inside the  Stored Procedure
• Fixed notataion, named notataion, Mixed notation
• View the Meta Data information about the Procedure in Data Dictionary table
• User_Procedures 

Create Stored Function

• Syntax for Create, Execute, Drop the Function
• Calling of user-defined function in SQL statement
• Invoking Stored function in another Stored procedure 
• Advantage of using stored function
• Data Dictionary Table Stored Function
• How PLSQL Function returns more than one value
• Difference between Stored Procedure and Stored Function
• View the Meta Data Information about Function in Data Dictionary table

Package

• Reason to use Package
• Create the package
• Identify the advantage of Package
• Describing the package
• Components in package
• Global variable
• Forward Declaration, and Procedure overloading
• Create The Package specification and Package Body
• Implement the Business logic in the Package
• Implements the Cursor and Exception in the Package efficiently 
• View the source code using the data dictionary table  
• Compile and Drop the Package

Trigger

• Describe the trigger
• Purpose of trigger
• Identify the trigger events type and body
• Type of triggers
• Syntax for creating Row level trigger and statement level trigger 
• And difference 
• Instead of trigger
• Create the DDL, Database(logon and logoff) trigger
• Create and Drop the trigger
• View the Meta data information about the trigger 
• Mutating error
• How to avoid the mutation error
• Create the Compound trigger to avoid the mutating error
• Describe the Pragma_autonomous_transcation
• Using data dictionary ALL_TRIGGERS
• Manage dependencies 
• Overview of Schema object dependencies  
• Finding out the Object Dependencies using the USER_DEPENDENCIES
• Invalidation of object dependencies

Data Dictionary table usage 

• USER_OBJECT, ALL_OBJECT, DBA_OBJECT
• USER_ERRORS, ALL_ERRORS, DBA_ERRORS
• USER_SOURCE
• USER_TABLES
• USER_VIEWS
• USER_SEQUENCES
• USER_SYNONYMNS
• USER_INDEXES

Advanced PLSQL

Oracle Supplied Package

• Create UTL_FILE to import and export the file as CSV, TXT file Tab separated file
• DBMS_PROFILER to debug the Procedure, Package

Collection

• Overview of collection 
• Composite data type, Collection data type
• Types of collection
• Associate array
• Nested table
• Varray
• Real time implementation using the collection
• Collection Methods
• Multiset Operator
• Describing Assignment and Equality test
• Object, Records High level data type
• Use collection efficiently 

Bulk Collect

• Overview of Bulk collect
• Understanding how  Context  Switching reduce using the bulk collect
• Advantage of Bulk Collect
• Bulk collect and Bulk Bind using the FORALL
• Bulk inserting can archive using the bulk Bind
• Difference between FOR and FORALL
• Use the save exception to handle the runtime error in the collection
• Ref cursor
• When do we need Ref Cursor with practical example
• Advantage of ref cursor
• Type of ref cursor
• Strong Ref cursor, Weak Ref Cursor and SYS_REFCURSOR

Materialized View

• How it different form View
• Create, Refresh, Drop – Usage
• Types of Refresh
• When we need View, When we need materialized view

Performance tuning 

• DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
• DBMS_UTILITY.FORMAT_ERROR_STACK;
• Creating the index
• Using the BULK collect