What is a procedure in Oracle ? How it differs from a function. Explain with suitable example.
In Oracle, a procedure is a named PL/SQL block that performs one or more specific tasks. It is stored in the database and can be executed
multiple times with different parameters. Procedures are typically used to perform actions, such as inserting data into a table, updating
records, or handling complex business logic.
A function, on the other hand, is also a named PL/SQL block, but it is primarily designed to return a single value. Functions are typically
used to compute and return a value, such as a calculation or a result based on input parameters.
Key Differences between Procedures and Functions in Oracle:
Feature Procedure Function
Purpose Performs actions, like DML operations. Calculates and returns a value.
Return Type Does not return a value (can return via OUT parameters). Must return a single value.
Use in SQL Cannot be called in SQL statements. Can be called in SQL statements.
Parameter Mode Supports IN, OUT, and IN OUT parameters. Only supports IN parameters (return value as output).
Syntax CREATE OR REPLACE PROCEDURE ... CREATE OR REPLACE FUNCTION ...
Example of a Procedure in Oracle
Here’s a simple procedure that adds a new record to an employees table:
sql
Copy code
CREATE OR REPLACE PROCEDURE add_employee (
p_emp_id IN NUMBER,
p_emp_name IN VARCHAR2,
p_salary IN NUMBER
) AS
BEGIN
INSERT INTO employees (emp_id, emp_name, salary)
VALUES (p_emp_id, p_emp_name, p_salary);
COMMIT;
END add_employee;
Usage: This procedure does not return a value directly. Instead, it performs an action of inserting a new employee record into the
employees table.
Example of a Function in Oracle
Here’s a simple function that calculates and returns an employee's annual salary based on their monthly salary.sql
Copy code
CREATE OR REPLACE FUNCTION annual_salary (
p_salary IN NUMBER
) RETURN NUMBER AS
BEGIN
RETURN p_salary * 12;
END annual_salary;
Usage: This function can be called in SQL statements, like so:
sql
Copy code
SELECT annual_salary(3000) FROM dual;
This will return the annual salary by multiplying the monthly salary by 12.
Summary
• Use a procedure to perform an action (like inserting, updating, or deleting data) where no direct return value is needed.
• Use a function to compute and return a specific value, which can be directly used in SQL
