GBase 8a
其他
问答
存储过程中循环语句内使用with语句问题
发表于2023-08-01 21:58:32655次浏览12个评论
为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【GBase版本】: 8.6.2.43-R27.121364
【操作系统】:
【CPU】:
【问题描述】*:存储过程中使用了循环语句（测试了while do和loop），循环体内部一个insert语句使用了with的写法，发现每次循环时with语句未重新执行，一直是首次循环的结果，去掉with写法改为普通嵌套SQL发现子语句每次循环能够重新执行。该问题是不支持还是已在后续版本修复的bug？
评论
登录后才可以发表评论
GBase社区管理员发表于 3年前
74581发表于 3年前
@社区管理员：_t_gcluster_support_cte参数值在发现问题前已为1，存储过程正常运行无报错，数据加工未按照业务需求实现
74581发表于 3年前
@社区管理员：出现存储过程中循环语句内使用with语句未重跑问题的存储过程示例：
DROP Procedure `test_db`.`p_test_table` ;
DELIMITER //
create definer="gbase"@"%" procedure "p_test_table"(out p_result_o int,
out p_message_o varchar,
in p_period_i varchar,
in p_jobid_i varchar,
in p_taskid_i varchar)
begin
declare v_state varchar(50) default '';
declare v_errno integer default 0;
declare v_errmessage varchar(2000);
declare p_beg_date datetime default to_date(p_period_i, 'yyyymmdd');
declare p_end_date datetime default to_date(p_period_i, 'yyyymmdd');
declare v_day varchar(10) default p_period_i;
declare v_count integer default 0;
declare exit handler for sqlexception
begin
get diagnostics condition 1 v_state=returned_sqlstate, v_errno=gbase_errno, v_errmessage=message_text;
set p_message_o = concat(p_message_o, 'ERROR:' || v_errno || '++' || v_state || '++', v_errmessage);
set p_result_o = 1;
call p_pub_writedetaillog(p_period_i, p_taskid_i, p_jobid_i, p_message_o);
select p_result_o, p_message_o;
end;
set p_result_o = 0;
select min(d_biz),max(d_biz) into p_beg_date, p_end_date from dc_ods.test_table_from t where t.load_period = p_period_i;
drop temporary table if exists test_pub_1;
create temporary table test_pub_1 as
select distinct a1.test_day from test_pub a1
where 1=1
and to_date(a1.test_day,'yyyymmdd') >= p_beg_date
and to_date(a1.test_day,'yyyymmdd') <= p_end_date;
select count(*) into v_count from test_pub_1;
while v_count > 0 do
select test_day into v_day from test_pub_1 order by test_day limit 1;
delete from test_table t where t.business_date = v_day;
insert into test_table (business_date, aaa)
with tmp as
(select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
)
select a2.business_date, aaa
from (select nvl(a1.business_date, b1.business_date) as business_date, a1.aaa
from (select * from tmp t) a1
full join (select * from tmp t) b1 on b1.business_date = a1.business_date
) a2;
delete from test_pub_1 t where t.test_day = v_day;
select count(0) into v_count from test_pub_1;
end while;
drop temporary table if exists test_pub_1;
END //
DROP Procedure `test_db`.`p_test_table` ;
DELIMITER //
create definer="gbase"@"%" procedure "p_test_table"(out p_result_o int,
out p_message_o varchar,
in p_period_i varchar,
in p_jobid_i varchar,
in p_taskid_i varchar)
begin
declare v_state varchar(50) default '';
declare v_errno integer default 0;
declare v_errmessage varchar(2000);
declare p_beg_date datetime default to_date(p_period_i, 'yyyymmdd');
declare p_end_date datetime default to_date(p_period_i, 'yyyymmdd');
declare v_day varchar(10) default p_period_i;
declare v_count integer default 0;
declare exit handler for sqlexception
begin
get diagnostics condition 1 v_state=returned_sqlstate, v_errno=gbase_errno, v_errmessage=message_text;
set p_message_o = concat(p_message_o, 'ERROR:' || v_errno || '++' || v_state || '++', v_errmessage);
set p_result_o = 1;
call p_pub_writedetaillog(p_period_i, p_taskid_i, p_jobid_i, p_message_o);
select p_result_o, p_message_o;
end;
set p_result_o = 0;
select min(d_biz),max(d_biz) into p_beg_date, p_end_date from dc_ods.test_table_from t where t.load_period = p_period_i;
drop temporary table if exists test_pub_1;
create temporary table test_pub_1 as
select distinct a1.test_day from test_pub a1
where 1=1
and to_date(a1.test_day,'yyyymmdd') >= p_beg_date
and to_date(a1.test_day,'yyyymmdd') <= p_end_date;
select count(*) into v_count from test_pub_1;
while v_count > 0 do
select test_day into v_day from test_pub_1 order by test_day limit 1;
delete from test_table t where t.business_date = v_day;
insert into test_table (business_date, aaa)
with tmp as
(select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
)
select a2.business_date, aaa
from (select nvl(a1.business_date, b1.business_date) as business_date, a1.aaa
from (select * from tmp t) a1
full join (select * from tmp t) b1 on b1.business_date = a1.business_date
) a2;
delete from test_pub_1 t where t.test_day = v_day;
select count(0) into v_count from test_pub_1;
end while;
drop temporary table if exists test_pub_1;
END //
74581发表于 3年前
@社区管理员：正常实现业务需求的存储过程示例，去掉with写法改为普通嵌套SQL：
DROP Procedure `test_db`.`p_test_table` ;
DELIMITER //
create definer="gbase"@"%" procedure "p_test_table"(out p_result_o int,
out p_message_o varchar,
in p_period_i varchar,
in p_jobid_i varchar,
in p_taskid_i varchar)
begin
declare v_state varchar(50) default '';
declare v_errno integer default 0;
declare v_errmessage varchar(2000);
declare p_beg_date datetime default to_date(p_period_i, 'yyyymmdd');
declare p_end_date datetime default to_date(p_period_i, 'yyyymmdd');
declare v_day varchar(10) default p_period_i;
declare v_count integer default 0;
declare exit handler for sqlexception
begin
get diagnostics condition 1 v_state=returned_sqlstate, v_errno=gbase_errno, v_errmessage=message_text;
set p_message_o = concat(p_message_o, 'ERROR:' || v_errno || '++' || v_state || '++', v_errmessage);
set p_result_o = 1;
call p_pub_writedetaillog(p_period_i, p_taskid_i, p_jobid_i, p_message_o);
select p_result_o, p_message_o;
end;
set p_result_o = 0;
select min(d_biz),max(d_biz) into p_beg_date, p_end_date from dc_ods.test_table_from t where t.load_period = p_period_i;
drop temporary table if exists test_pub_1;
create temporary table test_pub_1 as
select distinct a1.test_day from test_pub a1
where 1=1
and to_date(a1.test_day,'yyyymmdd') >= p_beg_date
and to_date(a1.test_day,'yyyymmdd') <= p_end_date;
select count(*) into v_count from test_pub_1;
while v_count > 0 do
select test_day into v_day from test_pub_1 order by test_day limit 1;
delete from test_table t where t.business_date = v_day;
insert into test_table (business_date, aaa)
/*with tmp as
(select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
)*/
select a2.business_date, aaa
from (select nvl(a1.business_date, b1.business_date) as business_date, a1.aaa
from (select *
from (select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
) t) a1
full join (select *
from (select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
) t) b1 on b1.business_date = a1.business_date
) a2;
delete from test_pub_1 t where t.test_day = v_day;
select count(0) into v_count from test_pub_1;
end while;
drop temporary table if exists test_pub_1;
END //
DROP Procedure `test_db`.`p_test_table` ;
DELIMITER //
create definer="gbase"@"%" procedure "p_test_table"(out p_result_o int,
out p_message_o varchar,
in p_period_i varchar,
in p_jobid_i varchar,
in p_taskid_i varchar)
begin
declare v_state varchar(50) default '';
declare v_errno integer default 0;
declare v_errmessage varchar(2000);
declare p_beg_date datetime default to_date(p_period_i, 'yyyymmdd');
declare p_end_date datetime default to_date(p_period_i, 'yyyymmdd');
declare v_day varchar(10) default p_period_i;
declare v_count integer default 0;
declare exit handler for sqlexception
begin
get diagnostics condition 1 v_state=returned_sqlstate, v_errno=gbase_errno, v_errmessage=message_text;
set p_message_o = concat(p_message_o, 'ERROR:' || v_errno || '++' || v_state || '++', v_errmessage);
set p_result_o = 1;
call p_pub_writedetaillog(p_period_i, p_taskid_i, p_jobid_i, p_message_o);
select p_result_o, p_message_o;
end;
set p_result_o = 0;
select min(d_biz),max(d_biz) into p_beg_date, p_end_date from dc_ods.test_table_from t where t.load_period = p_period_i;
drop temporary table if exists test_pub_1;
create temporary table test_pub_1 as
select distinct a1.test_day from test_pub a1
where 1=1
and to_date(a1.test_day,'yyyymmdd') >= p_beg_date
and to_date(a1.test_day,'yyyymmdd') <= p_end_date;
select count(*) into v_count from test_pub_1;
while v_count > 0 do
select test_day into v_day from test_pub_1 order by test_day limit 1;
delete from test_table t where t.business_date = v_day;
insert into test_table (business_date, aaa)
/*with tmp as
(select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
)*/
select a2.business_date, aaa
from (select nvl(a1.business_date, b1.business_date) as business_date, a1.aaa
from (select *
from (select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
) t) a1
full join (select *
from (select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
) t) b1 on b1.business_date = a1.business_date
) a2;
delete from test_pub_1 t where t.test_day = v_day;
select count(0) into v_count from test_pub_1;
end while;
drop temporary table if exists test_pub_1;
END //
一头猪发表于 2年前
with tmp as
(select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
)
可以把这个SQL语句，当字符串输出看看，是不是每次都一样。
(select to_char(a.d_biz, 'yyyymmdd') as business_date, aaa
from dc_ods.test_table_from a
where a.d_biz = to_date(v_day,'yyyymmdd')
and a.load_period = p_period_i
)
可以把这个SQL语句，当字符串输出看看，是不是每次都一样。
GBase社区管理员发表于 2年前
您好，经确认该问题确实存在，我们会在后续版本中尽快修复此问题，非常感谢您的反馈。
最佳回答
嗷呜一口仙贝发表于 9个月前
学习一下
GBase用户31099发表于 8个月前
学习一下
崔哥发表于 3个月前
春日游，杏花吹满头。陌上谁家年少足风流？妾拟将身嫁与一生休。纵被无情弃，不能羞。
A杰发表于 1个月前
这问题不错，认真学习中。
热门帖子
- 12025-12-01浏览数：182764
- 22023-05-09浏览数：25062
- 42023-09-25浏览数：18526
- 52020-05-11浏览数：17529
使用cte需要开启_t_gcluster_support_cte参数值为1，您有更详细的报错信息或者其余补充信息也请尽快补充，谢谢