MySQL Store Procedure Basic Examples.

Hello Everyone,

STEP -1

Now, I want to know about MySQL Store Proceduer (SP) , Trigger , View etc. available in MySql. So, I am searching many sites for best examples. off course MySql manual is best one for learning above things. but we can learn better with more examples.

For that I have search few sites like..

http://www.devshed.com/c/a/MySQL/Examining-MySQL-50/2/

http://guyh.textdriven.com/MySqlSpp/MyExamples.html

Above both are relay good sites.

As off my learning curve I am posting few things for all MySql DBA. So, If you found any other good example then you are free to post.

From above site I have made one simple SP as below..

DELIMITER $$

DROP PROCEDURE IF EXISTS `mass_testsite`.`testing`$$

CREATE PROCEDURE `mass_testsite`.`testing`(IN par1 VARCHAR(100), OUT par2 VARCHAR(100))

BEGIN

UPDATE adminactivitylog SET userName = ‘Blog’ where userName=par1;
SELECT userName from adminactivitylog where userName = ‘Blog’ INTO par2;

END$$

DELIMITER ;

For Run SP you have use CALL function

For Eg.

SET @par1 = ‘mark’;
call `mass_testsite`.`testing`(@par1, @userName);
SELECT @userName;

Need to learn More….

STEP -2

How to use WHILE….DO…END WHILE?
/*****
WHILE …DO
END WHILE
*****/
DELIMITER $$
DROP PROCEDURE IF EXISTS `db5`.`t3`$$
CREATE PROCEDURE `db5`.`t3`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ’string’*/
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES(v);
SET v = v + 1;
END WHILE;
END$$
DELIMITER ;

How to use REPEAT….UNTIL…END REPEAT?
/*****
REPEAT;
UNTIL … /*** No semicoloan ****/
END REPEAT;
*****/

DELIMITER $$
DROP PROCEDURE IF EXISTS `db5`.`t3`$$
CREATE PROCEDURE `db5`.`t3`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ’string’*/
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO t VALUES(v);
SET v = v + 1;
UNTIL v >= 5 /****** NEED TO CHECK THIS POINT *******/
END REPEAT;
END$$
DELIMITER ;

/*****
LOOP;

END LOOP;
*****/

How to use LOOP…END LOOP?

DELIMITER $$
DROP PROCEDURE IF EXISTS `db5`.`t3`$$
CREATE PROCEDURE `db5`.`t3`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ’string’*/
BEGIN
DECLARE v INT;
SET v = 0;
label_1: LOOP
INSERT INTO t VALUES(v);
SET v = v + 1;
IF v > 5 THEN
LEAVE label_1;
END IF;
END LOOP;

END$$
DELIMITER ;

/*****
ITERATE - Means start loop again.
LEAVE - Break out loop.
*****/