22.7. 存储过程(PROCEDURE)

存储过程没有返回数据，需使用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');			
			

在过程中运行SQL，下面的例子是文件导出的例子。

			
DROP procedure IF EXISTS `export_file`;

DELIMITER $$
CREATE DEFINER=`dba`@`%` PROCEDURE `export_file`(IN file_name char(64), IN tabname char(64))
BEGIN
	set @sql = concat('SELECT * INTO OUTFILE ',"'/var/lib/mysql-files/",file_name,"'",' FROM ', tabname) ; 
    -- select @sql;
	PREPARE stmt FROM @sql; 
	EXECUTE stmt; 
	Deallocate prepare stmt;
END$$

DELIMITER ;
			
			

call 存储过程

			
call test.export_file('test', 'mytable');			
			
			

			
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)

mysql> 			
			
			

			
USE `test`;
DROP procedure IF EXISTS `test`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`dba`@`%` PROCEDURE `test`(in a int, in b int ,out num int)
BEGIN

	set num = a + b;

END$$

DELIMITER ;



			
			

运行后返回结果 10

			
set @num = 0;
call test(3,7,@num);
select @num;
			
			

			
			
USE `netkiller`;
DROP procedure IF EXISTS `table2json`;

DELIMITER $$
USE `netkiller`$$
CREATE DEFINER=`neo`@`%` PROCEDURE `table2json`(
IN `schema` VARCHAR(32), 
IN `table` VARCHAR(32), 
IN `id` VARCHAR(10), 
OUT rev VARCHAR(1024)
)
BEGIN
	SET @column = NULL;
	SET @str = NULL;

	SELECT 
    GROUP_CONCAT(fields) AS col INTO @column FROM (
		SELECT 
			CONCAT('"', COLUMN_NAME, '",', COLUMN_NAME) AS fields
		FROM
			INFORMATION_SCHEMA.Columns
		WHERE
			table_name = `table`
				AND table_schema = `schema`) AS tmptable;


	-- SELECT @column;


	SET @sql = CONCAT('SELECT json_object(',@column, ' ) as json INTO @str FROM ', `table`,' where id = ', `id`);

	-- SELECT @sql;

	PREPARE stmt FROM @sql; 
	EXECUTE stmt; 
	Deallocate prepare stmt;

	set rev = @str;

END$$

DELIMITER ;			
			
			

使用实例

			
set @rev = '0';
call netkiller.table2json('test', 'test', '1', @rev);
select @rev;
			
			

			
USE `netkiller`;
DROP procedure IF EXISTS `trigger2json`;

DELIMITER $$
USE `netkiller`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `trigger2json`(
IN `schema` VARCHAR(32), 
IN `table` VARCHAR(32), 
OUT rev VARCHAR(1024)
)
BEGIN
	SET @column = NULL;
	SET @str = NULL;

	SELECT 
    GROUP_CONCAT(fields) AS col
INTO @column FROM
    (SELECT 
        CONCAT('"', COLUMN_NAME, '", NEW.', COLUMN_NAME) AS fields
    FROM
        INFORMATION_SCHEMA.Columns
    WHERE
        table_name = `table`
            AND table_schema = `schema`) AS tmptable;


-- SELECT @column;


	SET @sql = CONCAT('SELECT json_object(',@column, ' ) as json INTO @str ');

	-- SELECT @sql; 

	PREPARE stmt FROM @sql; 
	EXECUTE stmt; 
	Deallocate prepare stmt;

	set rev = @str;

END$$

DELIMITER ;


			
			

			
set @rev = '0';
call neo.trigger2json('gw', 'member', @rev);
select @rev;