PL/SQL PROGRAMMING _WEEK 2
Exercise 1: Control Structures
Scenario 1:
Apply a 1% Discount to Loan Interest Rates for Customers Above 60
Question:
Write a PL/SQL block that loops through all customers, checks their age, and if they are
above 60, applies a 1% discount to their current loan interest rates.
Answer:
BEGIN
FOR cust IN (SELECT customer_id, interest_rate
FROM customers
WHERE age > 60)
LOOP
UPDATE customers
SET interest_rate = interest_rate – 1 //1% discount
WHERE customer_id = cust.customer_id;
END LOOP;
COMMIT;
END;
Explanation:
a) The FOR loop selects all customers where age > 60.
b) Inside the loop, the interest rate is decreased by 1%.
c) The UPDATE statement applies the discount.
d) COMMIT is used to save the changes permanently in the database.
Scenario 2:
Promote Customers to VIP Based on Balance
Question:
Write a PL/SQL block that iterates through all customers and sets a flag Is VIP to TRUE for
those with a balance over $10,000.
Answer:
BEGIN
FOR cust IN (SELECT customer_id
FROM customers
WHERE balance > 10000)
LOOP
UPDATE customers
SET VIP = 'TRUE'
WHERE customer_id = cust.customer_id;
END LOOP;
COMMIT;
END;
Explanation:
• The loop finds customers whose balance > 10000.
• The IsVIP field is updated to 'TRUE' for those customers.
• COMMIT ensures the changes are saved in the database.
Scenario 3:
Send Reminders for Loans Due in the Next 30 Days
Question:
Write a PL/SQL block that fetches all loans due in the next 30 days and prints a reminder
message for each customer.
Answer:
BEGIN
FOR loan IN (SELECT customer_id, due_date
FROM loans
WHERE due_date BETWEEN SYSDATE AND SYSDATE + 30)
LOOP
DBMS_OUTPUT.PUT_LINE('Reminder: Customer ' || loan.customer_id ||
' has a loan due on ' || TO_CHAR(loan.due_date, 'DD-MON-YYYY'));
END LOOP;
END;
Explanation:
1. This block selects loans with a due_date within 30 days from today.
2. It uses DBMS_OUTPUT.PUT_LINE to display a reminder message.
3. No changes are made to the database; this is just for notifying.
Exercise 3:
Stored Procedures
Scenario 1: Process Monthly Interest
Question:
Write a stored procedure ProcessMonthlyInterest that calculates and updates the balance of
all savings accounts by applying an interest rate of 1% to the current balance.
Answer:
CREATE OR REPLACE PROCEDURE ProcessMonthlyInterest AS
BEGIN
FOR acc IN (SELECT account_id, balance
FROM accounts
WHERE account_type = 'SAVINGS')
LOOP
UPDATE accounts
SET balance = balance + (balance * 0.01)
WHERE account_id = acc.account_id;
END LOOP;
COMMIT;
END;
Explanation:
1. Loops through all accounts where account_type = 'SAVINGS'.
2. Increases the balance by 1% for each account.
3. COMMIT saves the changes.
Scenario 2:
Update Employee Bonus
Question:
Write a stored procedure UpdateEmployeeBonus that updates the salary of employees in a
given department by adding a bonus percentage passed as a parameter.
Answer:
CREATE OR REPLACE PROCEDURE UpdateEmployeeBonus (
dept_id IN NUMBER,
bonus_pct IN NUMBER
) AS
BEGIN
UPDATE employees
SET salary = salary + (salary * bonus_pct / 100)
WHERE department_id = dept_id;
COMMIT;
END;
Explanation:
a. The procedure accepts:
a. dept_id: ID of the department
b. bonus_pct: Bonus percentage (e.g., 10 for 10%)
b. Updates all employees in that department by increasing their salary.
c. COMMIT confirms the salary updates.
Scenario 3:
Transfer Funds Between Accounts
Question:
Write a stored procedure TransferFunds that transfers a specified amount from one account to
another, ensuring the source account has enough balance.
Answer:
CREATE OR REPLACE PROCEDURE TransferFunds (
source_acc_id IN NUMBER,
target_acc_id IN NUMBER,
amount IN NUMBER
) AS
source_balance NUMBER;
BEGIN
SELECT balance INTO source_balance
FROM accounts
WHERE account_id = source_acc_id;
IF source_balance >= amount THEN
UPDATE accounts
SET balance = balance - amount
WHERE account_id = source_acc_id;
UPDATE accounts
SET balance = balance + amount
WHERE account_id = target_acc_id;
COMMIT;
ELSE
RAISE_APPLICATION_ERROR(-20001, 'Insufficient balance in source account.');
END IF;
END;
Explanation:
a. Takes 3 input parameters:
a. source_acc_id: Account to debit
b. target_acc_id: Account to credit
c. amount: Amount to transfer
b. Checks if the source account has enough balance.
c. If yes, deducts from source and adds to target.
d. If not, raises an error.
