MySQL存储过程 & 函数（简介 & 练习）

1、存储过程1）存储过程简介2）存储过程特性3）存储过程使用说明4）存储过程的创建与调用5）查看存储过程6）删除存储过程7）存储过程使用示例①无参数②输入参数（IN）③输出参数（OUT）④输入参数 & 输出参数（IN & OUT）【同时使用】⑤输入输出参数（INOUT） 2、函数1）函数简介2）函数的创建与调用3）函数创建说明4）查看存储函数5）修改存储函数6）删除存储函数7）函数使用示例①无参有返回值②有参有返回值 3、存储过程 & 函数区别3、素材4、练习要求1）创建一个函数，函数名为count_sch()2）创建一个存储过程，存储过程名为avg_sal()

1、存储过程

1）存储过程简介

MySQL存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，通常用来进行一些相对复杂、繁琐的操作。

2）存储过程特性

• 可以提高数据库执行速度（存储过程只在创建时进行编译，SQL语句每执行一次就编译一次）；• 简化复杂操作，可以结合事务一起封装；• 复用性好；• 安全性高（设定权限）；

3）存储过程使用说明

• 并发量少的情况下，很少使用存储过程；• 并发量高的情况下，为了提高效率，用存储过程比较多；

4）存储过程的创建与调用

#创建存储过程语法 ：create procedure sp_name(参数列表)[特性...]过程体#存储过程的参数形式：[IN | OUT | INOUT]参数名 类型IN 输入参数OUT 输出参数INOUT输入输出参数delimiter $$#设定结束符为$$（避免存储过程中的SQL语句与存储过程冲突【结束符不能一样】）create procedure 过程名(参数列表)beginSQL语句;#SQL语句结束符为“;”（上方改动实则避免此处与下方"end"冲突）end $$#此处使用上方所指定的结束符“$$”（避免与上方SQL语句结束符“;”冲突）delimiter ;#设定结束符为;（更改回去，否则后面结束符都需要使用“$$”）#调用：call 存储过程名(实参列表);#此处结束符继续使用“;”

5）查看存储过程

#方法一：可以通过 SHOW PROCEDURE STATUS 或者 SHOW CREATE PROCEDURE 查看存储过程的状态信息#方法二：可以通过information_schema库中进行查询(1)使用SHOW PROCEDURE STATUS语句查看存储过程的状态；SHOW PROCEDURE STATUS [LIKE 'pattern'](2)使用SHOW CREATE PROCEDURE查看存储过程的信息；SHOW CREATE PROCEDURE sp_name(3)通过INFORMATION_SCHEMA.ROUTINES查看存储过程的信息；

6）删除存储过程

DROP PROCEDURE IF EXISTS p_name;

7）存储过程使用示例

①无参数

mysql> \d $$#\d表示delimitermysql> create procedure p1()-> begin->select count(*) from mysql.user;-> end$$Query OK, 0 rows affected (0.51 sec)mysql> \d ;#\d表示delimitermysql> call p1()mysql> create school.table t1(-> id int,-> name varchar(50)-> ); Query OK, 0 rows affected (2.81 sec)mysql> delimiter $$mysql> create procedure autoinsert1()-> BEGIN-> declare i int default 1;#定义变量i（整型，初始值1）-> while(i<=20000)do->insert into school.t1 values(i,md5(i));->set i=i+1;-> end while;-> END$$mysql> delimiter ;

②输入参数（IN）

mysql> create procedure autoinsert2(IN a int)-> BEGIN-> declare i int default 1;-> while(i<=a)do-> insert into school.t1 values(i,md5(i));-> set i=i+1;-> end while;-> END$$Query OK, 0 rows affected (0.00 sec)mysql> call autoinsert1(10);Query OK, 1 row affected (1.10 sec)mysql> set @num=20;mysql> select @num;+------+| @num |+------+| 20 |+------+1 row in set (0.00 sec)mysql> call autoinsert1(@num);

③输出参数（OUT）

mysql> delimiter $$mysql> CREATE PROCEDURE p2 (OUT param1 INT)-> BEGIN-> SELECT COUNT(*) INTO param1 FROM t1;-> END$$Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> select @a;+------+| @a |+------+| NULL |+------+1 row in set (0.00 sec)mysql> CALL p2(@a);#变量a接收了p2的输出结果（count（*）结果）Query OK, 0 rows affected (0.00 sec)mysql> SELECT @a;+------+| @a |+------+| 3 |+------+

④输入参数 & 输出参数（IN & OUT）【同时使用】

