mysql stored procedure

MySQL stored procedure is set of sql code which is stored in database server and can be invoked by program or trigger.

the stored procedure in MySQL that calls itself is known as a recursive stored procedure.stored procedure is way to execute your business logic directly into database server.


advantages of mysql stored procedure:

  • multiple applications are running in multiple environment and need to use the same   database.by using stored procedure you can make your business logic independent of   programming language.
  • if you are using MySQL stored procedure then you do not have table access directly which is   one more way to secure the data and transaction.
  • stored procedure in MySQL increases performance of your application
  • if your application is big or your database server on remote system then by using stored   procedure you can decrease the traffic between your database server and application   server
  • Since stored procedure is written in your database server and application call it sepratly   then the degree of re-usability increase because despite to going in much detail you can   call stored procedure to perform your action

disadvantages of mysql stored procedure:

  • if you overuse a large number of logical operations inside stored procedures, the cpu   usage will also increase because database server is not well-designed for logical   operations
  • A constructs of stored procedures make it more difficult to develop stored procedures   that have complicated business logic
  • Managing stored procedure is bit difficult because it does not have any object oriented   paradigm like things
  • MySQL Stored procedure are set of sql command form our logic so sometime programmer need to   debug the stored procedure. In mysql stored procedure it is very hard to debug

mysql Stored Procedure Syntax

we are going to develop a simple stored procedure named getusers() to help you get familiar with the syntax. the getusers() stored procedure selects all users from the 'users' table.

the create procedure statement is used to create procedure in mysql

launch the mysql client tool and type the following commands:

    delimiter//

                       create   procedure  getusers()
                       
                       begin
                       
                       select  *  from  user;
                       
                       end
                       
                       delimiter ;

  • we use the create procedure statement to create a new stored procedure.we specify   the name of stored procedure after the create procedure statement.
  • the code - section between begin and end is called the body of the stored procedure in MySQL.

calling stored procedures

in order to call a stored procedure, you use the following sql command:

         call      stored_procedure_name

you use the call statement to call a stored procedures e.g. to call the getusers stored procedure, use the following statement:

         call        getusers();

delete a stored procedures

if you want to delete a procedure you use the drop procedure statement

syntax:

         drop  procedure     if exists    procedure_name

mySQL stored procedure variables

a variable is a named data object whose value can change during the stored procedure execution.

you must declare a variable before you can use it.

you use the declare statement to declare a variable inside a stored procedure.

         declare     variable_name   datatype(size)    default  default_name ;

we declared two int variables x and y, and set their default values to zero

you use the set statement to assign a variable another value

         declare     x,  y       int     default   0

         set x = 10 ;

the value of the x variable is 10 after the assignment.


variables scope

a variable has its own scope,which defines its life time.if you declare a variable inside a stored procedure,it will be out of scope when the end statement of stored procedure reached.

if you declare a variable inside  begin   end  block,it will be out of scope if the end is reached

we discuss the variable scope- when a variable that begins with the @ sign at the beginning is session variable.it is available and accessible until the session ends


parameters

the parameters make the stored procedure more flexible and useful.in mysql, a parameter has one of three modes in, out or inout.

  • in : is the default mode.
  • out: the value of an out parameter can be changed inside the stored procedure and its    new value is passed back to the calling program.
  • inout : an inout parameter is the combination of in parameter and out parameter. it    means that the calling program may pass the argument, and the stored procedure can    modify the inout parameter and pass the new value back to the calling program.

how you can define parameters within a stored procedure

create procedure proc () : parameter list is empty

create procedure proc (in varname data-type) :one input parameter.in is optional because                                                                       parameters are in by default

create procedure proc (out varname data-type) : one output parameter.

create procedure proc (inoutvarname data-type):one parameter which is both input and outpu.

note : you can define multiple parameters defined with different types


in example

    delimiter//

                      create procedure   proc_in (in     var1     int) 
 
                      begin
                      
                               select    var1+2   as  result;
                               
                      end//

out example

    delimiter//

                      create procedure   proc_out (out   name   varchar(100)) 
 
                      begin
                      
                               set    name = 'sandeep kumar nehra';
                               
                      end//

inout example

    delimiter//

                      create procedure   proc_inout (inout   var1   int(4), in   var2  int(4)) 
 
                      begin
                      
                               set     var1 =  4+var2;
                               
                      end//