• Oracle PL/SQL

Oracle PL SQL Training Syllabus

Chapter 1 - Retrieving Data Using the SQL SELECT Statement

• The SELECT Statement
• The SELECT and FROM Clauses
• Conditions and the WHERE Clause
• Other Conditional Operators
• logical operators

Chapter 2 - Restricting and Sorting Data

• The ORDER BY Clause
• Column Aliases
• Arithmetic Expressions
• Precedence of Operators

Chapter 3 - Using Single-Row Functions to Customize Output

• Function Types
• Using Table dual to try out Functions
• Numeric Functions
• Character Functions
• String Concatenation
• Date Arithmetic and Date Functions

Chapter 4 - Using Conversion Functions and Conditional Expressions

• Conversion Functions
• NVL and NVL2
• DECODE
• CASE Expressions
• COALESCE and NULLIF

Chapter 5 - Reporting Aggregated Data Using the Group Functions

• SUM()
• COUNT()
• MIN()
• MAX()
• AVG()

Chapter 6 - Displaying Data from Multiple Tables

• Overview of Table Joins
• Inner Joins
• Table Aliases
• Outer Joins
• Self Joins
• ANSI Standard Joins

Chapter 7 - Using Sub queries to Solve Queries

• Overview of Subqueries
• Simple Subqueries
• Complex Subqueries
• Subquery Rules
• Using the ANY, ALL and IN Operators

Chapter 8 - Using the Set Operators

• UNION ,UNION ALL,INTERSECT and MINUS

Chapter 9 - Manipulating Data

• Inserting Rows
• Updating Rows
• Deleting Rows
• Verifying Updates
• Transaction Control
• Commit and Rollback
• Save points
• Commits and Constraints
• Amending Data in SQL Developer

Chapter 10 - Using DDL Statements to Create and Manage Tables

• Creating Tables
• Specifying Constraints
• Altering Tables, Columns and Constraints
• Dropping Tables, Columns and Constraints
• Copying Tables
• Data dictionary

Chapter 11 - Creating Other Schema Objects

• Creating Indexes
• Dropping Indexes
• Listing Indexes
• Creating and Using Views
• Dropping Views
• Listing Views
• Create a Sequence
• View Sequence Details
• Create a Synonym
• List Synonyms

Oracle11g content

• Introduction to PL/SQL
• What is PL/SQL
• PL/SQL Environment
• Benefits of PL/SQL
• Overview of the Types of PL/SQL blocks
• Create and Execute a Simple Anonymous Block
• Generate Output from a PL/SQL Block

Declaring PL/SQL Identifiers

• Identify the Different Types of Identifiers in a PL/SQL subprogram
• Use the Declarative Section to Define Identifiers
• List the Uses for Variable
• Store Data in Variables
• Declare PL/SQL Variables

Writing Executable Statements

• Describe Basic Block Syntax Guidelines
• Use Literals in PL/SQL
• Customize Identifier Assignments with SQL Functions
• Use Nested Blocks as Statements
• Reference an Identifier Value in a Nested Block
• Qualify an Identifier with a Label
• Use Operators in PL/SQL
• Use Proper PL/SQL Block Syntax and Guidelines

Interacting with the Oracle Server

• Identify the SQL Statements You Can Use in PL/SQL
• Include SELECT Statements in PL/SQL
• Retrieve Data in PL/SQL with the SELECT Statement
• Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
• Manipulate Data in the Server Using PL/SQL
• The SQL Cursor concept
• Use SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions

Writing Control Structures

• Control PL/SQL Flow of Execution
• Conditional processing Using IF Statements
• Conditional Processing CASE Statements
• Handle Nulls to Avoid Common Mistakes
• Build Boolean Conditions with Logical Operators
• Use Iterative Control with Looping Statements

Working with Composite Data Types

• Learn the Composite Data Types of PL/SQL Records and Tables
• Use PL/SQL Records to Hold Multiple Values of Different Types
• Inserting and Updating with PL/SQL Records
• Use INDEX BY Tables to Hold Multiple Values of the Same Data Type

Using Explicit Cursors

• Cursor FOR Loops Using Sub-queries
• Increase the Flexibility of Cursors By Using Parameters
• Use the FOR UPDATE Clause to Lock Rows
• Use the WHERE CURRENT Clause to Reference the Current Row
• Use Explicit Cursors to Process Rows
• Explicit Cursor Attributes
• Cursors and Records

Handling Exceptions

• Handling Exceptions with PL/SQL
• Predefined Exceptions
• Trapping Non-predefined Oracle Server Errors
• Functions that Return Information on Encountered Exceptions
• Trapping User-Defined Exceptions
• Propagate Exceptions
• Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications

Creating Stored Procedures and Functions

• Differences Between Anonymous Blocks and Subprograms
• Creating and invoking Procedure
• Creating and invoking Functions
• Passing Parameter to the Function

Packages

• What are packages
• Advantages of packages
• Examples on packages

Triggers

• What are triggers?
• Types of triggers.
• Advantages of triggers