MySQL Forums
Forum List  »  Stored Procedures

Conversion of a cursor based Oracle stored procedure to MySQL.
Posted by: Vijay Singh
Date: June 02, 2017 01:42AM

Please help me converting a cursor based oracle stored proc to MySQL. I am well-verged in Oracle and very new in MySQL.

The procedure is-

procedure pcd_visit_combo_select(
i_hosp_id in CHAR (3),
o_visit_id out INTEGER(18),
o_regd_id out INTEGER(18),
o_first_name out VARCHAR (25),
o_middle_name out VARCHAR (25),
o_last_name out VARCHAR (25),
o_org_name out VARCHAR (60),
o_org_id out CHAR(8)
)
is
cursor cs_visit_combo_select is
select
a.ppv_visit_id,
a.com_organization_id,
b.pprh_regd_id,
b.pprh_first_name,
b.pprh_middle_name,
b.pprh_last_name,
c.com_organization_name com_organization_name

from pi_pt_visit a,
pi_pt_regd_header b,
cb_organization_mst c

where ltrim(rtrim(a.ghm_hosp_id)) = upper(ltrim(rtrim('CHH')))
and a.ppv_pt_type = pkg_check_constant.indoor
and a.ppv_indoor_status = pkg_check_constant.admitted
and a.ppv_discharge_allowed = pkg_check_constant.discharge_not_allowed

and ltrim(rtrim(b.pprh_regd_id)) = ltrim(rtrim(a.pprh_regd_id))
and ltrim(rtrim(a.com_organization_id)) = ltrim(rtrim(c.com_organization_id(+)))
and ltrim(rtrim(ppv_settlement_status))= pkg_check_constant.payment_not_settled;

i INTEGER :=1;

begin

for siglevalue in cs_visit_combo_select
loop

o_visit_id(i) :=siglevalue.ppv_visit_id;
o_org_id(i) :=siglevalue.com_organization_id;
o_regd_id(i) :=siglevalue.pprh_regd_id;
o_first_name (i) :=siglevalue.pprh_first_name;
o_middle_name(i) :=siglevalue.pprh_middle_name;
o_last_name(i) :=siglevalue.pprh_last_name;
o_org_name(i) :=siglevalue.com_organization_name;

i:=i+1;

if cs_visit_combo_select%notfound then
close cs_visit_combo_select;
exit;
end if;

end loop;

end pcd_visit_combo_select;

Options: ReplyQuote


Subject
Views
Written By
Posted
Conversion of a cursor based Oracle stored procedure to MySQL.
4113
June 02, 2017 01:42AM


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.