MYSQL的存儲過程和函數

MYSQL中創建存儲過程和函數分別使用CREATE PROCEDURE和CREATE FUNCTION

使用CALL語句來調用存儲過程，存儲過程也可以調用其他存儲過程

函數可以從語句外調用，能返回標量值

---

創建存儲過程

語法

CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body

?proc_parameter指定存儲過程的參數列表，列表形式如下：

[IN|OUT|INOUT] param_name type

其中in表示輸入參數，out表示輸出參數，inout表示既可以輸入也可以輸出；param_name表示參數名稱；type表示參數的類型

該類型可以是MYSQL數據庫中的任意類型

有以下取值：

characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' routine_body: Valid SQL procedure statement or statements

LANGUAGE SQL ：說明routine_body部分是由SQL語句組成的，當前系統支持的語言為SQL，SQL是LANGUAGE特性的唯一值

[NOT] DETERMINISTIC ：指明存儲過程執行的結果是否正確。DETERMINISTIC 表示結果是確定的。每次執行存儲過程時，相同的輸入會得到

相同的輸出。

[NOT] DETERMINISTIC 表示結果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為[NOT] DETERMINISTIC?

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL語句的限制。

CONTAINS SQL表明子程序包含SQL語句，但是不包含讀寫數據的語句；

NO SQL表明子程序不包含SQL語句；

READS SQL DATA：說明子程序包含讀數據的語句；

MODIFIES SQL DATA表明子程序包含寫數據的語句。

默認情況下，系統會指定為CONTAINS SQL

SQL SECURITY { DEFINER | INVOKER } ：指明誰有權限來執行。DEFINER 表示只有定義者才能執行

INVOKER 表示擁有權限的調用者可以執行。默認情況下，系統指定為DEFINER?

COMMENT 'string' ：注釋信息，可以用來描述存儲過程或函數

?

routine_body是SQL代碼的內容，可以用BEGIN...END來表示SQL代碼的開始和結束。

?

下面的語句創建一個查詢t1表全部數據的存儲過程

DROP PROCEDURE IF EXISTS Proc; DELIMITER // CREATE PROCEDURE Proc() BEGIN SELECT * FROM t3; END// DELIMITER ; CALL Proc();

t3表是我們上一節創建的表

這里的邏輯是

1、先判斷是否有Proc() 這個存儲過程，有就drop掉

2、創建Proc() 存儲過程

3、執行Proc()?存儲過程

?

注意：“DELIMITER //”語句的作用是將MYSQL的結束符設置為//，因為MYSQL默認的語句結束符為分號;，為了避免與存儲過程

中SQL語句結束符相沖突，需要使用DELIMITER 改變存儲過程的結束符，并以“END//”結束存儲過程。

存儲過程定義完畢之后再使用DELIMITER ;恢復默認結束符。DELIMITER 也可以指定其他符號為結束符！！！！！！！！！！！

?

?

?如果你是這樣寫的話，就會得到如下錯誤，初學者很容易犯這個錯誤，包括本人

CREATE PROCEDURE Proc() BEGINSELECT * FROM t3; END Query: CREATE PROCEDURE Proc() BEGIN SELECT * FROM t3 Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.001 sec --------------------------------------------------- Query: END Error Code: 1064

?

創建名為CountProc的存儲過程，代碼如下：

DELIMITER // CREATE PROCEDURE CountProc(OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t3; END// DELIMITER ;

上面代碼的作用是創建一個獲取t3表記錄數的存儲過程，名稱是CountProc，

COUNT(*)計算后把結果放入參數param1中。

?

注意：當使用DELIMITER命令時，應該避免使用反斜杠（\）字符，因為反斜杠是MYSQL的轉義字符！！！

---

存儲函數

創建存儲函數，需要使用CREATE FUNCTION語句，基本語法如下：

CREATE FUNCTION func_name([func_parameter]) RETURNS TYPE [characteristics...] routine_body

CREATE FUNCTION為用來創建存儲函數的關鍵字；func_name表示存儲函數的名稱

func_parameter為存儲函數的參數列表，參數列表如下

[IN|OUT|INOUT]PARAM_NAMETYPE

其中，IN表示輸入參數，OUT表示輸出參數，INOUT表示既可以輸入也可以輸出；

