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

sql stored-procedures function plsql
Tony picture Tony · Apr 21, 2009 · Viewed 236.5k times · Source

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

Answer

Petros picture Petros · Apr 21, 2009

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.

Related questions

Solution to "cannot perform a DML operation inside a query"?

I am using a Data Analysis tool and the requirement I have was to accept a value from the user, pass that as a parameter and store it in a table. Pretty straighforward so I sat to write this create …

Read More
Functions vs Stored Procedures

Let's say I have to implement a piece of T-SQL code that must return a table as result. I can implement a table-valued function or else a stored procedure that returns a set of rows. What should I use? In …

Read More
Difference between language sql and language plpgsql in PostgreSQL functions

Am very new in Database development so I have some doubts regarding my following example: Function f1() - language sql create or replace function f1(istr varchar) returns text as $$ select 'hello! '::varchar || istr; $$ language sql; Function f2() - …

Read More