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

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 ... endCREATE 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.

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).

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 greetingsBEGIN 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 IStotal 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;/