param_name表示參數名稱；type表示參數類型，該類型可以是MYSQL數據庫中的任意類型

RETURNS TYPE語句表示函數返回數據的類型；characteristics：指定存儲函數的特性，取值與創建存儲過程時相同

?

創建存儲函數，名稱為NameByT，該函數返回SELECT語句的查詢結果，數值類型為字符串型

DELIMITER //CREATE FUNCTION NameByT() RETURNS CHAR(50) RETURN (SELECT NAME FROM t3 WHERE id=2); // DELIMITER ;

注意：RETURNS CHAR(50)數據類型的時候，RETURNS 是有S的，而RETURN (SELECT NAME FROM t3 WHERE id=2)的時候RETURN是沒有S的

所以有時候大家可能覺得MYSQL很煩，誰不知是自己寫錯了

這里有一個方法，就是利用SQLYOG的代碼格式化功能，選中要格式化的代碼，然后按F12，如果能格式化，證明你的代碼沒有問題，如果不能格式化

證明你寫的代碼有問題！！！

不加s的話就會出現語法錯誤了

Query: create function NameByT() return char(50) return (select name from t3 where id=2) Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'return char(50) return (select name from t3 where id=2)' at line 2 Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.003 sec -----------------------------

調用函數

SELECT nameByT()

?

如果在存儲函數中的RETURN語句返回一個類型不同于函數的RETURNS子句中指定類型的值，返回值將被強制轉換為恰當的類型。

例如，如果一個函數返回一個SET或ENUM值，但是RETURN語句返回一個整數，對于SET成員集的相應ENUM成員，從函數返回的值

是字符串。

指定參數為IN、OUT、INOUT只對PROCEDURE是合法的。

（FUNCTION中總是默認是IN參數）RETURNS子句對FUNCTION做指定，對函數而言這是強制的。

他用來指定函數的返回類型，而且函數體必須包含一個RETURN value語句

---

變量的使用

變量可以在子程序中聲明并使用，這些變量的作用范圍是在BEGIN...END程序中

1、定義變量

在存儲過程中定義變量

DECLARE var_name[,varname]...date_type[DEFAULT VALUE];

var_name為局部變量的名稱。DEFAULT VALUE子句給變量提供一個默認值。值除了可以被聲明為一個常數外，還可以被指定為一個表達式。

如果沒有DEFAULT子句，初始值為NULL

DECLARE MYPARAM INT DEFAULT 100;

?

2、為變量賦值

定義變量之后，為變量賦值可以改變變量的默認值，MYSQL中使用SET語句為變量賦值

SET var_name=expr[,var_name=expr]...

在存儲過程中的SET語句是一般SET語句的擴展版本。

被SET的變量可能是子程序內的變量，或者是全局服務器變量，如系統變量或者用戶變量

?

他運行SET a=x,b=y,....

聲明3個變量，分別為var1，var2和var3

DECLARE var1,var2,var3 INT; SET var1=10,var2=20; SET var3=var1+var2;

?MYSQL中還可以通過SELECT...INTO為一個或多個變量賦值

DECLARE NAME CHAR(50); DECLARE id DECIMAL(8,2); SELECT id,NAME INTO id ,NAME FROM t3 WHERE id=2;

---

定義條件和處理程序

特定條件需要特定處理。這些條件可以聯系到錯誤，以及子程序中的一般流程控制。定義條件是事先定義程序執行過程中遇到的問題，

處理程序定義了在遇到這些問題時候應當采取的處理方式，并且保證存儲過程或函數在遇到警告或錯誤時能繼續執行。

這樣可以增強存儲程序處理問題的能力，避免程序異常停止運行

?

1、定義條件

DECLARE condition_name CONDITION FOR[condition_type] [condition_type]: SQLSTATE[VALUE] sqlstate_value |mysql_error_code

condition_name：表示條件名稱

condition_type：表示條件的類型

sqlstate_value和mysql_error_code都可以表示mysql錯誤

sqlstate_value為長度5的字符串錯誤代碼

mysql_error_code為數值類型錯誤代碼，例如：ERROR1142(42000)中，sqlstate_value的值是42000，

mysql_error_code的值是1142

?

這個語句指定需要特殊處理條件。他將一個名字和指定的錯誤條件關聯起來。

