西北农林科技大学数据库实习五

1、存储过程与触发器实验报告一、实验目的(1)掌握Oracle数据库编程语言PL/SQL的基础知识。(2)掌握游标、存储过程和触发器的创建，使用方法。(3) 了解使用高级语言连接数据库的技术、基本方法，了解 的技 ODBC ADO JDBC 术。八实验内容在实验一、实验二创建的表中用PL/SQL语言完成以下内容：1?创建存储过程，根据调用时提供的学生姓名查询该学生所修课程的课程信息，在过程体中将课程号、课程名和成绩输出到输出窗口，在SQL窗口中给出过程调用语句块。te:it ?.根摇调用时握供的学牛姑名查询该学生庙修课程的课程信息课程窖和成绩输出到输出窗口，在驱窗口巾给出过程调用语句create

2、or replace procedure Pl(V_snanue in student. snaraje%type ?al is select course, cnot cname, gradeopen al;| loopfetch 和芝cno out course. cnoXtypsA芝cnaHE out coursefrom studentj. coursescwhere student? sno- snainBi =V_snamiG;1 into V-cno, VAcnAme, V.grade.exit when alKnotfound;dbms_Dutput, put_line 

3、9;'V_cnn end loop;close al;必ORCL-w编译砺功enamel type, V_grade out sc. grade%type) issno and course-cno-sc. eno and stud&nt.I |JI I V_cnaniE W ' I |V_grade);创建的存储过程P1(b 11 c 11 d);讪？RJCLtn SQL § ? declareA stu d pnt r5n a me%typs出编计兼declarea student, snaneAtype := 刘鹏程b varchar2(2V );c v

4、archar2 C5Q);d number ;beginPl a, bT c? dj dbms_output. put_lineenU；|国SQL窗口 -几色昆值:SQL输出统计表清除iE冲区丸小10000116，经济法，33 A140，审之党史，95291,-国际结算，42291国际结算42=眇 & 44SQL中调用的语句块语句执行结果2 ?创建存储过程，统计指定学生学号的平均成绩和选课门数，将统计结果用输出参数传递给主程序，在SQL窗口中调用存储过程，输出过程的返回结果E1B92创建存储过程.统计拾定学生学号的平均成绩和选课门数.一将轨讣结果用输出参数倍递给主程序，在SQL窗口中调

