Home | 简体中文 | 繁体中文 | 杂文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 作品与服务 | Email

40.7. 存储过程(PROCEDURE)

40.7.1. 存储程序

存储过程没有返回数据,需使用call proc()调用

CREATE DEFINER=`neo`@`%` PROCEDURE `angelfund`(IN `puid` VARCHAR(50), IN `ptime` DATETIME)
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN

	DECLARE fusername VARCHAR(16) DEFAULT NULL;
	DECLARE fname VARCHAR(16) DEFAULT NULL;
	DECLARE fmembers_date VARCHAR(20) DEFAULT NULL;

	SELECT username,name,FROM_UNIXTIME(createtime) INTO fusername,fname,fmembers_date FROM members WHERE username = puid;

	IF fusername IS NOT NULL THEN
		INSERT IGNORE INTO angelfund(username,name,members_date,accounts_date,endtime,`status`,op,operator,`description`) value(fusername,fname,fmembers_date,ptime,DATE_ADD(ptime, INTERVAL +1 MONTH),'N','N','computer','');
	END IF;

END			
			

调用过程

call angelfund('100','2013-10-10 10:10:10');			
			

40.7.2. 函数

函数会返回数据,调用函数使用 select fun(),不能使用call调用,否则提示

			
mysql> call myfun();
ERROR 1305 (42000): PROCEDURE test.myfun does not exist			
			
			

下面做一个实验

CREATE TABLE `t` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`n` INT(11) UNSIGNED NULL DEFAULT '0',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5;

CREATE DEFINER=`neo`@`%` FUNCTION `myfun`()
	RETURNS int(11)
	LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	INSERT INTO t (n) VALUES(rand()*100);
	RETURN LAST_INSERT_ID();
END			
			
			
mysql> select myfun();
+---------+
| myfun() |
+---------+
|       9 |
+---------+
1 row in set, 2 warnings (0.07 sec)
			
			
comments powered by Disqus