implementing horizontally fragmented tables in pl/sql procedure

Question

I have two tables.account on server and account1 on site , which have same attributes but different data.

CREATE TABLE ACCOUNT (
Accno int, Balance int, Acctype varchar2(20), Accbranch varchar2(20), PRIMARY KEY(Accno));

CREATE TABLE ACCOUNT1 (
Accno int, Balance int, Acctype varchar2(20), Accbranch varchar2(20), PRIMARY KEY(Accno));

now i wish to make a procedure that will make withdrawal bank transaction.user will give input a account number which may from either account table or account1 table and the withdrawal amount. then the procedure will update the balance in that specific table which has the account number that user has given as input. My code is ok for one table.but cant understand what to do for two horizontally fragmented table(account and account1). Can anyone suggest me a better way to find the solution. here is my procedure

CREATE OR REPLACE  PROCEDURE test(x IN number, y IN number)
AS
    cur_balance number;
    new_balance number;

BEGIN
    select Balance into cur_balance
    from Account  
    where Accno = x;

if (cur_balance < y)then
    dbms_output.put_line('Insufficient balance');
else
    new_balance:=cur_balance-y;
    update Account
    set Balance = new_balance
    where Accno = x;

end if; 
    dbms_output.put_line('Money has been withdrawn successfully');
    dbms_output.put_line('Current Balance:' || new_balance);
commit;
END;
/

score 0 · Accepted Answer · answered Oct 08 '19 at 19:20

Check both tables; if accno doesn't exist in one, I presume that no_data_found is raised. Then check another.

Here's an example:

CREATE OR REPLACE  PROCEDURE test(x IN number, y IN number)
AS
    cur_balance number;
    new_balance number;
    which_table varchar2(20);
BEGIN
  begin
    select Balance 
      into cur_balance
      from Account 
      where Accno = x;

    which_table := 'account';
  exception
    when no_data_found then
      -- ACCNO wasn't found in ACCOUNT, so - check ACCOUNT1
      select balance
        into cur_balance
        from account1
        where accno = x;

      which_table := 'account1';        
  end;

  if (cur_balance < y)then
     dbms_output.put_line('Insufficient balance');
  else
     new_balance := cur_balance - y;

     if which_table = 'account' then
        update Account
          set Balance = new_balance
          where Accno = x;
     else
        update account1
          set balance = new_balance
          where accno = x;
     end if;

     dbms_output.put_line('Money has been withdrawn successfully');
     dbms_output.put_line('Current Balance:' || new_balance);

     commit;
  end if;  
END;
/

its working.thanks for sharing your idea.is it possible to find the solution by using union or other operation between two tables? :) — Rubaet Hossain, Oct 08 '19 at 19:39
You're welcome. UNION? Well, you could fetch current balance *somehow*, but the point is how to know which table to update. I guess code I posted is quite simple and easy to maintain, so - I wouldn't complicate it. — Littlefoot, Oct 08 '19 at 19:44

implementing horizontally fragmented tables in pl/sql procedure

1 Answers1