我需要一些帮助来制作此MySQL游标，我一直在遵循此指南：http : //www.mysqltutorial.org/mysql-cursor/。我正在尝试使一个游标遍历一个表并一次创建一个名称列表。
CREATE PROCEDURE build_people_table ( @people varchar(4000))
AS
BEGIN
DECLARE @v_finished INT;
DECLARE @v_name nvarchar(255);
--Main Cursor
DECLARE people_cursor CURSOR FOR
SELECT [Person Name]
FROM [dbo].[People];
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET @v_finished = 1;
OPEN people_cursor;
get_name: LOOP
FETCH people_cursor INTO @v_name ;
IF @v_finished = 1 THEN
LEAVE get_name;
END IF;
SET @people = CONCAT(@v_name,';',@people);
END LOOP get_name;
CLOSE people_cursor;
END
mySQL错误消息
Msg 156, Level 15, State 1, Procedure build_people_table, Line 41
Incorrect syntax near the keyword 'CONTINUE'.
Msg 136, Level 15, State 1, Procedure build_people_table, Line 41
Cannot use a CONTINUE statement outside the scope of a WHILE statement.
Msg 102, Level 15, State 1, Procedure build_people_table, Line 47
Incorrect syntax near 'LOOP'.
Msg 156, Level 15, State 1, Procedure build_people_table, Line 56
Incorrect syntax near the keyword 'THEN'.
Msg 102, Level 15, State 1, Procedure build_people_table, Line 61
Incorrect syntax near 'LOOP'.
Msg 156, Level 15, State 1, Procedure build_people_table, Line 74
Incorrect syntax near the keyword 'END'.
这表明它在Mysql（您所说的使用的技术）中是端到端的。
注意，最好用
select group_concat(`Person Name`) from People;
为了这。但是无论出于何种原因，您似乎都想为此编写存储的proc，也许出于学习目的。重要的是要注意，游标非常慢。
另请注意，您的输出缓冲区（out参数）很有可能溢出。有很多数据可以进入那里。所以我将其从varchar（4000）更改为TEXT。
create table People
( id int auto_increment primary key,
`Person Name` varchar(200)
);
insert People(`Person Name`) values ('John Henry'),('Kim Billings'),('Gertrude Smith');
drop procedure if exists build_people_table;
DELIMITER $$
CREATE PROCEDURE build_people_table ( OUT people_out TEXT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_name nvarchar(255);
-- DECLARE bFirst boolean DEFAULT TRUE;
-- Main Cursor
DECLARE people_cursor CURSOR FOR
SELECT `Person Name`
FROM `People`;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
OPEN people_cursor;
set @people='';
get_name: LOOP
FETCH people_cursor INTO v_name;
IF done THEN
LEAVE get_name;
END IF;
SET @people:= CONCAT(v_name,";",@people);
-- SET @people:= CONCAT(@people,";",v_name);
END LOOP get_name;
CLOSE people_cursor;
set people_out:=@people;
-- select @people; -- used for debugging
END
$$
DELIMITER ;
set @saveHere:='';
call build_people_table(@saveHere);
select @saveHere; -- show results
'Gertrude Smith;Kim Billings;John Henry;'
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句