1. 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))
   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 ;
   

   
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. Mysql 创建函数

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

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