Why Do Choose Oracle PL/SQL?

This tutorial page is based on examples to make it easier to follow. Oracle PL/SQL (Procedural Language / Structured Query Language) is a SQL with extended options and commands. It is allowing to write SQL queries directly into your Oracle database objects or procedures and gain a better performance through it. Keeping your code in the Oracle database will reduce traffic between an application and a database. The following summary will bring out some of the reasons why you should prefer PL/SQL to other programming languages.

You may choose Oracle PL/SQL as your database programming for the following reasons:

• PL/SQL is the Oracle procedural extension of SQL. It is a portable and high-performance transaction-processing language.
• PL/SQL is tightly integrated with SQL and widely used database manipulation language.
• PL/SQL allows to send a block of statements to the database and so reducing significantly traffic between the application and the database.
• PL/SQL allows to write compact code for manipulating data. It lets you to read, transform, and write data in files; Oracle PL/SQL can query, transform, and update data inside a database.
• PL/SQL applications can use on any operating system and platform where Oracle Database runs.
• PL/SQL procedures can be used with web applications.

How does work PL/SQL?

The PL/SQL engine is sitting in a different area than SQL Statement Executor and it knows if some part of code does not belong to him, so it takes that part out and sends it to the SQL Statement Executor who also only knows how to parse, execute and validate SQLs and it doesn’t know anything about PL/SQL code and variables. After the SQL statement is done the result will be returned by SQL Statement Executor back to Procedural Statement Executor who will finish the block executing. The SQL Statement Executor will be skipped when the PL/SQL block doesn’t have any SQL statement inside and the code will be sent directly to Procedural Statement Executor who will do rest. The same logic is described on the following PL/SQL tutorial image:

The PL/SQL block can be stored procedure or anonymous block. We will look at stored procedures later and we will start with the anonymous PL/SQL block. The anonymous block can be stored into database inside some database object like procedures, triggers, constraints and so on, but you cannot store into database the PL/SQL anonymous block alone. For example in database programming anonymous blocks have been used to execute scripts or code without saving it into database. The following example shows how looks a syntax of the anonymous block in PL/SQL.

DECLARE
  <variables and/or sub-programs>
BEGIN
  <executable code>
END;

To write a simple “Hello World!” example using PL/SQL anonymous block and a database developer program we are going to use the next piece of code.

DECLARE
    v_text VARCHAR2(20) := 'Hello World!';
BEGIN
    dbms_output.put_line ('>'||v_text);
END;

Now when you are knowing how looks PL/SQL anonymous block we will come back to SQL Statement Executor and you should be able to follow much easier the way how gets SQL statement executed in PL/SQL. To make the next PL/SQL tutorial example possible to demonstrate we need to use a bit more complicated PL/SQL anonymous block that is including a SQL statement. As the following example shows:

DECLARE
    s_names VARCHAR2(2000);
BEGIN
  FOR rec_names IN (SELECT Name
                      FROM Users)
  LOOP
    s_names := s_names ||' '||rec_names.name;
  END LOOP;
END;

The same block will be used in the next example and the SQL statement is coloured red to make it easier to follow.

All the SQL statements are getting in the same “way” not depending on if they are in the database procedure, function or trigger. This schema applies to all PL/SQL code and using this database programming way the application outside from Oracle database can have the final result without much work, plus the business logic will be better secured and maintained from one place which is from an Oracle database.

Next: How Is Structured A PL/SQL Block