MYSQL 存储过程详解

夜晨丶

已于 2023-03-20 15:26:19 修改

阅读量709

点赞数

文章标签： mysql java 数据库

于 2023-02-12 22:04:39 首次发布

本文链接：https://blog.csdn.net/qq_38098608/article/details/128890201

版权

用了多年的mysql，基本上都是在写增删改查的SQL语句，对存储过程只是有所耳闻，一直没有认真的去了解、使用过。所以编写这篇文档，记录对存储过程的学习笔记和 mysql函数编程的一些基础知识。

WHILE-DO...END-WHILE语句

REPEAT...END REPEAT语句（相当于先do，再检查结果）

LOOP...END LOOP语句（可以在循环体任意位置跳出）

概念

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。

类似于Java中的方法，需要指定方法名，并给定参数（如果需要的话）来调用执行。

MySQL 5.0开始支持存储过程。

优点：

功能强大、灵活：不是傻乎乎的一大条SQL语句，因为加入了控制语言，可以完成复杂的逻辑判断、运算
标准组件式编程：创建后可以被多次调用，就像Java封装通用方法，大家都可以调用它。
较快的执行速度：如果存储过程里有多条SQL，那么相较于传统的Java调用，免去了多次建立、关闭mysql连接的过程，所以更加高效
安全：通过对存储过程的权限控制，可以保证数据安全

为了便于理解，在下文中我会把“存储过程”类比做java中的“方法”。

创建存储过程

基本语法：CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名数据类型[,[IN|OUT|INOUT] 参数名数据类型…]]) [特性 ...] 过程体

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
  SQL SECURITY INVOKER
  COMMENT '存储过程的注释信息'
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

为了避免编译器把上述代码当成SQL语句处理，所以要在开头用 “DELIMITER //” 来声明分隔符，让编译器把2个 “//” 之间的内容当做存储过程的代码。结尾处的 “DELIMITER ;” 表示把分隔符还原。整体使用下来，有点类似SQL事务的 BEGIN; 和 COMMIT;
开头为固定值 CREATE PROCEDURE，后面 myproc 表示创建的“方法名”。
括号内表示“方法”的参数定义，共有3种类型：IN, OUT, INOUT

IN：表示该参数为传入参数，且必传。另外，IN参数的值被保护。这意味着即使在方法体中更改了IN参数的值，在方法调用结束后仍保留其原始值。换句话说，方法只使用IN参数的副本。

OUT：表示该参数为返回参数（在调用“方法”时，要把该参数传入，“方法体”中对其赋值后会回传）。可以在方法体中更改OUT参数的值，并将其更改后新值回传给主程序。请注意，存储过程在启动时无法访问OUT参数的初始值。

INOUT：是IN和OUT参数的组合，表示该参数既是传入参数，也是返回参数。调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序。

以上面代码为例：s表示变量名，int为数据类型。如果有多个参数，用 “,” 分隔开，例：myproc(OUT a int, b int, c VARCHAR(25)) 、myproc(OUT a int, OUT b int))

SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。

IN、OUT、INOUT参数例子：

IN方法：

DROP PROCEDURE IF EXISTS ycProc1; # 注意存储名不加引号。
 
# 声明一个IN方法，功能是将传入参数值+1
DELIMITER //
  CREATE PROCEDURE ycProc1(IN a_in int)
    BEGIN
        SET a_in = a_in + 1;
    SELECT a_in;
    END;
    //
DELIMITER ;
 
# 调用
set @tt=1;              # 用set来声明变量，且变量名必须以@开头。可同时赋值多个变量：set @a=1,@b=2,@c=3;
call ycProc1(@tt);      # 用关键字call来调用方法
select @tt;
 
# 结果
会执行2次SELECT SQL，输出结果分别为：2、1
 
# 总结
IN方法传入参数，但不返回参数。即便在方法体中修改了参数的值，也不会影响主函数传入变量（@tt）的值

OUT方法：

# 声明一个OUT方法，功能是将传入参数值set为7，并返回
DELIMITER //
  CREATE PROCEDURE ycProc2(OUT a_out int)
    BEGIN
    SELECT a_out;
        SET a_out = 7;
    SELECT a_out;
    END;
    //
DELIMITER ;
 
# 调用
set @tt=1;              # 用set来声明变量，且变量名必须以@开头。可同时赋值多个变量：set @a=1,@b=2,@c=3;
call ycProc2(@tt);      # 用关键字call来调用方法
select @tt;
 
# 结果
会执行3次SELECT SQL，输出结果分别为：null、7、7
 
# 总结
OUT方法不关心传入参数的值（就算读，也是读到null），在方法体中修改参数值，会回传，影响主函数传入变量的（@tt）值。
 
# 拓展：也可以通过select ... into ...的方式，对OUT变量进行赋值：
    BEGIN
        SELECT count(0) INTO a_out FROM orderinfo;
    END$$

INOUT方法：

# 声明一个INOUT方法，方法体和上一个OUT方法完全一致
DELIMITER //
  CREATE PROCEDURE ycProc3(INOUT a_inout int)
    BEGIN
    SELECT a_inout;
        SET a_inout = 7;
    SELECT a_inout;
    END;
    //
DELIMITER ;
 
# 调用
set @tt=1;              # 用set来声明变量，且变量名必须以@开头。可同时赋值多个变量：set @a=1,@b=2,@c=3;
call ycProc3(@tt);      # 用关键字call来调用方法
select @tt;
 
# 结果
会执行3次SELECT SQL，输出结果分别为：1、7、7
 
# 总结
INOUT方法读取传入参数值，在方法体中修改参数值，会回传，影响主函数传入变量的（@tt）值。

