MySQL存储过程和触发器的语法

MySQL的存储过程

一、创建存储过程

存储过程就是具有名字的一段代码，用来完成一个特定的功能，

实质就是数据库 SQL 语言层面的代码封装与重用。

优点(为什么要用存储过程？)：

①将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

②批量处理：SQL+循环，减少流量，也就是“跑批”

③统一接口，确保数据的安全

CREATE
    [DEFINER = { user | CURRENT_USER }]　
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter: 
    [ IN | OUT | INOUT ] param_name type  #参数名 参数类型（ IN | OUT | INOUT ）
    
    #IN 	输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
	#OUT 	输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
	#INOUT	输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
	# 注：INOUT　尽量少用

characteristic:　
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

（一）存储过程中的关键语法

声明语句的结束符，可以自定义

DELIMITER $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
或
DELIMITER //

声明存储过程

CREATE PROCEDURE demo_in_parameter(IN p_in int) #带参的存储过程（也可以不带参）

存储过程的开始和结束符号

BEGIN .... END    

定义变量

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)，例如：

DECLARE l_int int unsigned default 4000000; #int类型、无符号类型、默认4000000
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';

变量赋值

SET @p_in=1  

调用存储过程

call sp_name[(传参)];  

（二）存储过程的控制语句

条件语句

(1)if-then-else语句：

if var=0 then #var代表一个变量
	#执行体，比如：
	insert into tableName values(17); #tableName里只有一个整型字段filedName
end if;  
if parameter=0 then 
	#执行体，比如：
  	update tableName set filedName=filedName+1;  
else 
	#执行体，比如：
  	update tableName set filedName=filedName+2;  
end if;  

(2)case语句：

case
    when var=0 then #var代表一个变量
        insert into tableName values(30);
    when var>0 then
    	insert into tableName values(20);
    when var<0 then
    	insert into tableName values(10);
    else
    	insert into tableName values(0);
end case

循环语句

（1）while ···· end while

类似C、C++、Java等语言中的while语句

while 条件 do
    -- 循环体
end while

示例：

declare var int;  
set var=0;  
while var<6 do  
	insert into tableName values(var);  
set var=var+1;  
end while;  

（2）repeat···· end repea

它在执行操作后检查结果，而 while 则是执行前进行检查。类似C、C++、Java等语言中的do … while语句

repeat
    -- 循环体
until 循环条件  
end repeat;

示例：

declare v int; 
set v=0;  
repeat  
	insert into tableName values(v);  
set v=v+1;  
until v>=5
end repeat;

（3） loop ·····endloop

loop 循环和 repeat 循环相似, leave 语句的意义是离开循环。

declare v int;  
set v=0;  
LOOP_LABLE: loop
	insert into tableName values(v);
set v=v+1;
if v >=5 then 
	leave LOOP_LABLE; 
end if;  
end loop;

（4）ITERATE迭代

ITERATE 通过引用复合语句的标号,来从新开始复合语句:

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;  

（三）存储过程的示例

delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name VARCHAR(255);#定义一个临时的字符类型的name
DECLARE ageVal INT;#定义一个临时的整型的age
DECLARE i INT;#定义一个整型的i
DECLARE pre_gender VARCHAR(255);#定义一个临时的字符类型的gender
DECLARE pre_nick_name VARCHAR(255);#定义一个临时的字符类型的nick_name

SET ageVal=100;#给ageVal变量赋值
SET i=1;#给i赋值初始值

WHILE i < 100 DO
		#循环产生一个随机的“姓名”的字符串，并赋值给pre_name
		SET pre_name=concat(substring('赵钱孙李龚',floor(1+5*rand()),1),substring('明国华建',floor(1+4*rand()),1),substring('明国华建',floor(1+4*rand()),1));
		#循环产生一个随机的“性别”的字符串，并赋值给pre_gender
		SET pre_gender=SUBSTRING('男女',floor(1+2*rand()),1);
		#循环产生一个随机的“昵称”的字符串，并赋值给pre_nick_name
		SET pre_nick_name=concat(substring('赵钱孙李',floor(1+4*rand()),1),substring('明国华建',floor(1+4*rand()),1),substring('明国华一',floor(1+4*rand()),1));
		INSERT INTO student(sname,age,gender,nick_name)VALUES(pre_name,(ageVal+i)%30,pre_gender,pre_nick_name);
SET i=i+1;
END WHILE;
END $$ #这里的$$相当于平时的SQL语句结束时的;   代表着语句真正结束

delimiter;　　#将语句的结束符号恢复为分号
call proc_batch_insert(); #调用存储过程

解析：默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。 在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

存储过程体

• 存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等

• 过程体格式：以begin开始，以end结束(可嵌套)

  label1: BEGIN #“label1”为语句贴上标签
  　　label2: BEGIN
  　　　　label3: BEGIN
  　　　　　　statements; 
  　　　　END label3;
  　　END label2;
  END label1
  

**注意：**每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

**标签有两个作用： **

• 1、增强代码的可读性
• 2、在某些语句(例如:leave和iterate语句)，需要用到标签

二、修改存储过程

ALTER PROCEDURE 数据库.存储过程名;

三、删除存储过程

DROP PROCEDURE 数据库.存储过程名;

四、查询存储过程

SHOW CREATE PROCEDURE 数据库.存储过程名; -- 查看存储过程详细情况
select name from mysql.proc where db='数据库名'; -- 查询某个数据库中的存储过程的名字
select routine_name from information_schema.routines where routine_schema='数据库名';
-- 查询某个数据库中的routine_name
show procedure status where db='数据库名'; -- 查看某个存储过程的状态

MySQL中的触发器

！！尽量少使用触发器，不建议使用。

触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
　　[trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER } #触发时机：触发操作前、触发操作后

trigger_event: { INSERT | UPDATE | DELETE } #触发事件：增加、更新、删除

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

示例：通过触发器实现级联更新、删除

#级联更新的触发器
DROP TRIGGER IF EXISTS `ins_goods`;
create trigger ins_goods
after insert on goods for each row 
begin
	  insert into shop_goods ( cId, gId)
		values( new.cId, new.gId);
end;
#级联删除的触发器
DROP TRIGGER IF EXISTS `del_goods`;
create trigger del_goods
after delete on goods for each row 
begin
	  delete from shop_goods where cId = old.cId;
end;