目錄

目標

存盤程序

• 基本的創建語法
• 基本的呼叫語法
• 基本的語法案例
• 中斷存盤程序
• 存盤程序的事務
• 存盤程序內回圈

函式

• 基本的創建語法
• 基本的呼叫語法

觸發器

• 觸發順序
• 觸發條件
• 觸發的資料型別
• 基本的創建語法

事件

• 常用語法
• 從2000-11-16 23:59:00開始，每天定時執行一次
• 從2000-11-16 23:59:00開始，每小時定時執行一次
• 從2000-11-16 23:59:00開始，每15分鐘定時執行一次

注意事項

目標

• 熟練使用MySQL存盤程序、函式、觸發器、事件，

存盤程序

基本的創建語法

DELIMITER $$

CREATE
    PROCEDURE `資料庫名稱`.`存盤程序名稱`(引數串列)
    BEGIN
	/*********************下面寫業務，**********************/

	/*********************上面寫業務，**********************/
    END$$
 
DELIMITER ;

基本的呼叫語法

call 存盤程序名(引數串列);

基本的語法案例

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `pro_find_user_by_id`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_find_user_by_id`(param_user_id INT(10))
BEGIN
/*創建一個用戶名變數*/
DECLARE var_user_name VARCHAR(64);
/*創建一個成績變數*/
DECLARE var_achievement DOUBLE(3,2);
/*根據用戶ID查詢用戶名和成績，并給變數名賦值，*/
SELECT user_name,achievement INTO var_user_name,var_achievement FROM `user_info` WHERE id=param_user_id;
IF
	/*如果用戶名為空*/
	var_user_name IS NULL OR  LENGTH(REPLACE(var_user_name,' ',''))=0
THEN
	/*回傳資訊*/
	SELECT '用戶名為空，' msg,'500' resultCode;
ELSEIF
	var_achievement IS NULL OR var_achievement<60
THEN
	SELECT '不及格' msg,var_user_name userName,var_achievement achievement,'200' resultCode;
ELSE
	SELECT '及格' msg,var_user_name userName,var_achievement achievement,'200' resultCode;
END IF;
    END$$

DELIMITER ;

中斷存盤程序

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `pro_judge`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_judge`(param_a INT(10))
tx:BEGIN
IF
	param_a>0
THEN
	SELECT CONCAT(param_a,'大于0') msg;
	/*在這里終止存盤程序，不會再繼續下去，*/
	LEAVE tx;
END IF;
	/*在這里終止存盤程序，不會再繼續下去，*/
	SELECT CONCAT(param_a,'不大于0') msg;
    END$$

DELIMITER ;

存盤程序的事務

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `pro_insert_user`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_insert_user`()
BEGIN

DECLARE var_b INT(10);
	
	/*事務標識*/
	DECLARE t_error INTEGER DEFAULT 0;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
    START TRANSACTION;
        
        /*向用戶表插入一條資料，*/
        INSERT INTO `user_info` (user_name,achievement)VALUES('zhangsan',99);
        
        /*在這里定義一個例外:設定var_b的值，*/
	    SET var_b= 1/0;
IF 
	t_error = 1 
THEN
	/*失敗回滾，*/
	ROLLBACK;
ELSE
	/*成功提交，*/
        COMMIT;
END IF;

    END$$

DELIMITER ;

存盤程序內回圈

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `pro_while`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_while`(param_i INT(10))
BEGIN
	
	WHILE param_i > 0 DO 
	/*可以在這里寫業務*/
	SET param_i=param_i-1;
	END WHILE;
	
	/*列印param_i最后的值*/
	SELECT param_i ;
	
    END$$

DELIMITER ;

函式

基本的創建語法

DELIMITER $$
 
CREATE
    FUNCTION `資料庫名稱`.`函式名`(param_a INT(5),param_b INT(5))
    /*定義函式回傳型別，我這里定義回傳INT型別，*/
    RETURNS INT(6)
    BEGIN
/*********************下面寫業務，**********************/

/*********************上面寫業務，**********************/
/*回傳同樣是INT型別*/
RETURN param_a+param_b;
    END$$
 
DELIMITER ;

基本的呼叫語法

SELECT 函式名稱(引數串列);

觸發器

觸發順序

有兩種，分別是：
1.BEFORE表示對表操作前執行觸發器；
2.AFTER表示對表操作后執行觸發器；

觸發條件

有三種，分別是：
1.INSERT表示對表進行插入資料才會觸發；
2.UPDATE表示對表進行修改資料才會觸發；
3.DELETE表示對表進行洗掉資料才會觸發，

觸發的資料型別

有兩種，分別是：
1.新資料（NEW.欄位名）；
2.舊資料（OLD.欄位名），
其中，新資料只有在觸發條件是INSERT、UPDATE才有會產生；舊資料只有在UPDATE、DELETE產生，

基本的創建語法

DELIMITER $$
 
CREATE
/*AFTER和INSERT組合使用，表示資料插入以后觸發該觸發器，*/
    TRIGGER `資料庫名`.`觸發器名稱` AFTER INSERT
    /*監控user_info表*/
    ON `資料庫名`.`監控的表名`
    FOR EACH ROW BEGIN
    /***********************下面寫業務***************************/
    
	/*表示每當有資料插入user_info表時，會向user_info_copy插入新產生的資料，*/
	INSERT INTO `user_info_copy` (user_name,achievement)VALUES(NEW.user_name,NEW.achievement);
	
    /***********************上面寫業務***************************/
    END$$
 
DELIMITER ;

事件

常用語法

從2000-11-16 23:59:00開始，每天定時執行一次

DELIMITER $$ 

CREATE	EVENT `資料庫名`.`事件名`ON SCHEDULE EVERY 1 DAY STARTS '2000-11-16 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
/*************************下面寫業務************************/

/*************************上面寫業務************************/

	END$$
DELIMITER ;

從2000-11-16 23:59:00開始，每小時定時執行一次

DELIMITER $$ 

CREATE	EVENT `資料庫名`.`事件名`ON SCHEDULE EVERY 1 HOUR STARTS '2000-11-16 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
/*************************下面寫業務************************/

/*************************上面寫業務************************/

	END$$
DELIMITER ;

從2000-11-16 23:59:00開始，每15分鐘定時執行一次

DELIMITER $$ 

CREATE	EVENT `資料庫名`.`事件名`ON SCHEDULE EVERY 15 MINUTE STARTS '2000-11-16 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
/*************************下面寫業務************************/

/*************************上面寫業務************************/

	END$$
DELIMITER ;

注意事項

1. 存盤程序和函式的引數可以為空，如有多個引數需要用逗號分隔，引數格式為：引數名稱 資料型別，比如：param_username VARCHAR(64)；
2. SQL結尾處必須加分號，IF與THEN之間的判斷SQL陳述句結尾處不能加分號；
3. ELSE和IF組合使用的語法為：ELSEIF，而不是ELSE IF；
4. 為了避免引數名、變數名、表欄位重復而發生沖突，建議統一引數名和變數名的前綴，引數名形如：param_username；變數名形如：var_username；
5. 為了區分觸發器、函式、存盤程序、事件，建議各個名稱前綴統一，函式名形如：fun_*；存盤程序名形如：pro_*；觸發器名形如：trigger_*；事件名形如：even_*，