mysql整理总结（三）：存储过程

最新推荐文章于 2021-02-18 01:15:02 发布

Send_youCherry

最新推荐文章于 2021-02-18 01:15:02 发布

阅读量181

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/ll_mor/article/details/79377839

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

存储过程简介

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。

存储过程的优点

增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。减小程序业务的复杂性，在一定程度上降低了程序的耦合性。
较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。不同的排序算法性质有所不同，衡量算法的效率，通常是用资源，例如CPU(时间)占用、内存占用、硬盘占用和网络占用。当讨论大O表示法时，一般考虑的是CPU占用。

MySQL存储过程

语法

DELIMITER $$
CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER ;

参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,”分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

MySQL 存储过程 “in” 参数：跟 C 语言的函数参数的值传递类似， MySQL 存储过程内部可能会修改此参数，但对 in 类型参数的修改，对调用者（caller）来说是不可见的（not visible）。

drop procedure if exists pr_param_in;
create procedure pr_param_in
(
in id int -- in 类型的 MySQL 存储过程参数
)
begin
if (id is not null) then
set id = id + 1;
end if;
select id as id_inner;
end;

mysql> call pr_param_in(@id);
+----------+
| id_inner |
+----------+
|       11 |
+----------+
mysql> select @id as id_out;
+--------+
| id_out |
+--------+
| 10     |
+--------+

可以看到：用户变量 @id 传入值为 10，执行存储过程后，在过程内部值为：11（id_inner），但外部变量值依旧为：10（id_out）。

OUT:该值可在存储过程内部被改变，并可返回

MySQL 存储过程 “out” 参数：从存储过程内部传值给调用者。在存储过程内部，该参数初始值为 null，无论调用者是否给存储过程参数设置值。

drop procedure if exists pr_param_out;
create procedure pr_param_out
(
out id int
)
begin
select id as id_inner_1;  -- id 初始值为 null
if (id is not null) then
set id = id + 1;
select id as id_inner_2;
else
select 1 into id;
end if;
select id as id_inner_3;
end;

mysql> set @id = 10;
mysql>
mysql> call pr_param_out(@id);
+------------+
| id_inner_1 |
+------------+
|       NULL |
+------------+
+------------+
| id_inner_3 |
+------------+
|          1 |
+------------+
mysql> select @id as id_out;
+--------+
| id_out |
+--------+
| 1      |
+--------+

可以看出，虽然我们设置了用户定义变量 @id 为 10，传递 @id 给存储过程后，在存储过程内部，id 的初始值总是 null（id_inner_1）。最后 id 值（id_out = 1）传回给调用者。

INOUT:调用时指定，并且可被改变和返回

MySQL 存储过程 inout 参数跟 out 类似，都可以从存储过程内部传值给调用者。不同的是：调用者还可以通过 inout 参数传递值给存储过程。

drop procedure if exists pr_param_inout;
create procedure pr_param_inout
(
inout id int
)
begin
select id as id_inner_1;  -- id 值为调用者传进来的值
if (id is not null) then
set id = id + 1;
select id as id_inner_2;
else
select 1 into id;
end if;
select id as id_inner_3;
end;

mysql> set @id = 10;
mysql>
mysql> call pr_param_inout(@id);
+------------+
| id_inner_1 |
+------------+
|         10 |
+------------+
+------------+
| id_inner_2 |
+------------+
|         11 |
+------------+
+------------+
| id_inner_3 |
+------------+
|         11 |
+------------+
mysql>
mysql> select @id as id_out;
+--------+
| id_out |
+--------+
| 11     |
+--------+

从结果可以看出：我们把 @id（10），传给存储过程后，存储过程最后又把计算结果值 11（id_inner_3）传回给调用者。 MySQL 存储过程 inout 参数的行为跟 C 语言函数中的引用传值类似。

通过以上例子：如果仅仅想把数据传给 MySQL 存储过程，那就使用“in” 类型参数；如果仅仅从 MySQL 存储过程返回值，那就使用“out” 类型参数；如果需要把数据传给 MySQL 存储过程，还要经过一些计算后再传回给我们，此时，要使用“inout” 类型参数。

动态拼接sql例子

DELIMITER $$
DROP PROCEDURE IF EXISTS dsql $$
CREATE PROCEDURE dsql(IN parameter INT,IN name1 VARCHAR(64))
  BEGIN
    SET @sql1='SELECT * FROM  t_user u WHERE 1=1';
    IF parameter IS NOT NULL THEN
      SET @sql1= CONCAT(@sql1,' and u.id = ',parameter);
    END IF ;
    IF (name1 IS NOT NULL AND name1!='') THEN
      SET @sql1= CONCAT(@sql1,' and u.name LIKE\'%',name1,'%\'');
    END IF ;
    PREPARE stmt FROM @sql1;
    EXECUTE stmt;
  END$$
DELIMITER ;

实际例子

