本文參考自 《深入淺出Mysql》mysql儲存程式相關用法
儲存函數將向調用者返回結果
儲存函數建立執行個體mysql> delimiter $ //設定$為分隔字元mysql> create function get_carname(car_id int) //建立函數 參數為car_id -> returns varchar(100) //函數返回值 -> reads sql data -> begin -> return (select name from car_info where id = car_id); -> end$ //結束符Query OK, 0 rows affectedmysql> delimiter ; mysql> select get_carname(100); //調用函數getcarname(100);+------------------------------------------------+| get_carname(100) |+------------------------------------------------+| 起亞K2 2012款 兩廂 1.6L AT Premium紀念版111122 |+------------------------------------------------+1 row in set
儲存函數查看show function status;show create function get_carname; show function status like 'get_car%';
儲存函數刪除drop function get_carname;
預存程序建立執行個體mysql> create procedure show_name(car_id int) -> begin -> select name from car_info where id = car_id; -> update car_info set name = '123456' where id = car_id; -> end$1304 - PROCEDURE show_name already existsmysql> create procedure show_carname(car_id int) -> begin -> select name from car_info where id = car_id; -> update car_info set name = '123456' where id = car_id; -> end$Query OK, 0 rows affectedmysql> call show_carname(100); -> $+------------------------------------------------+| name |+------------------------------------------------+| 起亞K2 2012款 兩廂 1.6L AT Premium紀念版111122 |+------------------------------------------------+1 row in set
預存程序的參數類型IN參數：調用者把IN參數傳值給過程OUT參數：過程把值賦值給OUT參數
mysql> create procedure get_carname(in car_id int,out car_name varchar(100)) -> begin -> select name into car_name from car_info where id = car_id; -> end$Query OK, 0 rows affectedmysql> delimiter ;mysql> call get_carname(100,@car_name);Query OK, 0 rows affectedmysql> select @car_name;+-----------+| @car_name |+-----------+| 123456 |+-----------+1 row in set
預存程序特徵值介紹
LANGUAGE SQL 預設，routine_boyd由SQL組成
[NOT]DETERMINISTIC 指明預存程序的執行結果是否是確定的，預設不確定
CONSTAINS SQL 子程式包含SQL，但不包含讀寫資料的語句，預設
NO SQL 子程式中不包含SQL語句
READS SQL DATA 子程式中包含讀資料的語句
MODIFIES SQL DATA 子程式中包含了寫資料的語句
SQLSECURITY {DEFINER|INVOKER} 指明誰有許可權執行。
DEFINER 只有定義者自己才能夠執行，預設
INVOKER 表示調用者可以執行
預存程序中變數的使用變數定義：declare procedure_temp varchar(100); 變數賦值：set procedure_temp = 'hello'；也可以將查詢結果賦值給變數：select name into procedure_temp ;
mysql> create procedure procedure_test() -> begin -> declare continue handler for sqlstate '23000' set @x1 = 1; -> set @x2 = 2; -> insert into web_car_brands(name) values('dazhong'); -> set @x2 = 3; -> end$紅色部分表示mysql執行是遇到錯誤、異常、警告時採取的方式，目前支援continue 和exit兩種方式錯誤類別包括：sqlwarning，not found ，sqlstate
預存程序中流程式控制制語句的使用，待完善......
預存程序中游標的使用create procedure curser_test() -> begin -> declare count int; -> declare car_name varchar(100); -> declare cur cursor for select name from car_info where id <10; //聲明游標 -> declare exit handler for not found close cur; -> -> set @x1 = 0; -> set @x2 = 0; -> -> open cur; /開啟游標 -> -> repeat -> fetch cur into car_name; // fetch 游標 -> if(car_name == '凱迪拉克') -> then set @x1 = 1; -> else -> set @x2 = 1; -> end if; -> until 0 end repeat; -> close cur; //關閉游標 -> end$
當某個資料表被INSERT、DELETE、UPDATE時，觸發器將自動執行。
觸發器建立執行個體mysql> delimiter $mysql> create trigger trigger_test //觸發器名稱為trigger_test -> after insert on car_info for each row begin //在插入操作之後執行 -> insert into web_car_brands(name) values('dahzong'); //要執行的動作 -> end; -> $
觸發器被觸發的語句如下：INSERT、DELETE、UPDATE
查看觸發器 show triggers;
刪除觸發器drop trigger trigger_test;
觸發器使用注意觸發器按照BEFORE觸發器、行操作、AFTER觸發器的順序執行，其中任何一部發生錯誤的操作都不會繼續執行剩下的操作。對於事務操作，如果期間發生錯誤，整個交易回復，對於非事務，如果發生錯誤，那麼已經執行的部分將無法復原。
視圖建立執行個體mysql> create view view_test as //建立視圖view_test -> select name from car_info where id = 100;Query OK, 0 rows affected
查看視圖不能通過show views方式查看視圖，通過show tables 可以查看到所有的表和視圖show table status like 'view%';show create view view_test;select * from information_schema.views where table_name = 'view_test'; //通過INFORMATION_SCHEMA.VIEWS 查看
視圖刪除 drop view view_test;
mysql視圖若干限制：
- from關鍵字後不能包含子查詢
- 以下類型視圖補課更新：彙總函式（SUM、MIN、MAX、COUNT）、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL
- SELECT中包含子查詢
- JION
- FORM一個補課更新的視圖
使用視圖的優點：
- 資料獨立：可以屏蔽表結果變化對使用者的影響，源表增加列對視圖沒有影響，源表修改列，只需修改視圖對應列即可
- 安全：使用者只能訪問視圖呈現的結果集，可以限制到某個行、某個列
- 簡單：使用者使用視圖不許關心其背後對應的表結果、關聯條件、篩選條件，直接呈現對應條件的結果集
事件調度器可以安排資料庫在預訂的時間執行某個事件，預設情況下事件調度器不會開啟，查看事件調度器狀態： show variables like 'event_scheduler';開啟事件調度器：set global event_scheduler = ON;
事件建立執行個體mysql> create event expire_web_session //建立事件expire_web_session -> on schedule every 4 hour //每四小時執行一次 -> do -> delete from car_info where publish_date < current_timestamp - interval 1 day; //要執行的事件Query OK, 0 rows affected
停止某個事件mysql> alter event expire_web_session disable;
開啟某個事件alter event expire_session enable;
