关于BindDataEx接口函数 - 深圳全飞鸿

DELIMITER $
DROP PROCEDURE IF EXISTS `sfc`.`SOAP_04_BIND_DATA_EX`$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SOAP_04_BIND_DATA_EX`(
IN P_DATA VARCHAR(35),
IN P_TYPE VARCHAR(20),
IN P_Value VARCHAR(200)
)
SP:BEGIN
DECLARE res varchar(200);
DECLARE msg varchar(200);
DECLARE MO VARCHAR(25);
DECLARE C_MODEL VARCHAR(30);
DECLARE L_TYPE VARCHAR(20);
DECLARE L_VALUE VARCHAR(200);
DECLARE L_NAME VARCHAR(20);
DECLARE L_STR VARCHAR(200);
DECLARE L_GROUP varchar(25);
declare l_sn varchar(30);
DECLARE L_I INT;
DECLARE L_J INT;
DECLARE L_ROWID INT;
DECLARE L_COUNT INT;
DECLARE L_LOOP int;
DECLARE stopFlag int default 0;
DECLARE this_input CURSOR FOR
select d1,d2 from sfc.z_cursor where idd=l_rowid;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
SET msg='FAIL,SN not exists';
set res='FAIL,SN not exists';
SET L_TYPE=UPPER(TRIM(P_TYPE));
SET L_VALUE=Trim(P_VALUE);
SELECT serial_number,MO_NUMBER,MODEL_NAME,WIP_GROUP INTO L_SN, MO,C_MODEL,L_GROUP FROM SFC.R_WIP_TRACKING_T
WHERE SERIAL_NUMBER = P_DATA LIMIT 1;
if(stopFlag=1)then
SET stopFlag=0;
SELECT serial_number,MO_NUMBER,MODEL_NAME,WIP_GROUP INTO L_SN, MO,C_MODEL,L_GROUP FROM SFC.R_WIP_TRACKING_T
WHERE SHIPPING_SN = P_DATA LIMIT 1;
end if;
if(stopFlag>0)then
select res,msg from dual;
leave sp;
end if;
set L_LOOP:=0;
SET L_ROWID:=NEXTVAL('ROWID');
SET L_I:=ifnull(INSTR(L_VALUE,'='),0);
WHILE L_I>0 and l_loop<=20 DO
set l_loop:=L_loop+1;
SET L_NAME=SUBSTR(L_VALUE,1,L_I-1);
set L_value:=substr(l_value,L_I+1);
set L_STR:='';
--
SET L_J:=ifnull(INSTR(L_VALUE,','),0);
if(L_J>0)then
SET L_STR=SUBSTR(L_VALUE,1,L_J-1);
set L_value:=substr(l_value,L_J+1);
else
set L_STR=L_VALUE;
set L_VALUE='';
end if;
insert into sfc.z_cursor(IDD,d1,d2,d3,IN_TIME)values (L_ROWID,L_NAME,L_STR,'BIND_DATA',now());
--
SET L_I:=ifnull(INSTR(L_VALUE,'='),0);
END WHILE;
if(l_loop>=20)then
delete from sfc.z_cursor where idd=l_rowid;
SET msg='FAIL,DEAD LOOP IN BIND DATA EX';
set res='FAIL,DEAD LOOP IN BIND DATA EX';
select res,msg from dual;
leave sp;
end if;
set res="PASS";
set msg="PASS";
set stopFlag:=0;
OPEN this_input;
read_loop: LOOP
FETCH this_input INTO L_NAME, L_STR;
IF stopFlag=1 THEN
LEAVE read_loop;
END IF;
insert into sfc.r_wip_keyparts_undo_t
select * from sfc.r108 where serial_number=L_SN and key_part_no=L_NAME limit 2;
delete from sfc.r108 where serial_number=L_SN and key_part_no=L_NAME limit 2;
insert into r108(emp_no,serial_number,key_part_no,key_part_sn,kp_relation,group_name,carton_no,work_time,version,kp_code,mo_number)
values (L_GROUP,L_SN,L_NAME,L_STR,0,'ATE','N/A',now(),'00S0','N/A',MO);
END LOOP;
CLOSE this_input;
delete from sfc.z_cursor where idd=l_rowid;
select res,msg from dual;
END$
DELIMITER ;

复制代码