PL SQL Tutorial: Everything you need to know about PL SQL

PL/SQL is the Oracle Corporation’s procedural extension for SQL, i.e. Procedural Language for Structured Query Language (SQL) and the Oracle relational database. It was developed in the late 1980s. PL/SQL is out there in Oracle Database, TimesTen in-memory database, and IBM DB 2. PL/SQL is a block-structured language. The written programs in PL/SQL are logical blocks that can contain any number of nested sub-blocks.

PL/SQL ensures logical processing of SQL statements by enhancing the data’s safety, portability, and robustness. PL/SQL instructs the compiler ‘what should be done through SQL and ‘how it should be done through its procedural manner. Like other database languages, it gives control to the programmers by applying loops, conditions, and object-oriented concepts.

Characteristics of PL/SQL:

• PL/SQL is seamlessly integrated with SQL.
• It offers an extensive feature of error checking.
• It also offers numerous varieties of data types.
• It offers programmers to select programming structures.
• It is block-structured programming that provides support to functions and procedures.
• It supports object-oriented programming concepts and features.
• It helps the event of web applications and server page development.

Advantages of PL/SQL:

• SQL is that the quality database language, and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL.
• In Static SQL DML operations and transaction, control is supported from PL/SQL block. In Dynamic SQL, embedded DDL statements are allowed in PL/SQL blocks.
• It sends an entire block of statements to the database on just one occasion. This helps to reduce network traffic and also provides high performance for the applications.
• PL/SQL provides high productivity performance to programmers because it can query, transform, and update data during a database.
• PL/SQL reduces time consumption on design and debugging by strong features, like exception handling, encapsulation, data hiding, and object-oriented data types.
• The applications written in PL/SQL are highly portable.
• PL/SQL gives a high-security level.
• PL/SQL gives access to predefined SQL packages.

Disadvantages of PL/SQL:

• Procedures that are stored in PL/SQL use high memory spaces.
• These stored procedures lack functionality debugging. 
• Any change taking place in the primary database needs a change in the presentation layer also.
• It does not separate the roles of back-end developer and front-end developer.
• It is very difficult to separate HTML development from PL/SQL development.

Also Read: SQL Tutorial for Beginners

The Architecture of PL/SQL:

PL/SQL block:

PL/SQL block is the part where actual PL/SQL code is present. This part contains different sections to divide the code logically, like the declaration section for declaring purpose, execution section for processing, exception handling section for handling errors. It also consists of the SQL instruction that is used to interact with the database server. All the PL/SQL units are managed as PL/SQL blocks, and this is the stage where the architecture starts serving as a primary input.

Following are some different types of PL/SQL units:

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

PL/SQL Engine:

PL/SQL engine is the section where actual processing of the Pl/SQL codes takes place. PL/SQL engine divides PL/SQL units and SQL part in the input. The divided PL/SQL units will be managed by the PL/SQL engine itself. The SQL part, which was separated by the PL/SQL engine, will be sent to the database server, where the actual interaction with the database occurs. It can be installed in both the database server and in the application server.

Database Server:

This is the most important part of the PL/SQL unit, which stores the data. From the PL/SQL unit, the PL/SQL engine utilizes SQL to interact with the database server. It consists of an SQL executor which parses the input SQL statements and executes the same.

Difference between SQL and PL/SQL:

PL/SQL Installation:

PL/SQL is not an independent programming language; it’s a tool within the Oracle programming. To work with PL/SQL, we will use SQL*Plus. SQL* Plus is an interactive tool that allows us to write SQL and PL/SQL commands or statements at the command prompt. After providing the commands to the prompt, the commands are then sent to the database for processing. Once the database processes the commands, the results are displayed on the screen.

To execute PL/SQL programs, we should have the Oracle RDBMS Server installed in our systems responsible for the execution of the SQL commands.

First of all, we are going to download Oracle Database 11g Release 2 on Microsoft Windows (x64) from Oracle official website. We will download the 32-bit or the 64-bit version of the installation as per our operating system requirement. We can also follow similar steps for the Linux operating system.

After downloading Oracle Database 11gR2 Express Edition for Windows x64 zip file unzip or extract the files inside the zip file.

1. JPG

Following are the steps to install and Launch Oracle database successfully.

