理解MySQL存儲過程和函數(shù)

1、理解mysQL者過程和函數(shù)一、概述一提到存儲過程可能就會引出另一個話題就是存儲過程的優(yōu)缺點，這里也不做討論，一般別人問我我就這樣回答你覺得它好你就用它。因為y中存儲過程和函數(shù)的語法非常接近所以就放在一起，主要區(qū)別就是函數(shù)必須有返回值（），并且函數(shù)的參數(shù)只有類型而存儲過程有、這三種類型。二、語法創(chuàng)建存儲過程和函數(shù)語法Stypecteristic.routine_bodyOU、|I、OU、param_nametytypeype:AnyvalidMySQLdtypeLA、GUAGESQLRMI、IS、I、AI、SSQLOSQL|EADSSQL|SQLSE|COMMEbody:SQLproMODIFI

2、ESSQL語法來自官方自帶的參考手冊,例子：語法塊是需要注意的地方，先用一個例子來介紹。ployeeLLM主鍵YCOMM人名LLOMME、咅部門MMS、SEmploy插入測試數(shù)據(jù)I、SER、I#創(chuàng)建存儲過程DELIMI、EREADSSQLSQLSECURyee陳王張L李S40,2郭SSloyeeo_Employee(I、SELMEmployLIM創(chuàng)建數(shù)據(jù)庫ABASEA、ABASRSE、SEDpro;創(chuàng)建部門表ROP、ABLEREA、E、ABL#執(zhí)行存儲過程CALLPro_Employee(101,pcount);SELECTpcount;語法解釋：在創(chuàng)建存儲過程的時候一般都會用DELIMITE

3、R$END$DELIMITER放在開頭和結束，目的就是避免mysqI把存儲過程內(nèi)部的”;解釋成結束符號，最后通過“DELIMITER;”來告知存儲過程結束。主要解釋characteristic部分：LANGUAGESQL:用來說明語句部分是SQL語句，未來可能會支持其它類型的語句。NOTDETERMINISTIC:如果程序或線程總是對同樣的輸入?yún)?shù)產(chǎn)生同樣的結果，則被認為它是,確定的”，否則就是“非確定”的。如果既沒有給定DETERMINISTIC也沒有給定NOTDETERMINISTIC，默認的就是NOTDETERMINISTIC(非確定的)CONTAINSSQL:表示子程序不包含讀或?qū)憯?shù)據(jù)

4、的語句。NOSQL:表示子程序不包含SQL語句。READSSQLDATA:表示子程序包含讀數(shù)據(jù)的語句，但不包含寫數(shù)據(jù)的語句。MODIFIESSQLDATA:表示子程序包含寫數(shù)據(jù)的語句。SQLSECURITYDEFINER:表示執(zhí)行存儲過程中的程序是由創(chuàng)建該存儲過程的用戶的權限來執(zhí)行。SQLSECURITYINVOKER:表示執(zhí)行存儲過程中的程序是由調(diào)用該存儲過程的用戶的權限來執(zhí)行。(例如上面的存儲過程我寫的是由調(diào)用該存儲過程的用戶的權限來執(zhí)行，當前存儲過程是用來查詢Employee表，如果我當前執(zhí)行存儲過程的用戶沒有查詢Employee表的權限那么就會返回權限不足的錯誤，如果換成DEFINER

5、如果存儲過程是由ROOT用戶創(chuàng)建那么任何一個用戶登入調(diào)用存儲過程都可以執(zhí)行，因為執(zhí)行存儲過程的權限變成了root)COMMENTstring:備注，和創(chuàng)建表的字段備注一樣。注意：在編寫存儲過程和函數(shù)時建議明確指定上面characteristic部分的狀態(tài)，特別是存在復制的環(huán)境中，如果創(chuàng)建函數(shù)不明確指定這些狀態(tài)會報錯，從一個非復制環(huán)境將帶函數(shù)的數(shù)據(jù)庫遷移到復制環(huán)境的機器上如果沒有明確指定DETERMINISTIC,NOSQL,orREADSSQLDATA該三個狀態(tài)也會報錯。報錯示例ErrorCode:1418.ThisfunctionhasnoneofDETERMINISTIC,NOSQL,or

6、READSSQLDATAinitsdeclarationandbinaryloggingisenabled(you*might*wanttousethelesssafelog_bin_trust_function_creatorsvariable)這個報錯就是上面注意部分說的問題。原來是因為在主從復制的兩臺MySQL服務器中開啟了二進制日志選項og-bin,slave會從master復制數(shù)據(jù)，而一些操作，比如function所得的結果在master和slave上可能不同，所以存在潛在的安全隱患。因此，在默認情況下回阻止function的創(chuàng)建。解決辦法有兩種:將log_bin_trust_fun

7、ction_creator參數(shù)設置為ON，這樣一來開啟了log-bin的MySQLServe便可以隨意創(chuàng)建function。這里存在潛在的數(shù)據(jù)安全問題，除非明確的知道創(chuàng)建的function在master和slave上的行為完全一致。設置該參數(shù)可以用動態(tài)的方式或者指定該參數(shù)來啟動數(shù)據(jù)庫服務器或者修改配置文件后重啟服務器。需注意的是，動態(tài)設置的方式會在服務器重啟后失效。mysqlshowvariableslikelog_bin_trust_function_creators;mysqlsetgloballog_bin_trust_function_creators=1;另外如果是在master上創(chuàng)

8、建函數(shù)，想通過主從復制的方式將函數(shù)復制到slave上則也需在開啟了log-bin的slave中設置上述變量的值為ON(變量的設置不會從master復制到slave上,這點需要注意)，否則主從復制會報錯。明確指明函數(shù)的類型)、DETERMINISTIC不確定的)、NOSQL沒有SQl語句，當然也不會修改數(shù)據(jù))、READSSQLDATA只是讀取數(shù)據(jù)，當然也不會修改數(shù)據(jù)比如：CREATEDEFINER=username%READSSQLDATAFUNCTIONfn_getitemclock(i_itemidbigint,i_clockint,i_posint)RETURNSint(11).這扌樣一來

9、相當于明確的告知MySQL服務器這個函數(shù)不會修改數(shù)據(jù)，因此可以在開啟了log-bin的服務器上安全的創(chuàng)建并被復制到開啟了log-bin的slave上。修改存儲過程函數(shù)語法ALTERPROCEDURE|FUNCTIONsp_namecharacteristic.characteristic:CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA|SQLSECURITYDEFINER|INVOKER|COMMENTstring刪除存儲過程函數(shù)語法DROPPROCEDURE|FUNCTIONIFEXISTSsp_name查看存儲過程和函數(shù)查看存儲過程狀態(tài)SHOWPROCEDURE|FUNCTIONSTATUSLIKEpatternshowprocedurestatuslikePro_EmployeeG查看存儲過程和函數(shù)的創(chuàng)建語法SHOWCREATEPROCEDURE|FUNCTIONsp_nameSHOWCREATEPROCEDUREPro_EmployeeG;查看存儲過程和函數(shù)詳細信息復制代碼代碼如下:SELECT*FROMinformation_s