Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Trainings
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation to enhance the capabilities of SQL. It provides specific syntax for this purpose and supports exactly the same datatypes as SQL. Server-side PL/SQL is stored and compiled in Oracle Database along with SQL itself and Java and runs within the Oracle executable. Gain expertise in developing performance specific queries only at apps2fusion.


Course Contents

Day 1

Retrieving Data Using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements
• Generate a report of data from the output of a basic SELECT statement
• Select All Columns
• Select Specific Columns
• Use Column Heading Defaults
• Use Arithmetic Operators
• Understand Operator Precedence
• Learn the DESCRIBE command to display the table structure

Restricting and Sorting Data

• Write queries that contain a WHERE clause to limit the output retrieved
• List the comparison operators and logical operators that are used in a WHERE clause
• Describe the rules of precedence for comparison and logical operators
• Use character string literals in the WHERE clause
• Write queries that contain an ORDER BY clause sort the output of a SELECT statement
• Sort output in descending and ascending order

Using Single-Row Functions to Customize Output

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the date functions

Using Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions

• Use the aggregation functions in SELECT statements to produce meaningful reports
• Create queries that divide the data in groups by using the GROUP BY clause
• Create queries that exclude groups of date by using the HAVING clause

 

Day 2

Displaying Data From Multiple Tables

• Write SELECT statements to access data from more than one table
• View data that generally does not meet a join condition by using outer joins
• Join a table by using a self join

Using Sub-queries to Solve Queries

• Describe the types of problem that sub-queries can solve
• Define sub-queries
• List the types of sub-queries
• Write single-row and multiple-row sub-queries

Using the SET Operators

• Describe the SET operators
• Use a SET operator to combine multiple queries into a single query
• Control the order of rows returned when using the SET operators

Manipulating Data

• Describe each DML statement
• Insert rows into a table with the INSERT statement
• Use the UPDATE statement to change rows in a table
• Delete rows from a table with the DELETE statement
• Save and discard changes with the COMMIT and ROLLBACK statements
• Explain read consistency

Using DDL Statements to Create and Manage Tables

• Categorize the main database objects
• Review the table structure
• List the data types available for columns
• Create a simple table
• Decipher how constraints can be created at table creation
• Describe how schema objects work

Creating Other Schema Objects

• Create a simple and complex view
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

 

Day 3

Controlling User Access

• Differentiate system privileges from object privileges
• Grant privileges on tables
• Grant roles
• Distinguish between privileges and roles

Managing Schema Objects

• Add constraints
• Create indexes
• Create indexes using the CREATE TABLE statement
• Create function-based indexes
• Drop columns and set column UNUSED
• Create and use external tables

Retrieving Data Using Sub-queries

• Write a multiple-column sub-query
• Solve problems with correlated sub-queries
• Update and delete rows using correlated sub-queries
• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause

Introduction to PL/SQL

• PL/SQL Overview
• Benefits of PL/SQL Subprograms
• Overview of the Types of PL/SQL blocks
• Creating and Executing a Simple Anonymous Block
• Generating Output from a PL/SQL Block

Declaring PL/SQL Identifiers

• Different Types of Identifiers in a PL/SQL subprogram
• Using the Declarative Section to Define Identifiers
• Storing Data in Variables
• Scalar Data Types
• %TYPE Attribute
• Bind Variables
• Using Sequences in PL/SQL Expressions

Writing Executable Statements

• Describing Basic PL/SQL Block Syntax Guidelines
• Commenting Code
• SQL Functions in PL/SQL
• Data Type Conversion
• Nested Blocks
• Operators in PL/SQL

 

Day 4

Interacting with the Oracle Server

• Including SELECT Statements in PL/SQL to Retrieve data
• Retrieving Data in PL/SQL with the SELECT Statement
• The SQL Cursor concept
• Avoiding Errors by Using Naming Conventions When Using Retrieval and DML Statements
• Manipulating Data in the Server Using PL/SQL
• Understanding the SQL Cursor concept
• Using SQL Cursor Attributes to Obtain Feedback on DML
• Saving and Discarding Transactions Writing Control Structures
• Conditional processing Using IF Statements
• Conditional processing Using CASE Statements
• Simple Loop Statement
• While Loop Statement
• For Loop Statement
• The Continue Statement

Working with Composite Data Types

• Using PL/SQL Records to Hold Multiple Values of Different Types
• Using the %ROWTYPE Attribute
• Inserting and Updating with PL/SQL Records
• INDEX BY Tables to Hold Multiple Values of the Same Data Type
• INDEX BY Table Methods
• INDEX BY Table of Records

Using Explicit Cursors

• Understanding Explicit Cursors
• Declaring the Cursor
• Opening the Cursor
• Fetching data from the Cursor
• Closing the Cursor
• Cursor FOR loop
• The %NOTFOUND and %ROWCOUNT Attributes
• FOR UPDATE Clause and WHERE CURRENT Clause

Handling Exceptions

• Understanding Exceptions
• Handling Exceptions with PL/SQL
• Trapping Predefined Oracle Server Errors
• Trapping Non-Predefined Oracle Server Errors
• Trapping User-Defined Exceptions
• Propagate Exceptions
• RAISE_APPLICATION_ERROR Procedure

Creating Stored Procedures

• Creating a Modularize and Layered Subprogram Design
• Modularizing Development With PL/SQL Blocks
• Understanding the PL/SQL Execution Environment
• The Benefits of Using PL/SQL Subprograms
• The Differences Between Anonymous Blocks and Subprograms
• Creating, Calling, and Removing Stored Procedures Using the CREATE Command and SQL Developer
• Using Procedures Parameters and Parameters Modes
• Viewing Procedures Information Using the Data Dictionary Views and SQL Developer

 

Day 5

Creating Stored Functions and Debugging Subprograms

• Creating, Calling, and Removing a Stored Function Using the CREATE Command and SQLDeveloper
• Identifying the Advantages of Using Stored Functions in SQL Statements
• Identify the steps to create a stored function
• Using User-Defined Functions in SQL Statements
• Restrictions When Calling Functions from SQL statements
• Controlling Side Effects When Calling Functions from SQL Expressions
• Viewing Functions Information
• Debugging Functions and Procedures

Creating Packages

• Listing the Advantages of Packages
• Describing Packages
• The Components of a Package
• Developing a Package
• The Visibility of a Package’s Components
• Creating the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
• Invoking the Package Constructs
• Viewing the PL/SQL Source Code Using the Data Dictionary

Working With Packages

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

Creating Triggers

• Working With Triggers
• Identifying the Trigger Event Types and Body
• Business Application Scenarios for Implementing Triggers
• Creating DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
• Identifying the Trigger Event Types, Body, and Firing (Timing)
• Statement Level Triggers Versus Row Level Triggers
• Creating Instead of and Disabled Triggers
• Managing, Testing, and Removing Triggers

Performance Tuning Fundamentals

Scalability

Cost Based Optimizer (CBO)

SQL Processing

Writing Scalable SQL

Hints

 



Enroll



Training Hours

Start Date :  1st June 2019

Training Schedule:  1, 2, 8, 9 & 15th June 2019

Timing: 12:00 NOON GMT | 08:00AM EST | 5:00AM PST | 7:00AM CST | 6:00AM MST | 5:30PM IST | 01:00PM GMT+1



Audience



Training Administrator

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner