--create obj type
create type "obj_user_bank_account" as
object (
user_id number(18) ,
account_number varchar2(50),
bank_name varchar2(50)
);
--create pkg spec
function fnc_user_bank_account_no(p_user_id in number) return obj_user_bank_account;
--create pkg body
function fnc_user_bank_account_no(p_user_id in number) return obj_user_bank_account is
objuseraccountbank obj_user_bank_account := obj_user_bank_account (null, null, null);
accountno table_user_bank_accounts.account_number%type;
bankname table_banks.adi%type;
begin
select count(*)
into rowcnt
from table_user_bank_accounts account
where account.user_id = p_user_id
and default=1;
if rowcnt >0 then
select account.account_number, bank.adi
into accountno, bankname
from table_user_bank_accounts account,
table_banks bank
where account.user_id=p_user_id
and account.bank_id=bank.id
and account.default=1;
objuseraccountbank.user_id := p_user_id;
objuseraccountbank.account_number := accountno;
objuseraccountbank.bank_name := bankname;
return objuseraccountbank;
else
select account_number, adi
into accountno, bankname
from( select account.account_number,
bank.adi
from table_user_bank_accounts account,
table_banks bank
where account.user_id = p_user_id
and account.bank_id = bank.id
order by account.id desc)
where rownum=1;
objuseraccountbank.user_id := p_user_id;
objuseraccountbank.account_number := accountno;
objuseraccountbank.bank_name := bankname;
return objuseraccountbank;
end if;
end;
--create view
create or replace view test_view
as
(select pkg_ort.fnc_user_bank_account_no(knt.id).account_number_no from dual) account_number,
(select pkg_ort.fnc_user_bank_account_no(knt.id). bank_name from dual) bank_name
from table_users knt
order by knt.id;
create type "obj_user_bank_account" as
object (
user_id number(18) ,
account_number varchar2(50),
bank_name varchar2(50)
);
--create pkg spec
function fnc_user_bank_account_no(p_user_id in number) return obj_user_bank_account;
--create pkg body
function fnc_user_bank_account_no(p_user_id in number) return obj_user_bank_account is
objuseraccountbank obj_user_bank_account := obj_user_bank_account (null, null, null);
accountno table_user_bank_accounts.account_number%type;
bankname table_banks.adi%type;
begin
select count(*)
into rowcnt
from table_user_bank_accounts account
where account.user_id = p_user_id
and default=1;
if rowcnt >0 then
select account.account_number, bank.adi
into accountno, bankname
from table_user_bank_accounts account,
table_banks bank
where account.user_id=p_user_id
and account.bank_id=bank.id
and account.default=1;
objuseraccountbank.user_id := p_user_id;
objuseraccountbank.account_number := accountno;
objuseraccountbank.bank_name := bankname;
return objuseraccountbank;
else
select account_number, adi
into accountno, bankname
from( select account.account_number,
bank.adi
from table_user_bank_accounts account,
table_banks bank
where account.user_id = p_user_id
and account.bank_id = bank.id
order by account.id desc)
where rownum=1;
objuseraccountbank.user_id := p_user_id;
objuseraccountbank.account_number := accountno;
objuseraccountbank.bank_name := bankname;
return objuseraccountbank;
end if;
end;
--create view
create or replace view test_view
as
(select pkg_ort.fnc_user_bank_account_no(knt.id).account_number_no from dual) account_number,
(select pkg_ort.fnc_user_bank_account_no(knt.id). bank_name from dual) bank_name
from table_users knt
order by knt.id;
Comments
Post a Comment