PL/SQL 综合复习题之答案(2)
初始化数据：--1create table a_location ( loc_id number(5) primary key, loc_name varchar(20));
insert into a_location values(01001,'buildinga');insert into a_location values(01005,'buildingb');insert into a_location values(01006,'buildingc');
create table a_dept( dept_id number(5) primary key, dept_name varchar2(20), loc_id number(5) );alter table a_dept add constraint fk1 foreign key (loc_id) references a_location(loc_id);
insert into a_dept values(001,'HR',01005);insert into a_dept values(002,'ADMIN',01001);insert into a_dept values(003,'TR',01005);insert into a_dept values(004,'MARKETING',01005);insert into a_dept values(005,'IT',01001); create table a_emp( emp_id number(5) primary key, emp_name varchar2(20), emp_salary number(6), dept_id number(5)); alter table a_emp add constraint a foreign key(dept_id) references a_dept(dept_id);
insert into a_emp values(00101,'aaron',4200,'005');insert into a_emp values(00203,'clara',3600,'002');insert into a_emp values(00507,'chris',2500,'005');insert into a_emp values(00045,'sam',1500,'005');insert into a_emp values(00406,'jack',2200,'004'); --2 create or replace procedure p_emp is --my_exception exception; cursor c_emp is select d.loc_id,count(e.emp_id) from a_emp e,a_dept d where e.dept_id=d.dept_id and loc_id in (select loc_id from a_location) group by loc_id; v_loc a_location.loc_id%type; v_num number; begin open c_emp; dbms_output.put_line('员工所在地'||'||'||'员工总人数'); loop fetch c_emp into v_loc,v_num; exit when c_emp%notfound; dbms_output.put_line(v_loc||' '||v_num); end loop; --if(v_num:=0)then --raise my_exception; --exception --when my_exception then --dbms_output.put_line('无员工'); end; [select d.loc_id,count(e.emp_id) from a_emp e,a_dept d where e.dept_id=d.dept_id and loc_id in (select loc_id from a_location) group by loc_id;] --3create or replace function f_add_emp(f_emp_id number,f_add_sal number)return numberisv_sum_sal number;begin update a_emp set emp_salary=emp_salary*f_add_sal+emp_salary where emp_id=f_emp_id; select emp_salary into v_sum_sal from a_emp where emp_id=f_emp_id ; return v_sum_sal;end; create or replace procedure p_emp(p_dname varchar2)isv_empid a_emp.emp_id%type;v_sal a_emp.emp_salary%type;v_emp_sal a_emp.emp_salary%type;v_n number;cursor c_emp is select e.emp_id,e.emp_salary from a_emp e,a_dept d where e.dept_id=d.dept_id and d.dept_name=p_dname;v_dept_sum_sal number;beginopen c_emp; loop fetch c_emp into v_empid,v_sal; exit when c_emp%notfound; if(v_sal<2000)then v_n:=0.25; end if; if (v_sal>2000 or v_sal<=3000)then v_n:=0.15; end if; if (v_sal >3000 or v_sal<=5000)then v_n:=0.8; end if; if (v_sal>5000)then v_n:=0.4; end if; v_emp_sal:=f_add_emp(v_empid,v_n); end loop; select sum(emp_salary) into v_dept_sum_sal from a_emp e,a_dept d where e.dept_id=d.dept_id and d.dept_name=p_dname; dbms_output.put_line('部门总工资为：'||v_dept_sum_sal);end;
[select e.emp_id,e.emp_salary from a_emp e,a_dept d where e.dept_id=d.dept_id and d.dept_name='IT';] --4create table emp_sal( emp_id number, emp_old_sal varchar2(20), emp_new_sal varchar2(20));
create or replace trigger t_emp_salbefore update of emp_salary on a_emp for each rowbegin insert into emp_sal values(:old.emp_id,:old.emp_salary,:new.emp_salary);end; --5select b.dept_id,count(a.emp_id)*500 from a_emp a,a_dept b where a.dept_id=b.dept_id and b.loc_id=(select loc_id from a_location where loc_name='buildinga')group by b.dept_id; [select count(a.emp_id),b.dept_id from a_emp a,a_dept b where a.dept_id=b.dept_id group by b.dept_id;]
