8、MySLQ存儲過程

簡述

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

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

較SQL語句存儲過程的優點：

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

2、存儲過程能夠實現較快的存儲速度。

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

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

一、存儲過程的創建

1.1 存儲過程語法：

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

創建存儲過程之前我們必須使用delimiter修改MySQL語句的默認結束符，否則不能創建成功。

存儲過程語句的注釋：

MySQL注釋的兩種風格

"--":單行注釋

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

語法：

delimiter <新執行符號>
delimiter //將默認結束符修改為//。

1.2 調用存儲過程

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

1.3 存儲過程參數類型

1.3.1 IN參數

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

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結果作比較

實例1 存儲過程的簡單創建和調用

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應用   |
|       2 | 網站          |
|       3 | 3D動畫        |
|       4 | linux學習     |
|       5 | Delphi學習    |
|       6 | ***          |
|       7 | 網絡技術      |
|       8 | 安全          |
|       9 | 平面          |
|      10 | AutoCAD技術   |
+---------+---------------+
10 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

定義存儲過程getonebook，當輸入書的ID后可以調用處對應的書籍記錄

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短期培訓教程       | 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參數

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

實例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;

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

1.3.3 如何調用存儲過程out類型的返回值

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

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                    |
+-----------------------+
| ***與網絡安全        |
+-----------------------+
1 row in set (0.00 sec)

1.3.4 Inout參數

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

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

1.3.5 不加參數的存儲過程

如果存儲國恒在創建的時候沒有指定參數類型，則需要在調用的時候指定參數值。

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); 循環10次
Query OK, 1 row affected (0.03 sec)
結果驗證
mysql> select * from t2;

二、存儲過程變量的使用

2.1 使用declare進行變量定義

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

datatype [default value];
datatype為MySQL的數據類型，如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語句

定義由順序執行的SQL語句構成的塊。

語法格式：

BEGIN
Statement Block
END

2.2 IF...ELSE語句

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

語法格式：

IF Boolean_expression
statement
[ELSE [IF boolean_expression] statentent]

2.3 循環語句

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

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

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

執行操作后檢查結果，而while則是執行前進行檢查

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

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

4、LABLES標號

可以用在begin repeat while 或者loop語句前，語句標號只能在合法的語句前使用。可以跳出循環，使運行指令達到符合語句的最后異步

5、ITERATE迭代

通過引用符合語句的標號，來重新開始符合語句

查看存儲過程：

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

characreristic參數指定存儲過程函數的特性。

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

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

READSSQL DATA表示子程序博阿寒寫數據的語句。

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

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

INVOKER表示調用者可以執行

COMMENT 'string'是注釋信息

刪除存儲過程

語法一：drop procedure sp_name

語法二：drop procedure if exists sp_name

注：不能在一個存儲過程中刪除另一個存儲過程，只能調用另一個存儲過程

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

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

數據庫默認事務是自動提交的， 也就是發一條 sql 它就執行一條。如果想多條 sql 放在一個事務中執行，則需要使用事務進行處理。當我們開啟一個事務，并且沒有提交，mysql 會自動回滾事務。或者我們使用 rollback 命令手動回滾事務。

作用：事務是程序更加可靠，簡化錯誤恢復

四大特性：

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

一致性(Consistency):事務必須是使數據庫從一個一致性狀態編導另一個一致性狀態，一致性與原子性密切相關。在事務開始之前和結束之后，數據庫的完整性沒有被破壞。

隔離性(Isolation):一個事務的執行不能被其他事務干擾。及一個事務內部的操作及使用的數據對并發的其他事務是隔離的，并發執行的各個事務之間不能互相干擾，這些通常經過加鎖來實現。

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

mysql事物處理實例

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

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