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

[Roland Bouman]

Sultana Jahan Rini wrote:
> convert into mysql. Please advise me and treat it
> as urgent...

Advise, yes. Urgent...well, this is not a helpdesk. It's weekend for me too you know. I'll do my best, yes?

Some general points:

1) mysql does not have the %type syntax. I just used mockup datatypes, substitute where appropriate
2) the bulk collect thingie is not supported in mysql (also see next point). However, a plain cursor should do the job equally well
3) MySQL does not have collections or arrays (PLSQL 'Tables') like oracle. This means you should crack open the ep_calllimit(); procedure and see if you can work around how things are now (you are passing it a collection now)
4) You have some unreferenced variables: p_lcr and p_doroutefailure. Something I missed?
5) Look at the comments inside what I made of you proc


CREATE PROCEDURE SP_test (
IN p_routedefid int unsigned
, IN p_rateareacode varchar(10)
, IN p_lcr varchar(10)
, IN p_doroutefailure varchar(10)
, IN p_sessionid int unsigned
, OUT p_returnrouteid integer unsigned
)
BEGIN
DECLARE v_routeid int unsigned;
DECLARE v_first_routeid int unsigned;
DECLARE v_epid_type varchar(10);
DECLARE v_areacode varchar(10);
DECLARE v_calllimit int unsigned;

DECLARE v_done bit default FALSE;

DECLARE CURSOR csr_route FOR
SELECT route_id, ep_id
FROM route
WHERE route_def_id = p_routedefid
AND area_code = p_rateareacode
ORDER BY (delta_t/delta_r) ASC
;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_done := TRUE
;
--
-- not really sure what to use
-- instead of value error.
-- in fact, I think MySQL would just
-- coerce or covert a value that would
-- raise value_error in oracle.
-- so, here, we just mark the return. Always.
--
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET p_returnrouteid := -1
;


OPEN csr_route;
FETCH csr_route
INTO v_route_id
, v_epid_type
;
IF NOT v_done THEN
v_first_route_id := v_route_id;
REPEAT
--
-- todo:
-- Not sure what to do here
-- what is this proc supposed to do?
--
CALL ep_calllimit();
--
-- end of todo
--
IF v_calllimit = 1 THEN
--
-- no nextval in mysql
-- just use ddl to make this
-- an auto-increment column
--
INSERT INTO table_2 (
route_id
, epid_type
, sessionid
)
VALUES (
v_route_id
, v_epid_type
, v_sessionid
);
COMMIT;
SET p_returnrouteid := v_first_routeid;
END IF;

FETCH csr_route
INTO v_route_id
, v_epid_type;
UNTIL v_done END REPEAT;
END IF;
CLOSE csr_route;

END;


Let me know if this got you started.

kind regards

Roland.