MySQL Forums
Forum List  »  MySQL Workbench

Converting Oracle Stored Procedure to MySQL
Posted by: Vijay Singh
Date: February 24, 2017 11:10PM

Hi,
Can you help me converting the following oracle SP to MySql SP. The procedure is with Input and Output parameters

The Store Procedure is---


create or replace package body PKG_IV_HOSP_MST is

procedure pcd_hosp_validation (
i_hosp_id in gen_hosp_mst.ghm_hosp_id%type,
o_msg out number
) as

var_dl_status gen_hosp_mst.ghm_hosp_dl_status%type;

begin

select ghm_hosp_dl_status
into var_dl_status
from gen_hosp_mst
where ltrim(rtrim(ghm_hosp_id))= ltrim(rtrim(i_hosp_id));


if var_dl_status = pkg_check_constant.not_deleted then
o_msg := pkg_check_constant.record_present;
elsif var_dl_status =pkg_check_constant.deleted then
o_msg := pkg_check_constant.record_deleted_before;
end if;

exception
when no_data_found then
o_msg := pkg_check_constant.no_record;

end pcd_hosp_validation;


procedure pcd_hosp_mst_insert(
i_hosp_id in gen_hosp_mst.ghm_hosp_id%type,
i_hosp_name in gen_hosp_mst.ghm_hosp_name%type,
i_address1 in gen_hosp_mst.ghm_address1%type,
i_address2 in gen_hosp_mst.ghm_address2%type,
i_addrs_child_name in gen_demography_mst.gdg_addrs_child_name%type,
i_pin in gen_hosp_mst.ghm_pin%type,
i_phone1 in gen_hosp_mst.ghm_phone1%type,
i_phone2 in gen_hosp_mst.ghm_phone2%type,
i_fax in gen_hosp_mst.ghm_fax%type,
i_email in gen_hosp_mst.ghm_email%type,
i_website in gen_hosp_mst.ghm_website%type,
i_hosp_type in gen_hosp_mst.ghm_hosp_type%type,
o_msg out number
) as
var_city_id gen_hosp_mst.gdg_addrs_child_id%type;
var_city_type gen_hosp_mst.gdg_addrs_child_type%type;

begin
o_msg := pkg_check_constant.no_error;

select gdg_addrs_child_id
into var_city_id
from gen_demography_mst
where ltrim(rtrim(gdg_addrs_child_name))=ltrim(rtrim(i_addrs_child_name))
and ltrim(rtrim(gdg_addrs_child_type))=pkg_check_constant.city_type;

select gdg_addrs_child_type
into var_city_type
from gen_demography_mst
where ltrim(rtrim(gdg_addrs_child_id))=ltrim(rtrim(var_city_id));


insert into
gen_hosp_mst
( ghm_hosp_id,
ghm_hosp_name,
ghm_address1,
ghm_address2,
gdg_addrs_child_type,
gdg_addrs_child_id,
ghm_pin,
ghm_phone1,
ghm_phone2,
ghm_fax,
ghm_email,
ghm_website,
ghm_hosp_type,
ghm_hosp_dl_status
)

values(
upper(i_hosp_id),
i_hosp_name,
i_address1,
i_address2,
var_city_type,
var_city_id,
i_pin,
i_phone1,
i_phone2,
i_fax,
i_email,
i_website,
upper(i_hosp_type),
pkg_check_constant.not_deleted
);
Exception
when others then
o_msg := pkg_check_constant.error_present;

end pcd_hosp_mst_insert;
end PKG_IV_HOSP_MST;

Options: ReplyQuote


Subject
Views
Written By
Posted
Converting Oracle Stored Procedure to MySQL
865
February 24, 2017 11:10PM


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.