Difference between procedure and function.

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.
  • Interview Candidate
  • 28
  • 8035

Showing Answers 1 - 28 of 28 Answers

Abhineet Jindal

  • Nov 10th, 2005
 

The main difference is DML (Data Manipulation). Through Stored procedure you can manipulate data i.e u can insert,update,delete records from table but in function u can't manipulate data. then comes secondary differences like function returns a value and stored procedures does not until and unless u specify an output parameter.stores procedure is a precomplied function i.e it has to be complied oly once but a function when called is complied again and again.

manju

  • Dec 12th, 2005
 

procedure performs an action

function computing a value

  Was this answer useful?  Yes

shyki8

  • Dec 22nd, 2005
 

procedure perform action.in procedure ,a return statement can't contain an expressionfuncation computing a value.in function return statement must conain expression

  Was this answer useful?  Yes

SHIKHA

  • Aug 1st, 2006
 

One more diff is that function must return a value while procedure may or may not return a value

  Was this answer useful?  Yes

Mrunal

  • Sep 21st, 2006
 

One more difference is

Procedure can executed by writing exec(Procedure_name), But Functions are called in DML statements or a PL/SQL Block.

  Was this answer useful?  Yes

thumatinagaraju

  • Nov 2nd, 2006
 

hi manju both can perform same action but only diff iz procedure does't return a value n function directly returns a value....ok byeee

  Was this answer useful?  Yes

sayeed321in

  • Apr 12th, 2007
 

Difference between functions and procedures:
1) DML is allowed in the function if it is not called from a DML (select) statement.but it is absolutely allowed when called from a procedure or a function which is not called from any DML

2) you can have a return statement in function where as procedure does not have return statement.

3) You can invoke a function from a DML.but the same is not possible with a procedure.


This is what the difference i feel.......If u know anything else then let me know.

  Was this answer useful?  Yes

irinak

  • Apr 12th, 2007
 

Procedure does not return a Value , but a Funtion return a Value.

SCOTT@LSC01> create or replace type tn is table of number;
  2  / 
 
Type created.
 
SCOTT@LSC01> create or replace function f return tn pipelined is begin null; end;
  2  / 
 
Function created.
 
SCOTT@LSC01> select * from table(f);
 
no rows selected

  Was this answer useful?  Yes

venkat

  • Oct 11th, 2007
 

We can perform DML statements in a function. But we can't call this function in a SELECT statement

  Was this answer useful?  Yes

Rahul Kataria

  • Oct 19th, 2007
 

PROCEDURE                                                                                              FUNCTION
 USE IN,OUT PARAMETERS                                                           DON'T USE IN,OUT
                                                                                                                 PARAMETERS

CANNOT USE IN SQL SELECT                                                      CAN BE USE IN SQL STATEMENTS                                                                                              SELECT 
                                                                                                                  STATEMENTS


MAY OR MAY NOT RETURN VALUE                                                MUST RETURN VALUE
                                                                                                   WITH RETURN 
                                                                                                                 KEYWORD

LESS FASTER                                                                               FUNCTIONS WORKS
                                                                                                     MORE FAST AS THEY 
                                                                                                     ARE PRE-COMPILED
                                                                                                  

  Was this answer useful?  Yes

Rabindra_Kumar

  • Oct 28th, 2009
 

Procedure and function both are stored named subprogramme however but both are little bit dissimilar from each other.
Procedure is a named subprogramme that may or may not accept any argument and that is basically used to perform a sequece of actions. that never directly returns a value but more that one value can be returned by using out parameters.
Procedure can't be direcly called with sql statements.

Like Procedure, Function is also a named subprogramme that may or may not accept argument and that is basically used to perform some calculation, functions always have to return a value. a stored function can directly be called with SQL statements.

  Was this answer useful?  Yes

Prakash Jayabalan

  • Oct 29th, 2009
 

Procedure:
Parameters IN, OUT and IN OUT and can return n number of values via sys cursor.

Function:
Only IN parameter and must return a value by using RETURN.
Can be used in where clause of the Query but performance issue will raise.

  Was this answer useful?  Yes

usha`

  • Aug 19th, 2011
 

Functions can be used in a select statement where as procedures cannot.

functions cannot return values where as procedures can.

functions can be used as user defines data types in create table but procedures cannot.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions