Oracle PL/SQL Training

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

Book Your Course