Mysql存储过程和存储函数
文章目录
1.5.1. IF - THEN - ELSEIF - ELSE -ENDIF
1.5.2. CASE - WHEN - THEN - ELSE - END CASE
Mysql存储过程和存储函数
存储过程的好处
- 增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
- 标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
- 较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
- 减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
- 作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
存储函数
创建存储函数
- 在Mysql中有许多已经存在的存储函数，比如
CONCAT(..),LENGTH(str)。但是我们也可以自己定义存储函数。 - 格式如下：
delimiter // -- 指定分割符
create function fun_name()
returns type -- type是执行存储函数返回的类型
begin
-- 执行其他的语句
return (); -- 返回的数据
end
//
delimiter ; -- 指定创建结束returns type: 指定存储函数返回的类型，比如returns char(50),returns int- 存储函数有且只有一个返回值
return (): 存储函数的返回值，这里的返回值类型需要和returns type中的类型一致，如果不一致会强制转换return (select name from user where id=1);
- 下面我们创建一个存储函数，返回
user表中的id=1的name值
delimiter //
create function selectUserById()
returns varchar(50)
begin
return (select name from user where id=1);
end
//
delimiter ;指定参数
- 在存储函数创建的时候还可以指定参数，这个参数是用户调用的时候输入的。
- 存储函数中的参数默认是
IN参数，而存储过程中的参数可以是IN、OUT、INOUT - 直接使用
parameter 类型指定即可，如果有多个参数可以使用,分割 - 在调用的时候直接使用
select funName(parmeter1,....);即可
delimiter //
create function selectUserById(uid int)
returns varchar(50)
begin
return (select name from user where id=uid);
end
//
delimiter ;调用存储函数
- 存储函数是依赖数据库的，因此我们需要在指定的数据库中调用，或者前面指定数据库的名称
select selectUserById();: 直接在存储函数所在数据库中调用select dbName.selectUserById();: 直接使用数据库的名称调用
删除存储函数
drop function selectUserById;：直接在存储函数所在数据库中直接删除存储函数drop function dbName.selectUserById;：使用数据库名称删除存储函数
查看存储函数状态
- 格式：
show function status [like pattern]： 查看存储函数的状态show function status \G: 查看所有的存储函数状态,\G是一种特定格式的输出show function status like 'select%'\G：查看select开头的存储函数状态，\G是一种特定格式的输入。
查看存储函数的定义
- 格式：
show create function dbName.funNameshow create function test.selectUserById \G;：查询test数据库中的存储函数selectUserById的定义，\G是一种特定的输出格式
修改存储函数
变量的使用
- 变量的作用范围是
begin.....end程序中
定义变量
- 格式：
declare var_name,.... type [default value]declare age int default 22：定义一个局部变量age，类型为int，默认值为22declare var1,var2,var3 int： 定义三个局部变量，类型为int
- 全部变量的声明一定要在赋值的前面，否则报错
定义用户变量
- 用户变量以
@开头 set @pin=10
为变量赋值
- 格式：
set var1=value1,[var2=value2,....]set age=33;： 设置age的值为33set var1=22,var2=33: 同时设置多个值
declare var1,var2,var3 int;
set var1=22,var2=33;
set var3=var1+var2;- 使用
select col_name[,...] into var_name[,....] table_expr: 使用select查询得到的结果赋值给变量- 这个
select把选定的列的值直接赋值给对应位置的变量 table_expr: 可以是表的查询条件，其中包含from 表名
- 这个
declare uname varchar(10); -- 定义变量uname
declare uage int; -- 定义变量uage
select name,age into uname,uage from user where id=1; -- 将id=1的用户姓名和年龄赋值给变量实例
在存储函数中使用
- 在存储函数中定义局部变量，并且获取输出
delimiter //
create function selectUserById(uid int)
returns varchar(50)
begin
declare uname varchar(50);
select name into uname from user where id=uid;
return uname;
end
//
delimiter ;在存储过程中使用
delimiter //
create procedure selectUserById(IN uid int)
begin
declare offest,count int; -- 定义偏移量
set offest=0,count=2; -- 赋值
if uid is not null -- 如果uid不为null，按照id查询
then select * from user where id=uid; -- 按照id查询
else select * from user limit offest,count; -- 否则uid为null，按照分页查询前面两个
end if;
end
//
delimiter ;call selectUserById(1); ： 查询id=1的用户信息call selectUserById(null);：查询所有的用户信息，显示前面两个
注释
- MySQL存储过程可使用两种风格的注释：
- 双杠：–，该风格一般用于单行注释
- C风格： 一般用于多行注释
流程控制标签的使用
- 在
begin和end之间使用
IF - THEN - ELSEIF - ELSE -ENDIF
- 格式
begin
if expression -- 判断条件
then .... ; -- 条件成立执行
elseif .....; -- 其他条件
else ..... ; -- 条件相反执行
endif; -- 结束if
end- 可以不是成对出现，比如只有
if，或者if-else - 如果没有
else，那么可以省略，比如if - then - endif - 判断相等使用
= - 实例
delimiter //
create procedure selectUserById(IN uid int)
begin
declare offest,count int; -- 定义偏移量
set offest=0,count=2; -- 赋值
if uid is not null -- 如果uid不为null，按照id查询
then select * from user where id=uid; -- 按照id查询
else select * from user limit offest,count; -- 否则uid为null，按照分页查询前面两个
end if;
end
//
delimiter ;CASE - WHEN - THEN - ELSE - END CASE
- 这个和java中的
switch-case-default相似 - 格式：
case expr
when value1 then ....;
when value2 then .....;
when......;
....
else .......;
end case;- 实例
- 创建一个存储过程，使用
case
- 创建一个存储过程，使用
delimiter //
create procedure deleteUserById(IN uid int)
begin
case uid -- uid做选择
when 1 -- uid==1
then delete from user where id=1;
when 2 -- uid==2
then delete from user where id=2;
else
delete from user; -- 删除全部
end case;
end;
//
delimiter ;LOOP - ENDLOOP
LOOP只是创建一个循环执行的过程，并不进行条件判断，这个和while不一样，不需要判断条件，如果不跳出，那么将会永远的执行的下去。但是我们可以使用leave跳出循环- 格式：
[LOOP_LABEL]:LOOP
statement;
END LOOP [LOOP_LABEL];- 实例
- 执行这个语句可以插入
9条数据，如果i>=10跳出循环
- 执行这个语句可以插入
delimiter //
create procedure insertUserByName(IN uname varchar(50))
begin
declare i int default 0;
add_loop:loop -- 开始循环
set i=i+1; -- id++操作
insert into user(name) values(uname); -- 插入语句
if i>=10
then leave add_loop; -- 使用leave跳出循环
end if;
end loop add_loop; -- 结束循环
end
//
delimiter ;LEAVE
- 和循环一起使用，用于退出循环控制，见上面的例子
ITERATE
- 格式：
iterate label iterate只可以出现在LOOP，REPEAT，WHIE语句内，表示再次循环的意思，label表示循环的标志- 实例
- 如果
p<10重复执行p++
- 如果
delimiter //
create procedure doiterate()
begin
declare p int default 0; -- 定义局部变量
my_loop:loop
set p=p+1; -- p++
if p<10
then iterate my_loop; -- 继续执行前面的循环的语句，p++
elseif p>20
then leave my_loop;
end if
select "p在10到20之间" -- 输出语句
end loop my_loop;
end
//
delimiter ;REPEAT
- 这个也是循环语句，相当于
do-while - 格式：
[repeat_loop]: repeat
statement_list;
until exper -- 没有分号
end repeat;- 实例
delimiter //
create procedure dorepeat()
begin
declare p int default 0; -- 定义局部变量
my_loop:repeat
set p=p+1;
select p;
until p>10 -- 当p>10的时候循环结束
end repeat my_loop;
end
//
delimiter ;WHILE
- 这个和
REPEAT不同，先进行判断，然后才执行语句 - 格式：
[while_label]:while expr do
statement_list;
end while [while_lable];- 实例
delimiter //
create procedure dowhile()
begin
declare p int default 0; -- 定义局部变量
my_loop:while p<10 do -- 满足条件才执行
set p=p+1; -- p++
end while my_loop; -- 结束循环
end
//
delimiter ;存储过程
- 存储过程没有返回值
创建存储过程
- 格式：
delimiter //
create procedure p_name([IN,OUT,INOUT]parameter 类型.....)
begin
-- 执行功能
end
//
delimiter ;参数
- 存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,”分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
- IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
- OUT:该值可在存储过程内部被改变，并可返回
- INOUT:调用时指定，并且可被改变和返回
过程体
- 过程体的开始与结束使用
BEGIN与END进行标识。
实例
- 定义一个根据
id查询的查询用户信息的存储过程，这里的id是由用户输入的，因此可以使用IN参数
delimiter //
create procedure selectUserById(IN uid int)
begin
select * from user where id=uid;
end
//
delimiter ;调用存储过程
- 格式：
call procedure_name(...)call selectUserById(1);: 直接在当前的数据库中调用存储过程selectUserByIdcall db_name.selectUsrById(1): 指定数据库的名字调用
查看存储过程的状态
- 格式：
show procedure status like pattern \Gshow procedure status like "select%"\G: 查看select开头的存储过程状态show procedure status \G: 查看所有的存储过程状态
- 查询的结果如下：
*************************** 1. row ***************************
Db: test -- 数据库名称
Name: selectUserById -- 存储过程的名字
Type: PROCEDURE
Definer: root@localhost
Modified: 2018-06-25 22:25:44
Created: 2018-06-25 22:25:44
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.01 sec)查看存储过程的定义
- 格式：
show create procedure db.pro_nameshow create procedure test.selectUserById\G: 查询数据库test中存储过程的定义
- 返回的结果如下：
*************************** 1. row ***************************
Procedure: selectUserById
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `selectUserById`(IN uid int)
begin
select * from user where id=uid;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci删除存储过程
- 格式
drop procedure pro_namedrop procedure selectUserById: 删除当前数据库的selectUserById的存储过程drop procedure test.selectUserById;: 删除test数据库的selectUserById的存储过程
游标cursor
- 在面对大量的数据的时候，游标能够一行一行的读取数据
声明游标
- 格式：
declare cursor_name cursor for select_statementcursor_name: 游标的变量名称select_statement：表示select语句，用于返回一个结果集给游标
- 比如：
declare users cursor for select name,age from user;
打开游标
- 格式：
open cursor_name;open users
使用游标获取一行数据
- 格式：
fetch cursor_name into var_name[,var_name,...]cursor_name：表示游标的名称var_name： 表示将select语句查询到的一行信息存入到该参数中，var_name必须在声明游标之前定义好
- 比如：
fetch user into uname,uage
关闭游标
- 格式：
close cursor_name
实例
- 使用游标获取
user表中的一行数据
delimiter //
create procedure selectOneUser()
begin
declare uname varchar(50); -- 定义uname存储
declare uage int; -- 定义uage存储
declare users cursor for select name,age from user; -- 声明游标
open users; -- 打开游标
fetch users into uname,uage; -- 获取一行数据到存储到uname和uage中
select uname as name,uage as age; -- 输出一行的结果
close users; -- 关闭游标
end
//
delimiter ;
call selectOneUser(); -- 调用存储过程，此时只是输出第一行的数据- 使用循环获取所有的数据
- 这里使用循环获取，首先需要使用
select count(*)获取总数
- 这里使用循环获取，首先需要使用
delimiter //
create procedure selectUsers()
begin
declare uname varchar(50); -- 定义uname存储
declare uage int; -- 定义uage存储
declare total int default 0; -- 定义count，这个用来统计总数
declare i int default 1; -- 用来循环
declare users cursor for select name,age from user; -- 声明游标
select count(*) from user into total; -- 查询总数
open users; -- 打开游标
-- 开始循环遍历
my_loop:while i<=total do
set i=i+1; -- i++
fetch users into uname,uage; -- 获取一行数据到存储到uname和uage中
select uname as name,uage as age; -- 输出一行的结果
end while my_loop;
close users; -- 关闭游标
end
//
delimiter ;
call selectUsers(); -- 调用存储过程，获取全部数据- 使用
HANDLER判断游标是否还有元素continue HANDLER for not found- 当游标中没有值的时候就会指定返回的值
delimiter //
create procedure selectUsers()
begin
declare uname varchar(50); -- 定义uname存储
declare uage int; -- 定义uage存储
declare flag int default 1; -- 创建结束游标的标志，默认值为1
declare users cursor for select name,age from user; -- 声明游标
declare continue HANDLER for not found set flag=0; -- 指定游标结束时的返回值
open users; -- 打开游标
my_loop:loop
if flag=0 -- 这里使用=，否则报错
then leave my_loop; -- 跳出循环
end if;
fetch users into uname,uage; -- 获取一行数据到存储到uname和uage中
select uname as name,uage as age; -- 输出一行的结果
end loop my_loop;
close users; -- 关闭游标
end
//
delimiter ;存储过程和存储函数的区别
- 存储函数可以使用
return返回一个返回值，但是存储过程不能有返回值，如果需要实现返回的功能，可以使用OUT参数实现返回 - 存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
- 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用
insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。 - 存储过程可以调用存储函数。但函数不能调用存储过程。
- 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
总结
- 存储过程中可以使用
call调用其他的存储过程，但是不能使用drop语句删除其他的存储过程 - 存储过程的参数不要和数据库表的字段相同，否则将出现无法预料的结果
参考文章
delimiter //
create function selectUserById()
returns varchar(50)
begin
declare uname varchar(50);
select name into uname from user where id=2;
return uname;
end
//
delimiter ;本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2018-06-26，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
对象存储
对象存储（Cloud Object Storage，COS）是由腾讯云推出的无目录层次结构、无数据格式限制，可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限，无需分区管理，适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。