MySql存储过程

最新推荐文章于 2024-06-29 13:56:33 发布

时长两年半的Bug制造者

最新推荐文章于 2024-06-29 13:56:33 发布

阅读量706

点赞数

分类专栏： MySQL数据库文章标签：存储过程数据库

本文链接：https://blog.csdn.net/weixin_45013792/article/details/100046158

版权

MySQL数据库专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一、数据库存储过程（Stored Procedure）

SQL语句是需要先编译后执行，每执行一次就需要编译一次。

存储过程：一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。外部程序可以直接调用数据库里面定义好的存储过程，另外数据库内部的触发器(trigger)、或者其他存储过程也可以调用它。

存储过程的优缺点：
优点

1.执行速度快：存储过程创建是就已经通过语法检查和性能优化，在执行时无需每次编译。存储在数据库服务器，性能高。
2.允许模块化设计：只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改 。
3.提高系统安全性：可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限，建立特定的存储过程供用户使用，完成对数据的访问。存储过程的定义文本可以被加密，使用户不能查看其内容。
4.减少网络流量：一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。
5.可维护性高，一些业务规则发生变化时，有时只需调整存储过程，而不要改动和重编辑程序
6.更好的代码重用

缺点

1、存储过程会使得数据库占用的系统资源加大(cpu、memory)，数据库毕竟主要用来做数据存取的，并不进行复杂的业务逻辑操作。
2、因为存储过程依旧是sql，所以没办法像编程语言那样写出复杂业务逻辑对应的存储过程。
3、存储过程不容易进行调试。
4、存储过程书写及维护难度都比较大。

什么时候使用存储过程呢？

复杂的操作或需要事务操作的SQL建议使用存储过程，而参数多且操作简单SQL语句不建议使用存储过程。

存储过程的创建
语法：

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]])
 [特性 ...] 
过程体
（BEGIN
.....
END）

例如：

DELIMITER //
  CREATE PROCEDURE demo_out(OUT id int)
#BEGIN....END ===>相当于函数
    BEGIN
      SELECT id FROM t_user;
    END
    //
DELIMITER ;

注意：（如果是用MySQL的图形化工具（小海豚、那只猫）时，可以直接创建，不再需要声明。）
DELIMITER // 和 DELIMITER ;是两句
由于MySQL默认以";“为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错。那么我们需要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个”//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

参数说明：
存储过程的参数用在存储过程的定义，共有三种参数类型，IN、OUT、INOUT：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名数据类型…])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回

每执行完一次存储过程的创建
数据库中的函数就会增加一条
在这里插入图片描述
如果要使用存储过程的SQL语句（类似于调用方法一样）

使用Call 函数名（参数【@参数】）

变量
语法：

DECLARE 变量名1[,变量名2...] 数据类型 [默认值];

数据类型为MYSQL的数据类型：

数据类型
日期和时间类型
字符串类型

变量赋值
语法：

语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]

用户变量

用户变量一般以@开头
注意：滥用用户变量会导致程序难以理解及管理

在MySQL客户端使用用户变量
1.SELECT ‘anone’ into @a;
SELECT @a;

2.SET @b=‘Say hello to U’;
SELECT @b;

3.SET @c=2+2*4;
SELECT @c;
在存储过程中使用用户变量
CREATE PROCEDURE Hello() SELECT CONCAT(@hello,’ world’);
SET @hello=‘hello’;
CALL Hello();
在存储过程间传递全局范围的用户变量
CREATE PROCEDURE pro1() SET @procedure1=‘world’;
CREATE PROCEDURE pro2() SELECT CONCAT('hello ',@procedure1);
CALL pro1();
CALL pro2();

存储过程的调用：
用Call和过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输出输入参数

Call 存储过程名（）
参数类型：
in：@参数名
out：直接输出的参数，需要与定义的数据类型一致
inout：可以使用@参数名，也可以写输出参数

存储过程的查询

#查询存储过程
SELECT name FROM mysql.proc WHERE db='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
SHOW PROCEDURE STATUS WHERE db='数据库名';

#查看存储过程详细信息
SHOW CREATE PROCEDURE 数据库.存储过程名;

存储过程的修改
ALTER PROCEDURE 更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

ALTER {PROCEDURE | FUNCTION} name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

参数说明：

name参数表示存储过程或函数的名称；
characteristic参数指定存储函数的特性。
CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；
NO SQL表示子程序中不包含SQL语句；
READS SQL DATA表示子程序中包含读数据的语句；
MODIFIES SQL DATA表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。
COMMENT 'string'是注释信息。