這個名字隨后被用在定義處理程序的DECLARE HANDLER語句中

?

定義ERROR1148(42000)錯誤，名稱為command_not_allowed。

可以用兩種方法定義

//方法一：使用sqlstate_value DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000' //方法二：使用mysql_error_code DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148

?

2．定義處理程序

MySQL中可以使用DECLARE關鍵字來定義處理程序。其基本語法如下：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement handler_type: CONTINUE | EXIT | UNDO condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

其中，handler_type參數指明錯誤的處理方式，該參數有3個取值。這3個取值分別是CONTINUE、EXIT和UNDO。

CONTINUE表示遇到錯誤不進行處理，繼續向下執行；

EXIT表示遇到錯誤后馬上退出；

UNDO表示遇到錯誤后撤回之前的操作，MySQL中暫時還不支持這種處理方式。

注意：通常情況下，執行過程中遇到錯誤應該立刻停止執行下面的語句，并且撤回前面的操作。

但是，MySQL中現在還不能支持UNDO操作。

因此，遇到錯誤時最好執行EXIT操作。如果事先能夠預測錯誤類型，并且進行相應的處理，那么可以執行CONTINUE操作。

condition_value參數指明錯誤類型，該參數有6個取值。

sqlstate_value和mysql_error_code與條件定義中的是同一個意思。

condition_name是DECLARE定義的條件名稱。

SQLWARNING表示所有以01開頭的sqlstate_value值。

NOT FOUND表示所有以02開頭的sqlstate_value值。

SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。

sp_statement表示一些存儲過程或函數的執行語句。

下面是定義處理程序的幾種方式。代碼如下：

//方法一：捕獲sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @info='CAN NOT FIND'; //方法二：捕獲mysql_error_code DECLARE CONTINUE HANDLER FOR 1148SET @info='CAN NOT FIND'; //方法三：先定義條件，然后調用 DECLARE can_not_find CONDITION FOR 1146 ; DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND'; //方法四：使用SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; //方法五：使用NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; //方法六：使用SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

上述代碼是6種定義處理程序的方法。

第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42000，執行CONTINUE操作，并且輸出"CAN NOT FIND"信息。

第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1148，執行CONTINUE操作，并且輸出"CAN NOT FIND"信息。

第三種方法是先定義條件，然后再調用條件。這里先定義can_not_find條件，遇到1148錯誤就執行CONTINUE操作。

第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值，然后執行EXIT操作，并且輸出"ERROR"信息。

第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值，然后執行EXIT操作，并且輸出"CAN NOT FIND"信息。

第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然后執行EXIT操作，并且輸出"ERROR"信息

?

定義條件和處理程序

?

CREATE TABLE t8(s1 INT,PRIMARY KEY(s1)) DELIMITER // CREATE PROCEDURE handlerdemo() BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1; SET @X=1; INSERT INTO t8 VALUES(1); SET @X=2; INSERT INTO t8 VALUES(1); SET @X=3; END; // DELIMITER ; /* 調用存儲過程*/ CALL handlerdemo(); /* 查看調用存儲過程結果*/ SELECT @X

?

@X是一個用戶變量，執行結果@X等于3，這表明MYSQL執行到程序的末尾。

如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;，這一行不存在

第二個INSERT因PRIMARY KEY約束而失敗之后，MYSQL可能已經采取EXIT策略，并且SELECT?@X可能已經返回2

注意：@X表示用戶變量，使用SET語句為其賦值，用戶變量與連接有關，一個客戶端定義的變量不能被其他客戶端所使用

即有作用域的，該客戶端退出時，客戶端連接的所有變量將自動釋放

?

這里的變量跟SQLSERVER沒有什么區別，都是用來存儲臨時值的

MYSQL這里的條件和預定義程序其實跟SQLSERVER的自定義錯誤是一樣的

---

光標

MYSQL里叫光標，SQLSERVER里叫游標，實際上一樣的

?

查詢語句可能查詢出多條記錄，在存儲過程和函數中使用光標來逐條讀取查詢結果集中的記錄。

光標的使用包括聲明光標、打開光標、使用光標和關閉光標。光標必須聲明在處理程序之前，并且聲明在變量和條件之后。

1．聲明光標

MySQL中使用DECLARE關鍵字來聲明光標。其語法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement ;

