當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

存储过程和函数的操作

發布時間：2023/12/10 编程问答 23 豆豆

生活随笔收集整理的這篇文章主要介紹了存储过程和函数的操作小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

一、學習任務1：為什么使用存儲過程和函數

在MySQL數據庫中，數據庫對象表是存儲和操作數據的邏輯結構，而本章所要介紹的數據庫對象存儲過程和函數，則用來實現將一組關于表操作的SQL語句代碼當作一個整體來執行，也是與數據庫對象表關聯最緊密的數據庫對象。在數據庫系統中，當調用存儲過程和函數時，則會執行這些對象中所設置的SQL語句組，從而實現相應的功能。

通過前面章節的學習，用戶不僅能夠編寫操作單表的單條SQL語句，而且還能夠編寫操作多表的SQL單條語句。但是針對表的一個完整操作往往不是單條SQL語句就可以實現，而是需要一組?SQL語句來實現。

例如，為了完成購買商品的訂單處理，需要考慮如下的情形。

（1）在生成訂單信息之前，首先需要查看商品庫存中是否有相應的商品。

（2）如果商品庫存中存在相應的商品，接著需要預定商品以便不將該商品賣給別人，并且修改庫存物品數量以反映正確的庫存量。

（3）如果商品庫存中不存在相應的商品，就需要向供應商訂貨。

對于上述一個完整操作，它顯示不是單條SQL語句所能實現。因為實現這個完整操作需要編寫針對許多表的多條SQL語句，此外在具體執行過程中，這些SQL語句的執行順序也不是固定的，它會根據相應條件而變化。

在具體應用中，一個完整的操作會包含多條SQL語句，在執行過程中需要根據前面SQL語句的執行結果有選擇的執行后面的SQL語句。為了解決該問題，MySQL軟件提供了數據庫對象存儲過程和函數。

觸發器可以簡單理解為一條或多條SQL語句的集合。存儲過程和函數就是事先經過編譯并存儲在數據庫中的一段SQL語句集合。觸發器的執行不是由程序調用，也不是由手動啟動，而是由事件來觸發、激活從而實現執行。而存儲過程和函數的執行，則需要手動調用存儲過程和函數的名字并需要指定相應的參數。

存儲過程和函數有什么區別呢？這兩者的區別主要在于函數必須有返回值，而存儲過程則沒有（不能使用return關鍵字）。存儲過程的參數類型遠遠多于函數參數類型。

既然己經知道為什么要使用存儲過程和函數，那么使用存儲過程和函數有哪些優點和缺點呢？?關于存儲過程和函數的優點如下：

? ?存儲過程和函數允許標準組件式編程，提高了SQL語句的重用性、共享性和可移植性。

? ?存儲過程和函數能夠實現較快的執行速度，能夠減少網絡流量。

? ?存儲過程和函數可以被作為一種安全機制來利用。

上述優點可以概述成簡單和高性能，不過在具體使用存儲過程和函數時，也需要了解這些數據庫對象的缺陷，分別為：

? ?存儲過程和函數的編寫比單句SQL語句復雜，需要用戶具有更高的技能和更豐富的經驗。

? ?在編寫存儲過程和函數時，需要創建這些數據庫對象的權限。

二、學習任務2：創建存儲過程和函數

存儲過程和函數的操作包括創建存儲過程和函數、查看存儲過程和函數、更新存儲過程和函數，以及刪除存儲過程和函數。本節將詳細介紹如何創建存儲過程和函數。

2.1?創建存儲過程語法形式

在MySQL中創建存儲過程通過SQL語句CREATE PROCEDURE來實現，其語法形式如下：

CREATE PROCEDURE procedure_name([procedure_parameter[,…]])

[characteristic…] routine_body;

在上述語句中，procedure_name參數表示所要創建的存儲過程名字，procedure_parameter參數表示存儲過程的參數，characteristic參數表示存儲過程的特性，routine_body參數表示存儲過程的SQL 語句代碼，可以用BEGlN...END來標志SQL語句的開始和結束。

注意：在具體創建存儲過程時，存儲過程名不能與已經存在的存儲過程名重名。除了上述要求外，推薦存儲過程名命名（標識符）為procedure_xxx或者proce_xxx；

procedure_parameter中每個參數的語法形式如下：

[IN|OUT|INOUT] parameter_name type

在上述語句中，每個參數由3部分組成，分別為輸入輸出類型、參數名和參數類型。其中輸入/ 輸出類型有3種類型，分別為IN：表示輸入類型；OUT：表示輸出類型；INOUT表示輸入/輸出類型。parameter_name表示參數名。type表示參數類型，可以是MySQL軟件所支持的任意一個數據類型。

characteristic參數的取值為:

LANGUAGE SQL

|[NOT] DETERMINISTIC

|{CONTAINS SQL|NO SQL|READS SQL DATA | MODIFIES SQL DATA}

|SQL SECURITY {DEFINER|INVOKER}

|COMMENT 'string'

當characteristic參數的值為上述各個值時,分別表不：

LANGUAGE SQL，表示存儲過程的routine_body部分由SQL語言的語句組成，為MySQL 軟件所有默認的語句。
[NOT] DETERMINISTIC，表示存儲過程的執行結果是否確定。如果值為DETERMINISTIC，表示執行結果是確定的。即每次執行存儲過程時，如果輸入相同的參數將得到相同的輸出；?如果值為NOT DETERMNISTIC，表示執行結果不確定，即相同的輸入可能得到不同的輸出。默認值為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',表示注釋語句。

