過程-函數(shù).doc

過程作業(yè)評講練習一：作一存儲過程和函數(shù)，完成下面的功能：輸入姓名，課程名，成績該過程完成對SC表的插入或修改操作，若插入成功，返回成功信息，若該選課信息已經(jīng)存在，則修改其成績?yōu)檩斎氲某煽?，若遇系統(tǒng)錯誤，返回錯誤信息。 過程實現(xiàn):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;-執(zhí)行: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;-函數(shù)實現(xiàn):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;-執(zhí)行:declares_sname varchar2(20):=劉佳;s_cname varchar2(20):=數(shù)據(jù)庫; s_grade number:=50;begindbms_output.put_line(fsc_inorup(s_sname,s_cname,s_grade);end;-練習二定義一個包，使其中包括下面功能：1 建立過程，當傳入學號和選課門數(shù)，首先判斷SC_Number表是否存在，若不存在則創(chuàng)建該表格（包括學號和選修門數(shù)兩列）， 將傳入值插入或修改到SC_Number表中（該生不存在則插入，若存在則修改其選課門數(shù)）（私有過程）2 建立過程（重載），當用戶輸入學號（或姓名），課程號，成績，將該信息插入到SC表格中，若該課程已經(jīng)滿額，則提示相關(guān)信息；若該生已經(jīng)選擇了該課程，則修改該課程的成績?yōu)檩斎氤煽?；若該生或該課程不存在，則提示相關(guān)錯誤。插入成功后調(diào)用上一個過程將學生選課情況修改。3 建立過程，當用戶輸入學號，將該生對應(yīng)的選課信息(SC)，學生基本信息(Student)，SC_Number中關(guān)于該生的信息全部刪除, 若該生不存在，則給出相關(guān)提示。4 建立過程，實現(xiàn)刪除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）);-動態(tài)SQLexecute immediate-保持數(shù)據(jù)一致性,給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) ;-修改選課門數(shù) 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表中選該課程的數(shù)目 nS number;-COURSE表中該課程的最大人數(shù) i number;-臨時變量 s_count number;-學生的選課數(shù) 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; -找該學號的選課門數(shù) inorup_sc_number(sc_sno,s_count);-調(diào)用私有過程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表中選該課程的數(shù)目 nS number;-COURSE表中該課程的最大人數(shù) student_sno varchar(20);-該學生姓名對應(yīng)的學號 s_count number;-學生的選課數(shù) 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); -找該學號的選課門數(shù) inorup_sc_number(student_sno,s_count);-調(diào)用私有過程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