luctus nec ullamcorper mattis, pulvinar dapibus leo.

Services

Follow Us

Database Management

Stored Procedure for SQL

  • Raj Patel
  • May 12, 2022
Stored Procedure for SQL

What is Stored Procedure

It is a code of SQL that can be used many times based on our requirements during managing data. In programming when you need to execute certain SQL queries multiple times, instead of calling them you can write a stored procedure that can be executed as and when required.

Why we need Stored Procedure

It allows the user to create a query that executes on the server. When you want to perform any repetitive CRUD operation Stored procedure is a better option. The main purpose of the stored procedure is to improve the performance of the database. We can manage the data of tables with good efficiency.

Stored Procedure Function:

It is used to extend the database functionality. Using stored procedures we can create our own custom function and reuse it in our applications. We gave some examples using the PostgreSQL database.

Structured of PostgreSQL function:

CREATE FUNCTION function_name(arg1 type,arg2 type) RETURNS type As $$ Declare variable_name integer; Begin // custom logic End; $$; LANGUAGE 'language-name';

Here, we explain how the structure looks in the stored procedure function. there are two arguments passed in function with its data type, using these two arguments we can write some logical code in the block between BEGIN and END.

After creating the above function, To execute the above function we just need to call below the line

SELECT * FROM function_name(value1,value2);

We also call below the line get function

SELECT function_name(val1,val2);

For more understanding, I wrote one simple example below.

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ #variable_conflict use_variable DECLARE curtime timestamp := now(); BEGIN UPDATE users SET last_modified = curtime, comment = comment WHERE users.id = id; END; $$ LANGUAGE plpgsql;

Here in the above function we just wrote the code for the sum of two numbers and multiplication of two numbers.

We passed four integer variables a, b, sum, and product in the sum_and_product function. Using a and b variables we add and multiply those numbers and store their value to corresponding variables sum and product.

We can also replace the same function using the below code

CREATE OR REPLACE FUNCTION sum_and_product(IN a int,IN b int, OUT sum int, OUT product int) AS $$ BEGIN IF a < 2 THEN RAISE WARNING 'information message %', now(); RAISE NOTICE 'information message %', now(); RAISE INFO 'information message %', now(); END IF; sum := a + b; product := a * b; END; $$ LANGUAGE plpgsql

This code creates function sum_and_product or replaces the function if it is already in the system.

When we need to display the message to the user then we used WARNING, NOTICE, and INFO.

WARNING: It is used when we want to display a warning message to the user.

INFO: It is used when we want to display an information message to the user.

NOTICE: It is used when we want to display the error message to the user.

Difference between Stored procedure and SQL queries:

Sometimes our business requirements are complicated and at that time we cannot use large SQL queries due to performance issues, complications, etc. So we need to use the stored procedures.

Stored procedure without function

Example: Update table1 based on another table which is table2.
do $$ declare f record; begin for f in select id, district_name from table2 loop update table1 set name = f.district_name where key = f.id; end loop; end; $$;

We don’t create functions in this kind of stored procedure. Generally when we want to manipulate some data in the system this kind of stored procedure is useful.

In this example, we are updating the tables1 records using table2. If we use normal queries and code then it will take more time compared to this query.

Exception Handling With Stored procedures:

do $$ declare d record; begin d = 4/0; exception when others then raise notice 'Zero division error'; end; $$;

Create test function:

create function pg_temp.testfunc() returns text as $$ select 'hello'::text $$ language sql;

This schema is created for your connection and is where temporary tables are stored. When your connection expires and is closed this schema is dropped. Turns out that if you create a function on this schema, the schema will be created automatically.

Advantages:

  • Faster to execute the complex database operation
  • It reduces network traffic.
  • It is reusable

Disadvantages:

  • Debugging is difficult.
  • Code is difficult because it contains complex and large queries.
  • Dependent on the database.

Summary:

When we develop some applications, then in certain cases our server is broken. Due to that reason, some important database operations are not executed and they will not migrate based on our business requirements. So, for these situations sometimes we need to execute those database operations manually using stored procedures.