最简单的mysql存储过程和函数

最新推荐文章于 2022-08-03 23:22:50 发布

百灵不鸣

最新推荐文章于 2022-08-03 23:22:50 发布

阅读量274

点赞数

分类专栏：笨鸟系列文章标签： mysql

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/qq_42892858/article/details/114748758

版权

笨鸟系列专栏收录该内容

3 篇文章 1 订阅

订阅专栏

目录

1，什么是存储过程

2.1）创建存储过程

2.2）调用与销毁存储过程

2.3)查看存储过程

3、存储过程的使用

3.2 无参存储过程

3.3带in模式的存储过程

3.5 带out模式的存储过程

3.6 带inout模式的存储过程

4、存储过程例

1.1）创建语法

1.2)调用语法

1.3）查看与销毁

流程控制结构

1.1） if函数

1.2) case结构

存储过程

1，什么是存储过程

存储过程指一组预先编译好的sql语句的集合，理解成批处理语句。学好存储过程，需要大量实践，做案例。

提高代码的重用性
简化操作
减小了编译次数并且减少了和数据库服务器的连接次数，提高了效率

2、语法

2.1）创建存储过程

create procedure 存储过程名(参数列表)
begin
    存储过程体（一组合法的sql语句）
end
注意：
1、参数列表包含三部分
参数模式   参数名   参数类型
举例：  in  stuname varchar(20)
参数模式：
    in： 该参数可以作为输入
    out:  该参数可以作用输出，也就是该参数可以作为返回值
    inout: 该参数既可以作为输入又可以作为输出
2、如果存储过程体仅仅只有一句话，begin end可以省略

2.2）调用与销毁存储过程

#调用存储过程
call 过程名(传入参数)
#销毁存储过程
drop procedure 过程名,过程名;

2.3)查看存储过程

mysql> show create procedure myp4&  #查看名字为myp4的存储过程
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                                                  | Create Procedure                                                                                                        | character_set_client | collation_connection | Database Collation |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| myp4      | 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 DEFINER=`root`@`localhost` PROCEDURE `myp4`(inout a int,inout b int)
begin
    set a = a*2;
    set b = b*2;
end | utf8mb4              | utf8mb4_general_ci   | latin1_swedish_ci  |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

3、存储过程的使用

