MySQL--儲存過程與檢視

Luson發表於2019-05-13
MySql儲存過程

儲存過程與檢視、觸發器

儲存過程

1、應用場景

對資料進行計算，分析彙總的時候，尤其是拿到別的公司資料的時候，進行轉換為自己系統需要的資料和格式的時候

2、概念

儲存過程和函式可以理解為一段 SQL 語句的集合，它們被事先編譯好並且儲存在資料庫中

建立一個儲存過程的語法: 

 create procedure 儲存過程的名字（引數列表）
 begin 
      儲存過程體
  end
  call 儲存過程名（引數列表）

例子： 

 DROP PROCEDURE user_procedure;
 create PROCEDURE user_procedure(in x int)
 BEGIN 
         select * from 'user' where id=x;
 END
 --執行
 call user_procedure(1);

儲存過程引數型別
1. 從上面的過程中我們瞭解到儲存過程有引數型別這種說法，它的型別可以取值有三個：in、out、inout。
2. 其中它們的意義如下：

(1) in 表示只是用來輸入。

(2) out 表示只是用來輸出。

(3) inout 可以用來輸入，也可以用作輸出。

3.儲存過程的理解

  1. 呼叫儲存過程與直接執行 SQL 語句的效果是相同的，但是儲存過程的一個好處是處理邏輯都封裝在資料庫端。
  2. 當我們呼叫儲存過程的時候，我們不需要了解其中的處理邏輯，一旦處理邏輯發生變化，只需要修改儲存過程即可，對呼叫它的程
    序完全無影響。
  3. 呼叫儲存過程和函式可以簡化應用開發人員的很多工作，減少資料在資料庫和應用伺服器之間的傳輸，可以提高資料處理的效率

4.儲存過程中使用變數

1. 儲存過程中是可以使用變數的，我們可以透過 declare 來定義一個區域性變數，該變數的作用域只是 begin….end 塊中。
2. 變數的定義必須寫在符合語句的開頭，並且在任何其他語句的前面。我們可以一次宣告多個相同型別的變數，我們還可以使用default 來賦予預設值。
3. 定義一個變數的語法為：
declare 變數名 1 [,變數名 2…] 變數型別 [default 預設值]
4.變數的型別就是mysql支援的型別，可以直接賦值，也可以透過查詢來賦值

5.儲存過程中的資料型別:

  • 數值型別：Int,float,double,decimal
  • . 日期型別：timestamp,date,year
  • . 字串：char,varchar,text

5. 遊標

理解：即在儲存過程中使用遊標對結果集進行迴圈的處理，使用遊標的基本步驟為: 宣告、開啟、取值、關閉
語法:

DECLARE test_cursor CURSOR FOR 結果集;//宣告遊標
OPEN test_cursor;  //開啟
CLOSE test_cursor; //關閉
DECLARE CONTINUE HANDLER  FOR NOT FOUND //結果集查詢不到資料就自動跳出

案例： 

delimoter $$ 
create proceddure exchange（out count int）
begin 
         declare supply_id1 int default 0;
         declare amount int default 0;
         -- 遊標標識
         declare blag int default 1;
         -- 遊標
         declare order_cursor cursor for select supply_id,amount from order_group;
         -- not found 異常處理，退出
         declear continue handler for not found set blag=0;
        set count =0;
        --開啟遊標
        open order_cursor;
        --遍歷
        read_loop：LOOP
                       fetch order_cursor into supply_id1,amount1;
                       if blag = 0 then
                                  leave read_loop;
                      end if;
                      if supply_id1 = 1 then 
                                set count = count +amount1;
                     end if;
         end loop  read_loop;
end;
$$
delimiter ;
call exchang(@count);
select @count;