例子：

#将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。
ALTER  PROCEDURE  id_from_user
  MODIFIES SQL DATA
  SQL SECURITY INVOKER ;
#将读写权限改为READS SQL DATA，并加上注释信息'FIND NUMBER'。
ALTER  PROCEDURE  number_from_employee
  READS SQL DATA
  COMMENT 'FIND NUMBER' ;

存储过程的删除
删除一个存储过程比较简单，和删除表一样：

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

存储过程的控制语句

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

#变量作用域
DELIMITER //
  CREATE PROCEDURE procedure1()
    BEGIN
      DECLARE proc1 VARCHAR(30) DEFAULT 'ProcedureIN';
        BEGIN
          DECLARE proc1 VARCHAR(30) DEFAULT 'ProcedureOUT';
          SELECT proc1;
        END;
      SELECT proc1;
    END;
    //
DELIMITER ;
#调用
CALL procedure1();

结果如下：
在这里插入图片描述
2.条件语句
IF-THEN-ELSE语句

#条件语句IF-THEN-ELSE
DROP PROCEDURE IF EXISTS proc3;
DELIMITER //
CREATE PROCEDURE procedure02(IN num int)
  BEGIN
    DECLARE pro2 int;
    SET pro2=num+1;
    IF pro2=1 THEN
      INSERT INTO table2 VALUES (20);
    END IF ;
    IF num=0 THEN
      UPDATE table2 SET prop2=prop2+10;
    ELSE
      UPDATE table2 SET prop2=prop2+20;
    END IF ;
  END ;
  //
DELIMITER ;

CASE-WHEN-THEN-ELSE语句

#CASE-WHEN-THEN-ELSE语句
DELIMITER //
  CREATE PROCEDURE procedure3 (IN num INT)
    BEGIN
      DECLARE pro3 INT;
      SET pro3=numr+1;
      CASE pro3
        WHEN 0 THEN
          INSERT INTO table3 VALUES (30);
        WHEN 1 THEN
          INSERT INTO table3 VALUES (40);
        ELSE
          INSERT INTO table3 VALUES (50);
      END CASE ;
    END ;
  //
DELIMITER ;

3.循环语句
WHILE-DO…END-WHILE

DELIMITER //
  CREATE PROCEDURE procedure4()
    BEGIN
      DECLARE pro4 INT;
      SET pro4=0;
      WHILE pro4<6 DO
        INSERT INTO table4 VALUES (pro4);
        SET pro4=pro4+33;
      END WHILE ;
    END;
  //
DELIMITER ;

REPEAT…END REPEAT

此语句的特点是执行操作后检查结果
DELIMITER //
  CREATE PROCEDURE procedure5 ()
    BEGIN
      DECLARE num INT;
      SET num=10;
      REPEAT
        INSERT INTO table5 VALUES(num);
        SET num=num+2;
        UNTIL num>=5
      END REPEAT;
    END;
  //
DELIMITER ;

LOOP…END LOOP

DELIMITER //
  CREATE PROCEDURE procedure6 ()
    BEGIN
      DECLARE num INT;
      SET num=10;
      LOOP_LABLE:LOOP
        INSERT INTO table6 VALUES(num);
        SET num=num+5;
        IF num >=5 THEN
          LEAVE LOOP_LABLE;
        END IF;
      END LOOP;
    END;
  //
DELIMITER ;

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

#ITERATE
DELIMITER //
  CREATE PROCEDURE procedure7()
  BEGIN
    DECLARE num INT;
    SET num=0;
    LOOP_LABLE:LOOP
      IF num=5 THEN
        SET num=num+5;
        ITERATE LOOP_LABLE;
      END IF;
      INSERT INTO table7 VALUES(num);
      SET num=num+5;
      IF num>=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个字符,
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

例如：
SELECT Right(‘anone’,3);
结果如下：
在这里插入图片描述
数学类

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
注：返回类型并非均为整数

例如：
SELECT LEAST(2,8,1,4,0,-1)；
结果如下：
在这里插入图片描述
日期时间类

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) //开平方

如：
给出年及年中的第几天,生成日期串
SELECT MAKEDATE(2019,180)；
结果如下：
在这里插入图片描述

时长两年半的Bug制造者

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySql存储过程

一、数据库存储过程（Stored Procedure）SQL语句是需要先编译后执行，每执行一次就需要编译一次。存储过程：一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。外部程序可以直接调用数据库里面定义好的存储过程，另外数据库内部的触发器(trigger)、或者其他存储过程也可以调用它。存...
复制链接

扫一扫

专栏目录