创建存储过程pSalaryOrder,使用游标确定一个员工的实际收入按从大到小的排名。要求输入员工ID,输出该员工实际收入排名。命令行如下:
DELIMITER $$
CREATE PROCEDURE pSalaryOrder(IN id CHAR(6),OUT RK INTEGER)
BEGIN
DECLARE RK INTEGER DEFAULT 0;
DECLARE i_id char(6);
DECLARE rINCOME FLOAT;
DECLARE STA INTEGER DEFAULT 0;
DECLARE SalaryOrder cursor FOR
SELECT EMPLOYEEID,INCOME-OUTCOME FROM SALARY ORDER BY INCOME-OUTCOME DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
OPEN SalaryOrder;
FETCH SalaryOrder INTO i_id,rINCOME;
WHILE STA=0 DO
SET RK=RK+1;
IF id=i_id THEN
SET STA=1;
END IF;
FETCH SalaryOrder INTO i_id,rINCOME;
END WHILE;
CLOSE SalaryOrder;
END $$
DELIMITER ;思路是输入欲查询的员工id号,返回排名RK;
将游标的数据源按实际收入rIncome降序排列,然后利用游标逐个读取数据,并匹配员工id号。若匹配成功则更改状态STA,跳出循环,否则读到最后一行为止以下测试结果总是NULL或者CURSOR IS NOT OPEN,命令行存在什么问题呢??求助大神们!!
CALL pSalaryOrder(102201,@rk);
SELECT @rk;
DELIMITER $$
CREATE PROCEDURE pSalaryOrder(IN id CHAR(6),OUT RK INTEGER)
BEGIN
DECLARE RK INTEGER DEFAULT 0;
DECLARE i_id char(6);
DECLARE rINCOME FLOAT;
DECLARE STA INTEGER DEFAULT 0;
DECLARE SalaryOrder cursor FOR
SELECT EMPLOYEEID,INCOME-OUTCOME FROM SALARY ORDER BY INCOME-OUTCOME DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
OPEN SalaryOrder;
FETCH SalaryOrder INTO i_id,rINCOME;
WHILE STA=0 DO
SET RK=RK+1;
IF id=i_id THEN
SET STA=1;
END IF;
FETCH SalaryOrder INTO i_id,rINCOME;
END WHILE;
CLOSE SalaryOrder;
END $$
DELIMITER ;思路是输入欲查询的员工id号,返回排名RK;
将游标的数据源按实际收入rIncome降序排列,然后利用游标逐个读取数据,并匹配员工id号。若匹配成功则更改状态STA,跳出循环,否则读到最后一行为止以下测试结果总是NULL或者CURSOR IS NOT OPEN,命令行存在什么问题呢??求助大神们!!
CALL pSalaryOrder(102201,@rk);
SELECT @rk;
解决方案 »
- 求救优化SQL 设置索引方法
- 1449 - The user specified as a definer ('root'@'%') does not exist
- mysql 提示Incorrect information in file: 'tablename.frm'
- mysql乱码问题
- c++如何取出LAST_INSERT_ID的值?也用bind绑定参数的形式吗?
- 求一个mysql trigger, 急!
- mysql备份数据库问题
- mysql连接端口问题
- MySql主从同步不同表如何解决
- win10系统中安装mysql5.6时启动不了服务怎么解决
- 求救,c++程序调用mysql函数插入数据后内存无法释放
- mysql时区问题请教
BEGIN
#DECLARE RK INTEGER DEFAULT 0;
DECLARE i_id,done int;
DECLARE rINCOME decimal(8,2);
#DECLARE STA INTEGER DEFAULT 0;DECLARE SalaryOrder cursor FOR
SELECT EMPLOYEEID,INCOME-OUTCOME FROM SALARY ORDER BY INCOME-OUTCOME DESC;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;set RK=0;OPEN SalaryOrder;testLoop:LOOP
FETCH SalaryOrder INTO i_id,rINCOME;IF done = 1 THEN
LEAVE testLoop;
END IF;
SET RK=RK+1;
IF id=i_id THEN
LEAVE testLoop;
END IF;END LOOP testLoop;CLOSE SalaryOrder;END测试没问题。
注意:必须有set RK=0; 设置DEFAULT 0不行。