Structured Query Language (SQL) is a language that has been widely accepted and adopted for accessing relational databases. This language allows users to perform database operations such as reading, creating, modifying, and deleting the data. Since the summer of 1970, when Dr. E.F. Codd published the paper A Relational Model of Data for Large Shared Data Banks for the ACM journal, the language has matured comprehensively as an industry standard. With its broad range of features and easy adaptation to enterprise environments, the SQL language has been typically regarded as the most reliable language for interacting with relational databases.
PL/SQL was developed in 1991 by Oracle Corporation as a procedural language extension to SQL. Its ability to integrate seamlessly with SQL makes it a powerful language to construct the data access layer and the rich procedural extensions help in translating business logic within the Oracle Database. This first article in my blog introduces you to the PL/SQL language and refreshes some of the key programming concepts. The article is outlined as follows:
- Introduction to PL/SQL
- Recapitulate procedures, functions, packages, and cursors
- Exception handling
- Object dependencies
- Major Oracle supplied packages
- Oracle Development tools - SQL Developer and SQL*Plus
Overview of PL/SQL main programming concepts
PL/SQL stands for Procedural Language-Structured Query Language(PL/SQL). It is part of the Oracle Database product, which means no separate installation is required. It is commonly used to translate business logic in the database and expose the program interface layer to the application. While SQL is purely a data access language that directly interacts with the database, PL/SQL is a programming language in which multiple SQLs and procedural statements can be grouped in a program unit. PL/SQL code is portable between Oracle Databases (subject to limitations imposed by versions). The built-in database optimizer refactors the code to improve the execution performance.
PL/SQL advantages
The advantages of PL/SQL as a language are as follows:
- PL/SQL supports all types of SQL statements, data types, static SQL, and dynamic SQL
- PL/SQL code runs on all platforms supported by the Oracle Database
- PL/SQL code performance can be improved by the use of bind variables in direct SQL queries
- PL/SQL supports the object-oriented model of the Oracle Database
- PL/SQL applications increase scalability by allowing multiple users to invoke the same program unit
Although it is not used to build user interfaces, it provides the opportunity to build robust, secure, and portable interface layers, which can be exposed to a high-level programming language. Some of the key faculties of PL/SQL (PL/SQL accomplishments) are listed here:
- A procedural language: A PL/SQL program can include a list of operations that can execute sequentially to get the desired result. Unlike SQL, which is just a declarative language, PL/SQL adds selective and iterative constructs to it.
- Database programming language: Server side programs run faster than the middle-tier programs. Code maintenance becomes easy as it needs to be re-written less frequently.
- An integral language: Application developers can easily integrate a PL/SQL program with other high-level programming interfaces such as Java, C++, or .NET. The PL/SQL procedures or subprograms can be invoked from client programs as executable statements.
PL/SQL program fundamentals
A well-written PL/SQL program should be able to answer the following fundamental questions:
- How do we handle an SQL execution in the program?
- How do we handle the procedural execution flow in the program?
- Does the program handle the exceptions?
- How do we maintain (trace and debug) the PL/SQL program code?
Well, there are multiple tips and techniques to standardize PL/SQL coding practices. But before we drill down to the programming skills, let us familiarize ourselves with the structure of a PL/SQL program. A PL/SQL program can be broken down into four sections. Each section carries a specific objective and must exist in the same sequence in a program. Let us have a brief look at the sections:
- Header: This is an optional section which is required for named blocks such as procedures, functions, and triggers. It contains the program name, the program's owner, and the parameter specification.
- Declaration: This is an optional section used to declare local variables, cursors, and local subprograms that are likely to be used in the program body. The DECLARE keyword indicates the beginning of the declaration section. The section can be skipped if the PL/SQL program uses no variables.
- Execution: This is the procedural section of the program and comprises the main program body and an exception section. The BEGIN and END keywords indicate the beginning and end of the program body. It must contain at least one executable statement. During block execution, these statements are parsed and sequentially executed by the PL/SQL engine.
- Exception: This is an optional section in the program body that contains a set of instructions as procedural statements, for various errors, that may occur in the program leading to abnormal termination. The program control lands into the exception section and the appropriate exception handler is executed. The EXCEPTION keyword indicates the start of the exception section.
The following block diagram shows the structure of a PL/SQL block:
A PL/SQL block is the elementary unit of a program that groups a set of procedural statements. Based on the sections included in a PL/SQL program unit, we can classify a program under following categories:
- Anonymous PL/SQL block: This is the simplest PL/SQL program that has no name, but has its DECLARE-BEGIN-END skeleton. It can either be run for current execution as standalone block or embedded locally within a PL/SQL program unit. An anonymous block cannot be stored in the database.
- Named: This block is a named PL/SQL routine that is stored persistently in the database as a schema object. It can be invoked either from a database session or by another program unit. A named PL/SQL program can be a function, procedure, trigger, or package.
- Nested: A block within another PL/SQL block forms a nested block structure.
So, let's get started with our first anonymous PL/SQL block. The block declares a string and displays it on screen. Note that each line in the program ends with a semi-colon and the block ends with a slash (/) for code execution.
/*Enable the Serveroutput to display block messages*/
SET SERVEROUTPUT ONNote
The SERVEROUTPUT parameter is a SQL*Plus variable that enables the printing of DBMS_OUTPUT messages from a PL/SQL block.
/*Start the PL/SQL block*/
DECLARE
/*Declare a local variable and initialize with a default value*/
L_STR VARCHAR2(50) := 'I am new to PL/SQL';
BEGIN
/*Print the result*/
DBMS_OUTPUT.PUT_LINE('I Said - '||L_STR);
END;
/
I Said - I am new to PL/SQL
PL/SQL procedure successfully completed.
