PL/SQL How to Return Object Value with PL/SQL Function

--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;

Comments

Popular posts from this blog

Solution for the fault: "System.Diagnostics.Process.Start is not working on IIS, but working on ASP.NET Development Server"

Solution for "Can not obtain Oracle client information from registry"