MySQL Forums
Forum List  »  Oracle

Convert Oracle Stored Procedure to MySQL
Posted by: Mohamed Nihar
Date: February 06, 2011 11:41PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Convert Oracle Stored Procedure to MySQL
4648
February 06, 2011 11:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.