数据库上机 能使用 Oracle 进行示例数据库的设计。掌握 Oracle

【一】上机实验内容及要求

实验要求： 能使用 Oracle 进行示例数据库的设计。掌握 Oracle 函数、存储过程、触发器、视图的使用方法。 实验内容：

Oracle 实例数据库系统 ---------- 招生录取系统

通过对这一系统的模拟，我们可以比较全面地应用前面所学的知识和技能，并提高使用 Oracle 数据库技术解决实际问题的能力。 一、基本需求分析 1 、院校

院校应提供招生的要求，如招生的人数、最低录取分数线以及对考生的其他条件的限制。院校需要在录取结束后查询院校的录取名单。 2 、招生部门

招生部门要收集和整理考生信息，建立考生信息库和院校信息库。考生信息库用于存取考生的基本信息，包括考生的高考成绩和报考志愿；院校信息库用于存取院校的基本信息和招生信息。 招生部门通过数据库中的院校信息和考生信息，由相应的数据库应用程序来完成一系列的数据加工处理过程，其中最主要的就是投档录取过程。所谓投档，就是把满足院校要求的考生档案信息发送给院校，由院校审查档案后决定考生的录取与否。为了简化录取过程，我们由系统投档程序来完成考生的录取工作，一旦考生满足院校招生条件，即视为被录取。被录取的考生，在数据库中要标识成录取状态，并记录录取院校的信息，在院校信息库中要回填录取人数的有关信息。 在录取过程中或录取结束后，招生部门要进行查询和统计，主要是在录取结束后统计所有院校的招生情况。 3 ．考生

考生在录取过程中需要查询其高考分数和录取状态。 二、功能分析设计

系统完成的主要功能有数据录入、投档和查询统计。 （ 1 ）数据录入

数据录入要完成院校和学生信息表的数据输入及修改工作。

在本系统中，为了完成录取的模拟，可以虚拟 10 所院校，给出院校的基本信息和招生要求；虚拟至少 50 名考生，给出考生的基本信息以及考试成绩，并为每个考生填报 2 个院校志愿 ( 一志愿和二志愿 ) 。为了简化数据录入，可直接由 SQL 语句或其他工具完成，比如可以使用 PL/SQL Developer 工具来完成。 2 ．投档

招生录取的原则是：一志愿要求优先录取，一志愿全部录取完毕后，才能开始二志愿的录取；二志愿录取对没有达到计划招生人数的院校进行补充录取。

根据以上原则，投档可分为一志愿投档和二志愿投档。一志愿投档是根据院校编号完成对一个学校的一志愿投档的；二志愿投档是根据院校编号完成对一个学校的二志愿投档的，二志愿投档应该在一志愿投档完成以后进行。一次完成全部院校的投档称为自动投档。自动投档一次完成对所有院校的一志愿或二志愿投档。如果使用自动投档，只需为一志愿和二志愿分别投档一次，即完成投档过程。作为补充，可以设计一个调剂投档功能，对一、二志愿没有被录取的考生，如果存在没有招满的院校，补充录取同意调剂的考生。 3 、查询统计

在投档过程中或投档结束之后，根据院校编号显示院校的录取结果，即录取考生按分数排序的名单，同时应该显示考生的分数、录取的志愿等信息。

在录取结束之后，按院校的录取平均分数排名，显示所有院校的招生统计信息。 三、账户的创建和授权

在开发之前，要为新的应用创建模式账户，并授予必要的权限，以便创建表和其他数据库对象。为了能够创建账户和授权，必须使用具有足够权限的管理账户，可使用系统管理员账户来创建新的应用账户。 步骤 1 ：创建账户：

SQL> create user student identified by student default tablespace users ; 步骤 2 ：授予用户权限。 连接数据库权限：

SQL> grant connect to student; 创建表权限：

SQL> grant create table to student; 创建视图权限：

SQL> grant create view to student; 创建序列权限：

SQL> grant create sequence to student; 创建同义词权限：

SQL> grant create synonym to student; 创建存储过程、函数权限：

SQL> grant create procedure to student; 创建触发器权限：

SQL> grant create trigger to student; 表空间使用权限：

SQL> grant unlimited tablespace tostudent; 步骤 3 ：使用新账户登录： SQL> connect student/student

