MySQL之視圖、預存程序、觸發器、函數、事務、動態執行SQL

標籤：mysql 函數 預存程序 事務

視圖

視圖是一個虛擬表（非真實存在），其本質是【根據SQL語句擷取動態資料集，並為其命名】，使用者使用時只需使用【名稱】即可擷取結果集，並可以將其當作表來使用。

建立視圖：

create view v1 asselect * from student where id > 100;

今後可以直接通過:

select * from v1;   #直接存取學生ID大於100的資訊

刪除視圖：

drop view v1;

修改視圖：

alter view v1 asselect id,name from student where id<10 and name=dandan;

注意：使用視圖時，將其當作表進行操作即可，由於視圖是虛擬表，所以使用其對真實表進行建立、更新和刪除操作，僅能做查詢用。

觸發器

對某個表進行【增/刪/改】操作的前後如果希望觸發某個特定的行為時，可以使用觸發器，觸發器用於定製使用者對錶的行進行【增/刪/改】前後的行為。

建立觸發器：

delimiter //create trigger tg_before_insert_student before insert on student for each rowbegin    if new.name==‘alex‘ then    insert into student_back (name) values (‘alex_back‘);end //delimiter ;

也可以用 after insert 表示插入之後執行觸發器中語句，還有其他動作：before update、after update、before delete 、after delete

注意：NEW表示即將插入的資料行，OLD表示即將刪除的資料行

刪除觸發器：drop tgigger tg_before_insert_student

觸發器無法由使用者直接調用，而知由於對錶的【增/刪/改】操作被動引發的。

預存程序

預存程序是一個SQL語句集合，當主動去調用預存程序時，其中內部的SQL語句會按照邏輯執行。

-- 建立預存程序delimiter //create procedure p1()BEGIN    select * from t1;END//delimiter ;-- 執行預存程序call p1()

對於預存程序，可以接受參數，其參數類型有三類：

    in 只用於傳入參數

    out 用於傳回值

    inout 即可以傳入又可以當作傳回值

有參數的預存程序：

-- 建立預存程序delimiter \create procedure p1(    in i1 int,    in i2 int,    inout i3 int,    out r1 int)BEGIN    DECLARE temp1 int;    DECLARE temp2 int default 0;        set temp1 = 1;    set r1 = i1 + i2 + temp1 + temp2;        set i3 = i3 + 100;end\delimiter ;-- 執行預存程序set @t1 =4;set @t2 = 0;CALL p1 (1, 2 ,@t1, @t2);SELECT @t1,@t2;

結果集加傳回值：

delimiter //                    create procedure p2(                        in n1 int,                        inout n3 int,                        out n2 int,                    )                    begin                        declare temp1 int ;                        declare temp2 int default 0;                        select * from v1;                        set n2 = n1 + 100;                        set n3 = n3 + n1 + 100;                    end //                    delimiter ;

事務型預存程序：

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

遊標：

delimiter //                    create procedure p3()                    begin                         declare ssid int; -- 自訂變數1                          declare ssname varchar(50); -- 自訂變數2                          DECLARE done INT DEFAULT FALSE;                        DECLARE my_cursor CURSOR FOR select sid,sname from student;                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;                                                open my_cursor;                            xxoo: LOOP                                fetch my_cursor into ssid,ssname;                                if done then                                     leave xxoo;                                END IF;                                insert into teacher(tname) values(ssname);                            end loop xxoo;                        close my_cursor;                    end  //                    delimter ;

動態執行SQL：

delimiter \                    CREATE PROCEDURE p4 (                        in nid int                    )                    BEGIN                        PREPARE prod FROM ‘select * from student where sid > ?‘;                        EXECUTE prod USING @nid;                        DEALLOCATE prepare prod;                     END\                    delimiter ;

刪除預存程序：drop procedure proc_name;

執行預存程序：

-- 無參數call proc_name()-- 有參數，全incall proc_name(1,2)-- 有參數，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)

函數

MySql中的函數分為：內建函數、自訂函數

內建函數參考：

http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions

自訂函數：

delimiter \create function f1(    i1 int,    i2 int)returns intBEGIN    declare num int;    set num = i1 + i2;    return(num);END \delimiter ;

MySQL之視圖、預存程序、觸發器、函數、事務、動態執行SQL