MySQL create a function

Question

I am trying to create a function to insert a table information, The order id in auto-implemented from the database and in the end I wanted to return it.

CREATE FUNCTION `New_Order`(
P_Customers_customer_id int(11),
P_Employees_employee_id int(11),
P_Shops_shop_id int(11),
P_total double
) RETURNS int(11)
BEGIN
INSERT INTO `shopsorders`
            (`order_id`,
            `Customers_customer_id`,
            `Employees_employee_id`,
            `Shops_shop_id`,
            `total`,
            `date`)
        VALUES
            (P_Customers_customer_id,
             P_Employees_employee_id,
             P_Shops_shop_id,
             P_total,
             sysdate());
RETURN LAST_INSERT_ID();
END

The function doesn't work as expecting, it doesn't store the data in the database — , May 28 '17 at 08:57

Kristofer Soler · Answer 1 · 2017-05-28T09:24:13.520

Try using DELIMTER options & modifies sql data:

DELIMITER //
CREATE FUNCTION `New_Order`(
P_Customers_customer_id int(11),
P_Employees_employee_id int(11),
P_Shops_shop_id int(11),
P_total double
) RETURNS int(11)
modifies sql data
BEGIN
INSERT INTO `shopsorders`
            (`order_id`,
            `Customers_customer_id`,
            `Employees_employee_id`,
            `Shops_shop_id`,
            `total`,
            `date`)
        VALUES
            (P_Customers_customer_id,
             P_Employees_employee_id,
             P_Shops_shop_id,
             P_total,
             sysdate());
return LAST_INSERT_ID();
END // DELIMITER ;

score 0 · Accepted Answer · answered May 28 '17 at 10:09

You are missing the Delimiter option. You need to define a custom delimiter which wraps the start and end of the work you want to accomplish. After which you need to set the delimiter to its default value.

Please refer to this question for a complete example.

How to check if INSERT went well in stored function?

For more information on delimiter in mysql:

Delimiters in MySQL

MySQL create a function

2 Answers2