3.1 sql模型

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for rfid_archive
-- ----------------------------
DROP TABLE IF EXISTS `rfid_archive`;
CREATE TABLE `rfid_archive`  (
  `r_archive_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `r_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `r_state` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL ,
  PRIMARY KEY (`r_archive_id`, `r_type`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11255156218 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

3.2 无参存储过程

#无参存储过程
#向表向插入一条 r_type 等于1的数据
delimiter $   //存储过程里每条sql语句必须加 ';'，但存储过程结尾不重新设置就和语句冲突了
create procedure myp2()
begin
insert into rfid_archive(r_type) value (1);
end$
call myp2()

3.3带in模式的存储过程

#自定义变量
#动态向数据库插入  r_type,r_state 
#参数有就近原则 
delimiter $
create procedure myp4(in type varchar(10),in state varchar(10))
begin 
	declare result varchar(20) default '';  #自定义一个变量，declare声明变量，必须在begin,end中使用
	select count(*) into result from rfid_archive; //into 代表把count(*)值传入result
	insert into rfid_archive(r_type,r_state) value (type,state);
	select result;
end$
call myp4('人','w')

3.4 if与for

#if方法使用
select if(count(*)>0,'成功','失败') from rfid_archive;

#for使用
drop procedure forTest;
delimiter $
create procedure forTest(in sum int)
begin
	declare i int default 0;
	while i < sum do
		insert into rfid_archive(r_type,r_state) value (concat(i,'type'),concat(i,'state'));
		set i=i+1;
	end while;
end$
call forTest(1000000);

3.5 带out模式的存储过程

#通过r_archive_id查询对应的r_type,r_state

create procedure myp3(in id bigint,out type varchar(20),out state varchar(20))
begin
    select r_type,r_state into type,state #into是写在中间的
    from rfid_archive
    where r_archive_id=id;
end&



mysql> set @type = '',@stat=''&&   # @代表定义会话变量
Query OK, 0 rows affected (0.00 sec)
mysql> call myp3('11255156228',@type,@stat)&&  #y调用方法
Query OK, 1 row affected (0.01 sec)
mysql> select @type,@stat&&
+-------+--------+
| @type | @stat  |
+-------+--------+
| 7type | 7state |

3.6 带inout模式的存储过程

# 传入a,b 返回 两值×2
create procedure myp4(inout a int,inout b int)
begin
    set a = a*2;
    set b = b*2;
end&

set @c = 2,@d = 3&
call myp4(@c,@d)&
select @c,@d&

4、存储过程例

#1、创建一个存储过程将日期格式化为 X年X月X日
create procedure date_demo(in date datetime,out str varchar(50))
begin 
    select date_format(date,'%y年%m月%d日') into str;
end&

call date_demo(now(),@Str)&
select @Str&

# 2、创建一个存储过程 输入 r_archive_id 输出  r_archive_id and r_type
如果没有对应的输出 r_archive_id null

create procedure find(in id bigint,out str varchar(30))
begin
    declare s varchar(20) default '';
    set s = (select r_type from rfid_archive where r_archive_id =id);
    set str = concat(id,'and',ifnull(s,'null'));
end&
call find(1,@s)

存储函数

1、语法

1.1）创建语法

#一、创建语法
create function 函数名(参数) returns 返回类型
begin
    函数体
end
/*
注意：
1、参数列表 包含两部分
参数名  参数类型

2、函数体: 肯定会有return语句，如果没有会报错。如果return语句没有放在函数最后不报错，但是不建议

3、函数体中仅有一句话，则可以省略begin  end
4、使用 delimiter 语句设置结束标记

1.2)调用语法

select 函数名(参数列表)

1.3）查看与销毁

#查看创建的方法定义 
show create function 方法名
#删除创建的方法
drop function 方法名

2 案例

#无参有返回
#1  返回表内有几条数据
delimiter &
create function myf1() returns int
begin
    declare sum int default 0;  #定义一个参数
    select count(*) into sum
    from rfid_archive;
    return sum;
end
select myf1()&
#有参有返回
#1  通过 r_type 查找对应的 r_state
delimiter &
create function myf2(type varchar(100)) returns varchar(100)
begin
    set @s = 'a';
    select r_state into @s  #由于一个r_type可能对应多个r_state会报错
    from rfid_archive
    where r_type = type;
    return @s;
end&
# 传入两个值，返回两个值的和
delimiter &
create function myf3(var1 float,var2 float) returns float
begin
declare re float default 0;
set re = var1+var2;
return re;
end&
select myf3(1.2,3)&

流程控制结构

1 分支结构

1.1） if函数

功能：实现简单的双分支

语法：

if(表达式1，表达式2，表达式3）

执行顺序：

如果表达式1成功，返回表达式2的值，否则返回表达式3的值

mysql> select if(1>2,'1大','2大')&
+-----------------------+
| if(1>2,'1大','2大')   |
+-----------------------+
| 2大                   |
+-----------------------+

1.2) case结构

注意：

1、如果返回的是值，可以在任意地方使用，而如果返回的是语句只能在begin,end之中使用

2、else 可以省略，如果省略没有匹配值返回null

情况1：类型java中的switch语句，一般用于实现等值判断

语法：
    case  变量|表达式|字段
    when 要判断的值1 then  返回的值1或语句1；
    when 要判断的值2 then  返回的值2或语句2；
    else 要返回的值n或语句n;
    end case;

情况2

语法：
    case 
    when 要判断的条件1 then  返回的值1或语句1；
    when 要判断的条件2 then  返回的值2或语句2；
    else 要返回的值n或语句n;
    end case;

：类似于java中的多重if语句，一般用于实现区间判断

# 判断值如果为1 返回值10，否则返回值20
delimiter &
set @a = 1&
select 
    case @a
    when 1 then '10'   #注意如果返回值，不加 (;)结束，只在end后添加(;)
    else 20
    end;
&

#创建存储过程，根据传入的成功显示等级，超过90显示A，超过60显示B,否则c
create procedure test_ca1(in score int)
begin
    case
    when score >= 90 and score <= 100 then select 'A';select 'b';
    when score >= 60 then select 'B';
    else select 'C';
    end case;
end&
mysql> call test_ca1(90)&
+---+
| A |
+---+
| A |
+---+

#当r_type为‘11'时显示r_state.否则不显示r_state值
delimiter &

select r_type,
case r_type
when '11' then r_state
end as s
from rfid_archive
&

1.3）if语句

#3.if结构
功能：实现多重分支
语法：只能应用在begin-end中
if 条件1 then 语句1；
elseif 条件2 then 语句2；
[else 语句n]
end if;

#根据传入的值，返回等级 如 90-100 返回 A,80-90返回B，60-80返回c，否则返回d
delimiter &
create function myf1(score int) returns char
begin
	declare res char default 'q';
	if score >= 90 then set res = 'a';
	elseif score >= 80 then set res = 'b';
	elseif score >=60 then set res = 'c';
	else set res = 'd';
	end if;
	return res;
end &
select myf1(80) &

2 循环结构

分类： while (类似while(){})、loop(类似while(true){})、repeat(类似do{}while())

循环控制： iterate 类型于continue,结束本次循环，继续 leave类似于 break, 跳出，结束当前所在的循环

注意：必须放在begin,end内

2.1） while

语法：

[标签:] while 循环条件 do

循环体

end while [标签];

#通过while往数据库插入数据
delimiter &
drop procedure myp11 &
create procedure myp11(in count int)
begin
	declare temp int default 0;
	while temp <= count do
		insert into rfid_archive(r_type,r_state) value (concat(temp,"rtype"),concat(temp,"rstae"));
		set temp = temp+1;
	end while;
end &
call myp11(10)

#住 rfid_archive 插入n条数据,如果rfid_archive量超过20条，不执行插入
delimiter &
create procedure myp12(in count int)
begin
	declare temp int default 0;
	declare t int default 0;
	a:while temp <= count do
		select count(*) into t from rfid_archive;
		if t > 20 then leave a; end if;
		insert into rfid_archive(r_type,r_state) value (concat(temp,"rtype"),concat(temp,"rstae"));
		set temp = temp+1;
	end while a;
end &
call myp12(10)

2.2）loop

语法：

[标签:] loop

循环体;

end loop [标签];

可以用来模拟简单的死循环

#执行三次 select
delimiter &
create procedure myp2()
begin
	declare i int default 0;
	a:loop
		select * from rfid_archive;
		if i >= 2 then leave a; end if;  #注意使用 if 一定要带 end if
		set i = i+1;
	end loop a;
end &
call myp2();

2.3）repeat

语法:

[标签:] repeat

循环体;

until 结束循环的条件 #注意后边没有分号

end repeat [标签]

#往数据库插入10条数据v
delimiter &
create procedure myp1(in count int)
begin
	declare i int default 0;
	repeat 
		insert into rfid_archive(r_state,r_type) value (concat(i,'ss'),concat(i,'aa'));
		set i = i+1;
	until i > count end repeat; #注意until后面是结束的条件
end &
call myp1(10)

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
最简单的mysql存储过程和函数

存储过程1，什么是存储过程存储过程指一组预先编译好的sql语句的集合，理解成批处理语句。提高代码的重用性简化操作减小了编译次数并且减少了和数据库服务器的连接次数，提高了效率2、语法2.1）创建存储过程create procedure 存储过程名(参数列表)begin 存储过程体（一组合法的sql语句）end注意：1、参数列表包含三部分参数模式参数名参数类型举例： in stuname varchar(20)参数模式： in：该参
复制链接

扫一扫

专栏目录

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。