2.2?創建函數語法形式

在MySQL中創建函數通過SQL語句CREATE FUNCTION來實現，其語法形式如下：

CREATE FUNCTION function _name([function_parameter[,…]]) [characteristic…] routine_body;

在上述語句中，function _name參數表示所要創建的函數名字；function_parameter參數表示函數的參數，characteristic參數表示函數的特性，該參數的取值與存儲過程中的取值相同。routine_body 參數表示函數的SQL語句代碼，可以用BEGEN...END來標示SQL語句的開始和結束。

注意：在具體創建函數時，函數名不能與已經存在的函數名重名，除了上述要求外，推薦函數名命名(標識符）為function_xxx或者func_xxx。

function_parameter中每個參數的語法形式如下：

parameter_name type

在上述語句中，每個參數由兩部分組成，分別為參數名和參數類型。parameter_name表示參數名。type表示參數類型，可以是MySQL軟件所支持的任意一個數據類型。

2.3?創建簡單的存儲過程和函數

前面兩節詳細介紹了關于存儲過程和函數的語法形式，本節將通過具體的實例來講述如下應用存儲過程和函數。

【實例14-1】執行SQL語句CREATE PROCEDURE,在數據庫company中，創建查詢雇員表?(t_employee)中所有雇員工資的存儲過程，具體如下：

執行SQL語句CREATE PROCEDURE，創建名為proce_employee_sal的存儲過程，具體SQL語句如下：

DELIMITER $$

CREATE PROCEDURE proce_employee_sal()

COMMENT '查詢所有雇員的工資'

BEGIN

SELECT sal FROM t_employee;

END$$

DELIMITER ；

【代碼說明】在上述代碼中，創建了一個名為proce_enployee_sal的存儲過程，主要用來實現通過SELECT語句從t_employee表中查詢sal字段值，實現査詢雇員工資功能。

【運行效果】執行上面的SQL語句，其結果如圖所示。

執行結果沒有顯示任何錯誤，表示該存儲過程對象proce_employee_sal已經創建成功。

通常在創建存儲過程時，經常通過命令“DELIMITER $$”將SQL語句的結束符由“;”符號修改成“$$”。這主要是因為SQL語句中默認語句結束符為分號（;），即存儲過程中的routine_body語句也需要用分號來結束，當將結束符號修改成“$$”符號后就可以在執行過程中避免沖突。不過最后一定不要忘記通過命令“DELIMITER ;”將結束符號修改成SQL語句中默認的結束符號。

【實例14-2】執行SQL語句CREATE FUNCTION,在數據庫company中，創建査詢雇員表(t_empIoyee)中某個雇員工資的函數，具體如下：

執行SQL語句CREATE FUNCTION,創建名為func_employee_sal的函數，具體SQL語句如下：

DELIMITER $$

CREATE FUNCTION func_employee_sal(empno INT(11))

RETURNS DOUBLE(10,2)

COMMENT“查詢某個雇員的工資”

BEGIN

RETURN (SELECT sal FROM?t_employee WHERE t_employee.empno=empno);

END$$

DELIMITER ;

【代碼說明】在上述代碼中，創建了一個名為func_employee_sal的函數，該函數擁有一個類型?為int(11)名為empno的參數，返回值為double（10,2）類型。SELECT語句從t_employee表中查詢empno字段值等于所傳入參數empno值的記錄，同時并將該條記錄的sal字段的值返回。?

【運行效果】執行上面的SQL語句，其結果如圖所示。

執行結果沒有顯示任何錯誤，表示該函數對象func_employee_sal已經創建成功。在具體創建函數時，與創建存儲過程一樣，也需要將SQL語句結束符由“;”符號?修改成“$$”，最后通過命令“DELIMITER ;”將結束符號修改成SQL語句中默認的結束符號。

三、學習任務3：關于存儲過程和函數表達式

在MySQL軟件中，除了支持標準的存儲過程和函數外，還對其進行了擴充，引入了表達式。表達式與其他高級語言的表達式一樣，由變量、運算符和流程控制來構成。本節將詳細介紹存儲過程和函數中所包含的表達式語句。

3.1?操作變量

變量是表達式語句中最基本的元素，可以用來臨時存儲數據。對于MySQL軟件來說，可以通過變量存儲從表中査詢到的數據等。下面將介紹如何聲名變量和給變量賦值。

1.聲明變量

在MySQL中定義變量通過關鍵字DECLARE來實現，其語法形式如下：?

DECLARE var_name[, …] type [DEFAULT value]

在上述語句中，var_name參數表示所要聲明的變量名字；參數TYPE表示所要聲明變最的類型; DEFAULT value用來實現設置變量的默認值，如果無該語句默認值為NULL。

注意：在具體聲明變量時，可以同時定義多個變量。

2.賦值變置

變量賦值通過關鍵字SET來實現，其語法形式如下：

SET var_name=expr[,…]

在上述語句中，var_name參數表示所要賦值變量名字；參數expr是關于變量的賦值表達式。

注意：在為變量賦值時，可以同時為多個變量執值，各個變量的賦值語句之間用逗號隔開。

為變量賦值時，除了上述語法外，還可以通過關鍵字“SELECT...INTO”語句來實現，其語法形式如下：

SELECT field_name[,...] INTO var_name[,…] FROM table_name?WHERE condition;

在上述語句中將查詢到結果賦值給變量，參數field_name表示查詢的字段名，參數var_name 參數表示變量名。

注意：當將查詢結果賦值給變量時，該查詢語句的返回結果只能是單行。

【實例14-4】下面將通過具體的實例來演示如何聲明變量和為變量賦值，具體步驟如下：

（1）執行帶有關鍵字DECLARE的語句，聲明一個名為employee_sal的變量，具體SQL語句如下：

DECLARE employee_sal INT DEFAULT 1000;

【代碼說明】上述語句聲明了一個表示雇員工資的變量employee_sal，并設置該變量的默認值為?1000。

（2）執行帶有關鍵字SET的語句，為變量employee_sal賦值，具體語句如下：

DECLARE employee_sal INT DEFAULT 1000;

SET employee_sal=3500;

【代碼說明】上述語句中首先聲明了一個表示雇員工資的變量employee_sal，其默認值為1000，?然后設置該變量的值為3500。

（3）將查詢結果賦值給變量，即將表t_employee中empno為“7566”記錄中字段sal的值賦值給變量employee_sal。具體SQL語句如下：

SELECT sal INTO employee_sal FROM t_employee WHERE empno=7566;

【代碼說明】在上述語句中通過SELECT...INTO語句將表t_employee里相應數據記錄中字段sal 的值賦值給變量employee_sal。

3.2?操作條件

在高級編程語言中為了提高語言的安全性，提供了異常處理機制。對于MySQL軟件，也提供了一種機制來提高安全性，即本節所要介紹的“條件”。條件的定義和處理可以用來定義在處理過程中遇到問題時相應的處理步驟。下面將介紹如何定義條件和設置處理程序。

1.定義條件

在MySQL中定義條件通過關鍵字DECLARE來實現，其語法形式如下：

DECLARE condition_name CONDITION FOR condition_value;

condition_value:

SQLSTATE[VALUE] sqlstate_value

| mysql_error_code

這個語句指定需要特殊處理的條件。它將一個名字和指定的錯誤條件關聯起來。這個名字可以隨后被用在DECLARE HANDLER語句中。除了SQLSTATE值，也支持MySQL錯誤代碼。

在上述語句中，condition_name參數表示所要定義的條件名稱；參數condition_value用來實現設置條件的類型；參數sqlstate_value和mysql_error_code都可以表示MySQL的錯誤。

例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。 ?

//定義"ERROR 1146 (42S02)"這個錯誤，名稱為can_not_find

//方法一：使用sqlstate_value ??

DECLARE ?can_not_find ?CONDITION ?FOR ?SQLSTATE ?'42S02' ; ??

//方法二：使用mysql_error_code ??

DECLARE ?can_not_find ?CONDITION ?FOR ?1146 ;

SQLSTATE 42S02為所有沒有該表錯誤的總稱: ?

mysql> SELECT * FROM FAN;

ERROR 1146 (42S02): Table 'fandb.FAN' doesn't exist

mysql> DROP TABLE FAN;

ERROR 1051 (42S02): Unknown table 'fandb.FAN' ?

Error: 1051 SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR) ?Message: Unknown table '%s'