其中，cursor_name參數表示光標的名稱；select_statement參數表示SELECT語句的內容，返回一個用于創建光標的結果集

下面聲明一個名為cur_employee的光標。代碼如下：

DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee ;

上面的示例中，光標的名稱為cur_employee；SELECT語句部分是從employee表中查詢出name和age字段的值。

?

2．打開光標

MySQL中使用OPEN關鍵字來打開光標。其語法的基本形式如下：

OPEN cursor_name ;

其中，cursor_name參數表示光標的名稱。

下面打開一個名為cur_employee的光標，代碼如下：

OPEN cur_employee ;

?

3．使用光標

MySQL中使用FETCH關鍵字來使用光標。其語法的基本形式如下：

?

FETCH cur_employee INTO var_name[,var_name…] ;

?

其中，cursor_name參數表示光標的名稱；var_name參數表示將光標中的SELECT語句查詢出來的信息存入該參數中。var_name必須在聲明光標之前就定義好。

下面使用一個名為cur_employee的光標。將查詢出來的數據存入emp_name和emp_age這兩個變量中，代碼如下：

FETCH cur_employee INTO emp_name, emp_age ;

上面的示例中，將光標cur_employee中SELECT語句查詢出來的信息存入emp_name和emp_age中。emp_name和emp_age必須在前面已經定義。

4．關閉光標

MySQL中使用CLOSE關鍵字來關閉光標。其語法的基本形式如下：

?

CLOSE cursor_name ;

?

其中，cursor_name參數表示光標的名稱。

【示例14-11】 下面關閉一個名為cur_employee的光標。代碼如下：

CLOSE cur_employee ;

上面的示例中，關閉了這個名稱為cur_employee的光標。關閉之后就不能使用FETCH來使用光標了。

?

注意：MYSQL中，光標只能在存儲過程和函數中使用！！

?

到目前為止存儲函數，存儲過程、變量、條件、預定義程序、光標跟SQLSERVER差不多，只不過語法不同，結構不同

剛開始的時候會有不適應

---

流程控制的使用

存儲過程和函數中可以使用流程控制來控制語句的執行。

MySQL中可以使用IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句來進行流程控制。

每個流程中可能包含一個單獨語句，或者是使用BEGIN...END構造的復合語句，構造可以被嵌套

1．IF語句

IF語句用來進行條件判斷。根據是否滿足條件，將執行不同的語句。其語法的基本形式如下：

IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF

其中，search_condition參數表示條件判斷語句；statement_list參數表示不同條件的執行語句。

注意：MYSQL還有一個IF()函數，他不同于這里描述的IF語句

下面是一個IF語句的示例。代碼如下：

IF age>20 THEN SET @count1=@count1+1; ELSEIF age=20 THEN SET @count2=@count2+1; ELSE SET @count3=@count3+1; END IF;

該示例根據age與20的大小關系來執行不同的SET語句。

如果age值大于20，那么將count1的值加1；如果age值等于20，那么將count2的值加1；

其他情況將count3的值加1。IF語句都需要使用END IF來結束。

2．CASE語句

CASE語句也用來進行條件判斷，其可以實現比IF語句更復雜的條件判斷。CASE語句的基本形式如下：

CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE

其中，case_value參數表示條件判斷的變量；

when_value參數表示變量的取值；

statement_list參數表示不同when_value值的執行語句。

CASE語句還有另一種形式。該形式的語法如下：

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE

其中，search_condition參數表示條件判斷語句；

statement_list參數表示不同條件的執行語句。

下面是一個CASE語句的示例。代碼如下：

CASE age WHEN 20 THEN SET @count1=@count1+1; ELSE SET @count2=@count2+1; END CASE ;

代碼也可以是下面的形式：

CASE WHEN age=20 THEN SET @count1=@count1+1; ELSE SET @count2=@count2+1; END CASE ;

本示例中，如果age值為20，count1的值加1；否則count2的值加1。CASE語句都要使用END CASE結束。

注意：這里的CASE語句和“控制流程函數”里描述的SQL CASE表達式的CASE語句有輕微不同。這里的CASE語句不能有ELSE NULL子句

并且用END CASE替代END來終止！！

?

3．LOOP語句

LOOP語句可以使某些特定的語句重復執行，實現一個簡單的循環。

