Defining a variable when I define a mysql function-CodePudding

I get an error when defining the following mysql function:

CREATE DEFINER=`root`@`localhost` FUNCTION PDATE(`gdate` datetime) RETURNS char(100) CHARSET utf8
BEGIN
    DECLARE 
        i,
        gy, gm, gd,
        g_day_no, j_day_no, j_np,
        jy, jm, jd INT DEFAULT 0;
END

The error is:

#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 '' at line 11

Even if I simply define just one variable, I get this error. What is the problem?

Thanks...

CodePudding user response：

You code is multi statement so you might need to set delimiters https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html and 'the function body must contain a RETURN value statement' - https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

DROP FUNCTION IF EXISTS F;

DELIMITER $$
CREATE FUNCTION F(`gdate` datetime) 
RETURNS char(100) CHARSET utf8
BEGIN
    DECLARE 
        i,
        gy, gm, gd,
        g_day_no, j_day_no, j_np,
        jy, jm, jd 
          INT DEFAULT 0;
     RETURN i;
END $$
DELIMITER ;

https://www.db-fiddle.com/f/8kWMQjBS57kNaAKWq2EZhM/1

CodePudding user response：

I think the issue is your function needs to do something in addition to DECLARE.

I would not recommend to use uppercase for object names

DROP FUNCTION IF EXISTS pdate ;
DELIMITER //
CREATE FUNCTION pdate(gdate DATETIME)
RETURNS VARCHAR(100) CHARSET UTF8
BEGIN
  DECLARE i, gy, gm, gd, g_day_no, j_np, jy, jm, jd INT DEFAULT 0 ;
  DECLARE r_value VARCHAR(100) DEFAULT NULL ;
  SET r_value:=DAYOFWEEK(gdate) ; -- for example
  RETURN r_value ;
END//
DELIMITER ;