Advantages of PL/SQL

Advantages Of PL/SQL

The following are a few important advantages of PL/SQL. Moreover most of the features listed above are also advantages of PL/SQL. 

Support for SQL

PL/SQL allows you to use SQL commands, function and operators.  PL/SQL supports data types of SQL.

PL/SQL also allows SQL statements to be constructed and executed on the fly. The process of creating SQL statements on the fly is called as Dynamic SQL. This is different from writing SQL commands at the time of writing the program, which is called as Static SQL.

Starting from Oracle8i, PL/SQL support native dynamic SQL, which makes programming Dynamic SQL easier than its predecessor, where we used DBMS_SQL package. 

Better performance

PL/SQL block is sent as one unit to Oracle server. Without PL/SQL each SQL command is to be passed to Oracle server, which will increase network traffic heavily. As a collection of SQL statements is passed as a block to Oracle server, it improves performance.

Portability

Applications written in PL/SQL are portable to any platform on which Oracle runs.  Once you write a program in PL/SQL, it can be used in any environment without any change at all.

PL/SQL block

PL/SQL programs are written as blocks.  Block allows you to group logically related statements and declarations. PL/SQL block is consisting of the following three parts:

q  Declarative part

q  Executable part

q  Exception-handling part

Declarative Part

This is the area of the block where variables, cursors etc are declared.  All variables used in the block are to be declared in declarative part.

PL/SQL Datatypes

PL/SQL provides a variety of predefined datatypes, which can be divided into four categories:

Scalar Represents a single value.
Composite Is a collection of components
Reference Is a pointer that points to another item.
LOB Holds a lob locator.

The following are the datatypes in various categories

Scalar NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN
Composite  RECORD, TABLE and VARRAY.
Reference    REF CURSOR, REF Object_type
LOB    BFILE, BLOB, CLOB, and NCLOB.

 Note: There may be minor differences between PL/SQL datatypes and SQL datatypes though they have the same name. For complete information about datatypes in PL/SQL please see PL/SQL User’s guide and Reference.

Executable part

Is the area where we write SQL and PL/SQL commands that are to be executed. This is the only mandatory part of the entire block.

Exception-handling part

Is the place where we handle exceptions (errors) that are raised in executable part.  Exception handlers handle exceptions.  We will discuss more about this in later chapter. 

Comments in PL/SQL

You can give comments in PL/SQL block in two ways.

First way is by preceding the comment with two hyphens (- -).

Example:        — this is single line comment

Second way is by starting the comment with /* and ending it with */.

Example:   /* this comment can be of multiple lines */

SELECT… INTO

SQL*Plus displays the data retrieved by SELECT command. Whereas in PL/SQL SELECT command is used only to retrieve the data and storing and using data is to be done explicitly. So Oracle provided INTO clause with SELECT command that is used to specify the variable(s) into which the value(s) retrieved must be copied.

Declaring Constants

Constant is a PL/SQL variable whose value doesn’t change. Any attempt to change the value of a constant will result in error.

variable CONSTANT datatype [precision , scale] := expression;

The following declarative statement creates a constant that takes value 500.

bonus constant  number(3) :=  500;

Nesting Blocks

It is possible to define a block within another block.  When blocks are defined one within another they are said to be nested.

Scope and visibility of variables

Scope of the variable refers to the region of the program in which the variable can be used. A variable is said to be visible when it can be referred without any qualifier. 

Available functions

Most of the functions available in SQL are also available in PL/SQL. 

The functions that are NOT available in procedural statements are:

q  DECODE

AVG, COUNT, GROUPING, MIN, MAX, SUM, STDDEV, and VARIANCE

However, these functions can be used with SQL commands and those SQL commands may be used in PL/SQL.

Advertisements

About Oracle Technology Blogs
Oracle Application Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: