Scribd
Upload
2 views3 pages

Dbms_Unit_I

PL/SQL, or Procedural Language/Structured Query Language, is Oracle's procedural extension to SQL that addresses SQL's limitations such as single statement execution and lack of error handling. It is a block-structured language consisting of Declare, Begin, Exception, and End sections, allowing for more complex programming features like control structures and error message customization. PL/SQL programs reduce database overhead by making a single call to the Oracle engine for multiple SQL statements.

Uploaded by

ranganadh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views3 pages

Dbms_Unit_I

PL/SQL, or Procedural Language/Structured Query Language, is Oracle's procedural extension to SQL that addresses SQL's limitations such as single statement execution and lack of error handling. It is a block-structured language consisting of Declare, Begin, Exception, and End sections, allowing for more complex programming features like control structures and error message customization. PL/SQL programs reduce database overhead by making a single call to the Oracle engine for multiple SQL statements.

Uploaded by

ranganadh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Data Base Management System

UNIT V
5
PL/SQL

PL/SQL stands for Procedural Language/Structured Query Language, which


is provided by Oracle as a procedural extension to SQL. SQL is a declarative
language. In SQL, the statements have no control to the program and
can be executed in any order. PL/SQL, on the other hand, is a procedural
language that makes up for all the missing elements in SQL.

Shortcomings in SQL
We know, SQL is a powerful tool for accessing the database but it suffers from
some deficiencies as follows:
(a) SQL statements can be executed only one at a time. Every time to execute
a SQL statement, a call is made to Oracle engine, thus it results in an
increase in database overheads.

While processing an SQL statement, if an error occurs, Oracle generates


its own error message, which is sometimes difficult to understand. If a
user wants to display some other meaningful error message, SQL does not
have provision for that.
(c) SQL is not able to do the conditional query on RDBMS, this means one
cannot use the conditions like if . . . then, in a SQL statement. Also looping
facility (repeating a set of instructions) is not provided by SQL.

Structure of PL/SQL
PL/SQL is a 4GL (fourth generation) programming language. It offers all features
of advanced programming language such as portability, security, data
encapsulation, information hiding, etc. A PL/SQL program may consist of
more than one SQL statements, while execution of a PL/SQL program makes
only one call to Oracle engine, thus it helps in reducing the database overheads.
With PL/SQL, one can use the SQL statements together with the control
structures (like if . . . then) for data manipulation.

PL/SQL is a block structured language. This means a PL/SQL program


is made up of blocks, where block is a smallest piece of PL/SQL code having
logically related statements and declarations. A block consists of three sections
namely:
Declare, Begin, and Exception followed by an End statement. We will see
the different sections of PL/SQL block.

Declare Section
Declare section declares the variables, constants, processes, functions, etc., to
be used in the other parts of program. It is an optional section.
Begin Section
It is the executable section. It consists of a set of SQL and PL/SQL statements,
which is executed when PL/SQL block runs. It is a compulsory section.
Exception Section
This section handles the errors, which occurs during execution of the PL/SQL
block. This section allows the user to define his/her own error messages. This
section executes only when an error occurs. It is an optional section.

End Section
This section indicates the end of PL/SQL block.
Every PL/SQL program must consist of at least one block, which may
consist of any number of nested sub-blocks. Figure 5.1 shows a typical PL/SQL
block.

PL/SQL Language Elements


Like other programminglanguages PL/SQL also have specific character sets, operators,
indicators, punctuations, identifiers, comments, etc.

Character Set
A PL/SQL program consists of text having specific set of characters. Character
set may include the following characters:
– Alphabets, both in upper case [A–Z] and lower case [a–z]
– Numeric digits [0–9]
– Special characters ( ) + − * /< >= ! ∼ ˆ ; : . _ @ % , __ # $ & |
{}?[]
– Blank spaces, tabs, and carriage returns.
PL/SQL is not case sensitive, so lowercase letters are equivalent to corresponding
uppercase letters except within string and character literals.

You might also like