Mysql 5.7儲存過程的學習

碼猿手發表於2019-07-31
MySql儲存過程

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

  2. 優缺點：

    （1） 優點：

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

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

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

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

    （2） 缺點：

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

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

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

  3. 儲存過程的建立：

    • create procedure 儲存過程名 ([引數列表])  begin   sql 語句  end;
      • 引數列表的格式：[型別限定 變數名 資料型別]
        • 引數列表有自己的型別限定，這個型別限定與資料型別不同，它是限定引數的作用範圍
          • 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;

 

相關文章