一、存儲(chǔ)過程概念

• 什么是存儲(chǔ)過程

• 一組為了完成特定功能的SQL 語(yǔ)句集。
  更加直白的理解：存儲(chǔ)過程可以說是一個(gè)記錄集，它是由一些T-SQL語(yǔ)句組成的代碼塊，這些T-SQL語(yǔ)句代碼像一個(gè)方法一樣實(shí)現(xiàn)一些功能（對(duì)單表或多表的增刪改查），然后再給這個(gè)代碼塊取一個(gè)名字，在用到這個(gè)功能的時(shí)候調(diào)用他就行了。

• 優(yōu)點(diǎn)

• 存儲(chǔ)過程是一個(gè)預(yù)編譯的代碼塊，執(zhí)行效率比較高

• 一個(gè)存儲(chǔ)過程替代大量T_SQL語(yǔ)句 ，可以降低網(wǎng)絡(luò)通信量，提高通信速率

• 可以一定程度上確保數(shù)據(jù)安全（可設(shè)定只有某些用戶才具有對(duì)指定存儲(chǔ)過程的使用權(quán)）

• 缺點(diǎn)

• 如果更改范圍大到需要對(duì)輸入存儲(chǔ)過程的參數(shù)進(jìn)行更改，或者要更改由其返回的數(shù)據(jù)，則您仍需要更新程序集中的代碼以添加參數(shù)、更新 GetValue() 調(diào)用，等等，這時(shí)候估計(jì)比較繁瑣了。

• 可移植性差

• 很多存儲(chǔ)過程不支持面向?qū)ο蟮脑O(shè)計(jì)，無(wú)法采用面向?qū)ο蟮姆绞綄I(yè)務(wù)邏輯進(jìn)行封裝，從而無(wú)法形成通用的可支持復(fù)用的業(yè)務(wù)邏輯框架。

• 代碼可讀性差，相當(dāng)難維護(hù)。

• 要不要用存儲(chǔ)過程
  個(gè)人覺得各組件應(yīng)該各司其職。MySQL就是存儲(chǔ)數(shù)據(jù)的倉(cāng)庫(kù)，邏輯實(shí)現(xiàn)還是應(yīng)該放在業(yè)務(wù)層。
  建議看看別人說的。為什么要用存儲(chǔ)過程

二、存儲(chǔ)過程使用

存儲(chǔ)過程的創(chuàng)建語(yǔ)法：

DELIMITER //
CREATE PROCEDURE 儲(chǔ)存過程名([in|out|inout] 參數(shù) datatype)
BEGIN
SQL語(yǔ)句代碼快
END
//
DELIMITER ;

幾點(diǎn)說明：

• 存儲(chǔ)過程名字后面的“()”是必須的，即使沒有一個(gè)參數(shù)，也需要“()”
• begin end 可以在只有一條sql語(yǔ)句的時(shí)候省略。
• 每條語(yǔ)句的末尾，都要加上分號(hào) “;”
• 不能在存儲(chǔ)過程中使用 “return” 關(guān)鍵字。
• DELIMITER 的意思是，告訴mysql，下面的語(yǔ)句中，語(yǔ)句定界符不再是分號(hào)（;），而是雙斜線（//）。這里的雙斜線可以任意指定，比如指定為|或者///，都是可以的（但不要指定為sql語(yǔ)句中經(jīng)常出現(xiàn)的逗號(hào)或等號(hào)等，不然就結(jié)束了），如：

mysql> delimiter // 
mysql> create procedure simpleproc (out param1 int) 
    -> begin 
    -> select count(*) into param1 from t; 
    -> end 
    -> //
Query OK, 0 rows affected

mysql> delimiter |
mysql> create procedure simpleproc1 (out param1 int) 
    -> begin 
    -> select count(*) into param1 from t;
    -> end 
    -> |
Query OK, 0 rows affected

mysql> delimiter ///
mysql> create procedure simpleproc2 (out param1 int)
    -> begin 
    -> select count(*) into param1 from t;
    -> end
    -> ///
Query OK, 0 rows affected

還要注意，定義完存儲(chǔ)過程之后，要重新將分號(hào)（;）指定為語(yǔ)義分隔符。即調(diào)用DELIMITER ;

• 關(guān)于參數(shù)

• in表示向存儲(chǔ)過程傳遞參數(shù)，out表示從存儲(chǔ)過程返回參數(shù)，而inout表示傳遞參數(shù)和返回參數(shù)；如果不顯式指定in、out、inout，則默認(rèn)為in。習(xí)慣上，對(duì)于是in的參數(shù)，我們都不會(huì)顯式指定；

