过程-函数.doc

过程作业评讲练习一：作一存储过程和函数，完成下面的功能：输入姓名，课程名，成绩该过程完成对SC表的插入或修改操作，若插入成功，返回成功信息，若该选课信息已经存在，则修改其成绩为输入的成绩，若遇系统错误，返回错误信息。 过程实现:create or replace procedure sc_inorup (sc_sname in varchar2,sc_cname in varchar2,sc_grade in number,out_msg out varchar2)is count_num number; student_sno varchar(10); course_cno varchar(10);begin begin/*select sno into student_sno from student where sname=sc_sname;exception when no_data_found then student_sno=; out_msg=名字不存在; return; when others then dbms_output.put_line(sqlerrm); return;end;select cno into course_cno from course where cname=sc_cname;exception when no_data_found then course_cno=; out_msg=课程名不存在; return; when others then dbms_output.put_line(sqlerrm); return;end;*/select 1 into count_num from sc,student,course where sc.sno=student.sno and o=o and sname=sc_sname and cname=sc_cname; exception when no_data_found then count_num:=0; when others then dbms_output.put_line(sqlerrm); return; end;if count_num=1 then begin update 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 then dbms_output.put_line(修改失败!);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 then dbms_output.put_line(没有此课程名!); return;end;begininsert into sc values(student_sno,course_cno,sc_grade); commit; dbms_output.put_line(插入成功!);exception when others then dbms_output.put_line(插入失败!);end;end if;- if count_num=0 thenend;-执行:declares_sname varchar2(20):=张三;s_cname varchar2(20):=C语言; s_grade 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 varchar2is count_num number; student_sno char(10); course_cno char(10); end_outputline varchar2(30);begin begin select 1 into count_num from sc,student,course where sc.sno=student.sno and o=o and sname=sc_sname and cname=sc_cname; exception when no_data_found then count_num:=0; when others then end_outputline:=sqlerrm; return(end_outputline);end;if count_num=1 then begin update 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; end_outputline:=修改成功!; return(end_outputline); exception when others then end_outputline:=修改失败!; return(end_outputline); end; end if;if count_num=0 then begin begin select sno into student_sno from student where sname=sc_sname; exception when no_data_found then end_outputline:=没有此姓名!; return(end_outputline); end; begin select cno into course_cno from course where cname=sc_cname; exception when no_data_found then end_outputline:=没有此课程名!; return(end_outputline); end; insert into sc values(student_sno,course_cno,sc_grade); commit; end_outputline:=插入成功!; return(end_outputline); exception when others then end_outputline:=插入失败!; return(end_outputline); end; end if;end;-执行:declares_sname varchar2(20):=刘佳;s_cname varchar2(20):=数据库; s_grade number:=50;begindbms_output.put_line(fsc_inorup(s_sname,s_cname,s_grade);end;-练习二定义一个包，使其中包括下面功能：1 建立过程，当传入学号和选课门数，首先判断SC_Number表是否存在，若不存在则创建该表格（包括学号和选修门数两列）， 将传入值插入或修改到SC_Number表中（该生不存在则插入，若存在则修改其选课门数）（私有过程）2 建立过程（重载），当用户输入学号（或姓名），课程号，成绩，将该信息插入到SC表格中，若该课程已经满额，则提示相关信息；若该生已经选择了该课程，则修改该课程的成绩为输入成绩；若该生或该课程不存在，则提示相关错误。插入成功后调用上一个过程将学生选课情况修改。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 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)-1 iscount_num number;-标记该生存不存在，1：存在，0：不存在e_sc_number number; -标记sc_number表存不存在,1:存在, 0:不存在 begin beginselect 1 into e_sc_number from tab where tname=SC_NUMBER; exception when no_data_found then e_sc_number:=0; end; if e_sc_number=0 then-sc_number表不存在 execute immediate create table sc_number(sno varchar(10) 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 immediate select 1 from sc_number where trim(sno)=:1 into count_num using trim(v_sno);-找表中有无该生exception when no_data_found then count_num:=0; when others then dbms_output.put_line(sqlerrm); return; end; if count_num=1 then -有该生 begin execute immediate update 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 if count_num=0 then -无该生 begin execute immediate insert 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/*IsSuess 0:失败,11:修改成功,12：插入成功，2:课程满额,3:该学号不存在,4:该课程号不存在*/ is count_num number;-SC表中存不存在该记录,1:存在,0:不存在 n number;-SC表中选该课程的数目 nS number;-COURSE表中该课程的最大人数 i number;-临时变量 s_count number;-学生的选课数 begin begin select 1 into count_num from sc where sno= trim(sc_sno) and trim(cno)=sc_cno; exception when no_data_found then count_num:=0; when others then dbms_output.put_line(sqlerrm); IsSucess:=0; return; end; if count_num=1 then -存在该记录 begin update sc set grade=sc_grade where trim(sno)=sc_sno and trim(cno)=sc_cno;-修改成绩 commit; IsSucess:=11;-修改成功 exception when others then IsSucess:=0;-修改失败 return; end; end if;- if count_num=1 if count_num=0 then-不存在该记录begin select snumber into nS from course where trim(cno)=sc_cno; exception when no_data_found then IsSucess:=4;-没有此课程号 return; end; select count(*) into n from sc where trim(cno)=sc_cno; if n=nS then IsSucess:=2;-选课已满 return; else begin select 1 into i from student where trim(sno)=sc_sno; exception when no_data_found then IsSucess:=3;-没有此学号 return; end; -判断cno是否存在； begin insert 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_number exception when others then IsSucess:=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重载 is count_num number;-SC表中存不存在该记录,1:存在,0:不存在 n number;-SC表中选该课程的数目 nS number;-COURSE表中该课程的最大人数 student_sno varchar(20);-该学生姓名对应的学号 s_count number;-学生的选课数 begin begin select 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 then mess:=sqlerrm; return; end; if count_num=1 then -存在该记录 begin update sc set grade=sc_grade where sno 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 then mess:=修改失败!; end; end if; if count_num=0 then-不存在该记录 begin select snumber into nS from course where trim(cno)=sc_cno; exception when no_data_found then mess:=没有此课程号!; return; end; select count(*) into n from sc where trim(cno)=sc_cno; if n=nS then mess:=选课已满!; return; else begin select sno into student_sno from student where sname=sc_sname; exception when no_data_found then mess:=没有此姓名!; return; end; begin insert into sc values(student_sno,sc_cno,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_number exception when others then mess:=插入失败!; end; end if; end if; end;procedure delete_student(v_sno varchar2)-3is i number;-临时变量 begin begin select 1 into i from student where trim(sno)=v_sno;-该生存不存在 exception when no_data_found then dbms_output.put_line(该生不存在!); return; end; begin delete from sc where trim(sno)=v_sno;-删除sc表中的该生信息 dbms_output.put_line(sc表中该生信息已删除!); exception when others then dbms_output.put_line(sqlerrm); end; begin delete from student where trim(sno)=v_sno;-删除student表中的该生信息 dbms_output.put_line(student表中该生信息已删除!); exception when others then dbms_output.put_line(sqlerrm); end; begin execute immediate delete from sc_number where trim(sno)=:1 using v_sno;-删除sc_number表中的该生信息 dbms_output.put_line(sc_number表中该生信息已删除!); exception when others then dbms_output.put_line(sqlerrm); end; commit;end; procedure drop_sc_number-4 is e_sc_number number; begin begin select 1 into e_sc_number from tab where tname=S