Create a function with phpMyAdmin

Question

I have trouble creating a function using phpMyAdmin like an interface for MySQL. So I write:

DELIMITER //
Create or Replace Function  affecter (id_patient IN integer , id_maladie IN VARCHAR2 ) 
Return VARCHAR2(30) IS  msg Varchar2(30);
Begin
    Insert into souffrir values (id_patient,id_maladie);
    msg:= 'Insertion effectuée';
    Return msg;
END//

I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Function affecter (id_patient IN integer , id_maladie IN VARCHAR2 ) Returns V' at line 1

How do I fix this error?

Answer 1

Try this:

DELIMITER $$
CREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)
BEGIN
  DECLARE profit DECIMAL(9,2);
  SET profit = price-cost;
  RETURN profit;
END$$
DELIMITER ;

Got it from https://www.a2hosting.com/kb/developer-corner/mysql/mysql-stored-functions-and-procedures

Answer 2

Currently, MySQL does not have an 'or replace' part of the create function. You should instead, after defining the delimiter, add a DROP FUNCTION IF EXISTS with the function name.

See 13.1.12. CREATE PROCEDURE and CREATE FUNCTION Syntax.

Answer 3

If you go to the SQL tab in PMA, just below the big box for entering your SQL, there's a small input field for the delimiter. Remove DELIMITER // from your SQL, change the delimiter input field from ';' to '//' paste your code and submit it.