数据库存储过程及触发器

发表于 2018-05-31 更新于 2021-03-21 分类于数据库

存储过程和触发器

一.关键语法

DELIMITER 声明语句结束符
CREATE PROCEDURE 声明存储过程
BEGIN … END 储存过程开始和结束符号
SET 变量赋值
DECLARE 变量定义
DEFINER 存储过程的权限

二.入门实例

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
SELECT COUNT(*) FROM `test`.`info`;
END$$
DELIMITER ;

三.存储过程使用

1 2	CALL test(); 调用存储过程 SET @ip=1; 存储过程外定义变量

四.参数

IN 输入参数,表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数,该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数,调用时指定，并且可被改变和返回

1.IN参数实例

set @ip=1;

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN ip INT)
BEGIN
SELECT ip; // 1
SET ip=10;
SELECT ip; // 10
END$$

DELIMITER ;

CALL test(@ip);

select @ip; // 1

外影响内,内并不影响外的值

2.OUT参数实例

set @ip=1;

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(OUT ip INT)
BEGIN
SELECT ip; // NULL
SET ip=10;
SELECT ip; // 10
END$$

DELIMITER ;

CALL test(@ip);

select @ip; // 10

存储过程内的会影响外部的变量,但是外部的不会影响存储过程内的变量

3.INOUT参数实例

set @ip=1;

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(INOUT ip INT)
BEGIN
SELECT ip; // 1
SET ip=10;
SELECT ip; // 10
END$$

DELIMITER ;

CALL test(@ip);

select @ip; // 10

内外都能影响

五.变量

1.变量定义

局部变量声明一定要放在存储过程体的开始
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

2.变量赋值

1	set ip = value;

3.用户变量

SELECT 'Hello World' into @x;
SELECT @x;
SET @y='Goodbye Cruel World';
SELECT @y;
SET @z=1+2+3;
SELECT @z;
// 存储过程中使用用户变量
CREATE PROCEDURE GreetWorld() SELECT CONCAT(@greeting,' World');
SET @greeting='Hello';
CALL GreetWorld();  
// 存储过程间传递全局范围的用户变量
CREATE PROCEDURE p1() SET @last_procedure='p1';
CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);
CALL p1();
CALL p2();

注意:

用户变量名一般以@开头
滥用用户变量会导致程序难以理解及管理

六.注释

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(INOUT ip INT) -- 创建存储过程
BEGIN
SELECT ip; -- 输出 1
/*
SET ip=10;
SELECT ip; -- 输出 10
*/
END$$

DELIMITER ;

七.存储过程的增删改查

1.创建

1	CREATE DEFINER=`root`@`localhost` PROCEDURE 存储过程名;

2.删除

1	DROP PROCEDURE 存储过程名;

3.修改

1	ALTER PROCEDURE 存储过程名

4.查看

SELECT NAME FROM mysql.proc WHERE db='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
SHOW PROCEDURE STATUS WHERE db='数据库名';
SHOW CREATE PROCEDURE 数据库.存储过程名;

八.存储过程的控制语句

1.变量的作用域

DELIMITER //  
CREATE PROCEDURE proc3(OUT x1 VARCHAR(5))  
BEGIN 
DECLARE x1 VARCHAR(5) DEFAULT 'outer';  
BEGIN 
DECLARE x1 VARCHAR(5) DEFAULT 'inner';  
SELECT x1; -- inner
END;  
SELECT x1; -- outer
END;  
//
DELIMITER ;

只在自已的定义域中有效,出了定义域无效,可以通过out参数或者将其值指派给会话变量来保存其值。

2.if -then -else语句

DELIMITER //  
CREATE PROCEDURE proc2(IN parameter INT)  
BEGIN 
DECLARE var INT;  
SET var=parameter+1;  

IF var=0 THEN 
    INSERT INTO t VALUES(17);  
END IF;  

IF parameter=0 THEN 
    UPDATE t SET s1=s1+1;  
ELSE 
    UPDATE t SET s1=s1+2;  
END IF;  
END;
//  
DELIMITER ;

3.case语句

