存储过程,函数的介绍
MySQL从5.0版本开始支持存储过程和函数。
存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合,然后直接通知调用执行即可,所以调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值,而存储过程没有, 存储过程的参数可以使用IN,OUT,INOUT类型,而函数的参数只能是IN类型的。
创建,删除,修改存储过程或者函数都需要权限,例如创建存储过程或者函数需要CREATEROUNTINE
权限,修改或者删除存储过程或者函数需要ALTERROUTINE
权限,执行存储过程或者函数需要EXECUTE
权限。
小结
- 存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合
- 减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的
- 存储过程的操作要确定是否有权限
一个简单的存储过程
测试数据
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR ( 20 ),
age INT,
sex VARCHAR ( 5 ),
address VARCHAR ( 100 ),
math INT,
english INT
);
INSERT INTO student ( NAME, age, sex, address, math, english )
VALUES
( '马云', 55, '男', '杭州', 66, 78 ),
( '马化腾', 45, '女', '深圳', 98, 87 ),
( '马景涛', 55, '男', '香港', 56, 77 ),
( '柳岩', 20, '女', '湖南', 76, 65 ),
( '柳 青', 20, '男', '湖南', 86, NULL ),
( '刘德华', 57, '男', '香港', 99, 99 ),
( '马德', 22, '女', '香港', 99, 99 ),
( '德玛西 亚', 18, '男', '南京', 56, 65 );
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR ( 20 ),
address VARCHAR ( 30 ) DEFAULT '广州'
);
-- 添加一条记录,不使用默认地址
INSERT INTO users ( NAME, address ) VALUES ( '李四', '广州' );
INSERT INTO users ( NAME, address ) VALUES ( '王五', '广州' );
一个简单的存储过程
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE testa ( ) BEGIN
SELECT * FROM student WHERE id = 2;
END $$
-- 调用存储过程
CALL testa ( );
小结
存储过程:
- 创建格式:create procedure 存储过程名
- 包含一个以上代码块,代码块使用begin和end 之间
- 在命令行中创建需要定义分隔符 delimiter $$
- 存储过程调用使用call命令
存储过程的特点:
- 能完成较复杂的判断和运算,而且处理逻辑都封装在数据库端,调用者不需要自己处理业务逻辑,一旦逻辑发
生变化,只需要修改存储过程即可,而对调用者程序完全没有影响。 - 可编程性强,灵活
- SQL编程的代码可重复使用
- 执行速度相对快一些
- 减少网络之间数据传输,节省开销
存储过程的删除,查看操作
删除存储过程/函数
-- 删除存储过程
DROP PROCEDURE testa1;
-- 删除函数
DROP FUNCTION testa1;
查看存储过程或者函数
-- 查看存储过程或者函数的状态
SHOW PROCEDURE STATUS LIKE 'testa';
-- 查看存储过程或者函数的定义
SHOW CREATE PROCEDURE testa;
存储过程的变量
需求1: 编写存储过程,使用变量取id=2的用户名.
DELIMITER $$
CREATE PROCEDURE testa3 ( )
BEGIN
DECLARE my_uname VARCHAR ( 32 ) DEFAULT '';
SET my_uname = 'javakf';
SELECT NAME INTO my_uname FROM student WHERE id = 2;
SELECT my_uname;
END $$
CALL testa3 ( );
小结
- 变量的声明使用declare,一句declare只声明一个变量,变量必须先声明后使用
- 变量具有数据类型和长度,与mysql的SQL数据类型保持一致,因此甚至还能指定默认值、字符集和排序规则 等
- 变量可以通过set来赋值,也可以通过select into的方式赋值
- 变量需要返回,可以使用select语句,如:select 变量名
需求2:统计表users,student的行数量和student表中英语最高分,数学最高分的注册时间。
DELIMITER $$
CREATE PROCEDURE stats_users_students5 ( )
BEGIN
BEGIN
DECLARE users_sum INT DEFAULT 0;
DECLARE students_sum INT DEFAULT 0;
SELECT COUNT( * ) INTO users_sum FROM users;
SELECT COUNT( * ) INTO students_sum FROM student;
SELECTusers_sum,students_sum;
END;
BEGIN
DECLARE max_math INT;
DECLARE max_english INT;
SELECT MAX( math ),MAX( english ) INTO max_math,max_english FROM student;
SELECT users_sum,students_sum,max_math,max_english;
END;
END;
$$
CALL stats_users_students5 ( );
小结
- 变量是有作用域的,作用范围在begin与end块之间,end结束变量的作用范围即结束。
- 需要多个块之间传递值,可以使用全局变量,即放在所有代码块之前。
- 传参变量是全局的,可以在多个块之间起作用
存储过程的传入参数IN
需求:编写存储过程,传入id,返回该用户的name
-- 需求:编写存储过程,传入id,返回该用户的name
DELIMITER $$
CREATE PROCEDURE getName ( my_uid INT )
BEGIN
DECLARE my_uname VARCHAR ( 32 ) DEFAULT '';
SELECT NAME INTO my_uname FROM student WHERE id = my_uid;
SELECT my_uname;
END;
$$
CALL getName ( 2 );
小结
- 传入参数:类型为IN,表示该参数的值必须在调用存储过程时指定,如果不显式指定为IN,那么默认就是IN类型。
- IN类型参数一般只用于传入,在调用存储过程中一般不作修改和返回
- 如果调用存储过程中需要修改和返回值,可以使用OUT类型参数
存储过程的传出参数OUT
需求:调用存储过程时,传入uid返回该用户的uname
-- 需求:调用存储过程时,传入uid返回该用户的uname
DELIMITER $$
CREATE PROCEDURE getName22 ( IN my_uid INT, OUT my_uname VARCHAR ( 32 ) )
BEGIN
SELECT NAME INTO my_uname FROM student WHERE id = my_uid;
SELECT my_uname;
END;
$$
SET @uname := '';
CALL getName22 ( 2, @uname );
SELECT @uname AS myName;
小结
- 传出参数:在调用存储过程中,可以改变其值,并可返回
- OUT是传出参数,不能用于传入参数值
- 调用存储过程时,OUT参数也需要指定,但必须是变量,不能是常量
- 如果既需要传入,同时又需要传出,则可以使用INOUT类型参数
存储过程的可变参数INOUT
需求:调用存储过程时,参数my_uid和my_uname,既是传入,也是传出参数
-- 需求:调用存储过程时,参数my_uid和my_uname,既是传入,也是传出参数
DELIMITER $$
CREATE PROCEDURE getName33 ( INOUT my_uid INT, INOUT my_uname VARCHAR ( 32 ) )
BEGIN
SET my_uid = 2;
SET my_uname = 'hxf3';
SELECT id,NAME INTO my_uid,my_uname FROM student WHERE id = my_uid;
SELECT my_uid,my_uname;
END;
$$
SET @uname := '';
SET @uid := 0;
CALL getName33 ( @uid, @uname );
SELECT @uname AS myName;
小结
- 可变变量INOUT:调用时可传入值,在调用过程中,可修改其值,同时也可返回值。
- INOUT参数集合了IN和OUT类型的参数功能
- INOUT调用时传入的是变量,而不是常量
存储过程条件语句
存储过程的条件语句
需求:编写存储过程,如果用户uid是偶数则就给出uname,其它情况只返回uid
-- 需求:编写存储过程,如果用户uid是偶数则就给出uname,其它情况只返回uid
DELIMITER $$
CREATE PROCEDURE getName44 ( IN my_uid INT )
BEGIN
DECLARE my_uname VARCHAR ( 32 ) DEFAULT '';
IF( my_uid % 2 = 0 )
THEN
SELECT NAME INTO my_uname FROM student WHERE id = my_uid;
SELECT my_uname;
ELSE
SELECT my_uid;
END IF;
END;
$$
CALL getName44 ( 1 );
CALL getName44 ( 2 );
-- 1.条件语句最基本的结构:if() then …else …end if;
-- 2.If判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式
存储过程的条件语句应用示例
需求:根据用户传入的uid参数判断:
(1)如果状态status为1,则给用户score加10分
(2)如果状态status 为2,则给用户score加20分
(3)其它情况加30分
DELIMITER $$
CREATE PROCEDURE addscore1 ( IN my_uid INT )
BEGIN
DECLARE my_status INT DEFAULT 0;
SELECT STATUS INTO my_status FROM student WHERE id = my_uid;
IF( my_status = 1 )
THEN
UPDATE student SET math = math + 10,english = english + 10 WHERE id = my_uid;
ELSEIF ( my_status = 2 )
THEN
UPDATE student SET math = math + 20,english = english + 20 WHERE id = my_uid;
ELSE
UPDATE student SET math = math + 30,english = english + 30 WHERE id = my_uid;
END IF;
END;
$$
CALL addscore1 ( 1 );
存储过程循环语句
while循环
需求:使用循环语句,向表uesrs中插入10条uid连续的记录。
-- 需求:使用循环语句,向表student(uid)中插入10条uid连续的记录。
DELIMITER $$
CREATE PROCEDURE insertdata ( )
BEGIN
DECLARE i INT DEFAULT 0;
WHILE( i < 10 ) DO
BEGIN
SELECT i;
SET i = i + 1;
INSERT INTO users ( NAME, address )VALUES( "孙悟空", "广州" );
END;
END WHILE;
END;
$$
CALL insertdata ( );
-- 1.while语句最基本的结构:while() do…end while;
-- 2.while判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式
repeat循环语句
需求:使用repeat循环向表users插入10条uid连续的记录
DELIMITER $$
CREATE PROCEDURE insertdata2 ( )
BEGIN
DECLARE i INT DEFAULT 100;
REPEAT
BEGIN
SELECT i;
SET i = i + 1;
INSERT INTO users ( NAME )VALUES( '黑马' );
END;
UNTIL i >= 110
END REPEAT;
END;
$$
CALL insertdata3 ( );
-- 1.repeat语句最基本的结构:repeat…until …end REPEAT;
-- 2.until判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式 只有当until语句为真时,循环结束。
光标的使用
在存储过程和函数中,可以使用光标(有时也称为游标)对结果集进行循环的处理,光标的使用包括了:
- 光标的申明
- OPEN
- FETCH
- CLOSE
需求:编写存储过程,使用光标,把id为偶数的记录逐一更新用户名。
-- 编写存储过程,使用光标,把id为偶数的记录逐一更新用户名。
DELIMITER $$
CREATE PROCEDURE testcursor ( )
BEGIN
DECLARE stopflag INT DEFAULT 0;
DECLARE my_uname VARCHAR ( 20 );
DECLARE uname_cur CURSOR FOR SELECT NAME FROM student WHERE id % 2 = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag = 1;
OPEN uname_cur;-- 打开游标
FETCH uname_cur INTO my_uname;-- 游标向前走一步,取出一条记录放到变量my_uname中。
WHILE
( stopflag = 0 ) DO -- 如果游标还没有到结尾,就继续
BEGIN
UPDATE student SET NAME = CONCAT( my_uname, '_cur' ) WHERE NAME = my_uname;
FETCH uname_cur INTO my_uname;
END;
END WHILE;
CLOSE uname_cur;
END;
$$
DELIMITER;
-- 注意 :变量,条件,处理程序,光标,都是通过 DECLARE定义的,它们之间是有先后顺序要求的,变量和条件必须在最 前面声明,然后才能是光标的申明,最后才可以是处理程序的申明。
简单的自定义函数
需求:编写函数,传入一个用户uid,返回用户的uname
-- 需求:编写函数,传入一个用户uid,返回用户的uname
DELIMITER $$
CREATE FUNCTION getFName1 ( my_uid INT ) RETURNS VARCHAR ( 32 ) READS SQL DATA # READS SQL DATA表示子程序包含读数据的语句,但不包含写数据的语句。
BEGIN
DECLARE my_uname VARCHAR ( 32 ) DEFAULT '';
SELECT NAME INTO my_uname FROM student WHERE id = my_uid;
RETURN my_uname;
END;
$$
SELECT getFName1 ( 3 );
-- 1.创建函数使用 CREATE FUNCTION 函数名(参数 ) RETURNS 返回类型
-- 2.函数体放在 BEGIN和END之间
-- 3.RETURN指定函数的返回值
-- 4.函数调用 : SELECT getuname ( )
自定义函数综合应用
需求:输入用户ID,获得address, id, name组合的UUID值,在游戏中作为用户的唯一标识
-- 需求:输入用户ID,获得address, id, name组合的UUID值,
-- 在全区游戏中作为用户的唯一标识
DELIMITER $$
CREATE FUNCTION getuuid2 ( my_uid INT ) RETURNS VARCHAR ( 30 ) CHARSET utf8 READS SQL DATA # READS SQL DATA表示子程序包含读数据的语句,但不包含写数据的语句。
BEGIN
DECLARE UUID VARCHAR ( 30 ) DEFAULT '';
SELECT CONCAT( address, id, NAME ) INTO UUID FROM student WHERE id = my_uid;
RETURN UUID;
END;
$$
SELECT getuuid2 ( 2 );
需求:输入参数id,查询全部学生中数学分数高于id学生数学成绩的总和
-- 需求:输入参数id,查询出数学成绩高于该参数的学生的数学成绩总和
DELIMITER $$
CREATE FUNCTION mathAll ( my_uid INT ) RETURNS INT READS SQL DATA
BEGIN
DECLARE math_id INT DEFAULT 0;
DECLARE math_all INT DEFAULT 0;
SELECT math INTO math_id FROM student WHERE id = my_uid;
SELECT SUM( math ) INTO math_all FROM student WHERE math > math_id;
RETURN math_all;
END;
$$
SELECT mathAll ( 2 );
MySQL触发器
什么是触发器
Mysql从5.0.2开始支持触发器功能,触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义 的语句集合、触发器这种特性可以协助应用在数据库端确定数据的完整性。接下来以需求的方式演示。
需求:出于审计目的,当有人往表users插入一条记录时,把插入的uid,uname,插入动作和操作时间记录下来。
-- 出于审计目的,当有人往表users插入一条记录时,把插入的uid,uname,插入动作和操作时间记录下来。
DELIMITER $$
CREATE TRIGGER tr_users_insert AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO oplog ( uid, uname, ACTION, optime )VALUES( NEW.uid, NEW.uname, 'insert', NOW( ) );
END;
$$
-- 1.创建触发器使用create TRIGGER 触发器名
-- 2.什么时候触发?After INSERT ON users,除了after还有before, 是在对表操作之前(BEFORE)或者之后(AFTER)触发动作的。
-- 3.对什么操作事件触发?after INSERT ON users ,操作事件包括insert,UPDATE,DELETE
-- 4.对什么表触发?after INSERT ON users
-- 5.影响的范围?For EACH ROW
需求:出于 审计目的,当删除users表时,记录删除前该记录的主要字段值
Delimiter $$
CREATE TRIGGER tr_users_delete BEFORE DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO oplog ( uid, uname, action, optime, old_value, new_value )VALUES( OLD.uid, OLD.uname, 'delete', now( ), OLD.regtime,OLD.regtime );
END;
$$
事件调度器(EVENT-SCHEDULE)
事件调度器是MySQL中提供的可做定时操作处理,或者周期操作处理的一个对象。
事件调度器的使用如下:
先确认是否开启了事件调度的支持,事件调度器开启后就可以定义时间调度器使用了
show variables like '%event_scheduler%';
set global event_scheduler =on;
事件调度器的入门案例
DELIMITER $$
CREATE EVENT IF NOT EXISTS event_hello
ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO
BEGIN
INSERT INTO users ( NAME, address )VALUES( '王五', '广州' );
END $$
DELIMITER;
-- CREATE EVENT IF NOT EXISTS event_hello 创建使用 CREATE EVENT
-- ON SCHEDULE EVERY 3 MINUTE 说明什么时候执行,多长时间执行一次
-- ON COMPLETION PRESERVE 调度计划执行完成后是否还保留
-- DO SQL; 这个调度计划要做什么?
事件调度器计划示例
单次计划任务示例:
On schedule at ‘2016-12-12 04:00:00’ 在 2016-12-12 04:00:00执行一次
重复计划任务
on schedule every 1 scond 每秒执行一次
on schedule every 1 minuter 每分钟执行一次
on schedule every 1 day 每天执行一次
指定时间范围的重复计划任务
on schedule every 1 day starts ‘2016-12-12 20:20:20’ 每天在20:20:20执行一次
on schedule every 1 minute starts ‘2016-12-12 9:00:00’ ends ‘2016-12-12 11:00:00’
综合案例
设计一个福彩3D的开奖存储过程,每3分钟开奖一次。
- 第一步:创建一张奖号表,存储三个中奖号码,以及生成时间。
- 第二步:定义一个存储过程,随机生成3个中奖号码存入到奖号表中。
- 第三步:做一个事件调度器,每隔3s调用一次存储过程。
-- 创建奖号的存储操作
CREATE TABLE lucky_num (
id INT PRIMARY KEY AUTO_INCREMENT,
num1 INT,
num2 INT,
num3 INT,
ctime DATETIME
)
-- 创建一个存储过程,负责生成3个随机号码给表存储
DELIMITER $$
CREATE PROCEDURE create_lucky_num ( )
BEGIN
INSERT INTO lucky_num ( num1, num2, num3, ctime )
SELECT FLOOR( RAND( ) * 9 ) + 1,FLOOR( RAND( ) * 9 ) + 1,FLOOR( RAND( ) * 9 ) + 1,NOW( );
END $$
-- 开启事件调度器
DELIMITER $$
CREATE EVENT IF NOT EXISTS create_lucky_num
ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE DO
BEGIN
CALL create_lucky_num;
END $$
DELIMITER;