• 參數(shù)只能指定參數(shù)類型，不能指定長(zhǎng)度；

• 參數(shù)不能指定默認(rèn)值。

• 關(guān)于注釋

/*   
這是   
多行 
注釋
*/
declare a int; -- 這是單行 MySQL 注釋 （注意 -- 后至少要有一個(gè)空格）   
if a is null then set a = 0; # 這也是個(gè)單行 MySQL 注釋   

查看已經(jīng)創(chuàng)建的存儲(chǔ)過程：

show procedure status where Db='數(shù)據(jù)庫(kù)名';或者：先use 數(shù)據(jù)庫(kù)名，再show procedure status

mysql> show procedure status where Db='cpgl';
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name   | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| cpgl | hi     | PROCEDURE | root@localhost | 2017-05-30 11:39:44 | 2017-05-30 11:39:44 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| cpgl | pr_add | PROCEDURE | root@localhost | 2017-05-30 11:41:42 | 2017-05-30 11:41:42 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set

刪除存儲(chǔ)過程

drop procedure '存儲(chǔ)過程名字';

mysql> drop procedure hi;
Query OK, 0 rows affected

實(shí)例

從網(wǎng)上找了幾個(gè)例子，真正用到的時(shí)候，可以參考：包含了事務(wù)，參數(shù)，嵌套調(diào)用，游標(biāo)，循環(huán)等使用

例子1

drop procedure if exists pro_rep_shadow_rs;   
delimiter |   
----------------------------------   
-- rep_shadow_rs   
-- 用來處理信息的增加，更新和刪除   
-- 每次只更新上次以來沒有做過的數(shù)據(jù)   
-- 根據(jù)不同的標(biāo)志位   
-- 需要一個(gè)輸出的參數(shù)，   
-- 如果返回為0，則調(diào)用失敗，事務(wù)回滾   
-- 如果返回為1，調(diào)用成功，事務(wù)提交   
--   
-- 測(cè)試方法   
-- call pro_rep_shadow_rs(@rtn);   
-- select @rtn;   
----------------------------------   
create procedure pro_rep_shadow_rs(out rtn int)   
begin   
    -- 聲明變量，所有的聲明必須在非聲明的語(yǔ)句前面   
    declare iLast_rep_sync_id int default -1;   
    declare iMax_rep_sync_id int default -1;   
    -- 如果出現(xiàn)異常，或自動(dòng)處理并rollback,但不再通知調(diào)用方了   
    -- 如果希望應(yīng)用獲得異常，需要將下面這一句，以及啟動(dòng)事務(wù)和提交事務(wù)的語(yǔ)句全部去掉   
    declare exit handler for sqlexception rollback;   
    -- 查找上一次的   
    select eid into iLast_rep_sync_id from rep_de_proc_log where tbl='rep_shadow_rs';   
    -- 如果不存在，則增加一行   
    if iLast_rep_sync_id=-1 then   
      insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs');   
      set iLast_rep_sync_id = 0;   
    end if;   
       
    -- 下一個(gè)數(shù)字   
    set iLast_rep_sync_id=iLast_rep_sync_id+1;   
    -- 設(shè)置默認(rèn)的返回值為0：失敗   
    set rtn=0;   
       
    -- 啟動(dòng)事務(wù)   
    start transaction;   
    -- 查找最大編號(hào)   
    select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;   
    -- 有新數(shù)據(jù)   
    if iMax_rep_sync_id>=iLast_rep_sync_id then   
        -- 調(diào)用   
        call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);   
        -- 更新日志   
        update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';   
    end if;   
       
    -- 運(yùn)行沒有異常，提交事務(wù)   
    commit;   
    -- 設(shè)置返回值為1  
    set rtn=1;   
end;   
|   
delimiter ;   

例子2

