Mysql 创建存储过程和函数及各种例子_百度小程序

您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >

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

1. Mysql 创建存储过程

1.1 前言知识

1.1.1 语法结构
1.1.2 简单解释
1.2 创建存储过程入门例子

1.2.1 无参存储过程

1.2.1.1 不带变量
1.2.1.2 带变量
1.2.2 有入参的存储过程
1.2.3 有出参的存储过程
1.2.4 有入参和存储的存储过程
1.2.5 inout的存储过程
1.3 实用存储过程例子

1.3.1 根据表名添加字段的存储过程
1.3.2 递归查询的存储过程

1.3.2.1 递归查父id的存储过程
1.3.2.2 注意问题
2. Mysql 创建函数

2.1 创建语法与删除语法
2.2 创建函数例子

2.2.1 入门例子

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 简单解释

部分语法简单介绍：

delimiter $$
$$ 是分隔符，用其他符号也行，比如一个$ 或者//等

定义变量：DECLARE

例子：

DECLARE `de_test` VARCHAR(20) DEFAULT '';

@符号
- 使用 SET 直接赋值变量，变量名以 @ 开头：如:set @dogNum = 1002;
- 其他使用例子如下：
- 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();

Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第7张

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;

Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第10张

Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第11张

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;

Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第19张

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 ;

调用存储过程，查看效果

测试一张表，首先先看这个表的结构：
确定没有那两个字段，然后调用存储过程

再次查看表结构，字段已添加上

Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第23张

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 $$