mysql存储过程函数触发器时间（note8）

第十周（MySQL存储过程函数触发事件）

6.1 存储过程

6.1.1 创建存储过程

  1. 创建存储命令

    create procedure 存储过程名 ([参数])

/*
参数的格式：
in 参数名 参数类型
out 参数名 参数类型
inout 参数名 参数类型
*/

create procedure 存储过程名 ([参数])
[特征] 存储过程体

  2. 存储过程特征

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

#LANGUAGE SQL:表名编写这个存储过程的语言为SQL语言
#DETERMINISTIC:表示存储过程对同样的输入参数产生相同的结果，默认为NOT DETERMINISTIC 表示产生					不确定的结果
#CONTAINS SQL:表示存储过程不包含读或写数据的语句
	/*
	NO SQL:表示存储过程不包含SQL语句
	READS SQL DATA:表示存储过程包含读，不包含写的语句
	MODIFIES SQL DATA:存储过程包含写数据的语句，默认为 CONTAINS SQL
	*/
#SQL SECURITY:可用来指定存储过程使用创建该存储过程的用户(DEFINER)的许可来执行，还是使用调用者				(INVOKER)的许可证来执行，默认为DEFINER
#COMMENT'string'：string里面是对存储过程的描述，使用SHOW CREATE PROCEDURE来显示

  3. 存储过程体

    SQL语句都是以分号结尾的，服务器处理遇到第一个分号就会认为程序结束。所以使用 'DELIMITER结束符号'命令将MySQL语句的结束标志修改为其它符号，最后在使用“ OPDELIMITER;”恢复以分号为结束标志

    DELIMITER $$	//以$$符号标志着存储过程的结束
CREATE PROCEDURE delete_student(IN xh CHAR(6))
#创建一个存储过程delete——student，in表示输入参数，输入参数名xh，字符型大小为6
BEGIN
DELETE FROM xs WHERE 学号=xh;
END $$
#begin...end表示一条sql语句的开始和结束，如果多条SQL语句，就需要DELIMITER来进行表示
DELIMITER ;

6.1.2 存储过程体

  1. 局部变量

    局部变量只能在begin...end语句块中声明，而且要在存储过程开头声明，如果不指定，默认为NULL，可以在begin...end语句块中使用，其他语句块不能使用，如下例二：

    # 语法格式：
DECLARE 变量名 ... 类型 [默认值]

#例：
declare num int(4);
declare str1,str2 varchar(6);

#例二：局部变量声明
...
begin
declare num int(4);#声明局部变量
...
end 结束符

  2. 使用set语句给局部变量赋值

    # 语法格式：
set 变量名=expr[,变量名=expr]...
#例：
set num=1,str1='hello';

  3. SELECT...INTO语句：直接把选中的值存储到变量中

    # 语法格式：
select 列名[,可包含多个] into 变量名[,变量名与列名个数对应] table_expr

  4. 流程控制语句

    1. if 语句
    # 语法格式：
if 条件 then 语句
[elseif 条件 then 语句]...
else 语句
end if

#例：
DELIMITER $$
CREATE PROCEDURE xscj.compar(IN k1 INTEGER,IN k2 INTEGER,IN k3 CHAR(6))
BEGIN
IF k1>k2 THEN
	SET k3='大于';
ELSEIF k1=k2 THEN
	SET k3='等于';
ELSE
	SET k3='小于';
END IF;
END $$
DELIMITER ;
    1. CASE 语句
    # 语法格式：
case expr
	when 值1(或条件1) then 语句
	[when 值2(或条件2) then 语句]
	...
	[else 语句]
end case

#例子：
DELIMITER $$
CREATE PROCEDURE xscj.result(IN str VARCHAR(4),OUT sex VARCHAR(4))
BEGIN
	CASE str
		WHEN 'm' THEN SET sex='男';
		WHEN 'f' THEN SET sex='女';
		ELSE SET sex ='无';
	END CASE;
END $$
DELIMITER ;
/*或者
	CASE 
		WHEN str='m' THEN SET sex='男';
		WHEN str='f' THEN SET sex='女';
		ELSE SET sex ='无';
	END CASE;
*/

    1. 循环语句

      # while语法格式：
[begin_label:]
while 条件 do
	语句
end while[end_table]

#while例子
DELIMITER $$
CREATE PROCEDURE mydowhile()
BEGIN
	DECLARE v1 INT DEFAULT 5;
	WHILE v1>0 DO
		SET v1=v1-1;
	END WHILE;
END $$
DELIMITER ;


# repeat语句格式：
[begin_label:]
	repeat
		语句
		until 条件
    end repeat[end_label]

    2. LOOP语句

      #语句格式：
[begin_label:]
	loop
		语句
    end loop[end_label]
    
