Oracle PLSQL Training in Chennai - PowerPoint PPT Presentation

About This Presentation

Oracle PLSQL Training in Chennai


Greens Technology is the best Oracle PLSQL Training with 100% Placement Institute in Tambaram Chennai. We offering Oracle PLSQL online, placement, corporate Training around the Chennai. Our syllabus is revised monthly once by our trainers. We provide weekday classes, Weekend classes and Fast track classes in low course fees. – PowerPoint PPT presentation

Number of Views:101


Transcript and Presenter's Notes

Title: Oracle PLSQL Training in Chennai

  • PL/SQL is one of three key programming dialects
    inserted in the Oracle Database, alongside SQL
    itself and Java.PL/SQL to continue with Oracle
    database and other progressed RDBMS ideas.

Facts About PL/SQL
  • PL/SQL is a totally versatile, elite exchange
    preparing dialect.
  • PL/SQL gives an implicit, deciphered and OS free
    programming condition.
  • PL/SQL can likewise specifically be called from
    the order line SQLPlus interface.
  • Coordinate call can likewise be produced using
    outside programming dialect calls to database.
  • PL/SQL's general sentence structure depends on
    that of ADA and Pascal programming dialect.
  • Aside from Oracle, PL/SQL is accessible in
    TimesTen in-memory database and IBM DB2.

Highlights of PL/SQL
  • PL/SQL is firmly coordinated with SQL.
  • It offers broad mistake checking.
  • It offers various information composes.
  • It offers an assortment of programming
  • It underpins organized programming through
    capacities and systems.
  • It underpins protest situated programming.
  • It underpins the improvement of web applications
    and server pages.

Favorable circumstances of PL/SQL
  • SQL is the standard database dialect and PL/SQL
    is emphatically coordinated with SQL. PL/SQL
    underpins both static and dynamic SQL. Static SQL
    underpins DML tasks and exchange control from
    PL/SQL piece. In Dynamic SQL, SQL permits
    installing DDL explanations in PL/SQL pieces.
  • PL/SQL permits sending a whole piece of
    articulations to the database at one time. This
    diminishes arrange activity and gives superior to
    the applications.
  • PL/SQL gives high profitability to software
    engineers as it can inquiry, change, and refresh
    information in a database.
  • PL/SQL spares time on outline and troubleshooting
    by solid highlights, for example, exemption
    taking care of, exemplification, information
    stowing away, and question arranged information

  • Applications written in PL/SQL are completely
  • PL/SQL gives high security level.
  • PL/SQL gives access to predefined SQL bundles.
  • PL/SQL offers help for Object-Oriented
  • PL/SQL offers help for creating Web Applications
    and Server Pages.

 Each piece code comprises of three sub-parts
  • 1.Declarations-
  • This area begins with the watchword DECLARE. It
    is a discretionary area and characterizes all
    factors, cursors, subprograms, and different
    components to be utilized as a part of the
  • 2.Executable Commands-
  • This area is encased between the watchwords
    BEGIN and END and it is a compulsory segment. It
    comprises of the executable PL/SQL articulations
    of the program. It ought to have no less than one
    executable line of code, which might be only a
    NULL order to show that nothing ought to be
  • 3.Exception Handling-
  • This segment begins with the watchword
    EXCEPTION. This discretionary area contains
    exception(s) that handle mistakes in the program.

The PL/SQL Identifiers
  • PL/SQL identifiers are constants, factors,
    special cases, systems, cursors, and held words.
    The identifiers comprise of a letter
    alternatively took after by more letters,
    numerals, dollar signs, underscores, and number
    signs and ought not surpass 30 characters.
  • As a matter of course, identifiers are not
    case-delicate. So you can utilize number or
    INTEGER to speak to a numeric esteem. You can't
    utilize a held catchphrase as an identifier.

PL/SQL Program Units
  • PL/SQL block
  • Function
  • Package
  • Package body
  • Procedure
  • Trigger
  • Type
  • Type body

  • Programming dialects give different control
    structures that take into account more confused
    execution ways.
  • A circle articulation enables us to execute an
    announcement or gathering of proclamations
    numerous circumstances and following is the
    general type of a circle explanation in a large
    portion of the programming dialects.

  • The string in PL/SQL is actually a sequence of
    characters with an optional size specification.
    The characters could be numeric, letters, blank,
    special characters or a combination of all.
  • The string in PL/SQL is really a succession of
    characters with a discretionary size particular.
    The characters could be numeric, letters, clear,
    unique characters or a blend of all.

