mysql的存储过程基本使用

创建存储过程

CREATE PROCEDURE dataBackupForAllTable () BEGIN
...
END;

带参数传递(IN)

CREATE PROCEDURE dataBackupForOneDept (IN p_In int ) BEGIN

INSERT INTO his_user (ID,ORG_ID,AU_ID) 
SELECT 
ID,ORG_ID,AU_ID FROM user  WHERE ORG_ID = p_In;

END

查看数据库存储过程

 show procedure status;

查看创建过程

show create procedure dataBackupForAllTable；

调用不带参存储过程

 CALL dataBackupForAllTable();

调用带参存储过程

	set @p_In=1;/**--设置p_In 为1**/
	call poin(@p_In);/**--调用存储过程**/

删除存储过程

 drop procedure dataBackupForAllTable;

局部变量设置

CREATE PROCEDURE dataBackupForAllTable () BEGIN
SET @XX = YEAR(NOW());
SET @XXX = MONTH(NOW());

INSERT INTO his_user (ID,ORG_ID,AU_ID,XX,XXX) 
SELECT 
ID,ORG_ID,AU_ID,@XX,@XXX 
FROM user;
...
END;