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.SP Vs Function

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:

  • 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:

Example #1:  Let’s Print a Notice     

Execute Procedure:

Output:

NOTICE:  First Procedure Example:  Hello World

Example 2: How to use transactions in Stored Procedures.

Execute Procedure:

Output:

To check how many tables are created, use below command:

postgres=# \d

This will give List of Relations

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:

Output:

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.

Now let’s see an example to update above accounts.

Execute Stored Procedure:

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

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.

Add a Comment

Your email address will not be published. Required fields are marked *