Stored Procedures

Stored Procedures

• It is an database object that contains a block of SQL code. In application code, we simply call these procedure to get and set data.
  • Store and organize SQL
  • Faster execution
  • Data security

Creating a stored Procedure

• Change of delimiter is only for MySQL.

  DELIMITER $$
  CREATE PROCEDURE get_invoices_with_balance()
  BEGIN
  	SELECT *,(invoice_total-payment_total) AS balance
  	FROM invoices
  	WHERE (invoice_total-payment_total) > 0;
  END$$
  DELIMITER ;

• CALLING of Stored Procedure

  CALL get_invoices_with_balance()

Droping a Stored Procedure

• DROP PROCRDURE IF EXISTS get_clients;

Parameters in Stored Procedure

• DROP PROCEDURE IF EXISTS get_invoices_by_client;
  DELIMITER $$
  CREATE PROCEDURE get_invoices_by_client
  (
  	client_id INT
  )
  BEGIN
  	SELECT * 
      FROM invoices i
      WHERE i.client_id = client_id; 
  END$$
  DELIMITER ;
  

Stored Procedure parameters with default value

•   DROP PROCEDURE IF EXISTS get_payments;
    
    DELIMITER $$
    CREATE PROCEDURE get_payments
    (
    	client_Id INT,
        payment_method_id TINYINT
    )
    BEGIN
    	SELECT *
        FROM payments p
        WHERE p.client_id = IFNULL(client_Id, p.client_id) AND p.payment_method = IFNULL(payment_method_id, p.payment_method);
    END $$
    DELIMITER ;
  

Stored Procedure parameter Validation

• DROP PROCEDURE IF EXISTS get_payments;
  
  DELIMITER $$
  CREATE PROCEDURE get_payments
  (
  	invoice_id INT,
      payment_amout DECIMAL(9,2),
      payment_date DATE
  )
  BEGIN
  	IF payment_amount <= 0 THEN
  		SIGNAL SQLSTATE '22003'
          SET MESSAGE_TEXT = 'Invalid payment amount';
  	END IF;
      
  	UPDATE invoices i
      SET
  		i.payment_total = payment_amount,
          i.payment_date = payment_date
       WHERE i.invoice_id = invoice_id;    
  END $$
  DELIMITER ;
  

Stored Procedure output parameters

• DROP PROCEDURE IF EXISTS get_unpaid_invoices_for_client;
  
  DELIMITER $$
  CREATE PROCEDURE get_unpaid_invoices_for_client
  (
  	client_id INT,
      OUT invoices_count INT,
      OUT invoices_total DECIMAL(9,2)
  )
  BEGIN
  	SELECT COUNT(*), SUM(invoice_total)
      INTO invoices_count, invoices_total
      FROM invoices i
      WHERE i.client_id = client_id AND payment_total =0;
  END $$
  DELIMITER ;

• Calling of stored procedure which return output parameters

  set @invoices_count = 0;
  set @invoices_total = 0;
  call sql_invoicing.get_unpaid_invoices_for_client(1, @invoices_count, @invoices_total);
  select @invoices_count, @invoices_total;
  

Variables

• User or session variable declaration

  set @invoices_count = 0;

• Local variable

  DROP PROCEDURE IF EXISTS get_risk_factor;
  
  DELIMITER $$
  CREATE PROCEDURE get_risk_factor()
  BEGIN
  	DECLARE risk_factor DECIMAL(9,2);   -- local variable declaration
      DECLARE invoices_total DECIMAL(9,2);
      DECLARE invoices_count INT;
      
  	SELECT COUNT(*), SUM(invoice_total)
      INTO invoices_count, invoices_total
      FROM invoices;
      
      SET risk_factor = invoices_total / invoices_count * 5;
      
      SELECT risk_fator;
  END $$
  DELIMITER ;
  

Functions

• DELIMITER $$
  CREATE FUNCTION get_risk_factor_for_client
  (
  	client_id INT
  )
  RETURNS INTEGER
  -- DETERMINISTIC
  READS SQL DATA
  -- MODIFIES SQL DATA 
  BEGIN
       DECLARE risk_factor DECIMAL(9,2) DEFAULT 0 ;
       DECLARE invoices_total DECIMAL(9,2);
       DECLARE invoices_count INT;
      
  	SELECT COUNT(*), SUM(invoice_total)
      INTO invoices_count, invoices_total
      FROM invoices i
      WHERE i.client_id = client_id;
      
      SET risk_factor = invoices_total / invoices_count * 5;
      
      RETURN IFNULL(risk_factor,0);
  END $$
  DELIMITER ;

• Usage of functions

  SELECT client_id,
  	   name,
         get_risk_factor_for_client(client_id) AS risk_factor
  FROM clients;