1. Click on the setup and the install wizard window will start preparing it for installation. This is the first screen which we will see. Click on the next button.
2. Accept the license agreement and click on the next button.
3. Now choose the destination location for the file installation. And click on the next button.
4. Set the database password and click on the next button.
5. The final installation window appears on the screen, click on install.
6. Oracle installation will install the required configuration files. This will take some time. 
7. The installation setup configures the database.
8. After installation, we will see the following window. Click on the finish button.
9. Now, we will verify our installation. At the command prompt, write the following command and press enter to verify installation on Windows.

The installation is successfully completed.

Text Editor:

It is always recommended to use the command files while running the programs from command prompt. There are various text editors to create the command files and run them easily:

• We can type code in text editors like Notepad, Notepad++, EditPlus, UltraEdit, etc.
• Save this program file with the extension of .sql in the home directory.
• Now, at the SQL*Plus command prompt type file_name and press enter to execute your program.

We can also directly execute programs without creating programs. Just write the code at the SQL command prompt and press enter key and the program will be executed.

PL/SQL Program Structure: 

The anonymous code block structure has three main sections that are the declaration, execution, and exception handling. In PL/SQL, only the execution section is mandatory, and the other sections are optional. 

Declaration Section

In the declaration section, we define data types, structures, and variables. We declare variables in this section by giving those names, data types, and initial values.

Execution Section

The execution section is the most important and required section in a block structure, and it must contain at least one statement. The execution section is the place where we put the execution code or business logic code. We use both procedural and SQL statements in this section.

Exception Handling

The exception section is the place where we put the code to handle exceptions. We can handle and catch exceptions in this section.

Basic Syntax of PL/SQL:

The PL/SQL anonymous code block structure has three main sections that are the declaration, execution, and exception handling:

Declare

This section starts with the keyword DECLARE. In the declaration section, we define data types, structures, and variables. The Declaration section is an optional section.

Executable Commands

The section is enclosed between the two keywords, BEGIN and END, and it’s a mandatory section. It contains all the executable PL/SQL statements of the program. This section should contain at least one executable statement of code, which can also be a NULL command to point out that nothing is going to be executed.

Exception Handling

This section starts with the keyword EXCEPTION. This section is optional and contains exceptions that handle errors if present in the program.

Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks are nested within other 

PL/SQL blocks using BEGIN and END keywords. Following is the primary structure of a PL/SQL block-structure: 

Example:

DECLARE

	BEGIN

		EXCEPTION

	END;

First ‘Hello World’ program:

BEGIN
	dbms_output.put_line ('Hello World');
	END;
	/

The END; line signals the top of the PL/SQL block i.e. execution starts from top. When the above code is executed at the SQL command prompt, it produces the following result −

Hello World

The PL/SQL Identifiers:

PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words that cannot be used for other programs. The identifiers contain a letter optionally followed by more letters, numerals, symbols, underscores, and number signs and should be in the length of 30 characters.

Identifiers are not case-sensitive. So we use integer or INTEGER to represent a numeric value. Note that we cannot use a reserved keyword as an identifier.

The PL/SQL Comments:

In programming languages, comments are the statements that explain the program to the reader, which will be included within the code. All programming languages allow some form of comments within the code block. The PL/SQL also supports single-line and multi-line comments. All the instructions inside the comment are ignored by the PL/SQL compiler. The single-line comments in PL/SQL start with a delimiter — (double hyphen), and multi-line comments are enclosed within /* and */.

DECLARE
	-- variable declaration
	msg varchar2(20):= 'Hello, World!';
	BEGIN
	/*
	* PL/SQL executable statements
	*/
	dbms_output.put_line (msg);
	END;
	/

Following is the output of above code −

Hello World

PL/SQL Datatypes:

Every Data Type is associated with some particular type of storage format and range constraints in programming languages. It defines how the data should be stored, handled, and treated by Oracle during the data storage and processing. The key difference between PL/SQL and SQL data types are, the PL/SQL datatypes are utilized in the PL/SQL code blocks, but SQL data types are limited only to the tables and columns.

Types of Data Types:

Scalar: A Single value with no internal components, such as a NUMBER, DATE, or BOOLEAN comes under Scalar data types.

Composite: The Data items that have internal components and are often accessed individually. Example collection and records.

Reference: These data types are pointers to other data items present in memory.

Large Object (LOB): The pointers to large objects or files that are stored separately from other data items, like text, graphic images, video clips, and sound waveforms come under large objects.

