Handling SQL exceptions in a Stored Procedure

Hello. In this tutorial, we will learn how to handle SQL exceptions in the stored procedure via the postgresql database.

1. Introduction

SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –

• Parsing – Process to check the SQL query syntax
• Binding – Process to check the SQL query semantics
• Optimization – Process to generate the SQL query execution plan

1.1 Usage of SQL

Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –

• Execute queries against the database
• Retrieve data from the database
• Insert new records into the database
• Update existing records into the database
• Created stored procedures, functions, and materialized views in the database
• Create users and grant permissions
• Set permissions on tables, stored procedures, functions, and materialized views

1.2 Raise exception

Raising an exception in the postgresql database is used to raise the statement for reporting the warning, errors, and other message types within the function or stored procedure. There are different levels of raise exceptions available in the postgresql database i.e. info, notice, warning, debug, log, and notice. The raise exception statement is used to raise errors and report the messages. By default, the exception level is used to raise the exception. Raise exception is represented by the below syntax.

-- syntax
RAISE [LEVEL] (Level which we have used with raise exception statement.) [FORMAT]

OR

RAISE;

where –

• RAISE keyword defines the exception statement used to raise the exception in postgresql
• LEVEL keyword defines the error severity. Every level represents the detailed information about the error or warning message based on the priority of the levels
• FORMAT keyword defines an error message that we want to display. If the message contains some variable value then we use the % symbol

This syntax offers some advantages i.e. –

• Helps to raise the statement for reporting the warning
• Offers to raise exceptions in various parameters
• Offers to have multiple levels for raising errors and warnings

2. Handling SQL exceptions in a Stored Procedure

Let us dive into some practice implementation on the postgresql database.

2.1 Pre-requirement – Postgres Setup

Usually, setting up the database is a tedious step but with technological advancements, this process has become simple with the help of Docker. Readers can watch the video available at this link to understand the Docker installation on Windows OS. Open the terminal and trigger the following commands to get the postgresql up and running on the local machine.

Docker commands

-- command to run postgres on docker --
-- remember to change the password --
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to enter the password of your choice. If everything goes well the postgresql database server would be up and running on a port number – 5433 and you can connect with the pgAdmin gui tool for connecting to the server.

2.2 Creating a Table

To implement this tutorial I will be creating a table named – division in the practice database. The table will hold the data inserted from the stored procedure.

Create Table

-- sql table
create table if not exists division(
  id serial, 
  val1 int not null, 
  val2 int not null, 
  res numeric not null, 
  primary key(id)
);

If everything goes well the table will be created as shown in fig. 2.

2.3 Creating the Stored Procedure

Stored Procedures in the postgresql help extend the database functionality by creating the user-defined functions. It helps to create the triggers or custom aggregate functions and add some complex features e.g. control features and complex calculations. It is helpful –

• Reduce the number of trips between application and database
• Increase application performance as the user-defined functions and stored procedures are pre-compiled and stored in a postgresql database server
• Reusable in many applications

The stored procedure created below will be responsible to add the record in the division table or raise an exception with a custom message in the division by zero (X/0) scenarios.

Create Stored procedure

-- sql stored procedure
create or replace procedure div_proc(num1 int, num2 int) as 
$
	declare 
	total numeric;
	begin
		total := num1/num2;
		insert into public.division  (val1, val2, res) values (num1, num2, total);
		raise notice 'Data inserted at %', now();	-- will help in audit logging
	exception when others then
		raise exception 'Cannot divide by zero exception';
		-- raise notice '%, %', sqlstate, sqlerrm;
	end;
$ language 'plpgsql'

If everything goes well the stored procedure will be created as shown in fig. 3.

2.4 Calling the Stored Procedure

Let us start the calling of the stored procedure with a positive scenario. To call the stored procedure we will use the call command.

Command 1

call div_proc(40, 6);

Here the stored procedure will perform the calculation based on the input arguments and save the details in the division table. The inserted information can be verified with the help of the select sql statement as shown in the below figure.

Now let us perform a negative scenario wherein the stored procedure will throw an exception while performing the calculation. The procedure will throw the custom error message as specified in the procedure.

Command 2

-- will result in raising exception in stored procedure
call div_proc(100, 0);

The above statement will raise an exception with a custom message (i.e. Cannot divide by zero exception) and show the error on the screen as below.

3. Summary

In this tutorial, we learned the brief to sql, stored procedure, and raise exception syntax in the postgresql database. Raise exception syntax is used to raise the warning or error message. There are six levels of raise exception e.g. notice, log, warn, debug, info, and exception. You can download the sql scripts and docker commands used in this tutorial from the Downloads section.

4. Download the Scripts

This was a tutorial on learning the sql exception syntax in the postgresql database.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.