Error: 1109 SQLSTATE: 42S02 (ER_UNKNOWN_TABLE) ?-multi delete時沒有該表 ?Message: Unknown table '%s' in %s ??

Error: 1146 SQLSTATE: 42S02 (ER_NO_SUCH_TABLE) ?Message: Table '%s.%s' doesn't exist

2.定義處理程序

在MySQL中定義條件通過關鍵字DECLARE ... HANDLER來實現，通過條件的定義和處理，可以在定義過程中，針對可能遇到的問題，做出相應的處理步驟。（也就是定義一個異常處理程序，指定當過程某條語句出錯時，相應的采取什么操作）其語法形式如下:

DECLARE handler_action?HANDLER FOR conition_value[,…]?statement

handler_?action:

CONTINUE

| EXIT

condition_value:

SQLSTATE[VALUE]?sqlstate_value

|condition_name ?

|SQLWARNING

|NOT FOUND

|SQLEXCEPTION

|mysql_error_code

注意：declare……handler語句必須出現在變量或條件聲明的后面。??

這個語句指定每個可以處理一個或多個條件的處理程序。如果產生一個或多個條件，指定的語句被執行。當某個錯誤（condition_value）發生時--->執行指定的語句（statement--記錄錯誤信息），執行完之后再決定如何操作（handler_action）。??

（1）handler_action ?

continue：繼續執行當前的程序(接著執行出錯的SQL的下一條語句)； ?　　　　

exit：當前程序終止(退出當前declare所在的begin end)； ?

（2）statement ?　　　　

可以是單條語句或復合語句。 ?

condition_value參數指明錯誤類型

指明handler被何種條件觸發；如果條件被觸發，卻沒有handler被聲明用于處理該條件，程序的進行將取決于條件類型。

condition_value該參數有6個取值：

sqlstate_value和mysql_error_code與條件定義中的是同一個意思，SQL語句過程中出現錯誤時返回的 5 個字符的錯誤代碼。

condition_name是DECLARE定義的條件名稱。

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

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

SQL?EXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。statement表示一些存儲過程或函數的執行語句。

例句：

//定義處理程序的幾種方式

//方法一：捕獲sqlstate_value ??

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' ?SET @info='CAN NOT FIND'; ??

//方法二：捕獲mysql_error_code ??

DECLARE CONTINUE HANDLER FOR 1146 SET @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';

第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42S02，執行CONTINUE操作，并且返回”CAN?NOT FIND”信息。

第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146，執行CONTINUE操作，并且返回”CAN NOT FIND”信息。

