MySQL – User Functions

Background

Let us write a simple user function in MySQL.

Code

SQL

fnCalcInt

Declaration



create database if not exists testdb;

use testdb;

DROP FUNCTION IF EXISTS fnCalcInt;
DELIMITER //

CREATE FUNCTION fnCalcInt 
(
      operator char(1)
    , number1   int
    , number2   int
) 
RETURNS DECIMAL(30,4)
DETERMINISTIC 
BEGIN
    
   return 
   (
        case
        
            when ( operator = '+' ) THEN 
                number1 + number2
              
            when ( operator = '-' ) THEN 
                number1 - number2

            when ( operator = '*' ) THEN 
                number1 * number2


            when ( 
                         ( operator = '/' ) 
                     and ( number2 != 0 )
                ) 
                THEN 
                      number1 
                    / NULLIF(number2, 0)
                
            when ( operator = '^' ) THEN 
                power(number1, number2)
                
            ELSE 
                null
                
        end    
   );
   
END//

-- restore the default delimiter ';'
DELIMITER ; 
    

 

Usage

 


set @operator = '^';
set @number1 = 5;
set @number2 = 3;

SELECT 
          @operator
        , @number1
        , @number2
        , fnCalcInt(@operator, @number1, @number2) as 'answer'
;   

Code Sharing

Github

Gist

DanielAdeniji/MySQL.function.fnCalcInt.sql
Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s