6.儲存過程的優缺點

  • 優點
    1. 第一點優勢就是執行速度快。因為我們的每個 SQL 語句都需要經
    過編譯，然後再執行，但是儲存過程都是直接編譯好了之後，直接
    執行即可。
    2. 第二點優勢就是減少網路流量。我們傳輸一個儲存過程比我們傳
    輸大量的 SQL 語句的開銷要小得多。
    3. 第三點優勢就是提高系統安全性。因為儲存過程可以使用許可權控
    制，而且引數化的儲存過程可以有效地防止 SQL 注入攻擊。保證了
    其安全性。
    4. 第四點優勢就是耦合性降低。當我們的表結構發生了調整或變動
    之後，我們可以修改相應的儲存過程，我們的應用程式在一定程度
    上需要改動的地方就較小了。
    44 / 123
    5. 第五點優勢就是重用性強。因為我們寫好一個儲存過程之後，再
    次呼叫它只需要一個名稱即可，也就是”一次編寫，隨處呼叫”,而且
    使用儲存過程也可以讓程式的模組化加強。

  • 缺點

  1. 第一個缺點就是移植性差。因為儲存過程是和資料庫繫結的，如
    果我們要更換資料庫之類的操作，可能很多地方需要改動。
  2. 第二個缺點就是修改不方便。因為對於儲存過程而言，我們並不
    能特別有效的除錯，它的一些 bug 可能發現的更晚一些，增加了應
    用的危險性。
  3. 第三個缺點就是優勢不明顯和贅餘功能。對於小型 web 應用來說，
    如果我們使用語句快取，發現編譯 SQL 的開銷並不大，但是使用存
    儲過程卻需要檢查許可權一類的開銷，這些贅餘功能也會在一定程度
    上拖累效能。

檢視

1.1概念

檢視可以理解為一個虛表，它只是儲存了一個表結構，並不存在真是資料，資料是查詢過程中動態生成的；使用檢視並不能達成最佳化，只是簡化查詢語句

1.2 檢視操作

語法：

//建立檢視
create VIEW user_view as select id,user_name,email from `user`;
//檢視檢視的每一列
desc user_view;
//檢視建立檢視的語法
show create view user_view;
//查詢檢視
select * from user_view;

1.3檢視優缺點

優點：
1.簡化了sql.
2.\更加安全，可以做許可權限制，如只能看到檢視，不能看到源資料.
3.\降低耦合，修改原表，只需修改檢視，不用更新程式碼
缺點：表結構修改則需要手動修改檢視

1.4檢視IUD

表是可以更新資料的，這裡的更新，指的是”增刪改”,但是對於檢視來說 不一定。
以下是檢視不可更新的情況
1. 包含聚合函式、distinct、group by、having、union、union all。
2. 常量檢視。
3. select 包含子查詢。
4. 包含連線操作。
5. from 一個不能更新的檢視。
6. where 子句的子查詢引用了 from 子句中的表。
有時我們會發現，可以向檢視插入資料，但是並不滿足我們的需求，那就需要使用 with check option 了
例子:\

DROP VIEW user_view_2;
create view user_view_2 as select id,user_name,password,email,status from `user` where status = 0  with check option;

這裡可以理解為 with check option 的作用就是多了一個 check 的功能，即檢查的功能，也就是說插入的資料必須滿足該檢視的條件，才允許被操作。

1.4 物化檢視

概念

物化檢視是相對於檢視而言的，但是兩者實際上並沒有什麼關係就如java/javaScript一樣,物化檢視 可以理解成 就是單獨再建立一張統計表\

作用

1.物化檢視可以幫助加快嚴重依賴某些聚合結果的查詢。
2.如果插入速度不是問題，則此功能可以幫助減少系統上的讀取負載。

實現

根據專案的需求 (資料實時性)

要定時更新資料, ==>>使用儲存過程 開銷小,誤差大一點\

實時更新資料, ==>> 使用觸發器, 會影響資料庫的寫操作的效能,開銷大

觸發器

建立觸發器

觸發器  類似於 框架 事件\
create trigger [觸發器名稱] [觸發器的執行時間] [執行的動作點] on [表名] for each row [函式 或者動作]

觸發器的執行時間 :before, after
執行的動作點:insert, update, delete
函式:begin end;
或者動作:update ,insert\

2.1 案例：

create trigger login_trigger_before
          before insert on login
          for each row
  begin
          update test_trigger set count=count+1 where id=1;
end;
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章