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

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

1. Mysql 创建存储过程

1.1 前言知识

1.1.1 语法结构

1. 无参的存储过程delimiter $CREATEPROCEDURE 存储过程名()begin 存储过程体 end $;
2. 有参数的存储过程delimiter $CREATEPROCEDURE 存储过程名(in|out|inout 参数名1 参数类型，参数名2 参数类型……)begin 存储过程体 end $;
3. 删除存储过程：DROPPROCEDUREIFEXISTS`存储过程名`;

1.1.2 简单解释

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

1.2 创建存储过程入门例子

1.2.1 无参存储过程

1.2.1.1 不带变量

1. 创建如下：DROPPROCEDUREIFEXISTS`sp_select_one_age_dogs`;delimiter $CREATEPROCEDURE sp_select_one_age_dogs()beginselect*from dog d where d.dog_age <=1;end $
2. 测试看效果 - 查看所有的狗狗- 调用存储过程查看年龄不超过1岁的狗狗call sp_select_one_age_dogs();

1.2.1.2 带变量

1. 创建如下：DROPPROCEDUREIFEXISTS`sp_test`;delimiter $CREATEPROCEDURE sp_test()beginDECLARE`col_test`VARCHAR(20)DEFAULT'';select'test'into col_test from dual;select col_test;end $;
2. 测试效果

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

1.2.2 有入参的存储过程

• 创建存储过程DROPPROCEDUREIFEXISTS`sp_select_dog_by_num`;delimiter $CREATEPROCEDURE 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：直接给定参数值1001call 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() 的基础上改一个出参的存储过程，如下： 1. 创建出参存储过程：DROPPROCEDUREIFEXISTS`sp_test_out`;delimiter $CREATEPROCEDURE sp_test_out(out col_test varchar(20))beginselect'test'into col_test from dual;end $;2. 测试看效果 - 调用存储过程，注意加：@``````call sp_test_out(@col_test);- 查看调用结果select@col_test;

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

1. 创建存储过程delimiter $CREATEPROCEDURE 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 $
2. 调用看效果set@dogNum :=1003;call sp_select_dogName_by_num(@dogNum,@dogName);select@dogName;

1.2.5 inout的存储过程

• 根据部门id找父节点（部门id或公司id），如下： - 创建存储过程DROPPROCEDUREIFEXISTS`sp_select_pId_by_deptId`;delimiter $CREATEPROCEDURE 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
• 创建存储过程dropprocedureifexists`add_col_date`;delimiter $$createprocedure add_col_date(in tableName varchar(50))beginset@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;deallocateprepare stmt;-- 释放数据库连接deallocateprepare stmt2;end $$delimiter;
• 调用存储过程，查看效果 - 测试一张表，首先先看这个表的结构：- 确定没有那两个字段，然后调用存储过程- 再次查看表结构，字段已添加上

1.3.2 递归查询的存储过程

1.3.2.1 递归查父id的存储过程

1. 先看想实现的效果
2. 创建存储过程dropprocedureifexists sp_find_pId_by_deptId;delimiter $$createprocedure sp_find_pId_by_deptId(inout deptId varchar(10))begindeclare count_num int(10);SET @@max_sp_recursion_depth=10;selectcount(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);endif;end $$delimiter;
3. 测试效果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 limit0(assetby the max_sp_recursion_depth variable) was exceeded forroutine sp_find_pid_by_deptId
• 问题原因： 原因是：存储过程里默认不允许递归，递归深度是0，可以查一下默认的递归深度：select @@max_sp_recursion_depth;
• 解决问题： 在存储过程里设置递归深度即可：SET @@max_sp_recursion_depth=10;

2. Mysql 创建函数

2.1 创建语法 与删除语法

1. 创建语法 - 如下：delimiter $$#在函数名后面一定要加上returns 函数返回类型createfunction fun_get_dog_name(dogNum VARCHAR(10))returnsVARCHAR(30)begindeclare dogName VARCHAR(30);#在函数中定义一个变量，用来接收函数返回值 函数逻辑处理 return dogName;# 返回变量 end $$
2. 删除语法：dropfunctionifexists 函数名;

2.2 创建函数例子

2.2.1 入门例子

• 创建如下：dropfunctionifexists fun_get_dog_name;delimiter $$#在函数名后面一定要加上returns 函数返回类型createfunction fun_get_dog_name(dogNum VARCHAR(10))returnsVARCHAR(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');
• 好了，简单的一个小知识，就到这吧