关于mysql存储过程中N/A和null的使用注意事项

发布时间:2023年12月18日

??oracle和mysql的存储过程大同小异,但是一些细节还是需要留意的。最近发现mysql的N/A和null在存储过程中容易忽略的一点,这会导致我们的存储过程提前结束。今天突然想起来了就记录一下。
??mysql的N/A和null区别网上也说得很详细了,我就不赘述了,只要知道mysql中查不到某条记录的时候,数据库返回值就是N/A:
在这里插入图片描述
如果数据库中能查到某个记录,但是该字段是空的,没有数据:
在这里插入图片描述
??说回去正题,在mysql的存储过程我们经常使用declare continue handler for not found set done = 1;,然后在循环遍历开始的时候使用IF done = 1 THEN LEAVE forloop; END IF;来退出循环(这个done是自定义变量)。这样会存在一个隐患就是,在存储过程中如果任一地方的sql如果没有查到数据(应该说没有查到记录,也即sql的返回值是N/A),那么这个continue handler for not found就会触发,将done设置为1,进而导致提前退出循环。
??看一下以下存储过程的执行结果(执行结果我注释在每个语句后面了):

CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
	declare ans2 varchar(255);
	DECLARE done INT DEFAULT 0;
	
	declare continue handler for not found set done = 1;
	
	select '1',done,ans2;-- 1	0	null
	select fname into ans2 from ib_tbs_tumorreport where fname = '数据库没有fname=此的记录';
	select '2',done,ans2;-- 2	1	null
	select fname into ans2 from ib_tbs_tumorreport where fname = '数据库没有fname=此的记录';
	select '3',done,ans2;-- 3	1	null
	set ans2:='@@@';
	select '4',done,ans2;-- 4	1	@@@
	select fname into ans2 from ib_tbs_tumorreport where fname = '数据库没有fname=此的记录';
	select '5',done,ans2;-- 5	1	@@@
	set ans2:=null;
	select '6',done,ans2;-- 6	1	null

END

??因此,在遍历游标中并且使用select 字段名 into 变量名 from {表名}的时候,要注意可能出现N/A,也就是没有查到一条记录的情况,这个时候会触发not found条件处理程序(handler),导致提前结束游标遍历。所以当我们希望遍历完整个游标时,可以先使用count(*)判断是否有值,有再进行赋值操作。但是如果每次inset into前都要count(*)一下的话,要多写很多行代码(不过这样方便代码阅读),那么我们可以在任何可能出现N/A的情况使用max(),这样如果没有查到一条记录时候会返回null,不会触发not found条件处理程序

select faddrno from ipaddr where fabbr = '广东省广州市.' -- (N/A)
select max(faddrno) from ipaddr where fabbr = '广东省广州市.' -- (Null)

??上面的存储过程中还要提一点的就是,如果将N/A赋值给一个变量是不生效的,但是如果查询结果是null的话赋值是生效的

??题外话:再贴一段游标和遍历游标处理数据的代码模板(更多请看更多请看):

CREATE DEFINER=`root`@`%` PROCEDURE `p_prepareduty`({你的入参和出参数})
BEGIN
    #下面定义一些变量用来存你要从游标中取出的字段内容
	DECLARE for_i int DEFAULT 0;
	DECLARE cur_fid VARCHAR(50);
	DECLARE cur_fdate date;
	DECLARE cur_fcityno VARCHAR(20);
	DECLARE cur_foverseas VARCHAR(200);
	DECLARE cur_freason VARCHAR(50);
	DECLARE cur_fspecial text;
	DECLARE var_ftaskid VARCHAR(50);#你的其他变量
	DECLARE done INT DEFAULT 0;#用于退出LOOP循环
	
	#DECLARE最后部分是定义你的游标,可以定义静态和动态游标
	declare cur_bq cursor for 
					select fid,fdate,fcityno,foverseas,freason,fspecial from ib_tbs_prepareduty 
					where ftaskid = ls_ftaskid and fempid = ls_fempid and fifvalid = '1';#静态
	declare cur_lastbq cursor for 
					select fcityno,foverseas,freason,fspecial from ib_tbs_prepareduty 
					where fempid = ls_fempid and fifvalid = '1' and ftaskid = @parameter;#动态
					#@parameter是域变量,也就是游标的参数
	declare continue handler for not found set done = 1;

	{这里处理你的业务,并且获得你需要的@parameter的值}
	#下面演示动态游标使用
	SET @parameter= var_ftaskid;#给动态游标传参
	open cur_lastbq;#打开游标
	read_loop:LOOP#循环遍历
			fetch cur_lastbq into cur_fcityno,cur_foverseas,cur_freason,cur_fspecial;#取出你要的字段
			IF done = 1 THEN LEAVE read_loop;
			END IF;#游标遍历完后退出循环
			{你的其他操作}
	END LOOP read_loop;  
	close cur_lastbq;#关闭游标

	#下面演示静态游标使用
	open cur_bq;
	lable1:loop
			fetch cur_bq into cur_fid,cur_fdate,cur_fcityno,cur_foverseas,cur_freason,cur_fspecial;
			IF done = 1 THEN LEAVE lable1;
			END IF;
			{你的其他操作}
	end loop lable1;
	close cur_bq;
END
文章来源:https://blog.csdn.net/qq_43791377/article/details/135052655
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。