Overview – PL/SQL

Neil Bhanja
PL/SQL is pronounced as a language but it is not a language typically rather than it is a mechanism or technology implemented on Oracle server to manipulate Oracle database programmatically using SQL command

What is PL/SQL?
  • PL/SQL stands for Procedural Language extension of SQL.
  • PL/SQL is a combination of SQL along with the procedural features of programming languages.
  • It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
  • It is a block structure language.
To design a program using a language and provides four different blocks namely
  • Declare
  • Input
  • Process
  • Output
In some programming language above blocks are physical in nature and they are identified by a block name. These type of language are categorized as block structure language like COBOL, PL/SQL.

Advantages of PL/SQL:
  • PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.
  • PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.
  • Applications written in PL/SQL are fully portable.
  • PL/SQL provides high security level.
  • PL/SQL provides access to predefined SQL packages.
  • PL/SQL provides support for Object-Oriented Programming.
  • PL/SQL provides support for Developing Web Applications and Server Pages.
PL/SQL is example of block structure language, whose total program is divided in three different blocks that are given below.

DECLARE (Optional block)
BEGIN (Mandatory)
EXCEPTION (Optional block)
END;
/

Declare :
This is a block which is responsible for declare of identifier, types and cursors to be used in PL/SQL program. This block is optional in nature.
Begin and End:
This block is responsible for input process and output process in PL/SQL program. PL/SQL always starts it’s program execution from the begin block and terminate with end. This is block is mandatory in nature.
Exception:
This block is responsible for exception handling in PL/SQL program. Whenever an exception occurs system control will navigated to the exception block and It handles that exception and exit out of the program normally. This block is optional in nature.

Above blocks are combined together and create the PL/SQL program.
Syntax:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;

Example – 1
STEP 1 – create a .sql file (example create file in name website.sql)
SQL >MY.SQL
STEP 2 – write your programme inside notepad .
DECLARE
WEBSITE VARCHAR2(30):='www.tutorialslife.com';
BEGIN
DBMS_OUTPUT.PUT_LINE(WEBSITE);
END;
/
STEP 3 – Save your sql file (default stored in this directory C:\oracle\product\od_1\bin)
STEP 4 – Now run your sql file
Syntax
SQL> start <filename> or @<filename>
SQL> @MY
www.tutorialslife.com
PL/SQL procedure successfully completed.