MySQL視圖，函數，觸發器，存儲過程

1. 視圖　　

視圖是一個虛擬表，它的本質是根據SQL語句獲取動態的數據集，併為其命名，用戶使用時只需使用【名稱】即可獲取結果集，可以將該結果集當做表來使用。
使用視圖我們可以把查詢過程中的臨時表摘出來，用視圖去實現，這樣以後再想操作該臨時表的數據時就無需重寫複雜的sql了，直接去視圖中查找即可，
但視圖有明顯地效率問題，並且視圖是存放在資料庫中的，如果我們程式中使用的sql過分依賴資料庫中的視圖，即強耦合，那就意味著擴展sql極為不便，
因此不推薦使用. 而且工作中一般不方便，因為是虛擬表 不方便共用，如果需要修改，可能設計到與DBA的溝通，很麻煩

 1  -- 使用視圖
 2 select .. from v1
 3 select asd from v1
 4 -- 某個查詢語句設置別名，日後方便使用
 5 
 6 - 創建
 7 create view 視圖名稱 as SQL
 8 
 9 PS: 虛擬的，臨時表 無法插入操作 
10 
11 - 修改
12 alter view 視圖名稱 as SQL
13 
14 - 刪除
15 drop view 視圖名稱;

2. 觸發器

定製用戶對錶進行【增、刪、改】操作時前後的行為，註意：沒有查詢
工作中一般也很少用到，因為自己在代碼中就能設計操作前後的行為

insert into tb (....)

delimiter //   -- 修改結束標記
create trigger t1 BEFORE INSERT on student for EACH ROW   -- 創建insert操作前的觸發器
BEGIN   -- 觸發器具體內容
     INSERT into teacher(tname) values(NEW.sname);
     INSERT into teacher(tname) values(NEW.sname);
     INSERT into teacher(tname) values(NEW.sname);
     INSERT into teacher(tname) values(NEW.sname);
 END //  
delimiter ;  -- 恢復預設的語句結束標記

------------------------------------------------

insert into student(gender,class_id,sname) values('女',1,'濤'),('女',1,'根');

-- NEW,代指新數據 可以在觸發器中點語法使用
-- OLD,代指老數據  刪除的那一行記錄被OLD引用

3.函數

因為在sql語句執行中調用函數會比較耗時，而且對索引的那一列使用了函數，則無法命中索引了。

所以工作中對響應速度要求高，一般不會不使用函數處理結果集。而是在架構級別或者程式級別處理結果集。

內置的函數很多，詳情參看官方文檔

-- 內置函數：
            執行函數 select CURDATE();
            
            blog
            id       title            ctime
             1        asdf        2019-11
             2        asdf        2019-11
             3        asdf        2019-10
             4        asdf        2019-10
             
             
            select ctime,count(1) from blog group ctime
            
            select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")
            2019-11   2
            2019-10   2
            
        DATE_FORMAT  時間格式化函數，較常用
        
        
--        自定義函數(必須有返回值)：
            
            delimiter \\
                create function f1(
                    i1 int,
                    i2 int)
                returns int
                BEGIN
                    declare num int default 0;
                    set num = i1 + i2;
                    return(num);
                END \\
            delimiter ;
            
            SELECT f1(1,100);

 4. 存儲過程

包含了一系列可執行的sql語句，存儲過程存放於MySQL中，通過調用它的名字可以執行其內部的一堆sql，可以讓程式與sql解耦合，且執行通過一個名字減少數據傳輸。mysql 5.5版本以後才有的功能
開發崗位一般也比較少使用。主要會是DBA使用。

用MySQL的三種方式：

方式一：
　　MySQL: 存儲過程
　　程式：調用存儲過程
方式二：
　　MySQL：。。
　　程式：SQL語句
方式三：
　　MySQL：。。
　　程式：類和對象（SQL語句）

MySQL中代碼屬於強類型語言， 變數需要先 聲明 變數名 和 變數類型.

delimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN
    ----- 獲取大於傳參數字的id行
    select * from student where sid > n1;
END //
delimiter ;

---------------- 命令行
call p2(12,2)

--------------- pymsql
cursor.callproc('p2',(12,2))

delimiter //
create procedure p3(
    in n1 int,
    inout n2 int
)
BEGIN
    set n2 = 123123;
    select * from student where sid > n1;
END //
delimiter ;

------------------- 
set @v1 = 10;
call p2(12,@v1)
select @v1;

set @_p3_0 = 12
ser @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1



------------------------ pymysql

cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)


cursor.execute('select @_p3_0,@_p3_1')  #  @_p3_0 是底層創建好的名字
r2 = cursor.fetchall() # 去除out值
print(r2)

為什麼有結果集又有out偽造的返回值？

delimiter //
create procedure p3(
    in n1 int,
    out n2 int  -- 用於標識存儲過程的執行結果  一般用 tinyint 1,2等來表示相應的執行結果，方便程式獲取後知道執行結果
)
BEGIN
    insert into vv(..)
    insert into vv(..)
    insert into vv(..)
    insert into vv(..)
    insert into vv(..)
    insert into vv(..)
END //
delimiter ;

delimiter //
create procedure p4(
    out status int
)
BEGIN
    -- 偽代碼描述
    1. 聲明如果出現異常則執行{
        set status = 1;
        rollback;
    }
       
    開始事務
        -- 由秦兵賬戶減去100
        -- 方少偉賬戶加90
        -- 張根賬戶加10
        commit;
    結束
    
    set status = 2;
    
    
END //
delimiter ;

===============================
delimiter \\
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 2; 
 
  END\\
delimiter ;

delimiter //
create procedure p6()
begin 
    declare row_id int; -- 自定義變數1  
    declare row_num int; -- 自定義變數2 
    declare done INT DEFAULT FALSE;  -- 預設為false 表述迴圈未執行完
    declare temp int;
    
        -- 聲明游標
    declare my_cursor CURSOR FOR select id,num from A;
    declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 開始迴圈
    open my_cursor;
        xxoo: LOOP
            fetch my_cursor into row_id,row_num;
            if done then   --  需要自己判斷是否迴圈結束
                leave xxoo; -- 結束迴圈
            END IF;
            set temp = row_id + row_num;
            insert into B(number) values(temp);
        end loop xxoo;
    close my_cursor;
    
end  //
delimter ;

游標性能比較差，一般很少用，使用場景是：針對每一行都需要專門的處理計算的時候可能會用到，但是一般update+ 迴圈也能解決 如：UPDATE B set num=id+num;

delimiter //
create procedure p7(
    in tpl varchar(255),
    in arg int
)
begin 
    set @xo = arg;
    PREPARE prod FROM 'select * from student where sid > ?'; -- 1. 預檢測某個東西 SQL語句合法性
    EXECUTE prod USING @xo; -- 2. SQL =格式化 tpl + arg 
    DEALLOCATE prepare prod; -- 3. 執行SQL語句
end  //
delimter ;

---------------------------
call p7("select * from tb where id > ?",9)