作用：统计指定部门的员工数mysql> create procedure count_num(IN p1 varchar(50), OUT p2 int)-> BEGIN-> select count(*) into p2 from employee-> where post=p1;-> END$$Query OK, 0 rows affected (0.00 sec)mysql> \d ;mysql> call count_num('hr',@a);mysql>select @a; 作用：统计指定部门工资超过例如5000的总人数mysql> create procedure count_num(IN p1 varchar(50), IN p2 float(10,2), OUT p3 int)-> BEGIN-> select count(*) into p3 from employee-> where post=p1 and salary>=p2;-> END$$Query OK, 0 rows affected (0.00 sec)mysql> \d ;mysql> call count_num('hr',5000,@a);

⑤输入输出参数（INOUT）

mysql> create procedure proce_param_inout(inout p1 int) -> begin-> if (p1 is not null) then-> set p1=p1+1; ->else-> select 100 into p1;->end if;-> end$$Query OK, 0 rows affected (0.00 sec)mysql> select @h;+------+| @h |+------+| NULL |+------+1 row in set (0.00 sec)mysql> call proce_param_inout(@h);Query OK, 1 row affected (0.00 sec)mysql> select @h;+------+| @h |+------+| 100 |+------+begin1 row in set (0.00 sec)mysql> call proce_param_inout(@h);Query OK, 0 rows affected (0.00 sec)mysql> select @h;+------+| @h |+------+| 101 |+------+1 row in set (0.00 sec)

2、函数

1）函数简介

MySQL函数（自定义函数），MySQL存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，函数一般用于计算和返回一个值，通常用来进行一些计算。

2）函数的创建与调用

#创建函数语法 ：CREATE FUNCTION func_name ([param_name type[,...]])RETURNS type[characteristic ...] BEGINroutine_bodyEND;#函数参数解析：（1）func_name ：存储函数的名称；（2）param_name type：指定存储函数的参数（可选项），type参数用于指定存储函数参数类型（MySQL数据库中所有支持类型）；（3）RETURNS type：指定返回值类型；（4）characteristic：指定存储函数的特性（可选项）；（5）routine_body：SQL代码内容；#调用：SELECT func_name([parameter[,…]]);

3）函数创建说明

MySQL开启bin-log后，调用存储过程或者函数以及触发器时，会出现错误号为1418的错误

错误示例：ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法

