Convert Oracle Stored Procedure to MySQL
Hello all,
I am working in MySQL migration from Oracle. I like to convert the below Oracle stored procedure in MySQL. Pls help.
PROCEDURE InsertEnquiryData(PartNo IN VARCHAR2,VendorCd IN VARCHAR2,
FromDt IN DATE,ToDate IN DATE) IS
---------------------------------------------------------------------------
--Purpose : To generate the enquiry report---------------------------------------------------------------------------
Fleet VARCHAR2(30);
Aircarft VARCHAR2(5);
Part VARCHAR2(32);
Serial VARCHAR2(15);
RemLength NUMBER;
RemoDate DATE;
ROnumber VARCHAR2(10);
RemReas VARCHAR2(3);
ShopFind VARCHAR2(2000);
Vendor VARCHAR2(5);
--If Vendor is NULL get by Part and Date
CURSOR Cur_Get_Data_Part IS
SELECT AIRCRAFT_TYPE,AIRCRAFT_NO,PART_NO,SERIAL_NO,REMAINING_LENGTH,
REMOVAL_DATE,RO_NUMBER,REMOVAL_REASON,SHOP_FINDINGS,VENDOR_CODE
FROM BNW_M_DETAILS
WHERE (REMOVAL_DATE >= FromDt AND REMOVAL_DATE <= ToDate)
AND part_no = PartNo;
--If Part is NULL get by Vendor and Date
CURSOR Cur_Get_Data_Vendor IS
SELECT AIRCRAFT_TYPE,AIRCRAFT_NO,PART_NO,SERIAL_NO,REMAINING_LENGTH,REMOVAL_DATE,
RO_NUMBER,REMOVAL_REASON,SHOP_FINDINGS,VENDOR_CODE
FROM BNW_M_DETAILS
WHERE (REMOVAL_DATE >= FromDt AND REMOVAL_DATE <= ToDate)
AND vendor_code = VendorCd;
--Query by Part/Vendor and Date
CURSOR Cur_Get_Data_Both IS
SELECT AIRCRAFT_TYPE,AIRCRAFT_NO,PART_NO,SERIAL_NO,REMAINING_LENGTH,REMOVAL_DATE,
RO_NUMBER,REMOVAL_REASON,SHOP_FINDINGS,VENDOR_CODE
FROM BNW_M_DETAILS
WHERE (REMOVAL_DATE >= FromDt AND REMOVAL_DATE <= ToDate)
AND vendor_code = VendorCd
AND part_no = PartNo;
--Query by Date and fetch all records
CURSOR Cur_Get_Data_All IS
SELECT AIRCRAFT_TYPE,AIRCRAFT_NO,PART_NO,SERIAL_NO,REMAINING_LENGTH,REMOVAL_DATE,
RO_NUMBER,REMOVAL_REASON,SHOP_FINDINGS,VENDOR_CODE
FROM BNW_M_DETAILS
WHERE (REMOVAL_DATE >= FromDt AND REMOVAL_DATE <= ToDate);
BEGIN
DELETE FROM bnw_t_enquiry_report;
IF PartNo = '*' AND VendorCd = '*' THEN
OPEN Cur_Get_Data_All;
LOOP
FETCH Cur_Get_Data_All INTO Fleet,Aircarft,Part,Serial,RemLength,
RemoDate,ROnumber,RemReas,ShopFind,Vendor;
EXIT WHEN Cur_Get_Data_All%NOTFOUND;
InsertTempData(Fleet,Aircarft,Part,Serial,RemLength,
RemoDate,ROnumber,RemReas,ShopFind,Vendor);
END LOOP;
ELSIF PartNo = '*' THEN
OPEN Cur_Get_Data_Vendor;
LOOP
FETCH Cur_Get_Data_Vendor INTO Fleet,Aircarft,Part,Serial,RemLength,
RemoDate,ROnumber,RemReas,ShopFind,Vendor;
EXIT WHEN Cur_Get_Data_Vendor%NOTFOUND;
InsertTempData(Fleet,Aircarft,Part,Serial,RemLength,
RemoDate,ROnumber,RemReas,ShopFind,Vendor);
END LOOP;
ELSIF VendorCd = '*' THEN
OPEN Cur_Get_Data_Part;
LOOP
FETCH Cur_Get_Data_Part INTO Fleet,Aircarft,Part,Serial,RemLength,
RemoDate,ROnumber,RemReas,ShopFind,Vendor;
EXIT WHEN Cur_Get_Data_Part%NOTFOUND;
InsertTempData(Fleet,Aircarft,Part,Serial,RemLength,
RemoDate,ROnumber,RemReas,ShopFind,Vendor);
END LOOP;
ELSE
OPEN Cur_Get_Data_Both;
LOOP
FETCH Cur_Get_Data_Both INTO Fleet,Aircarft,Part,Serial,RemLength,
RemoDate,ROnumber,RemReas,ShopFind,Vendor;
EXIT WHEN Cur_Get_Data_Both%NOTFOUND;
InsertTempData(Fleet,Aircarft,Part,Serial,RemLength,
RemoDate,ROnumber,RemReas,ShopFind,Vendor);
END LOOP;
END IF;
END InsertEnquiryData;
Thanks