Wednesday, October 15, 2014


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.

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

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.


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:
<  >  !=  ~ =   ^ =   = =    > =   :=  **   ||  <<   >>

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.
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.
‘Hello World’, 'Don’’t go without saving your work'

Character Literal
These are string literals consisting of single characters.

Logical (Boolean) Literal
These are predetermined constants. The values that can be assigned to this data type are: TRUE, FALSE,

No comments: