我的代码是
DECLARE
v_salary emp.sal%TYPE;
v_empno emp.empno%TYPE;
NUM NUMBER(9):=0;
BEGIN
LOOP
NUM :=NUM+1;
SELECT e.sal INTO v_salary FROM emp e
WHERE e.empno=NUM; --每次只取一个员工的薪水
IF v_salary<1000 THEN
UPDATE emp SET emp.comm='100' WHERE emp.empno=NUM;--根据员工编号修改福利
END IF;
IF v_salary<3000 AND v_salary>=1000 THEN
UPDATE emp SET emp.comm='50' WHERE emp.empno=NUM;
END IF;
IF v_salary<7000 AND v_salary>=3000 THEN
UPDATE emp SET emp.comm='25' WHERE emp.empno=NUM;
END IF;
IF v_salary>=7000 THEN
UPDATE emp SET emp.comm='10' WHERE emp.empno=NUM;
END IF;
EXIT WHEN NUM=10;--循环次数
END LOOP;
END;
DECLARE
v_salary emp.sal%TYPE;
v_empno emp.empno%TYPE;
NUM NUMBER(9):=0;
BEGIN
LOOP
NUM :=NUM+1;
SELECT e.sal INTO v_salary FROM emp e
WHERE e.empno=NUM; --每次只取一个员工的薪水
IF v_salary<1000 THEN
UPDATE emp SET emp.comm='100' WHERE emp.empno=NUM;--根据员工编号修改福利
END IF;
IF v_salary<3000 AND v_salary>=1000 THEN
UPDATE emp SET emp.comm='50' WHERE emp.empno=NUM;
END IF;
IF v_salary<7000 AND v_salary>=3000 THEN
UPDATE emp SET emp.comm='25' WHERE emp.empno=NUM;
END IF;
IF v_salary>=7000 THEN
UPDATE emp SET emp.comm='10' WHERE emp.empno=NUM;
END IF;
EXIT WHEN NUM=10;--循环次数
END LOOP;
END;
update emp set comm=(case
when sal<1000 then 100
when sal>=1000 and sal<2000 then 80
when sal>=2000 and sal<6000 then 50
end)
where ……
create table emp(
id number,
name varchar2(20),
sal number,
fun number
)update emp t set t.fun=(case when sal<1000 then 100 when sal<2000 and sal>=1000 then 80
when sal>=2000 and sal<6000 then 50 end
)
能用一条语句解决问题就用一条语句解决,即简洁又高效
update emp set comm=(case
when sal<1000 then 100
when sal>=1000 and sal<2000 then 80
when sal>=2000 and sal<6000 then 50
end)
where …… 正解
when sal<1000 then 100
when sal>=1000 and sal<2000 then 80
when sal>=2000 and sal<6000 then 50
end)
where 条件表达式;
使用case when 比较简洁。