8、MySLQ存儲過程

簡述

存儲過程是SQL語句和控制流語句的語句串（語句集合）。它不僅可以帶有輸入

參數(shù)還可以帶有輸出參數(shù)，存儲過程是能夠通過介紹參數(shù)向調(diào)用者返回結(jié)果集，結(jié)果集的格式由調(diào)用者確定。返回狀態(tài)值給調(diào)用者，指明調(diào)用是成功或是失敗，包括針對數(shù)據(jù)庫的操作語句，并且可以在一個存儲過程中調(diào)用另一存儲過程。

較SQL語句存儲過程的優(yōu)點：

1、存儲過程允許組件是編程，存儲過程在被創(chuàng)建以后，可以在程序中多次調(diào)用，而不必重新編寫存儲過程的SQL語句，從而提高了程序的可移植性。

2、存儲過程能夠?qū)崿F(xiàn)較快的存儲速度。

3、存儲過程能夠減少網(wǎng)絡(luò)流量。對于針對數(shù)據(jù)庫對象的相同操作如查詢，修改表如果這一操作所涉及的SQL語句被組織成存儲過程，那么當(dāng)計算機(jī)調(diào)用該存儲過程時，網(wǎng)絡(luò)中傳送的只是該調(diào)用語句，而不是多條SQL語句，從而大大提高網(wǎng)絡(luò)流量見底網(wǎng)絡(luò)負(fù)載。

4、存儲過程可被作為一種安全機(jī)制。

一、存儲過程的創(chuàng)建

1.1 存儲過程語法：

create procedure <過程名>(參數(shù)1、參數(shù)2、...)
begin
sql;
end

創(chuàng)建存儲過程之前我們必須使用delimiter修改MySQL語句的默認(rèn)結(jié)束符，否則不能創(chuàng)建成功。

存儲過程語句的注釋：

MySQL注釋的兩種風(fēng)格

"--":單行注釋

/*......*/：一般用于多行注釋

語法：

delimiter <新執(zhí)行符號>
delimiter //將默認(rèn)結(jié)束符修改為//。

1.2 調(diào)用存儲過程

call <過程名>(參數(shù)1、參數(shù)2、...);

1.3 存儲過程參數(shù)類型

1.3.1 IN參數(shù)

作用：讀取外部變量值，且有效范圍僅限于存儲過程內(nèi)部

mysql> delimiter //
mysql> create procedure pin(in p_in int)
    -> begin
    -> select p_in;
    -> set p_in=2;
    -> select p_in;
    -> end;
    -> //

等同于 set @p_in=1;

call pin(2)與select @p_in結(jié)果作比較

實例1 存儲過程的簡單創(chuàng)建和調(diào)用

mysql> delimiter %
mysql> create procedure selcg()
    -> begin
    -> select * from category;
    -> end %
Query OK, 0 rows affected (0.00 sec)
mysql> call selcg()%
+---------+---------------+
| bTypeId | bTypeName     |
+---------+---------------+
|       1 | windows應(yīng)用   |
|       2 | 網(wǎng)站          |
|       3 | 3D動畫        |
|       4 | linux學(xué)習(xí)     |
|       5 | Delphi學(xué)習(xí)    |
|       6 | ***          |
|       7 | 網(wǎng)絡(luò)技術(shù)      |
|       8 | 安全          |
|       9 | 平面          |
|      10 | AutoCAD技術(shù)   |
+---------+---------------+
10 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

定義存儲過程getonebook，當(dāng)輸入書的ID后可以調(diào)用處對應(yīng)的書籍記錄

mysql> delimiter //
mysql> create procedure getonebook(in id int)
    -> begin
    -> select * from books where bId=id;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call getonebook(4)
    -> ;
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
| bId | bName                           | bTypeId | publishing            | price | pubDate    | author    | ISBN       |
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
|   4 | pagemaker 7.0短期培訓(xùn)教程       | 9       | 中國電力出版社        |    43 | 2005-01-01 | 孫利英    | 7121008947 |
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>

1.3.2 Out參數(shù)

作用：不都去外部變量值，在存儲過程執(zhí)行完畢后保留新值。

實例2、

mysql> delimiter //
mysql> create procedure pout(out p_out int)
    -> begin
    -> select p_out;
    -> set p_out=2;
    -> select p_out;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> set @p_out=1;

執(zhí)行call @p_out存儲過程之后，再次使用select @p_out則顯示為2;則說明此時變量@P_out已經(jīng)被賦予2；

1.3.3 如何調(diào)用存儲過程out類型的返回值

例4、編輯存儲過程，使返回值是書名相關(guān)信息。

mysql> delimiter //
mysql> create procedure demo(out pa varchar(200))
    -> begin
    -> select bName into pa from books where bId=2;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call demo(@a);
Query OK, 1 row affected (0.00 sec)
mysql> select @a;
+-----------------------+
| @a                    |
+-----------------------+
| ***與網(wǎng)絡(luò)安全        |
+-----------------------+
1 row in set (0.00 sec)

1.3.4 Inout參數(shù)

作用：讀取外部變量，在存儲過程執(zhí)行完畢后保留心新值<類似銀行存款>

mysql> create procedure pinout(inout p_inout int)
    -> begin
    -> select p_inout;
    -> set p_inout=2;
    -> select p_inout;
    -> end;
    -> //

1.3.5 不加參數(shù)的存儲過程

如果存儲國恒在創(chuàng)建的時候沒有指定參數(shù)類型，則需要在調(diào)用的時候指定參數(shù)值。