DELIMITER $$
DROP PROCEDURE IF EXISTS ckm $$
CREATE
    PROCEDURE ckm(IN t1 INT)
    BEGIN
    DECLARE u_time TIMESTAMP;
    SELECT  t.update_time INTO  u_time FROM tx_msg_task t ;
    UPDATE tx_msg_task SET update_time=NOW();
    DELETE uk FROM tx_msg_uk uk INNER JOIN tx_msg_hc h ON  uk.msg_key=h.msg_key;
    PREPARE sql1 FROM 'SELECT * FROM tx_msg_uk u  WHERE DATE_SUB(NOW(),INTERVAL ? MINUTE) >=u.update_time';
    SET @a = t1;
    EXECUTE sql1 USING @a;#多个可以，分割
    DEALLOCATE PREPARE sql1;#清理资源
    END$$
DELIMITER ;

MySQL存储过程的调用

用call和过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。CALL

MySQL存储过程的查询

SELECT name FROM mysql.proc WHERE db='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
SHOW PROCEDURE STATUS WHERE db='数据库名';

MySQL存储过程的删除

DROP PROCEDURE [过程1[,过程2…]]
从MySQL的表格中删除一个或多个存储过程。

存储程序中的变量

DECLARE局部变量 DECLARE var_name[,…] type [DEFAULT value]
这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。局部变量的作用范围在它被声明的BEGIN … END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。
变量SET语句 SET var_name = expr [, var_name = expr]
在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量。在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, …这样的扩展语法。其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。
SELECT … INTO
SELECT col_name[,…] INTO var_name[,…] table_expr这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。 SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

列子：

SELECT 'Hello World' INTO @a;
SELECT @a;

SET @b='Hello World';
SELECT @b;

SET @c=1+2+3;
SELECT @c; 

#在存储过程中使用用户变量
CREATE PROCEDURE sayHi() SELECT CONCAT(@hi,'...');
SET @hi='hi';
CALL sayHi(); 
#在存储过程间传递全局范围的用户变量
CREATE PROCEDURE tp1() SET @param='p1';
CREATE PROCEDURE tp2() SELECT CONCAT('param... ',@param);
CALL tp1();
CALL tp2();

MySQL存储过程的控制语句

变量作用域

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `proc`$$
  CREATE PROCEDURE proc()
    BEGIN
      DECLARE x1 VARCHAR(5) DEFAULT 'outer';
        BEGIN
          DECLARE x1 VARCHAR(5) DEFAULT 'inner';
          SELECT x1;
        END;
      SELECT x1;
    END$$
DELIMITER ; #调用 CALL proc();  结果：inner

条件语句

IF-THEN-ELSE

DROP PROCEDURE IF EXISTS proc3 ;
DELIMITER $$
CREATE PROCEDURE proc3(IN parameter INT)
  BEGIN
    DECLARE var INT;
    SET var=parameter+1;
    IF var=0 THEN
      INSERT INTO t VALUES (17);
    END IF ;
    IF parameter=0 THEN
      UPDATE t SET s1=s1+1;
    ELSE
      UPDATE t SET s1=s1+2;
    END IF ;
  END$$
DELIMITER ;

CASE-WHEN-THEN-ELSE语句

DELIMITER $$
  CREATE PROCEDURE proc4 (IN parameter INT)
    BEGIN
      DECLARE var INT;
      SET var=parameter+1;
      CASE var
        WHEN 0 THEN
          INSERT INTO t VALUES (17);
        WHEN 1 THEN
          INSERT INTO t VALUES (18);
        ELSE
          INSERT INTO t VALUES (19);
      END CASE ;
    END$$
DELIMITER;

循环语句

WHILE-DO…END-WHILE

DELIMITER $$
  CREATE PROCEDURE proc5()
    BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
        INSERT INTO t VALUES (var);
        SET var=var+1;
      END WHILE ;
    END$$
DELIMITER;

REPEAT…END REPEAT

此语句的特点是执行操作后检查结果

DELIMITER $$
  CREATE PROCEDURE proc6 ()
    BEGIN
      DECLARE v INT;
      SET v=0;
      REPEAT
        INSERT INTO t VALUES(v);
        SET v=v+1;
        UNTIL v>=5
      END REPEAT;
    END$$
DELIMITER ;

LOOP…END LOOP

DELIMITER $$
  CREATE PROCEDURE proc7 ()
    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 ;

LABLES标号:标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

ITERATE迭代通过引用复合语句的标号,来从新开始复合语句

DELIMITER $$
  CREATE PROCEDURE proc8()
  BEGIN
    DECLARE v INT;
    SET v=0;
    LOOP_LABLE:LOOP
      IF v=3 THEN
        SET v=v+1;
        ITERATE LOOP_LABLE;
      END IF;
      INSERT INTO t VALUES(v);
      SET v=v+1;
      IF v>=5 THEN
        LEAVE LOOP_LABLE;
      END IF;
    END LOOP;
  END$$
DELIMITER ;

MySQL存储过程的基本函数

字符串类

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个字符,
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
注：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为小数位数] 注：返回类型并非均为整数，如：
SIGN (number2 ) // 正数返回1，负数返回-1
MINUTE(datetime) //分返回符号,正负或0
SQRT(number2) //开平方

日期时间类

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) //月

mybatis调用存储过程

<select id="proFindUser" resultMap="BaseResultMap" statementType="CALLABLE">
    {
        CALL find_user(#{uid,mode=IN,jdbcType=BIGINT},#{name,mode=IN,jdbcType=VARCHAR})
    }
</select>

Send_youCherry

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql整理总结（三）：存储过程

存储过程简介SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。...
复制链接

扫一扫

专栏目录