PL/SQL offers three sorts of strings
  • 1.Fixed-length strings - In such strings,
    developers indicate the length while pronouncing
    the string. The string is correct cushioned with
    spaces to the length so indicated.
  • 2.Variable-length strings - In such strings, a
    greatest length up to 32,767, for the string is
    indicated and no cushioning happens.
  • 3.Character extensive items (CLOBs) - These are
    variable-length strings that can be up to 128

  • The PL/SQL programming dialect gives an
    information structure called the VARRAY, which
    can store a settled size successive gathering of
    components of a similar kind. A varray is
    utilized to store a requested accumulation of
    information, in any case it is regularly better
    to think about an exhibit as a gathering of
    factors of a similar sort.
  • All varrays comprise of adjoining memory areas.
    The most reduced deliver relates to the main
    component and the most elevated deliver to the
    last component.

  • PL/SQL subprograms are named PL/SQL obstructs
    that can be conjured with an arrangement of
    parameters. PL/SQL gives two sorts of
  • 1.Functions - These subprograms restore a
    solitary esteem for the most part used to figure
    and restore an esteem.
  • 2.Procedures - These subprograms don't restore an
    esteem straightforwardly basically used to play
    out an activity.

Calling a Function
  • While making a capacity, you give a meaning of
    what the capacity needs to do. To utilize a
    capacity, you should call that capacity to play
    out the characterized undertaking. At the point
    when a program calls a capacity, the program
    control is exchanged to the called work.
  • A called work plays out the characterized
    assignment and when its arrival articulation is
    executed or when the last end proclamation is
    achieved, it restores the program control back to
    the fundamental program.
  • To call a capacity, you essentially need to pass
    the required parameters alongside the capacity
    name and if the capacity restores an esteem, at
    that point you can store the returned esteem

PL/SQL Recursive Functions
  • We have seen that a program or subprogram may
    call another subprogram. At the point when a
    subprogram calls itself, it is alluded to as a
    recursive call and the procedure is known as

  • cursor is a pointer to this setting region.
    PL/SQL controls the setting zone through a
    cursor. A cursor holds the lines (at least one)
    returned by a SQL proclamation. The arrangement
    of lines the cursor holds is alluded to as the
    dynamic set.
  • You can name a cursor so it could be alluded to
    in a program to get and process the lines
    returned by the SQL articulation, each one in
  • There are two kinds of cursors-
  • 1.Implicit cursors
  • 2.Explicit cursors

Implicit Cursors
  • Understood cursors are consequently made by
    Oracle at whatever point a SQL proclamation is
    executed, when there is no express cursor for the
    announcement. Software engineers can't control
    the certain cursors and the data in it.
  • At whatever point a DML proclamation (INSERT,
    UPDATE and DELETE) is issued, an understood
    cursor is related with this announcement. For
    INSERT tasks, the cursor holds the information
    that should be embedded. For UPDATE and DELETE
    tasks, the cursor recognizes the columns that
    would be influenced.
  • In PL/SQL, you can allude to the latest certain
    cursor as the SQL cursor, which dependably has
    traits, for example, FOUND, ISOPEN, NOTFOUND,
    and ROWCOUNT. The SQL cursor has extra
    intended for use with the FORALL proclamation

  • PL/SQL underpins software engineers to find such
    conditions utilizing EXCEPTION obstruct in the
    program and a proper move is made against the
    mistake condition. There are two sorts of
    exemptions .
  • 1.System-characterized exemptions
  • 2.User-characterized exemptions

Explicit Cursors
  • Unequivocal cursors are software engineer
    characterized cursors for increasing more control
    over the setting zone. An unequivocal cursor
    ought to be characterized in the revelation area
    of the PL/SQL Block. It is made on a SELECT
    Statement which returns in excess of one column.

  • A record is an information structure that can
    hold information things of various types. Records
    comprise of various fields, like a line of a
    database table.
  • For instance, you need to monitor your books in a
    library. You should need to track the
    accompanying traits about each book, for example,
    Title, Author, Subject, Book ID. A record
    containing a field for every one of these things
    permits regarding a BOOK as a legitimate unit and
    enables you to sort out and speak to its data
  • PL/SQL can deal with the accompanying kinds of
  • 1.Table-based
  • 2.Cursor-based records
  • 3.User-characterized records