第三種方法是先定義條件，然后再調用條件。這里先定義can_not_find條件，遇到1146錯誤就執行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”信息。

3.3?異常處理程序

1.單個異常處理程序

例子1：

CREATE TABLE test(s1 int,primary key (s1));

delimiter // ?

CREATE PROCEDURE small_mistake1() ???

BEGIN ???

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; ???

SET @x = 1; ????

INSERT INTO test.t VALUES (1); ???

SET @x = 2; ??

INSERT INTO test.t VALUES (1); ??

SET @x = 3; ?

END//

CALL small_mistake1()

Select?@x?

注意：@x是3，這表明MySQL被執行到程序的末尾。

如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; 這一行不在，第二個INSERT因PRIMARY KEY強制而失敗之后,MySQL可能已經采取默認(EXIT)路徑，并且SELECT @x可能已經返回2。

例子2：提交重復的數據記錄（continue/exit）

mysql> DELIMITER $$

mysql> CREATE PROCEDURE small_mistake2() ??????

-> BEGIN ????

->?DECLARE CONTINUE(EXIT)?HANDLER FOR SQLSTATE '23000' ?； ???

-> SET error = '23000'; ???#用來記錄錯誤發生時的一些信息 ???

-> select error; ????

-> SET error = '00000'; ????

-> select error; ????

-> INSERT INTO TEAMS VALUES(2,27,'third'); ?#會出錯的語句 ????

-> SET error = '23001'; ?????????

-> END?$$

mysql> DELIMITER ;

begin end塊里，定義declare……handler語句用來捕獲錯誤(待命)，select、set、select順序執行，insert語句出錯，SQLSTATE碼23000，捕獲，進行異常處理(賦值記錄)，（continue）結束后會繼續執行出錯的insert語句的下一條語句（exit）異常處理結束后不會繼續執行錯誤語句后面的語句，直接退出begin end語句塊。

2.多個異常處理程序

mysql> INSERT INTO TEAMS VALUES(2,27,'third',5);

ERROR 1136 (21S01): Column count doesn't match value count at row 1 ?

mysql> DELIMITER $$

mysql> CREATE PROCEDURE small_mistake3() ??????

-> BEGIN ????

->?DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' ??????

->?SET @error = '23000'; ?????

->?DECLARE CONTINUE HANDLER FOR SQLSTATE '21001' ?????

->?SET @error = '21001'; ????

-> INSERT INTO TEAMS VALUES(2,27,'third',5); ?#錯誤語句 ???????

-> END$$

mysql> DELIMITER?;

mysql> call small_mistake3();

mysql> select @error;

3.復合異常語句

delimiter $$ ?

create procedure small_mistake4()

begin ????

declare duplicatekely condition for sqlstate '23000'; ????

declare continue handler for duplicatekey begin select 'hello world';set @b=8;end; ????

set @a=1; ????

insert into table?values(1); ????

set @a=2; ????

insert into?table?values(2); ????

set @a=3; ????

insert into?table?values(3);

end $$ ?

delimiter ;

mysql> delete from table?where id in (1,3);

mysql> call small_mistake4 ();

mysql> select @a,@b;

mysql> select * from table;

注意：DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

也就是說，當遇到SQLWARNING的問題時，進行的異常處理是begin end塊，因為里面什么都沒有，就類同于直接忽略。

3.4?使用游標

通過前面章節的知識可以知道，MySQL軟件的査詢語句可以返回多條記錄結果，那么在表達式中如何遍歷這些記錄結果呢？MySQL軟件提供了游標來實現。通過指定由SELECT語句返回的行集合（包括滿足該語句的WHERE子句所列條件的所有行)，由該語句返回完整的行集合叫作結果集。應用程序需要一種機制來一次處理結果集中的一行或連續的幾行，?而游標通過每次指向一條記錄完成與應用程序的交互。

游標可以看作一種數據類型，可以用來遍歷結果集，相當于指針，或者是數組中的下標。處理結果集的方法可以通過游標定位到結果集的某一行，從當前結果集的位置搜索一行或一部分行或者對結果集中的當前行進行數據修改。

下面將介紹如何聲明游標、打開游標、使用游標和關閉游標。

1.聲明游標

在MySQL中聲明游標通過關鍵字DECLARE來實現，其語法形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

在上述語句中，cursor_name參數表示游標的名稱，參數select_statement表示SELECT語句。因為游標需要遍歷結果集中的每一行，增加了服務器的負擔，導致游標的效率并不高效。如果游標操作的數據超過1萬行，那么應該采用其他方式，另外如果使用了游標，還應盡量避免在游標循環中進行表連接的操作。

2.打開游標

在MySQL中打開游標通過關鍵字OPEN來實現，其語法形式如下：

OPEN cursor_name

在上述語句中，MySQL參數表示所要打開游標的名稱。注意打開一個游標時，游標并不指向第一條記錄，而是指向第一條記錄的前邊。

3.使用游標

在MySQL中使用游標通過關鍵字FETCH來實現，其語法形式如下：

FETCH cursor_name INTO var_name [,var_name] ...

在上述語句中，將參數游標cursor_name中SELECT語句的執行結果保存到變量參數var_name 中。變量參數var_name必須在游標使用之前定義。使用游標類似高級語言中的數組遍歷，當第一次使用游標時，此時游標指向結果集的第一條記錄。

4.關閉游標

在MySQL中關閉游標通過關鍵字CLOSE來實現，其語法形式如下：?

CLOSE cursor_name

