资源描述：
《存储过程、函数、触发器》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、一、创建视图：1创建班级人数统计视图createviewcount_classasselectclassid,count(classid)fromstudentgroupbyclassid2创建院系人数统计视图createorreplaceviewcount_deptasselectstudept院系,count(studept)人数fromstudentgroupbystudept3创建最高最低分以及平均分视图createorreplaceviewcount_scasselectcourse课程号,av
2、g(grade)平均分,max(grade)最高分,min(grade)最低分fromscs1groupbycourse二、存储过程1向student表中插入数据Createorreplaceprocedurepro_insert_student(snonumber,Namevarchar2,Sexvarchar2,Agenumber,Deptvarchar2,Telphonevarchar2,Classnumnumber)IsBeginInsertintostudentvalues(sno,name,s
3、ex,age,dept,telphone,classnum);End;执行存储过程:Beginpro_insert_student(95020,’张三’,’男’,22,’计算机’,’13838025510’,106915);end;2向class表中添加信息：Createorreplaceprocedurepro_insert_class(bjidnumber,bjnamevarchar2,bjteachervarchar2,bjmarksvarchar2,countnumber)isBeginInser
4、tintoclassvalues(bjid,bjname,bjteacher,bjmarks,count);End;执行存储过程：Beginpro_insert_class(116811,'11软工数据库1班','刘扬涛','数据库原理');end;3向course表中添加记录：Createorreplaceprocedurepro_insert_course(couidnumber,Counamevarchar2,Gradenumber,Bjidvarchar2)isBeginInsertintocou
5、rsevalues(couid,couname,grade,bjid);End;执行存储过程：Execpro_insert_course(1010,'JavaWeb高级编程',4,106814);4向prize表中添加记录Createorreplaceprocedurepro_insert_prize(prizeidnumber,stusnonumber,stunamevarchar2,prizevarchar2,commentvarchar2,prizeremvarchar2)isBeginInsert
6、intoprizevalues(prizeid,stusno,stuname,prize,comment,prizerem);End;执行存储过程：beginpro_insert_prize(9,95001,'段国平','奖励','运动会得奖','');end;5向sc表中添加记录：createorreplaceprocedurepro_insert_sc(snonumber,couidnumber,gradenumber,bjidnumber)isbegininsertintoscvalues(sno,
7、couid,grade,bjid);end;执行存储过程：execpro_insert_sc(95002,1001,82,106817);6向user表中添加记录：createorreplaceprocedurepro_insert_users(snonumber,namevarchar2,passwordvarchar2,typevarchar2)IsBeginInsertintousersvalues(sno,name,password,type);end;执行存储过程:execpro_insert_
8、users(2,'张三','321','学生');7删除class表中指定班级的信息createorreplaceprocedurepro_delete_class(classnumnumber)IsBeginDeletefromclasswhereclassid=classnum;end;执行存储过程：beginpro_delete_class(106816);end;8删除course信息createorreplacepr