Raising Exceptions
  • Exemptions are raised by the database server
    naturally at whatever point there is any interior
    database mistake, yet special cases can be raised
    unequivocally by the developer by utilizing the
    charge RAISE.

Client Characterized Exceptions
  • PL/SQL enables you to characterize your own
    special cases as indicated by the need of your
    program. A client characterized special case must
    be pronounced and after that raised
    unequivocally, utilizing either a RAISE
    explanation or the strategy DBMS_STANDARD.RAISE_AP

Pre-characterized Exceptions
  • PL/SQL gives numerous pre-characterized
    exemptions, which are executed when any database
    control is damaged by a program. For instance,
    the predefined special case NO_DATA_FOUND is
    raised when a SELECT INTO proclamation restores
    no columns.

  • Triggers are put away projects, which are
    naturally executed or terminated when a few
    occasions happen. Triggers are, indeed, composed
    to be executed in light of any of the
    accompanying occasions -
  • A database control (DML) explanation (DELETE,
  • A database definition (DDL) explanation (CREATE,
    ALTER, or DROP).
  • A database task (SERVERERROR, LOGON, LOGOFF,
  • Triggers can be characterized on the table, view,
    mapping, or database with which the occasion is

  • Bundles are blueprint protests that gatherings
    intelligently related PL/SQL writes, factors, and
  • A bundle will have two obligatory parts
  • 1.Package detail
  • 2.Package body or definition

 Bundle Specification
  • The particular is the interface to the bundle. It
    just DECLARES the sorts, factors, constants,
    special cases, cursors, and subprograms that can
    be referenced from outside the bundle.
  • At the end of the day, it contains all data about
    the substance of the bundle, however rejects the
    code for the subprograms.
  • All items put in the detail are called open
    articles. Any subprogram not in the bundle
    particular but rather coded in the bundle body is
    known as a private question.

Bundle Body
  • The bundle body has the codes for different
    techniques pronounced in the bundle particular
    and other private affirmations, which are avoided
    the code outside the bundle.
  • The CREATE PACKAGE BODY Statement is utilized for
    making the bundle body.

  • A collection is a requested gathering of
    components having similar information compose.
    Every component is distinguished by an
    extraordinary subscript that speaks to its
    situation in the collection.
  • PL/SQL gives three collection composes
  • 1.Index-by tables or Associative exhibit
  • 2.Nested table
  • 3.Variable-measure cluster or Varray

Tuning Oracle Database
  • This information about tuning Oracle Database for
  • 1.Performance Planning
  • 2.Instance Tuning
  • 3.SQL Tuning

Performance Tunning
  • One of the greatest obligations of a DBA is to
    guarantee that the Oracle database is tuned
    legitimately. The Oracle RDBMS is profoundly
    tunable and enables the database to be observed
    and acclimated to build its execution.
  • One ought to do execution tuning for the
    accompanying reasons
  • The speed of registering may squander profitable
    human time (clients sitting tight for reaction).
  • Empower your framework to stay aware of the speed
    business is led.
  • Improve equipment use to spare cash
    (organizations are burning through millions on

  • Always remember that tuning ought to be gone for
    settling business issues
  • The overnight clump occupations don't complete
    till noon a screen takes 10 seconds to
    invigorate, and SLA says 1 second a report is
    required like clockwork, yet it takes 20 minutes
    to produce.
  • Try not to tune since you see some figure in a
    report that you don't care.
  • For instance, no end client has ever called the
    assistance work area to gripe that "there are an
    excessive number of cradle occupied hold up
  • Despite the fact that this site isn't excessively
    worried about equipment issues, one needs to
    recall than you can't tune a Buick into a Ferrari.

Workflow Diagram
  • Visit our website
  • Contact 8939925577,9384637514
  • Place Chennai,Bangalore,Tambaram
Write a Comment
User Comments (0)