30.4. MySQL函數(shù),存儲過程,觸發(fā)器,視圖-顫沙的博客-51CTO博客

函數(shù)

函數(shù)：系統(tǒng)函數(shù)和自定義函數(shù)系統(tǒng)函數(shù):https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html數(shù)據(jù)庫之后利用help命令來查看幫助，比如help max自定義函數(shù) (user-defined function UDF)

注自定義函數(shù)定義之后保存在mysql.proc（mysql.func)表中 創(chuàng)建UDF必須得有，且只能有一個返回值

創(chuàng)建函數(shù)

示例1：無參UDF

查看自定義的函數(shù)列表：示例2：有參數(shù)UDF

其中的delimiter代表分隔符，先切換成//（也可以寫其他的符號），然后函數(shù)定義完了之后再切換回; 這樣做用于避免函數(shù)的的內(nèi)容被執(zhí)行，因為函數(shù)體內(nèi)的命令有分號作為結(jié)尾，不先修改分隔符的話會被當做命令來執(zhí)行。

自定義函數(shù)中定義局部變量語法

DECLARE 變量1[,變量2,… ] 變量類型 [DEFAULT 默認值]

說明：局部變量的作用范圍是在BEGIN…END程序中,而且定義局部變量語句必須在BEGIN…END的第一行定義

示例3:DECLARE a, b SMALLINT UNSIGNED;

在這里的函數(shù)中定義本地變量的時候，要使用declare命令(主義和set區(qū)別)，一行中的變量的類型只需要寫一次（寫在最后）即可，它代表把前面所有的變量都定義為這種類型的 寫在begin和and之間進行定義的變量都是局部變量，在這里類似C語言，且變量前面不用加任何符號直接定義和使用即可

為變量賦值語法

SET @parameter_name = value[,parameter_name = value…]示例4:

存儲過程

存儲過程優(yōu)勢

存儲過程把經(jīng)常使用的SQL語句或業(yè)務(wù)邏輯封裝起來,預(yù)編譯保存在數(shù)據(jù)庫中,當需要時從數(shù)據(jù)庫中直接調(diào)用,省去了編譯的過程 提高了運行速度 同時降低網(wǎng)絡(luò)數(shù)據(jù)傳輸量存儲過程與自定義函數(shù)的區(qū)別 存儲過程實現(xiàn)的過程要復(fù)雜一些,而函數(shù)的針對性較強 存儲過程可以有多個返回值,而自定義函數(shù)只有一個返回值 存儲過程一般可獨立執(zhí)行,而函數(shù)往往是作為其他SQL語句的一部分來使用

存儲過程：存儲過程保存在mysql.proc表中

創(chuàng)建存儲過程說明:當無參時,可以省略"()",當有參數(shù)時,不可省略"()” 存儲過程修改方法就是刪除重建 刪除存儲過程存儲過程示例

創(chuàng)建無參存儲過程

delimiter //

CALL showTime;

創(chuàng)建含參存儲過程：只有一個IN參數(shù)

delimiter //

call selectById(2);

創(chuàng)建包含有會話級全局變量的存儲過程

delimiter //

CALL dorepeat(100);

注意這里用到了循環(huán)體repeat … until … END repeat; 因為是用set命令定義的自定義的變量，所以就算在存儲過程外也能夠直接使用它。 在這里用了set定義會話級全局變量的方式把過程內(nèi)的結(jié)果傳入到外面，更簡單的方法就是下面4中的定義一個OUT參數(shù)，用它把結(jié)果傳到外面（不過也得利用外部的會話級自定義全局變量接收）。

創(chuàng)建含參存儲過程:包含IN參數(shù)和OUT參數(shù)

delimiter //SELECT row_count() into num; #注意這里的形參并沒有用@，看注意點7

call deleteById(2,@Line);

說明:創(chuàng)建存儲過程deleteById,包含一個IN參數(shù)和一個OUT參數(shù).調(diào)用時,傳入刪除的ID和保存被修改的行的數(shù)值的用戶變量@Line 然后用select @Line;輸出被修改后的行的值（這里也就指的是被刪除掉了多少行的行數(shù)值，刪除這些行的判斷條件由傳入的參數(shù)uid指定）。 可用help row_count 查看此函數(shù)的解釋

流程控制

存儲過程和函數(shù)中可以使用流程控制來控制語句的執(zhí)行

IF：用來進行條件判斷。根據(jù)是否滿足條件，執(zhí)行不同語句 CASE：用來進行條件判斷，可實現(xiàn)比IF語句更復(fù)雜的條件判斷 LOOP：重復(fù)執(zhí)行特定的語句，實現(xiàn)一個簡單的循環(huán) LEAVE：用于跳出循環(huán)控制 （類似break) ITERATE：跳出本次循環(huán)，然后直接進入下一次循環(huán) (類似continue) REPEAT：有條件控制的循環(huán)語句。當滿足特定條件時，就會跳出循環(huán)語句 WHILE：有條件控制的循環(huán)語句

trigger觸發(fā)器(類似ansible中的handler)

觸發(fā)器的執(zhí)行不是由程序調(diào)用，也不是由手工啟動，而是由事件來觸發(fā)、激活從而實現(xiàn)執(zhí)行創(chuàng)建觸發(fā)器說明：BEFORE | AFTER }，表示在事件之前或之后觸發(fā)