至此，已经做好了使用 student 账户进行开发的准备。 四、表和视图的设计和实现 1 、院校信息表 (1) 院校信息表结构设计 名称： college 字段结构如表 1 所示。

表 1 院校信息表 college 的结构

字段含义说明 :

院校编号：为该表的主键，是从 1001 开始的 4 位数。 院校名称：院校的全称，必须填写。

录取分数线：是院校确定的考生最低录取分数线，低于录取分数线的考生不能被录取。 招生人数：是院校计划招生的人数。

录取人数：在某院校录取过程中回填的已经被录取的一、二志愿人数的和。当录取人数等于招生人数时录取结束。

有关数值型数据的范围限定可以通过添加约束条件实现。 (2) 、表的创建

使用以下脚本创建院校信息表 COLLEGE ：

SQL>create table college( 院校编号 number(4) primary key, 院校名称 varchar2(30) not null,

录取分数线 number(3) check( 录取分数线 between 300 and 700), 招生人数 number(3) check( 招生人数 <=10), 录取人数 number(3) default 0 );

(3) 、数据的插入

使用以下脚本插入虚拟的 10 所院校数据： SQL>begin

insert into college values(1001,' 清华大学 ',620,5,0); insert into college values(1002,' 北京大学 ',600,4,0); insert into college values(1003,' 武汉大学 ',550,6,0); insert into college values(1004,' 华南理工大学 ',530,3,0); insert into college values(1005,' 复旦大学 ',580 ， 4,0); insert into college values(1006,' 中山大学 ',560,5,0); insert into college values(1007,' 华中科技大学 ',520,4,0); insert into college values(1008,' 暨南大学 ',510,3,0);

insert into college values(1009,' 东北大学 ',500,6,0); insert into college values(1010,' 湘潭大学 ',450,8,0); end;

SQL>commit;

注意：插入的数据如果违反约束条件就会发生错误。 (4) 、检查插入的数据 SQL>select * from college; 2. 学生信息表

(1) 、学生信息表的设计 字段结构如表 2 所示 :

表 2 学生信息表 student 的结构

字段含义说明 :

编号：为该表主键，是从 10001 开始的 5 位数值，可以使用序列自动填充。 学生性别：只能是 1 或 2 ， 1 代表男， 2 代表女，使用约束条件控制。 总分：为高考的总分数，约束条件是小于等于 700 分，假定满分为 700 分。

同意调剂：默认值为 0 ，表示不同意调剂，值为 1 代表同意调剂。同意调剂的考生，在一志愿、二志愿录取结束后，可以参加调剂录取。 (2) 、表的创建

以下脚本创建考生信息表 student ： SQL> create table student( 编号 number(5) primary key, 姓名 varchar2(15) not null,

性别 varchar2(1) check( 性别 in('1','2')),

总分 number(3) check( 总分 <=700), 同意调剂 varchar2(1) default '0', 一志愿 number(4), 二志愿 number(4),

录取状态 varchar2(1) default '0',

录取志愿 varchar2(1) default null check( 录取志愿 in('1','2','3')), 录取院校 number(4) default null, 录取日期 date, 操作人 varchar2(10),

constraint fk_1 foreign key( 一志愿 ) references college( 院校编号 ),

constraint fk_2 foreign key( 二志愿 ) references college( 院校编号 ),

constraint fk_3 foreign key( 录取院校 ) references college( 院校编号 ) );

(3) 、数据的插入

在插入数据中使用序列，可自动生成考生编号。 步骤 1 ：创建序列 stuno_squ:

SQL> create sequence stuno_squ start with 10001 increment by 1 nocache nocycle;

步骤 2 ：使用以下脚本插入 50 名虚拟考生数据： SQL>begin

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 )

