实验3：三轮乱弄版

Oracle数据库管理与开发I实验报告 系 所： 专 业： 学生姓名： 学生学号： 提交日期： 大连东软信息学院Dalian Neusoft University of InformationOracle数据库管理与开发I 实验报告实验名称： 利用PLSQL进行系统功能模块的开发实验日期：实验目的：掌握PL/SQL程序设计实验要求：按要求利用PL/SQL进行功能模块开发实验步骤描述：1. 编写一个PL/SQL块，输出所有员工的员工姓名、员工号、工资和部门号。declare cursor c_emp is select * from employees; begin for v_emp in c_emp loop dbms_output.put_line(v_emp.first_name| |v_emp.employee_id| |v_emp.department_id| |v_emp.salary); end loop; end;2. 编写一个PL/SQL块，输出所有比本部门平均工资高的员工信息。declare v_avg employees.salary%type;begin for v_emp in (select * from employees) loop select avg(salary) into v_avg from employees where department_id = v_emp.department_id; if v_emp.salary = v_avg then dbms_output.put_line(v_emp.employee_id| | v_emp.first_name| v_emp.salary | v_emp.department_id); end if; end loop;end;3. 写一个PL/SQL块，输出所有员工及其部门领导的姓名、员工号及部门号。declarecursor c_emp is select * from employees;v_emp1 c_emp%rowtype;v_emp2 c_emp%rowtype;begin open c_emp; loop fetch c_emp into v_emp1; exit when c_emp% notfound; if v_emp1.manager_id is not null then select * into v_emp2 from employees where employee_id = v_emp1.manager_id; dbms_output.put_line(|v_emp1.first_name| v_emp1.employee_id | v_emp1.department_id); dbms_output.put_line(|v_emp2.first_name|v_emp2.employee_id| v_emp2.department_id); else dbms_output.put_line(|v_emp1.first_name| v_emp1.employee_id | v_emp1.department_id); end if ; end loop; close c_emp;end; 4. 查询姓为“Smith”的员工信息，并输出其员工号、姓名、工资、部门号。如果该员工不存在，则插入一条新记录，员工号为2014，员工姓为“Smith”，工资为7500元，EMAIL为，入职日期为“2000年10月5日”，职位编号为AD_VP，部门号为50。如果存在多个名为“Smith”的员工，则输出所有名为“Smith”的员工号、姓名、工资、入职日期、部门号、EMAIL。declarev_emp employees%rowtype;beginselect * into v_emp from employees where last_name=Simth;dbms_output.put_line(v_emp.first_name| |v_emp.last_name| |v_emp.employee_id| |v_emp.salary| |v_emp.department_id);exceptionwhen no_data_found theninsert into employees(employee_id,last_name,salary,EMAIL,hire_date,job_id,department_id)values(2014,Simth,7500,,to_date(2000-10-05,yyyy-mm-dd),AD_VP,50);when too_many_rows thenfor v_emp in (select *from employees where last_name=Simth) loopdbms_output.put_line(v_emp.first_name| |v_emp.last_name| |v_emp.employee_id| |v_emp.salary| |v_emp.hire_date| |v_emp.email| |v_emp.department_id);end loop;end; 5. 编写一个PL/SQL块，根据员工职位不同更新员工的工资。职位为AD_PRES、AD_VP、AD_ASST的员工工资增加1000元，职位为FI_MGR、FI_ACCOUNT的员工工资增加800元，职位为AC_MGR 、AC_ACCOUNT的员工工资增加700元，职位为SA_MAN、SA_REP的员工工资增加600元，职位为PU_MAN、PU_CLERK的员工工资增加500元，职位为ST_MAN、ST_CLERK、SH_CLERK的员工工资增加400元，职位为IT_PROG、MK_MAN、MK_REP的员工工资增加300元，其它职位的员工工资增加200元。declarev_sal employees.salary%type;begin for v_emp in ( select * from employees) loop if v_emp.job_id= AD_PRESor v_emp.job_id=AD_VPor v_emp.job_id= AD_ASST then v_sal:=1000; else if v_emp.job_id= FI_MGR or v_emp.job_id= FI_ACCOUNT then v_sal:=800; else if v_emp.job_id= AC_MGR or v_emp.job_id=AC_ACCOUNT then v_sal:=700; else if v_emp.job_id= SA_MAN or v_emp.job_id= SA_REPthen v_sal:=600; else if v_emp.job_id= PU_MANor v_emp.job_id= PU_CLERKthen v_sal:=500; else if v_emp.job_id=ST_MANor v_emp.job_id= ST_CLERKor v_emp.job_id=SH_CLERKthen v_sal:=400; else if v_emp.job_id= IT_PROGor v_emp.job_id= MK_MANor v_emp.job_id=MK_REP then v_sal:=300; else v_sal:=200; end if; update employees set salary = v_emp.salary + v_sal where employee_id = v_emp.employee_id;end loop;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 = 8000 where employee_id = 201; if v_salmax8000 then raise e_lightlinit; end if; exception when e_lightlinit then dbms_output.put_line(工资超限制！); rollback; when others then dbms_output.put_line(未知错误！); end;7. 创建一个存储过程，以员工号为参数，输出该员工的工资。-不知道對不對create or replace procedure pro_sal(v_employees_name in employees.employee_id%type)asv_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; 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元。 create or replace procedure pro_sal_change(v_employees_name in employees.employee_id%type)asv_sal number;v_deptno number;begin FOR v_emp IN c_emp LOOP CASE v_emp.department_id WHEN 10 THEN v_increment:=140; WHEN 20 THEN v_increment:=200; WHEN 30 THEN v_increment:=250; ELSE v_increment:=300; END CASE; UPDATE employees SET salary=salary+v_increment WHERE CURRENT OF c_emp; END LOOP; exception when no_data_found then dbms.output.put_line(员工不存在); end;9. 创建一个函数，以员工号为参数，返回该员工的工资。 CREATE OR REPLACE FUNCTION func_emp_salary( p_empno employees.employee_id%type)RETURN employees.salary%typeAS v_sal employees.salary%type;BEGIN SELECT salary INTO v_sal FROM employees WHERE employee_id=p_empno; RETURN v_sal;EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000,There is not such an employee!);END func_emp_salary;10. 创建一个函数，以部门号为参数，返回该部门的平均工资； create or replace function dro_avg_salary(v_deptno emp.deptno%type) return emp.sal%type is vr_sal emp.sal%type;begin select avg(sal) into vr_sal from emp where deptno = v_deptno; return vr_sal;end;11. 创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。 CREATE OR REPLACE FUNCTION func_emp_dept_avgsal(p_empno employees.employee_id%type)RETURN employees.salary%typeAS 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; SELECT avg(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;12. 在employees表上创建一个触发器，保证每天8：