Pl/Sql Over Sql: Do You Really Need It? This Will Help You Decide!

Santwana Mohanty

4 years ago

A block is a unit of code that provides execution and scoping boundaries for variable declarations and exception handling. PL/SQL allows you to create anonymous blocks (blocks of code that have no name) and named blocks, which may be packages, procedures, functions, triggers, or object types.

Pl/sql over SQL

SQL is a powerful tool, but it has some disadvantages, such as, execute only one query at a time and no error handling mechanism.

To prevent these disadvantages oracle provided Pl/SQL.

What is pl/sql?

Procedural Language/ Structured query language is a Procedural language that brings programming language features to SQL.

Features

• Tool to Validate, control, manipulate and restrict unauthorized access of data from SQL database.
• Improves the application performance.
• Deals with errors by using exception handling.

Advantages

1. Procedural language capability
2. Better performance
3. Error handling
4. Application portability

Architecture

Programs are organized into blocks. Each block consists of three logical sub blocks. They are:

Declaration(optional)
Execution(mandatory)
Exception handling(optional)

Block structure

DECLARE
//Declare section
— Declaration of cursors, variables, records, constants
BEGIN
//Execution section
— Executable statements/ instructions
EXCEPTION
//Exception handling
— Error handling
END;

Example

DECLARE
Num1 number[5] :=2;
Num2 number[5] :=4;
result number [5];
BEGIN
result:= Num1+Num2;
dbms_out.put_line(‘The sum of Num1 and Num2 :=’।। result);
END;
/