```html
Focus keyword: PL/SQL function vs procedure
In PL/SQL, a function and a procedure are both database program units that can be used to perform specific tasks, but they have some key differences:
- PL/SQL Function: A function is a type of program unit that returns a single value. It can be used to perform a specific operation and return a result to the calling program. Functions are mainly used for computations and return values based on input parameters.
- Example of using a PL/SQL function: A function can be created to calculate the total sales amount based on the quantity and price of items sold.
- PL/SQL Procedure: A procedure is a program unit that performs a specific task or set of tasks. Unlike functions, procedures do not return any value. They are typically used for performing operations that do not require a return value.
- Example of using a PL/SQL procedure: A procedure can be created to update the inventory levels of products in a database after a purchase is made, without needing to return a value.
Overall, functions are ideal for calculations and returning values, while procedures are suitable for performing tasks that do not require a return value in a database application.
```
```html
PL/SQL Interview Question: Function vs Procedure
Functions and procedures are two important elements in PL/SQL that serve different purposes in database applications:
Function:
A function is a subprogram that returns a value. It can be used to perform a specific task and return a single value back to the calling program.
Example:
CREATE OR REPLACE FUNCTION calculate_total_price (quantity IN NUMBER, unit_price IN NUMBER) RETURN NUMBER IS total_price NUMBER; BEGIN total_price := quantity * unit_price; RETURN total_price; END;
Functions are often used to perform calculations or data manipulations and return a result that can be used in other parts of the application.
Procedure:
A procedure is a subprogram that performs a set of actions without returning a value. It can contain multiple statements and can be used to execute tasks or operations on the database.
Example:
CREATE OR REPLACE PROCEDURE update_employee_salary (employee_id IN NUMBER, new_salary IN NUMBER) IS BEGIN UPDATE employees SET salary = new_salary WHERE employee_id = employee_id; END;
Procedures are commonly used to modify data in the database, handle complex business logic, or perform administrative tasks.
Choosing between a function and a procedure depends on the specific requirements of the application. Functions are useful when a return value is needed for further processing, while procedures are more suitable for tasks that do not require a return value but involve multiple actions.
```
Please login or Register to submit your answer