1、过程作业评讲练习一：作一存储过程和函数，完成下面的功能：输入姓名，课程名，成绩该过程完成对 SC 表的插入或修改操作，若插入成功，返回成功信息，若该选课信息已经存在，则修改其成绩为输入的成绩，若遇系统错误，返回错误信息。过程实现:create or replace procedure sc_inorup (sc_sname in varchar2,sc_cname in varchar2,sc_grade in number,out_msg out varchar2)iscount_num number;student_sno varchar(10);course_cno varchar(10
2、);beginbegin/*select sno into student_sno from student where sname=sc_sname;exception when no_data_found then student_sno=;out_msg=名字不存在;return;when others thendbms_output.put_line(sqlerrm);return;end;select cno into course_cno from course where cname=sc_cname;exception when no_data_found then cours
3、e_cno=;out_msg=课程名不存在;return;when others thendbms_output.put_line(sqlerrm);return;end;*/select 1 into count_num from sc,student,course where sc.sno=student.sno and o=oand sname=sc_sname and cname=sc_cname; exception when no_data_found then count_num:=0;when others thendbms_output.put_line(sqlerrm);r
4、eturn;end;if count_num=1 then beginupdate sc set grade=sc_grade where sno in (select sc.sno from sc,student where sc.sno=student.sno and sname=sc_sname ) and cno in(select o from sc,course where o=o and cname=sc_cname);commit;dbms_output.put_line(修改成功!);exception when others thendbms_output.put_line
5、(修改失败!);end;end if;- if count_num=1 thenif count_num=0 then 插入操作beginselect sno into student_sno from student where sname=sc_sname;exception when no_data_found then dbms_output.put_line(没有此姓名!);return;end;beginselect cno into course_cno from course where cname=sc_cname;exception when no_data_found t
6、hen dbms_output.put_line(没有此课程名!);return;end;begininsert into sc values(student_sno,course_cno,sc_grade);commit;dbms_output.put_line(插入成功!);exception when others thendbms_output.put_line(插入失败!);end;end if;- if count_num=0 thenend;-执行:declares_sname varchar2(20):=张三;s_cname varchar2(20):=C 语言; s_grad
7、e number:=50;s_msg varchar2(200);beginsc_inorup(s_sname,s_cname,s_grade,s_msg);end;-函数实现:create or replace function fsc_inorup (sc_sname in varchar2,sc_cname in varchar2,sc_grade in number)return varchar2iscount_num number;student_sno char(10);course_cno char(10);end_outputline varchar2(30);beginbeg
8、inselect 1 into count_num from sc,student,course where sc.sno=student.sno and o=oand sname=sc_sname and cname=sc_cname; exception when no_data_found then count_num:=0;when others thenend_outputline:=sqlerrm;return(end_outputline);end;if count_num=1 then beginupdate sc set grade=sc_grade where sno in
9、 (select sc.sno from sc,student where sc.sno=student.sno and sname=sc_sname) and cno in (select o from sc,course where o=o and cname=sc_cname);commit;end_outputline:=修改成功!;return(end_outputline);exception when others thenend_outputline:=修改失败!;return(end_outputline);end;end if;if count_num=0 thenbegi
10、nbeginselect sno into student_sno from student where sname=sc_sname;exception when no_data_found then end_outputline:=没有此姓名!;return(end_outputline);end;beginselect cno into course_cno from course where cname=sc_cname;exception when no_data_found then end_outputline:=没有此课程名!;return(end_outputline);en
11、d;insert into sc values(student_sno,course_cno,sc_grade);commit;end_outputline:=插入成功!;return(end_outputline);exception when others thenend_outputline:=插入失败!;return(end_outputline);end;end if;end;-执行:declares_sname varchar2(20):=刘佳;s_cname varchar2(20):=数据库; s_grade number:=50;begindbms_output.put_li
12、ne(fsc_inorup(s_sname,s_cname,s_grade);end;-练习二定义一个包，使其中包括下面功能：1 建立过程，当传入学号和选课门数，首先判断 SC_Number 表是否存在，若不存在则创建该表格（包括学号和选修门数两列），将传入值插入或修改到 SC_Number 表中（该生不存在则插入，若存在则修改其选课门数）（私有过程）2 建立过程（重载），当用户输入学号（或姓名），课程号，成绩，将该信息插入到 SC 表格中，若该课程已经满额，则提示相关信息；若该生已经选择了该课程，则修改该课程的成绩为输入成绩；若该生或该课程不存在，则提示相关错误。插入成功后调用上一个过程将学
13、生选课情况修改。3 建立过程，当用户输入学号，将该生对应的选课信息(SC)，学生基本信息(Student)，SC_Number 中关于该生的信息全部删除,若该生不存在，则给出相关提示。4 建立过程，实现删除 SC_Number 表格的功能。命令窗口: grant create any table to tesuser;程序窗口:create or replace package pk1 is/*插入学生成绩信息；*/procedure sc_inorup (sc_sno in varchar2,sc_cno in varchar2,sc_grade in number,IsSucess out
14、 number);procedure sc_inorup (sc_sname in varchar2,sc_cno in varchar2,sc_grade in number,mess out varchar2);-删除学生信息；procedure delete_student(v_sno varchar2);-删除临时表；procedure drop_sc_number;end;-包体create or replace package body pk1isprocedure inorup_sc_number(v_sno in varchar2,v_count in number)-1isc
15、ount_num number;-标记该生存不存在，1：存在，0：不存在e_sc_number number; -标记 sc_number 表存不存在,1:存在, 0:不存在 beginbeginselect 1 into e_sc_number from tab where tname=SC_NUMBER; exception when no_data_found thene_sc_number:=0; end; if e_sc_number=0 then-sc_number 表不存在execute immediatecreate table sc_number(sno varchar(10
16、) primary key,cnum number（3）);-动态 SQLexecute immediate-保持数据一致性,给 sc_number 表初始化insert into sc_number select sno,count(*) from sc group by sno;commit;end if; beginexecute immediateselect 1 from sc_number where trim(sno)=:1 into count_num using trim(v_sno);-找表中有无该生exception when no_data_found then cou
17、nt_num:=0;when others thendbms_output.put_line(sqlerrm);return; end; if count_num=1 then -有该生beginexecute immediateupdate sc_number set cnum=:1 where trim(sno)=:2 using v_count,trim(v_sno) ;-修改选课门数commit;exception when others then dbms_output.put_line(sqlerrm);return; end; end if; - if count_num=1 i
18、f count_num=0 then -无该生beginexecute immediateinsert into sc_number values(:1,:2) using v_sno,v_count; -插入信息commit;exception when others then dbms_output.put_line(sqlerrm);return; end; end if; end;procedure sc_inorup (sc_sno in varchar2,sc_cno in varchar2,sc_grade in number,IsSucess out number)-2/*Is
19、Suess 0:失败 ,11:修改成功,12：插入成功，2:课程满额,3:该学号不存在 ,4:该课程号不存在*/ iscount_num number;-SC 表中存不存在该记录,1:存在 ,0:不存在n number;-SC 表中选该课程的数目nS number;-COURSE 表中该课程的最大人数i number;-临时变量s_count number;-学生的选课数 beginbeginselect 1 into count_num from sc where sno= trim(sc_sno) and trim(cno)=sc_cno; exception when no_data_f
20、ound then count_num:=0;when others thendbms_output.put_line(sqlerrm);IsSucess:=0;return; end; if count_num=1 then -存在该记录beginupdate sc set grade=sc_grade where trim(sno)=sc_sno and trim(cno)=sc_cno;-修改成绩commit;IsSucess:=11;-修改成功exception when others thenIsSucess:=0;-修改失败return; end; end if;- if coun
21、t_num=1 if count_num=0 then-不存在该记录beginselect snumber into nS from course where trim(cno)=sc_cno; exception when no_data_found thenIsSucess:=4;-没有此课程号return; end; select count(*) into n from sc where trim(cno)=sc_cno; if n=nS thenIsSucess:=2;-选课已满return;else beginselect 1 into i from student where t
22、rim(sno)=sc_sno; exception when no_data_found then IsSucess:=3;-没有此学号return; end; -判断 cno 是否存在； begininsert into sc values(sc_sno,sc_cno,sc_grade); commit;IsSucess:=12;-插入成功select count(*) into s_count from sc where trim(sno)=sc_sno; -找该学号的选课门数 inorup_sc_number(sc_sno,s_count);-调用私有过程 inorup_sc_numb
23、erexception when others thenIsSucess:=0;-插入失败 end; end if; end if;end; procedure sc_inorup (sc_sname in varchar2,sc_cno in varchar2,sc_grade in number,mess out varchar2)-2 重载iscount_num number;-SC 表中存不存在该记录,1:存在,0:不存在n number;-SC 表中选该课程的数目nS number;-COURSE 表中该课程的最大人数student_sno varchar(20);-该学生姓名对应的
24、学号s_count number;-学生的选课数 beginbeginselect 1 into count_num from sc,student where sc.sno=student.sno and trim(cno)=sc_cno and sname=sc_sname ; exception when no_data_found then count_num:=0;when others thenmess:=sqlerrm;return; end; if count_num=1 then -存在该记录 beginupdate sc set grade=sc_grade where s
25、no in (select sc.sno from sc,student where sc.sno=student.sno and sname=sc_sname ) and trim(cno) =sc_cno;-修改成绩commit;mess:=修改成功 !;exception when others thenmess:=修改失败 !; end; end if; if count_num=0 then-不存在该记录beginselect snumber into nS from course where trim(cno)=sc_cno; exception when no_data_foun
26、d thenmess:=没有此课程号 !;return; end; select count(*) into n from sc where trim(cno)=sc_cno; if n=nS thenmess:=选课已满 !;return;else beginselect sno into student_sno from student where sname=sc_sname; exception when no_data_found then mess:=没有此姓名!;return; end; begininsert into sc values(student_sno,sc_cno,
27、sc_grade); commit;mess:=插入成功 !;select count(*) into s_count from sc where trim(sno)=trim(student_sno); -找该学号的选课门数 inorup_sc_number(student_sno,s_count);-调用私有过程 inorup_sc_numberexception when others thenmess:=插入失败 !; end; end if; end if; end;procedure delete_student(v_sno varchar2)-3isi number;-临时变量b
28、eginbeginselect 1 into i from student where trim(sno)=v_sno;-该生存不存在exception when no_data_found thendbms_output.put_line(该生不存在!);return; end; begindelete from sc where trim(sno)=v_sno;-删除 sc 表中的该生信息dbms_output.put_line(sc 表中该生信息已删除!);exception when others thendbms_output.put_line(sqlerrm); end; begi
29、ndelete from student where trim(sno)=v_sno;-删除 student 表中的该生信息dbms_output.put_line(student 表中该生信息已删除!);exception when others thendbms_output.put_line(sqlerrm); end; begin execute immediatedelete from sc_number where trim(sno)=:1 using v_sno;-删除 sc_number 表中的该生信息dbms_output.put_line(sc_number 表中该生信息已
30、删除!);exception when others thendbms_output.put_line(sqlerrm); end; commit;end;procedure drop_sc_number-4ise_sc_number number; beginbeginselect 1 into e_sc_number from tab where tname=SC_NUMBER; -判断 sc_number 表存不存在 exception when no_data_found thene_sc_number:=0; end; if e_sc_number=1 thenexecute imm
31、ediatedrop table sc_number;dbms_output.put_line(sc_number 表已删除!);end if; end;end;测试窗口:declare sucess number;mess varchar(20);beginpk1.sc_inorup(001,c01,98,sucess);-sc 表修改成绩dbms_output.put_line(to_char(sucess);pk1.sc_inorup(003,c03,98,sucess);-sc 表插入记录,sc_number 表修改信息dbms_output.put_line(to_char(suce
32、ss);pk1.sc_inorup(002,c03,90,sucess);-sc 表插入记录,sc_number 表修改信息dbms_output.put_line(to_char(sucess);pk1.sc_inorup(005,c03,90,sucess);-选课已满dbms_output.put_line(to_char(sucess);pk1.sc_inorup(张三,c02,80,mess);-sc 表插入记录,sc_number 表修改信息(重载)dbms_output.put_line(mess);pk1.sc_inorup(张三,c23,80,mess);-无此课程号dbms_output.put_line(mess);pk1.sc_inorup(张,c01,80,mess);-无此姓名dbms_output.put_line(mess);pk1.sc_inorup(张三,c01,80,mess);-sc 表修改成绩dbms_output.put_line(mess);pk1.sc_inorup(李四,c03,80,mess);-sc 表插入记录,sc_number 表插入记录dbms_output.put_line(mess);pk1.delete_student(002);-删除信息pk1.drop_sc_number;-删除 sc_number 表end;
