how to create a function in mySQL workbench

Question

trying to get the total number of boxes ordered from a given type("mytype"). something seems to be wrong with my syntax

create function NumOrdersForBoxType (mytype varchar(20))
returns int
begin
DECLARE @numorders int 
  select @numOrders = count(*)
    from BOXES as B
    where B.Type = mytype
    return @numOrders
end
;

In mysql you don't declare user defined variables (at variables) https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql AND every statement needs to be terminated and since you have more than 1 statement in the function you need to wrap in begin..end and possibly set delimiters. AND you need to SET variables. — P.Salmon, Jan 05 '21 at 15:00

score 1 · Answer 1 · answered Jan 05 '21 at 15:06

1

In mysql you don't declare user defined variables (at variables) stackoverflow.com/questions/11754781/… AND every statement needs to be terminated and since you have more than 1 statement in the function you need to wrap in begin..end and possibly set delimiters. AND you need to SET variables

delimiter $$

create function f(mytype varchar(20))
returns int
begin
DECLARE numorders int; 
    set numorders = (select count(*)
    from boxes as B
    where B.Type = mytype
    );
    return numOrders;
end $$
delimiter ;

answered Jan 05 '21 at 15:06

P.Salmon

17,104
2
12
19

thanks for the answer. that doesn't seem to work – Assaf lewin Jan 05 '21 at 15:12
Please provide sample data as text and explain why you think it doesn't work. – P.Salmon Jan 05 '21 at 15:15
Please see https://www.db-fiddle.com/f/FgiQTV271HxHWMyA5nTv7/0 – P.Salmon Jan 05 '21 at 15:18
am I not supposed to add the '@' before declaring a local variable? – Assaf lewin Jan 05 '21 at 17:24
Not in MySQL did you read the link in my comment – P.Salmon Jan 05 '21 at 17:27

how to create a function in mySQL workbench

1 Answers1