PL/SQL Scalar Data Types and Subtypes 

In PL/SQL, some data types have subtypes of data types. For example, the datatype NUMBER has a subtype INTEGER, BINARY_INTEGER, BINARY_FLOAT, etc. We can use these subtypes in PL/SQL programs to make the data types compatible with data types used in other programs while embedding the PL/SQL code in other programs.

PL/SQL Numeric Data Types and Subtypes

Following are the valid declarations of subtype of NUMBER datatype:

DECLARE —declare keyword used for declaration

num1 INTEGER; — num1 is a valid variable name for storing integer type value

num2 REAL; — num2 is a valid variable name for storing real type value

num3 DOUBLE PRECISION; — num3 is a valid variable name for storing double precision type value:

BEGIN
null;
END;
/

 PL/SQL Character Data Types and Subtypes

PL/SQL Boolean Data Types

The BOOLEAN datatype stores logical values that are used to perform logical operations. The logical values such as TRUE and FALSE and the value NULL comes under BOOLEAN datatype.

SQL has no datatype like BOOLEAN. Therefore, Boolean values cannot be used in −

• SQL statements
• Built-in SQL functions 
• PL/SQL functions invoked from SQL statements

PL/SQL Datetime and Interval Types

The DATETIME datatype is used to store fixed-length Datetime values, which include the time of day in seconds since midnight. The default date format might be ‘DD-MON-YY’, which includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two-digit number for the year. For example, 07-APR-21. Following is the valid values for each field −

 PL/SQL Large Object (LOB) Data Types:

Large Object (LOB) datatypes are referred to large data items like text files, graphical images, video clips, and sound waveforms. Following are some PL/SQL LOB data types −

NULLs in PL/SQL:

PL/SQL NULL values represent missing or unknown data and that they aren’t an integer, a personality, or the other specific data type. Note that NULL isn’t an equivalent as an empty data string or the null character value ‘\0’. A null is often assigned but it can’t be equated with anything, including itself.

PL/SQL Variables:

PL/SQL programming language allows to define various sorts of variables, like date time data types, records, collections, etc. which we will cover in subsequent chapters

Variable Declaration in PL/SQL:

The variables should be declared in the declaration section of the PL/SQL code block structure. It can also be declared in a package as a global variable. When a variable is declared, PL/SQL allocates memory to the variable and stores the desired type value and the storage location is identified by the variable name.

The syntax for declaring a variable is −

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

Where, variable_name is a valid variable identifier in PL/SQL, datatype must be a valid PL/SQL data type. Some valid variable declarations are given below:

age number(3); 

pi CONSTANT double precision := 3.1415; 

name varchar2(25); 

address varchar2(100);

In the example the size, scale or precision limit with the data type is also provided, it is called constrained declaration. Constrained declarations occupy less memory than unconstrained declarations. For example −

Initializing Variables in PL/SQL:

Variable declaration in PL/SQL assigns it a default value NULL. If we want to initialize a variable with a not NULL value, we can do this during the variable declaration, using of the following keywords −

• The DEFAULT keyword
• The assignment operator(=)

For example −

counter binary_integer := 0; 

greetings varchar2(20) DEFAULT 'Have a Good Day';

We can also specify that a variable should not have a NULL value using the NOT NULL constraint. If we use the NOT NULL constraint, we must assign an initial value to that variable. It is a good practice to initialize variables properly.

Following is an example of various types of variables:

DECLARE
	a integer :=10;
	b integer :=20;
	c integer;
	d real;
BEGIN
	c := a+b;
	dbms_ouput.put_line('Value of variable c:' ||c);
	d :=45.5/8.3;
	dbms_ouput.put_line('Value of variable d:' ||d);
END;
/

Following is the output of above code:

Value of c: 30 

Value of f: 23.333333333333333333  

Variable Scope in PL/SQL:

In PL/SQL, we can perform nesting of blocks, i.e., each program block may contain another inner block. If a variable is declared inside an inner block it is not accessed by the outer block. And, if a variable is declared and accessible to an outer block, it cannot be accessed by all nested inner blocks. There are two types of variable scope −

• Local variables − the variables declared inside an inner block and is not accessible to outer blocks.
• Global variables − the variables declared in the outermost block or in a package as global variable.

Following is the example of Local and Global variables:

DECLARE
	p number :=20;     --global variables
	q number :=30;
