C#+Oracle学生信息管理系统

一、创建视图：

1 创建班级人数统计视图

create view count_class

as

select classid,count(classid) from student

group by classid

2 创建院系人数统计视图

create or replace view count_dept

select course 课程号,avg(grade) 平均分,max(grade) 最高分,min(grade) 最低分

from sc s1

group by course

二、存储过程

1 向 student 表中插入数据

Create or replace procedure pro_insert_student(sno number,Name varchar2,Sex

varchar2,

Age number,Dept varchar2,Telphone varchar2,Classnum number)

Is

Begin

2 向 class 表中添加信息：

Create or replace procedure pro_insert_class(

bjid number,

bjname varchar2,

bjteacher varchar2,

bjmarks varchar2,

count number)

is

Begin

Insert into class values(bjid,bjname,bjteacher,bjmarks,count);

pro_insert_class(116811,'11 软工数据库 1 班','刘扬涛','数据库原理');

end;

Create or replace procedure pro_insert_course

(couid number,

Grade number,

执行存储过程：

Exec pro_insert_course(1010,'JavaWeb 高级编程',4,106814);

4 向 prize 表中添加记录

Create or replace procedure pro_insert_prize

(prizeid number,stusno number,stuname varchar2,prize varchar2,comment

varchar2,prizerem varchar2)

is

Begin

Insert into prize values(prizeid,stusno,stuname,prize,comment,prizerem);

End;

执行存储过程：

begin

pro_insert_prize(9,95001,'段国平','奖励','运动会得奖','');

5 向 sc 表中添加记录：

)

is

begin

insert into sc values(sno,couid,grade,bjid);

end;

exec pro_insert_sc(95002,1001,82,106817);

create or replace procedure pro_insert_users

password varchar2,

type varchar2

)

Is

Begin

end;

执行存储过程:

Begin

Delete from class where classid=classnum;

end;

执行存储过程：

begin

pro_delete_class(106816);

end;

8 删除 course 信息

create or replace procedure pro_delete_course

(coursenum number) Is

Begin

Delete from course where coursed=coursenum;

执行存储过程：

9 删除 prize 中的信息

(sno number) Is

Begin

Delete from prize where prizeid=sno;

end;

执行该存储过程：

Begin

Pro_delete_prize(9);

End;

10 删除成绩信息(sc)表中的信息