What is Oracle PL/SQL? - An Introduction

By What is Oracle PL/SQL? - An Introduction

In the previous article, I introduced you to MS SQL Database. In this article, I will introduce you to Oracle PLSQL. And at the end of the tutorial, you will learn about PLSQL language as well as its architecture.

PLSQL combines SQL along with procedural features of programming. PLSQL stands for Procedural Language extensions to the Structured Query. It was created by Oracle Company in the 1990s to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. PLSQL is designed to process powerful SQL statements and enhance the security, portability, and robustness of the database. PLSQL allows programmers to use loops, conditions, and even OOP concepts.

PL/SQL is an embedded language, therefore, it can only execute in Oracle Database. Unlike other languages such as Java, C, and C++, PLSQL cannot run on a system that does not have Oracle database.

PLSQL is an easy language to learn. It is highly structured and readable. You can use other languages like Java besides PLSQL. However, it’s easier to write clean and efficient code purely in PLSQL instead of other programming languages.

PLSQL helps improves database performance.

The architecture of PL/SQL

The architecture of PL/SQL consists of three elements:

  1. PL/SQL block
  2. PL/SQL engine
  3. Database Server


PL/SQL Block

This part contains the actual PL/SQL code. It consists of various sections to divide the code logically (declarative section, execution section, and exception handling section). It also has SQL commands which are used to interact with the database server.

The following are the PL/SQL units:

  • Anonymous Block
  • Function
  • Library
  • Procedure
  • Package Body
  • Package Specification
  • Trigger
  • Type
  • Type Body

All the PL/SQL units are PL/SQL blocks.

PL/SQL Engine

This is the part where the processing of codes occurs. The engine separates PL/SQL units and SQL part in the input. The separated PL/SQL units are handled by the PL/SQL engine while the SQL part is sent to the database server where interaction with the database takes place.

The PL/SQL engine can be installed in both the database server and the application server. 

Database Server

This is the most critical part and it stores the data. The PL/SQL engine consists of SQL executor which parses the input SQL statements and executes the same.

Advantage of Using PL/SQL

  1. High performance, as SQL is executed in bulk rather than a single statement
  2. High productivity
  3. Strong integration with SQL
  4. Fully portable
  5. Strong security
  6. Support Object-Oriented Programming concepts.

PL/SQL Example

The following is a sample PL/SQL code

FUNCTION ExamplePLSQLFormula
   RETURN CHAR
IS
   v_rec_no   VARCHAR2 (2000) := '';
   v_cnt     NUMBER := 0;
BEGIN
   THEN
      FOR nos
         IN (SELECT tbl1_doc_no
               FROM tbl1, tbl2 b
              WHERE     tbl1_org_code = tbl2_org_code
                    AND tbl2_batch_no = tbl2_batch_no
                    AND tbl1_sys_no = 500041)
      LOOP
         v_cnt := v_cnt + 1;
         IF (v_cnt > 1)
         THEN
            v_rec_no := v_rec_no || CHR (10) || nos.tbl_doc_no;
         ELSE
            v_rec_no := nos.tbl_doc_no;
         END IF;
      END LOOP;
   RETURN (v_rec_no);
END;

Conclusion

In this tutorial, you have learned about the PL/SQL program language and its architecture. After you submit a PL/SQL block to the Oracle Database server, the PL/SQL engine works together with the SQL engine to compile and run the code. PL/SQL engine executes the procedural elements while the SQL engine processes the SQL statements. Now you should have a basic understanding of PL/SQL programming language and its architecture. Let’s create the first working PL/SQL anonymous block.

Was this article helpful?
Donate with PayPal: https://www.paypal.com/donate

Bessy
Eric Murithi Muchenah

Life is beautiful, time is precious. Make the most out of it.