Mysql 5.7-- 存儲過程

mysql 存儲過程

存儲過程：對sql的封裝和重用，經編譯建立并儲存在資料庫中，通過指定存儲過程的名字并給定參數(需要時)來調用執行。
優缺點：
（1）優點：
- 執行速度快------存儲過程隻在建立時進行編譯，以後每次執行存儲過程都不需要重新編譯，而一般SQL語句每執行一次就需編譯一次，是以使用存儲過程可提高資料庫的執行速度；
- 減少網絡通信量------當對資料庫進行複雜操作時，（如對多個表進行insert、update、select、delete時）可将這些複雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用。這些操作，如果用程式完成就是多條SQL語句，可能要多次連接配接資料庫，而換成存儲過程隻需一次連接配接。
- 更強的适應性與複用性------存儲過程可以重複使用，提高了可重用性，減少資料庫開發人員的工作量。
- 可維護性高------更新存儲過程通常比更改、測試以及重新部署程式集需要較少的時間和精力。
- 安全性高，可設定隻有某使用者才能對指定存儲過程的使用權，且存儲過程比多條sql穩定，隻要資料庫不出現問題，基本上是不會出現什麼問題的，同時可以防止 SQL注入。
（2）缺點：
- 開發調試差：無良好的IDE開發工具，存儲過程的調試比一般SQL要複雜的多。
- 可移植性差：由于存儲過程将應用程式綁定到資料庫上，是以使用存儲過程封裝業務邏輯将限制應用程式的可移植性，如果應用程式進行資料源的切換（Mysql-->Orcle)，需重新編寫存儲過程。
- 不支援群集：資料庫伺服器無法水準擴充，或者資料庫的切割（水準或垂直切割）。資料庫切割之後，存儲過程并不清楚資料存儲在哪個資料庫中。
存儲過程的建立：
- create procedure 存儲過程名 ([參數清單]) begin sql 語句 end;
  - 參數清單的格式：[類型限定變量名資料類型] （5.7的發現參數資料類型長度必須聲明，不然sql編譯不過）
    - 參數清單有自己的類型限定，這個類型限定與資料類型不同，它是限定參數的作用範圍
      - in:限定這個參數是傳值給存儲過程,既然是傳值，是以可以是變量或常量資料【in修飾的參數一般是傳入存儲過程中作為某些條件的，不會被存儲過程修改】
      - out:限定這個參數是存儲過程傳出的一個值，因為有值的傳回，是以這個參數必須是一個變量【存儲過程中會給out修飾的變量指派，使得過程外部可以擷取這個更改的值】
      - inout:inout是上面兩者的疊加,既可以被存儲過程内部使用，又可以修改後被外部使用,因為有值的傳回，是以這個參數必須是一個變量
- 理論上，對于希望簡潔代碼的地方都可以使用存儲過程來處理，比如希望快速使用多條select,又比如希望從資料中取出多個值指派給變量；是以下面隻給出用法，應用場景就不講述了。
存儲過程的使用：
- 調用存儲過程：call 存儲過程名();
- 帶參數的調用存儲過程：call 存儲過程名(參數);
- 對于in類型的，參數可以是數值，可以是變量
  - 對于out\inout類型的，參數必須是變量
  - 所有MySQL變量都必須以 @ 開始
  - 示例：call myselect("lilei",@變量名);示例：call myselect(@變量名,@變量名)
執行個體：

-- 無參建立
CREATE PROCEDURE demo1()
BEGIN 
    SELECT * from cli_txinfo LIMIT 1;
end;
-- 調用，輸出查詢結果
call demo1();


-- 2.有參建立，格式為：in 參數名 類型（長度）....
-- 單參demo2(in tableName varchar(20))
-- 多參demo2(in tableName varchar(20),name varchar(40))
create procedure demo2(in tableName varchar(20),name varchar(40))
BEGIN
    SELECT * from cli_txinfo where txname = name LIMIT 1;
end;
-- 調用，輸出查詢結果
call demo2("abc001","專屬顧問綁定申請清單查詢");


--3.有參建立，值傳回格式為：out 參數名 類型（長度）
create procedure demo3(in tx varchar(20),out otx varchar(20))
begin 
    SELECT txname from cli_txinfo where txcode = tx into otx;
end;
-- 調用，輸出結果存儲到@tx中，隻顯示sql的執行情況，沒有輸出操作結果，變量命名必須以@開頭
-- call demo3("abd002",tx);  ---->>編譯錯誤
call demo3("abd002",@tx);
-- 變量傳參，格式為 set @變量名 = 值
-- set @a = "abd002";
-- call demo3(@a,@tx);
-- 查詢輸出結果變量名的值
SELECT @tx;


-- 存儲過程的複雜sql語句
create procedure demo4()
begin 
  SELECT * from cli_txinfo limit 1;
  select * from cli_role limit 1;
END;
demo
call demo4;

-- 動态傳入表名（案例以兩個資料庫進行表的同步）
-- 使用CONCAT進行sql的拼接，拼接處必須留有空格，比如 concat(\'insert into \')，不然拼接無法識别參數
-- prepare預處理，execute 執行，DEALLOCATE PREPARE釋放資源 
create procedure demo5(in syncTable varchar(20))
BEGIN
    set @deleteSql = CONCAT(\'truncate table keyun.\',syncTable);
        set @insertSql = CONCAT(\'insert into keyun.\',syncTable,\' SELECT distinct * \',\'from keyun_sync.\',syncTable);
    prepare delstmt from @deleteSql;
    prepare insstmt from @insertSql;
    execute delstmt;
    execute insstmt;
   DEALLOCATE PREPARE delstmt;
   DEALLOCATE PREPARE insstmt;
end;
-- 調用，輸出查詢結果
call demo5("third_binding");

　　6.檢視存儲過程：

- 檢視存儲過程的建立語句：show create procedure 存儲過程名;
- 檢視存儲過程狀态：show procedure status;【顯示的内容包括建立時間、注釋、定義的使用者、安全類型等等】

7.修改存儲過程：

- 修改存儲過程隻能修改那些選項，并不能修改傳入傳出參數或者sql語句。
- alter procedure 存儲過程名選項；

8.删除存儲過程：

- 文法：drop procedure 存儲過程名;
- 示例：
  - ```
  drop procedure demo1;      
```

　　9.MyBatis實戰使用：

//=========Service業務層==============
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("YPID", ypId);
        factory.yearPlanMapper.updArrAndLeav(param);
        Date arriveTime = (Date) param.get("arrive_time");
        Date leaveTime = (Date) param.get("leave_time");
        //=========Dao資料層================
        void updArrAndLeav(Map<String, Object> param);
        //=====MyBatis的xml：in參數，out傳回值======
       <select id="updArrAndLeav" statementType="CALLABLE">
        {call
        usp_update_year_date_formal(
        #{YPID,mode=IN,jdbcType=NVARCHAR},
        #{arrive_time,mode=OUT,jdbcType=DATE},
        #{leave_time,mode=OUT,jdbcType=DATE}
        )}
       </select>

Mysql 5.7-- 存儲過程

存儲過程：對sql的封裝和重用，經編譯建立并儲存在資料庫中，通過指定存儲過程的名字并給定參數(需要時)來調用執行。

優缺點：

存儲過程的建立：

存儲過程的使用：

執行個體：

6.檢視存儲過程：

7.修改存儲過程：

8.删除存儲過程：

9.MyBatis實戰使用：

　　6.檢視存儲過程：

　　9.MyBatis實戰使用：