实验三 存储过程和函数

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

create or replace function f_num_sal(vempno in emp.empno%type) return number
as
vsal number;
begin
select sal into vsal from emp where empno=vempno;
return vsal;
end;
/

2. 创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。

create or replace procedure f_deptno_avgdeptsal(vdeptno in emp.deptno%type)
as
vsal number;
begin
select avg(sal)  into vsal from emp where deptno=vdeptno;
dbms_output.put_line('平均工资'||vsal);
dbms_output.put_line('比平均工资高的员工号、员工名');
for v_emp in (select * from emp where deptno=vdeptno and sal>vsal)
loop
dbms_output.put_line(v_emp.empno||'、'||v_emp.ename);
end loop;
end;

3. 创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。

create or replace procedure p_deptno_numandmaxsal(pdeptno emp.deptno%type)
is
max_sal emp.sal%type;
a number;
begin
select  count(*) into a from emp where deptno=pdeptno;
dbms_output.put_line('部门人数='||a);
select max(sal) into max_sal from emp where deptno=pdeptno;
dbms_output.put_line('最高工资='||max_sal);
end;

4. 创建一个以部门号为参数，返回该部门最高工资的函数。

create or replace function f_deptno_maxsal(vdeptno in emp.deptno%type) return number
as
max_sal number;
begin
select sal into max_sal from emp where empno=vdeptno;
return max_sal;
end;

5. 使用存储过程统计每个学生的‘已修学分’。

create table stu(
sname char(10) not null,
sno char(10) not null,
tcredit number);

create table sc
(sno char(10) not null,
cno char(10) not null,
grade number,
credit number,
primary key (sno,cno));

create table course
(cno char(10) not null,
cname char(20) not null);
insert into sc values('100001','200001',69,5);
insert into sc values('100001','200002','50','4');
insert into course values('200002','数据库系统设计');

create or replace procedure p_student_credit(
p_name out stu.sname%type,
p_credit out number)
as
begin
for p in (
select a.sname,sum(b.credit) credit
from stu a,sc b
where a.sno=b.sno)
loop
dbms_output.put_line(p.sname||'、'||p.credit);
end loop;
end;

6. 使用触发器实现当登记学生成绩（60分以上）时自动统计学生的‘已修学分’。

create or replace trigger t_insert_credit
after insert on sc
for each row
begin
if :new.grade>=60 then
update stu 
set stu.tcredit=stu.tcredit+ :new.credit where stu.sno= :new.sno;
end if;
end;

7. 使用函数实现统计各课程的未及格人数，要求输入参数课程号后，返回各课程的人数NUM。并实现调用，查‘数据库系统设计’的未及格人数。

create or replace function f_cno_num(f_cno sc.cno%type) return number
as
num number;
name char(20);
begin
select cname into name from course where  cno=f_cno;
select count(*) into num from sc where grade<60 and cno=f_cno;
 dbms_output.put_line(name||'未及格人数'||num);
return num;
end;

select f_cno_num(200002) 不及格人数 from dual;

8. 将上实验中的存储过程与本实验中函数创建包。

create or replace package emp_package
is
function f_num_sal(vempno in emp.empno%type) return number;
procedure f_deptno_avgdeptsal(vdeptno in emp.deptno%type);
procedure p_deptno_numandmaxsal(pdeptno emp.deptno%type);
function f_deptno_maxsal(vdeptno in emp.deptno%type)  return number;
end emp_package;