Why in MySQL (MariaDB) does the loop not work even in the procedure?
2 UPDATE: Something to think about:
delimiter ;;
drop procedure if exists test2;;
create procedure test2()
begin
select ‘Hello World’;
end
;;
UPDATE: Code (from @Igor Dyshlenko):
DROP PROCEDURE IF EXISTS procedure1;
DELIMITER //
create procedure procedure1()
begin
set @p := 2;
while @p<20 do set @p:=@p+1; end while;
end;//
DELIMITER ;
It doesn't work either:
Error
The SQL query probably contains an error. If there is one, the MySQL server error will be displayed below, which makes it easier to diagnose the problem.
ERROR: Unknown punctuation @ 99 STR: / / SQL: DROP PROCEDURE IF EXISTS procedure1;# MySQL returned an empty result (i.e. zero rows). DELIMITER // create procedure procedure1() begin set @p := 2; while @p
MySQL returned an empty result (i.e. zero rows). DELIMITER ;
SQL query:
DELIMITER ;
MySQL Response:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
Near 'DELIMITER' at line 1
And so, according to MySQL: why does not work while or any other loop the loop in MySQL works, and it is enough to write such a query to check and everything will work:
create procedure procedure1()
begin
set @p := 2;
while @p<20 do set @p:=@p+1; end while;
end;
Code from @vkovalchuk88
BUT! I check this code in phpMyAdmin, with characteristics:
Сервер: MySQL (Localhost via UNIX socket)
Тип сервера: MariaDB
Версия сервера: 5.5.60-MariaDB-cll-lve - MariaDB Server
Версия протокола: 10
And I get:
Error
SQL query:
Create procedure procedure1() begin set @p := 2;
MySQL Response:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
Near '' at line 3
So still, how to use the loop?
Example from the documentation (https://www.techonthenet.com/mariadb/loops/while.php) doesn't work either...
P.S. I I tried to provide full information about my actions, and what I know (I'm just starting to understand the database queries). Search queries on this question do not give normal answers, I gave an example from the site ru.stackoverflow.com to show what and where I can't do it... I ask the moderators and users to help in this matter, and not to give the tag duplicate and set downvote. Please!
1 answers
The server responds to you absolutely correctly. Because in order to create a store procedure (function, trigger), you first need to change the separator for the SQL intertrepator, so that when it meets ;, it does not immediately rush to try to execute the expression. By the way, this is also in the example given by the link to the documentation. I.e., first we change the separator, and the intertrepator stops responding to ;:
DELIMITER //
Then create the procedure:
create procedure procedure1()
begin
set @p := 2;
while @p<20 do set @p:=@p+1; end while;
end;//
Please note that the operator ends with the characters // - our new separator. Only at this point does the mysql command-line intertrepator begin to process everything that was entered earlier as a single statement. Now you need to turn back the standard separator so that you can work comfortably, and the mysql intertrepator does not get lost when performing procedures:
DELIMITER ;
The article you refer to as a source is probably intended for a more prepared reader who is about the need to change the separator is not known.