oracle数据库实验3 存储过程和函数

改命令提示符set sqlprompt 名字>

scott/密码 登录scott用户
conn sys/ as sysdba切换超级管理员

表格不在一行显示，可修改以下两个参数
set linesize 150
set pagesize 200

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;

执行以下代码查看效果

declare
vnum number;
begin
vnum:=f_num_sal(7788);
dbms_output.put_line('工资'||vnum);
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;

 执行以下代码查看效果

declare
begin
f_deptno_avgdeptsal(20);
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;

执行以下代码查看效果

declare
begin
p_deptno_numandmaxsal(20);
end;

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

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

执行以下代码查看效果

declare
vsal number;
begin
vsal:= f_deptno_maxsal(20);
dbms_output.put_line('该部门最高工资'||vsal);
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(10) not null);

insert into stu values('w','2001',3);
insert into stu values('n','2002',1);
insert into sc values('2001','101',58,1);
insert into sc values('2001','102',59,1);
insert into sc values('2001','103',61,1);
insert into sc values('2002','102',62,1);
insert into course values('101','课程-数');
insert into course values('102','课程-据');
insert into course values('103','课程-库');

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
group by a.sname)
loop
p_name:=p.sname;
p_credit:=p.credit;
dbms_output.put_line('姓名：'||p.sname||'已修学分：'||p.credit);
end loop;
exception
when others then
dbms_output.put_line('其他错误');
end;

执行以下代码查看效果

declare
vname stu.sname%type;
vcredit number;
begin
p_student_credit(vname,vcredit);
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;

执行以下代码查看效果

insert into sc values('2001','104',63,1);
select * from stu;

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(102) 不及格人数 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;