第7章SQL擴展編程習題參考答案選擇題1．下列不屬于存儲過程作用的是A．能夠根據數據更改操作自動執行B．能夠實現模塊化程序設計C．可以減少網絡流量D．可作為安全機制使用答案：A2．下列有關存儲過程的敘述，錯誤的是A．MySQL允許在存儲過程創建時引用一個不存在的對象B．存儲過程可以帶多個輸入參數，也可以帶多個輸出參數C．使用存儲過程可以減少網絡流量D．在一個存儲過程中不可以調用其他存儲過程答案：D3．下列不能引發觸發器執行的操作是A．INSERTB．DELETEC．SELECTD．UPDATE答案：C4．MySQL為觸發器創建的兩個臨時表是A．max

和

minB．avg

和

sumC．int

和

charD．old

和

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