查询、删除存储过程：

# 查询
SELECT * FROM mysql.proc WHERE db='数据库名';
SHOW PROCEDURE STATUS WHERE db='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
 
# 删除
DROP PROCEDURE [过程1[,过程2…]]

存储过程的控制语言

变量作用域

内部变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，不再可见了，在存储
过程外再也找不到这个内部变量，但是可以通过out参数或者将其值指派给会话变量来保存其值。

#变量作用域
DELIMITER //
  CREATE PROCEDURE proc()
    BEGIN
      DECLARE x1 VARCHAR(5) DEFAULT 'B';
        BEGIN
          DECLARE x1 VARCHAR(5) DEFAULT 'A';
          SELECT x1;
        END;
      SELECT x1;
    END;
    //
DELIMITER ;
 
#调用
CALL proc();
 
#结果
执行两次SELECT SQL，分别打印：A、B

条件语句

IF-ELSE语句

DROP PROCEDURE IF EXISTS proc;
DELIMITER //
CREATE PROCEDURE proc(IN parameter int)
  BEGIN
    IF parameter=0 THEN
      SELECT 1;
    END IF ;
 
    IF parameter>0 THEN
      SELECT 2;
    ELSE
      SELECT 3;
    END IF ;
  END ;
  //
DELIMITER ;

CASE-WHEN-ELSE语句

DROP PROCEDURE IF EXISTS proc;
DELIMITER //
  CREATE PROCEDURE proc(IN parameter INT)
    BEGIN
      CASE parameter
        WHEN 0 THEN
          SELECT 1;
        WHEN 1 THEN
          SELECT 2;
        ELSE
          SELECT 3;
      END CASE ;
    END ;
  //
DELIMITER ;

循环语句

WHILE-DO...END-WHILE语句

DROP PROCEDURE IF EXISTS proc;
DELIMITER //
  CREATE PROCEDURE proc()
    BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
        SELECT var;
        SET var=var+1;
      END WHILE ;
    END;
  //
DELIMITER ;

REPEAT...END REPEAT语句（相当于先do，再检查结果）

DROP PROCEDURE IF EXISTS proc;
DELIMITER //
  CREATE PROCEDURE proc()
    BEGIN
      SET v=0;
      REPEAT
        SELECT ;
        SET v=v+1;
        UNTIL v>=5
      END REPEAT;
    END;
  //
DELIMITER ;

LOOP...END LOOP语句（可以在循环体任意位置跳出）

DROP PROCEDURE IF EXISTS proc;
DELIMITER //
  CREATE PROCEDURE proc()
    BEGIN
      DECLARE v INT;
      SET v=0;
      LOOP_LABLE:LOOP
        INSERT INTO t VALUES(v);
        SET v=v+1;
        IF v >=5 THEN
          LEAVE LOOP_LABLE;
        END IF;
      END LOOP;
    END;
  //
DELIMITER ;

一些基本函数

字符串类

CHARSET(str) # 返回字串字符集
CONCAT (string2 [,... ]) # 连接字串
INSTR (string ,substring ) # 返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) # 转换成小写
LEFT (string2 ,length ) # 从string2中的左边起取length个字符
LENGTH (string ) # string长度
LOAD_FILE (file_name ) # 从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) # 重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) # 去除前端空格
REPEAT (string2 ,count ) # 重复count次
REPLACE (str ,search_str ,replace_str ) # 在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) # 在str后用pad补充,直到长度为length
RTRIM (string2 ) # 去除后端空格
STRCMP (string1 ,string2 ) # 逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) # 从str的position开始,取length个字符,
# 注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

数字类

ABS (number2 ) # 绝对值
BIN (decimal_number ) # 十进制转二进制
CEILING (number2 ) # 向上取整
CONV(number2,from_base,to_base) # 进制转换
FLOOR (number2 ) # 向下取整
FORMAT (number,decimal_places ) # 保留小数位数
HEX (DecimalNumber ) # 转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) # 求最小值
MOD (numerator ,denominator ) # 求余
POWER (number ,power ) # 求指数
RAND([seed]) # 随机数
ROUND (number [,decimals ]) # 四舍五入,decimals为小数位数] 注：返回类型并非均为整数

日期时间类

ADDTIME (date2 ,time_interval ) # 将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) # 转换时区
CURRENT_DATE () # 当前日期
CURRENT_TIME () # 当前时间
CURRENT_TIMESTAMP () # 当前时间戳
DATE (datetime ) # 返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) # 在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) # 使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) # 在date2上减去一个时间
DATEDIFF (date1 ,date2 ) # 两个日期差
DAY (date ) # 返回日期的天
DAYNAME (date ) # 英文星期
DAYOFWEEK (date ) # 星期(1-7) ,1为星期天
DAYOFYEAR (date ) # 一年中的第几天
EXTRACT (interval_name FROM date ) # 从date中提取日期的指定部分
MAKEDATE (year ,day ) # 给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) # 生成时间串
MONTHNAME (date ) # 英文月份名
NOW ( ) # 当前时间
SEC_TO_TIME (seconds ) # 秒数转成时间
STR_TO_DATE (string ,format ) # 字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) # 两个时间差
TIME_TO_SEC (time ) # 时间转秒数]
WEEK (date_time [,start_of_week ]) # 第几周
YEAR (datetime ) # 年份
DAYOFMONTH(datetime) # 月的第几天
HOUR(datetime) # 小时
LAST_DAY(date) # date的月的最后日期
MICROSECOND(datetime) # 微秒
MONTH(datetime) # 月
MINUTE(datetime) # 分返回符号,正负或0
SQRT(number2) # 开平方