Create stored procedures pSalaryOrder, use the cursor to determine according to the actual income of an employee from big to small, required to enter your employee ID, output the employee's actual income ranking, the command line is as follows:
DELIMITER $$
CREATE PROCEDURE pSalaryOrder (id IN CHAR (6), the OUT fairly RK INTEGER)
The BEGIN
DECLARE fairly RK INTEGER DEFAULT 0;
DECLARE i_id char (6);
DECLARE rINCOME FLOAT;
DECLARE the STA INTEGER DEFAULT 0;
DECLARE SalaryOrder cursor FOR
The SELECT EMPLOYEEID, INCOME - OUTCOME FROM SALARY ORDER BY INCOME - OUTCOME DESC;
DECLARE the CONTINUE HANDLER FOR the NOT FOUND
The OPEN SalaryOrder;
The FETCH SalaryOrder INTO i_id, rINCOME;
WHILE the STA=0 DO
The SET fairly RK=fairly RK + 1;
IF id=i_id THEN
SET the STA=1;
END the IF;
The FETCH SalaryOrder INTO i_id, rINCOME;
END the WHILE;
The CLOSE SalaryOrder;
END $$
DELIMITER.

Idea is to input to query staff id number, rank fairly RK the return;
The cursor data sources according to the actual income rIncome descending order, and then use vernier reading data individually, and match the employee id number, if match the change state of STA, jump out of the loop, or read till the last line

The following test results always NULL or CURSOR IS NOT OPEN, the command line IS what problem?? Turn to the great god!!!!!
CALL pSalaryOrder (102201, @ fairly rk);
SELECT @ fairly rk;

CodePudding user response:

I according to their own habits, the stored procedure rewrite like this,

The CREATE DEFINER=` root ` @ ` localhost ` PROCEDURE ` pSalaryOrder ` (IN id int, OUT fairly RK int)
The BEGIN
# DECLARE fairly RK INTEGER DEFAULT 0;
DECLARE i_id, done int.
DECLARE rINCOME decimal (8, 2);
# DECLARE the STA INTEGER DEFAULT 0;

DECLARE SalaryOrder cursor FOR
The SELECT EMPLOYEEID, INCOME - OUTCOME FROM SALARY ORDER BY INCOME - OUTCOME DESC;

DECLARE the CONTINUE HANDLER FOR the NOT FOUND SET done=1;

The set fairly RK=0;

The OPEN SalaryOrder;

TestLoop: LOOP
The FETCH SalaryOrder INTO i_id, rINCOME;

IF done=1 THEN
LEAVE testLoop;
END the IF;


The SET fairly RK=fairly RK + 1;
IF id=i_id THEN
LEAVE testLoop;
END the IF;

END LOOP testLoop;

The CLOSE SalaryOrder;

END

Test no problem,
Note: must have set fairly RK=0; Set the DEFAULT 0 no.