Blog > detail

Just my tiny mind

MySql Stored Prochedure

26-2-2012 Admin General 2 Comments

MySql Stored Prochedure

Previously my dear friend, request to me to post something about MySql Stored Prochedure. Therefore, in this post I will explain about MySql Stored Prochedure.

What is MySql Stored Prochedure? Like its name, MySql Stored Prochedure is a prochedure stored in MySql. Nice explanation huh? LOL. Ok, In more deep explanation, MySql Stored Prochedure is a declaration code saved in MySql that will run some method/prochedure. MySql Stored Prochedure can be trigered by a program, a trigger or even another MySql Stored Prochedure.

What is the benefit of MySql Stored Prochedure?

  • Run faster than SQL command send from application
  • Increase the performance of the application
  • Reduce requseted command. How? Instead sending multiple command. The application may send only one trigger, and MySql will do the rest
  • MySql Stored Prochedure can be accessed by many type of application
  • More secure. How? MySql Stored Prochedure, dont reveal the database to user.

What is the drawback of MySql Stored Prochedure?

  • Database server will have more load to do. In this case, it make high usage of memory and processor.
  • You cant debug MySql Stored Prochedure.

Lets build MySql Stored Prochedure

First, make the table that will be used.

<br />CREATE TABLE IF NOT EXISTS `member` (<br />  `member_id` int(11) NOT NULL AUTO_INCREMENT,<br />  `name` text NOT NULL,<br />  `pass` text NOT NULL,<br />  `desc` text NOT NULL,<br />  PRIMARY KEY (`member_id`)<br />) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;<br />

Second, declare MySql Stored Prochedure.

<br />DELIMITER //<br />DROP PROCEDURE IF EXISTS del_mem//<br />CREATE PROCEDURE del_mem(id int)<br />  BEGIN<br />  DELETE FROM `member` WHERE `member_id` = id;<br />  END //<br />DELIMITER ;<br />

Third, Call the MySql Stored Prochedure.

call del_mem(2);

When the stored prochedure called, it will execute a deletion for member that has the id. Different with MySql stored function, MySql Stored Prochedure dont have to return value.

That's all about MySql Stored Prochedure, that i can share in this article.

MySql Stored Prochedure

MySql Stored ProchedureMySql Stored Prochedure

word word

MySql Stored Prochedure

MySql Stored Prochedure

MySql Stored Prochedure