drop procedure if exists pro_rep_shadow_rs_do;   
delimiter |   
---------------------------------   
-- 處理指定編號(hào)范圍內(nèi)的數(shù)據(jù)   
-- 需要輸入2個(gè)參數(shù)   
-- last_rep_sync_id 是編號(hào)的最小值   
-- max_rep_sync_id 是編號(hào)的最大值   
-- 無(wú)返回值   
---------------------------------   
create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int)   
begin   
    declare iRep_operationtype varchar(1);   
    declare iRep_status varchar(1);   
    declare iRep_Sync_id int;   
    declare iId int;   
    -- 這個(gè)用于處理游標(biāo)到達(dá)最后一行的情況   
    declare stop int default 0;   
    -- 聲明游標(biāo)   
    declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id;   
    -- 聲明游標(biāo)的異常處理，設(shè)置一個(gè)終止標(biāo)記   
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;   
       
    -- 打開游標(biāo)   
    open cur;   
       
    -- 讀取一行數(shù)據(jù)到變量   
    fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;   
    -- 這個(gè)就是判斷是否游標(biāo)已經(jīng)到達(dá)了最后   
    while stop <> 1 do  
        -- 各種判斷   
        if iRep_operationtype='I' then   
            insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;   
        elseif iRep_operationtype='U' then   
        begin   
            if iRep_status='A' then   
                insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;   
            elseif iRep_status='B' then   
                delete from rs0811 where id=iId;   
            end if;   
        end;   
        elseif iRep_operationtype='D' then   
            delete from rs0811 where id=iId;   
        end if;    
           
        -- 讀取下一行的數(shù)據(jù)    
        fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;   
    end while;  -- 循環(huán)結(jié)束   
    close cur; -- 關(guān)閉游標(biāo)   
 end;   
|  
delimiter ;

關(guān)于游標(biāo)定義和使用，可以參考：MySql存儲(chǔ)過程—游標(biāo)(Cursor)

• 游標(biāo)循環(huán)次數(shù)不對(duì)的問題
  在使用游標(biāo)的時(shí)候，最容易出現(xiàn)的問題就是循環(huán)次數(shù)與實(shí)際記錄數(shù)不一：第一種就是循環(huán)次數(shù)比實(shí)際記錄數(shù)多一次；第二種情況就是循環(huán)次數(shù)遠(yuǎn)小于記錄數(shù)。
  第一種情況的發(fā)生，是由于對(duì)while do結(jié)構(gòu)（或者有時(shí)候使用repeat）以及cursor的性質(zhì)認(rèn)識(shí)不夠?qū)е碌?，cursor會(huì)在找不到記錄時(shí)（一般是循環(huán)完了最后一行，也有可能是循環(huán)時(shí)的select into 賦值語(yǔ)句結(jié)果為空導(dǎo)致）發(fā)生NOT FOUND，可以根據(jù)這一點(diǎn)來判斷循環(huán)結(jié)束。而while do 結(jié)構(gòu)類似于java中的do while 結(jié)構(gòu)，無(wú)論怎樣都會(huì)先do再判斷。上面例子2中的寫法可以很好的解決這個(gè)問題：在每次循環(huán)結(jié)尾，執(zhí)行賦值操作，相當(dāng)于讓cursor的判斷提前一步。
  第二種情況的發(fā)生，一般是select into 賦值語(yǔ)句結(jié)果為空（有待研究）。

• 存儲(chǔ)過程如何調(diào)試
  我使用的有兩種：
  第一種是在call調(diào)用存儲(chǔ)過程之前，定義全局變量，如SET @test=0；在存儲(chǔ)過程的函數(shù)體中的某個(gè)地方，為該變量賦值，如SET @test=1；在調(diào)用完存儲(chǔ)過程之后，使用select @test的方式查看之前定義的@test的值。如果存儲(chǔ)過程寫的沒問題，執(zhí)行到了你寫SET @test=1；的地方，則值為1，否則還是初始值0。
  第二種則是，在函數(shù)體中，使用select var1,var2...這種方式：var1,var2...是你在其中定義的變量，你想在某處查看它們的值是否與你期望的一樣，就在該處寫上select var1,var2...語(yǔ)句。這樣，在控制臺(tái)調(diào)用存儲(chǔ)過程時(shí)，就會(huì)打印出這樣變量的值。
  下面的存儲(chǔ)過程可供參考：

CREATE PROCEDURE copy_nfi_item(nfi_id_from varchar(100), nfi_id_to varchar(100))
    COMMENT '復(fù)制nfi考核，與業(yè)務(wù)無(wú)關(guān)；如果兩個(gè)部門的nfi考核項(xiàng)相同，可以通過該存儲(chǔ)過程快捷復(fù)制。'