但是LOOP語句本身沒有停止循環的語句，必須是遇到LEAVE語句等才能停止循環。

LOOP語句的語法的基本形式如下：

[begin_label:] LOOP statement_list END LOOP [end_label]

其中，begin_label參數和end_label參數分別表示循環開始和結束的標志，這兩個標志必須相同，而且都可以省略；

statement_list參數表示需要循環執行的語句。

下面是一個LOOP語句的示例。代碼如下：

add_num: LOOP SET @count=@count+1; END LOOP add_num ;

該示例循環執行count加1的操作。因為沒有跳出循環的語句，這個循環成了一個死循環。

LOOP循環都以END LOOP結束。

?

4．LEAVE語句

LEAVE語句主要用于跳出循環控制。其語法形式如下：

LEAVE label

其中，label參數表示循環的標志。

?

下面是一個LEAVE語句的示例。代碼如下：

add_num: LOOP SET @count=@count+1; IF @count=100 THEN LEAVE add_num ; END LOOP add_num ;

該示例循環執行count加1的操作。當count的值等于100時，則LEAVE語句跳出循環。

?

5．ITERATE語句

ITERATE語句也是用來跳出循環的語句。但是，ITERATE語句是跳出本次循環，然后直接進入下一次循環。

ITERATE語句只可以出現在LOOP、REPEAT、WHILE語句內。

ITERATE語句的基本語法形式如下：

ITERATE label

其中，label參數表示循環的標志。

下面是一個ITERATE語句的示例。代碼如下：

add_num: LOOP SET @count=@count+1; IF @count=100 THEN LEAVE add_num ; ELSE IF MOD(@count,3)=0 THEN ITERATE add_num; SELECT * FROM employee ; END LOOP add_num ;

該示例循環執行count加1的操作，count值為100時結束循環。如果count的值能夠整除3，則跳出本次循環，不再執行下面的SELECT語句。

說明：LEAVE語句和ITERATE語句都用來跳出循環語句，但兩者的功能是不一樣的。

LEAVE語句是跳出整個循環，然后執行循環后面的程序。而ITERATE語句是跳出本次循環，然后進入下一次循環。

使用這兩個語句時一定要區分清楚。

?

6．REPEAT語句

REPEAT語句是有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句。REPEAT語句的基本語法形式如下：

[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]

其中，statement_list參數表示循環的執行語句；search_condition參數表示結束循環的條件，滿足該條件時循環結束。

下面是一個ITERATE語句的示例。代碼如下：

REPEAT SET @count=@count+1; UNTIL @count=100 END REPEAT ;

該示例循環執行count加1的操作，count值為100時結束循環。

REPEAT循環都用END REPEAT結束。

?

7．WHILE語句

WHILE語句也是有條件控制的循環語句。但WHILE語句和REPEAT語句是不一樣的。

WHILE語句是當滿足條件時，執行循環內的語句。

WHILE語句的基本語法形式如下：

[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]

其中，search_condition參數表示循環執行的條件，滿足該條件時循環執行；

statement_list參數表示循環的執行語句。

下面是一個ITERATE語句的示例。代碼如下：

WHILE @count<100 DO SET @count=@count+1; END WHILE ;

該示例循環執行count加1的操作，count值小于100時執行循環。

如果count值等于100了，則跳出循環。WHILE循環需要使用END WHILE來結束。

---

調用存儲過程和函數

存儲過程和存儲函數都是存儲在服務器端的SQL語句的集合，要使用這些已經定義好的存儲過程和存儲函數就必須要通過調用的方式來實現

存儲過程是通過CALL語句來調用的。而存儲函數的使用方法與MySQL內部函數的使用方法是一樣的

執行存儲過程和存儲函數需要擁有EXECUTE權限

EXECUTE權限的信息存儲在information_schema數據庫下面的USER_PRIVILEGES表中

?

調用存儲過程

MySQL中使用CALL語句來調用存儲過程。調用存儲過程后，數據庫系統將執行存儲過程中的語句。

然后，將結果返回給輸出值。

CALL語句的基本語法形式如下：

CALL sp_name([parameter[,…]]) ;

其中，sp_name是存儲過程的名稱；parameter是指存儲過程的參數。

CALL proc()

?

?

調用存儲函數

