This similar article may be found on the internet, but i guess this article will be helpful for newbie's.
Migrations are a convenient way to alter your database in a structured and organised manner.
Here we are going to see how to create stored procedures with rake db:migrate
First create a new migration file in your db/migrate folder using
ruby script/generate migration stored_procedures. This will create the file db/migrate/001_stored_procedures.
Edit the code to tell it what to do.
self.up is used when migrating to a new version,
self.down is used to roll back any changes if needed. The class name needs to be the same as the migration name (i.e.
001_stored_procedures needs a class name of @
Let us migrate a stored procedure called items with a basic sql code :
The migration file now looks like this :
class StoredProcedures < ActiveRecord::Migration
CREATE DEFINER=`root`@`localhost` PROCEDURE `items`(IN l_item INT,IN userid INT,OUT l_itemid INT,OUT l_item_name VARCHAR)
BEGIN SELECT itemid,item_name,held_by,id INTO l_itemid,userid,id FROM users_items WHERE itemid=l_item AND held_by=userid;
execute "DROP PROCEDURE IF EXISTS `items`"
And now the cool part is just run rake db:migrate, Rails will create all the migration files in your database and also creates Stored procedures.