BEGIN
        
    DECLARE v_item_id varchar(50);
    DECLARE v_percent float(11,0);
    DECLARE v_first_level_duty varchar(255);
    DECLARE v_first_level_duty_weight float(11,0);
    DECLARE v_title varchar(255);
    DECLARE v_task text;
    DECLARE v_task_weight float(11,0);
    DECLARE v_plan_begin_date date;
    DECLARE v_plan_end_date date;
    DECLARE v_complete_date date;
    DECLARE v_results text;
    DECLARE v_scoring_standard text;
    DECLARE v_results_desc text;
    DECLARE v_provide_dept varchar(255);
    DECLARE v_check_dept varchar(255);
    DECLARE v_self_marks float(11,0);
    DECLARE v_self_score float(11,2);
    DECLARE v_dept_marks float(11,0);
    DECLARE v_dept_score float(11,2);
    DECLARE v_nonfinancial_score float(11,2);
    DECLARE v_president_score float(11,2);
    DECLARE v_review_marks float(11,2);
    DECLARE v_review_score float(11,2);
    DECLARE v_advice text;
    DECLARE v_nfi_id varchar(50);
    DECLARE v_executor_id varchar(50);
    DECLARE v_status int(11);
    DECLARE v_step int(11);
    DECLARE v_dr bit(1);
    DECLARE v_ts datetime;
    DECLARE v_improvements text;
    DECLARE v_update_ts varchar(50);

    DECLARE temp_dept_id_from VARCHAR(200);
    DECLARE temp_dept_id_to VARCHAR(200);
    DECLARE v_count int(10) DEFAULT 0;


    -- 這個(gè)用于處理游標(biāo)到達(dá)最后一行的情況
    DECLARE stopFlag int default 0;
     
    -- 聲明游標(biāo)：指向數(shù)據(jù)源  
    DECLARE cur CURSOR FOR SELECT item.item_id,item.percent,item.first_level_duty,item.first_level_duty_weight,item.title,item.task,item.task_weight,item.plan_begin_date,item.plan_end_date,
                item.complete_date,item.results,item.scoring_standard,item.results_desc,item.provide_dept,item.check_dept,item.self_marks,item.self_score,item.dept_marks,item.dept_score,item.review_marks,item.review_score,
                item.advice,item.nfi_id,item.status,item.step,item.dr,item.ts,item.executor_id,item.nonfinancial_score,item.president_score,item.improvements FROM nfi_item item WHERE item.nfi_id = nfi_id_from;

    -- 聲明游標(biāo)的異常處理，設(shè)置一個(gè)終止標(biāo)記 
    -- 另一種寫法：DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop=1;   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;  


    -- 打開游標(biāo)   
    OPEN cur;


    /**

    使用游標(biāo)的一個(gè)常見問題就是循環(huán)次數(shù)不對(duì)。這樣可以正確。
    mysql的while與java里的類似，會(huì)先進(jìn)入其中，do之后，再判斷。
    在循環(huán)體的最后使用fetch，是為了讓游標(biāo)提前更進(jìn)一步，使得stopFlag=1。

    */
-- 讀取一行數(shù)據(jù)到變量   
        FETCH cur INTO v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
                v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
                v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements;   

    -- 判斷是否游標(biāo)已經(jīng)到達(dá)了最后
    WHILE stopFlag <> 1 DO
            
            SET v_count = v_count + 1;

            -- 測(cè)試用，這樣的語(yǔ)法會(huì)在控制臺(tái)直接輸出變量的值
            SELECT v_item_id,v_percent, temp_dept_id_from, temp_dept_id_to, v_count;

            -- 幾個(gè)特殊的變量重新賦值:使用SELECT 表中字段名 INTO 變量名的方式 
            SELECT n.dept_id INTO temp_dept_id_from FROM nfi n WHERE n.nfi_id = nfi_id_from;
            SELECT n.dept_id INTO temp_dept_id_to FROM nfi n WHERE n.nfi_id = nfi_id_to;

            IF temp_dept_id_from = v_provide_dept THEN SET v_provide_dept = temp_dept_id_to;
            END IF;

            SET v_item_id = CONCAT(SUBSTR(DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),3),SUBSTR(UUID(),1,8));
            SET v_nfi_id = nfi_id_to;

            SELECT n.psn_code INTO v_executor_id FROM nfi n WHERE n.nfi_id = nfi_id_to;

            -- 執(zhí)行插入
            INSERT INTO nfi_item
                    (item_id,percent,first_level_duty,first_level_duty_weight,title,task,task_weight,plan_begin_date,plan_end_date,complete_date,
                    results,scoring_standard,results_desc,provide_dept,check_dept,self_marks,self_score,dept_marks,dept_score,review_marks,review_score,
                    advice,nfi_id,status,step,dr,ts,executor_id,nonfinancial_score,president_score,improvements)
            VALUES(
                    v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
                    v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
                    v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements);

       -- 讀取一行數(shù)據(jù)到變量   
         FETCH cur INTO v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
                    v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
                    v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements;
    END WHILE;  -- 循環(huán)結(jié)束   
    CLOSE cur; -- 關(guān)閉游標(biāo)   
END