# LEAVE语句经常和begin...end或循环一起使用
leave label


#例子：
DELIMITER $$
CREATE PROCEDURE mydoloop()
BEGIN
	SET @a=10;
	label:LOOP
		SET @a=@a-1;
		IF @a<0 THEN
			LEAVE label;
		END IF;
	END LOOP label;
END $$
DELIMITER ;

#调用该存储过程命令
call mydoloop();
#查看用户变量
select @a;

#再次循环语句，只能适用于loop，repeat，while语句中
iterate label

  5. 处理程序和条件

    为防止mysql在一条错误信息产生时就停止处理，需要用到 DECLARE HANDLER 语句

    #语法格式：
HANDLER FOR condition_value[,...]

    1. 处理程序的类型

      CONTINUE：mysql不中断存储过程的处理

      EXIT：当前BEGIN...END复合语句的执行被终止

      UNDO：处理程序类型语句暂时还不被支持

    2. condition_value

      condition_value=:
SQLSTATE [VALUE] sqlstate_value
|condition_name
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
|mysql_error_code

#sqlstate_value 给出SQLSTATE的代码表示
#condition_name 是处理条件的名称

    3. 存储过程语句

      
#创建一个存储过程，向xs表插入一行数据
#（'081101', '王民', '计算机', 1, '1994-02-10', 50 , NULL, NULL），
#已知学号081101在XS表中已存在。如果出现错误，程序继续进行。
DELIMITER $$
CREATE PROCEDURE my_insert()
BEGIN
	DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
	SET @x=2;
	INSERT INTO xs VALUES('081101','王民','计算机',1,'1994-02-10',50,NULL,NULL);
	SET @x=3;
END$$
DELIMITER ;

#调用存储过程查看结果的语法格式：
call my_insert();
select @x;


# 提高可读性 DECLARE condition_name CONDITION FOR condition_value
DELIMITER $$
CREATE PROCEDURE my_insert()
BEGIN
	DECLARE non_unique CONDITION FOR sqlstate '23000';
	DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
	SET @x=2;
	INSERT INTO xs VALUES('081101','王民','计算机',1,'1994-02-10',50,NULL,NULL);
	SET @x=3;
END$$
DELIMITER ;

6.1.3 游标及其应用

  1. 声明游标

    #语法格式
DECLARE 游标名 CURSOR FOR select语句
#一个存储过程中能有多个游标，一个块中每个游标必须有唯一的名字

#例
declare xs_cur1 cursor for
	select 学号,姓名,性别,出生日期,总学分
		from xs
		where 专业名='计算机';

  2. 打开游标

    OPEN 游标名

  3. 读取数据

    FETCH 游标名 INTO 变量名...

  4. 关闭游标

    close 游标名

# 例子：
close xs_cur2;

  5. 例题

    #创建一个存储过程，计算xs表中行的数目
DELIMITER $$
CREATE PROCEDURE compute(OUT number INTEGER)
BEGIN
	DECLARE xh CHAR(6);	#声明变量
	DECLARE FOUND BOOLEAN DEFAULT TRUE;
	DECLARE number_xs CURSOR FOR
		SELECT 学号 FROM xs;
	DECLARE CONTINUE HANDLER FOR NOT FOUND
		SET FOUND=FALSE;
	SET number=0;
	OPEN number_xs;
	FETCH number_xs INTO xh;
	WHILE FOUND DO
		SET number=number+1;
		FETCH number_xs INTO xh;
	END WHILE;
	CLOSE number_xs;	
END$$
DELIMITER ;

# 调用此存储过程并查看结果
CALL compute(@num);
SELECT @num;

6.1.4 存储过程的调用、修改和删除

  1. 调用

    call 存储过程名 ([参数...])

#例
create procedure do_query()
	select count(*) from xs order by 学号;
CALL do_query();

#例
CALL compar(3,6,@k3);
SELECT @k3;

/*
创建一个存储过程，有两个输入参数：xh和kcm，要求当某学生某门课程的成绩小于60分时将其学分修改为零，大于等于60分时将学分修改为此课程的学分。
*/
/*
创建一个存储过程，有两个输入参数：xh和kcm，
要求当某学生某门课程的成绩小于60分时将其学分修改为零，
大于等于60分时将学分修改为此课程的学分。
*/
DELIMITER $$
CREATE PROCEDURE xscj.do_update(IN xh CHAR(6),IN kcm CHAR(16))
BEGIN
	DECLARE kch CHAR(3);
	DECLARE xf TINYINT;
	DECLARE cj TINYINT;
	SELECT 课程号,学分 INTO kch,xf FROM kc WHERE 课程名=kcm;
	SELECT 成绩 INTO cj FROM xs_kc WHERE 学号=xh AND 课程号=kch;
	IF cj<60 THEN
		UPDATE xs_kc SET 学分=0 WHERE 学号=xh AND 课程号=kch;
	ELSE
		UPDATE xs_kc SET 学分=xf WHERE 学号=xh AND 课程号=kch;
	END IF;
