一个最简单的MySQL存储过程例子:
DELIMITER $$
CREATE PROCEDURE mypro (IN $id INT)
BEGIN
SET @SQL = CONCAT('select * from job_queue where id=', $id);
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;
$$
DELIMITER ;
SQLyog工具生成的代码例子:
DELIMITER $$
USE `archive_lib`$$
DROP PROCEDURE IF EXISTS `test_proc`$$
CREATE DEFINER=`root`@`%` PROCEDURE `test_proc`($tableName VARCHAR(20))
BEGIN
SET @SQL = CONCAT('select * from ', $tableName);
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;
DELIMITER $$
USE `archive_lib`$$
DROP PROCEDURE IF EXISTS `pro_insert`$$
CREATE DEFINER=`root`@`%` PROCEDURE `pro_insert`(IN $barcode VARCHAR(15),IN $title VARCHAR(100),OUT $test VARCHAR(20))
BEGIN
SET @SQL = CONCAT('insert into job_queue (barcode,title)values(',QUOTE($barcode),',',QUOTE($title),')');
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET $test='Hello World!';
END$$
DELIMITER ;
调用:
CALL pro_insert('L--3', '沃尔玛都能散发阿斯蒂芬',@test);
SELECT @test;
函数的一个简单例子:
DELIMITER $$
USE `archive_lib`$$
DROP FUNCTION IF EXISTS `hello`$$
CREATE DEFINER=`root`@`%` FUNCTION `hello`(s CHAR(20)) RETURNS CHAR(50) CHARSET utf8
BEGIN
DECLARE str VARCHAR(20);
SELECT title INTO str FROM job_queue WHERE ID=0 LIMIT 1;
IF str IS NULL THEN
RETURN '';
ELSE
RETURN CONCAT('Hello, ',s,'!',str);
END IF;
END;
$$
DELIMITER ;
如果觉得《设计mysql存储过程 MySQL的存储过程设计的例子》对你有帮助,请点赞、收藏,并留下你的观点哦!