MySQL之函數(shù)、存儲過程和觸發(fā)器

MySQL中函數(shù)、存儲過程和觸發(fā)器現(xiàn)在用得不多了，做了解就可以，因?yàn)楝F(xiàn)在數(shù)據(jù)庫性能是很多系統(tǒng)的瓶頸了，這些操作都可以在數(shù)據(jù)庫外由專業(yè)的編程語言如java等來完成，以減輕數(shù)據(jù)庫負(fù)擔(dān)，數(shù)據(jù)庫只負(fù)責(zé)數(shù)據(jù)存儲。

函數(shù)

系統(tǒng)函數(shù)和自定義函數(shù)

系統(tǒng)函數(shù):https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

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

保存在mysql.proc表中

創(chuàng)建UDF:

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

RETURNS {STRING|INTEGER|REAL}

runtime_body

說明：

參數(shù)可以有多個(gè),也可以沒有參數(shù)

必須有且只有一個(gè)返回值

自定義函數(shù)

查看函數(shù)列表：

SHOW FUNCTIOIN STATUS;

查看函數(shù)定義

SHOW CREATE FUNCTION function_name

刪除UDF:

DROP FUNCTION function_name

調(diào)用自定義函數(shù)語法:

SELECT function_name(parameter_value,...)

示例:無參UDF

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

示例：有參數(shù)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 ;

自定義函數(shù)中定義局部變量語法:

DECLARE 變量1[,變量2,... ]變量類型 [DEFAULT 默認(rèn)值]

說明：局部變量的作用范圍是在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表中

創(chuàng)建存儲過程

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

routime_body

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

其中IN表示輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示既可以輸入也可以輸出；param_name表示參數(shù)名稱；type表示參數(shù)的類型

查看存儲過程列表

SHOW PROCEDURE STATUS

查看存儲過程定義

SHOW CREATE PROCEDURE sp_name

調(diào)用存儲過程:

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

CALL sp_name

說明:當(dāng)無參時(shí),可以省略"()",當(dāng)有參數(shù)時(shí),不可省略"()”

存儲過程修改:

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

刪除存儲過程:

DROP PROCEDURE [IF EXISTS] sp_name

創(chuàng)建無參存儲過程:

delimiter //

CREATE PROCEDURE showTime()

BEGIN

SELECT now();

END//

delimiter ;

CALL showTime;

創(chuàng)建含參存儲過程:只有一個(gè)IN參數(shù)

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;

創(chuàng)建含參存儲過程:包含IN參數(shù)和OUT參數(shù)

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;

說明:創(chuàng)建存儲過程deleteById,包含一個(gè)IN參數(shù)和一個(gè)OUT參數(shù).調(diào)用時(shí),傳入刪除的ID和保存被修改的行數(shù)值的用戶變量@Line,select @Line;輸出被影響行數(shù)

存儲過程優(yōu)勢:

存儲過程把經(jīng)常使用的SQL語句或業(yè)務(wù)邏輯封裝起來,預(yù)編譯保存在數(shù)據(jù)庫中,當(dāng)需要時(shí)從數(shù)據(jù)庫中直接調(diào)用,省去了編譯的過程

提高了運(yùn)行速度

同時(shí)降低網(wǎng)絡(luò)數(shù)據(jù)傳輸量

存儲過程與自定義函數(shù)的區(qū)別:

存儲過程實(shí)現(xiàn)的過程要復(fù)雜一些,而函數(shù)的針對性較強(qiáng)

存儲過程可以有多個(gè)返回值,而自定義函數(shù)只有一個(gè)返回值

存儲過程一般獨(dú)立的來執(zhí)行,而函數(shù)往往是作為其他SQL語句的一部分來使用

流程控制

存儲過程和函數(shù)中可以使用流程控制來控制語句的執(zhí)行

流程控制：

IF：用來進(jìn)行條件判斷。根據(jù)是否滿足條件，執(zhí)行不同語句

CASE：用來進(jìn)行條件判斷，可實(shí)現(xiàn)比IF語句更復(fù)雜的條件判斷

LOOP：重復(fù)執(zhí)行特定的語句，實(shí)現(xiàn)一個(gè)簡單的循環(huán)

LEAVE：用于跳出循環(huán)控制

ITERATE：跳出本次循環(huán)，然后直接進(jìn)入下一次循環(huán)

REPEAT：有條件控制的循環(huán)語句。當(dāng)滿足特定條件時(shí)，就會跳出循環(huán)語句

WHILE：有條件控制的循環(huán)語句

觸發(fā)器

觸發(fā)器的執(zhí)行不是由程序調(diào)用，也不是由手工啟動，而是由事件來觸發(fā)、激活從而實(shí)現(xiàn)執(zhí)行

創(chuàng)建觸發(fā)器

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

說明：

trigger_name：觸發(fā)器的名稱

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

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

tbl_name：該觸發(fā)器作用在表名

觸發(fā)器示例

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);

示例：創(chuàng)建觸發(fā)器

在向?qū)W生表INSERT數(shù)據(jù)時(shí)，學(xué)生數(shù)增加，DELETE學(xué)生時(shí)，學(xué)生數(shù)減少

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;

查看觸發(fā)器

SHOW TRIGGERS

查詢系統(tǒng)表information_schema.triggers的方式指定查詢條件，查看指定的觸發(fā)器信息。

mysql> USE information_schema;

Database changed

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

刪除觸發(fā)器

DROP TRIGGER trigger_name;