CODEWITHSUNDEEP
mysql
James Satori-Brunet
James Satori-Brunet

Reputation: 901

Custom Functions in MySQL

I am trying to create a simple custom function in MySQL that takes 2 values (id int, currencyValue double), looks up a value in another table based on the id and returns the currencyValue*rate.

Here is my psuedo-code that is not getting me anywhere near this. There is also a surprising lack of examples of this on Google.

DROP FUNCTION IF EXISTS ConvertCurrency
DROP FUNCTION IF EXISTS F_ConvertCurrency //
CREATE FUNCTION F_ConvertCurrency(PID INT, C_VALUE DOUBLE) 
RETURNS DOUBLE
BEGIN

  DECLARE Currency_Rate DOUBLE;

  SET Currency_Rate = SELECT `Rate` FROM `Currencies` WHERE `ID` = PID;
  RETURN Currency_Rate*C_VALUE;
END;//

I am getting the 'there is an error in your code near...' which helps me none. I have seen other examples of functions with the DELMITER keyword but have no idea what this means.

Upvotes: 4

Views: 1835

Answers (2)

Barranka
Barranka

Reputation: 21047

First of all, when you are creating a function, you are using many times the standard instruction terminator ;, so you must tell MySQL to ignore it until you are done.

So, you need to write your procedure like this:

delimiter $$
drop function if exists F_ConvertCurrency $$
create function F_ConvertCurrency(PID INT, C_VALUE DOUBLE) returns DOUBLE
BEGIN
    declare Currency_Rate DOUBLE;
    set Currency_Rate = (SELECT `Rate` from `Currencies` WHERE `ID`=PID);
    return Currency_Rate * C_VALUE;
END $$
delimiter ;

Things to remember:

  • When you want to assign a variable with a select query, enclose the query in parenthesis. One alternative is to use select column into variable from yourTable where....
  • At the end of the procedure (or function) declaration, don't use ; but use the custom delimiter you defined earlier.
  • Always remember to restore the default delimiter (by using: delimiter ;)

Hope this helps

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65527

You should set DELIMITER to something other than a semi-colon (I use $$) temporarily in order to use semi-colons in the body of your stored function definition.

You can use SELECT ... INTO ... in order to set the local Currency_Rate variable. I recommend prefixing the variable name with v_ as well to represent the fact that it is a local variable as opposed to a table column.

Here's an example using your code:

DELIMITER $$

DROP FUNCTION IF EXISTS ConvertCurrency $$
DROP FUNCTION IF EXISTS F_ConvertCurrency $$
CREATE FUNCTION F_ConvertCurrency(PID INT, C_VALUE DOUBLE) 
RETURNS DOUBLE
BEGIN

  DECLARE v_Currency_Rate DOUBLE;

  SELECT `Rate` 
  INTO v_Currency_Rate
  FROM `Currencies` 
  WHERE `ID` = PID;

  RETURN v_Currency_Rate*C_VALUE;
END $$

DELIMITER ;

Upvotes: 3

Related Questions