在MySQL中，存儲函數的使用方法與MySQL內部函數的使用方法是一樣的。

換言之，用戶自己定義的存儲函數與MySQL內部函數是一個性質的。

區別在于，存儲函數是用戶自己定義的，而內部函數是MySQL的開發者定義的。

下面定義一個存儲函數，然后調用這個存儲函數。

代碼執行如下：

-- 創建存儲函數 DELIMITER // CREATE FUNCTION name_from_t3(id INT ) RETURNS CHAR(80) RETURN (SELECT NAME FROM t3 WHERE id=id ); // DELIMITER ; SELECT name_from_t3(2);

上述存儲函數的作用是根據輸入的id值到t3表中查詢記錄。

查詢出id字段的值等于id的記錄。然后將該記錄的name字段的值返回。

---

查看存儲過程和函數

存儲過程和函數創建以后，可以查看存儲過程和函數的狀態和定義。

通過SHOW STATUS語句來查看存儲過程和函數的狀態，也可以通過SHOW CREATE語句來查看存儲過程和函數的定義。

通過查詢information_schema數據庫下的Routines表來查看存儲過程和函數的信息

?

1、SHOW STATUS語句查看存儲過程和函數的狀態

MySQL中可以通過SHOW STATUS語句查看存儲過程和函數的狀態。其基本語法形式如下：

SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ' pattern ' ] ;

其中，PROCEDURE參數表示查詢存儲過程；FUNCTION參數表示查詢存儲函數；

LIKE ' pattern '參數用來匹配存儲過程或函數的名稱。

?

下面查詢名為name_from_t3的函數的狀態。代碼執行如下：

SHOW FUNCTION STATUS LIKE '%name_from_t3%' Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation ------ ------------ -------- -------------- ------------------- ------------------- ------------- ------- -------------------- -------------------- ------------------ school name_from_t3 FUNCTION root@localhost 2014-06-21 18:52:39 2014-06-21 18:52:39 DEFINER utf8 utf8_general_ci utf8_general_ci

查詢結果顯示了函數的創建時間、修改時間和字符集等信息。

?

注意：SHOW STATUS語句只能查看存儲過程或函數是操作哪一個數據庫、存儲過程或函數的名稱、類型、誰定義的、創建和修改時間、字符編碼等信息。

但是，這個語句不能查詢存儲過程或函數的具體定義。如果需要查看詳細定義，需要使用SHOW CREATE語句

?

2、SHOW CREATE語句查看存儲過程和函數的定義

MySQL中可以通過SHOW CREATE語句查看存儲過程和函數的狀態。其基本語法形式如下：

SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;

其中，PROCEDURE參數表示查詢存儲過程；

FUNCTION參數表示查詢存儲函數；

sp_name參數表示存儲過程或函數的名稱

?

下面查詢名為name_from_t3的函數的定義。代碼執行如下

SHOW CREATE FUNCTION name_from_t3 Function sql_mode Create Function character_set_client collation_connection Database Collation ------------ -------- ----------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- ------------------ name_from_t3 CREATE DEFINER=`root`@`localhost` FUNCTION `name_from_t3`(id INT ) RETURNS char(80) CHARSET utf8 utf8 utf8_general_ci utf8_general_ci RETURN (SELECT NAME FROM t3 WHERE id=id )

?

3、從information_schema.Routines表中查看存儲過程和函數的信息

存儲過程和函數的信息存儲在information_schema數據庫下的Routines表中。可以通過查詢該表的記錄來查詢存儲過程和函數的信息。

其基本語法形式如下：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;

其中，ROUTINE_NAME字段中存儲的是存儲過程和函數的名稱；

sp_name參數表示存儲過程或函數的名稱。

?

下面從Routines表中查詢名為name_from_t3函數的信息。

代碼執行如下：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='name_from_t3' SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION ------------- --------------- -------------- ------------ ------------ --------- ------------------------ ---------------------- ----------------- ------------- ------------------ --------------- -------------- ------------ ---------------------------------------------- ------------- ----------------- --------------- ---------------- --------------- -------- ------------- ------------------- ------------------- -------- --------------- -------------- -------------------- -------------------- ------------------ name_from_t3 def school name_from_t3 FUNCTION char 80 240 (NULL) (NULL) utf8 utf8_general_ci char(80) SQL RETURN (SELECT NAME FROM t3 WHERE id=id ) (NULL) (NULL) SQL NO CONTAINS SQL (NULL) DEFINER 2014-06-21 18:52:39 2014-06-21 18:52:39 root@localhost utf8 utf8_general_ci utf8_general_ci

