mysql - 游标在存储过程中不起作用，它抛出 1064 错误

Question

我想在我的项目中使用光标，但它抛出错误1064。请帮我解决这个问题......

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE curs1 CURSOR FOR SELECT healthinsurancepremium.productid ,healthinsuran' at line 63

我的程序代码:

CR-EATE DEFINER=`root`@`localhost` PROCEDURE `spGettestOffline` (
   IN in_sumassured INT(10),
   IN in_age INT(3),
   IN in_adult INT(4),
   IN in_child INT(4),
   IN in_tenure INT(3),
   IN in_city VARCHAR(20)
)

BEGIN

    DECLARE bDone INT DEFAULT 0;
    DECLARE var1 INT ;  
    DECLARE Var2 INT;
    DECLARE Var3 INT;
    DECLARE var4 INT;

    /* this is the table declaration */  
    DROP TEMPORARY TABLE IF EXISTS tblResults;
        CREATE TEMPORARY TABLE IF NOT EXISTS tblResults (
        productid INT,
        suminsured INT,
        amount INT,
        tenent INT
        );

    /* this is the cursor declaration */  
    DECLARE curs1 CURSOR FOR SELECT healthinsurancepremium.productid ,healthinsurancepremium.suminsured ,healthinsurancepremium.amount ,healthinsurancepremium.tenure FROM healthinsurancepremium LEFT JOIN `cityspecifichealthpremium` ON `healthinsurancepremium`.`id` >= `cityspecifichealthpremium`.`healthpremiumidmin` AND `healthinsurancepremium`.`id` <= `cityspecifichealthpremium`.`healthpremiumidmax` WHERE (`cityspecifichealthpremium`.`healthpremiumidmax` IS NULL AND `cityspecifichealthpremium`.`healthpremiumidmin` IS NULL) AND healthinsurancepremium.suminsured = in_sumassured AND in_age >=healthinsurancepremium.minage AND in_age <=healthinsurancepremium.maxage AND healthinsurancepremium.adult=in_adult AND healthinsurancepremium.child=in_child ;

    /* this is the cursor looping */  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
    OPEN curs1;

    read_loop: LOOP
       FETCH curs1 INTO var1,var2,var3,var4;

       INSERT INTO tblResults VALUES (var1,var2, var3,var4);

       IF (bDone = 1) THEN 
           LEAVE read_loop;
       END IF;

    END LOOP;
    CLOSE curs1;

    SELECT * FROM tblResults;

END$$

我认为这个问题属于游标声明部分......但我没有找到任何内容

Answer 1

您将 DECLARE 语句放在了错误的位置。

DECLARE 语句的使用条件:

You must DECLARE them explicitly at the start of the BEGIN/END block, along with their data types.

将所有 DECLARE ... 语句移至 BEGIN block 的开头。它应该看起来像:

BEGIN
    DECLARE bDone INT DEFAULT 0;
    DECLARE var1 INT ;  
    DECLARE Var2 INT;
    DECLARE Var3 INT;
    DECLARE var4 INT;
    DECLARE curs1 CURSOR FOR
        SELECT
          healthinsurancepremium.productid, 
          healthinsurancepremium.suminsured,
          healthinsurancepremium.amount,
          healthinsurancepremium.tenure
        FROM healthinsurancepremium
          LEFT JOIN `cityspecifichealthpremium` ON 
            `healthinsurancepremium`.`id` >= `cityspecifichealthpremium`.`healthpremiumidmin` AND
            `healthinsurancepremium`.`id` <= `cityspecifichealthpremium`.`healthpremiumidmax`
          WHERE 
           ( `cityspecifichealthpremium`.`healthpremiumidmax` IS NULL AND
             `cityspecifichealthpremium`.`healthpremiumidmin` IS NULL ) AND
           healthinsurancepremium.suminsured = in_sumassured AND 
           in_age >= healthinsurancepremium.minage AND 
           in_age <= healthinsurancepremium.maxage AND 
           healthinsurancepremium.adult = in_adult AND 
           healthinsurancepremium.child = in_child ;

引用:Stored Procedures - MySQL