Stored Procedure

Stored Procedure
  User defined functions stored in database which contains set of SQL statements
  It can accept parameters, return values, and can be used to perform various operations such as data manipulation, validation, and business logic implementation.
  Written in SQL or a procedural language supported by the database management system (DBMS).
Advantages:
  1. Performance: Stored procedures can reduce network traffic and improve performance by executing multiple SQL statements in a single call.
  2. Security: They can restrict direct access to tables and provide a controlled interface for data access.
  3. Reusability: Stored procedures can be reused across different applications and users.
  4. Maintainability: Changes to business logic can be made in the stored procedure without affecting the application code.
  5. Transaction Management: They can handle complex transactions and ensure data integrity.
  6. Code Organization: Stored procedures help in organizing code and separating business logic from application code.
  7. Error Handling: They can include error handling mechanisms to manage exceptions and errors.
  8. Version Control: Changes to stored procedures can be versioned and tracked, making it easier to manage changes over time.
  9. Scalability: Stored procedures can be optimized for performance, making them suitable for large-scale applications.

Examples

Transfer Funds from account1 to account2

1. Suppose we have 2 accounts, Raj and Hari, and we want to transfer funds from Raj to Hari.
2. We can create a function = stored procedure that takes the account numbers(or name) and the amount to be transferred as parameters.
3. The stored procedure will then perform the following steps:
  a. Check if the source account has sufficient balance.
  b. Deduct the amount from the source account.
  c. Add the amount to the destination account.

// Install postgres on Windows

C:\Users\ursam>psql -U postgres
Password for user postgres:

// Create a database: crm
postgres=# \c crm;      // Connect to the database

//Create a table: accounts
crm=# create table accounts (
  id serial primary key,
  name varchar(100) not null,
  balance numeric(15,2) not null
);
CREATE TABLE

crm=# \d accounts
                                  Table "public.accounts"
 Column  |          Type          | Collation | Nullable |             Default
---------+------------------------+-----------+----------+----------------------------------
 id      | integer                |           | not null | generated by default as identity
 name    | character varying(100) |           | not null |
 balance | numeric(15,2)          |           | not null |
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (id)

// Insert data into the table
crm=# insert into accounts (name, balance) values ('Raj', 100.00), ('Hari', 100.00);
INSERT 0 2
crm=# select * from accounts;
 id | name | balance
----+------+---------
  1 | raj  |  100.00
  2 | hari |  100.00

// Create a stored procedure to transfer funds
create or replace procedure transfer(
   sender int,
   receiver int, 
   amount dec
)
language plpgsql    
as $$
begin
    -- subtracting the amount from the sender's account 
    update accounts 
    set balance = balance - amount 
    where id = sender;

    -- adding the amount to the receiver's account
    update accounts 
    set balance = balance + amount 
    where id = receiver;

    commit;
end;$$;

// Call the stored procedure to transfer funds
crm=# call transfer(1, 2, 50.00);
CALL

// Check the updated balances
crm=# select * from accounts;
 id | name | balance
----+------+---------
  1 | raj  |   50.00
  2 | hari |  150.00


      

Commands

List Stored Procedure

crm=# \df
List of functions
Schema |   Name   | Result data type |                    Argument data types                    | Type
--------+----------+------------------+-----------------------------------------------------------+------
public | transfer |                  | IN sender integer, IN receiver integer, IN amount numeric | proc
(1 row)
      

Drop Stored Procedure

crm=# drop procedure transfer(integer, integer, dec);
      

Stored Procedures vs Functions

CREATE OR REPLACE FUNCTION get_employee_count()
RETURNS integer AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM employee);
END;
$$ LANGUAGE plpgsql;

-- Call as:
SELECT get_employee_count();
          

CREATE OR REPLACE PROCEDURE raise_salary(dept_id integer, percentage numeric)
AS $$
BEGIN
    UPDATE employee 
    SET salary = salary * (1 + percentage/100)
    WHERE department_id = dept_id;
    
    COMMIT; -- Can commit within procedure
END;
$$ LANGUAGE plpgsql;

-- Call as:
CALL raise_salary(10, 5); -- Give 5% raise to dept 10