What is PL/SQL? Architecture, Features, Datatypes, & More

Oracle released PL/SQL in 1991 as an extension to the SQL programming language. It is a procedural language used in the Oracle database to develop complex applications. Oracle believes PL/SQL code can execute up to ten times faster than SQL code. This is because it is performance-optimized and can process database operations more quickly than other programming languages. In this blog, we will learn what is PL/SQL, its features, architecture, advantages, and more.

What is PL/SQL in DBMS? 

PL/SQL is a procedural language extension to the Structured Query Language. It is popular for building database-driven applications. Furthermore, it is a programming language that creates and manages tables, views, procedures, functions, packages, and triggers. It can only be used when you want to execute it in an Oracle database.

Now as we have a clear understanding of the meaning of PL SQL. Let’s dig more into this database.

PL SQL Architecture

The PL/SQL architecture comprises three main components. These are:

• PL/SQL Block: It is the primary input that has the PL/SQL code. It has three different sections to logically divide the code:
  • The declarative section declares the purpose.
  • The execution section processes statements.
  • The exception handling section handles errors.

All the PL/SQL units are considered PL/SQL blocks. Some types of units are function, library, package specification, type, anonymous block, etc. These contain SQL instructions that help in interacting with the database server. 

• PL/SQL Engine: It is mainly responsible for executing PL/SQL code on the server. This engine separates PL/SQL units and SQL parts in the input and handles the PL/SQL units. The SQL part is sent to the database server. 
• Database Server: It is an essential component of the PL/SQL architecture that stores the data and has the SQL executor to parse and execute input SQL statements. PL/SQL engine uses the SQL from PL/SQL units to interact with the database server. 

Also Read: What is MySQL

Advantages of PL/SQL 

The following are a few advantages of using PL/SQL:

• High Performance: PL SQL supports bulk processing, which means it allows efficient processing of large amounts of data. Furthermore, it stores in a compiled form in the database, which makes it faster to execute than interpreted languages like SQL.
• Portability: PL/SQL is a good choice for cross-platform applications because it can be simply ported between various platforms and operating systems. 
• SQL Seamless Integration: PL SQL is an extension of SQL; therefore, it seamlessly integrates with SQL to allow easy data manipulation.
• Modular Programming: Through the use of packages, it supports modular programming. Hence, it allows better code organization and maintenance. 
• Exception Handling: Because of its inbuilt exception handling, it allows the detection and handling of errors in a controlled way.
• Security: To control access to the database and ensure data integrity, PL/SQL includes security features, such as privileges and roles.

Features of PL/SQL

PL/SQL has several key features. Some of these features are:

• Block Structure: PL SQL code is structured into blocks that include declarations, executable statements, and exception handlers.
• Data Types: Numbers, characters, dates, Booleans, and user-defined types are among the data types supported by PL/SQL.
• Procedural Capabilities: PL/SQL allows you to write procedural code using constructs, such as loops, conditional statements, and exception handling.
• Stored Procedures and Functions: It permits the creation of reusable code in the form of stored procedures and functions. 
• Cursors: It provides cursors that enable retrieval and row-by-row processing of data from a database.
• Exception Handling: It offers extensive error-handling mechanisms that enable you to detect and handle errors during program execution.
• Packages: It permits the grouping of related procedures, functions, and variables into a package, making code easier to manage and maintain.
• Triggers: The triggers allow you to automatically execute code in response to database events like insert, update, or delete operations.
• Dynamic SQL: It allows you to construct SQL statements dynamically at runtime. This enables you to create more flexible and potent applications.
• Object-Oriented Features: It allows you to write more complex and modular code by supporting object-oriented programming concepts like encapsulation, inheritance, and polymorphism.

PL SQL Block Structure

A PL/SQL block is a group of statements executed together as one unit. The block may contain both Structured Query Language (SQL) and Procedural Language/Structured Query Language (PL/SQL) instructions that will be processed at the same time, rather than being parsed line by line.

The structure of the block is split into three parts: declaration, execution, and exception handling. The image below shows the different parts of a PL/SQL block, and how they are arranged.

Declaration Section

This section of the PL/SQL block is used to declare variables, cursors, exceptions, subprograms, pragma instructions, and collections that will be needed in the block. 

Features:

• If declarations are not necessary, this section of the PL/SQL block can be omitted. 
• The beginning of a PL/SQL block should contain this part, if present.
• Triggers and anonymous blocks start with ‘DECLARE’ keyword, but in other subprograms, they will have something to mark the declaration section instead. 
• After every declaration section comes the execution statement.

Execution Section

The execution part of PL/SQL must have code that can be executed, meaning it cannot be empty. It is the mandatory component responsible for running the written code. Additionally, there are characteristics associated with this block that should also be taken into account.

Features:

• PL/SQL and SQL code can both be included in this section.
• Multiple nested blocks are possible inside it. 
• The beginning of the section needs to start with the keyword “BEGIN.” 
• For its conclusion, either an “END” must follow or any exception-handling sections, if present.

Exception-Handling Section

