Stored Procedures in MySQL

A stored procedure is a block of SQL statements which is stored inside the database.A specific stored procedure can be used by multiple applications and using multiple programming languages.MySQL stored procedures are compiled on demand and it's stored in cache.Stored procedures increase performance of the applications and also help reduce the traffic between application and database server.

Creating a Stored Procedure

In MySQL console let's write a simple stored procedure without any parameters to get all the information about the employees of the school database.
MySQL use ; as the delimiter but while defining the procedure we have to change it temporarily so:

mysql[school]> delimiter |

And,then for the stored procedure:

create procedure getInfoEmployees() 
begin 
select * from employees; 
end
|


Notice the delimiter | which we changed before from ;. Here we are using the select statement to get all information from the employees table.So, to simply put we created a function called getInfoEmployees() when called executes a block of code i.e. the select statement.
Let's now write ruby application to use the stored procedure.Before that, let's change the delimiter of mysql to ;.

mysql[school]> delimiter ;

With Ruby

require 'mysql2'

$my_client = Mysql2::Client.new(:host => "localhost", :username => "neymar",:password => "school",:database => "school" )

def execute_stored_procedure 
  # to display the information
  query_results = $my_client.query("call getInfoEmployees()")
  # to count the result 
  query_results.count
  query_results.each(:as => :hash) do |m|
      m.each do |key,value|
        if key == "ename" and value == "Ram"
          puts m["ephone"].to_s + '  ' +  m["eaddress"]
        end 
      end
  end
end
# calling the function 
execute_stored_procedure

def insert_into eid,ename,pid,ephone,eaddress
  tablename="Emp"
  sqlquery = "insert into #{tablename} values (#{eid},'#{ename}',#{pid},#{ephone},'#{eaddress}')"
end 

def execute_query
  # first of all we require a set of variables
  eid=[1,2,3,4]
  ename=["Sabin","Nepal","hello","world"]
  pid=[3,2,3,3]
  ephone=[9898,1234,4678,789]
  eaddress=["Banepa"]
  eid.zip(ename,pid,ephone,eaddress.cycle).each do |eid,ename,pid,ephone,eaddress|
      $my_client.query(insert_into(eid,ename,pid,ephone,eaddress))
  end 
end
if execute_query
  puts "Successfully added the data"
end

Here,first of all we require the mysql2 library to interact with the database from the application.And, then we start a new client where we provide information to open the connection.
Then a function is defined execute_stored_procedure which calls the procedure and stores the output query_reults variable.And, some operations is performed.This is a very simple use of stored procedures. insert_into and execute_query are the functions for performing other operations with the database.

From MySQL

Inside MySQL "call" statement can be used:

mysql[school]> call execute_stored_procedure();