一、项目(实训)名称
函数的创建及调用
二、项目(实训)学时数
本实训项目预计实训学时数为2课时
三、项目(实训)目标
1. 了解数据表的创建方法
2. 数据操纵练习
3. 函数的创建
4. 函数的调用
四、项目(实训)中的具体任务
1.建数据表并完成数据插入
(1)学生表
(2)课程表
(3)成绩表
2. 完成PL/SQL程序段练习
(1)创建名为“FUNC_DEPT_MAXSAL”的函数,以系别编号为参数,返回该系最高高考分数。
(2)创建一个名为“FUNC_DEPT_INFO”的函数,以系别编号为参数,返回该编号的系名称、人数及平均高考分数。
(3)通过func_dept_maxsal函数的调用,输出各个系别的最高高考分数;通过func_dept_info函数调用,输出各个系名称、人数及平均高考分数。
全部代码:
CREATE user C##test identified by 123;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO C##test;
GRANT create view TO C##test;
ALTER USER C##test DEFAULT TABLESPACE USERS;
ALTER USER C##test TEMPORARY TABLESPACE TEMP;
CONN C##test/123
--student table
create table student(
stuno number(10) not null,
sname varchar(10) not null,
sex char(4) constraint sex_check check (sex='男' or sex='女'),
age int constraint age_check check (age>0 and age<120),
native_place varchar(10) not null,
department varchar(20) not null,
dapno number(2) not null,
lead varchar(20) not null,
Cscore number(3) not null,
constraint student_pk primary key(stuno)
);
insert into student values(20120211,'詹鹏飞','男',23,'广东','计算机系',10,'张三',590);
insert into student values(20120212,'王嘉威','男',22,'广西','计算机系',10,'张三',576);
insert into student values(20120213,'刘阳','男',21,'浙江','计算机系',10,'张三',590);
insert into student values(20120214,'徐俊杰','男',21,'上海','生化系',20,'李四',567);
insert into student values(20120215,'卢鸿健','男',23,'江苏','生化系',20,'李四',456);
insert into student values(20120216,'王玉莹','女',24,'浙江','中文系',30,'王五',563);
insert into student values(20120217,'陈志恒','男',21,'浙江','中文系',30,'王五',345);
insert into student values(20120218,'林慧敏','女',23,'浙江','电子信息',40,'赵子龙',456);
insert into student values(20120219,'陈慧琳','女',22,'上海','电子信息',40,'赵子龙',456);
insert into student values(20120220,'黄静怡','女',23,'江苏','电子信息',40,'赵子龙',561);
create table class(
cno number(3) not null,
cname varchar(25) not null,
cgrade varchar(5) not null,
hour_class number(3) not null,
constraint class_pk primary key(cno)
);
insert into class values(101,'XML技术','12级',20);
insert into class values(102,'数据库原理','13级',30);
insert into class values(103,'Oracle原理与应用','14级',40);
create table c_score(
stuno number(10) references student(stuno),
cno number(3) references class(cno),
term number(1) constraint term_check check (term>0 and term<10),
score number(3) not null,
label varchar(20),
constraint C_score_pk primary key(stuno,cno)
);
insert into c_score values(20120211,101,4,69,null);
insert into c_score values(20120212,101,4,84,null);
insert into c_score values(20120213,102,4,75,null);
insert into c_score values(20120214,102,4,76,null);
insert into c_score values(20120215,103,5,89,null);
--创建名为“PROC_SHOW_EMP”的存储过程,以系别编号为参数,查询并输出该系平均高考分数,以及该系中比该系平均高考分数高的学生信息。
create or replace procedure PROC_SHOW_EMP(
p_dapno in student.dapno%type)
as
p_score number;
begin
select avg(Cscore) into p_score from student where dapno = p_dapno;
DBMS_OUTPUT.PUT_LINE(p_dapno||'系平均高考分数:'||p_score);
for i in (select * from student where dapno = p_dapno and Cscore>p_score) loop
DBMS_OUTPUT.PUT_LINE(i.stuno||' '||i.sname||' '||i.sex||' '||i.age||' '||i.native_place||' '||i.department||' '||i.dapno||' '||i.lead||' '||i.Cscore);
end loop;
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('no data!');
end;
--创建名为“PROC_RETURN_DEPTINFO”的存储过程,以系别编号为参数返回该系的人数和平均高考分数
create or replace procedure PROC_RETURN_DEPTINFO(
p_dapno in student.dapno%type,
p_num out number,
p_score out number)
as
begin
select count(stuno),avg(Cscore) into p_num,p_score from student where dapno = p_dapno;
DBMS_OUTPUT.PUT_LINE('系名:'||p_dapno||' 人数:'||p_num||' 平均高考成绩:'||p_score);
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('no data!');
end;
--PROC_SHOW_EMP调用
set serveroutput on
exec PROC_SHOW_EMP(10)
--PROC_RETURN_DEPTINFO过程调用
set serveroutput on
declare
v_num number;
v_score number;
begin
PROC_RETURN_DEPTINFO(20,v_num,v_score);
DBMS_OUTPUT.PUT_LINE('人数:'||v_num||'平均高成绩:'||v_score);
end;
--FUNC_DEPT_MAXSAL()方法
create or replace function FUNC_DEPT_MAXSAL(
p_dapno in student.dapno%type)
return student.Cscore%type
as
v_maxscore student.Cscore%type;
begin
select max(Cscore) into v_maxscore from student where dapno=p_dapno;
return v_maxscore;
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('no data!');
end;
--FUNC_DEPT_INFO()方法
create or replace function FUNC_DEPT_INFO(
x_dapno in student.dapno%type,
x_num out number,
x_avgscore out number)
return student.department%type
as
x_department student.department%type;
begin
select department into x_department from student where dapno=x_dapno and rownum = 1;
--只需要一列即可
select count(*),avg(Cscore) into x_num,x_avgscore from student where dapno=x_dapno;
return x_department;
end;
--FUNC_DEPT_MAXSAL()方法调用
set serveroutput on
declare
v_maxscore student.Cscore%type;
begin
for i in (select DISTINCT dapno from student where dapno is not null) loop
v_maxscore:=FUNC_DEPT_MAXSAL(i.dapno);
DBMS_OUTPUT.PUT_LINE(i.dapno||' '||v_maxscore);
end loop;
end;
--FUNC_DEPT_INFO()方法调用
set serveroutput on
declare
v_avg student.Cscore%type;
v_num number;
v_department student.department%type;
begin
for i in (select DISTINCT dapno from student where dapno is not null) loop
v_department:=FUNC_DEPT_INFO(i.dapno,v_num,v_avg);
DBMS_OUTPUT.PUT_LINE(v_department||' '||v_num||' '||v_avg);
end loop;
end;