Advertisement
psi_mmobile

Untitled

Mar 29th, 2021
1,255
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.88 KB | None | 0 0
  1. PROCEDURE invertVehicleInfo(p_source_vehicle_id IN NUMBER, p_dest_vehicle_id IN NUMBER, p_date IN DATE) IS
  2. v_source_remote_unit_id NUMBER(10);
  3. v_dest_remote_unit_id NUMBER(10);
  4. v_source_remote_unit_identifier VARCHAR2(50);
  5. v_dest_remote_unit_identifier VARCHAR2(50);
  6. v_src_last_vehicle_data_id NUMBER(10);
  7. v_dest_last_vehicle_data_id NUMBER(10);
  8.  
  9. CURSOR srcVehicleRecords_c IS
  10. SELECT vehicle_data_id
  11. FROM vehicle_data
  12. WHERE vehicle_id = p_source_vehicle_id
  13. AND gps_full_date > p_date;
  14.  
  15. CURSOR destVehicleRecords_c IS
  16. SELECT vehicle_data_id
  17. FROM vehicle_data
  18. WHERE vehicle_id = p_dest_vehicle_id
  19. AND gps_full_date > p_date;
  20.  
  21. BEGIN
  22. SELECT remote_unit_id
  23. INTO v_source_remote_unit_id
  24. FROM vehicle
  25. WHERE vehicle_id = p_source_vehicle_id;
  26.  
  27. SELECT remote_unit_id
  28. INTO v_dest_remote_unit_id
  29. FROM vehicle
  30. WHERE vehicle_id = p_dest_vehicle_id;
  31.  
  32. SELECT IMEI
  33. INTO v_source_remote_unit_identifier
  34. FROM remote_unit
  35. WHERE remote_unit_id = v_source_remote_unit_id;
  36.  
  37. SELECT IMEI
  38. INTO v_dest_remote_unit_identifier
  39. FROM remote_unit
  40. WHERE remote_unit_id = v_dest_remote_unit_id;
  41.  
  42. SELECT MAX(vehicle_data_id)
  43. INTO v_src_last_vehicle_data_id
  44. FROM vehicle_data
  45. WHERE vehicle_id = p_source_vehicle_id;
  46.  
  47. SELECT MAX(vehicle_data_id)
  48. INTO v_dest_last_vehicle_data_id
  49. FROM vehicle_data
  50. WHERE vehicle_id = p_dest_vehicle_id;
  51.  
  52. FOR srcVehicleRecords_r IN srcVehicleRecords_c
  53. LOOP
  54. UPDATE vehicle_data
  55. SET remote_unit_identifier = v_dest_remote_unit_identifier,
  56. vehicle_id = p_dest_vehicle_id,
  57. remote_unit_id = v_dest_remote_unit_id
  58. WHERE vehicle_data_id = srcVehicleRecords_r.vehicle_data_id;
  59. END LOOP;
  60.  
  61. FOR srcVehicleRecords_r IN srcVehicleRecords_c
  62. LOOP
  63. UPDATE vehicle_data
  64. SET remote_unit_identifier = v_source_remote_unit_identifier,
  65. vehicle_id = p_source_vehicle_id,
  66. remote_unit_id = v_source_remote_unit_id
  67. WHERE vehicle_data_id = srcVehicleRecords_r.vehicle_data_id;
  68. END LOOP;
  69.  
  70. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement