PL/SQL Block Example: Deduct Balance and Low Balance Warning

PL/SQL Block: Deduct Balance and Display Warning in Accounts Table

PL SQL block example, Deduct balance in PL/SQL, Low Balance Warning, PL SQL conditional statement, Banking database PL/SQL, SQL Account Transaction Example

📌 Question

Suppose you have a table Accounts(account_id, customer_name, balance). Write a PL/SQL block to:

  • Deduct 500 from the balance of account_id = 101.
  • If the balance becomes less than 1000 after deduction, display: "Low Balance!".
  • Otherwise, display: "Transaction Successful".

✅ Answer

PL/SQL Code

DECLARE
   v_balance Accounts.balance%TYPE;
BEGIN
   -- Deduct 500 from the account balance
   UPDATE Accounts
   SET balance = balance - 500
   WHERE account_id = 101
   RETURNING balance INTO v_balance;

   -- Check balance and display appropriate message
   IF v_balance < 1000 THEN
      DBMS_OUTPUT.PUT_LINE('Low Balance!');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Transaction Successful');
   END IF;

   COMMIT;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Account not found.');
END;
/

📖 Explanation

  • UPDATE with RETURNING: Deducts 500 and immediately fetches the new balance into v_balance.
  • IF condition: Checks whether the balance is below 1000.
  • DBMS_OUTPUT: Prints either “Low Balance!” or “Transaction Successful”.
  • Exception Handling: Prints “Account not found.” if the account_id doesn’t exist.
  • COMMIT: Ensures the deduction is permanently saved.

📌 Example Execution

SET SERVEROUTPUT ON;
/
-- Execution Output (Case 1: Balance >= 1000):
Transaction Successful

-- Execution Output (Case 2: Balance < 1000):
Low Balance!

📌 Final Summary

  • The PL/SQL block deducts 500 from the account balance.
  • Checks if the balance falls below 1000.
  • Displays appropriate message to the user.
  • Useful for banking transactions and balance validation scenarios.

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: পোস্টগুলি (Atom)