4

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.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
James Satori-Brunet
  • 901
  • 2
  • 13
  • 28
  • What MySQL client are you using to create this? The `DELIMITER` keyword is specific to the command-line MySQL client. You should have a `//` delimiter after your first `DROP FUNCTION`, and it looks like you should remove the `;` after the final `END`. That should be the delimiter `//` – Michael Berkowski Jan 09 '14 at 16:40
  • See also [Delimiters in MySQL](http://stackoverflow.com/questions/10259504/delimiters-in-mysql/10259528#10259528) – Michael Berkowski Jan 09 '14 at 16:54

2 Answers2

3

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 ;
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
0

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

Barranka
  • 20,547
  • 13
  • 65
  • 83