Step 1:create three tables 1.1 create table bms_so_mapping_bak use test CREATE TABLE `bms_so_mapping_bak` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `wh_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `ownerkey` varchar(30) COLLATE utf8_bin DEFAULT 'a', `sono` varchar(30) COLLATE utf8_bin DEFAULT 'a', `solineno` varchar(30) COLLATE utf8_bin DEFAULT 'a', `so_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `ship_method` varchar(30) COLLATE utf8_bin DEFAULT 'a', `cust_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `externalsono` varchar(30) COLLATE utf8_bin DEFAULT 'a', `purchase_group` varchar(30) COLLATE utf8_bin DEFAULT 'a', `updatedt` datetime DEFAULT NULL, `effective_date` datetime DEFAULT NULL, `request_ship_date` datetime DEFAULT NULL, `make_order_date` datetime DEFAULT NULL, `post_date` datetime DEFAULT NULL, `reserve_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `plate_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `load_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `promise_hour_am` int(11) DEFAULT 0, `promise_hour_pm` int(11) DEFAULT 0, `ship_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `ship_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `ship_name` varchar(60) COLLATE utf8_bin DEFAULT 'a', `rec_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `rec_code` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT 'a', `rec_name` varchar(60) COLLATE utf8_bin DEFAULT 'a', `rec_address` varchar(200) COLLATE utf8_bin DEFAULT 'a', `rec_contact` varchar(200) COLLATE utf8_bin DEFAULT 'a', `cust_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `cust_name` varchar(100) COLLATE utf8_bin DEFAULT 'a', `linked_order_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `is_ships` varchar(10) COLLATE utf8_bin DEFAULT 'a', `memo` varchar(100) COLLATE utf8_bin DEFAULT 'a', `sku` varchar(30) COLLATE utf8_bin DEFAULT 'a', `sku_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `uom` varchar(30) COLLATE utf8_bin DEFAULT 'a', `shipqty` decimal(22,6) DEFAULT 0, `weight_uom` varchar(30) COLLATE utf8_bin DEFAULT 'a', `volume_uom` varchar(30) COLLATE utf8_bin DEFAULT 'a', `sap_ship_area` varchar(30) COLLATE utf8_bin DEFAULT 'a', `trigger_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `insert_dt` datetime DEFAULT NULL, `read_dt` datetime DEFAULT NULL, `status` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT 'a', `run_status` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT 'a', `log` varchar(1000) COLLATE utf8_bin DEFAULT 'a', `source` varchar(32) COLLATE utf8_bin DEFAULT 'a', `delete_dt` datetime DEFAULT NULL, `delete_id` varchar(32) COLLATE utf8_bin DEFAULT 'a', PRIMARY KEY (`id`), KEY `index_bms_so_mapping_wh_code` (`wh_code`), KEY `index_bms_so_mapping_sono` (`sono`) ) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 1.2 create table bms_order_header_bak CREATE TABLE `bms_order_header_bak` ( `order_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `order_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `bill_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wh_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `owner_key` varchar(30) COLLATE utf8_bin DEFAULT 'a', `sap_wh_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `settle_wh_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `vendor_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wms_order_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `sap_order_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `sap_doc_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wms_type_attr` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wms_order_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wms_order_subtype` varchar(30) COLLATE utf8_bin DEFAULT 'a', `sap_move_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `trigger_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wms_last_update_dt` datetime DEFAULT NULL, `wms_issue_dt` datetime DEFAULT NULL, `wms_request_ship_dt` datetime DEFAULT NULL, `wms_create_dt` datetime DEFAULT NULL, `wms_post_dt` datetime DEFAULT NULL, `do_post_dt` datetime DEFAULT NULL, `do_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wms_reserve_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wms_load_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `wms_plate_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `pur_group_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `promise_hour_am` int(11) DEFAULT 0, `promise_hour_pm` int(11) DEFAULT 0, `biz_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `ship_method` varchar(30) COLLATE utf8_bin DEFAULT 'a', `pickup_dt` datetime DEFAULT NULL, `expected_arrival_dt` datetime DEFAULT NULL, `promised_arrival_dt` datetime DEFAULT NULL, `expected_receipt_dt` datetime DEFAULT NULL, `actual_receipt_dt` datetime DEFAULT NULL, `actual_arrive_dt` datetime DEFAULT NULL, `pickup_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `pickup_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `pickup_name` varchar(60) COLLATE utf8_bin DEFAULT 'a', `shipto_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `shipto_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `shipto_name` varchar(60) COLLATE utf8_bin DEFAULT 'a', `shipto_address` varchar(200) COLLATE utf8_bin DEFAULT 'a', `shipto_contacts` varchar(200) COLLATE utf8_bin DEFAULT 'a', `area_code1` varchar(30) COLLATE utf8_bin DEFAULT 'a', `area_code2` varchar(30) COLLATE utf8_bin DEFAULT 'a', `area_code3` varchar(30) COLLATE utf8_bin DEFAULT 'a', `area_code4` varchar(30) COLLATE utf8_bin DEFAULT 'a', `cust_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `cust_id` varchar(30) COLLATE utf8_bin DEFAULT 'a', `cust_name` varchar(60) COLLATE utf8_bin DEFAULT 'a', `ref_order_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `source` varchar(30) COLLATE utf8_bin DEFAULT 'a', `collect_dt` datetime DEFAULT NULL, `partial_rec_status` varchar(1) COLLATE utf8_bin DEFAULT 'a', `partial_ship_status` varchar(1) COLLATE utf8_bin DEFAULT 'a', `is_fee` varchar(1) COLLATE utf8_bin DEFAULT 'a', `is_exception` varchar(1) COLLATE utf8_bin DEFAULT 'a', `is_kpi` varchar(1) COLLATE utf8_bin DEFAULT 'a', `is_onroad` varchar(1) COLLATE utf8_bin DEFAULT 'a', `is_transit` varchar(1) COLLATE utf8_bin DEFAULT 'a', `is_arrive_dt` varchar(1) COLLATE utf8_bin DEFAULT 'a', `is_reversal` varchar(2) COLLATE utf8_bin DEFAULT 'a', `STATUS` varchar(2) COLLATE utf8_bin DEFAULT 'a', `exception_status` varchar(2) COLLATE utf8_bin DEFAULT 'a', `onroad_status` varchar(2) COLLATE utf8_bin DEFAULT 'a', `bill_back_status` varchar(2) COLLATE utf8_bin DEFAULT 'a', `settle_status` varchar(2) COLLATE utf8_bin DEFAULT 'a', `settle_no` varchar(30) COLLATE utf8_bin DEFAULT 'a', `invoice_status` varchar(2) COLLATE utf8_bin DEFAULT 'a', `is_org_paper` varchar(1) COLLATE utf8_bin DEFAULT 'a', `delay_month` int(11) DEFAULT 0, `comments` varchar(500) COLLATE utf8_bin DEFAULT 'a', `create_id` varchar(32) COLLATE utf8_bin DEFAULT 'a', `create_dt` datetime DEFAULT NULL, `update_id` varchar(32) COLLATE utf8_bin DEFAULT 'a', `update_dt` datetime DEFAULT NULL, `delete_id` varchar(32) COLLATE utf8_bin DEFAULT 'a', `delete_dt` datetime DEFAULT NULL, `ext_ref1` varchar(100) COLLATE utf8_bin DEFAULT 'a', `ext_ref2` varchar(100) COLLATE utf8_bin DEFAULT 'a', `ext_ref3` varchar(100) COLLATE utf8_bin DEFAULT 'a', `ext_ref4` varchar(100) COLLATE utf8_bin DEFAULT 'a', `ext_ref5` varchar(100) COLLATE utf8_bin DEFAULT 'a', `min_id` bigint(20) DEFAULT 0, `type_rule_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `is_wms_order` varchar(2) COLLATE utf8_bin DEFAULT 'a', `is_tms_order` varchar(2) COLLATE utf8_bin DEFAULT 'a', `is_freight` varchar(1) COLLATE utf8_bin DEFAULT 'a', `is_wh_fee` varchar(1) COLLATE utf8_bin DEFAULT 'a', `biz_trans_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `biz_wh_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `pickup_rule_code` varchar(30) COLLATE utf8_bin DEFAULT 'a', `ref_date_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `pickup_date_col` varchar(30) COLLATE utf8_bin DEFAULT 'a', `pickup_date_col_type` varchar(30) COLLATE utf8_bin DEFAULT 'a', `ref_date` datetime DEFAULT NULL, `add_hour` int(11) DEFAULT NULL, `pickup_close` time DEFAULT NULL, `am_or_pm` varchar(30) COLLATE utf8_bin DEFAULT 'a', `is_calendar` varchar(1) COLLATE utf8_bin DEFAULT 'a', `ctrl_status` varchar(1) COLLATE utf8_bin DEFAULT 'a', KEY `i_bms_order_header_so_tmp5` (`ctrl_status`), KEY `i_bms_order_header_so_tmp1` (`order_type`), KEY `i_bms_order_header_so_tmp2` (`is_tms_order`), KEY `i_bms_order_header_so_tmp3` (`pickup_date_col`), KEY `i_bms_order_header_so_tmp4` (`pickup_date_col_type`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 1.3 create table plat_odometer CREATE TABLE `plat_odometer` ( `no_name` varchar(50) COLLATE gbk_bin NOT NULL, `no_count` bigint(18) NOT NULL DEFAULT '0', PRIMARY KEY (`no_name`) ) ENGINE=ndbcluster DEFAULT CHARSET=gbk COLLATE=gbk_bin; insert into plat_odometer values ('bms_so_key',1); STEP 2 CREATE PROCEDURE 2.1 CREATE PROCEDURE proc_data_1 DROP PROCEDURE IF EXISTS test.proc_data_1; delimiter // CREATE PROCEDURE test.proc_data_1(loop_time int) BEGIN DECLARE var INT DEFAULT 1; DECLARE i int default 0; WHILE i < loop_time DO INSERT INTO bms_so_mapping_bak (wh_code) VALUES ('a'); SET i = i + 1; END WHILE; END; // delimiter ; 2.2 CREATE PROCEDURE proc_data_2 DROP PROCEDURE IF EXISTS test.proc_data_2; delimiter // CREATE PROCEDURE test.proc_data_2(loop_time int) BEGIN DECLARE var INT DEFAULT 1; DECLARE i int default 0; WHILE i < loop_time DO INSERT INTO bms_order_header_bak (order_no) VALUES ('a'); SET i = i + 1; END WHILE; END; // delimiter ; STEP 2 FILL DATA call test.proc_data_1(80000); call test.proc_data_2(3000); STEP 3 CREATE FUNCTION set global log_bin_trust_function_creators=1; DROP FUNCTION IF EXISTS test.f_bms_odometers_no; delimiter // CREATE FUNCTION test.`f_bms_odometers_no`(odometer_code VARCHAR (100) , prefix VARCHAR (20)) RETURNS varchar(255) CHARSET utf8 COLLATE utf8_bin BEGIN DECLARE str VARCHAR(255) DEFAULT ''; DECLARE no_count_id INT DEFAULT -1; SELECT no_count INTO no_count_id FROM plat_odometer WHERE no_name = 'odometer_code'; IF (no_count_id >= 0) THEN SET no_count_id = no_count_id + 1; SET str= CONCAT(prefix ,DATE_FORMAT(NOW(),'%Y%m%d'), RIGHT(CONCAT('0000000000',no_count_id),10)); END IF; RETURN str; END; // delimiter ; STEP 4 EXECUTE THE SQL STATEMENT SELECT oh.order_no,f_bms_odometers_no('bms_so_key','SODETAIL'), sm.sono,sm.solineno,sm.sku,sm.shipqty,sm.sap_ship_area,'SYSTEM',SYSDATE() FROM bms_so_mapping_bak sm INNER JOIN bms_order_header_bak oh ON sm.wh_code = oh.wh_code AND sm.sono=oh.wms_order_no;