Oracle provides an exception-handling section (optional) in PL/SQL blocks that can be used to handle unexpected errors occurring at run-time. This section of the block may also include PL/SQL statements.

Features:

• This is the part of the code that takes care of any exception produced in the execution block.
• The keyword ‘EXCEPTION’ marks the beginning of this section.
• The final part of the PL/SQL block is in this section.
• Once control leaves this section, it can never go back to the execution block.
• The keyword ‘END’ should always be included at the conclusion of this section.

Difference Between SQL and PL SQL

While PL SQL is an addition to SQL that gives it procedural features, SQL is a standard programming language used to manage and manipulate data in relational databases. Let’s know the difference between SQL and PL/SQL:

• Purpose: PL/SQL is used to create applications that use SQL, whereas SQL is primarily used for data manipulation and querying in databases. By enhancing SQL with programming constructs like loops, variables, and conditional statements, PL/SQL makes SQL more effective for creating applications.
• Syntax: SQL has a syntax designed specifically for querying data in a database. However, PL/SQL is a procedural language with syntax for variable declaration and manipulation.
• Execution: During execution, operations are carried out on sets of data rather than single rows because SQL is a set-based language. Since PL/SQL is executed as a procedural language, operations are carried out sequentially.
• Features: PL/SQL boosts SQL with programming constructs like loops, variables, and conditional statements to make it more powerful for application development. However, SQL is restricted to querying and manipulating data in databases.

PL SQL Data Types

SQL data types can also be used in PL SQL because PL SQL data types are a subset of data types in a structured query language. PL/SQL also allows for implicit data type conversion. Therefore, values can be automatically converted from one data type to another. To learn more about the PL SQL meaning and data types, opt for an in-depth SQL course.

1. Scalar Data Types

NUMBER: It is used for numerical data (integers or floating-point numbers).

CHAR and VARCHAR2: It is used for fixed and variable length character data, respectively.

DATE: It is used to store dates and times.

2. Composite Data Types 

RECORD: It is used to store related data items of various types.

TABLE: It stores data collections in memory or as database tables.

VARRAY: It is used to store a variable-sized array of the same data type’s elements.

3. LOB Data Types 

BLOB and CLOB: These are storage types for large amounts of binary and character data.

LONG and LONG RAW: These are used for backward compatibility with older Oracle database versions. However, they are not preferred for new development.

4. Reference Data Types

REF CURSOR: It is a pointer to the result set returned by a SELECT statement.

OBJECTIVE: It enables the creation of user-defined data types that combine data and behavior.

PL/SQL Identifiers

PL/SQL identifiers are simply the names associated with a PL/SQL object. There is an array of possible objects, including constants, variables, exceptions, cursors, procedures, and functions. 

Identifiers can include letters (upper-case and lower-case), numerical characters, along with symbols, such as underscores. However, they have a maximum limit of 30 characters and are case-insensitive. Some of the important properties of PL/SQL identifiers are:

• No more than 30 characters are allowed in the name. 
• The beginning character must be an alphabetical letter.
• A dollar sign (‘$’), underscore (‘_’), or hash sign(‘#’) can be a part of it. 
• It is case-insensitive.
• Spaces and other whitespace characters cannot be included. 

Some common naming conventions are followed with PL/SQL identifiers to avoid confusion. These are: 

• Use the first letter of the variable to specify its declared level. For example, ‘P’ is declared at the perimeter level, ‘L’ at the local block level, and ‘G’ at the global level. 
• The second letter should specify the type of identifier. For example, ‘V’ is used for varchar and char data types, ‘T’ for table type, ‘N’ for number data type, ‘C’ for cursor, and ‘R’ for record type. 
• Here is a complete example: “Ln_user_id” signifies a local level variable of numerical data type. 

Variables in PL/SQL are basic identifiers that users can use to store values. They are associated with valid data types to define storage and processing methods. 

Here is a syntax to declare a variable:

variable_name datatype [NOT NULL := value ];

Additionally, you can take input from the user and store it in a variable. Here is an example of the same:

-- Declare a variable to store user input
ACCEPT user_input CHAR PROMPT 'Enter a value: ';

-- Use the variable in your PL/SQL block
DECLARE
  v_user_input VARCHAR2(50);
BEGIN
  v_user_input := '&user_input'; -- Access the user_input variable using substitution
  DBMS_OUTPUT.PUT_LINE('You entered: ' || v_user_input);
END;
/

Here, you will store the input entered by the user in the variable “v_user_input” in the PL/SQL block. The “v” here signifies the VARCHAR data type.

Conclusion

PL/SQL adds procedural features to SQL for creating applications that interact with Oracle databases. Applications can be managed and maintained easily because it enables developers to write flexible and potent code. This includes stored procedures, triggers, and packages. Learning about what is PL/SQL can be useful if you are interested in programming or working with Oracle databases.

Were you able to understand the basic concepts of PL/SQL through this blog? Share with us in the comments sections below. Learn other important concepts of PL/SQL through this blog on PL SQL interview questions. Also, do not forget to check out these interview tips to ace your next interview.

FAQs