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

1.1 前言知识

1.1.1 语法结构

1. 无参的存储过程

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

2. 有参数的存储过程

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

3. 删除存储过程：

   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 不带变量

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 $

   
2. 测试看效果
   • 查看所有的狗狗
     
   • 调用存储过程查看年龄不超过1岁的狗狗

     call sp_select_one_age_dogs();

     

1.2.1.2 带变量

1. 创建如下：

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

   
2. 测试效果

• 调用存储过程：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() 的基础上改一个出参的存储过程，如下：
  1. 创建出参存储过程：

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

     
  2. 测试看效果
     • 调用存储过程，注意加：@

       call sp_test_out(@col_test);

       
     • 查看调用结果

       select @col_test;

       

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

1. 创建存储过程

   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 $

   
2. 调用看效果

   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的存储过程

1. 先看想实现的效果
   
2. 创建存储过程

   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 ;

   
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 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.1 创建语法 与删除语法

1. 创建语法
   • 如下：

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

2. 删除语法：

   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');

  
• 好了，简单的一个小知识，就到这吧

来源地址：https://blog.csdn.net/suixinfeixiangfei/article/details/128271724