Differencebetween Function and Procedure in Pl/Sql

PL/Sql procedure vs function?

You already found the main difference. You create a function if you want to use it in SQL. You create a procedure, when you want to use it only in PL/SQL.

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

Difference between procedures/functions and objects in Oracle PL/SQL

FUNCTION allows a value to return with the RETURN statement.

PROCEDURE has no such return value. However it is possible to return values by declaring the parameter as OUT rather than the default IN. There is also a IN OUT.

OBJECT in Oracle is some other concept and has nothing to do with PROCEDURE AND FUNCTION and is more like a class definition as you know it from Java. Though this comparison is a bit weak. There is some useful documentation on Oracle Objects, e.g. this link http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/obj_types.htm

PACKAGE Though you have not asked for it, it should be mentioned. A Oracle Package contains a collection of Functions and Procedures (and more). The Package consists of the declaration and the package body. What is defined in the Package declaration can be accessed from outside the rest is private.

What is the difference between a function and a procedure ?

A function returns a value and a procedure just executes commands.

The name function comes from math. It is used to calculate a value based on input.

A procedure is a set of commands which can be executed in order.

In most programming languages, even functions can have a set of commands. Hence the difference is only returning a value.

But if you like to keep a function clean, (just look at functional languages), you need to make sure a function does not have a side effect.

is there a difference between is and as in a PL/SQL procedure

Both keywords can be used equivalently. See the documentation:

---

Related Topics