MySQL – Stored Functions Introduction

Stored functions allow you to manipulate data stored in tables. Unless specifically coded the data created is “impermanent” which means it only exist while using the function. If you need the results to be used later then you will have to create the process for the table to be updated.

Store Function Example:

delimiter $$

create function retailCalc(part_price int, markup float) returns float
begin
declare retailPrice float;
set retailPrice = part_price * markup;
return retailPrice;
end$$

delimiter ;

select *,retailCalc(part_price, 1.25) as Retail from parts;

Stored Procedure Example:

delimiter $$

create procedure priceList()
begin
select *,retailCalc(part_price, 1.25) as Retail from parts;
end$$

delimiter ;

call priceList;

• show function status where db=‘database’; – shows stored functions in database
• show create function functionName; – shows how stored function was created
• drop function functionName; – deletes stored function