当前位置： 首页 > web >正文

MYSQL 存储过程详解

web 2025/7/4 13:42:11

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

目录

概念

创建存储过程

IN、OUT、INOUT参数例子：

IN方法：

OUT方法：

INOUT方法：

查询、删除存储过程：

存储过程的控制语言

变量作用域

条件语句

IF-ELSE语句

CASE-WHEN-ELSE语句

循环语句

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 INVOKERCOMMENT '存储过程的注释信息'BEGINSELECT 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)BEGINSET 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)BEGINSELECT 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变量进行赋值：BEGINSELECT count(0) INTO a_out FROM orderinfo;END$$

INOUT方法：

# 声明一个INOUT方法，方法体和上一个OUT方法完全一致
DELIMITER //CREATE PROCEDURE ycProc3(INOUT a_inout int)BEGINSELECT 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()BEGINDECLARE x1 VARCHAR(5) DEFAULT 'B';BEGINDECLARE 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)BEGINIF parameter=0 THENSELECT 1;END IF ;IF parameter>0 THENSELECT 2;ELSESELECT 3;END IF ;END ;//
DELIMITER ;

CASE-WHEN-ELSE语句

DROP PROCEDURE IF EXISTS proc;
DELIMITER //CREATE PROCEDURE proc(IN parameter INT)BEGINCASE parameterWHEN 0 THENSELECT 1;WHEN 1 THENSELECT 2;ELSESELECT 3;END CASE ;END ;//
DELIMITER ;

循环语句

WHILE-DO...END-WHILE语句

DROP PROCEDURE IF EXISTS proc;
DELIMITER //CREATE PROCEDURE proc()BEGINDECLARE var INT;SET var=0;WHILE var<6 DOSELECT var;SET var=var+1;END WHILE ;END;//
DELIMITER ;

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

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

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

DROP PROCEDURE IF EXISTS proc;
DELIMITER //CREATE PROCEDURE proc()BEGINDECLARE v INT;SET v=0;LOOP_LABLE:LOOPINSERT INTO t VALUES(v);SET v=v+1;IF v >=5 THENLEAVE 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) # 开平方

http://www.xdnf.cn/news/11334.html

相关文章：

  • delphi2007、2010无法二次启动，报错 EditorLineEnds.ttr 另一个程序正在使用此文件
  • 出现java.lang.IllegalArgumentException异常处理
  • 屏幕截取招招看！教你十种屏幕抓取方法
  • 带通滤波器电路图大全（三款带通滤波器电路设计原理图详解）
  • 宝妈剪辑视频也养活了自己，辞职做自媒体一天3百，靠这5个网站
  • 计算机硬盘有坏道,电脑硬盘有坏道怎么办
  • marquee横向循环滚动
  • 125款程序员专属情人节表白网站【建议收藏】HTML+CSS+JavaScript
  • vscode下git的常见操作
  • Restorator 2007 Build 1729 汉化版
  • mac装载NTFS的u盘报错：com.apple.DiskManagement.disenter错误49223
  • LBS应用简介及值得关注的方向
  • C++ 内存池 -- C++ Memory Pool
  • 系统引导方式GRUB的详细介绍
  • C++利用 _findfirst与_findnext查找文件的方法
  • （一）UDP基本编程步骤
  • dz带模板跟全套插件 搭建一款属于自己的论坛
  • 开源免费的一个企业级商城系统
  • 林锐《高质量程序设计指南C/C++》笔记04：异常处理
  • C# Label显示多行文本及换行(WinForm/WebForm)
  • 计算机论文投稿指南
  • MP3的频率、比特率、码率与音质的关系
  • PowerBuilder 12使.NET的应用开发更快捷
  • 适合新手编程的软件，什么编程软件比较好比较容易入门
  • ln 命令用法 hard link 与 symbolic link 区别 分析
  • 搜狗云输入法、Google手机语音搜索：两款创新云产品
  • IOCP的一些总结
  • linux串口驱动
  • 2020年12月统考练习题
  • 电脑蓝屏代码大全及解决办法合集