Convert stored procedure from Oracle to MySQL
Hi All,
I want immediate help to convert below stored procedure from oracle to mysql migration project. please help me out.
mostly the issue with how to achieve start with .... connect by and start with ...connect by prior.
delimiter //
create PROCEDURE SP_Recon_Limit(IN p_le_short_name varchar(4000),
IN p_br_short_name varchar(4000)
)
BEGIN
DECLARE v_txn_exists integer default 1;
DECLARE v_parent_facility integer;
DECLARE v_facility_nt facility_nt;
DECLARE connect by
declare c_facility cursor for
select distinct a.ADV_FACI_ID,
a.CURRENCY_ID,
a.OBLIGOR_ID,
a.ADV_FACI_AMOUNT,
a.ADV_FACI_NAME,
case (select 1 from m_obligor where obligor_id = a.obligor_id and reference_id like 'H%') when 1 then 'Y' else 'N' end as IS_DUMMY,
(case when exists(select 1 from t_advance_facility where parent_advf_id = a.ADV_FACI_ID) then 'Y' else 'N' end) as IS_MULTIENTITY
from t_advance_facility a
where a.valid_flag = 'T'
and a.parent_advf_id is null
and a.LEGAL_ENTITY_BRANCH_ID in (select sk_legal_entity_branch_id from r_legal_entity_branch
where LEGAL_ENTITY_ID = (select sk_legal_entity_id from m_legal_entity where legal_entity_shortname = p_le_short_name)
and BRANCH_ID = (select branch_id from m_branch where branch_shortname = p_br_short_name))
and (a.ADV_FACI_STATUS in (6,21,16,26,27,30,43)
or (a.ADV_FACI_STATUS in (9, 8, 11, 7, 24, 25) and a.ADV_FACI_AMOUNT > 0)) prior
a.adv_faci_id = a.parent_advf_id;
cursor c_sub_facility(f_id in number) is
select ADV_FACI_ID,
CURRENCY_ID,
OBLIGOR_ID,
ADV_FACI_AMOUNT,
ADV_FACI_NAME
from t_advance_facility
where parent_advf_id = f_id;
begin
delete from dbs_recon_limit;
declare v_facility cursor for c_facility LOOP
BEGIN
SAVEPOINT start_transaction;
select ADV_FACI_ID bulk collect into v_facility_nt from t_advance_facility
start with ADV_FACI_ID = v_facility.ADV_FACI_ID
connect by prior adv_faci_id = parent_advf_id;
end SP_Recon_Limit;
//
delimiter;