时间戳

首页 > 解决方案 > “字段列表”mysql存储过程中的未知列。如何解决这个问题？

mysql - “字段列表”mysql存储过程中的未知列。如何解决这个问题？

问题描述

        DELIMITER $$

        CREATE or replace PROCEDURE test()
            LANGUAGE SQL
            NOT DETERMINISTIC
            CONTAINS SQL
            SQL SECURITY DEFINER
            COMMENT ''
        BEGIN    
        DECLARE DNO varchar(4000);
        DECLARE ORG_MRK FLOAT;
        DECLARE RV1 FLOAT;
        DECLARE RV2 FLOAT;

        DECLARE D2 FLOAT;
        DECLARE D3 FLOAT;
        DECLARE D FLOAT;
        DECLARE D1 FLOAT;
        DECLARE lst_val FLOAT;
        DECLARE T FLOAT; 


        DECLARE CUR1 cursor for select col1,col2,col3,col4 from table1;


        OPEN CUR1;
        read_loop: LOOP
        FETCH CUR1 INTO DNO,ORG_MRK,RV1,RV2;
        WHILE CUR1%FOUND DO

        BEGIN
        set D1=85;
        set D=100;

        IF D=D1 THEN
            BEGIN
            UPDATE table2 SET FMARK_100=GREATEST(ORG_MRK,RV1) WHERE rtrim(ltrim(fmark_100)) =''
            and RTRIM(LTRIM(DUMYNUMB))=DNO;
            END;
        ELSE 
            UPDATE table2 SET FMARK_100=lEAST(ORG_MRK,RV1) WHERE rtrim(ltrim(fmark_100)) =''
            and RTRIM(LTRIM(DUMYNUMB))=DNO;

        END IF;


        END;
        END WHILE;

        END LOOP;
        CLOSE CUR1;
        END$$ 
        DELIMITER ;

它创建成功，没有错误返回。但是当调用这个存储过程时它给出了错误。产生这个错误

1054 - “字段列表”中的未知列“CUR1”。如何解决此问题。有没有版本兼容性问题。

标签： mysql

解决方案

DELIMITER $$

CREATE or replace PROCEDURE test()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN    
DECLARE DNO varchar(4000);
DECLARE ORG_MRK FLOAT;
DECLARE RV1 FLOAT;
DECLARE RV2 FLOAT;
DECLARE D2 FLOAT;
DECLARE D3 FLOAT;
DECLARE D FLOAT;
DECLARE D1 FLOAT;
DECLARE lst_val FLOAT;
DECLARE T FLOAT; 
-- add variable-flag 
DECLARE done INT DEFAULT 0;

DECLARE CUR1 cursor for select col1,col2,col3,col4 from table1;
-- add handler which sets flag when cursor is empty
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN CUR1;
read_loop: LOOP
    FETCH CUR1 INTO DNO,ORG_MRK,RV1,RV2;
-- check does the record was fetched
    IF done THEN
-- leave cycle if cursor is empty
        LEAVE read_loop;
    END IF;
    set D1=85;
    set D=100;
    IF D=D1 THEN
        UPDATE table2 SET FMARK_100=GREATEST(ORG_MRK,RV1) WHERE rtrim(ltrim(fmark_100)) =''
        and RTRIM(LTRIM(DUMYNUMB))=DNO;
    ELSE 
        UPDATE table2 SET FMARK_100=lEAST(ORG_MRK,RV1) WHERE rtrim(ltrim(fmark_100)) =''
        and RTRIM(LTRIM(DUMYNUMB))=DNO;
    END IF;
END LOOP;
CLOSE CUR1;
END$$ 
DELIMITER ;

推荐阅读