Usage of database stored procedures

Hello guys. Today I’ll try to explain 2 things. First WHY to use stored procedures. And second, HOW to use them. First of all, a small notice. I’ll explain the subject on PostgreSQL example, because of the fact that I used that language syntax most recently.

So let’s begin with the question “Why?“.

Depending on the client’s needs and his limitations sometimes you need to use them to decrease the number of queries to a minimum. An example of that is if you have a situation of network overhead, stored procedures come in handy to minimize the network traffic. Or on the other hand, sometimes you just need to move some of the database logic to a DB side. A good example of that would be if you have a “Cron job” that will clean (truncate) some of the database’s tables.

My example would be based on that specific case, because it will cover some of the most used features and capabilities of PL/pgSQL (SQL procedural language).

So, to begin with next, and more important topic: How to use the database stored procedures?

Let me first give you an example situation scenario. We have a data_table table that contains IDs of fields that we’ll delete, along with other (in this case irrelevant) data. We also have IDs that we need to delete from our database.

Stored procedure would go something like this:

CREATE OR REPLACE FUNCTION delete_data(IN DATA INTEGER[]) RETURNS INTEGER AS
--DECLARATION OF FUNCTION
$$
DECLARE
--DECLARATION OF LOCAL VARIABLES
SQL VARCHAR;
i INTEGER;
BEGIN
--START OF THE PROCEDURE
i := 0;
loop
--LOOP THROUGH AOUR ARRAY OF DATA
IF (DATA[i][1] IS NULL) THEN
RETURN 1;
--IF WE LOOPED THROUG ALL OF ARRAY, IT'S DONE
exit;
END IF;
SQL := 'DELETE FROM data_table WHERE data_table.id='||DATA[i];
--THE SQL ITSELF, COMBINED WITH VARIABLE
EXECUTE(SQL);
--THE EXECUTE FUNCTION BUILT IN PL/pgSQL, EXECUTES VARCHAR SQLs
i := i + 1;
END loop;
--END OF THE PROCEDURE
 
END;
--END OF FUNCTION'S LOGIC
 
$$
LANGUAGE 'plpgsql';
--DECLARATION OF LANGUAGE USED

As you can see, in PostgreSQL, the syntax is a combination of a few others, but regardles of that, now you can just execute a command from your application that would look something like this:

SELECT delete_data(Array[1,5,158,6,77,43]);

To conclude, you may not see the point at this small example, but imagine if you had 30 small changes like this in your application logic at the same time / file. If that was the case here, you would reduce the number of queries to 30 (depending on stored procedures count), from lets say 30*N queries for your N records that you’d use.

Since we are Magento development team, it would make sense if I tell you here that stored procedures can help a lot with maintaining Magentos database. 😀

Well, I tried to explain how and why you should use stored procedures, and hope I managed to do that.