CCNA training in Kochi | CCNP training in kochi www.emigonetworks.com | Emigo Networks | CCIE Training in Kochi, Cochin, Kerala | CCIE, CCNP, CCNA, MCSE, MCITP
 

Oracle PL/SQL Training

Oracle PL/SQL Training in Cochin at Emigo Networks is the best place to do your Oracle PL/SQL Certification Training because of its practical training with latest updated PL/SQL Course Content.Oracle PL/SQL Training course is designed from basic to advance level topics which suits both beginners and professionals.

  What is Oracle PL/SQL ?

What Oracle PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. Oracle PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency.

.

  ORACLE SQL TRAINING COURSE CONTENT

  SECTION1: INTRODUCTION

  • Describe the features of Oracle Database 12c
  • Describe the salient features of Oracle Cloud 12c
  • Explain the theoretical and physical aspects of a relational database
  • Describe Oracle server€™s implementation of RDBMS and object relational database management system (ORDBMS

  SECTION2: BASIC DATABASE CONCEPT AND SQL

  • Basic history of database concept: DBMS, RDBMS, ORDBMS
  • Advantage of ORACLE database and version information
  • SQL Language overview : DQL, DML, DDL, DCL, TCL
  • What is the usage of ANSI standard
  • SELECT Command – Column Alias Rules, String data
  • Concatenations with various data
  • Null Value handling with number and characters
  • Arithmetic Operator
  • Concatenation Operator
  • Eliminating Duplicate Rows

  SECTION3: RESTRICTING AND SORTING DATA

  • WHERE Clause – Character Strings and Dates, number
  • General Comparison Conditions = >>= <<= <>
  • Other Comparison BETWEEN , IN , LIKE , NULL
  • Logical Conditions AND OR NOT
  • ORDER BY Clause, Sorting by Column Alias , Column Position, Multiple Columns

  SECTION4: SINGLE-ROW FUNCTIONS

  • Character Functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, CONCAT, LTRIM, RTRIM, TRIM, REPLACE, TRANSLATE, REVERSE
  • Number Functions: ROUND, TRUNC, MOD, POWER, CEIL , FLOOR, ABS
  • Dates Functions: SYSDATE, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ADD_MONTHS, ROUND, TRUNC, Arithmetic on Date
  • Conversion Functions: Implicit Data-Type Conversion & Explicit Data-Type Conversion, TO_CHAR ,TO_NUMBER ,TO_DATE
  • General Functions: NVL , NVL2 , NULLIF, COALESCE
  • CASE Expression, DECODE
  • Nested function with real-time usage

  SECTION5: JOINS

  • EQUI JOIN / SIMPLE JOIN / NORMAL JOIN
  • ANSI JOIN, LEFT OUTER, RIGHT OUTER, FULL OUTER
  • NATURAL JOIN, NATURAL OUTER JOINS
  • INNER JOIN, JOIN … USING clause, JOIN … ON clause
  • CROSS JOIN, NON-EQUI JOIN, SELF JOIN
  • ORACLE STANDARD OUTER JOINS

  SECTION6: MULTI-ROW FUNCTIONS

  • Group Functions Rules, SUM, MIN, MAX, COUNT, AVG
  • Creating Groups of Data: GROUP BY Clause
  • Filtering Group Results: The HAVING Clause

  SECTION7: SUB-QUERIES

  • Single-Row Subqueries- Rules, Operators : = >>= <<= <>
  • Null Values in a Subquery
  • Multi-Row Subqueries- Rules, Operators : IN, ANY , ALL

SECTION8: DATA MANIPULATION LANGUAGE DML AND TRANSACTION CONTROL LANGUAGE TCL

  • DML : INSERT, UPDATE, DELETE, MERGE
  • TCL : COMMIT, ROLLBACK, SAVEPOINT

  SECTION9: DATA DEFINITION LANGUAGE – DDL

  • DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE
  • DEFAULT OPTION

  SECTION10: CONSTRAINTS

  • NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
  • Column Level Constraint, Table Level Constraint €“ Naming constraints and usage
  • Adding a Constraint, Dropping a Constraint
  • Disabling Constraints, Enabling Constraints
  • Validating Constraints

  SECTION11: VIEWS

  • Simple Views and Complex Views €“ Create, Drop, Source Code
  • Rules for Performing DML Operations on a View
  • WITH CHECK OPTION , WITH READ ONLY
  • Inline Views
  • Materialized View €“ Create, Refresh, Drop – Usage

  SECTION12: OTHER DATABASE OBJECTS

  • Sequence- NEXTVAL and CURRVAL
  • Index – When to Create an Index, When Not to Create an Index.
  • Synonyms

  SECTION13: DCL COMMANDS

  • Granting / Revoking Privileges

   SECTION14: SET OPERATORS

  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS

   SECTION15: ADVANCED DATE-TIME FUNCTIONS

  • TIME ZONES
  • SYSDATE, SYSTIMESTAMP
  • CURRENT_DATE , CURRENT_TIMESTAMP
  • Storing time zone data in Table

   SECTION16: ADVANCED GROUP BY CLAUSE

  • Group by with ROLLUP AND CUBE
  • GROUPING SETS

   SECTION17: ADVANCED SUBQUERIES

  • Pairwise Comparison Subquery
  • Nonpairwise Comparison Subquery
  • Correlated Subqueries
  • EXISTS , NOT EXISTS Operator

   SECTION18: HIERARCHICAL RETRIEVAL

  • Walking the Tree: From the Bottom Up , From the Top Down
  • LEVEL Pseudo column
  • Connect by prior

   SECTION21: MULTI-TABLE INSERT

  • Unconditional INSERT ALL
  • Conditional INSERT ALL
  • Conditional FIRST INSERT

.

  ORACLE PL/SQL TRAINING COURSE MODULES

   SECTION1: INTRODUCTION TO PL/SQL

  • PL/SQL Overview
  • Benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • Generate Output from a PL/SQL Block

   SECTION2: PL/SQL IDENTIFIERS

  • List the different Types of Identifiers in a PL/SQL subprogram
  • Usage of the Declarative Section to define Identifiers
  • Use variables to store data
  • Identify Scalar Data Types
  • The %TYPE Attribute
  • What are Bind Variables?
  • Sequences in PL/SQL Expressions

   SECTION3: WRITE EXECUTABLE STATEMENTS

  • Describe Basic PL/SQL Block Syntax Guidelines
  • Comment Code
  • Deployment of SQL Functions in PL/SQL
  • How to convert Data Types?
  • Nested Blocks
  • Identify the Operators in PL/SQL

   SECTION5: INTERACTION WITH THE ORACLE SERVER

  • Invoke SELECT Statements in PL/SQL to Retrieve data
  • Data Manipulation in the Server Using PL/SQL
  • SQL Cursor concept
  • Usage of SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions

   SECTION6: CONTROL STRUCTURES

  • Conditional processing Using IF Statements
  • Conditional processing Using CASE Statements
  • Use simple Loop Statement
  • Use While Loop Statement
  • Use For Loop Statement
  • Describe the Continue Statement

   SECTION7: COMPOSITE DATA TYPES

  • Use PL/SQL Records
  • The %ROWTYPE Attribute
  • Insert and Update with PL/SQL Records
  • Associative Arrays (INDEX BY Tables)
  • Examine INDEX BY Table Methods
  • Use INDEX BY Table of Records

   SECTION8: EXPLICIT CURSORS

  • What are Explicit Cursors?
  • Declare the Cursor
  • Open the Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • Cursor FOR loop
  • Explicit Cursor Attributes
  • FOR UPDATE Clause and WHERE CURRENT Clause

   SECTION9: EXCEPTION HANDLING

  • Understand Exceptions
  • Handle Exceptions with PL/SQL
  • Trap Predefined Oracle Server Errors
  • Trap Non-Predefined Oracle Server Errors
  • Trap User-Defined Exceptions
  • Propagate Exceptions
  • RAISE_APPLICATION_ERROR Procedure

   SECTION10: STORED PROCEDURES AND FUNCTIONS

  • Understand Stored Procedures and Functions
  • Differentiate between anonymous blocks and subprograms
  • Create a Simple Procedure
  • Create a Simple Procedure with IN parameter
  • Create a Simple Function
  • Execute a Simple Procedure
  • Execute a Simple Function

   SECTION11: CREATE STORED PROCEDURES

  • Create a Modularized and Layered Subprogram Design
  • Modularize Development With PL/SQL Blocks
  • Describe the PL/SQL Execution Environment
  • Identity the benefits of Using PL/SQL Subprograms
  • List the differences Between Anonymous Blocks and Subprograms
  • Create, Call, and Remove Stored Procedures Using the CREATE Command
  • Implement Procedures Parameters and Parameters Modes

   SECTION12: CREATE STORED FUNCTIONS

  • Create, Call, and Remove a Stored Function Using the CREATE Command and SQL Developer
  • Identity the advantages of Using Stored Functions in SQL Statements
  • List the steps to create a stored function
  • Implement User-Defined Functions in SQL Statements
  • Identity the restrictions when calling Functions from SQL statements
  • Control Side Effects when calling Functions from SQL Expressions
  • View Functions Information

   SECTION13: CREATE PACKAGES

  • Identity the advantages of Packages
  • Describe Packages
  • List the components of a Package
  • Develop a Package
  • How to enable visibility of a Package€™s components?
  • Create the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
  • Invoke Package Constructs
  • View PL/SQL Source Code Using the Data Dictionary

   SECTION14: PACKAGES

  • Overloading Subprograms in PL/SQL
  • Use the STANDARD Package
  • Use Forward Declarations to Solve Illegal Procedure Reference
  • Implement Package Functions in SQL and Restrictions
  • Persistent State of Packages
  • Persistent State of a Package Cursor
  • Control Side Effects of PL/SQL Subprograms
  • Invoke PL/SQL Tables of Records in Packages

   SECTION16: DYNAMIC SQL

  • The Execution Flow of SQL
  • What is Dynamic SQL?
  • Declare Cursor Variables
  • Dynamically executing a PL/SQL Block
  • Configure Native Dynamic SQL to Compile PL/SQL Code
  • Invoke DBMS_SQL Package
  • Implement DBMS_SQL with a Parameterized DML Statement
  • Dynamic SQL Functional Completeness

   SECTION18: TRIGGERS

  • Describe Triggers
  • Identify the Trigger Event Types and Body
  • Business Application Scenarios for Implementing Triggers
  • Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
  • Identify the Trigger Event Types, Body, and Firing (Timing)
  • Statement Level Triggers Versus Row Level Triggers
  • Create Instead of and Disabled Triggers
  • How to Manage, Test, and Remove Triggers?

   SECTION19: CREATE COMPOUND, DDL, AND EVENT DATABASE TRIGGERS

  • What are Compound Triggers?
  • Identify the Timing-Point Sections of a Table Compound Trigger
  • Compound Trigger Structure for Tables and Views
  • Implement a Compound Trigger to Resolve the Mutating Table Error
  • Compare Database Triggers to Stored Procedures
  • Create Triggers on DDL Statements
  • Create Database-Event and System-Event Triggers
  • System Privileges Required to Manage Triggers

   SECTION24: USING COLLECTIONS

  • Overview of collections
  • Use Associative arrays
  • Use Nested tables
  • Use VARRAYs
  • Compare nested tables and VARRAYs
  • Write PL/SQL programs that use collections
  • Use Collections effectively

  Key Features of our Oracle PL/SQL Training in Cochin


RHCE training in kochi | CCIE training in kochi Emigo
All Rights Riserved @ 2017 Emigo Networks
Powerd byHost On Pdl.com