存儲過程的概念：
所謂的存儲過程就是存儲在數據庫當中的可以執行特定工作(查詢和更新)的一組SQL代碼的程序段。
與自定義函數的區別：
自定義函數有且只有一個返回值，就像普通的函數一樣，可以直接在表達式中嵌入調用。
存儲過程可以沒有返回值，也可以有任意個輸出參數，必須單獨調用。
執行的本質都一樣。只是函數有如只能返回一個變量的限制。而存儲過程可以返回多個。而函數是可以嵌入在sql中使用的，可以在select中調用，而存儲過程不行。
函數限制比較多，比如不能用臨時表，只能用表變量。還有一些函數都不可用等等。而存儲過程的限制相對就比較少。
一般來說，存儲過程實現的功能要復雜一點，而函數的實現的功能針對性比較強。
對於存儲過程來說可以返回參數，而函數只能返回值或者表對象。
存儲過程一般是作為一個獨立的部分來執行，而函數可以作為查詢語句的一個部分來調用，由於函數可以返回一個表對象，因此它可以在查詢語句中位於FROM關鍵字的後面。
為什麼使用存儲過程：
存儲過程的優點：
存儲過程只在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高數據庫執行速度。
當對數據庫進行復雜操作時（如對多個表進行Update、Insert、Query、Delete時），可將此復雜操作用存儲過程封裝起來與數據庫提供的事務處理結合一起使用。
存儲過程可以重復使用，可減少數據庫開發人員的工作量。
安全性高，可設定只有某此用戶才具有對指定存儲過程的使用權。
基本的創建、刪除語法：
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
默認地，子程序與當前數據庫關聯。要明確地把子程序與一個給定數據庫關聯起來，可以在創建子程序的時候指定其名字為db_name.sp_name。
sp_name 存儲過程的名字
proc_parameter指定參數為IN， OUT，或INOUT
characteristic 特征
routine_body 包含合法的SQL過程語句。
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
這個語句被用來移除一個存儲程序或函數。即，從服務器移除一個制定的子程序。在MySQL 5.1中，你必須有ALTER ROUTINE權限才可用此子程序。這個權限被自動授予子程序的創建者。
IF EXISTS 子句是一個MySQL的擴展。如果程序或函數不存在，它防止發生錯誤。
基本的創建、調用、刪除語法：
delimiter //
DROP PROCEDURE IF EXISTS test //
CREATE PROCEDURE test /* 存儲過程名 */
(IN inparms INT, OUT outparams varchar(32)) /* 輸入參數 */
BEGIN /* 語句塊頭 */
DECLARE var CHAR(10); /* 變量聲明 */
IF inparms = 1 THEN /* IF條件開始*/
SET var = 'hello'; /* 賦值 */
ELSE
SET var = 'world';
END IF; /* IF結束 */
INSERT INTO t1 VALUES (var); /* SQL語句 */
SELECT name FROM t1 LIMIT 1 INTO outparams;
END
//
delimiter ;
call test(1, @out);
存儲過程的變量：
聲明變量：
DECLARE var_name[,...] type [DEFAULT value]
這個語句被用來聲明局部變量。要給變量提供一個默認值，需要包含一個DEFAULT子句。值可以被指定為一個表達式，不需要為一個常數。如果沒有DEFAULT子句，初始值為NULL。
局部變量的作用范圍在它被聲明的BEGIN ... END塊內。它可以被用在嵌套的塊中，除了那些用相同名字聲明變量的塊。
變量賦值，SET語句：
SET var_name = expr [, var_name = expr] ...
也可以用語句代替SET來為用戶變量分配一個值。在這種情況下，分配符必須為:=而不能用=，因為在非SET語句中=被視為一個比較 操作符，如下所示： mysql> SET @t1=0, @t2=0, @t3=0; MySQL> SELECT @t1:=0,@t2:=0,@t3:=0; 對於使用select語句為變量賦值的情況，若返回結果為空，即沒有記錄，此時變量的值為上一次變量賦值時的值，如果沒有對變量賦過值，則為NULL。
變量賦值，SELECT ... INTO語句
SELECT col_name[,...] INTO var_name[,...] table_expr這個SELECT語法把選定的列直接存儲到變量。因此，只有單一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
BEGIN...END復合語句：
[begin_label:] BEGIN
[statement_list]
END [end_label]
存儲子程序可以使用BEGIN ... END復合語句來包含多個語句。statement_list 代表一個或多個語句的列表。statement_list之內每個語句都必須用分號（；）來結尾。
流程控制結構語句：
IF語句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
IF實現了一個基本的條件構造。如果search_condition求值為真，相應的SQL語句列表被執行。如果沒有search_condition匹配，在ELSE子句裡的語句列表被執行。statement_list可以包括一個或多個語句。
舉例：
DELIMITER //
CREATE PROCEDURE p1(IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
DELIMITER ;
復合語句可以被標記。除非begin_label存在，否則end_label不能被給出，並且如果二者都存在，他們必須是同樣的。
流程控制結構語句：
CASE語句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
存儲程序的CASE語句實現一個復雜的條件構造。如果search_condition 求值為真，相應的SQL被執行。如果沒有搜索條件匹配，在ELSE子句裡的語句被執行。
舉例：
CREATE PROCEDURE p2 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
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; //
流程控制結構語句：
循環語句
WHILE … END WHILE
LOOP … END LOOP
REPEAT … END REPEAT
GOTO
前三種是標准的循環方式，至於GOTO就如C語言裡的GOTO一樣，盡量少用！
在循環中還穿插一些循環控制語句，如LEAVE（類似C語言的break）、ITERATE（類似C語言的continue）等。
LEAVE語句
LEAVE label 這個語句被用來退出任何被標注的流程控制構造。它和BEGIN ... END或循環一起被使用。
ITERATE語句
ITERATE label ITERATE只可以出現在LOOP, REPEAT, 和WHILE語句內。ITERATE意思為：再次循環。
流程控制結構語句：
循環語句
WHILE … END WHILE 舉例：
CREATE PROCEDURE p4 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END; //
流程控制結構語句：
循環語句
LOOP … END LOOP 舉例：
CREATE PROCEDURE p5 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LOOP允許某特定語句或語句群的重復執行，實現一個簡單的循環構造。在循環內的語句一直重復直到循環被退出，退出通常伴隨著一個LEAVE 語句。
流程控制結構語句：
循環語句
REPEAT … END REPEAT 舉例：
CREATE PROCEDURE p6 ()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO t VALUES (v);
SET v = v + 1;
UNTIL v >= 5 END REPEAT;
END; //
功能與WHILE差不多，差別是在執行一次後檢查，而WHILE是在開始時檢查，累死DO…WHILE功能。
流程控制結構語句：
補充：迭代（ITERATE）語句
CREATE PROCEDURE p7 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label;
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
注釋語法：
MySQL存儲過程可使用兩種風格的注釋
雙模槓：--，該風格一般用於單行注釋
c風格：/* 注釋內容 */， 一般用於多行注釋
使用權限：
關於存儲過程的權限管理，一種是SQL SECURITY INVOKER，一種是SQL SECURITY DEFINER,也就是一種是調用者權限，一種是定義者權限，如果使用第一種，那麼執行的時候是以執行者本身的權限來操作存儲過程中包含的表。如果是第二種，那麼執行的時候，是以該存儲過程的定義者權限來操作。
條件和異常處理程序：
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
CONTINUE | EXIT
condition_value:
SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION
這個語句指定每個可以處理一個或多個條件的處理程序。如果產生一個或多個條件，指定的語句被執行。
對一個CONTINUE處理程序，當前子程序的執行在執行處理程序語句之後繼續。對於EXIT處理程序，當前BEGIN...END復合語句的執行被終止。UNDO 處理程序類型語句還不被支持。
SQLWARNING是對所有以01開頭的SQLSTATE代碼的速記。
NOT FOUND是對所有以02開頭的SQLSTATE代碼的速記。
SQLEXCEPTION是對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記。
聲明自定義條件：
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
條件和異常處理程序：
舉例：
CREATE TABLE test.t (s1 int,primary key (s1));
delimiter //
CREATE PROCEDURE handlerdemo ()
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;//
delimiter ;
游標：
聲明游標
DECLARE cursor_name CURSOR FOR select_statement
這個語句聲明一個光標。也可以在子程序中定義多個光標，但是一個塊中的每一個光標必須有唯一的名字。
打開游標
OPEN cursor_name
這個語句打開先前聲明的光標。
游標FETCH
FETCH cursor_name INTO var_name [, var_name] ...
這個語句用指定的打開光標讀取下一行（如果有下一行的話），並且前進光標指針。
關閉游標CLOSE
CLOSE cursor_name
這個語句關閉先前打開的光標。
如果未被明確地關閉，光標在它被聲明的復合語句的末尾被關閉。
游標的特性：
READ ONLY 只讀，只能取值而不能賦值；
NOT SCROOLABLE 不可回滾，只能順序讀取；
ASENSITIVE 敏感，不能在已經打開游標的表上執行update事務；
舉例：
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
