MySQL之函數、存儲過程和觸發器

MySQL中函數、存儲過程和觸發器現在用得不多了，做了解就可以，因為現在數據庫性能是很多系統的瓶頸了，這些操作都可以在數據庫外由專業的編程語言如java等來完成，以減輕數據庫負擔，數據庫只負責數據存儲。

函數

系統函數和自定義函數

系統函數:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

自定義函數 (user-defined function UDF)

保存在mysql.proc表中

創建UDF:

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])

RETURNS {STRING|INTEGER|REAL}

runtime_body

說明：

參數可以有多個,也可以沒有參數

必須有且只有一個返回值

自定義函數

查看函數列表：

SHOW FUNCTIOIN STATUS;

查看函數定義

SHOW CREATE FUNCTION function_name

刪除UDF:

DROP FUNCTION function_name

調用自定義函數語法:

SELECT function_name(parameter_value,...)

示例:無參UDF

CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!";

示例：有參數UDF

DELIMITER //

CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)

BEGIN

DELETE FROM students WHERE stuid = uid;

RETURN (SELECT COUNT(uid) FROM students);

END//

DELIMITER ;

自定義函數中定義局部變量語法:

DECLARE 變量1[,變量2,... ]變量類型 [DEFAULT 默認值]

說明：局部變量的作用范圍是在BEGIN...END程序中,而且定義局部變量語句必須在BEGIN...END的第一行定義

示例:

DELIMITER //

CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)

RETURNS SMALLINT

BEGIN

DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;

SET a = x, b = y;

RETURN a+b;

END//

為變量賦值語法

SET parameter_name = value[,parameter_name = value...]

SELECT INTO parameter_name

示例:

...

DECLARE x int;

SELECT COUNT(id) FROM tdb_name INTO x;

RETURN x;

END//

存儲過程

存儲過程保存在mysql.proc表中

創建存儲過程

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])

routime_body

其中:proc_parameter : [IN|OUT|INOUT] parameter_name type

其中IN表示輸入參數，OUT表示輸出參數，INOUT表示既可以輸入也可以輸出；param_name表示參數名稱；type表示參數的類型

查看存儲過程列表

SHOW PROCEDURE STATUS

查看存儲過程定義

SHOW CREATE PROCEDURE sp_name

調用存儲過程:

CALL sp_name ([ proc_parameter [,proc_parameter ...]])

CALL sp_name

說明:當無參時,可以省略"()",當有參數時,不可省略"()”

存儲過程修改:

ALTER語句修改存儲過程只能修改存儲過程的注釋等無關緊要的東西,不能修改存儲過程體,所以要修改存儲過程,方法就是刪除重建

刪除存儲過程:

DROP PROCEDURE [IF EXISTS] sp_name

創建無參存儲過程:

delimiter //

CREATE PROCEDURE showTime()

BEGIN

SELECT now();

END//

delimiter ;

CALL showTime;

創建含參存儲過程:只有一個IN參數

delimiter //

CREATE PROCEDURE seleById(IN id SMALLINT UNSIGNED)

BEGIN

SELECT * FROM students WHERE stuid = id;

END//

delimiter ;

call seleById(2);

示例

delimiter //

CREATE PROCEDURE dorepeat(p1 INT)

BEGIN

SET @x = 0;

REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;

END

//

delimiter ;

CALL dorepeat(1000);

SELECT @x;

創建含參存儲過程:包含IN參數和OUT參數

delimiter //

CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)

BEGIN

DELETE FROM students WHERE stuid = id;

SELETE row_count() into num;

END//

delimiter ;

call seleById(2,@Line);

SELETE @Line;

說明:創建存儲過程deleteById,包含一個IN參數和一個OUT參數.調用時,傳入刪除的ID和保存被修改的行數值的用戶變量@Line,select @Line;輸出被影響行數

存儲過程優勢:

存儲過程把經常使用的SQL語句或業務邏輯封裝起來,預編譯保存在數據庫中,當需要時從數據庫中直接調用,省去了編譯的過程

提高了運行速度

同時降低網絡數據傳輸量

存儲過程與自定義函數的區別:

存儲過程實現的過程要復雜一些,而函數的針對性較強

存儲過程可以有多個返回值,而自定義函數只有一個返回值

存儲過程一般獨立的來執行,而函數往往是作為其他SQL語句的一部分來使用

流程控制

存儲過程和函數中可以使用流程控制來控制語句的執行

流程控制：

IF：用來進行條件判斷。根據是否滿足條件，執行不同語句

CASE：用來進行條件判斷，可實現比IF語句更復雜的條件判斷

LOOP：重復執行特定的語句，實現一個簡單的循環

LEAVE：用于跳出循環控制

ITERATE：跳出本次循環，然后直接進入下一次循環

REPEAT：有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句

WHILE：有條件控制的循環語句

觸發器

觸發器的執行不是由程序調用，也不是由手工啟動，而是由事件來觸發、激活從而實現執行

創建觸發器

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

說明：

trigger_name：觸發器的名稱

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后觸發（在事件之前觸發的話，事件會被替代）

trigger_event:：{ INSERT |UPDATE | DELETE }，觸發的具體事件

tbl_name：該觸發器作用在表名

觸發器示例

CREATE TABLE student_info (

stu_no INT(11) NOT NULL AUTO_INCREMENT,

stu_name VARCHAR(255) DEFAULT NULL,

PRIMARY KEY (stu_no)

);

CREATE TABLE student_count (

student_count INT(11) DEFAULT 0

);

INSERT INTO student_count VALUES(0);

示例：創建觸發器

在向學生表INSERT數據時，學生數增加，DELETE學生時，學生數減少

CREATE TRIGGER trigger_student_count_insert

AFTER INSERT

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count+1;

CREATE TRIGGER trigger_student_count_delete

AFTER DELETE

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count-1;

查看觸發器

SHOW TRIGGERS

查詢系統表information_schema.triggers的方式指定查詢條件，查看指定的觸發器信息。

mysql> USE information_schema;

Database changed

mysql> SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';

刪除觸發器

DROP TRIGGER trigger_name;