Mysql 创建存储过程和函数及各种例子

@素素~

14718人浏览 · 2022-12-11 20:01:27

@素素~ · 2022-12-11 20:01:27 发布

1. Mysql 创建存储过程

1.1 前言知识

1.1.1 语法结构

无参的存储过程

delimiter $
CREATE PROCEDURE 存储过程名()
	begin
		存储过程体
	end $;

有参数的存储过程

delimiter $
CREATE PROCEDURE 存储过程名(in|out|inout 参数名1 参数类型，参数名2 参数类型……)
	begin
		存储过程体
	end $;

删除存储过程：

DROP PROCEDURE IF EXISTS `存储过程名`;

1.1.2 简单解释

部分语法简单介绍：
1. delimiter $$
  $$ 是分隔符，用其他符号也行，比如一个$ 或者//等
2. 定义变量：DECLARE
  例子：
```
DECLARE `de_test` VARCHAR(20) DEFAULT '';
```
3. @符号
  - 使用 SET 直接赋值变量，变量名以 @ 开头：如:set @dogNum = 1002;
  - 其他使用例子如下：
4. prepare语法格式
- 处理动态sql，比如表名做变量的sql
```
prepare stmt from 'sql语句; --定义
execute stmt; -- 执行
deallocate prepare stmt;  -- 删除定义（释放资源）
```

1.2 创建存储过程入门例子

1.2.1 无参存储过程

1.2.1.1 不带变量

创建如下：

DROP PROCEDURE IF EXISTS `sp_select_one_age_dogs`;  

delimiter $
CREATE PROCEDURE sp_select_one_age_dogs()
	begin
		select * from dog d where d.dog_age <=1;
	end $

测试看效果
- 查看所有的狗狗
- 调用存储过程查看年龄不超过1岁的狗狗
```
call sp_select_one_age_dogs();
```

1.2.1.2 带变量

创建如下：

DROP PROCEDURE IF EXISTS `sp_test`;  

delimiter $
CREATE PROCEDURE sp_test()
	begin
		DECLARE `col_test` VARCHAR(20) DEFAULT '';
		select 'test' into col_test from dual;
		select col_test;
	end $;

测试效果

调用存储过程：call sp_test();

1.2.2 有入参的存储过程

创建存储过程

DROP PROCEDURE IF EXISTS `sp_select_dog_by_num`;  

delimiter $
CREATE PROCEDURE sp_select_dog_by_num(in dogNum int(10))
	begin
		select d.dog_num ,d.dog_name ,d.dog_kind ,d.dog_age  
	    from dog d where d.dog_num =dogNum;
	end $

调用存储过程
- 调用方式1：直接给定参数值1001
```
call sp_select_dog_by_num(1001);
```
- 调用方式2：通过变量调用
```
set @dogNum = 1002;
call sp_select_dog_by_num(@dogNum);
```
  注意：赋值也可以用：set @dogNum := 1002;

1.2.3 有出参的存储过程

直接在上面无参存储过程 sp_test() 的基础上改一个出参的存储过程，如下：

创建出参存储过程：

DROP PROCEDURE IF EXISTS `sp_test_out`;  

delimiter $
CREATE PROCEDURE sp_test_out(out col_test varchar(20))
	begin
		select 'test' into col_test from dual;
	end $;

测试看效果
- 调用存储过程，注意加：@
```
call sp_test_out(@col_test);
```
- 查看调用结果
```
select @col_test;
```

1.2.4 有入参和存储的存储过程

创建存储过程

delimiter $
CREATE PROCEDURE sp_select_dogName_by_num(in dogNum int(10),out dogName varchar(20))
	begin
		select d.dog_name into dogName from dog d where d.dog_num =dogNum;
	end $

调用看效果

set @dogNum := 1003;
call sp_select_dogName_by_num(@dogNum,@dogName);

select @dogName;

1.2.5 inout的存储过程

根据部门id找父节点（部门id或公司id），如下：

创建存储过程

DROP PROCEDURE IF EXISTS `sp_select_pId_by_deptId`;  

delimiter $
CREATE PROCEDURE sp_select_pId_by_deptId(inout v_code varchar(10))
	begin
		SELECT t.PARENT_ID into v_code FROM SYS_COMPANY_DEPT t 
        where t.DEPT_ID =v_code;
	end $
	
delimiter ;

调用测试效果

set @code := 'C001';
call sp_select_pId_by_deptId(@code);
select @code;

1.3 实用存储过程例子

1.3.1 根据表名添加字段的存储过程

动态给表添加字段 create_time 和 update_time

创建存储过程

drop procedure if exists `add_col_date`;  
delimiter $$
create procedure add_col_date(in tableName varchar(50))  
begin 
	set @tableName = tableName;
	set @createTimeSql = concat(' alter table ',@tableName,' add create_time datetime;'); 
	set @updateTimeSql = concat(' alter table ',@tableName,' add update_time datetime;'); 
	select  @createTimeSql;
	prepare stmt from @createTimeSql; 
	prepare stmt2 from @updateTimeSql;
	execute stmt;
	execute stmt2;
	deallocate prepare stmt; -- 释放数据库连接
	deallocate prepare stmt2; 
end $$
delimiter ;

调用存储过程，查看效果
- 测试一张表，首先先看这个表的结构：
- 确定没有那两个字段，然后调用存储过程
- 再次查看表结构，字段已添加上

1.3.2 递归查询的存储过程

1.3.2.1 递归查父id的存储过程

先看想实现的效果

创建存储过程

drop procedure if exists sp_find_pId_by_deptId;

delimiter $$
create procedure sp_find_pId_by_deptId(inout deptId varchar(10))
begin
    declare count_num int(10);  
    SET @@max_sp_recursion_depth = 10;
	select count(0) into count_num from sys_company_dept t where t.`TYPE` ='1' and t.dept_id =deptId;
    if (count_num = 0) then
        select t.PARENT_ID into deptId from sys_company_dept t where t.dept_id =deptId;
		call sp_find_pId_by_deptId(deptId); 
    end if;
end $$
delimiter ;

测试效果

set @deptId:='D001';
call sp_find_pid_by_deptId(@deptId);
select @deptId;

1.3.2.2 注意问题

遇到的问题：

call sp_find_pid_by_deptId(@deptId)
1456 - Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine sp_find_pid_by_deptId

问题原因：
原因是：存储过程里默认不允许递归，递归深度是0，可以查一下默认的递归深度：
```
select @@max_sp_recursion_depth;
```
解决问题：
在存储过程里设置递归深度即可：
```
SET @@max_sp_recursion_depth = 10;
```

2. Mysql 创建函数

2.1 创建语法与删除语法

创建语法

如下：

delimiter $$
#在函数名后面一定要加上returns 函数返回类型
create function fun_get_dog_name(dogNum VARCHAR(10)) 
returns VARCHAR(30) 
begin
	declare dogName VARCHAR(30);  #在函数中定义一个变量，用来接收函数返回值
	 函数逻辑处理
	return dogName;   # 返回变量                
end $$

删除语法：
```
drop function if exists 函数名;
```

2.2 创建函数例子

2.2.1 入门例子

创建如下：

drop function if exists fun_get_dog_name;

delimiter $$
#在函数名后面一定要加上returns 函数返回类型
create function fun_get_dog_name(dogNum VARCHAR(10)) returns VARCHAR(30) 
begin
	declare dogName VARCHAR(30);  #在函数中定义一个变量，用来接收函数返回值
	select d.dog_name into dogName from dog d where d.dog_num =dogNum;
	return dogName;                   
end $$