Learnitweb

Overview of PL/SQL

—

by

Editorial Team
in

1. Introduction

In this tutorial, we’ll discuss about PL/SQL. We’ll also discuss its advantages, features and architecture.

PL/SQL is the Oracle procedural extension of SQL. An SQL statement is a single statement and does not provide any functionality to group related SQL statements and treat the group as a unit. This problem can be solved by using PL/SQL. For example, PL/SQL enables you to write a function like we write in any other programming language like Java. A simple single executable statement in a Java function can be thought as SQL statement for understanding purpose. However, this analogy of Java function and PL/SQL is not accurate and is only for understanding purpose.

2. Advantages of PL/SQL

2.1 PL/SQL fully supports SQL

PL/SQL is tightly integrated with SQL. PL/SQL supports SQL data types, so manual conversion of types is not required. Using PL/SQL you can do all regular tasks like data manipulation, transaction control, using functions, operators etc.

2.2 High Performance

  • PL/SQL enables you send a block of statements to the database. This results in reduction of traffic between the application and the database.
  • A PL/SQL subprogram can contain thousands of executable statements. A PL/SQL subprogram is stored in executable forms and can be invoked repeatedly. This results in less traffic between application and the database in comparison to the execution of SQL statement one at a time.
  • Stored PL/SQL subprograms are cached and are shared among users. This results in less memory and faster invocation.
  • The PL/SQL compiler has an optimizer that can rearrange code for better performance.
  • Oracle database can reuse certain SQL statements when the SQL statement is executed repeatedly.

2.3 Portability

PL/SQL subprograms can be run on any operating system and platform where Oracle Database runs.

2.4 Scalability

PL/SQL subprograms stored and run on database server and can be scaled easily in comparison to the data processing applications running outside the database server.

2.5 Manageability

PL/SQL subprograms are stored and run on database server. So you have to manage it only at database server. If you write a Java program, you have to manage it at every client.

2.6 PL/SQL supports Object-Oriented Programming

PL/SQL allows you to define object types which are an integral part of object-oriented design.

2.7 PL/SQL provides debugging features

PL/SQL enables you to write code for processing data. PL/SQL provides many features for debugging as well. Using another programming language like Java, can be completely avoided for writing code for data processing.

2.8 PL/SQL supports both static SQL and dynamic SQL

Static SQL is SQL which is known completely at compile time. Dynamic SQL is SQL which is not known completely until run time.

3. PL/SQL Engine

PL/SQL engine complies and runs PL/SQL units. PL/SQL engine accepts valid PL/SQL as input, runs procedural statements and send SQL statements to the SQL engine for execution.

4. Example

Following is an example of a valid procedure.

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
total_emps NUMBER;
BEGIN
	DELETE FROM employees
	WHERE employees.employee_id = remove_emp.employee_id;
	total_emps := total_emps - 1;
END;