Category Covered

Mysql store procedure and function example

Here are some rough sample example of store procedure and mysql sample that will help you to understand

 

CALL albumItems;




DELIMITER $$

USE `areslog_shanghai`$$

DROP PROCEDURE IF EXISTS `albumItems`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `albumItems`()
BEGIN
	SELECT *, FnItemPartyAlbum(id) AS funcResults FROM areslog_shanghai.nite_parties;
    END$$

DELIMITER ;




DELIMITER $$
DROP FUNCTION IF EXISTS `FnItemPartyAlbum`$$
CREATE
    FUNCTION `areslog_shanghai`.`FnItemPartyAlbum`(pid INT(11))
    RETURNS INT DETERMINISTIC
    
    BEGIN
	DECLARE a INT;
	SELECT item_id INTO a FROM areslog_shanghai.nite_parties WHERE item_id=pid LIMIT 1;
	RETURN a;
    END$$

DELIMITER ;


	/*select count(*) into co from nite_parties where home=1 and item_id=pid order by date;
	SELECT item_id INTO a FROM areslog_shanghai.nite_parties WHERE item_id=pid LIMIT 1;*/
	
	
	

DELIMITER $$
DROP FUNCTION IF EXISTS `FnItemPartyAlbum`$$
CREATE
    FUNCTION `areslog_shanghai`.`FnItemPartyAlbum`(pid INT(11))
    RETURNS INT DETERMINISTIC
    
    BEGIN
	DECLARE a INT;
	DECLARE co INT;
	UPDATE nite_parties SET home=1 WHERE item_id=pid AND home != 1 ORDER BY DATE DESC LIMIT 2 ;
	/*select count(*) into co from nite_parties where home=1 and item_id=pid order by date;*/
	SELECT item_id INTO a FROM areslog_shanghai.nite_parties WHERE item_id=pid LIMIT 1;
	RETURN a;
    END$$

DELIMITER 




DELIMITER $$

USE `areslog_shanghai`$$

DROP PROCEDURE IF EXISTS `albumItems`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `albumItems`()
BEGIN
	/*select *, FnItemPartyAlbum(item_id) as funcResults from areslog_shanghai.nite_parties order by date desc;
	select count(id) as nid from nite_parties where home=1;
	update nite_parties set home=nid where id=id;*/	
	SELECT *, FnItemPartyAlbum(id) AS funcResults FROM areslog_shanghai.items;
    END$$

DELIMITER ;








DELIMITER $$

USE `areslog_shanghai`$$

DROP PROCEDURE IF EXISTS `albumItems`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `albumItems`()
BEGIN
	/*select *, FnItemPartyAlbum(item_id) as funcResults from areslog_shanghai.nite_parties order by date desc;
	select count(id) as nid from nite_parties where home=1;
	update nite_parties set home=nid where id=id;*/	
	SELECT *, FnItemPartyAlbum(id) AS funcResults FROM areslog_shanghai.items;
    END$$

DELIMITER ;








DELIMITER $$
DROP FUNCTION IF EXISTS `FnItemPartyAlbum`$$
CREATE
    FUNCTION `areslog_shanghai`.`FnItemPartyAlbum`(pid INT(11))
    RETURNS INT DETERMINISTIC
    
    BEGIN
	DECLARE a INT;
	DECLARE co INT;
	UPDATE nite_parties SET home=1 WHERE item_id=pid AND home != 1 ORDER BY DATE DESC LIMIT 2 ;
	/*select count(*) into co from nite_parties where home=1 and item_id=pid order by date;*/
	SELECT item_id INTO a FROM areslog_shanghai.nite_parties WHERE item_id=pid LIMIT 1;
	RETURN a;
    END$$

DELIMITER