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

最新推荐文章于 2025-03-09 12:46:00 发布

Johnny.G

最新推荐文章于 2025-03-09 12:46:00 发布

阅读量1.3k

点赞数 1

文章标签： mysql 数据库 sql linux 服务器

本文链接：https://blog.youkuaiyun.com/JohnnyG2000/article/details/124604152

版权

1、存储过程

1）存储过程简介

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

2）存储过程特性

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

3）存储过程使用说明

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

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

#创建存储过程语法 ：
create procedure sp_name(参数列表)
	[特性...]过程体

#存储过程的参数形式：
[IN | OUT | INOUT]参数名 类型
IN        输入参数
OUT       输出参数
INOUT     输入输出参数

delimiter $$	#设定结束符为$$（避免存储过程中的SQL语句与存储过程冲突【结束符不能一样】）
create procedure 过程名(参数列表)
begin
	SQL语句;	#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表示delimiter
mysql> create procedure p1()
    -> begin
    ->      select count(*) from mysql.user;
    -> end$$
Query OK, 0 rows affected (0.51 sec)

mysql> \d ;		#\d表示delimiter
mysql> 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 |
+------+begin
1 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 ...] 
BEGIN
	routine_body
END;

#函数参数解析：
（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或my.cnf中的[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表结构

字段名	数据类型	主键	外键	非空	唯一	自增
id	INT	是	否	是	是	否
name	VARCHAR(50)	否	否	是	否	否
glass	VARCHAR(50)	否	否	是	否	否

sch表内容

id	name	glass
1	xiaommg	glass 1
2	xiaojun	glass 2

创建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: 0

mysql> 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, '2007-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, '2007-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, '2013-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 | 2007-04-19 | 30000 |  NULL |     20 |
|  1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000 |  NULL |     10 |
|  1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000 |     0 |     30 |
|  1011 | 周泰      | 文员      | 1006 | 2007-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 | 2013-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 do
ouble)
    -> 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)