Oracle SQL PL/SQL Course Contents

Online Training Course Contents for Oracle SQL PLSQL

Oracle SQL PL/SQL Course Contents – Join Now

The Oracle SQL PL/SQL Course is designed for developers. The developers who wants to store the application data in Oracle database.

Creating Views in Oracle Database  – Watch Introduction Lecture

  • Introduction to Oracle SQL PL/SQL
  • Describe a view. Create, alter the definition, and drop a view.
  • Retrieve, Insert, update and delete data through a view
  • Create and use an inline view
  • Perform Top ‘N’ Analysis

Oracle SQL PL/SQL Syntax and Logic – Watch Video on Variables

  • PL/SQL Blocks and Programs
  • Conditional Statements – IF/THEN and CASE
  • Comments and Labels
  • WHILE and FOR Loops

Lab assignment : logic, statement and breaking the statement

Stored Procedures and Functions – Watch Video

  • Stored Subprograms
  • Define Procedures and Functions
  • Creating a Stored Procedure
  • Calling a Stored Procedure
  • Passing Parameters and Default Arguments
  • Parameter Modes
  • Creating a Stored Function
  • Calling a Stored Function
  • Stored Functions and SQL
  • Local Procedures and Functions

Exception Handling in Oracle SQL PL/SQL

  • SQLCODE and SQLERRM
  • Exception Handlers
  • Nesting Blocks
  • Scope and Name Resolution
  • User-Defined Exceptions
  • Compile-Time Warnings

Cursors in Oracle SQL PL/SQL

  • Multi-Row Queries
  • Declaring and Opening Cursors
  • Fetching Rows
  • Closing Cursors
  • The Cursor FOR Loop
  • FOR UPDATE Cursors
  • Cursor Parameters
  • The Implicit (SQL) Cursor

Maintaining Data Integrity 

  • Implement data integrity constraints, Maintain integrity constraints
  • Obtain constraint information from the data dictionary

Query Processing

  • Query decomposition and Query optimization
  • Code generation
  • Runtime query execution
  • Rule-Based Optimization
  • Cost-Based Optimization

When to Use Specific Constructs

  • EXISTS Is Preferable to DISTINCT
  • WHERE Versus HAVING
  • UNION Versus UNION ALL
  • LEFT Versus RIGHT OUTER JOIN

Avoid Unnecessary Parsing

  • Using Bind Variables
  • Using Table Aliases

SQL Transformations

  • Simple view merging
  • Complex view merging
  • Subquery “flattening”
  • Transitive predicate generation
  • Common subexpression elimination
  • Predicate pushdown and pullup
  • Outer-join to inner join conversion

Cost-based query transformations

  • Materialized view rewrite
  • OR-expansion
  • Star transformation
  • Predicate pushdown for outer-joined views

Access path selection

  • Join ordering
  • Bitmap indexes and Bitmap join indexes
  • Domain indexes and extensibility
  • Fast full index scans
  • Index joins
  • Index skip scans
  • Partition optimizations
  • Partition-wise joins, GROUP-BY’s and sorts
  • Sort elimination
  • OLAP optimizations
  • Parallel execution
  • Hints

Cost Model and Statistics

  • Optimizer statistics
    • Object-level statistics
    • System statistics
    • User-defined statistics
  • Statistics management
    • Automatic statistic gathering
    • Parallel sampling
    • Monitoring
    • Automatic histogram determination
    • Dynamic sampling.
  • Optimization cost modes.

Dynamic Runtime Optimizations

  • Dynamic degree of parallelism and Dynamic memory allocation.
  • Database resource manager

Ranking Functions

  • RANK, DENSE_RANK and ROW_NUMBER
  • Handling NULLs
  • Top/bottom N queries
  • FIRST/LAST
  • NTILE
  • WIDTH_BUCKET
  • CUME_DIST and PERCENT_RANK
  • Hypothetical Functions

Join Now

November 14, 2018

0 responses on "Oracle SQL PL/SQL Course Contents"

Leave a Message

Your email address will not be published. Required fields are marked *

Featured Testimonial

I think the course was excellent for someone like me (Web Designer working on always improving...Read more

Karen Cadman

Sr. Web Designer

Certificate Code

Who’s Online

There are no users currently online

Last Tweets

top
X