MySQL Forums
Forum List  »  Oracle

Convert a SP containing "connect by" and "TYPE"
Posted by: Tony ding
Date: October 05, 2012 03:21PM

Hello, i have a SP in Oracle doing the following things that needs to be migrated to MySQL.

Not sure if MySQL can accomplish the things we asked here. Please give some advice.


1. Requirement
There is a table with 3 columns

ID |Last_Modified | Flag
1 |2011-12-11 02:00:00| 0
2 |2011-12-13 02:00:00| 0
3 |2011-12-02 02:00:00| 0
4 |2011-12-12 02:00:00| 0

the stored procedure “getback(String pId, String pLast_Modified). E.g. pId =”1,2,3” and pLast_Modified = “2011-12-11 02:00:00, 2011-12-13 02:00:00, 2011-12-01 02:00:00”.

The logic is that, for the same ID, if the last_modified in the table is later than the passed last_modified, then set the flag for that record to “1”.


2. Stored procedure in Oracle

--procedure
create or replace procedure test1(
p_ids varchar2,
p_dates varchar2
) IS
TYPE id_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
TYPE date_type IS TABLE OF DATE INDEX BY VARCHAR2(64);

ids id_type;
last_updated date_type;
lvar1 varchar2(200);
lvar2 varchar2(200);

cursor c1 is
With ids as (select rownum id, regexp_substr(p_ids,'[^,]+', 1, level) val from dual
connect by regexp_substr(p_ids, '[^,]+', 1, level) is not null),
lupdated as (select rownum id, regexp_substr(p_dates,'[^,]+', 1, level) val from dual
connect by regexp_substr(p_dates, '[^,]+', 1, level) is not null)
select lut.id,
case when lut.last_updated > to_date(lu.val,'yyyy-mm-dd hh24:mi:ss') then 1
else 0
end flag
from last_updated_tbl lut
,ids
,lupdated lu
where lut.id = ids.val
and ids.id = lu.id
;

BEGIN


open c1;
loop
fetch c1 into lvar1,lvar2;
exit when c1%notfound;

dbms_output.put_line(lvar1||' flag:'||lvar2);

end loop;

close c1;

END;

3. Issues

MySQL doesn't support "Connect By" and "TYPE" and others here. Is there anything i can do to workaround those?

Thanks

Tony

Options: ReplyQuote


Subject
Views
Written By
Posted
Convert a SP containing "connect by" and "TYPE"
4033
October 05, 2012 03:21PM


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.