实验3：三轮乱弄版

Oracle数据库管理与开发I 实验报告

v_sal:=200; endif;

update employees set salary = v_emp.salary + v_sal where employee_id = v_emp.employee_id; endloop; end;

6. 编写一个PL/SQL块，修改员工号为201的员工工资为8000元，保证修改后的工资在

职位允许的工资范围之内，否则取消操作，并说明原因。

declare

v_salmin employees.salary%type; v_salmax employees.salary%type; e_lightlinit exception; begin

v_salmin:=&x; v_salmax:=&y;

update employees set salary = 8000where employee_id = 201;

if v_salmax<8000then raise e_lightlinit; elsif v_salmax>8000then raise e_lightlinit; endif; exception

when e_lightlinit then

dbms_output.put_line('工资超限制！'); rollback; whenothersthen

dbms_output.put_line('未知错误！'); end;

7. 创建一个存储过程，以员工号为参数，输出该员工的工资。 --不知道對不對

createorreplaceprocedure pro_sal(

v_employees_name in employees.employee_id%type) as

v_sal number; v_deptno number; begin

select department_id into v_deptno from employees where employee_id=p_empno;

select salary into v_sal from employees where departments_id=v_deptno;

Oracle数据库管理与开发I 实验报告

dbms_output.put_line(v_sal); exception

when no_data_found then

dbms.output.put_line('员工不存在'); end;

--自己寫的

8. 创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，

则工资增加140元；若属于20号部门，则工资增加200元；若属于30号部门，则工资增加250元；若属于其他部门，则工资增长300元。

createorreplaceprocedure pro_sal_change(

v_employees_name in employees.employee_id%type) as

v_sal number; v_deptno number; begin

FOR v_emp IN c_emp LOOP CASE v_emp.department_id WHEN10THEN v_increment:=140; WHEN20THEN v_increment:=200; WHEN30THEN v_increment:=250; ELSE v_increment:=300; ENDCASE;

UPDATE employees SET salary=salary+v_increment WHERECURRENTOF c_emp; ENDLOOP; exception

when no_data_found then

dbms.output.put_line('员工不存在'); end;

9. 创建一个函数，以员工号为参数，返回该员工的工资。

CREATEORREPLACEFUNCTION func_emp_salary( p_empno employees.employee_id%type) RETURN employees.salary%type AS

v_sal employees.salary%type; BEGIN

SELECT salary INTO v_sal FROM employees WHERE employee_id=p_empno;

Oracle数据库管理与开发I 实验报告

RETURN v_sal; EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20000,'There is not such an employee!'); END func_emp_salary;

10. 创建一个函数，以部门号为参数，返回该部门的平均工资；

createorreplacefunction dro_avg_salary( v_deptno emp.deptno%type) return emp.sal%type is

vr_sal emp.sal%type; begin

selectavg(sal) into vr_sal from emp where deptno = v_deptno; return vr_sal; end;

11. 创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

CREATEORREPLACEFUNCTION func_emp_dept_avgsal( p_empno employees.employee_id%type) RETURN employees.salary%type AS

v_deptno employees.department_id%type; v_avgsal employees.salary%type; BEGIN

SELECT department_id INTO v_deptno FROM employees WHERE employee_id=p_empno;

SELECTavg(salary) INTO v_avgsal FROM employees WHERE department_id=v_deptno; RETURN v_avgsal; EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20000,'There is not such an employee!'); END func_emp_dept_avgsal;

Oracle数据库管理与开发I 实验报告

12. 在employees表上创建一个触发器，保证每天8：00～17：00之外的时间禁止对该表进

行DML操作。 --自己做

createorreplacetrigger trg_emp beforeinsertorupdateordelete on employees begin

if to_char(sysdate,'HH24:MI')NOTBETWEEN'08:00'AND'17:00' THEN

RAISE_APPLICATION_ERROR (-20005,'只能在正常的工作时间内进行改变。'); ENDIF; END trg_emp;

--自己寫的

13. 在employees表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的

人数及平均工资，并输出。

createorreplacetrigger trg_emp_dept_stat afterinsertordeleteorupdateof salary on employees declare

v_count number; v_salary number(6,2); begin

if inserting or deleting or updating then

for v in(select department_id,count(*)num,avg(salary)avgsal groupby department_id)loop

dbms_output.put_line(v.department_id||''||v.num); endloop; end;