在上述語句中，cursor_name參數表示所要關閉游標的名稱。

【實例14-5】此實例實現的功能為統計工資大于999的雇員人數，此功能可以直接通過WHERE 條件和COUNT（）函數直接完成，此實例主要為演示游標的使用方法。下面將通過此實例的實現過程來演示如何使用游標，具體步驟如下：

（1）執行帶有關鍵字DECLARE的語句，聲明一個名為cursor_employee的游標，具體SQL語如下：

DECLARE cursor_employee?CURSOR FOR select sal?FOR?t_employee;

Open?cursor_employee;

【代碼說明】在上述語句中打開名為cursor_employee的游標。

（2）執行帶有關鍵字FETCH的語句，通過游標cursor_employee將查詢結果賦值給變量，即將表t_employee表中所有記錄中字段sal的值賦值給變量employee_sal具體SQL語句如下：

FETCH cursor_emplayee INTO employee_sal;

【代碼說明】在上述語句中，將游標cursor_employee的查詢結果賦值給變量employee sal。?

（3）執行帶有關鍵字CLOSE的語句，關閉游標cursor_employee,具體SQL語句如下：

CLOSE cursor_employee;

【代碼說明】在上述語句中，關閉名為cursor_employee的游標。在具體使用游標時，游標必須在處理程序之前且在變量和條件之后聲明，并且最后一定要關閉游標。經過上面的介紹，本實例完成的代碼如下：

DELIMITER $

#創建存儲過程

CREATE PROCEDURE employee_count (OUT NUM INTEGER)

BEGIN

#聲明變量

DECLARE employee_sal INTEGER;

DECLARE flag INTEGER;

#聲明游標

DECLARE cursor_employee?CURSOR FOR SELECT sal FROM t_employee;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

#設里初始變量

SET flag=0;

SET NUM=0;

#打開游標

OPEN cursor_employee;

#遍歷游標指向的結果集

FETCH cursor_employee INTO employee_sal;

WHILE flag=0 DO

IF employee_sal >999 THEN SET num=num+1;

END IF;

FETCH cursor_emtployee INTO employee_sal;

END WHILE;

#關閉游標

CLOSE cursor_employee;

END

DELIMITER ;

上述實例創建了一個存儲過程，并使用游標遍歷結果集中的每一行，如果發現工資大于999, 則變量num加1，最后統計出符合條件的記錄條數。如需調用此存儲過程，可以使用以下方法：?

#調用存儲過程

mysql> CALL employee_count(@count);

mysql> select @count;

除了使用WHILE...END WHILE遍歷結果集以外，游標的遍歷還有以下幾種方式。

LOOP…END LOOP
REPEAT…END REPEAT

使用LOOP循環遍歷重寫實例14-5如下:

DROP PROCEDURE IF EXISTS employee_count;

DELIMITER $

#創建存儲過程

CREATE PROCEDURE employee_count(OUT NUM INTEGER)

BEGIN

#聲明變量

DECLARE employee_sal INTEGER;

DECLARE flag INTEGER;

#聲明游標

DECLARE cursor_employee

CURSOR FOR SELECT sal FROM t_employee;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;

#設置結束標志

SET flag=0;

SET NUM=O;

#打開游標

OPEN cursor_employee;

#遍歷游標

FETCH cursor_employee INTO employee_sal;

loop_label:LOOP

IF employee_sal >999 THEN SET num=num+l;

END IF;

FETCH cursor_employee INTO employee_sal;

if(flag=1) then LEAVE loop_label;

end if;

END LOOP;

#關閉游標

CLOSE cursor__employee;

END$

DELMITER ;

使用REPEAT…UNTIL循環遍歷重寫實例14-5如下:

DROP PROCEDURE IF EXISTS employee__count;

DELIMITER $

#創建存儲過程

CREATE PROCEDURE employee_count(OUT NUM INTEGER)

BEGIN

#聲明變量

DECLARE employee_sal INTEGER;

DECLARE flag INTEGER;

#聲明游標

DECLARE cursor_employee CURSOR FOR SELECT sal FROM t_employee;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;

#設置結束標志

SET flag=0;

SET NUM=0;

#打開游標

Open?cursor_employee；

#遍歷游標

FETCH cursor_employee INTO errployee_sal;

REPEAT

IF employee_sal >999 THEN SET num=num+l;

END IF;

FETCH cursor_employee INTO employee_sal;

UNTIL flag=1 END REPEAT;

#關閉游標

CLOSE cursor__employee;

END?$

DELIMITER ;

以上的介紹說明了聲明游標、打開游標、使用游標和關閉游標這幾個概念，并通過一系列實例演示了游標的使用方法。由于使用游標需要把結果集中的記錄一條條取出來處理，增加了服務器的負擔，使用游標處理結果集效率比較低。所以如果可以不使用游標就盡量不要使用。

3.5?使用流程控制

流程控制語句主要用來實現控制語句的執行順序，例如順序、條件和循環。可以通過關鍵字IF和CASE來實現條件控制，關鍵字LOOP、WHILE和REPEAT實現循環控制。下面將介紹如何實現條件控制語句和循環控制語句。

1.條件控制語句

在MySQL軟件中可以通過關鍵字IF和CASE來實現條件控制語句，IF語句具體進行條件控制時，根據是否滿足條件，執行不同的語句；而對于CASE語句則可以實現更復雜的條件控制。下面將詳細條件控制語句：

在MySQL中實現條件判斷通過關鍵字lF來實現，其語法形式如下：

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list]…

