例如:一个公司,按照如下规则计算加薪金额:
1.公司中除了总裁(president)外,所有人都会至少增加p_min的薪水
2.任何奖金(bonus)高于$600的员工都会另增加4%
3.员工的佣金(commission)越高,增加越少。佣金(commission)少于$2000的另增加3%,佣金(commission)在$2000到$3000的增加另2%
4.佣金(commission)高于$3000的另增加1%
5.无论每个员工增加多少,增加比例不能高于p_max
CREATE PROCEDURE total_raise ( IN p_min DEC(4,2)
, IN p_max DEC(4,2)
, OUT p_total DEC(9,2) )
LANGUAGE SQL
SPECIFIC total_raise
tr: BEGIN
-- Declare variables
DECLARE v_salary DEC(9,2);
DECLARE v_bonus DEC(9,2);
DECLARE v_comm DEC(9,2);
DECLARE v_raise DEC(4,2);
DECLARE v_job VARCHAR(15) DEFAULT 'PRES';
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
1.公司中除了总裁(president)外,所有人都会至少增加p_min的薪水
2.任何奖金(bonus)高于$600的员工都会另增加4%
3.员工的佣金(commission)越高,增加越少。佣金(commission)少于$2000的另增加3%,佣金(commission)在$2000到$3000的增加另2%
4.佣金(commission)高于$3000的另增加1%
5.无论每个员工增加多少,增加比例不能高于p_max
CREATE PROCEDURE total_raise ( IN p_min DEC(4,2)
, IN p_max DEC(4,2)
, OUT p_total DEC(9,2) )
LANGUAGE SQL
SPECIFIC total_raise
tr: BEGIN
-- Declare variables
DECLARE v_salary DEC(9,2);
DECLARE v_bonus DEC(9,2);
DECLARE v_comm DEC(9,2);
DECLARE v_raise DEC(4,2);
DECLARE v_job VARCHAR(15) DEFAULT 'PRES';
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
-- Procedure logic
DECLARE c_emp CURSOR FOR
SELECT salary, bonus, comm
FROM employee
WHERE job != v_job; -- (1)这里的SELECT定义了结果集中的行和
列
OPEN c_emp; -- (2)
SET p_total = 0;
SET p_total = 0;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (3)得到一行数据,并将其
复制给本地变量
WHILE ( SQLSTATE = '00000' ) DO --SQLSTATE 00000: 操作执行成功,
并且未产生任何类型的警告或异常情
况。通过这个可以检查是否到达最后一行
SET v_raise = p_min;
SET v_raise = p_min;
IF ( v_bonus >= 600 ) THEN
SET v_raise = v_raise + 0.04;
END IF;
SET v_raise = v_raise + 0.04;
END IF;
IF ( v_comm < 2000 ) THEN
SET v_raise = v_raise + 0.03;
ELSEIF ( v_comm < 3000 ) THEN
SET v_raise = v_raise + 0.02;
ELSE
SET v_raise = v_raise + 0.01;
END IF;
IF ( v_raise > p_max ) THEN
SET v_raise = p_max;
END IF;
SET p_total = p_total + v_salary * v_raise;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (4)在WHILE逻辑中得到
更多的行数据
END WHILE;
END WHILE;
CLOSE c_emp; -- (5)
END tr
1、为什么要在存储过程中加入游标?
当我要在存储过程中选出表的多行记录时,并对这些记录进行操作时,就要使用游标。
游标本质上是存储过程中定义的一个变量。
2、以上的计算规则是怎样的?
以上的计算规则要确定 优先级。
第一优先级,公司中除了总裁(president)外,所有人都会至少增加p_min的薪水。
第二优先级, 2.任何奖金(bonus)高于$600的员工都会另增加4%。
看代码的计算顺序就可以知道。