Oracle PL SQL Online Training

(Updated: Mar-29-2018 )Max Troy
Oracle PL-SQL Online Training

SQL is a structured query language. Databases access information through SQL. PL SQL is the procedural language extension to SQL; it is a programming language just like C++, JAVA, Pascal, etc. It is a server-side and easy to use language. The basic units that make up PL/SQL program can be nested inside one another; the units include functions, procedures, and anonymous blocks.

It is an imperative 3GL that was designed for seamless programming of SQL commands.

❯ DBMS concepts

  • Define database
  • Define database system
  • What is DBMS
  • What is RDBMS
  • What is ORDBMS
  • Difference between DBMS, RDBMS, and ORDBMS

❯ SQL

  • Define SQL
  • DDL commands
  • DML command
    • Insert
  • SQL SELECT statements
    • Operators
    • Distinct clause
    • Order by clause
    • Comparison conditions
  • Constraints
    • Primary key constraint
    • Check constraint
    • Unique constraint
    • Cascade constraint
    • Foreign key constraint
    • Not null constraint
    • Cascade constraint
  • Joins
    • Define join
    • Cartesian join
    • Equi and non-equi join
    • Outer joins
  • Functions
    • General functions
    • Numeric functions
    • Date functions
    • Conversion functions
    • Character functions
    • Aggregation of data via group functions
  • Sub-queries
    • Simple
    • Multiple row
  • Delete
    • Data deletion
    • Deletion using join
    • Deletion using sub-queries
  • Update
    • Updating data
    • Updating using sub-queries
  • Database objects
    • Synonym
    • Index
    • View
    • Privilege
    • Sequence
  • Privileges
    • Revoke
    • Grant
  • View
    • Simple
    • In line
    • Complex
  • Synonym
    • Private
    • Public

❯ PL/SQL

❯ Introduction

  • Overview
  • Sub-programs
  • Benefits of sub-programs
  • Types o PL/SQL blocks
  • Simple anonymous block
  • Generating output

❯ Block structure of PL/SQL

Identifiers

  • Types of identifiers
  • Using declarative section to define identifiers
  • %TYPE attribute
  • Scalar data types identification
  • Bind variables definition
  • Sequences in PL/SQL expressions

❯ Executable statements

  • Basic guidelines of PL/SQL block syntax
  • Nested blocks
  • Conversion of data types
  • Comment code
  • Operators identification

❯ Iterative statements

  • Simple loop
  • For loop
  • While loop

❯ Interaction with Oracle server

  • Invoking SELECT statements in PL/SQL to retrieve data
  • SQL cursor concept
  • Save transactions
  • Discard transactions
  • SQL cursor attributes

❯ Composite data types

  • Using records of PL/SQL
  • Insert and update with PL/SQL records
  • %ROWTPE attribute
  • Examining INDEX BY table of records
  • Associative arrays

❯ Control structures

  • Conditional processing
    • Using IF statements
    • Using CASE statements
  • Simple loop statement
  • WHILE loop statement
  • FOR loop statement
  • Description of continue statement

❯ Cursor management

  • Define cursor
  • Implicit cursor
  • Explicit cursor
  • Cursor with FOR loop
  • Cursor with parameters

❯ Exception

  • Define an exception
  • Types of exception
    • Predefined exception
    • User-defines exception

❯ Exception handling

  • What are exceptions
  • Handling exceptions with PL/SQL
  • Propagating exceptions
  • RAISE_APPLICATION_ERROR procedure
  • Trapping errors
    • User-defined errors
    • Predefined Oracle server errors
    • Non-predefined server errors

❯ Stored procedures and functions

  • Introduction
  • Subprograms and anonymous blocks
  • Simple procedure
    • Creation
    • Execution
  • Simple function execution

❯ Create packages

  • What is a package
  • Benefits
  • Components
  • Developing of a package
  • Invoking package constructs
  • Enabling visibility of package’s components
  • Viewing PL/SQL source code

❯ Dynamic SQL

  • Introduction
  • Execution flow
  • Cursor variables declaration
  • PL/SQL’s dynamic execution
  • Invoking DBMS_SQL package
  • Dynamic SQL function completeness

❯ Design tips and techniques

  • NOCOPY hint theory
  • Format of standard packages in an application

❯ Describe triggers

  • Trigger event types identification
  • Trigger event body identification
  • Identification of timing of
    • Trigger event types
    • Trigger event body
    • Trigger event firing
  • Business application scenarios
  • Statement-level triggers vs. row-level triggers
  • Managing triggers
  • Removing triggers
  • Testing triggers

 

Oracle PL/SQL Online Training Placement in Various Cities: