數(shù)據(jù)庫原理與應(yīng)用（MySQL版） 第7章 SQL擴展編程 習(xí)題答案

第7章SQL擴展編程習(xí)題參考答案選擇題1．下列不屬于存儲過程作用的是A．能夠根據(jù)數(shù)據(jù)更改操作自動執(zhí)行B．能夠?qū)崿F(xiàn)模塊化程序設(shè)計C．可以減少網(wǎng)絡(luò)流量D．可作為安全機制使用答案：A2．下列有關(guān)存儲過程的敘述，錯誤的是A．MySQL允許在存儲過程創(chuàng)建時引用一個不存在的對象B．存儲過程可以帶多個輸入?yún)?shù)，也可以帶多個輸出參數(shù)C．使用存儲過程可以減少網(wǎng)絡(luò)流量D．在一個存儲過程中不可以調(diào)用其他存儲過程答案：D3．下列不能引發(fā)觸發(fā)器執(zhí)行的操作是A．INSERTB．DELETEC．SELECTD．UPDATE答案：C4．MySQL為觸發(fā)器創(chuàng)建的兩個臨時表是A．max

和

minB．a(chǎn)vg

和

sumC．int

和

charD．old

和

new答案：D5．下列說法錯誤的是A．觸發(fā)器觸發(fā)時機有BEFORE、AFTER兩種B．對于同一張數(shù)據(jù)表，只能定義一個觸發(fā)器C．由INSERT操作引發(fā)的觸發(fā)器，NEW臨時表用來保存新插入的數(shù)據(jù)D．OLD臨時表中的數(shù)據(jù)是只讀的，不能被更新答案：B二、簡答題存儲過程的作用是什么？答：（1）允許模塊化程序設(shè)計（2）改善性能（3）減少網(wǎng)絡(luò)流量（4）可作為安全機制使用存儲過程和函數(shù)有哪些共同點和不同點？答：相同點：都是數(shù)據(jù)庫系統(tǒng)中一組為了完成特定功能的SQL語句塊，它將常用或復(fù)雜的工作預(yù)先用SQL語句編寫腳本并指定名稱存儲在數(shù)據(jù)庫服務(wù)器中，可以包含控制語句，有很強的靈活性，可以完成復(fù)雜的數(shù)據(jù)操作功能。不同點：（1）自定義函數(shù)不能有輸出參數(shù)，這是因為自定義函數(shù)自身就是輸出參數(shù)；而存儲過程可以有輸出參數(shù)。（2）自定義函數(shù)的函數(shù)體中必須包含一條RETURN語句，而存儲過程不需要。（3）自定義函數(shù)是在SELECT語句中調(diào)用執(zhí)行的，而存儲過程是通過CALL語句調(diào)用執(zhí)行的。存儲過程的參數(shù)有哪幾種形式？答：in：輸入?yún)?shù)(默認(rèn)情況下為in參數(shù))，該參數(shù)的值在調(diào)用時指定。out：輸出參數(shù)，存儲過程運行過程中可以對該類參數(shù)賦值，起到將計算結(jié)果返回給調(diào)用程序的作用。inout：既是輸入?yún)?shù)，又是輸出參數(shù)，該參數(shù)的值可在調(diào)用時指定，又可以將計算結(jié)果返回給調(diào)用程序。存儲過程和函數(shù)如何將結(jié)果返回給調(diào)用者？答：存儲過程：通過OUT或INOUT參數(shù)返回結(jié)果。函數(shù)：通過RETURN語句返回單個值。什么是觸發(fā)器？觸發(fā)器和存儲過程主要區(qū)別是什么？答：觸發(fā)器是存儲數(shù)據(jù)庫中的一段完成特定功能的SQL語句集合。觸發(fā)器和存儲過程主要區(qū)別是觸發(fā)器是由對數(shù)據(jù)進行的更改操作觸發(fā)自動執(zhí)行，這些操作包括INSERT、UPDATE、DELETE等。觸發(fā)器的主要作用是什么？答：當(dāng)對數(shù)據(jù)表中的數(shù)據(jù)執(zhí)行插入、更新和刪除操作，需要自動執(zhí)行一些數(shù)據(jù)庫邏輯時，可以使用觸發(fā)器來實現(xiàn)。觸發(fā)器通常用于保證業(yè)務(wù)規(guī)則和復(fù)雜數(shù)據(jù)完整性。三、編程題請根據(jù)books、students和borrow表，編寫實現(xiàn)下列要求的代碼。定義變量num，統(tǒng)計所有圖書的平均價格，將值賦給變量num并顯示num的值。答：--定義變量并計算平均價格SET@num=(SELECTAVG(price)FROMbooks);--顯示變量值SELECT@numASaverage_price;定義存儲過程，輸入?yún)?shù)是圖書類型，求該類型的圖書總數(shù)量，寫出定義語句和調(diào)用語句。答：--定義存儲過程DELIMITER//CREATEPROCEDUREGetBookCountByType(INbook_typeVARCHAR(50))BEGINSELECTCOUNT(*)AStotal_countFROMbooksWHEREtype=book_type;END//DELIMITER;--調(diào)用存儲過程CALLGetBookCountByType('小說');定義存儲過程，輸入?yún)?shù)是出版社、出版年份，輸出參數(shù)是圖書數(shù)量，統(tǒng)計指定年份該出版社出版的圖書數(shù)量，寫出存儲過程定義語句和調(diào)用語句。答：--定義存儲過程DELIMITER//CREATEPROCEDUREGetBookCountByPublisherAndYear(INpublisher_nameVARCHAR(100),INpublish_yearINT,OUTbook_countINT)BEGINSELECTCOUNT(*)INTObook_countFROMbooksWHEREpress=publisher_nameANDYEAR(publish_date)=publish_year;END//DELIMITER;--調(diào)用存儲過程CALLGetBookCountByPublisherAndYear('清華大學(xué)出版社',2022,@count);SELECT@countASbook_count;定義函數(shù)，輸入?yún)?shù)是出生日期，返回值是學(xué)生年齡，寫出函數(shù)定義語句和在SELECT語句調(diào)用此函數(shù)查詢學(xué)生的姓名、年齡的語句。答：--定義函數(shù)DELIMITER//CREATEFUNCTIONCalculateAge(birth_dateDATE)RETURNSINTBEGINRETURNYEAR(CURDATE())-YEAR(birth_date)-(DATE_FORMAT(CURDATE(),'%m%d')<DATE_FORMAT(birth_date,'%m%d'));END//DELIMITER;--調(diào)用函數(shù)查詢學(xué)生姓名和年齡SELECTname,CalculateAge(birth_date)ASageFROMstudents;查看題目3定義的存儲過程的信息。答：SHOWPROCEDUREGetBookCountByPublisherAndYear;刪除題目3定義的存儲過程。答：DROPPROCEDUREGetBookCountByPublisherAndYear;定義觸發(fā)器，在students表中插入新生信息前判斷：如果新生年齡小于15歲或者大于60歲，則提示年齡輸入錯誤。答：DELIMITER//CREATETRIGGERBeforeInsertStudentBEFOREINSERTONstudentsFOREACHROWBEGINDECLAREstudent_ageINT;SETstudent_age=YEAR(CURDATE())-YEAR(NEW.birth_date)-(DATE_FORMAT(CURDATE(),'%m%d')<DATE_FORMAT(NEW.birth_date,'%m%d'));IFstudent_age<15ORstudent_age>60THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='年齡輸入錯誤：年齡必須介于15到60歲之間';ENDIF;END//DELIMITER;為books表增加一個新列，列名：“狀態(tài)”，類型為字符串類型。定義觸發(fā)器，每當(dāng)有圖書被借出，判斷該圖書庫存，如果庫存為0，則將books表的“狀態(tài)”列設(shè)為“已全部借出”。答：--增加新列ALTERTABLEbooksADDCOLUMN`狀態(tài)`VARCHAR(50);--定義觸發(fā)器DELIMITER//CREATETRIGGERAfterBorrowBookAFTERINSERTONborrowFOREACHROWBEGINDECLAREcurrent_stockINT;SELECTstockINTO