Oracle基础-实训函数练习

一、项目（实训）名称
函数的创建及调用

二、项目（实训）学时数
本实训项目预计实训学时数为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 tablecreate 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)asbegin	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 onexec PROC_SHOW_EMP(10)--PROC_RETURN_DEPTINFO过程调用set serveroutput ondeclare 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%typeas	v_maxscore student.Cscore%type;begin	select max(Cscore) into v_maxscore from student where dapno=p_dapno;	return v_maxscore;exceptionwhen 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%typeas	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 ondeclare	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 ondeclare	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;