Can you explain the difference between a function and a procedure in PL/SQL, and provide an example of when you would use one over the other in a database application?

2 Answers
Answered by suresh

PL/SQL Programmer Interview Question: Difference between Function and Procedure

PL/SQL Programmer Interview Question

Question: Can you explain the difference between a function and a procedure in PL/SQL, and provide an example of when you would use one over the other in a database application?

Answer:

In PL/SQL, a function and a procedure are both named PL/SQL code blocks that can be stored in the database for reuse. The main difference between them is that a function must return a value, whereas a procedure does not have to return any value.

Functions are typically used when you need to manipulate data and return a single value, such as performing calculations or transformations on data. Procedures, on the other hand, are used when you need to perform an action but do not necessarily require a return value, such as inserting or updating data in a database.

For example, you might use a function to calculate the total sales amount for a given period based on the input sales data. This function would return the total sales amount as a result. On the other hand, you could use a procedure to update the customer information in the database after a successful transaction without needing to return any value.

Answered by suresh

Explanation of the Difference Between Function and Procedure in PL/SQL

Function vs. Procedure in PL/SQL

One of the key differences between a function and a procedure in PL/SQL is the return type. A function always returns a value, while a procedure does not have a return value.

Example:

Let's say we have a database application where we need to calculate the total cost of an order. In this scenario, we can create a function to calculate the total cost and return the result.


CREATE FUNCTION calculateTotalCost(order_id IN NUMBER) RETURN NUMBER
IS
total_cost NUMBER;
BEGIN
SELECT SUM(price) INTO total_cost
FROM order_items
WHERE order_id = order_id;
RETURN total_cost;
END;

On the other hand, if we need to update the status of an order in the database but do not require a return value, we can use a procedure.


CREATE PROCEDURE updateOrderStatus(order_id IN NUMBER, new_status IN VARCHAR2)
IS
BEGIN
UPDATE orders
SET status = new_status
WHERE order_id = order_id;
END;

In this example, we use a function to calculate the total cost because we need to retrieve a value to display to the user or use in further calculations. We use a procedure to update the order status because the focus is on updating the database without needing to return a value.

Answer for Question: Can you explain the difference between a function and a procedure in PL/SQL, and provide an example of when you would use one over the other in a database application?