What is the correct way to write stored procedures in MySQL with an IF/THEN/ELSE block inside? (Users of IntelliJ IDEA also here)?

Question

MySQL

The full question sounds like this: How to write a stored procedure in MySQL, inside which there is an IF THEN ELSE block, so that this script can then be executed from different environments without syntax errors?
I'll explain...
SQL script can be executed in different ways:

1. from the command line;
   
2. from special GUI tools like MySQL Administrator or MySQL Query Browser;
   
3. from IDEs like IntelliJ IDEA...
   
4. etc.
   

You write the usual procedure, and everything works fine and is performed. There are no special problems.
But as soon as the IF THEN ELSE block is declared inside the procedure, problems immediately begin ...
Somewhere you have to first set the delimiter (DELIMITER $$) and only in this way will this script be accepted and executed.
Someone like MySQL Administator from MySQL GUI Tools, on the contrary, does not accept it.
He only needs the procedure itself ...
And in the IntelliJ IDEA IDE, for example, when you try to run the stored inside which there is IF THEN ELSE, with DELIMITER $$, without it ... it doesn’t work at all!
DROP PROCEDURE IF EXISTS p;
DELIMITER $$
CREATE PROCEDURE p(IN Number INT)
BEGIN
IF NUMBER = 1 THEN
SELECT * FROM tblProduct WHERE ProductID = Number;
ELSE
SELECT * FROM tblProduct WHERE ProductId = 2;
END IF;
END $$
DELIMITER ;
[42000][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 'DELIMITER $$
<...>
and
CREATE PROCEDURE p(IN Number INT)
BEGIN
IF NUMBER = 1 THEN
SELECT * FROM tblProduct WHERE ProductID = Number;
ELSE
SELECT * FROM tblProduct WHERE ProductId = 2;
END IF;
END;
[42000][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 4
ELSE
<...>
How are procedures generally written in MySQL?
ZYZH Sorry, maybe he wrote chaotically, but something is not clear to me, how it can be that the syntax of the procedure could not be performed in any environment. Porridge in my head.
UPD: Looks like I figured out what's going on. JDBC does not support DELIMITER

Answer 1

I've never had a problem with this syntax:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_test`$$

CREATE DEFINER=`user`@`%` PROCEDURE `sp_test`(
  IN Number INT
  )
    READS SQL DATA
BEGIN
  -- 
  -- Код здесь
  -- 
END$$

DELIMITER ;

I'll ask: why do you need to run stored procedures from IDEA?

Answer 2

This suggests that the SQL syntax was done by idiots. What the fuck is DELIMITER? There is no such (keyword) in normal programming languages.