DELIMITER In sql

In MySQL, the DELIMITER command is used to change the default statement delimiter (;). When defining stored procedures, functions, triggers, or other compound statements, you might need to change the delimiter to allow for the use of semicolons within the body of the statement without prematurely ending it.

The common practice is to change the delimiter to something other than a semicolon (e.g., $$), define your stored procedure, and then change the delimiter back to a semicolon. The provided code below uses DELIMITER $$ at the beginning and DELIMITER ; at the end. This is the correct way to change and reset the delimiter.

Here's the breakdown:

DELIMITER $$ -- Change the delimiter to $$

CREATE PROCEDURE AddBonus(IN user_id INT, IN project_name VARCHAR(255), IN score INT )
BEGIN
    IF NOT EXISTS(SELECT name FROM projects WHERE name=project_name) THEN
        INSERT INTO projects (name) VALUES (project_name);
    END IF;
    INSERT INTO corrections (user_id, project_id, score)
    VALUES (user_id, (SELECT id from projects WHERE name=project_name), score);
END $$ --$$ here means the statement of program has finally ended

DELIMITER ; -- Change the delimiter($$) back to ;

By doing this, you can include semicolons within the body of the stored procedure without causing syntax errors. The semicolons within the stored procedure are interpreted as part of the statement, not as the end of the statement.

When you use DELIMITER ;, it automatically resets the delimiter to a semicolon (;). The statement DELIMITER ; alone is sufficient to set the delimiter back to semicolon. The DELIMITER $$ statement itself is sufficient to change the delimiter to $$.
The semicolon is used as a statement terminator in regular SQL statements, but in this specific context of changing the delimiter, it is not necessarily required to end it with a semi-colon like this DELIMITER $$;