【 使用环境 】 测试环境
【 OB or 其他组件 】OB FOR MYSQL
【 使用版本 】OceanBase 3.2.4.1
【问题描述】
DELIMITER $$
DROP PROCEDURE
IF
EXISTS proc_yukaihu_nota_0$$ CREATE PROCEDURE proc_yukaihu_nota_0 (
IN V_STARTER INTEGER,
IN V_DATA_COUNT INTEGER,
IN bank_no VARCHAR ( 255 ),
IN client_type VARCHAR ( 255 ),
IN open_branch VARCHAR ( 255 ),
IN id_type VARCHAR ( 255 ),
IN seller_code VARCHAR ( 255 ),
IN branch_no VARCHAR ( 255 )
) BEGIN
SET autocommit = 0;
SET @cc = 0;
SET SESSION ob_query_timeout = 3600000000;-- 设置查询超时时间为 3600 秒（1 小时）
SET SESSION ob_trx_timeout = 3600000000;-- 设置超时时间为 3600 秒（1 小时）
SET @areaCount = 1;
SET @areaId = 0;
SET @splitCount = 16;
SET @V_STARTER = V_STARTER;
SET @V_DATA_COUNT = V_DATA_COUNT;
SET @bank_no = bank_no;
SET @client_type = client_type;
SET @open_branch = open_branch;
SET @id_type = id_type;
SET @seller_code = seller_code;
SET @branch_no = branch_no;
SET @j = 0;
WHILE
@j < @V_DATA_COUNT DO
SET @i = @V_STARTER + @j;
IF
(( @areaCount = 1 AND @areaId = 0 ) OR @i % @areaCount + 1 = @areaId ) THEN
SET @trans_account_type = 1;
SET @bank_acc = concat(
'1',
lpad( @i, 17, '0' ));
SET @ta_client = concat(
'4',
lpad( @i, 16, '0' ));
SET @in_client_no = concat(
'3',
lpad( @i, 17, '0' ));
SET @id_code = concat(
'5',
lpad( @i, 17, '0' ));
SET @client_name = concat(
'user',
lpad( @i, 8, '0' ));
SET @client_no = concat(
'2',
lpad( @i, 11, '0' ));
SET @mobile = concat(
'130',
lpad( @i, 8, '0' ));
SET @trans_account = concat(
'2',
lpad( @i, 11, '0' ));
IF
@areaCount > 1 THEN
SET @xh = MOD ( FLOOR( CONVERT ( RIGHT ( @i, 8 ), SIGNED )/ @areaCount ), @splitCount )+ 1;
ELSE
SET @xh = MOD ( CONVERT ( RIGHT ( @i, 8 ), SIGNED ), @splitCount )+ 1;
END IF;
SET @stmt = concat( "insert into ", concat( 'tbbankacc', @xh ), " (bank_no, bank_acc, ta_client, in_client_no, client_type, open_branch, acc_status, trans_date, signoff_date, table_num, reserve1, reserve2, reserve3, card_type) values (?, ? ,?, ?, ?,?, '0', 20190102, 0, ' ', ' ', ' ', ' ', ' ')" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt USING @bank_no,
@bank_acc,
@ta_client,
@in_client_no,
@client_type,
@open_branch;
DEALLOCATE PREPARE stmt;
SET @stmt = concat( "insert into ", concat( 'tbclient', @xh ), " (in_client_no, client_type, id_type, id_code, id_code_date, short_name, client_name, sex, birthday, inst_type, repr_name, repr_id_type, repr_id_code, repr_idvalid, acc_status, last_modify_date, modi_time, modify_info, reserve1, reserve2, reserve3, reserve4) values (?, ?, ?, ?, 20990909, 'test', ?, '1', 19910819, ' ', ' ', ' ', ' ', 0, '0', 20990101, 15013, ' ', ' ', ' ', ' ', ' ')" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt USING @in_client_no,
@client_type,
@id_type,
@id_code,
@client_name;
DEALLOCATE PREPARE stmt;
SET @stmt = concat( "insert into ", concat( 'tbclientadd', @xh ), " (in_client_no, bank_no, seller_code, marriage_status, child_condition, education, annual_income, vocation, nationality, english_name, investor_type, other_id_type_name, spv_branch, other_branch, qua_investor_type, qua_investor_date, region_code, global_area, adrsta, adrcty, adrsec, office_telno, corp_name, company_emp, tax_id, inst_reprmanagerange, reg_addr, fina_code, control_holder, shareholder_id_type, shareholder_id_code, shareholder_idvalid, reg_scale, busi_unit, comp_industry, comp_type, tax_addr, actual_controller, controller_id_type, controler_id_code, controler_idvalid, control_explain, charger_name, charger_id_type, charger_id_code, charger_idvalid, office_address, qualification_type, qualification_code, qualify_code_date, benef_id_type, benef_id_code, benef_name, beneficiary, benefit_explain, repr_sex, repr_age, repr_job_position, repr_email, repr_office_address, repr_office_post_code, repr_tel, repr_mobile, actor_id_type, actor_id_code, actor_name, actor_idvalid, actor_sex, actor_age, actor_birthday, actor_job_position, actor_email, actor_office_address, actor_office_post_code, actor_tel, actor_mobile, actor_inst_relation, check_actor_type, contact_id_type, contact_id_code, contact_name, job_position, is_control, is_bad_record, bad_record_explain, client_authcode, bank_publicity, over_draft, par_comp_name, trans_pwd, trans_pwd_status, pwd_try_times, inst_attr) values (?, ?, ?, ' ', ' ', ' ', ' ', ' ', ' ', ' ', '0', ' ', ' ', ' ', ' ', 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0.00, ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, ' ', ' ', ' ', ' ', 0, ' ', ' ', ' ', 0, ' ', ' ', ' ', ' ', ' ', ' ', 0, ' ', ' ', ' ', ' ', ' ', ' ', '7', ?, 'test', 0, ' ', 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0.00, ' ', '321', '0', 0, ' ')" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt USING @in_client_no,
@bank_no,
@seller_code,
@id_code;
DEALLOCATE PREPARE stmt;
SET @stmt = CONCAT( "insert into tbclientmap (bank_no, bank_acc, ta_client, in_client_no, client_type, client_no) values (?, ?, ?, ?, ?, ?)" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt USING @bank_no,
@bank_acc,
@ta_client,
@in_client_no,
@client_type,
@client_no;
DEALLOCATE PREPARE stmt;
SET @stmt = concat( "insert into ", concat( 'tbclientriskinfo', @xh ), " (in_client_no, client_no, bank_no, prd_type, high_risk_flag, risk_counter_flag, risk_level, risk_date, last_modify_date, risk_score, branch_no, prd_manager, reserve1, reserve2, reserve3) values (?, ?, ?, 'Z', '0', '1', 0, 20500702, 20190102, 0.00, ?, ' ', ' ', ' ', ' ')" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt USING @in_client_no,
@client_no,
@bank_no,
@branch_no;
DEALLOCATE PREPARE stmt;
SET @stmt = concat( "insert into ", concat( 'tbclientseller', @xh ), " (in_client_no, bank_no, seller_code, client_no, open_date, close_date, host_client_type, host_id_type, client_group, address, post_code, tel, fax, mobile, email, send_freq, send_mode, channels, prd_types, client_manager, open_branch, new_client_flag, msg_flag, client_from, risk_level, risk_date, last_modify_date, last_modify_time, modify_info, open_flag, acc_status, reserve1, reserve2, reserve3, reserve4) values (?, ?, ?, ?, 20190101, 0, ' ', ' ', '1', 'addressssss', '310001', '0571-87909090', '123', ?, 'aaaaa@hundsun.com', ' ', ' ', ' ', ' ', ' ', ?, ' ', ' ', ' ', 0, 20191101, 20190101, 15013, '0', ' ', '0', ' ', ' ', ' ', ' ')" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt USING @in_client_no,
@bank_no,
@seller_code,
@client_no,
@mobile,
@open_branch;
DEALLOCATE PREPARE stmt;
SET @stmt = CONCAT( "insert into tbidmap (in_client_no, id_type, id_code) values (?, ?, ?)" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt USING @in_client_no,
@id_type,
@id_code;
DEALLOCATE PREPARE stmt;
SET @stmt = concat( "insert into ", concat( 'tbtransaccount', @xh ), " (in_client_no, bank_no, client_no, trans_account_type, trans_account, reserve1) values (?, ?, ?, ?, ?, ' ')" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt USING @in_client_no,
@bank_no,
@client_no,
@trans_account_type,
@trans_account;
DEALLOCATE PREPARE stmt;
END IF;
SET @j = @j + 1;
SET @cc = @cc + 1;
SELECT 'Transaction committed!'||@cc AS Message;
IF
@cc = 100 THEN
COMMIT;
SET @cc = 0;
END IF;
END WHILE;
COMMIT;
END $$ DECLARE
EXIT HANDLER FOR SQLEXCEPTION BEGIN
ROLLBACK;
RESIGNAL;
END;
DELIMITER;
该存储过程如果去掉其中 SELECT ‘Transaction committed!’||@cc AS Message; 的语句，存储过程仅仅执行两次while循环就结束了，中间没有任何sql执行异常，然而存储过程的逻辑并不会固定执行2次就结束，如果不去掉SELECT ‘Transaction committed!’||@cc AS Message; 这个sql，存储过程就会按照我调用时上传的V_STARTER INTEGER,V_DATA_COUNT 两个参数正常按照逻辑循环。怀疑SET @cc 加上针对参数判断后执行commit的组合方式会导致存储过程中断。