資源描述：
《存儲(chǔ)過程、函數(shù)、觸發(fā)器》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、一、創(chuàng)建視圖：1創(chuàng)建班級(jí)人數(shù)統(tǒng)計(jì)視圖createviewcount_classasselectclassid,count(classid)fromstudentgroupbyclassid2創(chuàng)建院系人數(shù)統(tǒng)計(jì)視圖createorreplaceviewcount_deptasselectstudept院系,count(studept)人數(shù)fromstudentgroupbystudept3創(chuàng)建最高最低分以及平均分視圖createorreplaceviewcount_scasselectcourse課程號(hào),av
2、g(grade)平均分,max(grade)最高分,min(grade)最低分fromscs1groupbycourse二、存儲(chǔ)過程1向student表中插入數(shù)據(jù)Createorreplaceprocedurepro_insert_student(snonumber,Namevarchar2,Sexvarchar2,Agenumber,Deptvarchar2,Telphonevarchar2,Classnumnumber)IsBeginInsertintostudentvalues(sno,name,s
3、ex,age,dept,telphone,classnum);End;執(zhí)行存儲(chǔ)過程:Beginpro_insert_student(95020,’張三’,’男’,22,’計(jì)算機(jī)’,’13838025510’,106915);end;2向class表中添加信息：Createorreplaceprocedurepro_insert_class(bjidnumber,bjnamevarchar2,bjteachervarchar2,bjmarksvarchar2,countnumber)isBeginInser
4、tintoclassvalues(bjid,bjname,bjteacher,bjmarks,count);End;執(zhí)行存儲(chǔ)過程：Beginpro_insert_class(116811,'11軟工數(shù)據(jù)庫1班','劉揚(yáng)濤','數(shù)據(jù)庫原理');end;3向course表中添加記錄：Createorreplaceprocedurepro_insert_course(couidnumber,Counamevarchar2,Gradenumber,Bjidvarchar2)isBeginInsertintocou
5、rsevalues(couid,couname,grade,bjid);End;執(zhí)行存儲(chǔ)過程：Execpro_insert_course(1010,'JavaWeb高級(jí)編程',4,106814);4向prize表中添加記錄Createorreplaceprocedurepro_insert_prize(prizeidnumber,stusnonumber,stunamevarchar2,prizevarchar2,commentvarchar2,prizeremvarchar2)isBeginInsert
6、intoprizevalues(prizeid,stusno,stuname,prize,comment,prizerem);End;執(zhí)行存儲(chǔ)過程：beginpro_insert_prize(9,95001,'段國(guó)平','獎(jiǎng)勵(lì)','運(yùn)動(dòng)會(huì)得獎(jiǎng)','');end;5向sc表中添加記錄：createorreplaceprocedurepro_insert_sc(snonumber,couidnumber,gradenumber,bjidnumber)isbegininsertintoscvalues(sno,
7、couid,grade,bjid);end;執(zhí)行存儲(chǔ)過程：execpro_insert_sc(95002,1001,82,106817);6向user表中添加記錄：createorreplaceprocedurepro_insert_users(snonumber,namevarchar2,passwordvarchar2,typevarchar2)IsBeginInsertintousersvalues(sno,name,password,type);end;執(zhí)行存儲(chǔ)過程:execpro_insert_
8、users(2,'張三','321','學(xué)生');7刪除class表中指定班級(jí)的信息createorreplaceprocedurepro_delete_class(classnumnumber)IsBeginDeletefromclasswhereclassid=classnum;end;執(zhí)行存儲(chǔ)過程：beginpro_delete_class(106816);end;8刪除course信息createorreplacepr