END$$
DELIMITER ;
#插入一条值
INSERT INTO xs_kc VALUES('081101','208',50,10);
#调用存储过程，查询结果
CALL do_update('081101','数据结构');
SELECT * FROM xs_kc WHERE 学号='081101' AND 课程号='208';



#例子
#创建第一个存储过程
CREATE PROCEDURE xscj.do_insert1()
	INSERT INTO xs VALUES('091101','陶韦','软件工程',1,'1994-03-05',50,NULL,NULL);
#创建第二个存储过程	
DELIMITER $$
CREATE PROCEDURE xscj.do_insert2(IN X BIT(1),OUT str CHAR(8))
BEGIN
	CALL do_insert1();
	IF X=0 THEN
		UPDATE xs SET 姓名='刘英',性别=0 WHERE 学号='091101';
		SET str='修改成功';
	ELSEIF X=1 THEN 
		DELETE FROM xs WHERE 学号='091101';
		SET str='删除成功';
	END IF;
END$$
DELIMITER ;
#调用查看
CALL do_insert2(1,@str);
SELECT @str;#删除成功

CALL do_insert2(0,@str);
SELECT @str;#修改成功

  2. 存储过程的删除

    DROP PROCEDURE [IF EXISTS] 存储过程名

DROP PROCEDURE IF EXISTS dowhile;

  3. 存储过程的修改

    #语法格式
ALTER PROCEDURE  存储过程名 [特征 ...]
#特征
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'



#例子
DELIMITER $$
DROP PROCEDURE IF EXISTS do_query;
CREATE PROCEDURE do_query()
BEGIN
	SELECT * FROM xs;
END$$
DELIMITER ;

CALL do_query();

6.2 存储函数

6.2.1 创建存储函数

  1. 语法格式

    create function 存储过程名 ([参数...])
	returns type
	[特征...] 存储函数体
	
#例子创建一个存储函数，它返回xs表中学生的数目作为结果
DELIMITER $$
CREATE FUNCTION num_of_xs()
RETURNS INTEGER
BEGIN
	RETURN (SELECT COUNT(*) FROM xs);
END$$
DELIMITER ;

#创建一个存储函数，返回某个学生的姓名
DELIMITER $$
CREATE FUNCTION name_of_stu(xh CHAR(6))
RETURNS CHAR(8)
BEGIN
	RETURN (SELECT 姓名 FROM xs WHERE 学号=xh);
END$$
DELIMITER ;

#创建一个存储函数来删除xs_kc表中有，但xs表中不存在的学号
DELIMITER $$
CREATE FUNCTION delete_stu(xh CHAR(6))
RETURNS CHAR(8)
BEGIN
	DECLARE stu CHAR(6);
	SELECT 姓名 INTO stu FROM xs WHERE 学号=xh;
	IF stu IS NULL THEN
		DELETE FROM xs_kc WHERE 学号=xh;
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END$$
DELIMITER ;

6.2.2 存储函数的调用、修改和删除

  1. 存储函数的调用

    select 存储函数名 ([参数[,...]])

#例子
select num_of_xs();
select name_of_stu('081106');


/*
创建一个存储函数，通过调用存储函数NAME_OF_STU获得学号的姓名，判断姓名是否是“王林”，是则返回王林的出生日期，不是则返回“FALSE”
*/
DELIMITER $$
CREATE FUNCTION is_stu(xh CHAR(6))
	RETURNS CHAR(10)
BEGIN
	DECLARE NAME CHAR(8);
	SELECT name_of_stu(xh) INTO NAME;
	IF NAME='王林' THEN
		RETURN(SELECT 出生日期 FROM xs WHERE 学号=xh);
	ELSE
		RETURN 'false';
	END IF;
END$$
DELIMITER ;

SELECT is_stu('081102');
SELECT is_stu('081101');

  2. 删除存储函数

    #语法格式
drop function [if exists] 存储过程名

#例
drop function if exists num_of_xs;

#使用ALTER FUNCTION 语句可以修改存储函数的特征
ALTER FUNCTION 存储过程名 [特征...]

6.3 触发器

  1. 创建触发器

    触发器名称在当前数据库必须是唯一的

    #语法格式
CREATE TRIGGER 触发器名 触发时刻 触发事件
	ON 表名 FOR EACH ROW 触发器动作
#FOR EACH ROW：这个声明用来指定，对于受触发事件影响的每一行，都要激活触发器的动作


/*
创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将用户变量str的值设为“trigger is working
*/
CREATE TABLE table1(a INTEGER);
CREATE TRIGGER table1_insert AFTER INSERT 
	ON table1 FOR EACH ROW
	SET @str='trigger is working';
	
