中南大學(xué)數(shù)據(jù)庫原理實(shí)驗(yàn)報(bào)告學(xué) 院： 信息科學(xué)與工程學(xué)院 班 級(jí)： 電子1102 學(xué) 號(hào)： 0909101329 姓 名： 毛艾力 指導(dǎo)老師： 張祖平 完成時(shí)間： 2021.4.27 實(shí)驗(yàn)一、熟悉ORALCE環(huán)境并練習(xí)SQL的定義一、目的與要求本實(shí)驗(yàn)主要是熟悉ORACLE的運(yùn)行環(huán)境，在CLIENT端進(jìn)行聯(lián)接設(shè)置與效勞器聯(lián)通，進(jìn)入ORACLE的SQL*PLUS的操作環(huán)境，進(jìn)一步建立表格，并考慮主鍵,外部鍵,值約束。二、操作環(huán)境硬件：主頻2GHz以上效勞器內(nèi)存2GB以上、硬件空閑2.2GB以上，主頻1GHz以上微機(jī)，內(nèi)存1GB以上。軟件：WINDOWS XP/2000/2003/或win7/2021等，如操作系統(tǒng)是 SERVER版 可安裝ORACLE/9i/10g/11g/12C FOR NT/WINDOWS(注意在32位與64位的區(qū)別，可選企業(yè)版) 如果Windows非server如XP/win7等，安裝時(shí)請(qǐng)選擇個(gè)人版(PERSONAL)三、實(shí)驗(yàn)內(nèi)容1.上機(jī)步驟在實(shí)驗(yàn)室上機(jī)時(shí),一般安裝時(shí)管理員已設(shè)置好.先找到windows中的oracle效勞一般為OracleServiceORCL，如下列圖1,啟動(dòng)此效勞,再找到sql*plus所在的位置一般桌面上有如下列圖2的程序組有,運(yùn)行即可進(jìn)行登錄界面.圖1 Oracle在計(jì)算機(jī)系統(tǒng)中的效勞圖2稱Sql*plus在系統(tǒng)程序組中的位置實(shí)驗(yàn)室現(xiàn)有環(huán)境操作步驟：通過桌面我的電腦找到管理效勞的入口，如圖3，或者通過資源管理器找到管理。圖3 通過桌面我的電腦找到管理效勞的入口然后找到效勞，如圖4。圖4 系統(tǒng)中的效勞再找啟動(dòng)oracle的效勞如圖1：（1） 從桌面或在程序中圖2執(zhí)行SQL*PLUS（2） 輸入：system/manager（3） 在SQL>狀態(tài)輸入建立用戶命令:create user username identified by password;其中username與password不要數(shù)字開頭，用戶名username中按規(guī)那么加班級(jí)學(xué)號(hào)后綴如：U_11221表示1班學(xué)號(hào)尾數(shù)為1221號(hào)的用戶。（4） 給用戶授權(quán)：grant resource,connect to username;(5) 連接用戶：connect username/password在SQL>就可以建表等操作了2.根據(jù)要求建立如下表定義根本表格book_?書與reader_?(讀者及l(fā)end_?(借閱，關(guān)系模式如下：book_?(bno char(10) Primary key, btitle VARCHAR2(32),author VARCHAR2(32),publisher varchar2(32)，price number(7,2) <10000reader_?(RNO char(10) primary key, RNAME VARCHAR2(32), sex VARCHAR2(4) 男,女,其它,其他),age number(3)，tel varchar2(13),e-mail varchar2(32) 檢查郵箱格式的合法性)lend_?(RNO char(10) FOREIGN key, bno char(10) FOREIGN key, BORROWDATE DATE, RETURNDATE/*應(yīng)還書日期*/ DATE, realRetDate /*實(shí)際還書日期*/ date,，PRIMARY KEY(RNO, BNO, BORROWDate),check(RETURNDATE- BORROWDATE<=100)注意表的命名規(guī)那么3.插入樣本數(shù)據(jù)每個(gè)表至少插入10條以上的記錄，要求在reader表中要有自己的信息，可以學(xué)號(hào)為讀者號(hào)，可以參考教材上的數(shù)據(jù)。四、思考題：（1） 能否任意改變表的名稱？ 不能任意改變表名稱；雖然可以改變表名稱但任意改變表名稱后會(huì)讓其他與之相關(guān)的表中出現(xiàn)錯(cuò)誤。（2） 能否先刪除reader表或book表？不能，要從后往前刪；因?yàn)閞eader中的rno和book中的bno是lend表中的外鍵，要先刪除lend表，再刪book表和reader表。3能否改變book表中BNO類型與長(zhǎng)度，或改變READER表中Rno的類型與長(zhǎng)度。不能，會(huì)出現(xiàn)列類型與引用的列類型不兼容錯(cuò)誤；因?yàn)閘end表中引用到了bno和rno。（4） 測(cè)試主鍵，外鍵與值的約束條件。一個(gè)表中主鍵的值不能相同，即book_21606中bno 0119320211只能有一個(gè)，不然會(huì)違反唯一性約束；外鍵的值只能是所對(duì)應(yīng)主鍵有的值，如insert into lend_21606values('0909112313','0119320210',TO_DATE('2021-03-20','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-03-30','yyyy-MM-dd');由于reader中BNO中未添加0909112313將會(huì)出現(xiàn)五、實(shí)驗(yàn)結(jié)論（1） 將VARCHAR2(n) 寫成varchar2(n )出現(xiàn)提示缺失右括號(hào)錯(cuò)誤；有的類型可以小寫char(n )有的那么必須大寫VARCHAR2(n)；（2） 定義外鍵時(shí)references少寫s出現(xiàn)提示缺失右括號(hào)錯(cuò)誤；（3） 日期不會(huì)輸入，通過TO_DATE進(jìn)行轉(zhuǎn)換。（4） 熟悉了oracle的使用。六、源代碼：Create user U_21329 identified by maoaili;Grant resource,connect to u_21329;Connect u_21329/maoaili;Create tablebook_21329(bno char(10) Primary key, btitle VARCHAR2(32),author VARCHAR2(32),publisher varchar2(32)，price number(7,2) check (price between 0 and 10000);Create tablereader_21329 (RNO char(10) primary key, RNAME VARCHAR2(32), sex VARCHAR2(4) check (sex in ('男','女','其他','其它'),age number(3)，tel varchar2(13),e_mail varchar2(32) check(e_mail like '%_%');Ceate tablelend_21329(RNO char(10) ,FOREIGN key(RNO) references reader_21329(RNO),bno char(10) ,FOREIGN key(bno) references book_21329(bno),BORROWDATE DATE, RETURNDATE/*應(yīng)還書日期*/ DATE, realRetDate /*實(shí)際還書日期*/ date,PRIMARY KEY(RNO, BNO, BORROWDate),check(RETURNDATE- BORROWDATE<=100);insert into book_21329values('0119320211','昆曲的源頭','李莉','遠(yuǎn)方出版社','38');insert into book_21329values('0119320212','鐵道學(xué)院的歷史開展','張三豐','鐵道出版社','25');insert into book_21329values('0119320213','英雄的意義','諾言','上海文藝出版社','30');insert into book_21329values('0119320214','乾隆的廚房','洪七公','山西文藝出版社','38');insert into book_21329values('0119320215','無間道','李明才','光明日?qǐng)?bào)出版社','45');insert into book_21329values('0119320216','數(shù)據(jù)庫應(yīng)用','孫紅雷','郵政出版社','33');insert into book_21329values('0119320217','故事會(huì)','范冰冰','人民出版社','38');insert into book_21329values('0119320218','人類群星閃耀的時(shí)刻','賈克斯','自然出版社','42');insert into book_21329values('0119320219','紅樓夢(mèng)','賈寶玉','人民郵政出版社','30');insert into book_21329values('0119320210','史蒂夫·喬布斯傳','史蒂夫·喬布斯','中信出版社','22');insert into book_21329values('0119320211','數(shù)據(jù)庫','李假設(shè)彤','科學(xué)出版社','38');insert into book_21329values('0119320212','數(shù)據(jù)庫原理及應(yīng)用','張祖平','中南大學(xué)出版社','38');insert into reader_21329values('0909101329','毛艾力','男','22','15116396465','416783747qq ');insert into reader_21329values('0909101328','張信哲','男','22','15111063261','37562135qq ');insert into reader_21329values('0909101327','阿朵','男','23','15111063263','375621353163 ');insert into reader_21329values('0909101326','陳冠希','女','21','15111063221','392677513163 ');insert into reader_21329values('0909101325','泰勒·斯威夫特','男','21','13111521623','3729863254qq ');insert into reader_21329values('0909101324','王假設(shè)琳','女','21','13111521733','3326163254qq ');insert into reader_21329values('0909101323','賈乃亮','女','22','13111521651','376211369qq ');insert into reader_21329values('0909101322','王假設(shè)冰','女','22','13711521651','376211395qq ');insert into reader_21329values('0909101321','李宗盛','女','22','18711521651','376211365qq ');insert into reader_21329values('0909101320','王大錘','男','22','18711526135','376211262qq ');insert into reader_21329values('0909101319','李正四','男','23','15773166137','376211262qq ');insert into lend_21329values('0909101329','0119320211',TO_DATE('2021-12-22','yyyy-MM-dd'),TO_DATE('2021-03-22','yyyy-MM-dd'),TO_DATE('2021-03-12','yyyy-MM-dd');insert into lend_21329values('0909101329','0119320212',TO_DATE('2021-12-22','yyyy-MM-dd'),TO_DATE('2021-03-22','yyyy-MM-dd'),TO_DATE('2021-03-25','yyyy-MM-dd');insert into lend_21329values('0909101328','0119320211',TO_DATE('2021-12-22','yyyy-MM-dd'),TO_DATE('2021-03-22','yyyy-MM-dd'),TO_DATE('2021-03-12','yyyy-MM-dd');insert into lend_21329values('0909101328','0119320212',TO_DATE('2021-12-22','yyyy-MM-dd'),TO_DATE('2021-03-22','yyyy-MM-dd'),TO_DATE('2021-04-12','yyyy-MM-dd');insert into lend_21329values('0909101327','0119320213',TO_DATE('2021-03-16','yyyy-MM-dd'),TO_DATE('2021-05-21','yyyy-MM-dd'),TO_DATE('2021-03-27','yyyy-MM-dd');insert into lend_21329values('0909101327','0119320214',TO_DATE('2021-03-17','yyyy-MM-dd'),TO_DATE('2021-05-22','yyyy-MM-dd'),TO_DATE('2021-03-29','yyyy-MM-dd');insert into lend_21329values('0909101326','0119320214',TO_DATE('2021-03-17','yyyy-MM-dd'),TO_DATE('2021-05-22','yyyy-MM-dd'),TO_DATE('2021-03-29','yyyy-MM-dd');insert into lend_21329values('0909101326','0119320212',TO_DATE('2021-03-20','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-03-30','yyyy-MM-dd');insert into lend_21329values('0909101325','0119320219',TO_DATE('2021-03-20','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-03-30','yyyy-MM-dd');insert into lend_21329values('0909101325','0119320217',TO_DATE('2021-03-20','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-03-30','yyyy-MM-dd');insert into lend_21329values('0909101324','0119320216',TO_DATE('2021-03-20','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-03-30','yyyy-MM-dd');insert into lend_21329values('0909101324','0119320216',TO_DATE('2021-03-20','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-03-30','yyyy-MM-dd');insert into lend_21329values('0909101323','0119320210',TO_DATE('2021-03-20','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-03-30','yyyy-MM-dd');insert into lend_21329values('0909101323','0119320211',TO_DATE('2021-03-23','yyyy-MM-dd'),TO_DATE('2021-05-23','yyyy-MM-dd'),TO_DATE('2021-04-02','yyyy-MM-dd');insert into lend_21329values('0909101322','0119320211',TO_DATE('2021-03-25','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-04-01','yyyy-MM-dd');insert into lend_21329values('0909101322','0119320211',TO_DATE('2021-03-25','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-04-01','yyyy-MM-dd');insert into lend_21329values('0909101321','0119320217',TO_DATE('2021-02-25','yyyy-MM-dd'),TO_DATE('2021-04-25','yyyy-MM-dd'),TO_DATE('2021-04-03','yyyy-MM-dd');insert into lend_21329values('0909101321','0119320219',TO_DATE('2021-02-25','yyyy-MM-dd'),TO_DATE('2021-04-25','yyyy-MM-dd'),TO_DATE('2021-04-03','yyyy-MM-dd');insert into lend_21329values('0909101320','0119320217',TO_DATE('2021-02-23','yyyy-MM-dd'),TO_DATE('2021-04-23','yyyy-MM-dd'),TO_DATE('2021-04-02','yyyy-MM-dd');insert into lend_21329values('0909101320','0119320219',TO_DATE('2021-02-23','yyyy-MM-dd'),TO_DATE('2021-04-23','yyyy-MM-dd'),TO_DATE('2021-05-02','yyyy-MM-dd');insert into lend_21329values('0909101319','0119320211',TO_DATE('2021-03-25','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-05-27','yyyy-MM-dd');實(shí)驗(yàn)二、數(shù)據(jù)更新操作一、目的與要求本實(shí)驗(yàn)主要是熟悉在SQL*PLUS環(huán)境下進(jìn)行數(shù)據(jù)記錄查詢、更新與刪除，其中查詢考慮簡(jiǎn)單查詢,復(fù)雜查詢,嵌套查詢。二、操作環(huán)境同實(shí)驗(yàn)一。三、實(shí)驗(yàn)內(nèi)容1查詢所有書的記錄2查詢所有讀者的記錄，并按讀者姓名升序，讀者號(hào)降序排列3.查詢有自己姓名的記錄4.查詢李正四的RNO，RNAME，TEL，E-MAIL 5.查詢借閱過數(shù)據(jù)庫書的所有讀者記錄6.查詢借閱書的本數(shù)與人數(shù)，如1本，1000人，2本200人，,100本，10人等7.查詢李正四的RNO，RNAME，BNO， BTITLE，借書本數(shù)8.在lend表中增加是否過期IsOverDue字段,并求出相應(yīng)的值考慮應(yīng)還書日期與實(shí)際還書日期的關(guān)系用alter 與update9.刪除中南出版社的所有圖書及相應(yīng)的借書記錄四、 源代碼select * from book_21329;select * from reader_21329order by RNAME ASC ,RNO DESC;select reader_21329.RNO,RNAME,book_21329.bno,btitle,lend_21329.BORROWDATE from book_21329,reader_21329,lend_21329where reader_21329.RNAME='毛艾力' and book_21329.bno=lend_21329.bno and reader_21329.RNO=lend_21329.RNO;select RNO,RNAME,tel,e_mail from reader_21329where RNAME='李正四'select book_21329.btitle,reader_21329.RNO,RNAME,tel,e_mail,lend_21329.BORROWDATE from book_21329,reader_21329,lend_21329where book_21329.btitle like '%數(shù)據(jù)庫%' and book_21329.bno=lend_21329.bno and reader_21329.RNO=lend_21329.RNOorder by RNAME ASC ,RNO desc;grant create view to U_21329; /*授權(quán)創(chuàng)立視圖*/create VIEW count_21329 ASselect count(lend_21329.bno) as c_count,lend_21329.RNO as c_RNOfrom lend_21329group by lend_21329.RNO;select c_count as 本數(shù),count(c_RNO) 人數(shù)from count_21329group by c_count;select reader_21329.RNO,RNAME,book_21329.bno,btitle,BORROWDATEfrom book_21329,reader_21329,lend_21329where book_21329.bno=lend_21329.bno and reader_21329.RNO=lend_21329.RNO and reader_21329.RNAME='李正四'alter table lend_21329add isoverdue char(2) check(isoverdue in('是','否');update lend_21329set isoverdue='是'where realRetDate>RETURNDATE;update lend_21329set isoverdue='否'where realretdate<returndate;select * from book_21329where publisher='中南大學(xué)出版社'select * from lend_21329where bno in(select bno from book_21329where publisher='中南大學(xué)出版社');delete from lend_21329where bno in(select bno from book_21329where publisher='中南大學(xué)出版社');deletefrom book_21329where publisher='中南大學(xué)出版社'select * from book_21329where publisher='中南大學(xué)出版社'select * from lend_21329where bno in(select bno from book_21329where publisher='中南大學(xué)出版社');實(shí)驗(yàn)三、視圖及權(quán)限控制一、目的與要求本實(shí)驗(yàn)主要是熟悉在SQL*PLUS環(huán)境下進(jìn)行數(shù)據(jù)視圖及權(quán)限控制的操作。二、操作環(huán)境同實(shí)驗(yàn)一。三、實(shí)驗(yàn)內(nèi)容綜合設(shè)計(jì)與實(shí)現(xiàn)批處理實(shí)現(xiàn)為每個(gè)讀者建立用戶，并授予connect角色，建立視圖并給相應(yīng)查詢權(quán)限，讓每個(gè)員工用自己的用戶登錄能查詢到自己的整個(gè)信息與相應(yīng)的借書信息。 四、實(shí)驗(yàn)報(bào)告要求1實(shí)驗(yàn)內(nèi)容2調(diào)試后的各命令清單3寫出在實(shí)驗(yàn)過程中遇到的問題及解決方法4要求字跡端正、條理清晰、概念正確五、 實(shí)驗(yàn)源代碼grant create user to U_21329;grant connect,resource to U_21329 with admin option;select 'create user u_'|substr(reader_21329.RNO,7,4)|' identified by p'|substr(reader_21329.RNO,7,4)|'' from reader_21329;create user u_1329 identified by p1329;create user u_1328 identified by p1328;create user u_1327 identified by p1327;create user u_1326 identified by p1326;create user u_1325 identified by p1325;create user u_1324 identified by p1324;create user u_1323 identified by p1323;create user u_1322 identified by p1322;create user u_1321 identified by p1321;create user u_1320 identified by p1320;create user u_1319 identified by p1319;select 'grant connect,resource to u_'|substr(reader_21329.RNO,7,4)|'' from reader_21329;grant connect,resource to u_1329;grant connect,resource to u_1328;grant connect,resource to u_1327;grant connect,resource to u_1326;grant connect,resource to u_1325;grant connect,resource to u_1324;grant connect,resource to u_1323;grant connect,resource to u_1322;grant connect,resource to u_1321;grant connect,resource to u_1320;grant connect,resource to u_1319;create view v_reader as select reader_21329.RNO,reader_21329.RNAME,book_21329.bno,book_21329.btitle,lend_21329.BORROWDATEfrom book_21329,reader_21329,lend_21329where substr(reader_21329.RNO,7,4)=substr(user,3,4) and lend_21329.RNO=reader_21329.RNO and book_21329.bno=lend_21329.bno;select 'grant select on v_reader to u_'|substr(reader_21329.RNO,7,4)|''from reader_21329;grant select on v_reader to u_1329;grant select on v_reader to u_1328;grant select on v_reader to u_1327;grant select on v_reader to u_1326;grant select on v_reader to u_1325;grant select on v_reader to u_1324;grant select on v_reader to u_1323;grant select on v_reader to u_1322;grant select on v_reader to u_1321;grant select on v_reader to u_1320;grant select on v_reader to u_1319;conn u_1329/p1329;select * from U_21329.v_reader;conn u_1328/p1328;select * from U_21329.v_reader;create table storebook_21329(bno char(10) references U_21329.book_21329(bno),sumnum int,remain int);insert into storebook_21329values('0119320211','5','3');insert into storebook_21329values('0119320212','3','3');insert into storebook_21329values('0119320213','3','2');insert into storebook_21329values('0119320214','5','3');insert into storebook_21329values('0119320215','3','3');insert into storebook_21329values('0119320216','5','3');insert into storebook_21329values('0119320217','3','1');insert into storebook_21329values('0119320218','3','3');insert into storebook_21329values('0119320219','5','1');insert into storebook_21329values('0119320210','3','2');insert into storebook_21329values('0119320211','5','1');insert into storebook_21329values('0119320212','5','2');實(shí)驗(yàn)四、數(shù)據(jù)庫觸發(fā)器一、目的與要求本實(shí)驗(yàn)主要是熟悉在SQL*PLUS環(huán)境下數(shù)據(jù)庫觸發(fā)器的建立及作用，了解大型數(shù)據(jù)庫編程。二、操作環(huán)境同實(shí)驗(yàn)一。三、實(shí)驗(yàn)內(nèi)容1先建立表storebook_?(bno,sumnum /*總數(shù)*/,remain/*剩余數(shù)*/)2建立觸發(fā)器滿足如下需求：插入刪除或修改lend_?表中的記錄時(shí)，將表storebook中的remain /*剩余數(shù)*/進(jìn)行相應(yīng)的處理，如插入時(shí)增減少相應(yīng)的一本，刪除時(shí)將增加相應(yīng)的一本等。如果remail<=0時(shí)，提示不能再借書。4. 測(cè)試與調(diào)試觸發(fā)器四、實(shí)驗(yàn)報(bào)告要求1實(shí)驗(yàn)內(nèi)容2調(diào)試后的程序清單3寫出在實(shí)驗(yàn)過程中遇到的問題及解決方法4要求字跡端正、條理清晰、概念正確五、實(shí)驗(yàn)源代碼CREATE or REPLACE TRIGGER tri_del_lendbefore delete or insert on lend_21329 /*借書表增加或刪除時(shí)觸發(fā)*/FOR EACH ROW /*每行發(fā)生變化時(shí)觸發(fā)1次*/BEGIN if inserting then update storebook_21329 set remain=remain-1 /*每借出一本書時(shí)，庫存減1*/ where bno=:new.bno; end if; if deleting then update storebook_21329 set remain=remain+1 where bno=:old.bno; end if;end;/*CREATE or REPLACE TRIGGER tri_del_lendbefore delete or insert on lend_21329FOR EACH ROWdeclare remain_now;remain_now int;BEGIN if inserting then update storebook_21329 set remain=remain-1 where bno=:new.bno; end if; if deleting then select remain into remain_now from storebook_21329 where bno=xxy_bno; if remain_now<=0 then raise_application_error( -20000,' 剩余0本，無法借書'); end if; update storebook_21329 set remain=remain+1 where bno=:old.bno; end if;end;*/*觸發(fā)器2*/CREATE or REPLACE TRIGGER tri_cal_lendbefore update ON storebook_21329FOR EACH ROWBEGIN if :new.remain<0 then raise_application_error( -20000,' 剩余0本，無法借書'); end if;end;insert into lend_21329 /*實(shí)例添加數(shù)據(jù)*/values('0909101318','0119320212',TO_DATE('2021-03-20','yyyy-MM-dd'),TO_DATE('2021-05-25','yyyy-MM-dd'),TO_DATE('2021-03-30','yyyy-MM-dd');delete from lend_21329where RNO='0909101320' and bno='0119320217'select * from storebook_21329; 實(shí)驗(yàn)五、數(shù)據(jù)庫過程一、目的與要求本實(shí)驗(yàn)主要是熟悉在SQL*PLUS環(huán)境下數(shù)據(jù)庫過程的建立及作用，了解大型數(shù)據(jù)庫編程。二、操作環(huán)境同實(shí)驗(yàn)一。三、實(shí)驗(yàn)內(nèi)容1先建立表SumLendBook_?(rno,sumnum /*總數(shù)*/,sumoverdue/*總過期數(shù)*/)2設(shè)計(jì)并調(diào)試過程1分讀者編號(hào)計(jì)算，sumnum /*總數(shù)*/,sumoverdue/*總過期數(shù)*/，如建立Pro_sumlend_?過程，運(yùn)行時(shí)：Pro Sumlend_?(0909110112);運(yùn)行結(jié)果為：0909110112顯示讀者的相應(yīng)統(tǒng)計(jì)數(shù)值，并存入表SumLendBook中四、實(shí)驗(yàn)報(bào)告要求1實(shí)驗(yàn)內(nèi)容2調(diào)試后的程序清單3寫出在實(shí)驗(yàn)過程中遇到的問題及解決方法4要求字跡端正、條理清晰、概念正確五、 實(shí)驗(yàn)源代碼/*創(chuàng)立sumlendbook表*/create table SumLendBook_21329(RNO char(10) references U_21329.reader_21329(RNO),sumnum number,sumoverdue number);/*創(chuàng)立過程*/create or replace procedure Pro_sumlend_21329(s_RNO in varchar)isvin_RNO char(10);v_RNO char(10);v_sumnum number;v_sumoverdue number;begin vin_RNO :='0'|s_RNO; select count(lend_21329.bno) into v_sumnum from lend_21329 where RNO=vin_RNO; select count(lend_21329.bno) into v_sumoverdue from lend_21329 where lend_21329.isoverdue='是' and RNO=vin_RNO;insert into SumLendBook_21329values(vin_RNO,v_sumnum,v_sumoverdue);DBMS_OUTPUT.put_line('學(xué)號(hào)0'|vin_RNO|'總借書數(shù)'|v_sumnum|'過期數(shù)'|v_sumoverdue); /*顯示輸出*/exception /*沒數(shù)據(jù)時(shí)*/ when no_data_found then DBMS_OUTPUT.put_line('未找到數(shù)據(jù)！');end Pro_sumlend_21329;execute Pro_sumlend_21329(0909101329)select reader_21329.RNO,book_21329.bno,lend_21329.BORROWDATE,RETURNdatefrom lend_21329,book_21329,reader_21329 where reader_21329.RNO(+)=lend_21329.RNO and lend_21329.bno=book_21329.bnoorder by reader_21329.RNO;