存储过程、函数、触发器.doc

一、创建视图：1 创建班级人数统计视图 create view count_class as select classid,count(classid) from student group by classid2创建院系人数统计视图 create or replace view count_deptas select studept 院系,count(studept) 人数 from student group by studept3创建最高最低分以及平均分视图create or replace view count_scas 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)IsBegin Insert into student values(sno,name,sex,age,dept,telphone,classnum);End;执行存储过程:Begin pro_insert_student(95020,’张三’,’男’,22,’计算机’,’13838025510’,106915);end;2向class表中添加信息：Create or replace procedure pro_insert_class(bjid number,bjname varchar2,bjteacher varchar2,bjmarks varchar2,count number)isBegin Insert into class values(bjid,bjname,bjteacher,bjmarks,count);End;执行存储过程：Begin pro_insert_class(116811,'11软工数据库1班','刘扬涛','数据库原理');end;3向course表中添加记录：Create or replace procedure pro_insert_course(couid number,Couname varchar2,Grade number,Bjid varchar2)isBegin Insert into course values(couid,couname,grade,bjid);End;执行存储过程：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)isBegin Insert into prize values(prizeid,stusno,stuname,prize,comment,prizerem);End;执行存储过程： begin pro_insert_prize(9,95001,'段国平','奖励','运动会得奖',''); end;5向sc表中添加记录： create or replace procedure pro_insert_sc (sno number, couid number, grade number, bjid number ) is begin insert into sc values(sno,couid,grade,bjid); end;执行存储过程：exec pro_insert_sc(95002,1001,82,106817);6向user表中添加记录：create or replace procedure pro_insert_users (sno number,name varchar2, password varchar2, type varchar2 ) Is Begin Insert into users values(sno,name,password,type); end;执行存储过程:exec pro_insert_users(2,'张三','321','学生'); 7删除class表中指定班级的信息create or replace procedure pro_delete_class (classnum number) Is 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; end;执行存储过程：begin pro_delete_course(1009); end;9删除prize中的信息create or replace procedure pro_delete_prize (sno number) Is Begin Delete from prize where prizeid=sno; end;执行该存储过程：Begin Pro_delete_prize(9);End;10删除成绩信息(sc)表中的信息create or replace procedure pro_delete_sc (sno number,couid number) Is Begin Delete from sc where stusno=sno and course=couid; end;执行该存储过程：Begin Pro_delete_sc(95011,1001);End;11删除学生信息(student)表中的信息create or replace procedure pro_delete_student (sno number) Is Begin Delete from student where stusno=sno; end;执行该存储过程:Begin Pro_delete_student( 95021);End;12删除用户（users）表中的信息； create or replace procedure pro_delete_users (id number) Is Begin Delete from users where userid=id; end;执行该存储过程：Begin Pro_delete_student( 95021);End;13修改student表中Create or replace procedure pro_modify_student(sno number,Name varchar2,Sex varchar2,Age number,Dept varchar2,Telphone varchar2,Classnum number)IsBegin Update student set stuname=name where stusno=sno; Update student set stusex=sex where stusno=sno; Update student set stuage=age where stusno=sno; Update student set studept=dept where stusno=sno; Update student set stutel=telphone where stusno=sno; Update student set classid=classnum where stusno=sno;End;begin pro_modify_student(95004,'来福','♀',20,'音乐','111111111',110);end;14修改course create or replace procedure pro_modify_course(num number,name varchar2,gra number,de varchar2) is begin Update course set coursename=num where coursed=num; Update course set grade=gra where coursed=num; Update course set dept=de where coursed=num; end; 15 修改prizecreate or replace procedure pro_modify_prize(id number,sno number,name varchar2,pri varchar2,com varchar2,rem varchar2) is begin Update prize set stusno=sno where prizeid=id; Update prize set stuname=name where prizeid=id; Update prize set prize=pri where prizeid=id; Update prize set content=com where prizeid=id; Update prize set prizerem=rem where prizeid=id; end;16 修改sc表create or replace procedure pro_modify_sc(id number,sno number,gra number,classid number) is begin Update sc prize set grade=gra where stusno=sno and course=id; Update sc prize set classid=gra where stusno=sno and course=id; end;17 修改class表create or replace procedure pro_。