?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE DEFINER=`root`@`%` PROCEDURE `updateWorker303`()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rosterid VARCHAR(50);
  DECLARE phone VARCHAR(50);
  DECLARE workStats VARCHAR(50);
 
  DECLARE cur1 CURSOR FOR SELECT F_Id,F_Phone,F_WorkerStatus FROM ftb_personnels_staff_roster WHERE F_Id = F_UserId;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
  OPEN cur1;
 
  read_loop: LOOP
    FETCH cur1 INTO rosterid, phone,workStats;
 
    IF done THEN
      LEAVE read_loop;
    END IF;
 
 
    IF EXISTS (
      SELECT 1 FROM ftb_personnels_staff_registration_form_data
      WHERE F_RosterId = rosterid AND F_FormFieldId = 'workerStatus'
    ) THEN
      -- 更新记录
      UPDATE ftb_personnels_staff_registration_form_data SET F_Phone = phone, F_Value = workStats WHERE F_RosterId = rosterid AND F_FormFieldId = 'workerStatus';
    ELSE
      -- 插入新记录
      INSERT INTO ftb_personnels_staff_registration_form_data (F_Id, F_RosterId, F_Phone, F_FormTypeId, F_FormFieldId, F_Value)
      VALUES (rosterid, rosterid, phone, '3', 'workerStatus', workStats);
    END IF;
  END LOOP;
 
  CLOSE cur1;
END
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `updateWorker303`()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rosterid VARCHAR(50);
  DECLARE phone VARCHAR(50);
  DECLARE workStats VARCHAR(50);
 
  DECLARE cur1 CURSOR FOR SELECT F_Id,F_Phone,F_WorkerStatus FROM ftb_personnels_staff_roster WHERE F_Id = F_UserId;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
  OPEN cur1;
 
  read_loop: LOOP
    FETCH cur1 INTO rosterid, phone,workStats;
 
    IF done THEN
      LEAVE read_loop;
    END IF;
 
 
    IF EXISTS (
      SELECT 1 FROM ftb_personnels_staff_registration_form_data
      WHERE F_RosterId = rosterid AND F_FormFieldId = 'workerStatus'
    ) THEN
      -- 更新记录
      UPDATE ftb_personnels_staff_registration_form_data SET F_Phone = phone, F_Value = workStats WHERE F_RosterId = rosterid AND F_FormFieldId = 'workerStatus';
    ELSE
      -- 插入新记录
      INSERT INTO ftb_personnels_staff_registration_form_data (F_Id, F_RosterId, F_Phone, F_FormTypeId, F_FormFieldId, F_Value)
      VALUES (rosterid, rosterid, phone, '3', 'workerStatus', workStats);
    END IF;
  END LOOP;
 
  CLOSE cur1;
END$$
DELIMITER ;