查詢結果顯示name_from_t3的詳細信息。

?

注意：在information_schema數據庫下的Routines表中，存儲著所有存儲過程和函數的定義。

如果使用SELECT語句查詢Routines表中的存儲過程和函數的定義時，一定要使用ROUTINE_NAME字段指定存儲過程或函數的名稱。

否則，將查詢出所有的存儲過程或函數的定義。

---

修改存儲過程和函數

修改存儲過程和函數是指修改已經定義好的存儲過程和函數。

MySQL中通過ALTER PROCEDURE語句來修改存儲過程。

通過ALTER FUNCTION語句來修改存儲函數。

MySQL中修改存儲過程和函數的語句的語法形式如下：

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] characteristic: { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'

其中，sp_name參數表示存儲過程或函數的名稱；

characteristic參數指定存儲函數的特性。

CONTAINS SQL表示子程序包含SQL語句，但不包含讀或寫數據的語句；

NO SQL表示子程序中不包含SQL語句；

READS SQL DATA表示子程序中包含讀數據的語句；

MODIFIES SQL DATA表示子程序中包含寫數據的語句。

SQL SECURITY { DEFINER | INVOKER }指明誰有權限來執行。

DEFINER表示只有定義者自己才能夠執行；

INVOKER表示調用者可以執行。

COMMENT 'string'是注釋信息。

說明：修改存儲過程使用ALTER PROCEDURE語句，修改存儲函數使用ALTER FUNCTION語句。

但是，這兩個語句的結構是一樣的，語句中的所有參賽都是一樣的。

而且，它們與創建存儲過程或函數的語句中的參數也是基本一樣的。

?

修改存儲過程和函數，只能修改他們的權限，目前MYSQL還不提供對已存在的存儲過程和函數代碼的修改

如果要修改，只能通過先DROP掉，然后重新建立新的存儲過程和函數來實現

在SQLYOG里選中選中函數，然后右鍵ALTER FUNCTION的時候，也是這樣，先DROP掉，然后重新建立新的函數來實現

DELIMITER $$ DROP FUNCTION IF EXISTS `name_from_t3`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `name_from_t3`(id INT ) RETURNS CHAR(80) CHARSET utf8 RETURN (SELECT NAME FROM t3 WHERE id=id )$$ DELIMITER ;

?

---

刪除存儲過程和函數

刪除存儲過程和函數指刪除數據庫中已經存在的存儲過程和函數。

MySQL中使用DROP?PROCEDURE語句來刪除存儲過程。通過DROP FUNCTION語句來刪除存儲函數。

DROP { PROCEDURE| FUNCTION } sp_name;

其中，sp_name參數表示存儲過程或函數的名稱

?

下面刪除存儲過程Proc和存儲函數name_from_t3。刪除存儲過程的代碼如下：

DROP PROCEDURE Proc;

刪除存儲函數name_from_employee的代碼如下：

DROP FUNCTION name_from_t3;

刪除完畢之后，查詢information_schema.Routines來確認上面的刪除是否成功

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='proc' OR ROUTINE_NAME='name_from_t3';

---

總結

TIPS:

存儲過程里面是可以調用其他存儲過程的，使用CALL語句調用其他存儲過程就可以了

存儲過程參數列表里的參數名盡量不要和數據庫中表的字段名一樣，否則有可能出錯

存儲過程的參數可以使用中文，在定義存儲過程的時候加上character set gbk就可以了

例如

CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) CHARACTER SET gbk,OUT u_age INT)

?

?

DECLARE 語句要寫在存儲過程里面，否則會報錯！！

DROP PROCEDURE IF EXISTS Proc; DELIMITER // CREATE PROCEDURE Proc() BEGIN DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000' ; END// DELIMITER ; CALL Proc();

?

?

?

搜索

復制

轉載于:https://www.cnblogs.com/dingdaxiong/p/4606012.html

總結

以上是生活随笔為你收集整理的自定义存储过程和函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。