Mysql 5.7存儲過程的學習

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

2. 優缺點：

   （1） 優點：

   • 執行速度快------存儲過程只在創建時進行編譯，以後每次執行存儲過程都不需要重新編譯，而一般SQL語句沒執行一次就需編譯一次，所以使用存儲過程可提高資料庫的執行速度；
   • 減少網路通信量------當對資料庫進行複雜操作時，（如對多個表進行insert、update、select、delete時）可將這些複雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用。這些操作，如果用程式完成就是多條SQL語句，可能要多次連接資料庫，而換成存儲過程只需一次連接。

   • 更強的適應性與復用性------存儲過程可以重覆使用，提高了可重用性，減少資料庫開發人員的工作量。

   • 可維護性高------更新存儲過程通常比更改、測試以及重新部署程式集需要較少的時間和精力。

   • 安全性高，可設定只有某用戶才能對指定存儲過程的使用權，且存儲過程比多條sql穩定，只要資料庫不出現問題，基本上是不會出現什麼問題的，同時可以防止 SQL註入。

   （2） 缺點：

   • 開發調試差：無良好的IDE開發工具，存儲過程的調試比一般SQL要複雜的多。

   • 可移植性差：由於存儲過程將應用程式綁定到資料庫上，因此使用存儲過程封裝業務邏輯將限制應用程式的可移植性，如果應用程式進行數據源的切換（Mysql-->Orcle)，需重新編寫存儲過程。

   • 不支持群集：資料庫伺服器無法水平擴展，或者資料庫的切割（水平或垂直切割）。資料庫切割之後，存儲過程並不清楚數據存儲在哪個資料庫中。

3. 存儲過程的創建：

   • create procedure 存儲過程名 ([參數列表])  begin   sql 語句  end;
     • 參數列表的格式：[類型限定 變數名 數據類型] （5.7的發現參數數據類型長度必須聲明，不然sql編譯不過）
       • 參數列表有自己的類型限定，這個類型限定與數據類型不同，它是限定參數的作用範圍
         • in:限定這個參數是傳值給存儲過程,既然是傳值，所以可以是變數或常量數據【in修飾的參數一般是傳入存儲過程中作為某些條件的，不會被存儲過程修改】
         • out:限定這個參數是存儲過程傳出的一個值，因為有值的返回，所以這個參數必須是一個變數【存儲過程中會給out修飾的變數賦值，使得過程外部可以獲取這個更改的值】
         • inout:inout是上面兩者的疊加,既可以被存儲過程內部使用，又可以修改後被外部使用,因為有值的返回，所以這個參數必須是一個變數
   • 理論上，對於希望簡潔代碼的地方都可以使用存儲過程來處理，比如希望快速使用多條select,又比如希望從數據中取出多個值賦值給變數；所以下麵只給出用法，應用場景就不講述了。
   • 1：不傳入參數，只執行某些特定代碼
   • 2.傳入參數，並利用參數作為條件執行代碼
   • 3.傳入參數，並利用參數作為條件執行代碼，同時利用變數獲取結果。

4. 存儲過程的使用：

   • 調用存儲過程：call 存儲過程名();
   • 帶參數的調用存儲過程：call 存儲過程名(參數);
   • 對於in類型的，參數可以是數值，可以是變數
     • 對於out\inout類型的，參數必須是變數
     • 所有MySQL變數都必須以 @ 開始
     • 示例：call myselect("lilei",@變數名);示例：call myselect(@變數名,@變數名)

5. 實例：

-- 無參創建
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;