Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

11.7. 存儲過程(PROCEDURE)

11.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');			
			

11.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)