存儲過程概述創(chuàng)建和執(zhí)行存儲過程管理存儲過程存儲函數事務第六章存儲過程與事務學習目標掌握了解理解1掌握存儲過程的創(chuàng)建、修改和執(zhí)行、存儲函數的創(chuàng)建和執(zhí)行、事務處理的過程2理解存儲過程的作用、事務的隔離級別3了解存儲過程和存儲函數、程序存儲的類型MySQL程序設計基礎6.1存儲過程概述6.26.3創(chuàng)建和執(zhí)行存儲過程目錄管理存儲過程6.4存儲函數6.56.6事務目錄創(chuàng)建存儲過程語法格式如下：CREATEPROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_body其中：

proc_parameter:[IN|OUT|INOUT]param_nametypecharacteristic:LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'routine_body:ValidSQLprocedurestatementorstatements6.3創(chuàng)建和執(zhí)行存儲過程6.3.1創(chuàng)建和執(zhí)行不帶參數的存儲過程使用SQL語句在“學生選課”數據庫中創(chuàng)建一個名為p_jiaoshi1的存儲過程。該存儲過程返回“教師”表中所有學歷為“碩士研究生”的記錄。對應的SQL語句如下：DELIMITER//CREATEPROCEDUREp_jiaoshi1()BEGINSELECT*FROMteacherWHEREtedu='碩士研究生';ENDEND//DELIMITER;實例6-1使用SQL語句執(zhí)行例6-1中創(chuàng)建的存儲過程。對應的SQL語句如下：執(zhí)行結果如圖6.1所示。執(zhí)行結果如下所示：USE學生選課;CALLp_jiaoshi1();+------+--------+---------+------------+----------+|tno|tname|tgender|tedu|tpro|+------+--------+---------+------------+----------+|t002|李琦|男|碩士研究生|副教授||t003|王艷紅|女|碩士研究生|講師||t005|萬麗|女|碩士研究生|助理講師|+------+--------+---------+------------+----------+3rowsinset(0.00sec)實例6-21.創(chuàng)建帶輸入參數的存儲過程輸入參數是指由調用程序向存儲過程傳遞的參數，在創(chuàng)建存儲過程語句中定義輸入參數，而在執(zhí)行該存儲過程中給出參數相應的值。6.3.2創(chuàng)建和執(zhí)行帶輸入參數的存儲過程（1）使用變量名傳遞參數值在執(zhí)行存儲過程的語句中，通過語句SET@parameter_name=value設定給參數的傳遞值，然后按位置傳遞參數。其語法格式如下：CALLprocedure_name([@parameter_name=value][,…n]);使用SQL語句在“學生選課”數據庫中創(chuàng)建一個名為p_jiaoshi2的存儲過程。該存儲過程能根據用戶給定的學歷值查詢返回“教師”表中對應的記錄SET@輸入學歷='本科';CALLp_jiaoshi2(@輸入學歷);SET@輸入學歷='博士研究生';CALLp_jiaoshi2(@輸入學歷);實例6-4+------+--------+---------+------+------+|tno|tname|tgender|tedu|tpro|+------+--------+---------+------+------+|t001|吳亞飛|男|本科|講師|+------+--------+---------+------+------+1rowinset(0.19sec)QueryOK,0rowsaffected(0.19sec)mysql>SET@輸入學歷='博士研究生';QueryOK,0rowsaffected(0.00sec)mysql>CALLp_jiaoshi2(@輸入學歷);+------+--------+---------+------------+------+|tno|tname|tgender|tedu|tpro|+------+--------+---------+------------+------+|t004|馬志超|男|博士研究生|教授|+------+--------+---------+------------+------+1rowinset(0.00sec)運行結果：2.執(zhí)行帶輸入參數的存儲過程執(zhí)行帶輸入參數的存儲過程，有兩種方法：一種是使用變量名傳遞參數值；另一種是按給定表達式值傳遞參數值。用參數名傳遞參數值的方法執(zhí)行存儲過程p_jiaoshi2，分別查詢學歷為“本科”和“博士研究生”的記錄。

DELIMITER//SET@輸入學歷='本科';CALLp_jiaoshi2(@輸入學歷);SET@輸入學歷='博士研究生';CALLp_jiaoshi2(@輸入學歷);//DELIMITER;實例6-4（2）按給定表達式值傳遞參數在執(zhí)行存儲過程的語句中，當存儲過程含有多個輸入參數時，采用這種方式傳遞值，給定參數值的順序必須與存儲過程中定義的輸入變量的順序一致。其語法格式如下：CALLprocedure_name(value1,value2,…)用按給定表達式值傳遞參數的方式執(zhí)行存儲過程p_jiaoshi2，分別查找學歷為“本科”和“博士研究生”的記錄。對應的SQL語句如下：運行結果和例6.4運行結果相同。CALLp_jiaoshi2('本科');CALLp_jiaoshi2('博士研究生');實例6-5如果需要從存儲過程中返回一個或多個值，可以通過在創(chuàng)建存儲過程的語句中定義輸出參數來實現。定義輸出參數，需要在CREATEPROCEDURE語句中定義參數時在參數名后面指定OUT關鍵字。語法格式如下：OUTparameter_namedatatype[=default]6.3.3創(chuàng)建和執(zhí)行帶輸出參數的存儲過程創(chuàng)建存儲過程p_jiaoshi3，要求能根據用戶給定的學歷值，統(tǒng)計出“教師”表的所有教師中，學歷為該值的教師人數，并將結果以輸出變量的形式返回給調用者。DELIMITER//CREATEPROCEDUREp_jiaoshi3(INtedu1varchar(10),OUTteachernumsmallint)BEGINSELECTCOUNT(*)INTOteachernumFROMteacherWHEREtedu=tedu1;END//DELIMITER;實例6-6執(zhí)行存儲過程p_jiaoshi3，統(tǒng)計教師表中學歷為“碩士研究生”的教師人數。運行結果：CALLp_jiaoshi3('碩士研究生',@abc);SELECT@abc;mysql>SELECT@abc;+------+|@abc|+------+|3|+------+1rowinset(0.00sec)實例6-7