Tuesday, February 9, 2016

MySQL - An alternative for using constants in MySQL stored procedures

MySQL does not support declaring constants to be shared between different stored procedures. But some other RDBMS like Oracle supports this feature. To still have something similar to constants in Oracle PL/SQL in MySQL, I found that following workaround acts as an alternative to declaring constant variables.

In MySQL, you can add some characteristics of the your stored function in the stored function definition. you can mark a stored function as a DETERMINISTIC or NON-DETERMINISTIC.
DETERMINISTIC means for same input, the function returns the same output every time it invokes.
In addition to this, you can mark a stored function to say whether the function has SQL or not, whether the SQL in the function does any update or read only.

What I am going to use as an alternative to constant is to use a DETERMINISTIC NO SQL function (without any input argument) with the same name as the constant I want and which returns the value of the constant variable I want to use.

Example: Following example shows how this alternative solution is applied for a constant named "OPERATION_TYPE_ABORT" with the value "ABORT". 
DROP FUNCTION IF EXISTS OPERATION_TYPE_ABORT;
DELIMITER //
CREATE FUNCTION OPERATION_TYPE_ABORT() RETURNS VARCHAR(6) 
DETERMINISTIC NO SQL
BEGIN
    RETURN ("ABORT");
END//
DELIMITER ;

Output:

mysql> select "test" FROM DUAL WHERE "ABORT" = OPERATION_TYPE_ABORT();
+------+
| test |
+------+
| test |
+------+
1 row in set (0.00 sec)

mysql> select "test" FROM DUAL WHERE "ABORT2" = OPERATION_TYPE_ABORT();
Empty set (0.00 sec)


~~~


No comments: