PL/SQL

PL/SQL Basics- an Introduction

PL/SQL stands for Programming Logic/ Structured Query Language. It is a tool in Oracle to enhance the capability of SQL by adding the processing ability of SQL.  So, you get a power pack of SQL data manipulation plus processing capability of Procedural Language.  PL/SQL employs all the features of object-oriented programming concepts like Data Encapsulation, Information Hiding and Exception Handling. Learn all about PL/SQL basics in the upcoming posts.

PL/SQL Basics- Block Structure

 A block of PL/SQL consists of four sections as shown in the image below

PL/SQL Basics Block
  • DECLARE –Declare section is optional. All variables, cursors or user-defined exceptions must be declared here.
  • BEGIN-Begin section is mandatory. It is made up of the SQL and PL/SQL statements that define the logic of a specific PL/SQL Block for data manipulation on a database.
  • EXCEPTION- The exception section again is optional. It is written to handle the exceptions raised in the BEGIN section. It is written to respond to users by giving the message for pre-defined system exceptions or user-defined exceptions.
  • END- This is the required and last section of a block. It signifies the end of a PL/SQL block. It must always be followed by a semicolon (;)

PL/SQL Features

  • PL enhances the capability of SQL with the programming logic like looping structures, conditional structures, variable declaration and making blocks.
  • PL/SQL uses SQL for data manipulation on Database objects by using the syntax of programming language statements.
  • A PL/SQL code is a block-structured unit. Every PL/SQL unit is an independently existing unit. It can be called any number of times in other PL/SQL blocks or front-end development tools like VB.NET.
  • A PL/SQL block is a way to logically group variable declarations and SQL statements. The variables declared within a PL/SQL block are local to it. When the block boundary ends the variable also ceases to exist.
  • PL/SQL can be used to create procedures, functions, and anonymous blocks defined as logical units for data processing or data manipulation.
  • The PL/SQL block can be nested as sub-blocks. A PL/SQL block, if named, can be called from another PL/SQL block.

Advantages of PL/SQL

  • PL/SQL exists only with SQL. It is created to support and extend the database manipulation language SQL. 
  • PL/SQL allows SQL data manipulation, cursor creation and management, transaction control with the support of all SQL functions and operators. It also uses pseudo columns.
  • PL/SQL improves the performance of database programs. The reason lies in the block-structure of PL/SQL codes. With PL/SQL, individual SQL statements are not sent for execution. A block of statements is passed on to the database server. This grouping of SQL statements and passing as one unit reduces the traffic between the application and the database.
  • When INSERTUPDATEDELETE, or SELECT statement are embedded in a PL/SQL block, the PL/SQL compiler reads the variables in WHERE clause and converts the values clause into bind variables. This allows dynamic binding of variables thus making the code read them at the time of execution.
  • The PL/SQL compiler is designed to optimize the PL/SQL code. This is done by rearranging statements for increasing the efficiency of applications using the PL/SQL blocks for database manipulation activities.
  • The PL/SQL compact code can be written compactly for better manipulation of data. With PL/SQL you can query, transform, and update data.
  • Once a PL/SQL block is written and compiled it can be used any number of times. The same PL/SQL block can be used in multiple applications by passing on appropriate arguments.  This saves coding and debugging time of programmers and developers.
  • PL/SQL applications are portable. They can be executed in applications running on any operating system and platform. The only condition is that the platform must support the specific RDBMS for which you have written procedures or functions.
  • When the applications use PL/SQL blocks for data manipulation you can guarantee scalability in terms of users. The PL/SQL stored subprograms are executed from the centralized database server, so any number of users can do the same activity without affecting performance. For example, if a procedure to display the account balance of bank customers is stored in a centralized server, multiple concurrent customers can view their accounts.
  • PL/SQL blocks are stored in a centralized server. Any change in it will be reflected in all applications using this procedure. So, PL/SQL helps in better manageability with single copies of the stored procedures or functions.