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:
- PL/SQL block
- PL/SQL engine
- 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
- High performance, as SQL is executed in bulk rather than a single statement
- High productivity
- Strong integration with SQL
- Fully portable
- Strong security
- 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.