Though SQL is the
natural language of the DBA, it suffers from various inherent disadvantages,
when used as a conventional programming language.
1.
SQL does not have any procedural capabilities i.e.
SQL does not provide the programming techniques of condition checking, looping
and branching that is vital (very important) for data testing before its
permanent storage.
2.
SQL statements are passed to the Oracle Engine one
at a time. Each time an SQL statement is executed, a call is made to the
engine’s resources. This adds to the traffic on the network, thereby decreasing
the speed of data processing, especially in a multi-user environment.
3.
While processing an SQL sentence if an error occurs,
the Oracle engine displays its own error messages. SQL has no facility for
programmed handling of errors that arise during the manipulation of data.
Although SQL is a
very powerful tool, its set of disadvantages prevent, it from being a fully
structured programming language. For a fully structured programming language,
Oracle provides PL/SQL.
As the name
suggests, PL/SQL is a superset of SQL. PL/SQL is a block-structured
language that enables developers to combine the power of SQL with procedural
statements. PL/SQL bridges the gap between database technology and procedural
programming languages.
ADVANTAGES OF
PL/SQL
1.
PL/SQL is development tool that not only supports
SQL data manipulation but also provides facilities of conditional checking,
branching and looping
2.
PL/SQL sends an entire block of SQL
statements to the Oracle engine all in one go. Communication between the
program block and the Oracle engine reduces considerably, reducing network
traffic. Since the Oracle engine got the SQL statements as a single block, it
processes this code much faster than if it got the code one sentence at a time.
There is a definite improvement in the performance time of the Oracle engine.
As an entire block of SQL code is passed to the Oracle engine at one time for
execution, all changes made to the data in the table are done or undone, in
one go
3.
PL/SQL also permits dealing with errors as required,
and facilitates displaying user-friendly messages, when errors are encountered
4.
PL/SQL allows declaration and use of variables in
blocks of code. These variables can be used to store intermediate results of a
query for later processing, or calculate values and insert them into an Oracle
table later. PL/SQL variables can be used anywhere, either in SQL statements or
in PL/SQL blocks
5.
Via PL/SQL, all sorts of calculations can be done
quickly and efficiently without the use of the Oracle engine. This considerably
improves transaction performance
6.
Applications written in PL/SQL are portable to any
computer hardware and operating system, where Oracle is operational. Hence,
PL/SQL code blocks written for a DOS version of Oracle will run on its Linux /
UNIX version, without any modifications at all
THE GENERIC PL/SQL
BLOCK
Every programming
environment allows the creation of structured, logical blocks of code that
describe processes, which have to be applied to data. Once these blocks are
passed to the environment, the processes described are applied to data,
suitable data manipulation takes place, and useful output is obtained.
PL/SQL permits the creation of
structured logical blocks of code that describe processes, which have to be
applied to data. A single PL/SQL code block consists of a set of SQL
statements, clubbed together, and passed to the Oracle engine entirely. This
block has to be logically grouped together for the engine to recognize it as a
singular code block. A PL/SQL
block has a definite structure, which can be divided into sections. The
sections of a PL/SQL block are:
Ø The Declare
section,
Ø The Master Begin
and End section that also (optionally) contains an Exception
section.
Each of these is
explained below:
The Declare
Section
Code blocks start with a
declaration section, in which, memory variables and other Oracle objects can be
declared, and if required initialized. Once declared, they can be used in SQL
statements for data manipulation.
The Begin Section
It consists of a
set of SQL and PL/SQL statements, which describe processes that
have to be applied to table data. Actual data manipulation, retrieval, looping
and branching constructs are specified in this section.
The Exception
Section
This section
deals with handling of errors that arise during execution of the data
manipulation statements, which make up the PL/SQL code block. Errors can arise
due to syntax, logic and/or validation rule violation.
The End Section
This marks the
end of a PL/SQL block.
A PL/SQL code block
can be diagrammatically represented as follows:
Diagram : The PL/SQL block
structure.
Wherever PL/SQL technology is
required, the PL/SQL engine accepts any valid PL/SQL block as input.
PL/SQL In The
Oracle Engine
The PL/SQL engine
resides in the Oracle engine, the Oracle engine can process not only single SQL
statements but also entire PL/SQL blocks.
These blocks are
sent to the PL/SQL engine, where procedural statements are executed and SQL
statements are sent to the SQL executor in the Oracle engine. Since the PL/SQL
engine resides in the Oracle engine, this is an efficient and swift
operation.
The call to the
Oracle engine needs to be made only once to execute any number of SQL
statements, if these SQL sentences are bundled inside a PL/SQL block.
Diagram below gives
an idea of how these statements are executed and how convenient it is to bundle
SQL code within a PL/SQL block. Since the Oracle engine is called only once for
each block, the speed of SQL statement execution is vastly enhanced, when
compared to the Oracle engine being called once for each SQL sentence.
Diagram : The PL/SQL
Execution Environment.
PL/SQL
The
Character Set
The basic
character set includes the following:
Ø Uppercase
alphabets { A - Z}
Ø Lowercase
alphabets { a - z }
Ø Numerals { 0 -
9 }
Ø Symbols ( ) +
- * / <> = ! ; : . % , ‘ # $ A & _ \ { } ? [ ]
Words used in a
PL/SQL block are called Lexical Units. Blank spaces can be freely
inserted between lexical units in a PL/SQL block. The blank spaces have no
effect on the PL/SQL block.
The ordinary
symbols used in PL/SQL blocks are:
(
) + - * / < > =
; % '
" [ ] :
Compound symbols
used in PL/SQL blocks are:
< >
!= ~ = ^ =
= = > = :=
** || <<
>>
LITERALS
A literal is a
numeric value or a character string used to represent itself.
Numeric Literal
These can be either integers or
floats. If a float is being represented, then the integer part must be
separated from the float part by a period.
Example:
25,
6.34, 25e-03, .1, 1.e4, +17, -5
String Literal
These are
represented by one or more legal characters and must be enclosed within single
quotes. The single quote character can be represented, by writing it twice in
a string literal. This is definitely not the same as a double quote.
Example:
‘Hello
World’, 'Don’’t go without saving your work'
Character Literal
These are string
literals consisting of single characters.
Example:
‘*’,’A’,’a’,’@’
Logical (Boolean)
Literal
These are
predetermined constants. The values that can be assigned to this data type are:
TRUE, FALSE,
No comments:
Post a Comment