Home>> IT Training>>Oracle >>Oracle-PL/Sql

ORACLE - PL/SQL
Program Highlights
PL/SQL is an imperative 3GL that was designed specifically for the seamless processing of SQL commands. 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 and runs within the Oracle executable. It automatically inherits the robustness, security, and portability of Oracle Database.
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. This tutorial will give you great understanding on PL/SQL to proceed with Oracle database and other advanced RDBMS concepts.
Professionals aspiring to launch their careers in software development with cutting edge technology.<

    This coursel is designed for Software Professionals, who are willing to learn PL/SQL Programming Language in simple and easy steps. This tutorial will give you great understanding on PL/SQL Programming concepts, and after completing this coursel, you will be at intermediate level of expertise from where you can take yourself to higher level of expertise.
     
This course in designed to help people move to the popular software development environment – using Oracle PL/SQL. The Objective of the course is to introduce the student to programming concepts in RDBMS (Relational database Management system ).
Before proceeding with this coursel, you should have a basic understanding of software basic concepts like what is database, source code, text editor and execution of programs, etc. If you already have understanding on SQL and other computer programming language then it will be an added advantage to proceed.
  • Introduction to PL/SQL
  • Explain the need for PL/SQL
  • Explain the benefits of PL/SQL
  • Identify the different types of PL/SQL blocks
  • Output messages in PL/SQL
  • Declaring PL/SQL Variables
  • Recognize valid and invalid identifiers
  • List the uses of variables, declare and initialize variables, use bind variables
  • List and describe various data types using the %TYPE attribute
    Writing Executable Statements
  • Identify lexical units in a PL/SQL block
  • Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions
  • Create PL/SQL executable blocks using DML and transaction control statements
  • Make use of the INTO clause to hold the values returned by a SQL statement
  • Writing Control Structures
  • Identify the uses and types of control structures (IF, CASE statements and expressions)
  • Construct and identify loop statements
  • Create a record with the %TYPE and %ROWTYPE attribute
  • Using Explicit Cursors
    Distinguish between usage of implicit and explicit cursors, use SQL cursor attributes
    Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data
    Declare and use cursors with parameters
    Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause
  • Handling Exceptions
    Define PL/SQL exceptions
    Recognize unhandled exceptions
    Handle different types of exceptions
  • Creating Stored Procedures and Functions
    Differentiate between anonymous blocks and subprograms
    Create a simple procedure and invoke it from an anonymous block
    Create a simple function
    Create a simple function that accepts a parameter
  • Differentiate between procedures and functions
  • Creating Procedures
    Differentiate between anonymous blocks and subprograms, use a modularized and layered subprogram design, and identify the benefits of subprograms
    Create a simple procedure and invoke it from an anonymous block
    Work with procedures
    Handle exceptions in procedures, remove a procedure, and display a procedure's information
  • Creating Functions
    Differentiate between a procedure and a function
    Describe the uses of functions
    Work with functions (create, invoke and remove functions)
  • Creating Packages
    Identify the benefits and the components of packages
    Work with packages (create package specification and body, invoke package subprograms,
    remove a package and display package information)
    Working with Packages
  • Overload package subprograms
  • Creating Triggers
    Describe different types of triggers and their uses
    Create database triggers
    Manage triggers
    Creating Compound, DDL, and Event Database Triggers
    Create triggers on DDL statements
    Create triggers on system events
  • 6 weeks
  • 2 hr a day –including Theory and Practical