Custom Functions in MySQL

Question

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.

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
– Michael Berkowski, Commented Jan 9, 2014 at 16:40

Ike Walker · Accepted Answer · 2014-01-09 16:42:16Z

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 ;

answered Jan 9, 2014 at 16:42

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barranka · Accepted Answer · 2014-01-09 16:44:00Z

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

answered Jan 9, 2014 at 16:44

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

1 Comment

James Satori-Brunet Over a year ago

Thank you for your answer, the code works fine. I chose to select Ike's answer as correct because his came in first.

Collectives™ on Stack Overflow

Custom Functions in MySQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related