MySql 工作台 - 游标

Question

为什么我不能这样做？我想从 TABLEA 中搜索大于光标值的最接近的值，对两者执行平均函数并将结果放入 test3 中。我收到错误代码 1054 未知列“Xnearest in 'field list'”。以下代码来 self 的程序:

    BEGIN
     #THIS DOES NOT WORK Unknown column Xnearest in field list, error, 
      # declare local variables
     Declare done boolean default 0; 
     Declare xval double; 
     declare i integer default 1;
     Declare Xnearest double;
     Declare polypoint double;

     #declare the cursor
     Declare rows cursor for Select x from TABLE_A;
     #declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; #SQL state look through table until no more rows
     # create a table for results if non exists
     CREATE TABLE IF Not Exists test3  (Row_no integer, x_val double, X_Nearest double, x_greater double);
     #OPEN THE CURSOR
     OPEN rows;
     # tell it to loop through all rows
     repeat
     #get x value
     FETCH rows into xval;

    SET Xnearest = (select x from TABLEA where x>xval order by x asc limit 1);
    Set polypoint = xval+abs(xval-Xnearest)/2;

     Insert into test3 (Row_no, x_val,Xnearest,polypoint) Values (i,xval,X_Nearest,x_greater);
     set i=i+1; 


      UNTIL done END REPEAT;
     #close the cursor
     CLOSE rows;
     End

Answer 1

语法略有混淆，代码现在可以工作了!

    BEGIN
    #example cursor attempt to travel through each row at a time, but this time while performing a function and putting result in a new table
     # declare local variables
     Declare done boolean default 0; 
     Declare xval double; 
     declare i integer default 1;
     Declare Xnearest double;
     Declare polypoint double;

     #declare the cursor
     Declare rows cursor for Select x from TABLEA;
     #declare continue handler
     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; #SQL state is an error code, so look through table until no more rows, which applied for this error code
    # create a table for results if non exists
     CREATE TABLE IF Not Exists test3  (Row_no integer, x_val double, X_Nearest double, x_greater double);
    #OPEN THE CURSOR
     OPEN rows;
     # tell it to loop through all rows
     repeat
     #get x value
     FETCH rows into xval;

    SET Xnearest = (select x from TABLEA where x>xval order by x asc limit 1);
    Set polypoint = xval+abs(xval-Xnearest)/2;

     Insert into test3 (Row_no, x_val, X_nearest, x_greater) Values (i,xval,Xnearest,polypoint);
     set i=i+1; 


     UNTIL done END REPEAT;
     #close the cursor
     CLOSE rows;
    End