5、用存储过程，输出过程的延回结果Create or replace praceduxe P2(V_sno in student* sno%typE ;avg_grade out nuniber,total ciut number)isbeginselect avg(grade),count(eno) into avg-eriide) totalfrom sc10 where sc. sno-V_sno;11 l end;< I xMDRCL£编译咸功创建的存储过程P2SQL 啬口m用 P2 dedare a varchar?(调用巴declarea varchar2( 10) :

6、= 103846,;b number;c number;beginP2(a, b, c);dbms_output?put_line(b |J, Id; end;|SQLfAQdeclare a va rchar2(u.血山SQL iKT歙呕朴蹶：毗*=E & 9:5 itffiKLSQL中调用存储过程 P2执行结果句块。2 在SQL窗口中给出过程调用语句块.J EI 1 di: 工二二 ；1 . .eLLc c P3 V csno ij. students n4 V_snaae in student. HnameSypa,V s£qm :二.student_6 V_snati

7、on in student- snation;ypeT阡in stud白mt节poli"ype.eV sbirth in student, sbirttire,7 V_scol 1 egp ;n stiidEnt 5CclleeB*vri.10 V una jor in etudant, unajoTtyp-11 is 12 begin1三臼 ： it i：r student1 sno, snamer ssev sna tion, apolitical sbirth, scollegeT snui j or i14 v«lue5(V_sno,Vessel, Vsnatian

8、n, V_spol itical. V_sbi rthF V_scd1 legeF V_sw joris | camnitJ 16 erjdjI"?，xkWKCL-w璃诺成功存储过程P3SQLffi入语句语句执行结果调用语句块。创建存储过程P4Q & 2:16热？ OECLT SQL .口 - begin P4c| 二二 9 & 4:1 xkaRCL执行SQL语句执行语句前P1的情况,'J SQL 超二 select , from P whtre pn口 SQL 输出 蜕计表Select from P "hera pno='FJI田aM 0P

9、HO IPHA1E COLOR fEIGHTI 11p，22=匕 & l：l* xHOBCL执行SQL语句后的P1输出窗口，并在SQL窗口中给出过程调用语句块。create or replace procedure P5(V_duwn in SPJ. QTYWtype,procedure AK-PHPUKLL 吐 1 WII 凹 IRKNV,up in SPJ. QTY%type,V_pname out p. pnajiLe%type TV sname out s. snaiiie%type) iss曰 cursor c5 isselect pnaiwe, sname from p,

10、s, spjwhere p. pn? =Ep j. pna and s. eno=spj. sna jnd JiTT between V_dovm and V_up, begin HD E penc5;loopfmteh c5irrtci V-pname, V_sname exit when cSltnotfound;dbjns_nutputA put_l inB (V_pnamB | |J h ' | |V_snane), end loop;close c5; end P5;x>QQBCL创建的存储过程P5111112345617189 0 12 3 4567890酬 QL窗匚

11、-declare aarchar2(5Q; b|V?,0 SQL 查口 - decldrA a v*rchar25Qk b. ”缓冲1K大小10000declareavarchar2(50) bvarchar2(50);begiPbtlOOja； b); end;红红民方锡感感方益感 民益为东感泰民泰东精橇 为r为丰 ，？刀刀刀 F?母母母 栓栓丝丝丝轮桧轮B螺螺 螺晶矍«凸齿齿执行结果执行SQL语句6. (1)删除SPJ关系中所有数据; SQL 誉匚 * delete from spj; commitSQL辅出绩计提deletefromspj commt|Delate spj Co

12、mmit(无结果集)C? 4 3: ?山SOBCL七1已完咸，耗日删除语句已清空数据(2)在插入和修改SPJ表中QTY属性列的值时用触发器实现约束：如果是 北京的供应商，供应任何零件的数M不能少于300,如果少于则自动改为300-XA?RCL卡I煽译箴|(3 )在SPJ表中录入值进行验证0| <=> |ST虔U SQL 匿口 - insert Into SPJ冏匚输生绩计表insertint.oSPJvalues' SlA, ' Pl, JJl3, 100);insertintoSPJvalues C S2J ?' P3?, ' J23 > 1

13、50);insertintoSPJvalues CS3? J F2 'J J3? f 200);update spjset QTY=200where sno = J S2?; |sei ect * from spj ; coirani tSNO J PNO |JNO QTY王 SIPlJI1002_S2P3J23003 S3P2J3300a 6：i6xkOORCL执行结果7. (1)删除SC关系中的所有数据。既 SQL 药口 * delete from sc : select * from $c; I <=j | 回 | SQL 输出统计裘delete from sc's

14、elect * fi 口 m sc;SNO CNO GRADE良 o vbfflnurTqsc表已经清空(2)在SC关系中增加新属性列 Status ,用来记录课程成绩的等级。盘 SQL 窗口- alter table sc add Status varchar(30? 11同L 输出绕计提alter table sc add Status varchar(30);令select * from sc JAlter sc Select sc| SNO CNO GRADE | STATUS |=呼& 2:10-w 0新增加的Status列(3)用触发器实现自动记录成绩等级，当插入和修改gr

15、ade列的值时，如 果grade在059分，status自动填写为“不及格” ;grade在60 69分，status 自动填写为“及 格” ；grade在70 89分，status自动填写为“良好” ；grade 在90以上status自动 填写为“优秀”。创建的触发器insert insert insert insert selectSNOCNO1 101326 103101327104101328105-101329106GRADE J STATUS50不合格"62合格75良好91优秀亍 xhflOECL七4行披选择，耗时O.D47秒)| SQL 窘口 * insert int

16、o scfEo.grade) valuesCIOISAS'/IOS'.SOJj insert into SQL血出into sc(sno, cn。 grade) valuesC 101326*/ 1031,50); values into sc(sno, eno, grade) C 101327* / 10<, 62). values C into sc(sno, eno, grade 1 101323' / 105 75); valuesC into scCsnoA cno grade? 101329 ?106 91);* from sc执行插入操作时候的运行结

17、果拐 SQL 窗口 - update sc set grade = 93 where sno = '101 326' update ec set gr.SQL 输出 统计表update update update updatesc set gradesc set grade sc set grade sc set grade93 where sno72 where sno65 where sno41 where snoJ 101326s ? 101327, J 101328s-'101 妙SMOCNOGRADE 1STATUS10132610393合格101327104

18、72优秀101328105口良好10132910641不一格丁 ilc?OECL44行被选择，耗时0 031秒执行更新操作时候的运行结果8 ?创建触发器，实现在转专业记录表change_major中自动登记转专业信息，即当修改学生表中属性列major的值时，系统自动在 change_major中插入一条 转专业的信息（注：change_major中的id属性列可设置为自动增长，具体参照课堂讲解的例题）。品精序列 XK.CHANGE MAJOR IDORCL- Create jsegu&ncecreate sequenceCHANGEJAJOR_IDminvalue 1maxvalue 9999999start with 1incrsnient by 1nocache;关闭？帮助址）查看SQL CO xPeORCLID自动增长语句rigger X<T3ORCL »»垃 aoscL4a嵋译感功创建的触发器t3SUL KU ? updmt 色 student set sinajor ='羽口牛，15/ wfriere sname = rA.W;