[ELSE search_condition]

END IF

上述語句中search_condition參數表示條件的判斷；參數statement_list表示不同條件的執行語句。在MySQL中實現條件判斷還可以通過關鍵字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表示不同條件的執行語句。

2.循環控制語句

在MySQL軟件中可以通過關鍵字LOOP、WHILE和REPEAT來實現循環控制語句，其中后兩個關鍵字用來實現帶有條件的循環控制語句，即對于關鍵字WHILE，只有在滿足條件的基礎上才執行循環體，而關鍵字REPEAT，則是在滿足的條件時退出循環體。

MySQL實現循環執行通過關鍵字LOOP來實現，其語法形式如下：

[begin_label:] LOOP

statement_list

END LOOP [end_label]

在上述語句中，begin_label和end_label參數分別表示循環開始和結束的標志，這兩個標志必須相同，并且可以省略。關鍵字LOOP表示循環體的開始，END LOOP表示循環體的結束。參數?statement_list表示所執行的循環體語句。

對于循環語句，如果想實現退出正在執行的循環體，可以通過關鍵字LEAVE來實現，其語法形式如下：

LEAVE label

在上述語句中，參數label表示循環的標志#

在MySQL中實現循環執行還可以通過關鍵字WHILE來實現，不過其是帶有條件控制的循環，即當滿足條件是才執行循環體語句，具體語法形式如下：

[begin_label:] WHILE search_condition DO

statement_list

END WHILE [end_label]

在上述語句中，參數search_condition表示循環的執行條件，當滿足該條件時才執行循環體?statement_Iist。

還可以通過關鍵字REPEAT來實現，其同樣也是帶有條件控制的循環，不過當滿足條件則跳出循環體

語句，具體語法形式如下：

[begin_label:] REPEAT

statement_list

END REPEAT [end_label]

四、學習任務4：存儲過程詳解

4.1?存儲過程的參數

存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，如果有多個參數用","分割開。MySQL存儲過程的參數，共有三種參數類型,IN,OUT,INOUT: ?

IN參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為默認值。

OUT參數的值可在存儲過程內部被改變，并可返回。

INOUT參數的值調用時指定，并且可被改變和返回。 ?

IN參數例子 ?

DELIMITER $$ ??

CREATE PROCEDURE in_param(IN p_in int) ????

BEGIN ????

SELECT p_in; ????

SET p_in=2; ????

SELECT p_in; ????

END; ????

DELIMITER ;

#調用

SET @p_in=1;

CALL in_param(@p_in);

SELECT @p_in;

執行結果： ???

以上可以看出，p_in雖然在存儲過程中被修改，但并不影響@p_in的值。 ?

OUT參數例子?

#存儲過程OUT參數

DELIMITER?$$???

CREATE PROCEDURE out_param(OUT p_out int) ????

BEGIN ??????

SELECT p_out; ??????

SET p_out=2; ??????

SELECT p_out; ????

END ???

DELIMITER?;

#調用

SET @p_out=1;

CALL out_param(@p_out);

SELECT @p_out;

執行結果： ????

INOUT參數例子?

#存儲過程INOUT參數

DELIMITER $$???

CREATE PROCEDURE inout_param(INOUT p_inout int) ????

BEGIN ??????

SELECT p_inout; ??????

SET p_inout=2; ??????

SELECT p_inout; ????

END ???

$$?

DELIMITER ;

#調用

SET @p_inout=1;

CALL inout_param(@p_inout) ;

SELECT @p_inout;

執行結果：

注意：用戶變量一般以@開頭

（濫用用戶變量會導致程序難以理解及管理甚至報錯） ?#在MySQL客戶端使用用戶變量

SELECT 'Hello World' into @x;

SELECT @x; ?

SET @y='Goodbye Cruel World';

SELECT @y;

SET @z=1+2+3;

SELECT @z;

執行結果：

MySQL注釋方法：

#單行注釋 ?

-- 單行注釋（特別注意，-- 后有個空格！！！）

/*多行注釋*/

4.2?存儲過程的控制語句

1、變量作用域

內部變量在其作用域范圍內享有更高的優先權，當執行到end時，內部變量消失，不再可見了，在存儲過程外再也找不到這個內部變量，但是可以通過out參數或者將其值指派給會話變量來保存其值。

#變量作用域

DELIMITER // ??

CREATE PROCEDURE proc() ????

BEGIN ??????

DECLARE x1 VARCHAR(5) DEFAULT 'outer'; ????????

BEGIN ??????????

DECLARE x1 VARCHAR(5) DEFAULT 'inner'; ??????????

SELECT x1; ????????

END; ??????

SELECT x1; ????

END??

DELIMITER ;

#調用 CALL proc();

執行結果：

2、條件語句

（1）IF-THEN-ELSE語句（舉例）

#條件語句IF-THEN-ELSE

DELIMITER //

CREATE PROCEDURE proc1(IN parameter int) ??

BEGIN ????

DECLARE var int; ????

SET var=parameter+1; ????

IF var=0 THEN ??????

INSERT INTO table VALUES (17); ????

END IF ; ????

IF parameter=0 THEN ??????

INSERT INTO table VALUES (18); ???

ELSE ??????

INSERT INTO table VALUES (19);??

END IF ; ??

END ?

DELIMITER ;

（2）CASE-WHEN-THEN-ELSE語句??

#CASE-WHEN-THEN-ELSE語句

DELIMITER // ??

CREATE PROCEDURE proc2?(IN parameter INT) ????

BEGIN ??????