DELIMITER //  
CREATE PROCEDURE proc3 (IN parameter INT)  
BEGIN 
DECLARE var INT;  
SET var=parameter+1;  
CASE var  
WHEN 0 THEN   
    INSERT INTO t VALUES(17);  
WHEN 1 THEN   
    INSERT INTO t VALUES(18);  
ELSE   
    INSERT INTO t VALUES(19);  
END CASE;  
END;  
//  
DELIMITER ;

4.while 条件 DO … end while;语句

DELIMITER //  
CREATE PROCEDURE proc4()  
BEGIN 
DECLARE var INT;  
SET var=0;  
WHILE var<6 DO  
    INSERT INTO t VALUES(var);  
    SET var=var+1;  
END WHILE;
END;  
//  
DELIMITER ;

在执行循环体之前进行判断

5.repeat …until 条件 end repeat;语句

DELIMITER //  
CREATE PROCEDURE proc5 ()  
BEGIN   
DECLARE v INT;  
SET v=0;  
REPEAT  
    INSERT INTO t VALUES(v);  
    SET v=v+1;  
UNTIL v>=5
END REPEAT;  
END;  
//  
DELIMITER ;

在执行循环体之后进行判断

6.loop … leave end loop;语句

DELIMITER //  
CREATE PROCEDURE proc6 ()  
BEGIN 
DECLARE v INT;  
SET v=0;  
LOOP_LABLE:LOOP  
    INSERT INTO t VALUES(v);  
    SET v=v+1;  
    IF v >=5 THEN 
        LEAVE LOOP_LABLE;  
    END IF;  
END LOOP;  
END;  
//  
DELIMITER ;

leave语句的意义是离开循环,LOOP_LABEL是自定义的标号

7.LABLES 标号:

在使用loop的时候，使用到的labels标号，对于labels可以用到while，loop，rrepeat等循环控制语句中。

create procedure pro13()
label_1:begin
label_2:while 0=1 do leave label_2;end while;
label_3:repeat leave label_3;until 0=0 end repeat;
label_4:loop leave label_4;end loop;
end;

-- 加了结束标号的语句,功能相同
create procedure pro14()
label_1:begin
label_2:while 0=1 do leave label_2;end while label_2;
label_3:repeat leave label_3;until 0=0 end repeat label_3;
label_4:loop leave label_4;end loop label_4;
end label_1;

8.ITERATE迭代

ITERATE只能出现在LOOP，REPEAT和WHILE语句中，它的意思是“再次循环”,类似于continue

DELIMITER //  
CREATE PROCEDURE proc11()  
BEGIN 
DECLARE v INT;  
SET v=0;  
LOOP_LABLE:LOOP  
    IF v=3 THEN   
        SET v=v+1;  
	    ITERATE LOOP_LABLE;  
	END IF;  
    INSERT INTO t VALUES(v);  
    SET v=v+1;  
	IF v>=5 THEN 
	    LEAVE LOOP_LABLE;  
    END IF;  
END LOOP;  
END;  
//  
DELIMITER ;

首先i的值为0，条件判断语句if i=3 then判断为假，跳过if语段，向数据库中插入0，然后i+1，同样后面的if i>=5 then判断也为假，也跳过；继续循环，同样插入1和2；在i=3的时候条件判断语句if i=3 then判断为真，执行i=i+1，i值为4，然后执行迭代iterate loop_label;，即语句执行到iterate loop_label;后直接跳到if i=3 then判断语句，执行判断，这个时候由于i=4，if i=3 then判断为假，跳过IF语段，将4添加到表中，i变为5，条件判断if i>=5 then判断为真，执行leave loop_label;跳出loop循环，然后执行end;//，结束整个存储过程。

九.存储过程中的基本函数

1.字符串类

CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,str1比str2小返回-1,大返回1,相同返回0
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
TRIM([[BOTH|LEADING|TRAILING][padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格

2.数学类

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注：返回类型并非均为整数,默认为整数
SQRT(number2) //开平方

3.日期时间类

ADDTIME (date2 ,time_interval)//将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ) //转换时区
CURRENT_DATE () //当前日期
CURRENT_TIME () //当前时间
CURRENT_TIMESTAMP () //当前时间戳
DATE (datetime) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type) //在date2上减去一个时间
DATEDIFF (date1 ,date2) //两个日期差
DAY (date) //返回日期的天
DAYNAME (date) //英文星期
DAYOFWEEK (date) //星期(1-7) ,1为星期天
DAYOFYEAR (date) //一年中的第几天
EXTRACT (interval_name FROM date) //从date中提取日期的指定部分
MAKEDATE (year ,day) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second) //生成时间串
MONTHNAME (date ) //英文月份名
NOW () //当前时间
SEC_TO_TIME (seconds) //秒数转成时间
STR_TO_DATE (string ,format) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2) //两个时间差
TIME_TO_SEC (time) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0

十.分页存储过程

DELIMITER //  
DROP PROCEDURE IF EXISTS pr_pager;
CREATE PROCEDURE pr_pager(
    IN p_table_name VARCHAR(1024),      -- 表名      
    IN p_fields VARCHAR(1024),          -- 列名
    IN p_page_size INT,                 -- 一页的大小
    IN p_page_now INT,                  -- 页码
    IN p_order_string VARCHAR(128),     -- 排序语句
    IN p_where_string VARCHAR(1024),    -- where条件
    OUT p_out_rows INT                    
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT '分页存储过程'

BEGIN
  
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);

SET m_begin_row = (p_page_now - 1) * p_page_size;   -- 开始行=(页面-1)*一页的大小
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);   -- 分页拼接 LIMIT 开始行,一页的大小

-- SELECT COUNT(*) INTO @ROWS_TOTAL FROM 表名 where条件); 将总行数赋值给ROWS_TOTAL,作为返回值
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);
-- SELECT 列名 FROM 表名 where条件 order排序 limit语句; 最后的输出页数据
SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string,m_limit_string);

PREPARE count_stmt FROM @COUNT_STRING;  -- 预定义sql
EXECUTE count_stmt;                     -- 执行sql    
DEALLOCATE PREPARE count_stmt;          -- 释放掉连接
SET p_out_rows = @ROWS_TOTAL;

PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
    
END;
//
DELIMITER ;

call pr_pager("t","var",3,3,"","",@result);
call pr_pager("t","var",3,2,"","",@result);

十一.存储过程的优点

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

补充:触发器

1.语法

CREATE TRIGGER 触发器名
触发时机(BEFORE,AFTER)
触发事件(INSERT,UPDATE,DELETE) ON 被触发的表名
FOR EACH ROW
触发器程序体

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

2.触发事件

MySQL除了对INSERT、UPDATE、DELETE基本操作进行定义外，还定义了LOAD DATA和REPLACE语句，这两种语句也能引起上述6中类型的触发器的触发。

LOAD DATA语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。

REPLACE语句一般来说和 INSERT 语句很像，只是在表中有primary key **或unique索引时，如果插入的数据和原来primary key或unique**索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条。

INSERT语句，有时候等价于一条DELETE语句加上一条INSERT语句。

INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE语句触发；

UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；

DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

3.触发器程序体

statement_list代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。

1
2
3

BEGIN
[statement_list]
END

4.触发器实例

DELIMITER $
-- 创建一个在对student表进行INSERT事件之后进行一系列操作的触发器
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
-- 查询原来class表的stuCount
set c = (select stuCount from class where classID=new.classID);
-- 将stuCount+1
update class set stuCount = c + 1 where classID = new.classID;
end
$
DELIMITER ;

5.NEW和OLD详解

MySQL中定义了NEW和OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。

在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；

在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

使用方法： NEW.columnName（columnName为相应数据表某一列名）

另外，OLD是只读的，而NEW则可以在触发器中使用SET赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

6.触发器的查找与删除

-- 查找
SHOW TRIGGERS [FROM schema_name]
SELETE TRIGGER_SCHEMA,TRIGGER_NAME,DEFINER,ACTION_STATEMENT FROM TRIGGERS WHERE TRIGGER_SCHEMA='course';

-- 删除
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

7.触发器的执行顺序

建立的数据库一般都是InnoDB数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL会回滚事务，有：

如果BEFORE触发器执行失败，SQL无法正确执行。

SQL执行失败时，AFTER型触发器不会触发。

AFTER类型的触发器执行失败，SQL会回滚。