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.

Spread the love

Techify Solutions Pvt Ltd

Techify Solutions Pvt Ltd

Stored Procedure for SQL

Stored Procedure for SQL

What is Stored Procedure

Why we need Stored Procedure

Stored Procedure Function:

Structured of PostgreSQL function:

Difference between Stored procedure and SQL queries:

Stored procedure without function

Exception Handling With Stored procedures:

Create test function:

Advantages:

Disadvantages:

Summary:

Leave a Reply Cancel reply

Stored Procedure for SQL

Stored Procedure for SQL

What is Stored Procedure

Why we need Stored Procedure

Stored Procedure Function:

Structured of PostgreSQL function:

Difference between Stored procedure and SQL queries:

Stored procedure without function

Exception Handling With Stored procedures:

Create test function:

Advantages:

Disadvantages:

Summary:

Related Posts

SQL Joins

Leave a Reply Cancel reply