PL/SQL

PL/SQL is a procedural superstructure of the SQL language by the Oracle company, which is based on the programming language Ada. It is widespread and some of their elements were taken over even by other relational databases. The base building element of PL/SQL is the block. Those can be nested one to another. Typically, the individual block executes one logic action to the given program. The structure of block:

DECLARE
  /* Declaration, which contains variables, types and local subprograms */
BEGIN
  /* Executive section: here are the procedure and the code in SQL */
  /* This section is the only one, that is within the writing compulsory */
EXCEPTION
  /* The area, which provides processing of the exceptions: here are processed the error events. */
END;

The advantages of using PL/SQL

• Lower network traffic. Because PL/SQL works in whole SQL expressions blocks, which processes simultaneously, so it significantly decreases network traffic.
• Support of procedural language. PL/SQL is a development tool for the data manipulation, but it also offers conditional controls, loops or branching as other programming languages.
• Solving mistakes. PL/SQL allows simple error solution and gives the user clear error messages, if there happens to be an error.
• Declaration of variables. PL/SQL gives the programmer a control over the variables definition and makes them accessible within the block. Declared variables are then being possible to use in a moment of processing the query.
• Immediate calculation. The calculations are in PL/SQL very fast and effective, which makes the transaction efficiency better. PL/SQL can send the whole block of commands and execute him in Oracle at once.
• The app transferability. Applications, which are written for PL/SQL are transferable to any operating system. PL/SQL applications are an independent program, which is possible to execute on any computer.
• Safety. PL/SQL is a very safe tool for manipulation, control, validation and limiting the access to the data from SQL database.
• Efficiency. Using PL/SQL can significantly better the app performance.
• Data processing. PL/SQL can display larger numbers of records from different tables simultaneously.

The disadvantages of using PL/SQL

• General error messages. Some of the error messages in regard to syntax are too general and don’t contain any indication of the exact location of the problematic code.
• Proprietary language Oracle. PL/SQL is proprietary language Oracle, which means, that if you need to change database to another supplier, it is needed to rewrite all of the programs. Also if you use a mixture of databases or developing apps, which are executed on different databases, you will have to manage large amount of source code or to choose neutral language to a database as is Java, VB or C and use API for the access to the database.
• Bad I/O of functionality. PL/SQL offers very weak support of i/o, concerning the reading and writing to files or reading and writing to user interface. PL/SQL is primarily designed for information manipulation within database and not for files processing or communication with the user. Although these operations are possible, they are more difficult than in a other languages.