What is the difference between function and procedure in PL/SQL?

Question

What is the difference between function and procedure in PL/SQL ?

Sherikasherill

I would disagree with this being a duplicate. In general, programming language terms like procedure and function are not used the same way in a database system. This is a good question, it's just that the answer is "same difference as with programming languages: see <other question>". – Excel

does anyone have the list of differences since oracle 11g and forward? I think we can use certain extra functionality in functions now! – Imprison

Answer 1

A procedure does not have a return value, whereas a function has.

Example:

CREATE OR REPLACE PROCEDURE my_proc
   (p_name IN VARCHAR2 := 'John') as begin ... end

CREATE OR REPLACE FUNCTION my_func
   (p_name IN VARCHAR2 := 'John') return varchar2 as begin ... end

Notice how the function has a return clause between the parameter list and the "as" keyword. This means that it is expected to have the last statement inside the body of the function read something like:

return(my_varchar2_local_variable);

Where my_varchar2_local_variable is some varchar2 that should be returned by that function.

Edee

As a little clarification, you can still get OUT values from a procedure - in fact you can have a return value for each OUT parameter you specify. – Stringfellow

Answer 2

A function can be in-lined into a SQL statement, e.g.

select foo
      ,fn_bar (foo)
  from foobar

Which cannot be done with a stored procedure. The architecture of the query optimiser limits what can be done with functions in this context, requiring that they are pure (i.e. the same inputs always produce the same output). This restricts what can be done in the function, but allows it to be used in-line in the query if it is defined to be "pure".

Otherwise, a function (not necessarily deterministic) can return a variable or a result set. In the case of a function returning a result set, you can join it against some other selection in a query. However, you cannot use a non-deterministic function like this in a correlated subquery as the optimiser cannot predict what sort of result set will be returned (this is computationally intractable, like the halting problem).

Burgas

It may not make sense, but the optimizer doesn't prevent you from using non-deterministic functions anywhere. – Kemp

Answer 3

In dead simple way it makes this meaning.

Functions :

These subprograms return a single value; mainly used to compute and return a value.

Procedure :

These subprograms do not return a value directly; mainly used to perform an action.

Example Program:

CREATE OR REPLACE PROCEDURE greetings

BEGIN 

dbms_output.put_line('Hello World!');

END ;
/

Executing a Standalone Procedure :

A standalone procedure can be called in two ways:

• Using the EXECUTE keyword • Calling the name of procedure from a PL/SQL block

The procedure can also be called from another PL/SQL block:

BEGIN 
greetings;
END;
/

Function:

CREATE OR REPLACE FUNCTION totalEmployees 
RETURN number IS
total number(3) := 0;
BEGIN 
SELECT count(*) into total 
FROM employees;
RETURN total; 
END;
/

Following program calls the function totalCustomers from an another block

DECLARE 
c number(3);
BEGIN 
c := totalEmployees();
dbms_output.put_line('Total no. of Employees: ' || c);
END;
/

Oat

Answer 4

Both stored procedures and functions are named blocks that reside in the database and can be executed as and when required.

The major differences are:

1. A stored procedure can optionally return values using out parameters, but can also be written in a manner without returning a value. But, a function must return a value.

2. A stored procedure cannot be used in a SELECT statement whereas a function can be used in a SELECT statement.

Practically speaking, I would go for a stored procedure for a specific group of requirements and a function for a common requirement that could be shared across multiple scenarios. For example: comparing between two strings, or trimming them or taking the last portion, if we have a function for that, we could globally use it for any application that we have.

Reggy

Answer 5

The following are the major differences between procedure and function,

1. Procedure is named PL/SQL block which performs one or more tasks. where function is named PL/SQL block which performs a specific action.
2. Procedure may or may not return value where as function should return one value.
3. we can call functions in select statement where as procedure we cant.

Jarmon

Can't a function perform more than one "action"? It returns only data-type. – Bartizan

Answer 6

In the few words - function returns something. You can use function in SQL query. Procedure is part of code to do something with data but you can not invoke procedure from query, you have to run it in PL/SQL block.

Wycliffite

Answer 7

1. we can call a stored procedure inside stored Procedure,Function within function ,StoredProcedure within function but we can not call function within stored procedure.
2. we can call function inside select statement.
3. We can return value from function without passing output parameter as a parameter to the stored procedure.

This is what the difference i found. Please let me know if any .

Load

You are incorrect - there is no reason you cannot call a function from a procedure. – Lys