以下都是基于Oracle数据库下默认帐户Scott
根据scott用户下的公司部门信息表,进行如下操作:
(1)使用游标一次上涨所有员工工资,工资上涨规则如下:
10部门上涨10%;
20部门上涨20%;
30部门上涨30%
每个员工上限5100元,上涨到5100之后就不上涨。
DECLARE
CURSOR cur IS SELECT * FROM emp;
erow emp%rowtype;
BEGIN
-- OPEN cur;
FOR erow IN cur LOOP
if erow.deptno = 10 THEN
if erow.sal*1.1 > 5100 THEN
UPDATE emp SET sal = 5100 WHERE empno = erow.empno;
else
update emp SET sal = sal*1.1 WHERE empno = erow.empno;
END if;
ELSIF erow.deptno = 20 THEN
if erow.sal*1.2 > 5100 THEN
UPDATE emp SET sal = 5100 WHERE empno = erow.empno;
else
UPDATE emp SET sal = sal*1.2 WHERE empno = erow.empno;
END if;
ELSIF erow.deptno = 30 THEN
if erow.sal*1.3 > 5100 THEN
UPDATE emp SET sal = 5100 WHERE empno = erow.empno;
else
UPDATE emp SET sal = sal*1.3 WHERE empno = erow.empno;
END if;
end if;
end loop;
end;
(2)定义过程,根据员工编号找到雇员姓名及工资;
CREATE OR REPLACE PROCEDURE get_emp_info_proc(p_eno emp.empno%TYPE)
AS
v_ename emp.ename%TYPE ;
v_sal emp.sal%TYPE ;
v_count NUMBER ;
BEGIN
SELECT COUNT(empno) INTO v_count FROM emp WHERE empno=p_eno ;
IF v_count = 0 THEN -- 没有发现数据
RETURN ; -- 结束过程调用
END IF ;
SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=p_eno ;
DBMS_OUTPUT.put_line('编号为' || p_eno || '的雇员姓名:' || v_ename || ',工资:' || v_sal) ;
END;
(3)定义函数通过雇员编号查询雇员的月薪;
CREATE OR REPLACE FUNCTION get_salary_fun(p_eno emp.empno%TYPE)
RETURN NUMBER
AS
v_salary emp.sal%TYPE ;
BEGIN
SELECT sal + nvl(comm,0) INTO v_salary FROM emp WHERE empno=p_eno ;
RETURN v_salary ;
END;