Why does this function have a syntax error near DECLARE?

The code is:

DELIMITER $$
CREATE FUNCTION CHECK_AVABILITY(nama CHAR(30))
RETURNS INT(4)
DECLARE vreturn INT(4);
BEGIN
IF nama = 'ika' THEN
SET vreturn = 0;
ELSE
SET vreturn = 1;
END IF
RETURN vreturn;
END $$

The error message is:

ERROR 1064 (42000): You Have an error inyour sql syntax; check the manual that corresponds to your MySQL server version for the right synta开发者_JAVA百科x to use near 'DECLARE vreturn INT4); BEGIN'

Help is appreciated.

---

Move DECLARE vreturn INT(4) inside the BEGIN / END block. You probably also need a ; after the END IF. Additionally, this looks like it is to be a DETERMINISTIC function. Add the DETERMINISTIC keyword before the BEGIN.

DELIMITER $$
CREATE FUNCTION CHECK_AVABILITY(nama CHAR(30))
RETURNS INT(4)
DETERMINISTIC
BEGIN
  DECLARE vreturn INT(4);
  IF nama = 'ika' THEN
    SET vreturn = 0;
  ELSE
    SET vreturn = 1;
  END IF;
  RETURN vreturn;
END $$

---

Here's my findings on the subject:

This is a quote from a manual:

"You need a BEGIN/END block when you have more than one statement in the procedure. You use the block to enclose multiple statements.

But that's not all. The BEGIN/END block, also called a compound statement, is the place where you can define variables and flow of control."

In other words:

(These rules appear to apply to triggers and stored procedures in the same way, as it seems the same syntax is used in both.)

First, notice that a flow control group of keywords such as IF ... END IF or WHILE ... END WHILE is seen as a single statement as far as its termination with a semicolon is concerned, that is, it is terminated as a whole by a single semicolon at the end of it: IF ... END IF; WHILE ... END WHILE;.

Then, if the body of a trigger or stored procedure contains just one stament, and that statement is not a variable declaration nor a flow control group of keywords as above, that statement may not be terminated by a semicolon (;) and not enclosed by a BEGIN ... END block.

On the contrary, if the body of a trigger or stored procedure contains more than one stament, and particularly if it contains variable declarations and/or flow control groups of keywords, then it must be enclosed in a BEGIN ... END block.

Finally, the BEGIN ... END block itself must not be terminated by a semicolon.

Hope this helps.