SQL for QA: Tutorial#12 – Stored Procedure
A Stored Procedure can be used like a modular programming where we must create procedure once, store it and call for several times whenever it is needed. Execution time is fast in stored procedures. Procedures reduce network traffic and provide security to the data.
In previous article we had seen user defined functions so let’s see few differences between Functions and Procedures.
Now we will see how to create and execute Procedures in PostgresSQL. PostgreSQL version 11 introduced stored procedures which support transactions, before that it was not possible to create stored procedures in PostgresSQL. From version 11 stored procedures added as a new schema object and which is similar object to stored function but without return value.
Syntax to Create Stored Procedure:
|
1 2 3 4 5 6 |
CREATE OR REPLACE PROCEDURE <Procedure_Name > (Parameter_List) LANGUAGE Language_Name AS $$ <Stored_Procedure_Body>; $$; |
- Procedure_Name: is the name of the procedure, write after CREATE PROCEDURE keywords, after Procedure_Name you can give list of parameters separated by comma inside parentheses.
- Language_Name: Specify the procedural language of the procedure e.g., plpgsql in case PL/pgSQL is used.
- Code : or logic is placed inside the AS followed by $$ symbol
- Finally, use $$ to end the stored procedure.
Syntax to Execute a Stored Procedure:
|
1 |
CALL <Procedure_Name> (Parameter_List); |
Example #1: Let’s Print a Notice
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE Procedure_Ex(INOUT p TEXT) AS $$ BEGIN RAISE NOTICE 'First Procedure Example: %', p ; END ; $$ LANGUAGE plpgsql ; |
Execute Procedure:
|
1 |
CALL Procedure_Ex (' Hello World '); |
Output:
NOTICE: First Procedure Example: Hello World
Example 2: How to use transactions in Stored Procedures.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE OR REPLACE PROCEDURE Transaction_Ex() LANGUAGE plpgsql AS $$ DECLARE BEGIN CREATE TABLE Table_1 (id int); INSERT INTO Table_1 VALUES (100); COMMIT; CREATE TABLE Table_2 (id int); INSERT INTO Table_2 VALUES (200); ROLLBACK; END $$; |
Execute Procedure:
|
1 |
CALL Transaction_Ex(); |
Output:
To check how many tables are created, use below command:
postgres=# \d
This will give List of Relations
|
1 2 3 4 5 |
Schema | Name | Type |Owner -------+---------+--------+---------- public | Table_1 | table | postgres |
After command \d we can see Table_1 is created because the table was committed , the Table_2 has not been created because of the rollback inside the stored procedure.
Now check the value inserted into the table:
|
1 |
SELECT * FROM Table_1; |
Output:
|
1 2 3 |
id ---- 100 |
Example 3: Create SP which transfers a specified amount of money from one account to another.
Suppose we have Account_Table (Acc_No, Name, Balance) with 2 rows in it.
|
1 2 3 4 |
Acc_No | Name | Balance -------+----------+----------- 101 | Jane | 20000 201 | Peter | 10000 |
Now let’s see an example to update above accounts.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE OR REPLACE PROCEDURE Ttransfer_Money(INT, INT, DEC) LANGUAGE plpgsql AS $$ BEGIN -- subtracting the amount from the sender's account UPDATE Account_Table SET Balance = Balance - $3 WHERE AccNo = $1; -- adding the amount to the receiver's account UPDATE Account_Table SET Balance = Balance + $3 WHERE AccNo = $2; COMMIT; END; $$; |
Execute Stored Procedure:
|
1 |
CALL Ttransfer_Money(101,201,2000); |
Above statement transfer 2000 from an account with id 101 (Jane) to the account with id 201 (Peter).
Output:
Let’s verify the data in the given table
|
1 |
SELECT * FROM Account_Table; |
|
1 2 3 4 5 |
Acc_No | Name | Balance -------+-------+----------- 101 | Jane | 18000 201 | Peter | 12000 |
It’s all about Stored Procedures. We executed few examples by using PostgresSQL 11, So we can say procedures are nothing but the extended version of Stored functions with added functionality to handle transactions.
