Introduction to PL/SQL: Features, Differences, and Advantages

PL/SQL

PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers. It can support Array and handle exceptions (runtime errors).

Differences between SQL and PL/SQL

• SQL is a single query that is used to perform DML and DDL operations.
• PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.
• SQL is declarative, that defines what needs to be done, rather than how things need to be done.
• PL/SQL is procedural that defines how the things needs to be done.
• In SQL Execute as a single statement.
• In PL/SQL Execute as a whole block.
• SQL Mainly used to manipulate data. PL/SQL Mainly used to create an application.
• SQL Cannot contain PL/SQL code in it. PL/SQL is an extension of SQL, so it can contain SQL inside it.

Features of PL/SQL

• PL/SQL is basically a procedural language, which provides the functionality of decision making, iteration and many more features of procedural programming languages.
• PL/SQL can execute a number of queries in one block using single command.
• One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored in the database for reuse by applications.
• PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as exception handling block.
• Application written in PL/SQL are portable to computer hardware or operating system where Oracle is operational.
• PL/SQL Offers extensive error checking.

Basics of PL/SQL

PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL). PL/SQL is a combination of SQL along with the procedural features of programming languages. Oracle uses a PL/SQL engine to processes the PL/SQL statements. PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers.

Advantages of PL/SQL

PL/SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. In Dynamic SQL, SQL allows embedding DDL statements in PL/SQL blocks. 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 gives high productivity to programmers as it can query, transform, and update data in a database. 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.

Sum of two number:

• declare
• x number(5);
• y number(5);
• z number(7);
• begin
• x:=10;
• y:=20;
• z:=x+y;
• dbms_output.put_line('Sum is '||z);
• end;