查看觸發(fā)器

SHOW TRIGGERS 查詢系統(tǒng)表information_schema.triggers的方式指定查詢條件，查看指定的觸發(fā)器信息:刪除觸發(fā)器觸發(fā)器示例

=================先定義一張表： CREATE TABLE student_info ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) DEFAULT NULL, PRIMARY KEY (stu_id) ); CREATE TABLE student_count ( student_count INT(11) DEFAULT 0 ); INSERT INTO student_count VALUES(0);  ================然后創(chuàng)建觸發(fā)器，在向?qū)W生表INSERT數(shù)據(jù)時，學(xué)生數(shù)增加，DELETE學(xué)生時，學(xué)生數(shù)減少 CREATE TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR EACH ROW  ：這里要指定有事件變化的表（info表），別指定成了count表 UPDATE student_count SET student_count=student_count+1; CREATE TRIGGER trigger_student_count_delete AFTER DELETE ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count-1;  注意這里的ON TABLE_NAME中的table_name指定的是變化的表和事件,它和trigger_body中的命令并無直接聯(lián)系 body中的SQL語句可以對任何表進行任何操作，和上面ON后面的表并無直接聯(lián)系，那個表只適用于判斷事件的發(fā)生，事件發(fā)生后就和body無關(guān)了（也就是說并非只能針對ON后面的這個表進行操作，body命令沒有限制）

注意點:

自定義函數(shù)存儲的位置是在mysql.proc表中，這是一個表。 函數(shù)和存儲過程（本質(zhì)也是一種函數(shù)）的定義可以用交互式方式進行，也可以用非交互式方式先寫到文件內(nèi)，然后導(dǎo)入即可（數(shù)據(jù)庫外直接重定向，連接到數(shù)據(jù)庫內(nèi)則用source命令）。 注意這里只是定義了函數(shù)和存儲過程，而并沒有調(diào)用或者執(zhí)行它們。 注意，與示例3中函數(shù)體中的局部變量相對應(yīng)的會話級的全局變量可以在mysql的交互式命令行中用set命令來進行定義賦值；set命令不僅可以定義并賦值用戶自己定義的變量，還可以用來修改系統(tǒng)自身存在的變量的值。 不過系統(tǒng)自身的變量的值是否能夠直接在mysql命令行中修改，以及系統(tǒng)變量生效的范圍（global,session也就是這個端口）等等，會在下一章進行詳細介紹。 這里需要注意的就是如果是global類型的變量，只有在mysql服務(wù)重啟的時候，它才會重新恢復(fù)到默認值（假設(shè)沒寫入配置文件且用set修改了它的值）;而session級別的，只要退出了mysql的連接，下次客戶端重新連接mysql服務(wù)的時候它都會恢復(fù)到默認值（包括另外的終端上連接的也是）。除非將它寫入配置文件中（有相對應(yīng)的選項）并且重啟mysql服務(wù)。 另外一點要注意的就是用mysqladmin能夠修改的變量會在重啟mysql服務(wù)后才恢復(fù)默認值，而不是客戶端mysql連接到服務(wù)中后。因為mysqladmin修改的就是服務(wù)器端的配置，如果沒有寫入配置文件，則服務(wù)器端重啟之后會恢復(fù)到默認值。它和mysql客戶端命令是沒有關(guān)系的。 注意用set命令定義和賦值自定義變量的時候要在變量名字前面加上@符號，不然會被當做系統(tǒng)變量。這個變量雖然是全局可用的，但它也是出于會話級別的全局，新開一個會話（終端端口）就不能再用了。 顯示這個變量的時候要用select @變量名 ； 注意show variables [like ‘系統(tǒng)變量名’] 命令只能顯示系統(tǒng)定義的變量 還可以用into命令把聚合函數(shù)的結(jié)果賦值到自定義變量中： select avg(age) form students into @avg_age; select @avg_age; 存儲過程就相當于是一部分SQL語句和操作的合集的封裝，和函數(shù)很類似（其實本質(zhì)就是一種特殊的function) 函數(shù)和存儲過程中的變量使用的的時候（不論是定義的時候用的形參，還是說內(nèi)部定義的本地變量）都不需要在變量或參數(shù)前面加上@符號，只有用到了set命令自定義變量賦值或者外部的自定義變量的時候，才需要在參數(shù)名前面加上@符號。 注意一點就是表中的字段用set修改賦值的時候也不需要加上@符號，這里的字段雖然也能夠進行各種計算和條件判斷（where)等,但是字段和變量并不一樣。（參考上面trigger的示例中的set命令，由此想起來這一點的）

視圖

視圖：VIEW,虛表，保存有實表的查詢結(jié)果

創(chuàng)建方法：注意：視圖中的數(shù)據(jù)事實上存儲于“基表”中，因此，其修改操作也會針對基表實現(xiàn)；其修改操作受基表限制

視圖注意點：

因為視圖創(chuàng)建完之后在數(shù)據(jù)庫中就像真的表一般無法區(qū)分，（此時當然也不可能用show create view view_name的方式來查看，因為不知道是view 還是 table)