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()beginselect * 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()beginDECLARE `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))beginselect 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))beginselect '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))beginselect 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))beginSELECT 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))begindeclare 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) thenselect 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) begindeclare 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) begindeclare dogName VARCHAR(30); #在函数中定义一个变量，用来接收函数返回值select d.dog_name into dogName from dog d where d.dog_num =dogNum;return dogName; end $$
测试看效果
select fun_get_dog_name('1001');
好了，简单的一个小知识，就到这吧
