存储过程

1）写出建表以及嵌入记录语句 

create table Department(depid varchar2(20) primary key, depname varchar2(20), depcj number(10,2));

create table Employee(empid varchar2(20) primary key, name varchar2(20),depid varchar2(20), cj number(10,2), xorder number(10));

insert into Department(depid,depname) values('A001','人力资源部');

 insert into Department(depid,depname) values('A002','财务部'); 

insert into Employee(empid, name, depid, cj) values('001','张三','A001',90);

 insert into Employee(empid, name, depid, cj) values('002','李四','A001',90); 

insert into Employee(empid, name, depid, cj) values('003','王五','A001',80);

 insert into Employee(empid, name, depid, cj) values('004','张飞','A002',70);

 insert into Employee(empid, name, depid, cj) values('005','刘备','A002',60); 

insert into Employee(empid, name, depid, cj) values('006','关羽','A002',50);

2）显示A001部门员工的姓名、成绩 

select name,cj from employee where depid='A001';

3）显示所有员工的员工号、姓名、部门名称、成绩 

select e.empid,e.name,d.depname,e.cj from employee e, department d where e.depid=d.depid;

4）将关羽的成绩修改成52分 

update employee set cj=52 where name=' 关羽';

5）按要求写视图VdepEmpMax求各部门的最高分，显示部门号、最高分成绩

create view VdepEmpMax as (select deptid,max(cj)  as max from employee e group by deptid);

6）按要求写存储过程SP_Calc求各部门的平均成绩，并更新到Department表depcj字段中 

create or replace procedure SP_Calc as begin

 update department d set depcj=( select nvl(avg(cj),0) from employee e where e.depid(+)=d.depid);

 end;

 /

DEPID                DEPNAME                     DEPCJ
-------------------- -------------------- ------------
A001                 人力资源部                  86.67
A002                 财务部                         61.00

7）按要求写存储过程SP_Order求员工的名次，并更新到Employee表xorder字段中 

create or replace procedure SP_Order as 

begin

 update employee w set xorder =(select b.rn from (select empid,rank() over (partition by depid order by cj desc ) rn from employee) b where w.empid=b.empid); 

end; 

/

EMPID      NAME       DEPID                CJ      XORDER
---------- ---------- ---------- ------------ -----------
001        张三       A001              90.00           1
002        李四       A001              90.00           1
003        王五       A001              80.00           3
004        张飞       A002              70.00           1
006        关羽       A002              52.00           3
005        刘备       A002              60.00           2

8）按要求写视图VdepEmp2，求各部门的前2名，显示部门号、员工号、成绩

create or replace view VdepEmp2 as 

select depid,name,cj,rn from (select e.*,rank() over (partition by depid order by cj desc) rn from employee e) 

where rn<3;

DEPID      NAME                 CJ         RN
---------- ---------- ------------ ----------
A001       张三              90.00          1
A001       李四              90.00          1
A002       张飞              70.00          1
A002       刘备              60.00          2