Oracle PLSQL Training in Chennai

1
ORACLE PLSQL
2
INTRODUCTION

• 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.

3
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.

4
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
  structures.
• It underpins organized programming through
  capacities and systems.
• It underpins protest situated programming.
• It underpins the improvement of web applications
  and server pages.

5
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
  writes.

6

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

7
 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
  program.
• 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
  executed.
• 3.Exception Handling-
• This segment begins with the watchword
  EXCEPTION. This discretionary area contains
  exception(s) that handle mistakes in the program.

8
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.

9
PL/SQL Program Units

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

10
Looping

• 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.

11
String

• 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.

12
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
  terabytes.

13
Array

• 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.

14
Subprograms

• PL/SQL subprograms are named PL/SQL obstructs
  that can be conjured with an arrangement of
  parameters. PL/SQL gives two sorts of
  subprograms-
• 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.

15
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

16
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
  recursion.

17
cursor

• 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
  turn.
• There are two kinds of cursors-
• 1.Implicit cursors
• 2.Explicit cursors

18
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
  qualities, BULK_ROWCOUNT and BULK_EXCEPTIONS,
  intended for use with the FORALL proclamation

19
Exception

• 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

20
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.

21
record

• 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
  betterly.
• PL/SQL can deal with the accompanying kinds of
  records
• 1.Table-based
• 2.Cursor-based records
• 3.User-characterized records

22
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.

23
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
  PLICATION_ERROR.

24
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.

25
Triggers

• 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,
  INSERT, or UPDATE)
• A database definition (DDL) explanation (CREATE,
  ALTER, or DROP).
• A database task (SERVERERROR, LOGON, LOGOFF,
  STARTUP, or SHUTDOWN).
• Triggers can be characterized on the table, view,
  mapping, or database with which the occasion is
  related.

26
Bundles

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

27
 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.

28
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.

29
collection

• 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

30
Tuning Oracle Database

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

31
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
  equipment).

32

• 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
  occasions".
• 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.

33
Workflow Diagram
34
THANK YOU

• Visit our website
• www.greenstechnologys.com
• www.trainingintambaram.net
• Contact 8939925577,9384637514
• Place Chennai,Bangalore,Tambaram