manju

• Dec 20th, 2005

 

         functions are return a value.

         No,Functions must return a single value.

         procedure are used to perform an action.

         variables in the package specification are called global variables

mvnagendra

 Profile Answers by mvnagendra 

• Dec 23rd, 2005

 

1) A function will return a value, adding to that , it is possible to use a function in SQL statements, whereas procedures cannot be used.2)No, functions cannot retrun more than one value, instead one can get this done in other way round by using OUT parameters for a function.3)To perform validations and transactions on database tables.4)Memory usage is more.5)The variable defined inside Package Specification are treated as Global variables, which can be referred externally.

PRATIK GHOSHAL

• Jan 8th, 2006

 

Hi,

function can return more than one value - one by return staement and others by OUT parameters.

senthil vinayagam

• Jan 28th, 2006

 

Function dost not have out parameters.

function should return single value only

Diptendu Hui

• Jan 28th, 2006

 

It is recommended that Not use OUT and INOUT parameter with Function to avoid the side effect. Although we can declare a OUT parameter but it should return by the return statement and both should have same data taypes.

So, We can't return more than one value from a function at a single point of time.

Rajeev C R

• Feb 1st, 2006

 

Both functions and procedures are using to do a special task or action, In functions it is must to return a single value, where as in procedures its not compulsory, In functions with the help of OUT parameter we can pass multiple outputs.

The functions can be used in DML statements , but it should satiesfy the function purity level. but procedures cant use in DML statements.

Sekhar

• Apr 20th, 2006

 

You can call functions which returned one value from SQL where as procedures you can't

aseem naithani

• May 1st, 2006

 

Hi,

The functions are used where we can't used the procedure.i.e we can use a function the in select statments,in the where clause of delete/update statments.But the procedure can't used like that.

It is true that function can return only one value, but a function can be used to return more than one value,by using out parameters and also by using ref cursors.

There is no harm in using the out parameter,when functins are used in the DML statements we can't used the out parameter(as per rules).

raj

• Oct 17th, 2006

 

Hii

A function can return more than one value. It can be done by using out parameters with the variables, datatype and size, and calling them in the return clause.

Hope this will help you.

Bye.

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Mar 27th, 2007

 

Function can able to return more than one value using ref cursur...it will return on base of select statement......

bhaskar

• Jun 9th, 2007

 

functions can return more than a value by returning a ref cursor variable instead of returning a single value(varchar2 or number). pls notify if this breaks any rule or functionality of oracle??

srianju

• Aug 11th, 2007

 

Function can have more than one value in a return value by using record structure

Define record type in a package and use this type in your function.

Tulashidas

• Sep 27th, 2007

 

1. Most use of the function is you can call function from your select or any exicuteble statment. You can't call procedure like function. 
2. You can pass any number of values to function and function will retern any number of values but it will must return one value.
3. Procedure is used for pass any number of parameter and return any number of values. You can call procedure from any whare by using call function or execute function.

g_sidhu

 Profile 

• Jan 31st, 2008

 

Functions are use to compute a value, which must be returned to the calling environment.

 

A function must return a value to the calling environment. Although multiple RETURN statements are allowed in a function (usually within an IF statement), only one RETURN statement is executed, because after the value is returned, processing of the block ceases. To have a function return multiple values is poor programming practice. Also, functions should be free from side effects, which change the values of variables that are not local to the subprogram.

 

Procedure is use to perform an action.

 

Disadvantages of Packages: more memory is required when using packages as the whole package is loaded into memory as soon as any object in the package is accessed native compilation can take twice as long as normal compilation.

 

Global Variables: The variable defined inside Package Specification are treated as Global variables, that can be referenced outside the package and is visible to external users. Global package items must be declared in the package specification.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 6th, 2008

 

Functions are used to compute a value

Using Return statement we can return only one value. Even in functions also we may return more than one value using out parameter. But It is not a good practice to return more than one value using functions.

Procedure are used to execute business logic.

Whenever you call a packaged pl/sql construct for the first time, entire package is loaded into memory.  High memory use is the disadvantage.

Variables declared in the package specification part of a package are called global variables.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 7th, 2008

 

1.Functions are used to compute a value
2. Function can return more than one value using out paramater. But it is not good practice to return more than one value in a function
3. Procedures are used to execute business logic
4. When a packaged object is called entire package is loaded into memory. High memory usage is disadvantage.
5. The variable declared in pakage specification is called global variable.

Here is simple example for using outparameter in functions

CREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C  OUT NUMBER)  RETURN NUMBER IS     D NUMBER(3); BEGIN     B := B + A;     C := B;     D := A + B + C;     RETURN D; END;   DECLARE    A1 NUMBER(3) := 10;    B1 NUMBER(3) := 20;    C1 NUMBER(3) := 30;    D1 NUMBER(3) := 40;  BEGIN    D1 := TEST_FUNC(A1,B1,C1);    DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);  END;

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 7th, 2008

 

1.Functions are used to computer a value.
2.Function can return more than one value using out parameter. But it is not a good practice to return more than one value in functions.
3.Procedures are used to execute business logic.
4. when a package object is called entire package is loaded into memory. High memory usage may be disadvantage.
5. A variable declared in package specification part is called a global variable.

Example for using out parameter in functions
 CREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C OUT NUMBER) RETURN NUMBER IS
D NUMBER(3);
 BEGIN
B := B + A;
C := B;
D := A + B + C;
RETURN D;
END;

DECLARE
A1 NUMBER(3) := 10;
B1 NUMBER(3) := 20;
C1 NUMBER(3) := 30;
D1 NUMBER(3) := 40;
BEGIN
D1 := TEST_FUNC(A1,B1,C1);
DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);
END;

ravivenkat1234

 Profile Answers by ravivenkat1234 

• Apr 28th, 2009

 

Functions can have multiple out Parameters one with Return Parameter and others can be IN OUT Parameter to the function. this way a function can return multiple out parameters.

Eg : Function ( a in number,
                        b in out number,
                        c in out number)
              Return number;