PL/SQL 综合复习题之答案(2)

 PL/SQL 综合复习题之答案(2)

初始化数据：
--1
create 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;]
                             
 --3
create or replace function f_add_emp(f_emp_id number,f_add_sal number)
return number
is
v_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)
is
v_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;
begin
open 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';]
 
--4
create table emp_sal
(
   emp_id number,
   emp_old_sal varchar2(20),
   emp_new_sal varchar2(20)
);

create or replace trigger t_emp_sal
before update of emp_salary on a_emp for each row
begin
   insert into emp_sal values(:old.emp_id,:old.emp_salary,:new.emp_salary);
end;
  
--5
select 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;]