DECLARE var INT; ??????

SET var=parameter+1; ??????

CASE var ????????

WHEN 0 THEN ??????????

INSERT INTO t VALUES (17); ????????

WHEN 1 THEN ??????????

INSERT INTO t VALUES (18); ????????

ELSE ??????????

INSERT INTO t VALUES (19); ??????

END CASE ; ????

END //

DELIMITER ; ?

（3）循環語句 WHILE-DO…END-WHILE?

DELIMITER // ??

CREATE PROCEDURE proc3() ????

BEGIN ??????

DECLARE var INT; ??????

SET var=0; ??????

WHILE var<6 DO ????????

INSERT INTO t VALUES (var); ????????

SET var=var+1; ??????

END WHILE ; ????

END //

DELIMITER ; ?

（4）REPEAT...END REPEAT?

此語句的特點是執行操作后檢查結果

DELIMITER // ??

CREATE PROCEDURE proc4() ????

BEGIN ??????

DECLARE v INT; ??????

SET v=0; ??????

REPEAT ????????

INSERT INTO table VALUES(v); ????????

SET v=v+1; ????????

UNTIL v>=5 ??????

END REPEAT; ????

END //

DELIMITER ; ?

（5）LOOP...END LOOP?

DELIMITER // ??

CREATE PROCEDURE proc5() ????

BEGIN ??????

DECLARE v INT; ??????

SET v=0; ??????

LOOP_LABLE:LOOP ????????

INSERT INTO table?VALUES(v); ????????

SET v=v+1; ????????

IF v >=5 THEN ??????????

LEAVE LOOP_LABLE; ???????

END IF; ??????

END LOOP; ????

END //

DELIMITER ;

五、學習任務5：函數詳解

（1）順序結構

先來一個簡單的，創建一個函數將'2009-06-23 00:00:00'這樣格式的datetime時間轉化為‘2009

年6月23日0時0分0秒’這樣的格式：

DELIMITER $$

CREATE FUNCTION func1(gdate datetime)

RETURNS varchar(255)

BEGIN ?

DECLARE x VARCHAR(255) DEFAULT ''; ?

SET x= date_format(gdate,'%Y年%m月%d日%h時%i分%s秒'); ?

RETURN? x;

END $$

DELIMITER ;

（2）分支結構

DELIMITER $$

CREATE FUNCTION? func2(s VARCHAR(255),n INT)

RETURNS varchar(255)

BEGIN ?

IF(ISNULL(s))

THEN RETURN ''; ?

ELSEIF CHAR_LENGTH(s) < n

THEN RETURN s; ?

ELSE

RETURN CONCAT(LEFT(s,n),'...'); ?

END IF;

END $$

DELIMITER ;

解析：這是一個截取字符串的函數，參數有兩個，一個是字符串s，一個是數字n，將字符串s保留前n位，如果字符串的個數小于n，則返回字符串s，如果大于n，則返回前n位后面加...。

調用：SELECT func2('abcdefghijklmnopqrstuvwxyz',5); 返回 'abced...'

循環結構

DELIMITER $$

CREATE FUNCTION func3(n INT)

RETURNS text

BEGIN ?

DECLARE i INT DEFAULT 0; ?

DECLARE s TEXT DEFAULT ''; ?

myloop:LOOP ???

SET i=i+1; ???

SET s = CONCAT(s,'*'); ???

IF i > n

THEN LEAVE myloop; ???

END IF; ?

END LOOP myloop; ?

RETURN s;

END $$

DELIMITER ;

解析：產生n+1個*。調用：SELECT func3(3)；返回 '*****'

六、學習任務6：查看存儲過程和函數

存儲過程和函數的操作包括創建存儲過程和函數、查看存儲過程和函數、更新存儲過程和函數，?以及刪除存儲過程和函數。本節將詳細介紹如何查看存儲過程和函數。在MySQL軟件中可以通過三種方式來查看存儲過程和函數，分別為通過SHOW STATUS語句查看存儲過程和函數狀態信息、通過系統表information_schema.routines查看存儲過程和函數詳細信息和通過SHOW CREATE語句查看存儲過程和函數定義信息。

5.1?SHOW PROCEDURE /FUNCTION STATUS語句查看存儲過程狀態信息

（1）當創建存儲過程時，如果數據庫中已經存在該存儲過程，則會發生提示存儲過程已經存在錯誤語句。

為了避免上述錯誤，對于有經驗的用戶，當在創建存儲過程之前，需要查看MySQL軟件中是否已經存在該標識符的存儲過程。那么如何査看mySQL軟件中已經存在的存儲過程呢？在MySQL軟件中查看已經存在的存儲過程通過SQL語句SHOW PROCEDURE來實現，其語法形式如下：

SHOW PROCEDURE STATUS [LIKE'pattern'] \G

在上述代碼中，關鍵字SHOW PROCEDURE STATUS表示實現查看存儲過程功能，參數LIKE 'pattern'用來設置所要查詢的存儲過程名稱。

執行SQL語句SHOW PROCEDURE STATUS,在數據庫company里查詢存儲過程對象proce_employee_sal,具體步驟如下：

SHOW PROCEDURE STATUS LIKE 'proce_employee_sal'\G

（2）當在創建函數之前，需要査看MySQL軟件中是否已經存在該標識符的函數。那么如何查看MySQL軟件中已經存在的函數？在MySQL軟件中查看己經存在函數通過SQL語句SHOW FUNCTION來實現，其語法形式如下：?

