Convert Oracle stored procedure using REF_CURSOR to MySQL - Help me plz

[Utkarsh Varma]

Hi,

I am trying to convert Oracle Stored Procedure using REF_CURSOR to MYSQL, but facing lots of issues. Can you produce the equivalent code for MySQL?

Here is my procedure in Oracle:

create or replace
PROCEDURE SP_CAPPLAN
(
p_attid IN String,
p_pmt IN String,
p_phase IN String,
p_application IN String,
p_out OUT String
)

AS
v_start_date String(30);
v_end_date String(30);
v_date String(30);
v_tot_days NUMBER;
v_allocated_hrs NUMBER;
v_per_allocation NUMBER;
v_location String(30);
v_hrs String(30);
query_str VARCHAR2(1000);
p_days VARCHAR2(5000);
TYPE cur_typ IS REF CURSOR;
c cur_typ;
BEGIN

SELECT
to_char(STARTDATE, 'DD-MM-YYYY'),to_char(ENDDATE, 'DD-MM-YYYY'), actual_allocated_hrs
INTO
v_start_date,
v_end_date,
v_allocated_hrs
FROM
RESOURCE_ALLOCATION_TABLE
WHERE
attuid = p_attid AND
pmt_work = p_pmt AND
phase = p_phase AND
application = p_application;

SELECT
unique(location)
INTO
v_location
FROM
resource_details
WHERE
attuid = p_attid;

SELECT
PERCENT_ALLOCATION
INTO
v_per_allocation
FROM
ITUP_MILESTONE_DETAILS
WHERE
PHASE_DESC = p_phase;


DELETE
FROM
RESOURCE_WORK_ALLOCATION_DET
WHERE
ATTUID = p_attid AND
PMT = p_pmt AND
PHASE = p_phase AND
APPLICATION =p_application;
COMMIT;

IF v_location is null THEN
v_location := 'Offshore' ;
END IF;

SELECT
count(dt)
INTO
p_days
FROM(select to_date(v_start_date, 'DD-MM-YYYY') + rownum -1 dt from dual connect by level <= to_date(v_end_date, 'DD-MM-YYYY') - to_date(v_start_date, 'DD-MM-YYYY') + 1) where to_char(dt,'fmday') not in ('sunday','saturday');

query_str :='select dt from(select to_date('''||v_start_date||''', ''DD-MM-YYYY'') + rownum -1 dt from dual connect by level <= to_date('''||v_end_date||''', ''DD-MM-YYYY'') - to_date('''||v_start_date||''', ''DD-MM-YYYY'') + 1) where to_char(dt,''fmday'') not in (''sunday'',''saturday'')';
OPEN c FOR query_str ;
LOOP
FETCH c INTO v_date;
EXIT WHEN c%NOTFOUND;
--v_hrs := ROUND(v_allocated_hrs*v_per_allocation*0.01/p_days,2)||'' ;
--Actual allocated hours added by team lead for a particular phase particular resource
--start date and end date added by team lead for woriking of the actual allocated hours
v_hrs := ROUND(v_allocated_hrs/p_days,2)||'' ;
-- v_hrs is what a resource will work per day
INSERT INTO RESOURCE_WORK_ALLOCATION_DET(ALL_DATE,ATTUID,PMT,PHASE,APPLICATION,HOURS,LOCATION) VALUES(v_date,p_attid,p_pmt,p_phase,p_application,v_hrs,v_location);
COMMIT;
END LOOP;
CLOSE c;
p_out := 'Success';
END SP_CAPPLAN;


Thanks in advance!



Edited 1 time(s). Last edit at 08/21/2012 08:58AM by Utkarsh Varma.