創建存盤程序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;
uj5u.com熱心網友回復：
我按自己的習慣，把存盤程序改寫成這樣。CREATE DEFINER=`root`@`localhost` PROCEDURE `pSalaryOrder`(IN id int,OUT RK int)
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不行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103666.html
標籤：MySQL
上一篇：求救，c++程式呼叫mysql函式插入資料后記憶體無法釋放
下一篇：sqoop的問題