Thursday, October 4, 2012

Difference between MySQL function and mysql procedure

 


 

 

What are the differences between stored procedure and functions in mysql

A FUNCTION is always returns a value using the return 
statement. A  PROCEDURE may return one or more values 
through parameters or may not return at all.
b. Functions are normally used for computations where as 
procedures are normally used for executing business logic.
c. A Function returns 1 value only. Procedure can return 
multiple values (max 1024).
d. Stored procedure returns always integer value by default 
zero. Whereas function returns type could be scalar or 
table or table values
e. Stored procedure is precompiled execution plan where as 
functions are not.
f. A function can call directly by SQL statement like 
select func_name from dual while procedure cannot.
g.Stored procedure has the security and reduces the network 
traffic and also we can call stored procedure in any no. of 
applications at a time.
h. A Function can be used in the SQL Queries while a 
procedure cannot be used in SQL queries .that cause a major 
difference b/w function and procedures.
 
--------------------------------  
 
Stored procedures are a set of actions already written and 
stored inside the database for acheiving a particular task 
where as functions are general database objects which are 
used for general purpose programming
 
there are 3 main differences between sp and function.
1 sp takes input,output parameters, function takes only 
input parameters.
2 temparary variables required to store return values of 
sp. in functions temparary variables will be optinale.
3 sp can not be called directly into DML statements , 
functions can be called directly into DML statements.
   
 
1. Functions can be used inline with a select statement 
while sprocs can't.

2. EXEC command can't be used inside a Function where it 
can be used inside an sproc.



Function :
1. Should return atleast one output parameter.Can return more than one parameter using OUT argument.
2. Parsed and compiled at runtime.
3.Cannot affect the state of database.
4.Can be invoked from SQL statement e.g. SELECT.
5. Functions are mainly used to compute values.
Procedure:
1. Doesn't need to return values, but can return value.
2.Stored as a pseudo-code in database i.e. compiled form.
3.Can affect the state of database using commit etc.
4.Cannnot be invoked from SQL statements e.g. SELECT.
5.Procedures are mainly used to process the tasks

6 comments:

  1. nice information....!!!!!
    Thank You....

    ReplyDelete
  2. I think the content covered in the blog is quiet impressive and brilliantly conveyed. Good job and great efforts. Keep it up.
    Web development Company in India Company | Web development Company Bangalore
    Web Development Company in Bangalore | Responsive Web development

    ReplyDelete
  3. Great and really helpful article! Adding to the conversation, providing more information, or expressing a new point of view...Nice information and updates. Really i like it and everyday am visiting your site..java training

    ReplyDelete