Scribd
Upload
16 views1 page

What Is A Procedure in Oracle

Uploaded by

ayushrajput7586
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views1 page

What Is A Procedure in Oracle

Uploaded by

ayushrajput7586
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

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

You might also like