相关推荐recommended
Mysql 创建存储过程和函数及各种例子
作者:mmseoamin日期:2023-12-18

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 语法结构

                    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;
                        • 其他使用例子如下:

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

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

                        • 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()
                          	begin
                          		select * from dog d where d.dog_age <=1;
                          	end $
                          
                          Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第3张
                        2. 测试看效果
                          • 查看所有的狗狗

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

                          • 调用存储过程查看年龄不超过1岁的狗狗
                            call sp_select_one_age_dogs();
                            
                            Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第5张
                        1.2.1.2 带变量
                        1. 创建如下:
                          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 $;
                          
                          Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第6张
                        2. 测试效果
                        • 调用存储过程: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 $
                            
                            Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第8张
                          • 调用存储过程
                            • 调用方式1:直接给定参数值1001
                              call sp_select_dog_by_num(1001);
                              
                              Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第9张
                            • 调用方式2:通过变量调用
                              set @dogNum = 1002;
                              call sp_select_dog_by_num(@dogNum);
                              
                              注意:赋值也可以用:set @dogNum := 1002;

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

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

                              1.2.3 有出参的存储过程

                              • 直接在上面无参存储过程 sp_test() 的基础上改一个出参的存储过程,如下:
                                1. 创建出参存储过程:
                                  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 $;
                                  
                                  Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第12张
                                2. 测试看效果
                                  • 调用存储过程,注意加:@
                                    call sp_test_out(@col_test);
                                    
                                    Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第13张
                                  • 查看调用结果
                                    select @col_test;
                                    
                                    Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第14张

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

                                1. 创建存储过程
                                  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 $
                                  
                                  Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第15张
                                2. 调用看效果
                                  set @dogNum := 1003;
                                  call sp_select_dogName_by_num(@dogNum,@dogName);
                                  select @dogName;
                                  
                                  Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第16张

                                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 ;
                                    
                                    Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第17张
                                  • 调用测试效果
                                    set @code := 'C001';
                                    call sp_select_pId_by_deptId(@code);
                                    select @code;
                                    
                                    Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第18张

                                    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 创建存储过程和函数及各种例子,在这里插入图片描述,第20张
                                    • 调用存储过程,查看效果
                                      • 测试一张表,首先先看这个表的结构:

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

                                      • 确定没有那两个字段,然后调用存储过程

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

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

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

                                        1.3.2 递归查询的存储过程

                                        1.3.2.1 递归查父id的存储过程
                                        1. 先看想实现的效果

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

                                        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 ;
                                          
                                          Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第25张
                                        3. 测试效果
                                          set @deptId:='D001';
                                          call sp_find_pid_by_deptId(@deptId);
                                          select @deptId;
                                          
                                          Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第26张
                                        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
                                          
                                          Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第27张
                                        • 问题原因:

                                          原因是:存储过程里默认不允许递归,递归深度是0,可以查一下默认的递归深度:

                                          select @@max_sp_recursion_depth;
                                          
                                          Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第28张
                                        • 解决问题:

                                          在存储过程里设置递归深度即可:

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

                                          2. Mysql 创建函数

                                          2.1 创建语法 与删除语法

                                          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 $$
                                            
                                            Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第30张
                                          • 测试看效果
                                            select fun_get_dog_name('1001');
                                            
                                            Mysql 创建存储过程和函数及各种例子,在这里插入图片描述,第31张
                                          • 好了,简单的一个小知识,就到这吧