VALUES(STUNO_SQU.NEXTVAL, ' 陈文政 ','1',598,1010,1001,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 李敏 ','2',460,1009,1010,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 黄宾 ','1',627,1001,1002,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 张晓羽 ','2',615,1002,1003,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 许小猛 ','1',534,1008,1009,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 杨煌 ','1',555,1005,1007,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 陈丽明 ','2',587,1006,1008,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 尹文哲 ','1',455,1004,1010,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 段然 ','1',325,1010,1006,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 袁慧瑶 ','2',477,1009,1010,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 罗卓群 ','2',367,1003,1008,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 张婷 ','2',665,1001,1009,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 )

VALUES(STUNO_SQU.NEXTVAL,' 李婷 ','2',585,1002,1003,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 林树金 ','1', 600,1005,1006,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 吴岳 ','2',525,1009,1010,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 周易 ','2',485,1010,1009,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 罗惯通 ','1',585,1007,1008,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 石海林 ','2',555,1005,1009,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 李旋 ','2',595,1002,1004,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 张建锋 ','1',688,1001,1002,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL,' 何健飞 ','1',689,1001,1002,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 徐子钊 ','1', 600,1001,1003,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 张庆旭 ','1', 490,1008,1010,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 张蜡 ','1', 502,1008,1009,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 )

VALUES(STUNO_SQU.NEXTVAL, ' 李香 ','2', 600,1003,1004,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 陈衬欢 ','2', 300,1009,1010,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 胡笛 ','2', 610,1001,1002,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 舒娜 ','2', 560,1003,1004,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 普伟 ','1', 519,1004,1009,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 国丹丹 ','2', 415,1009,1010,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 李冠军 ','1', 610,1005,1007,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 郭亚军 ','1', 588,1004,1006,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 陈兵 ','1', 498,1010,1008,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 洪智力 ','1', 378,1003,1005,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 李丽 ','2', 609,1002,1006,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 吴子俊 ','1', 600,1002,1005,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 )

VALUES(STUNO_SQU.NEXTVAL, ' 黄炎炎 ','1', 507,1009,1008,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 黄源源 ','2', 540,1008,1010,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 曹万吉 ','2', 617,1003,1004,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 谢敏 ','2', 348,1005,1006,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 林晨曦 ','1', 532,1007,1008,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 邓树林 ','2', 485,1002,1009,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 邱雨林 ','2', 608,1006,1008,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 唐文文 ','1', 582,1008,1009,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 张韦 ','2', 555,1005,1007,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 胡月 ','2', 557,1007,1009,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 高飞云 ','2', 540,1005,1006,'0');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 陆文浩 ','1', 550,1006,1010,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 )

VALUES(STUNO_SQU.NEXTVAL, ' 孙庆 ','2', 630,1001,1002,'1');

INSERTINTO STUDENT( 编号 , 姓名 , 性别 , 总分 , 一志愿 , 二志愿 , 同意调剂 ) VALUES(STUNO_SQU.NEXTVAL, ' 王聪 ','1', 605,1003,1004,'0'); end;

SQL>commit;

经检查发现考生 10045 的成绩输入错误，不是 555 而应该为 553 ，通过以下查询予以修改。 SQL>updatestudent set 总分 =553 where 编号 =10045; SQL>commit;

(4 ）、检查插入的数据 使用以下查询检查插入的数据： SQL>select * from student; (5) 、通过联合查询检查考生的志愿

由于填报志愿时，考生信息表中填写的是院校编号，需要通过相等连接才能显示院校名称。以一志愿为例，显示考生姓名和报考的一志愿院校名称：

SQL>select 姓名 , 总分 , 院校名称 from student s, college t where s. 一志愿 =t. 院校编号 ; 3 创建视图

一旦建立视图，通过直接对视图进行查询而不是对基表进行查询，可以实现对数据的保护，并简化操作。同时可建立视图的同义词，用于为复杂对象名生成一个简化和便于记忆的别名。考虑建立如表 3 所示的视图。 表 3 视 图

(1) ．考生成绩视图 基表： student

结构：考生成绩 ( 编号，姓名，总分 ) ，只读视图。

功能：为了方便查看学生的成绩，建立学生成绩视图，显示全部学生的考号、姓名和成绩。 步骤 1 ：创建视图：

SQL>create orreplace view 考生成绩 ( 编号 , 姓名 , 总分 )as select 编号 , 姓名 , 总分 from student with read only;

步骤 2 ：生成考生成绩视图的同义词 score ： SQL>CREATE SYNONYM SCORE FOR 考生成绩 ; (2) ．录取考生视图 基表： student 和 college

结构：录取考生 ( 编号，姓名，院校名称 ) ，条件是只显示录取的考生信息，只读视图。需要通过建立相等连接来实现。

功能：为了方便查看学生的录取结果，建立录取学生的视图，显示被录取学生的考号、姓名和录取院校名称。 步骤 1 ：建立视图：

SQL>create or replace view 录取考生 ( 编号 , 姓名 , 录取院校名称 )

as select 编号 , 姓名 , 院校名称 from student, college where 录取状态 ='1' and student. 录取院校 =college. 院校编号 withread only; 步骤 2 ：生成录取考生视图的同义词 result: SQL> create synonym result for 录取考生 ; (3) ．录取情况视图 基表： college

结构：录取情况 ( 院校编号，院校名称，状态，招生人数，缺额 ) ，只读视图。 功能：显示招生计划完成情况，计划招生人数和录取的缺额。 步骤 1 ：创建视图：

SQL> create or replace view 录取情况 ( 院校编号 , 院校名称 , 状态 , 招生人数 , 缺额 ) as select 院校编号 , 院校名称 ,decode(sign( 招生人数 - 录取人数 ),1,' 未完成 ',' 完成 '), 招生人数 , 招生人数 - 录取人数 from college with read only;

步骤 2 ：生成录取情况视图的同义词 status ： SQL> create synonym status for 录取情况 ;

说明： sign 函数返回算术运算结果的符号，结果大于 0 返回 1 ，等于 0 返回 0 ，小于 0 返回 -1 。如果招生人数大于录取人数，则表达式 sign( 招生人数 - 录取人数 ) 的结果为 1 。此时， decode 函数返回 “ 未完成 ” ；否则返回 “ 完成 ” 。 五、 应用程序的设计和实现 1 ．函数的创建

首先要建立一些函数，以便其他模块或查询引用。设计如表 4 所示的函数。 表 4 函数

(1) ．返回考生姓名函数 get_student_name 函数名和参数： get_student_name(p_bh) 。

该函数的返回值类型为 VARCHAR2 。其中， p_bh 代表考生编号。 功能：通过考生的编号获得考生的姓名。 返回考生名称函数如下：

SQL> create or replace functionget_student_name (p_bh number) return varchar2 as v_name varchar2(10); begin

select 姓名 into v_name from student where 编号 =p_bh; returnv_name; exception whenothers then return ' 无 '; end;

(2) ．返回考生成绩函数 get_score

函数名和参数： get_score(P_BH) 。

该函数的返回值类型为 number 。其中， p_bh 代表考生编号。 功能：通过考生的编号获得考生的总分。 返回考生成绩函数如下：

SQL> create or replace functionget_score(p_bh number) return number as

v_score number; begin

select 总分 into v_score from score where 编号 =p_bh; returnv_score; exception whenothers then return -1; end;

注意：分数直接从考生成绩视图中取得， score 为考生成绩视图的同义词。 (3) ．返回院校名称函数 get_college_name 函数名和参数： get_college_name(p_bh)

该函数的返回值类型为 VARCHAR2 。其中， p_bh 代表院校编号。 功能：通过院校的编号获得院校名称。 返回院校名称函数如下：

SQL> create or replace functionget_college_name(p_bh number) return varchar2 as v_name varchar2(30); begin

select 院校名称 into v_name from college where 院校编号 =p_bh; returnv_name; exception whenothers then return ' 无 '; end;

2. 存储过程的创建

系统的功能通过存储过程来完成。考虑建立如表 5 所示的存储过程。 表 5 存储过程

(1) ．插入院校存储过程 input_college 过程名和参数：

input_college(p_yxbh,p_yxmc,p_lqfsx,p_zsrs)

其中， p_yxbh 代表院校编号， p_yxmc 代表院校名称， p_lqfsx 表示录取分数线， p_zsrs 表示招生人数。

功能：该过程用于建立院校信息。每次执行时插入一个院校，部分字段的内容通过参数传递，没有指定参数的字段取字段的默认值。 插入院校程序如下：

步骤 1 ：输入和调试以下存储过程：

SQL> create or replace procedureinput_college( v_yxbh in number, v_yxmc in varchar2, v_lqfsx in number, v_zsrs in number) as rnumber; begin

selectcount(*) into r from college where 院校编号 =v_yxbh; if r>0 then

dbms_output.put_line(' 院校 '||'v_yxbh'||' 已经存在 !'); else

insert into collegevalues(v_yxbh,v_yxmc,v_lqfsx,v_zsrs,0); commit;

dbms_output.put_line(' 院校 '||v_yxmc||' 插入成功 !'); endif; exception when others then

dbms_output.put_line(' 院校 '||v_yxmc||' 插入失败 !'); end;

步骤 2 ：执行该存储过程：

SQL> execute input_college(1011,' 吉林大学 ',570,6);

说明：如果院校编号已经存在，则提示不能插入。通过存储过程插入考生，可以正确显示插入过程的错误信息。

(2) ．插入考生存储过程 input_student 过程名和参数：

input_student(p_ksxm, p_xb, p_zf, p_tytj,p_zy1, p_zy2)

其中， p_ksxm 代表考生姓名， p_xb 代表考生性别， p_zf 代表考生高考分数， p_tytj 代表是否同意调剂， p_zy1 代表考生报考的一志愿院校编号， p_zy2 代表考生报考的二志愿院校编号。

功能：该过程用于输入考生信息。每次执行时插入一个考生，部分字段的内容通过参数传递，没有指定参数的字段取字段的默认值，考生编号取自序列。 ( 3) ．投档初始化过程 clearstatus

过程名和参数： clearstatus

功能：该过程用于在每次开始模拟录取前对考生和院校表进行状态初始化。具体功能包括：清空 college 表的录取人数；设置 student 表所有考生的录取状态为 0( 未录取 ) ，录取院校为空，录取志愿为空，录取日期为空，操作人为空。 初始化程序如下：

SQL> create or replace procedureclearstatus as begin

updatecollege set 录取人数 =0;

updatestudent set 录取状态 =0, 录取志愿 =null, 录取院校 =null, 录取日期 =null, 操作人 =null; commit; end;

(4) ．一志愿投档存储过程 proc1 过程名和参数： proc1(p_yxbh)

其中，参数 p_yxbh 代表要进行一志愿投挡的院校编号。

功能：该过程完成按照院校的要求对某院校进行一志愿投档录取的过程。投档时要指定院校编号作为参数。

投档过程是：对一志愿报考该院校的、分数在最低录取分数线上的学生，按分数进行排序，根据招生人数取前几名录取。

本次录取的考生要回填状态和录取院校等信息，将考生信息表中录取状态改为录取，并将院校编号和录取志愿号 ( 为 1) 填入考生表。同时将院校信息表中对应的录取人数做相应的修改，将实际录取的人数回填院校表的录取人数字段。

说明：如果录取没有达到招生人数，将由二志愿来补充。该过程一次完成一个院校的投档。要一次完成所有院校的投档，可使用后面的自动投档存储过程。

一志愿投档程序如下：

SQL> create or replace procedureproc1(v_yxbh number) as

v_zsrs number(3); v_lqfsx number(3); v_yxmc varchar2(30); v_count number(3);

cursor stu_cursor is select * from student where 一志愿 =v_yxbh order by 总分 desc; begin

select 招生人数 , 录取分数线 , 院校名称 into v_zsrs,v_lqfsx,v_yxmc from collegewhere 院校编号 =v_yxbh; -- 取院校信息 v_count:=0;

dbms_output.put_line(' 院校名称 :'||v_yxmc||' 一志愿投档开始 '); dbms_output.put_line('-----------------------------'); forstu_rec in stu_cursor loop exit when v_count>=v_zsrs; if(stu_rec. 总分 >=v_lqfsx) then

update student set 录取状态 ='1', 录取志愿 ='1', 录取院校 =v_yxbh, 录取日期 =sysdate, 操作人 =user where 编号 =stu_rec. 编号 ; dbms_output.put_line(' 编号 : '||stu_rec. 编号 ||' 姓名 : '||stu_rec. 姓名 ||' 总分 :'||stu_rec. 总分 ); v_count:=v_count+1;

endif; endloop;

dbms_output.put_line('--------------------------------');

update college set 录取人数 =v_count where 院校编号 =v_yxbh; commit; end;

说明：该过程按院校进行一志愿投档，游标定义了一志愿报考该院校的按总分从高到低排序的所有考生信息。 v_count 记录录取的人数，初值为 0 ，每当录取一个考生，则加 1 。若 v_count 等于计划招生人数，则结束录取。如果 v_count 小于招生人数，则取游标的下一个考生，判断其分数是否在该院校的最低录取分数线之上。如果满足，则标记录取状态为 1 。录取结束后，将录取人数 v_count 回填到院校表。 5 ．二志愿投档存储过程 proc2 过程名和参数： proc2(v_yxbh)

其中，参数 v_yxbh 代表要进行二志愿投档的院校编号。

功能：该过程完成按照院校的要求对某院校进行二志愿投档和录取的过程。投档时要指定院校编号作为参数。

过程是：对二志愿报考该院校且还没有录取的 ( 去掉一志愿录取的考生 ) 、分数在最低录取分数线上的学生，按分数进行排序，根据缺额 ( 招生人数去掉录取人数 ) 取前几名录取 . 本次录取的考生要回填状态和录取院校等信息，将考生信息表中录取状态改为录取，并将院校编号、录取志愿号 ( 为 2) 填入考生表。同时将院校信息表中对应的录取人数做相应的修改，将实际录取的人数回填院校表的录取人数字段。

说明：如果没有达到招生人数，将由调剂录取来补充。该过程一次完成一个院校的投档。要一次完成所有院校的投档，可使用后面的自动投档存储过程。 二志愿投档程序如下：

SQL 〉 create or replace procedure proc2(v_yxbhnumber) as

v_zsrsnumber(3);

v_lqfsxnumber(3); v_yxmcvarchar2(30); v_lqrsnumber(3); v_countnumber(3); cursorstu_cursor is

select* from student where 二志愿 =v_yxbh and 录取状态 =0 orderby 总分 desc; begin

select 招生人数 , 录取分数线 , 录取人数 , 院校名称 into v_zsrs, v_lqfsx,v_lqrs,v_yxmc from college where 院校编号 =v_yxbh; v_count:=v_lqrs;

dbms_output.put_line(' 院校名称 '||v_yxmc||' 第二志愿投档开始 '); dbms_output.put_line('-----------------------------'); forstu_rec in stu_cursor loop exit when v_count>=v_zsrs; if(stu_rec. 总分 >=v_lqfsx) then

update student set 录取状态 ='1', 录取志愿 ='2', 录取院校 =v_yxbh, 录取日期 =sysdate, 操作人 =user where 编号 =stu_rec. 编号 ; dbms_output.put_line(' 编号 : '||stu_rec. 编号 ||' 姓名 :'|| stu_rec. 姓名 ||' 总分 :'||stu_rec. 总分 ); v_count:=v_count+1; end if; endloop;

dbms_output.put_line('-----------------------------');

update college set 录取人数 =v_count where 院校编号 =v_yxbh; commit; end;

说明：该过程按院校进行二志愿投档，游标定义了二志愿报考该院校且录取状态为 0( 未录取 ) 的、按总分从高到低排序的所有考生信息。 v_count 记录录取的人数，初值为一志愿已经录取的人数，每当录取一个考生，则 v_count 加 1 。若 v_count 等于计划招生人数，则结束录取。如果 V_COUNT 小于招生人数，则取游标的下一个考生，判断其分数是否在该院校的最低录取分数线上。如果满足，则标记录取状态为 1 。录取结束后，将录取人数 v_count 回填到院校表。

6 ．自动投档程序 autoproc 过程名和参数： autoproc(v_lqzy)

其中， v_lqzy 代表要进行录取的志愿，只能是 1 或 2 ， 1 代表一志愿， 2 代表二志愿。 功能：该过程根据选定志愿，循环对所有院校进行投档，即对所有院校循环调用 proc1 或 proc2 。一次完成所有院校的一志愿或二志愿投档。 自动投档程序如下：

SQL> create or replace procedure autoproc(v_lqzy number) as

cursorcollege_cursor is select 院校编号 from college; begin

forcollege_rec in college_cursor loop if(v_lqzy=1) then

proc1(college_rec. 院校编号 ); elsif (v_lqzy=2) then

proc2(college_rec. 院校编号 ); end if;

endloop; end;

说明：定义一个取所有院校编号的游标 COLLEGE_CURSOR ，根据志愿 ( 参数为 1 或 2) ，在游标循环中以取得的院校编号为参数，调用一志愿或二志愿投档过程。 7 ．查询考生分数过程 show_score 过程名和参数： show_score (p_bh)

其中， p_bh 代表考生编号。

功能：给出考生的考号，返回考生的高考成绩。 查询考生分数程序如下：

SQL> createor replace procedure show_score(v_bh number) as

v_scorenumber(3); begin

v_score:=get_score(v_bh); ifv_score=-1 then

dbms_output.put_line(' 考生编号错误 !'); else

dbms_output.put_line(get_student_name(v_bh)||' 总分 '||v_score); endif; end;

说明：本过程调用返回考生姓名和分数的函数 get_student_name 和 get_score 。 8 ．查询考生录取状态过程 show_result 过程名和参数：

show_result(v_bh)

其中， v_bh 代表考生编号。

功能：给出考生的考号，返回录取院校名称。如果没有录取，则返回 “ 未被录取 ” 。 查询考生录取状态程序如下：

SQL> create or replace procedureshow_result(v_bh number) as

v_lqyxmc varchar2(20); v_xmvarchar2(10); begin

v_xm:=get_student_name(v_bh); ifv_xm=' 无 ' then

dbms_output.put_line(' 考生编号错误 !'); else

select 录取院校名称 into v_lqyxmc from result where 编号 =v_bh; dbms_output.put_line(' 考生 '||v_xm||' 被 '||v_lqyxmc||' 录取 !'); endif; exception when others then

dbms_output.put_line(' 考生 '||v_xm||' 未被录取 !'); end;

说明：查询录取考生视图 result ，如果考生在视图中不存在，则表示未被录取。此过程中调用了前面定义的函数 get_student_name 以获得考生名称。 9 ．显示院校录取名册存储过程 student_list 过程名和参数：

student_list(v_yxbh)

其中， v_yxbh 表示院校编号。

功能：指定院校编号，显示按分数排序的统计报表 :

SQL> create or replace procedurestudent_list(v_yxbh number) as

v_sname varchar2(10); v_maxnumber(3); v_minnumber(3); col_rec college%rowtype;

cursorstu_cursor is select * from student where 录取院校 =v_yxbh order by 总分 desc; begin

select * into col_rec from college where 院校编号 =v_yxbh;

dbms_output.put_line(get_college_name(v_yxbh)||' 院校录取统计表 ');

dbms_output.put_line(' 招生人数 '||' 招生人数 '||' 录取人数 : '||col_rec. 录取人数 ||' 录取分数线 : '||col_rec. 录取分数线 );

dbms_output.put_line('-------------------------------');

dbms_output.put_line(' 序号 考生编号 姓名 性别 总分 录取志愿 录取日期 '); forstu_rec in stu_cursor loop ifstu_rec. 性别 =1 then

dbms_output.put_line(rpad(stu_cursor%rowcount,8,'')||rpad(stu_rec. 编号 ,9,' ')||rpad(stu_rec. 姓名 ,9,'')||' 男 '||rpad(stu_rec. 总分 ,9,' ')||rpad(stu_rec. 录取志愿 ,9,' ')||rpad(stu_rec. 录取日期 ,9,' ')); else

dbms_output.put_line(rpad(stu_cursor%rowcount,8,'')||rpad(stu_rec. 编号 ,9,' ')||rpad(stu_rec. 姓名 ,9,' ')||' 女 '||rpad(stu_rec. 总分 ,9,' ')||rpad(stu_rec. 录取志愿 ,9,' ')||rpad(stu_rec. 录取日期 ,9,' ')); endif; endloop;

dbms_output.put_line('-------------------------------');

select max( 总分 ),min( 总分 ) into v_max,v_min from student where 录取院校 =v_yxbh;

dbms_output.put_line(' 最高分 : '||v_max||' 最低分 : '||v_min); end;

说明：把院校编号作为条件，检索出被某个院校录取的考生，并按分数排序。其中，性别显示需要进行转换。过程中，使用了 rpad 函数产生相等的列宽。在列表之后，通过统计查询，显示最高分和最低分。

10 ．院校招生情况统计表 college_total 过程名和参数： college_total

功能：按照院校的平均录取分数排序所有院校，统计各院校的最高分数、最低分数，招生人数、录取人数、男生人数、女生人数等信息。 院校招生情况统计程序如下：

SQL> create or replace procedurecollege_total as

v_yxbh number(4); v_avgnumber(4); v_maxnumber(4); v_minnumber(4); v_boynumber(3);

(4) ．查询招生人数最多的院校 查询招生人数最多的院校：

SQL>select 院校名称 , 招生人数 from college where 招生人数 =(select max( 招生人数 ) from college);

(5) ．查询考生分数 查询考生分数：

SQL> set serveroutput on; SQL> exec show_score(10005);

(6) ．检查 operation 触发器的记录 SQL> select * from operation_log;

2. 投档过程 (1) ．初始化 初始化程序为：

SQL> set serveroutput on size 10000; SQL> exec clearstatus;

说明：在每次重新开始模拟投档时，都要先调用该过程进行初始化，清除原来的录取信息。 (2) ．一志愿自动投档 一志愿自动投档程序为： SQL> exec autoproc(1);

(3) ．查询考生录取状态 查询考生录取状态的程序为： SQL> exec show_result(10005);

考生 许小猛被暨南大学录取 !

说明：查询编号为 10005 的考生录取状态，返回结果是许小猛被暨南大学录取。 (4) ．二志愿自动投档 二志愿自动投档的程序为： SQL>exec autoproc(2);

说明：使用自动投档 autoproc 进行二志愿投档，参数 2 代表二志愿投档。如果使用 proc2 进行投档，则一次只能投档一个院校。 (5) ．查询录取情况视图 查询录取情况：

SQL>select * from status;

说明：通过视图检查录取情况 . 3. 统计报表

1 ．院校录取考生列表

显示湘潭大学的录取考生列表，并按成绩排序： SQL>exec student_list(1010);

2 ．招生情况统计

显示按平均分降序排列的招生情况统计表： SQL> exec college_total;

.4 结果分析

系统招生录取过程中和结束后，应该对系统数据进行分析，检查是否存在投档错误，避免造成损失。如果发现问题，则要重新投档。 检查没有被录取的考生一志愿报考的院校：

SQL> select 编号 , 姓名 , 总分 , 院校名称 from student s, college c where s. 一志愿 =c. 院校编号 and s. 录取志愿 is null order by 总分 desc;

执行结果：

编号 姓名 总分 院校名称

------ --------------- ---------------------------------- 10045 张韦 553 复旦大学 10047 高飞云 540 复旦大学 10042 邓树林 485 北京大学 10030 国丹丹 415 东北大学

10034 洪智力 378 武汉大学 10011 罗卓群 367 武汉大学 10040 谢敏 348 复旦大学 10009 段然 325 湘潭大学 10026 陈衬欢 300 东北大学 9 rows selected

后 5 名考生因为分数不够 ( 低于最低的录取分数线 450 分 ) 不能被录取，前 3 名考生要对其进行检查 ( 以第一个考生 10045 为例 ) ： SQL> select * from student where 编号 =10045;

该生总分为 553 分，一志愿报 1005 ，二志愿报 1007 。先检查一志愿的情况： SQL>select * from college where 院校编号 =1005;

复旦大学虽然没有招满，但因该考生的分数低于复旦大学的录取分数线，所以一志愿落选正常。继续检查二志愿情况：

SQL>select * from college where 院校编号 =1007;

该考生达到了华科技大学的录取分数线。该校招生 4 人，录取 4 人。继续检查该考生为什么没有被二志愿录取：

SQL >select 编号 , 姓名 , 总分 , 录取志愿 from student where 录取院校 =1007 order by 总分 desc;

该校有 3 名考生被一志愿录取，二志愿录取 1 人。按照一志愿优先的原则， 10045 号考生只能参加二志愿投档。该校二志愿缺额 1 人，按分数从高到低排序，考生杨煌的分数 555 高于考生张韦的分数 553 ，故杨煌优先录取，达到录取人数 4 人，录取结束。 通过以上分析可知，张韦没有被录取属于正常情况。

本文来源：https://www.bwwdw.com/article/fw4g.html