Oracle基础-实训存储过程

一、项目（实训）名称

存储过程的创建及调用

二、项目（实训）学时数

本实训项目预计实训学时数为2课时

三、项目（实训）目标
1.  了解数据表的创建方法
2.  数据操纵练习
3.  存储过程的创建
4.   存储过程的调用

四、项目（实训）中的具体任务

1.建数据表并完成数据插入

（1）学生表

 

（2）课程表

 

（3）成绩表

 2. 完成PL/SQL程序段练习

（1）创建名为“PROC_SHOW_EMP”的存储过程，以系别编号为参数，查询并输出该系平均高考分数，以及该系中比该系平均高考分数高的学生信息。

（2）创建名为“PROC_RETURN_DEPTINFO”的存储过程，以系别编号为参数返回该系的人数和平均高考分数。

（3）完成上述两个存储过程的调用。

 

 

全部代码：

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;

如有错误之处,敬请指正