MySQL 说 : #1338 - Cursor declaration after handler declaration

Question

我正在开发 Web 项目，并且在从 Oracle 数据库迁移到 mysql 数据库时遇到一些问题。我想用这段代码创建函数:

DROP FUNCTION IF EXISTS F_MANIFEST_GABUNG_SMR;

DELIMITER //

CREATE FUNCTION F_MANIFEST_GABUNG_SMR (input_val   varchar(4000))
  RETURNS VARCHAR(4000)
BEGIN
  DECLARE return_text  VARCHAR(10000)  DEFAULT  NULL;
DECLARE not_found INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; 
  DECLARE x CURSOR FOR SELECT DISTINCT IFNULL(SMR,'-') SMR FROM MANIFEST_EDI_SMR WHERE BL_NBR = input_val; OPEN x;
 FETCH x INTO;
 WHILE NOT_FOUND=0
 DO
    SET return_text = concat(ifnull(return_text, '') , ' ' , IFNULL(x.SMR, '')) ;
  FETCH  INTO;
  END WHILE;
  CLOSE ;
  IF char_length(return_text) > 85 THEN
       SET return_text = concat(ifnull(substr(return_text,1,85), '') , ' detail asp BL'); 
  END IF;
  RETURN return_text;
END;
//

DELIMITER ;

我正在使用 phpmyadmin 来存储带有例程的函数。感谢您的帮助:)

Answer 1

错误消息是不言自明的:您在处理程序之后声明了光标，需要更改顺序:

DROP FUNCTION IF EXISTS F_MANIFEST_GABUNG_SMR;

DELIMITER //

CREATE FUNCTION F_MANIFEST_GABUNG_SMR (input_val   varchar(4000))
  RETURNS VARCHAR(4000)
BEGIN
  DECLARE return_text  VARCHAR(10000)  DEFAULT  NULL;
DECLARE not_found INT DEFAULT 0;

-- Declare cursor before handler
DECLARE x CURSOR FOR SELECT DISTINCT IFNULL(SMR,'-') SMR 
 FROM MANIFEST_EDI_SMR WHERE BL_NBR = input_val; OPEN x;

-- handler need to be after cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; 

 FETCH x INTO;
 WHILE NOT_FOUND=0
 DO
    SET return_text = concat(ifnull(return_text, '') , ' ' , IFNULL(x.SMR, '')) ;
  FETCH  INTO;
  END WHILE;
  CLOSE ;
  IF char_length(return_text) > 85 THEN
       SET return_text = concat(ifnull(substr(return_text,1,85), '') , ' detail asp BL'); 
  END IF;
  RETURN return_text;
END;
//

DELIMITER ;

更多详情请访问:https://dev.mysql.com/doc/refman/8.0/en/cursors.html