MySQL Forums
Forum List  »  Oracle

Convert Oracle stored procedure using REF_CURSOR to MySQL - Help me plz
Posted by: Utkarsh Varma
Date: August 16, 2012 05:41AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Convert Oracle stored procedure using REF_CURSOR to MySQL - Help me plz
4772
August 16, 2012 05:41AM


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.