MySQL Forums
Forum List  »  Stored Procedures

Oracle procedure to MySQL procedure
Posted by: Niranjan Vyawahare
Date: July 17, 2014 07:00AM

Hi all,

I am new to MySQL. We have some procedures / function working in Oracle now we want to migrate the system in MySQL. The tables and views are done. Only few procedures are giving problems. Can someone please help me here. Following procedure need to convert in MySQL.

-------------------
drop function if exists f_book;

delimiter //

/*
Comment by Shiva

Definition of the database columns.
-----------------------------------

tab_user.user_id INT NOT NULL PK Auto Increment
tab_item.item_id INT NOT NULL PK Auto Increment
tab_office.office_id TINYINT NOT NULL PK Auto Increment
tab_operator.operator_id TINYINT NOT NULL PK Auto Increment
tab_item_type.type_id SMALLINT NOT NULL PK Auto Increment
tab_reservation_header.reh_id INT NOT NULL PK Auto Increment
tab_reservation_header.booked_for_date DATETIME;
*/

create function f_book ( p_item_id varchar(4000) /* tab_item.item_id%TYPE */,
p_off_id varchar(4000) /* tab_office.office_id%TYPE */,
p_user_id varchar(4000) /* tab_user.user_id%type */,
p_opr_id varchar(4000) /* tab_operator.operator_id%type */,
p_number double )
returns varchar(4000)
BEGIN
DECLARE v_total_neck tinyint;
DECLARE v_res_number tinyint;
DECLARE v_same_item tinyint;
DECLARE v_typ_id varchar(4000) /* tab_item_type.type_id%type */;
DECLARE v_reh_id varchar(4000) /* tab_reservation_header.reh_id%type */;
DECLARE v_iof_id varchar(4000) /* tab_item_office_item.iof_id%type */;
DECLARE v_message varchar(100);
DECLARE v_next_delivery_date datetime;
DECLARE v_step tinyint;


SET v_next_delivery_date = f_next_delivery_date ( p_user_id );
begin
select reh_id into v_reh_id from tab_reservation_header
where user_id = p_user_id
and truncate(booked_for_date, 0) = truncate(v_next_delivery_date, 0);

exception
when no_data_found then
SET v_reh_id = nextval('seq_reh');
insert into tab_reservation_header
( reh_id, user_id, booked_for_date, reh_status, reh_allowed_value )
values
( v_reh_id, p_user_id, truncate(v_next_delivery_date, 0), 'A', 0 );
end;

select count(*) into v_res_number from tab_reservation where reh_id = v_reh_id;
if v_res_number = 5
then
SET v_message = 'You already have 5 items. To book this item, please remove any one from your current list.';
return v_message;
else
select count(*) into v_same_item from tab_reservation where reh_id = v_reh_id and res_item_id = p_item_id;
if v_same_item > 0
then
SET v_message = 'You already booked this item';
return v_message;
else
select count(ity_typ_id) INTO v_typ_id from tab_item_type
where ity_item_id = p_item_id
and ity_typ_id in (1, 6, 8);

select count(distinct res.res_item_id) into v_total_neck from tab_reservation res, tab_item_type ity
where reh_id = v_reh_id
and res.res_item_id = ity.ITY_item_ID
and ity_typ_id in (1, 6, 8);

If v_total_neck = 3 and v_typ_id > 0
Then
SET v_message = 'You get maximum 3 necklaces or pendants or mangalsutras in a week.';
return v_message;
else
begin
select iof_id into v_iof_id
from tab_item_office_item
where and iof_item_id = p_item_id and iof_OFF_ID = p_off_id and iof_STATUS = 'A'
limit 1
for update nowait;
exception
when no_data_found then
SET v_message = 'This item is booked by other customer. Please select different item. Sorry for the inconvinience.';
return v_message;
end;

insert into tab_reservation
( res_id, reh_id, user_id, res_iof_id, res_item_id, res_booking_date, res_booking_method, res_status )
values ( nextval('seq_res'), v_reh_id, p_user_id, v_iof_id, p_item_id, sysdate(), 'W', 'A');

update tab_item_office_item
set iof_status = 'B', iof_usr_id = p_user_id, iof_booked_date = sysdate()
where iof_id = v_iof_id;

P_AUD_AUDIT ( p_user_id,
p_off_id,
p_opr_id,
p_description => concat(ifnull(v_iof_id, '') ,' is booked')
);
commit;
SET v_message = 'Item booked.';
return v_message;
end if;

end if;
end if;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
//

DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Oracle procedure to MySQL procedure
21931
July 17, 2014 07:00AM


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.