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.
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.
Please login or Register to submit your answer