【MySQL】存储过程和存储函数

存储函数

基本概念

存储过程是一组为了完成某项特定功能的sql语句集,实质上就是一段存储在数据库中的代码

可由声明式的SQL语句和过程式SQL语句组成
优点:

  1. 提高系统性能
  2. 增强SQL的功能和灵活性
  3. 降低网络的通信量
  4. 良好的封装性
  5. 作为一种安全机制确保了数据库的安全性和数据的完整性

创建存储过程

create procedure sp_name ([in|out|inout] 参数名 参数类型) 存储过程体
MySQL存储过程支持三种类型的参数:输入参数、输出参数、输入/输出参数(IN、OUT、INOUT)

  1. 输入:使数据可以传递给一个存储过程
  2. 输出:用于存储过程需要返回一个操作结果的情况
  3. 输入/输出:既可以充当输入参数,也可以充当输出参数

delimiter语法格式:
delimiter $$ 修改结束符号为 $$ 【可自定义修改】
避免使用反斜杠\ 因为它是MYSQL的转义字符


create procedure my_procedure()
begin
	-- 存储过程体
end

在navicat中创建:指定库–>函数–>选择’过程‘

存储过程体

以关键字begin开始,关键字end结束,在存储过程体中,begin...and复合语句可以嵌套使用,当存储过程体中只有一条sql语句时,关键字可以省略。

局部变量:可以用于存储临时的结果、必须使用declare语句,在声明局部变量的同时也可以对其赋一个初始值

declare语法:declare var_name[...] type [default value]

val_name为变量名;
type:变量类型;
default语句赋予默认值,不指定则为null

注意:局部变量只能在存储过程体中声明,
局部变量必须在存储过程体的开头声明;
使用范围仅限于存储过程体内
局部变量不是用户变量,局部变量声明时不需要加@符,且仅适用于数据存储体内;用户变量声明时需要加@符,存在于整个会话中。

set语句:给局部变量赋值可以使用set语句
set 局部变量名= expr [, var_name = expr]

set和declare的区别:
declare用于声明局部变量,只作用于存储过程或存储函数。
set用于给变量赋值,它可以用于给已经声明的变量赋值,也可以用于给存储过程或函数中的参数赋值。

select…into语句:可以把选定的列值直接存储到变量当中,返回的结果只能有一行
select 指定列名[,...] into 局部变量名 from t_name where 条件

流程控制语句

  1. 条件判断语句:if语句:if-then-else、case语句
  2. 循环语句:while(先判断)、repeat(后判断)、loop

游标:【可想象为一种数据集合】
游标是被select语句检索出来的结果集

  1. 声明游标:declare y_name cursor for 查询语句
  2. 打开游标:open y_name,一个游标可以打开多次,由于数据表可能会更新,所以每次打开游标得到的结果集可能不同
  3. 读取数据:fetch y_name into 变量名
  4. 关闭游标:close y_name

注意事项:
游标只用于存储过程或存储函数中,不能单独在查询操作中使用
在存储过程或存储函数中可以定义多个游标,但是在一个存储过程体中,游标的名字必须是唯一的
游标是select的结果集

调用和删除:

调用存储过程:
call 存储过程名([调用存储过程需要的参数])
删除存储过程:
drop procedure 存储过程名


  1. 创建一个计算年龄的存储过程,并调用
CREATE  PROCEDURE `my_procedure`(
in birth int, -- 输入参数
out olds int -- 输出参数
)
begin
	set olds = YEAR(CURDATE())-birth;
end

-- 调用
set @birth = 2004;
call my_procedure(@birth,@olds);
select @olds;
-- 结果 19
  1. 查询职工表出生年分,并更新年龄
CREATE DEFINER=`root`@`localhost` PROCEDURE `my_procedure`()
begin
-- 声明变量
	DECLARE done INT DEFAULT FALSE;
  DECLARE yea int;
	declare age int;
-- 声明游标
	DECLARE birthday cursor for 
		select birth from employee;
-- 判断游标是否结束
	DECLARE continue HANDLER for not found set done = True;
	-- 打开游标
	open birthday;
	-- loop循环
	read_loop:loop
		fetch birthday into yea;
		if done then
			leave read_loop;
		end if;
		set age = YEAR(CURDATE())-yea;
		update employee set age=age where birth=yea;
	end loop;
	close birthday;
end

在这里插入图片描述


存储函数

  1. 创建存储函数create function(参数名称, 参数类型) returns 类型 存储函数体
    returns子句:声明存储函数返回值的数据类型
    存储函数体中必须包含一个 return value语句

  2. 调用存储函数:直接select 函数名(输入参数)

  3. 查看已有的存储函数:show function status

  4. 删除存储函数:drop function f_nam

存储过程和存储函数的区别

存储函数和存储过程都是由sql语句和过程式语句所组成的代码片段。

存储函数和存储过程的区别:

  1. 存储函数不能拥有输出参数,存储函数自身就是输出参数;存储过程可以拥有输出参数。
  2. 存储函数可以直接调用,不用使用call语句;存储过程的调用需要使用call语句。
  3. 存储函数中必须包含一条return语句;存储过程中不能出现这条语句。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
好的,以下是一个简单的 MySQL 存储过程存储函数的实验。 首先,创建一个测试表 test_table,包含两个字段 id 和 name: ```sql CREATE TABLE test_table ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, PRIMARY KEY (id) ); ``` 接着,我们创建一个存储过程,用于向 test_table 中插入数据: ```sql DELIMITER // CREATE PROCEDURE insert_data() BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= 10) DO INSERT INTO test_table (name) VALUES (CONCAT('Name ', i)); SET i = i + 1; END WHILE; END // DELIMITER ; ``` 该存储过程名为 insert_data,它使用了一个 WHILE 循环来向 test_table 中插入 10 条数据,每个数据的 name 字段都是 'Name ' 加上当前循环次数 i。 接下来,我们创建一个存储函数,用于统计 test_table 中数据的数量: ```sql DELIMITER // CREATE FUNCTION count_data() RETURNS INT BEGIN DECLARE count INT; SELECT COUNT(*) INTO count FROM test_table; RETURN count; END // DELIMITER ; ``` 该存储函数名为 count_data,它使用了一个 SELECT COUNT(*) 查询来统计 test_table 中数据的数量,并将结果赋值给 count 变量,最终返回 count。 现在,我们可以分别调用 insert_data 存储过程和 count_data 存储函数来测试它们的效果: ```sql CALL insert_data(); SELECT count_data(); ``` 执行后,我们可以看到 test_table 中已经插入了 10 条数据,同 count_data 函数返回的结果也是 10。 这就是一个简单的 MySQL 存储过程存储函数的实验。当然,实际使用中还有很多更复杂的情况需要考虑和处理。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值