方法1：第一种是在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个， 例如: CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`()DETERMINISTIC BEGIN #Routine body goes here... END;;方法2：第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。具体操作：（1）在客户端上执行 SET GLOBAL log_bin_trust_function_creators = 1。（2）MySQL启动时，加上--log-bin-trust-function-creators选项，参数设置为1。（3）在MySQL配置文件my.ini或f中的[mysqld]段上加log-bin-trust-function-creators=1。

4）查看存储函数

#方法一：可以使用SHOW STATUS语句或SHOW CREATE语句来查看#方法二：可以使直接从系统的information_schema数据库中查询。SHOW FUNCTION STATUS [LIKE 'pattern']#查看存储过程和函数的状态#此语句是MySQL扩展，返回子程序的特征：如数据库，名字，类型，创建者及创建和修改日期。

5）修改存储函数

ALTER FUNCTION func_name [characteristic ...]characteristic:COMMENT 'string'| LANGUAGE SQL| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }#修改的内容是否包含SQL语句可以根据情况而定#MySQL官方给出的语法结构

6）删除存储函数

DROP FUNCTION IF EXISTS func_name;

7）函数使用示例

①无参有返回值

# 统计emp表中员工个数mysql> \d $mysql> CREATE FUNCTION myf1()-> RETURNS int-> BEGIN-> DECLARE c INT DEFAULT 0;-> SELECT COUNT(1) INTO c FROM emp;-> RETURN c;-> END $Query OK, 0 rows affected (0.00 sec)mysql> \d;mysql> select myf1();+--------+| myf1() |+--------+|15 |+--------+1 row in set (0.05 sec)

②有参有返回值

=============示例1：根据员工名返回工资=============mysql> \d $mysql> CREATE FUNCTION myf2(empName varchar(20))-> RETURNS INT-> BEGIN-> DECLARE sal INT;-> SELECT sai INTO sal FROM emp-> WHERE ename=empName;-> RETURN sal;-> END $Query OK, 0 rows affected (0.00 sec)mysql> \d;mysql> select myf2('刘备');+----------------+| myf2('刘备') |+----------------+|29750 |+----------------+1 row in set (0.00 sec)=========示例2：根据部门编号，返回平均工资=========mysql> \d $mysql> CREATE FUNCTION myf3(d_No int)-> RETURNS DOUBLE-> BEGIN-> DECLARE avg_sal DOUBLE;-> SELECT AVG(sai) INTO avg_sal FROM emp-> WHERE deptno=d_No;-> RETURN avg_sal;-> END $Query OK, 0 rows affected (0.00 sec)mysql> \d ;mysql> select myf3(20);+----------+| myf3(20) |+----------+| 21750 |+----------+1 row in set (0.00 sec)

3、存储过程 & 函数区别

存储过程和函数的区别：• 函数必须有返回值，而存储过程没有；• 存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN；

3、素材

sch表结构

sch表内容

创建sch表

mysql> create table sch-> (-> id int primary key unique,-> name varchar(50) not null,-> glass varchar(50) not null-> );Query OK, 0 rows affected (0.01 sec)mysql> desc sch;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11)| NO | PRI | NULL | || name | varchar(50) | NO || NULL | || glass | varchar(50) | NO || NULL | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.03 sec)

插入内容（sch表）

mysql> insert into sch values(1,"xiaoming","glass 1"),(2,"xiaojun","glass 2");Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from sch;+----+----------+---------+| id | name| glass |+----+----------+---------+| 1 | xiaoming | glass 1 || 2 | xiaojun | glass 2 |+----+----------+---------+2 rows in set (0.00 sec)

创建emp表

CREATE TABLE `emp` (`empno` int(4) NOT NULL,`ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`mgr` int(4) NULL DEFAULT NULL,`hiredate` date NOT NULL,`sal` int(255) NOT NULL,`comm` int(255) NULL DEFAULT NULL,`deptno` int(2) NOT NULL,PRIMARY KEY (`empno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

emp表数据

INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '-04-19', 30000, NULL, 20);INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1006, '-05-23', 11000, NULL, 20);INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);INSERT INTO `emp` VALUES (1015, '张三', '保洁员', 1001, '-05-01', 80000, 50000, 50);mysql> select * from emp;+-------+-----------+-----------+------+------------+-------+-------+--------+| empno | ename| job | mgr | hiredate | sal | comm | deptno |+-------+-----------+-----------+------+------------+-------+-------+--------+| 1001 | 甘宁| 文员| 1013 | 2000-12-17 | 8000 | NULL |20 || 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 |30 || 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 |30 || 1004 | 刘备| 经理| 1009 | 2001-04-02 | 29750 | NULL |20 || 1005 | 谢逊| 销售员 | 1006 | 2001-09-28 | 12500 | 14000 |30 || 1006 | 关羽| 经理| 1009 | 2001-05-01 | 28500 | NULL |30 || 1007 | 张飞| 经理| 1009 | 2001-09-01 | 24500 | NULL |10 || 1008 | 诸葛亮 | 分析师 | 1004 | -04-19 | 30000 | NULL |20 || 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL |10 || 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 |0 |30 || 1011 | 周泰| 文员| 1006 | -05-23 | 11000 | NULL |20 || 1012 | 程普| 文员| 1006 | 2001-12-03 | 9500 | NULL |30 || 1013 | 庞统| 分析师 | 1004 | 2001-12-03 | 30000 | NULL |20 || 1014 | 黄盖| 文员| 1007 | 2002-01-23 | 13000 | NULL |10 || 1015 | 张三| 保洁员 | 1001 | -05-01 | 80000 | 50000 |50 |+-------+-----------+-----------+------+------------+-------+-------+--------+15 rows in set (0.00 sec)

4、练习要求

1）创建一个函数，函数名为count_sch()

具体要求：可以统计表格内记录条数

mysql> SET GLOBAL log_bin_trust_function_creators = 1;#避免开启bin-log后触发的1418错误Query OK, 0 rows affected (0.00 sec)mysql> \d $$mysql> CREATE FUNCTION count_sch()-> RETURNS int-> BEGIN-> DECLARE c INT DEFAULT 0;-> SELECT COUNT(*) INTO c FROM sch;-> RETURN c;-> END $$Query OK, 0 rows affected (0.00 sec)mysql> \d ;mysql> select count_sch();+-------------+| count_sch() |+-------------+| 2 |+-------------+1 row in set (0.00 sec)

2）创建一个存储过程，存储过程名为avg_sal()

具体要求：有3个参数，分别是dept，position，接收平均工资，功能查询emp表deptno为30，job为销售员的平均工资。（使用之前的emp表）

mysql> create procedure avg_sal(in dept int,in position varchar(20),out avg_sal doouble)-> begin-> select avg(sal) into avg_sal from emp-> where deptno=dept and job=position;-> end $$Query OK, 0 rows affected (0.01 sec)mysql> \d ;mysql> call avg_sal(30,"销售员",@a);Query OK, 1 row affected (0.00 sec)mysql> select @a;+-------+| @a |+-------+| 14000 |+-------+1 row in set (0.00 sec)