BEGIN
	dbms_output.put_line('Global variable p:' || p);
	dbms_output.put_line('Global variable q:' || q);
	DECLARE
	p number :=102;    --local variables
	q number :=130;
	BEGIN
	dbms_output.put_line('Local variable p:' || p);
	dbms_output.put_line('Local  variable q:' || q);
	END;
END;
/

Following will be the output of above code:

Global variable p: 20

Global variable q: 30 

Local variable p: 120 

Local variable q: 130   

PL/SQL Constants and Literals:

A constant holds a value that, when declared, doesn’t change within the program. A constant declaration specifies its name, data type, and value and also allocates storage for it in the memory. The declaration also imposes the NOT NULL constraint. 

A constant is declared using the CONSTANT keyword. An initial value is assigned to it which is not allowed to be changed. For example −

Program to find circumference and area of circle:

PI CONSTANT NUMBER := 3.141592654; 
DECLARE 
   pi constant number := 3.141592654; 
   radius number(5,2);  
   dia number(5,2);  
   circumference number(7, 2); 
   area number (10, 2); 
BEGIN   
   radius := 9.5;  
   dia := radius * 2;  
   circumference := 2.0 * pi * radius; 
   area := pi * radius * radius; 
   dbms_output.put_line('Radius: ' || radius); 
   dbms_output.put_line('Diameter: ' || dia); 
   dbms_output.put_line('Circumference: ' || circumference); 
   dbms_output.put_line('Area: ' || area); 
END;
/

Following will be the result of the above code:

Radius: 9.5 

Diameter: 19 

Circumference: 59.69 

Area: 283.53  

PL/SQL Literals:

A literal is a particular numeric, character, string, or Boolean value which is not represented by an identifier. For example, TRUE, FALSE, 2021, NULL, ‘Programming’ are all literals. PL/SQL literals are case-sensitive. PL/SQL has the following types of literals:

• Character Literals: – Example ‘A’ ‘%’ ‘9’ ‘ ‘ ‘z’ ‘(‘.

• Numeric Literals: – Example 040 68 -15 0 +367, 6.667 0.0 -12.7 3.14159 +8900.00, 4E5 1.0E-8 3.13159e0 -1E38 -4.5e-3.
• String Literals: – Example ‘Hello, world!’, ‘Procedural Language for SQL’, ’07-APR-21′.

• BOOLEAN Literals: – Example TRUE, FALSE, and NULL.
• Date and Time Literals: – Example DATE ‘1978-12-25’; TIMESTAMP ‘2020-10-29 12:01:01’;

Following is the example of  embedding the single quotes in a string literal, place two single quotes next to each other as shown in the following program −

DECLARE 
   msg  varchar2(30):= 'That's A good book to study programming!'; 
BEGIN 
   dbms_output.put_line(msg); 
END; 
/  

Following will be the  result of above code:

That’s a good book to study programming!  

PL/SQL Operators:

An operator is a symbol that tells the compiler to perform some specific mathematical or logical operations. PL/SQL language consists of  rich in built-in operators. Following types of operators:

• Arithmetic Operators
• Relational Operators
• Comparison Operators
• Logical Operators
• String operators

Arithmetic Operators:

Following are arithmetic operators supported by PL/SQL. Let’s understand this with an example. Suppose we have two variables a and b with the values 2 and 40 respectively. We will perform the arithmetic operation on both the variables.

Relational Operators:

Relational operators are used to perform comparison between two expressions or values and provide a Boolean value as a result. Let’s understand this with an example. Suppose we have two variables a and b with the values 2 and 40 respectively. 

Comparison Operators:

Comparison operators are used for comparing one expression to a different one. The result after using these operators are always be TRUE, FALSE or NULL. Comparison operator is mainly in SQL statements.

Logical Operators:

Logical operators are used to perform comparison between two expressions or values and provide a Boolean value as a result. Let’s understand this with an example. Suppose we have two variables a and b with the values 2 and 40 respectively. 

This brings us to the end of the PL SQL Tutorial. We hope that you are now better equipped with the concepts.

Wondering where to learn in-demand skills for free? Check out the courses on Great Learning Academy. Enroll to any course in several domains such as AI, Machine Learning, Programming, learn the in demand skill and get your free certificate.

Also Read: SQL Interview Questions and Answers you must Prepare in 2021