Re: How to convert Oracle bulk collection, table of records into mysql

[Sultana Jahan Rini]

Hello Bouman,
Thanks for ur reply..
Please find below the procedure that i want to convert into mysql. Please advise me and treat it as urgent...

With rerards
Rini
-------------------------------------------------
CREATE OR REPLACE PROCEDURE SP_test (v_routedefid route.route_def_id%TYPE,
v_rateareacode rate.area_code%TYPE,
v_lcr route_def.do_lcr%TYPE,
v_doroutefailure route_def.do_route_failure%TYPE,
v_sessionid current_calls.session_id%TYPE,
v_returnrouteid OUT route.route_id%TYPE
)
IS
TYPE routeid_type IS TABLE OF route.route_id%TYPE INDEX BY BINARY_INTEGER;
TYPE epid_type IS TABLE OF route.ep_id%TYPE INDEX BY BINARY_INTEGER;

t_routeid routeid_type;
t_areacode areacode_type;
t_epid epid_type;
v_calllimit INTEGER;

BEGIN
SELECT route_id, ep_id
BULK COLLECT INTO t_routeid, t_epid
FROM route
WHERE route_def_id = v_routedefid
AND area_code = v_rateareacode
ORDER BY (delta_t/delta_r) ASC;

FOR i IN t_routeid.FIRST..t_routeid.LAST LOOP
ep_calllimit (t_epid(i), v_calllimit);

IF v_calllimit = 1 THEN
INSERT INTO table_2
VALUES (routefailoverid_seq.NEXTVAL, t_routeid(i), t_epid(i),
v_sessionid);

COMMIT;
v_returnrouteid := t_routeid(t_routeid.FIRST); END IF;

END LOOP;
EXCEPTION
WHEN value_error THEN
v_returnrouteid := -1;
null;
END;
/