MySQL--存储过程与存储函数的创建与调用示例

存储过程

存储过程是什么

• 存储过程可以将多个SQL语句写在一起，作为一个集合，类似Java里的方法封装。通过 Call 关键字调用。

创建存储过程

存储过程基本语句格式

   DELIMITER $$
   CREATE
   PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
   BEGIN
   [DECLARE 变量名 类型 [DEFAULT 值];]
   存储过程的语句块;
   END$$
   DELIMITER ;

example一：创建一个存储过程，使其返回sys_user表与sys_auth表的所有内容

• 创建语句

    create procedure test()
    begin
    select * from sys_user;
    select * from sys_auth ;
    end
    CALL test();
  

• 执行结果

    CALL test(); -- 执行结果输出两个表的所有数据
  

  

example二：创建一个存储过程，使其返回输入的第一个中文字符的拼音首字母

• 创建语句

    DROP procedure getFristpinyin;      -- 删除 getFristpinyin 存储过程
    CREATE procedure getFirstPinyin(in name varchar(255),out pinyin varchar(255)) -- 创建存储过程getFirstPinyin，入参为name，出参为pinyin
    begin
    DECLARE V_RETURN VARCHAR(255);     -- 定义变量
    SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(name USING gbk),1)),16,10),
        0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
        0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
        0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
        'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
    select V_RETURN into pinyin;       -- 将V_RETURN变量赋值给pinyin返回
    end
  

  

• 调用

    call getFirstPinyin("元稹",@pinyin);
    select @pinyin;
  

• 结果

  

使用Mybatis调用getFristpinyin存储过程

• Service层代码

    @Service
    @Slf4j
    public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
      @Override
      public String getTest() {
          Map<String,String> map = new HashMap(); //存储过程有入参，出参，使用map接收
          map.put("chinese","元");
          baseMapper.getTest(map);
          String result = map.get("result");
          log.info("输出的结果为：{}",result);
          return result;
      }
    }
  

• Mapper层代码

    <select id="getTest" resultType="java.lang.String" statementType="CALLABLE"  parameterType="Map">
        {CALL getFirstPinyin(#{chinese,mode = IN,jdbcType = VARCHAR},#{result,mode =OUT,jdbcType = VARCHAR})}
    </select>
  

  • parameterType=“Map” ：参数是Map集合
  • statementType=“CALLABLE”：表示执行存储过程
  • mode = IN / mode =OUT: 入参/出参
• 执行结果
  

存储函数

存储函数是什么

• 存储函数和存储过程有些类似，都可以封装多条SQL语句，存储函数可以像MySQL函数一样灵活调用，而不需要采用Call关键字调用，但是相比存储过程，存储函数只能return 一列数据作为结果。

创建存储函数准备

• 出现下列问题是因为mysql的设置默认是不允许创建存储函数的，需要设置系统信任创建函数。
  

    -- 查看mysql当前log_bin_trust_function_creators的值
    show variables like 'log_bin_trust_function_creators'; 
    -- 设置 log_bin_trust_function_creators的值为1，才可以创建函数
    SET GLOBAL log_bin_trust_function_creators = 1;       
  

• 尽管已经设置了全局变量log_bin_trust_function_creators的值为1，但是当MySQL服务重启时，该变量还会重新设置为0，所以为了永久设置该变量的值，可以在my.conf文件里设置：

  log_bin_trust_function_creators=1

创建存储函数

example：创建一个存储函数，使其返回输入字符串所有字符的拼音首字母

• 创建语句

    CREATE FUNCTION `pinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8  
    BEGIN  
        DECLARE V_COMPARE VARCHAR(255);      -- 定义变量     
        DECLARE V_RETURN VARCHAR(255);       -- 定义变量 
        DECLARE I INT;                       -- 定义变量 
        SET I = 1;  
        SET V_RETURN = '';  
        while I < LENGTH(P_NAME) do  
            SET V_COMPARE = SUBSTR(P_NAME, I, 1);  
            IF (V_COMPARE != '') THEN  
               CALL getFirstPinyin(V_COMPARE,@pinyin);        -- 调用了存储过程 getFirstPinyin
                SET V_RETURN = CONCAT(V_RETURN,@pinyin);
            END IF;  
            SET I = I + 1;  
        end while;  
        IF (ISNULL(V_RETURN) or V_RETURN = '') THEN  
            SET V_RETURN = P_NAME;  
        END IF;  
        RETURN V_RETURN;  
    END  
  

• 存储函数调用

    -- 调用时像普通SQL函数一样调用即可
    select upper(pinyin(nickname)) as un,nickname from sys_user where upper(pinyin(nickname)) like '%js%';  
  

  • 执行结果：
    

参考文章

• MySQL自定义中文转拼音函数