Oracle PL/SQL Next Level: Real-World Practice on Procedures & Functions

By /

If you’ve reached this post — congratulations 🎉
You’ve already crossed the basics of PL/SQL and database interaction.

Now comes the phase where:

  • Logic becomes reusable
  • Code becomes modular
  • And developers start thinking in business workflows, not just blocks

👉 Procedures and Functions are the backbone of enterprise PL/SQL systems used in banking, fintech platforms, trading engines, and analytics pipelines.

⚠️ Challenge Rule:
Attempt these questions by writing executable PL/SQL, not pseudo-code.

📌 1. Stored Procedures – Banking & FinTech Use Cases

🏦 Account & Transaction Processing

  1. Create a stored procedure to open a new bank account for an existing customer.
  2. Write a procedure to deposit money into a customer account and update the account balance.
  3. Develop a procedure to withdraw money from an account after validating minimum balance rules.
  4. Create a procedure to transfer funds between two accounts.
  5. Write a procedure to log every transaction into the TRANSACTION_LOG table.

🗄️ Database Integration

  1. Write a procedure that inserts transaction records into the TRANSACTIONS table.
  2. Create a procedure to update account status based on account balance.
  3. Write a procedure to delete inactive customer accounts older than a given number of years.

📌 2. Stored Procedures – Loans, Risk & Analysis

📊 Financial & Risk Scenarios

  1. Create a procedure to calculate EMI for a loan and update the LOANS table.
  2. Write a procedure to mark loans as “RISKY” if loan amount exceeds a threshold.
  3. Develop a procedure to apply penalty charges for overdue loans.
  4. Write a procedure to generate a loan summary report for a given customer.

🗄️ Database Integration

  1. Create a procedure to update loan status based on repayment history.
  2. Write a procedure that counts total active loans and stores the value in a log table.

📌 3. Functions – Business Calculations & Validations

💡 Financial Logic

  1. Create a function that returns monthly interest for a given account.
  2. Write a function to calculate EMI based on loan amount, interest rate, and tenure.
  3. Develop a function that calculates brokerage charges for a stock trade.
  4. Write a function to compute total transaction value for a given account.
  5. Create a function to calculate customer age based on date of birth.

📌 4. Functions – FinTech & Market Scenarios

📈 Stock Market & Trading

  1. Write a function that returns the latest stock price for a given stock symbol.
  2. Create a function to calculate profit or loss for a completed trade.
  3. Develop a function that checks if a stock is eligible for intraday trading.
  4. Write a function that returns total quantity traded by a customer.

🗄️ Database Integration

  1. Create a function that returns total number of trades executed on a given date.
  2. Write a function to return customer total investment value.

📌 5. Procedures vs Functions – Design Thinking Challenges

🧠 These questions test your architectural thinking.

  1. Identify which business logic should be implemented as a procedure instead of a function for fund transfers.
  2. Design a procedure-function combination for loan approval workflow.
  3. Decide whether brokerage calculation should be a procedure or function and justify your implementation through code.
  4. Implement a reporting requirement using both procedure and function together.
  5. Create a reusable function and call it from multiple procedures.

📌 6. Real-World Enterprise Scenarios (Mixed)

  1. Write a procedure that uses multiple functions to process a stock trade.
  2. Develop a procedure that validates inputs using functions before inserting records.
  3. Write a function that internally queries multiple tables to return a single business value.
  4. Create a procedure that calls another procedure and logs execution details.
  5. Implement a procedure that handles both success and failure scenarios for payment processing.

🔥 Developer Challenge (Highly Recommended)

💪 Try to solve each question using:

  • IN, OUT, and IN OUT parameters
  • Proper naming conventions
  • Transaction control (COMMIT / ROLLBACK)
  • Realistic business validations

If your code feels longer but cleaner, you’re doing it right.

⚠️ Disclaimer

All procedures, functions, scenarios, and table references in this post are created strictly for learning and practice purposes.
They do not represent real banking or trading systems and should not be used in production environments.

Scroll to Top