mysql> create table t1(id int(10));
Query OK, 0 rows affected (0.03 sec
mysql> create procedure t2(n1 int)                                                                                             
    -> begin
    -> set @x=0;
    -> repeat set @x=@x+1;
    -> insert into t2 values(@x);
    -> until @x>n1
    -> end repeat;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> create table t2(id int(10));
Query OK, 0 rows affected (0.03 sec)

mysql> call t2(10); 循環(huán)10次
Query OK, 1 row affected (0.03 sec)
結(jié)果驗證
mysql> select * from t2;

二、存儲過程變量的使用

2.1 使用declare進(jìn)行變量定義

變量定義：declare variable_name [,variable_name......]

datatype [default value];
datatype為MySQL的數(shù)據(jù)類型，如int，float，date，varchar(length)等
作用:變量賦值可以在不同額存儲過程中的繼承
create procedure decl()
mysql> delimiter //
mysql> create procedure decl()
    -> begin
    -> declare name varchar(20);
    -> set name=(select bName from books where bI=12);
    -> select name;
    -> end//
Query OK, 0 rows affected (0.00 sec)

二、存儲過程的流程控制語句

2.1 BEGIN......END語句

定義由順序執(zhí)行的SQL語句構(gòu)成的塊。

語法格式：

BEGIN
Statement Block
END

2.2 IF...ELSE語句

該語句用來定義有條件執(zhí)行的某些語句，其中ELSE語句是可選擇的

語法格式：

IF Boolean_expression
statement
[ELSE [IF boolean_expression] statentent]

2.3 循環(huán)語句

1、while......end while:

while 1 do ... if *** then break;end while

2、repeat ......end repeat：

執(zhí)行操作后檢查結(jié)果，而while則是執(zhí)行前進(jìn)行檢查

3、loop......end loop:

loop循環(huán)不需要初始化條件，類似while循環(huán)，同時repeat循環(huán)一樣不需要結(jié)束條件，leave語句的意義是離不開循環(huán)。

4、LABLES標(biāo)號

可以用在begin repeat while 或者loop語句前，語句標(biāo)號只能在合法的語句前使用?？梢蕴鲅h(huán)，使運行指令達(dá)到符合語句的最后異步

5、ITERATE迭代

通過引用符合語句的標(biāo)號，來重新開始符合語句

查看存儲過程：

show create procedure demo \G

查看所有存儲過程

mysql> show procedure status\G;

修改存儲過程：

使用alter語句修改

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參數(shù)表示存儲過程或函數(shù)名稱

characreristic參數(shù)指定存儲過程函數(shù)的特性。

CONTAINS SQL表示子進(jìn)程包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句；

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

READSSQL DATA表示子程序博阿寒寫數(shù)據(jù)的語句。

SQL SECURITY {DEFINER|INVOKER}指明誰有權(quán)限來執(zhí)行

DEFINER表示只有定義者自己才能夠執(zhí)行

INVOKER表示調(diào)用者可以執(zhí)行

COMMENT 'string'是注釋信息

刪除存儲過程

語法一：drop procedure sp_name

語法二：drop procedure if exists sp_name

注：不能在一個存儲過程中刪除另一個存儲過程，只能調(diào)用另一個存儲過程

事務(wù)是由一組SQL語句組成的邏輯處理單元，要不全成功要不全失敗。

事務(wù)處理：可以確保非事務(wù)性單元的多個操作都能夠成功完成，否則不會更新數(shù)據(jù)資源。

數(shù)據(jù)庫默認(rèn)事務(wù)是自動提交的， 也就是發(fā)一條 sql 它就執(zhí)行一條。如果想多條 sql 放在一個事務(wù)中執(zhí)行，則需要使用事務(wù)進(jìn)行處理。當(dāng)我們開啟一個事務(wù)，并且沒有提交，mysql 會自動回滾事務(wù)?；蛘呶覀兪褂?/span> rollback 命令手動回滾事務(wù)。

作用：事務(wù)是程序更加可靠，簡化錯誤恢復(fù)

四大特性：

原子性(Autmic):事務(wù)在執(zhí)行，要么全做，要么不做。

一致性(Consistency):事務(wù)必須是使數(shù)據(jù)庫從一個一致性狀態(tài)編導(dǎo)另一個一致性狀態(tài)，一致性與原子性密切相關(guān)。在事務(wù)開始之前和結(jié)束之后，數(shù)據(jù)庫的完整性沒有被破壞。

隔離性(Isolation):一個事務(wù)的執(zhí)行不能被其他事務(wù)干擾。及一個事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個事務(wù)之間不能互相干擾，這些通常經(jīng)過加鎖來實現(xiàn)。

持久性(Durability):指一個事務(wù)一旦提交，他對數(shù)據(jù)中的數(shù)據(jù)的改變就應(yīng)該是永久性的，接下來的其他操作或故障不應(yīng)該對齊有任何影響。

mysql事物處理實例

MYSQL的事務(wù)處理主要有兩種方法
1.用begin,rollback,commit來實現(xiàn)
    begin開始一個事務(wù)
    rollback事務(wù)回滾
    commit 事務(wù)確認(rèn)
2.直接用set來改變mysql的自動提交模式
    mysql默認(rèn)是自動提交的，也就是你提交一個query，就直接執(zhí)行！可以通過
    set autocommit = 0 禁止自動提交
    set autocommit = 1 開啟自動提交

但要注意當(dāng)用set autocommit = 0 的時候，你以后所有的sql都將作為事務(wù)處理，直到你用commit確認(rèn)或 rollback結(jié)束，注意當(dāng)你結(jié)束這個事務(wù)的同時也開啟了新的事務(wù)！按第一種方法只將當(dāng)前的做為一個事務(wù)!
MYSQL只有 INNODB和BDB類型的數(shù)據(jù)表才支持事務(wù)處理，其他的類型是不支持的!