INSERT INTO table1 VALUES(10);

SELECT @str;



/*
创建一个触发器，当删除表xs中某个学生的信息时，同时将xs_kc表中与该学生有关的数据全部删除
*/
DELIMITER $$
CREATE TRIGGER xs_delete AFTER DELETE
	ON xs FOR EACH ROW
BEGIN
	DELETE FROM xs_kc WHERE 学号=old.学号;
END$$
DELIMITER ;

DELETE FROM xs WHERE 学号='081101';
SELECT * FROM xs_kc;


/*
创建一个触发器，当修改xs_kc表中数据时，如果修改后的成绩小于60分，则触发器将该成绩对应的课程学分修改为0，否则将学分改成对应课程的学分
*/

DELIMITER $$
CREATE TRIGGER xs_kc_update BEFORE UPDATE
	ON xs_kc FOR EACH ROW
BEGIN
	DECLARE xf INT(1);
	SELECT 学分 INTO xf FROM kc WHERE 课程号=new.课程号;
	IF new.成绩<60 THEN
		SET new.学分=0;
	ELSE
		SET new.学分=xf;
	END IF;
END$$
DELIMITER ;


/*
创建触发器，实现当向xs_kc表插入一行数据时，根据成绩对xs表的总学分进行修改。如果成绩>=60，总学分加上该课程的学分，否则总学分不变。
*/
DELIMITER $$
CREATE TRIGGER xs_kc_zxf AFTER INSERT
	ON xs_kc FOR EACH ROW
BEGIN
	DECLARE xf INT(1);
	SELECT 学分 INTO xf FROM kc WHERE 课程号=new.课程号;
	IF new.成绩>=60 THEN
		UPDATE xs SET 总学分=总学分+xf WHERE 学号=new.学号;
	END IF;
END$$
DELIMITER ;

  2. 触发器中调用存储过程

    #例子
/*
假设xscj数据库中有一个与xs表结构完全一样的表student，创建一个触发器，在xs表中添加数据的时候，调用存储过程，将student表中的数据与xs表同步
*/

DELIMITER $$
CREATE PROCEDURE changes()
BEGIN
	REPLACE INTO student SELECT * FROM xs;
END$$
DELIMITER ;

  3. 触发器删除

    # 语法格式
drop trigger [schema_name.]trigger_name

#例子
DROP TRIGGER xs_delete;

6.4 事件

6.4.1 创建事件

  1. 语法格式

    CREATE EVENT  事件名    
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO sql语句;
#schedule：
AT 时间点 [+ INTERVAL 时间间隔]
| EVERY 时间间隔 
   [ STARTS 时间点[+ INTERVAL时间间隔]] 
   [ ENDS 时间点[+ INTERVAL时间间隔]]
#INTERVAL:
count {  YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
          WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
          DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND }


#说明
/*
schedule：时间调度，表示事件何时发生或者每隔多久发生一次
AT子句：表示在某个时刻事件发生
EVERY子句：表示在指定时间区间内每隔多长时间事件发生一次
STARTS子句指定开始时间
ENDS子句指定结束时间
*/

#MYSQL事件调度器负责调用事件，TRUE为打开 ，FALSE为关闭
SET GLOBAL EVENT_SCHEDULER = TRUE;

#创建一个立即启动的事件
create event direct
	 on schedule  at now()
	 do insert into xs values('091103', '张建', '软件工程', 1, '1994-06-05', 
			50,null,null);

#创建一个30秒后启动的事件
create event thrityseconds
	 on schedule at now()+interval 30 second
	 do 
		  insert into xs values('091104', '陈建', '软件工程', 1, '1994-08-16', 
								50,null,null);


#创建一个事件，它每个月启动一次，开始于下一个月并且在2014年的12月31日结束
DELIMITER $$
CREATE EVENT startmonth
	ON SCHEDULE  EVERY 1 MONTH
		STARTS CURDATE()+INTERVAL 1 MONTH
	ENDS '2014-12-31'
	DO 
	BEGIN
		IF YEAR(CURDATE())<2014  THEN
			INSERT INTO xs VALUES('091105', '王建', '软件工程', 1, '1994-03-16',48,NULL,NULL);
		END IF;
	END$$
DELIMITER ;

  2. 修改事件

    #语法格式
ALTER EVENT  event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO sql_statement]

#将事件startmonth的名字改成firstmonth
alter event startmonth
		rename to firstmonth;

  3. 删除事件

    #语法格式
DROP EVENT [IF EXISTS][database name.] event name

#例子
drop event direct;
posted @ 2020-04-22 16:16  风里来雨里去duu  阅读(537)  评论(0)  编辑  收藏  举报