SHOW FUNCTION STATUS [LIKE 'pattern'] \G

在上述代碼中，關鍵字SHOWFUNCTION STATUS表示實現査看函數功能，參數LIKE‘pattem’用來設置所要查詢的函數名稱。

執行SQL語句SHOW FUNCTION STATUS,在數據庫company中查詢函數對象?func_employee_sal,具體如下：

SHOW FUNCTION STATUS LIKE'func_employee_sal \G

執行結果顯示了所指定函數對象func_employee_sal的各種詳細信息。

5.2?通過查看系統表information_schema.routines實現查看存儲過程和函數信息

在MySQL軟件中，在系統數據庫information_schema中存在一個存儲所有存儲過程和函數信息的系統表routines,因此查詢該表格的記錄也可以實現查看存儲過程和函數功能。關于系統表routines 的表結構如圖所示。

執行SQL語句SELECT，查看系統表routines中的所有記錄，具體SQL語句如下:

SELECT * FROM routines \G

執行結果顯示了?MySQL軟件中所有的存儲過程和函數對象的詳細信息，除了顯示所有存儲過程和函數對象外，還可以査詢指定存儲過程和函數的詳細信息。通過系統表routines查詢關于存儲過程對象proce_employee_sal的信息，具體SQL語句如下：

SELECT*?FROM ROUTINES?WHERE SPECIFIC_NAME='func_employee_sal' \G

執行結果顯示了所指定函數func_employee_sal的詳細信息，與前面的方式相比，使用起來更加方便、靈活。

對于MySQL軟件用戶來說，很少使用語句“SHOW PROCEDURE”、“SHOW FUNCTION”和語句“SELECT * FROM routines\G”來查詢存儲過程和函數的詳細信息，因為在MySQL軟件中，隨著時間的推移，數據庫對象存儲過程和函數肯定會增多，如果査詢所有存儲過程和函數的詳細信息，將顯示許多許多的信息，不便于找到所需的存儲過程和函數的信息。

5.3?通過SHOW CREATE PROCEDURE/FUNCTION語句查看對應定義信息

（1）除了上述兩種方式來査看存儲過程對象外，對于有經驗的用戶，當在創建存儲過程之前，還可以通過關鍵字SHOW CREATE PROCEDURE來査看存儲過程定義信息，其語法形式如下：

SHOW CREATE PROCEDURE proce_name \G

在上述代碼中，關鍵字SHOW CREATE PROCEDURE表示實現查看存儲過程定義信息，參數?proce_name用來設置所要查詢的存儲過程名稱。

執行SQL語句SHOW CREATE PROCEDURE,查詢存儲過程對象proce_employee_sal,具體SQL語句如下：

SHOW CREATE PROCEDURE proce_employee_sal\G

執行結果會顯示了所指定存儲過程對象proce_employee_sal的定義詳細信息。

（2）還可以通過關鍵字SHOW CREATE FUNCTION來查看函數定義信息，其語法形式如下：

SHOW CREATE FUNCTION func_name \G

七、學習任務7：修改存儲過程和函數

對于已經創建好的存儲過程和函數，當使用一段時間后，就會需要進行一些定義上的修改。在?MySQL軟件中，可以通過ALTER PROCEDURE語句實現修改存儲過程，可以通過ALTER FUNCTION語句實現修改函數。

7.1?修改存儲過程

在MySQL數據庫管理系統中修改存儲過程通過SQL語句ALTER PROCEDURE來實現，其語法形式如下：

ALTER PROCEDURE procedure_name [characteristic…]

在上述語句中，procedure_name參數表示所要修改存儲過程的名字，而characteristic參數指定修改后存儲過程的特性，與定義存儲過程的該參數相比，取值只能是如下值：

|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}

|SQL SECURITY {DEFINER|INVOKER}

|COMMENT 'string'

執行SQL語句ALTER TABLE修改表t_dqrt的名字為tab_dept,具體SQL語句如下:

ALTER TABLE t_dept RENAME tab_dept;

7.2?修改函數

在MySQL數據庫管理系統中修改函數通過SQL語句ALTER FUNCTION來實現，其語法形式如下：

ALTER FUNCTION function_name[characteristic…]

在上述語句中，function_name參數表示所要修改函數的名字，而characteristic參數指定修改后函數的特性，與定義函數的該參數相比，取值只能是如下值：

|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}

|SQL SECURITY {DEFINER|INVOKER}

|COMMENT 'string'

注意：所要修改函數必須在數據庫中已經存在。

八、學習任務8：刪除存儲過程和函數

存儲過程和函數的操作包括創建存儲過程和函數、查看存儲過程和函數、更新存儲過程和函數，以及刪除存儲過程和函數。本節將詳細介紹如何刪除存儲過程和函數。在MySQL軟件中可以通過兩種方式來刪除存儲過程和函數，分別為通過drop?procedure/frunction語句和通過工具實現刪除存儲過程和函數。

（1）在MySQL中刪除存儲過程通過SQL語句DROP PROCEDURE來實現，其語法形式如下：

DROP PROCEDURE proce_name;

在上述語句中，關鍵字DROP PROCEDURE用來表示實現刪除存儲過程，proce_name參數表示所要刪除的存儲過程名稱。

（2）在MySQL中刪除存儲過程函數通過SQL語句DROP FUNCTION來實現，其語法形式如下：

DROP FUNCTION func_name;

在上述語句中，關鍵字DROP FUNCTION用來表示實現刪除函數，func_name參數表示所要刪除的函數名稱。

總結

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

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