mysql - mysql存储过程中游标内的选择语句未考虑关键字

Question

此存储过程不会读取游标循环内 SELECT 语句中的关键字“into”。 “选择 empid 进入 vempid”任何帮助将不胜感激。

BEGIN
DECLARE processed,vAttendEmpid,vNoOfDays, vempid, vbasic_pay, vallowance, vda, vhra, vmeal_voucher, vcar_allowance, vchild_education ,vID INT DEFAULT 0;
DECLARE curAttendance CURSOR FOR Select empid, count(in_date_time) as d1 from       emp_attendance_processed where DATE_FORMAT( in_date_time, '%m' ) =  process_month and DATE_FORMAT(     in_date_time, '%Y' ) = process_year group by empid order by empid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET processed = 1 ;

OPEN curAttendance;
REPEAT
FETCH curAttendance INTO vAttendEmpid, vNoOfDays;
    IF NOT processed THEN
    Select vNoOfDays;

   SELECT empid into vempid,  basic_pay into vbasic_pay, allowance into vallowance, da into vda, hra into vhra,
   meal_voucher into vmeal_voucher, car_allowance into vcar_allowance, child_education into vchild_education** 
   from empsalary where empid= vAttendEmpid;

    SET vbasic_pay = vbasic_pay*(vNoOfDays/DATE_FORMAT(  LAST_DAY(CONCAT(process_year,'-',process_month,'-','01')),'%d'));
    SET vallowance = vallowance *(vNoOfDays/DATE_FORMAT(  LAST_DAY(CONCAT(process_year,'-',process_month,'-','01')),'%d'));
    SET vda = vda *(vNoOfDays/DATE_FORMAT(  LAST_DAY(CONCAT(process_year,'-',process_month,'-','01')),'%d'));
    SET vhra = vhra *(vNoOfDays/DATE_FORMAT(  LAST_DAY(CONCAT(process_year,'-',process_month,'-','01')),'%d'));
    SET vmeal_voucher = vmeal_voucher *(vNoOfDays/DATE_FORMAT(  LAST_DAY(CONCAT(process_year,'-',process_month,'-','01')),'%d'));
    SET vcar_allowance = vcar_allowance *(vNoOfDays/DATE_FORMAT(  LAST_DAY(CONCAT(process_year,'-',process_month,'-','01')),'%d'));
    SET vchild_education = vchild_education *(vNoOfDays/DATE_FORMAT(  LAST_DAY(CONCAT(process_year,'-',process_month,'-','01')),'%d'));
    insert into emp_salary_processed (empid, basic_pay, allowance, da, hra, meal_voucher, car_allowance, child_education)
    values(vempid, vbasic_pay, vallowance, vda, vhra, vmeal_voucher, vcar_allowance, vchild_education);

commit;
END IF;
  UNTIL processed END REPEAT;
  CLOSE curAttendance;

commit;
END

Answer 1

SELECT ... INTO ... 的正确语法是:

SELECT col1, ..., coln
  INTO var1, ..., varn
FROM ...

这与FETCH语句类似。

FETCH curAttendance INTO vAttendEmpid, vNoOfDays;

更改您的声明:

SELECT 
       empid           into vempid, 
       basic_pay       into vbasic_pay, 
       allowance       into vallowance, 
       da              into vda, 
       hra             into vhra,  
       meal_voucher    into vmeal_voucher, 
       car_allowance   into vcar_allowance,
       child_education into vchild_education 
from 
       empsalary 
where 
       empid= vAttendEmpid;

致:

SELECT 
       empid, basic_pay, allowance, da, hra, 
       meal_voucher, car_allowance, child_education
  INTO 
       vempid, vbasic_pay, vallowance, vda, vhra, 
       vmeal_voucher, vcar_allowance, vchild_education 
FROM 
       empsalary 
WHERE 
       empid = vAttendEmpid;