1、无参存储过程语法
DROP FUNCTION IF EXISTS TEST_PROCEDURE;
CREATE PROCEDURE TEST_PROCEDURE() -- 创建存储过程
/*开始存储过程*/
BEGIN
/*自定义变量*/
DECLARE MY_ID VARCHAR(32);
DECLARE MY_NAME VARCHAR(50);
/*自定义控制游标循环变量,默认FALSE*/
DECLARE DONE INT DEFAULT FALSE;
/*定义游标并输入结果集*/
DECLARE MY_CURSOR CURSOR FOR ( SELECT ID, NAME FROM T_PEOPLE );
/*绑定控制变量到游标,游标循环结束自动转TRUE*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;
/*注意:一定要打开游标循环,上面CONTINUE那句语句才起作用*/
/*打开游标*/
OPEN MY_CURSOR;
/*开始循环体MY_LOOP为自定义循环名,结束循环时用到*/
MY_LOOP: LOOP
/*将游标当前读取行的数据顺序赋予自定义变量*/
FETCH MY_CURSOR INTO MY_ID, MY_NAME;
/*判断是否继续循环*/
IF DONE THEN
/*结束循环*/
LEAVE MYLOOP;
END IF;
/*自己要做的事情,在 SQL 中直接使用自定义变量即可*/
UPDATE T_USER SET C_NAME = MY_NAME WHERE ID = MY_ID AND RTRIM(LTRIM(C_NAME)) = '';
COMMIT;/*提交事务*/
END LOOP MY_LOOP;/*结束自定义循环体*/
CLOSE MY_CURSOR;/*关闭游标*/
END;/*结束存储过程*/
-- sql 执行存储过程
CALL TEST_PROCEDURE();
-- sql 删除存储过程
DROP PROCEDURE TEST_PROCEDURE;
2、有参(输入输出)存储过程
DROP PROCEDURE IF EXISTS `PRO_TEST_NAME`;
CREATE PROCEDURE PRO_TEST_NAME(IN ID INT,OUT NAME VARCHAR(50))
BEGIN
IF(ID = 1) THEN
SET NAME = 'TEST_0001';
END IF;
IF(ID = 2) THEN
SET NAME = 'TEST_0002';
END IF;
END;
DROP PROCEDURE IF EXISTS `PRO_INSERT_DATA`;
CREATE PROCEDURE PRO_INSERT_DATA(IN ID INT,IN NAME VARCHAR(10),IN CLASSNO INT,IN BIRTH DATETIME)
BEGIN
SET @ID = ID;
SET @NAME = NAME;
SET @CLASSNO = CLASSNO;
SET @BIRTH = BIRTH;
SET @CLASSNAME = NULL;
CALL PRO_TEST_NAME(@CLASSNO,@CLASSNAME);
SET @INSERTSQL = CONCAT('INSERT INTO TBL_STUDENT VALUES(?,?,?,?)');
PREPARE STMTINSERT FROM @INSERTSQL;
EXECUTE STMTINSERT USING @ID,@NAME,@CLASSNAME,@BIRTH;
DEALLOCATE PREPARE STMTINSERT;
END;
CALL PRO_INSERT_DATA(1,'XY',1,'2019-07-06 10:23:35');
在第二个存储过程中
①利用SET声明了参数,调用了第一个存储过程
②在第一个存储过程中的NAME参数是输出参数,所以@CLASSNAME这个参数在调用完第一个过程后就被附值
③最终利用CONCAT拼接SQL语句并传入参数执行SQL语句
调用存储过程 : CALL PRO_INSERT_DATA(1,'S-001',1,'2019-07-06 10:23:35');
3、存储过程例子
CREATE PROCEDURE proc_archive(in i_table_source varchar(40),
in i_table_target varchar(40),
in i_fieldname varchar(40),
in i_keepdays int,
in i_archdays int,
in i_other_cond varchar(500))
begin
/*
入参:
i_table_source:原表,含dbname
i_table_target:federated表
i_fieldname:时间字段
i_keepdays:保留天数
i_archdays:每次归档多少天数据
i_other_cond:数据额外条件(如status in (2,3)不能归档,需要保留),无额外条件则输入'1=1'
归档日志表archive_log.status字段含义:
0:成功, 1:现有数据在保留天数内, 2:目标表含有待归档时间范围的数据,
3:插入数据和删除数据记录数不同, 4:SQL执行异常,具体错误见remark
注意:
有额外条件时,如果历史数据被修改,从不符合归档条件变成符合归档条件,
因历史表中归档时间段内已经有之前归档的数据(@v_his_num_before>0),程序会退出,需手动处理
*/
declare EXIT HANDLER for SQLWARNING,NOT FOUND,SQLEXCEPTION
begin
GET DIAGNOSTICS CONDITION 1 @p1=RETURNED_SQLSTATE,@p2= MESSAGE_TEXT;
ROLLBACK;
insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
values(i_table_source,@v_arch_begin,@v_arch_end,4,@v_his_num_after,@v_del_num,concat('error ',@p1,' - ',@p2));
end;
/* 获取在线表的最小日期 */
set @mystmt = concat("select str_to_date(date_format(min(",i_fieldname,"),'%Y%m%d'),'%Y%m%d') into @v_arch_begin from ",i_table_source,' where ',i_other_cond);
prepare stmt from @mystmt;
execute stmt;
deallocate prepare stmt;
set @v_arch_end = date_add(@v_arch_begin,interval i_archdays day);
set @mystmt = concat("select count(*) into @v_his_num_before from ",i_table_target," where ",i_fieldname," >= ? and ",i_fieldname," < ?");
prepare stmt from @mystmt;
execute stmt using @v_arch_begin,@v_arch_end;
deallocate prepare stmt;
/* 如果在线表的数据低于keepday范围,退出 */
if timestampdiff(day,@v_arch_begin,now()) <= i_keepdays then
insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
values(i_table_source,@v_arch_begin,@v_arch_end,1,0,0,concat('error, all data in keey days, min ',i_fieldname,': ',@v_arch_begin));
end if;
/* 如果历史表所在的日期区间有数据,退出(需要手动排查原因) */
if @v_his_num_before <> 0 then
insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
values(i_table_source,@v_arch_begin,@v_arch_end,2,0,0,concat('error, data exists,row num:',@v_his_num_before));
end if;
if (timestampdiff(day,@v_arch_begin,now()) > i_keepdays and @v_his_num_before = 0) then
set @mystmt = concat("insert into ",i_table_target," select * from ",i_table_source," where ",i_fieldname," >= ? and ",i_fieldname," < ? and ",i_other_cond);
prepare stmt from @mystmt;
execute stmt using @v_arch_begin,@v_arch_end;
deallocate prepare stmt;
/* 因为federated引擎不支持事务,数据insert后再select下记录数,与下面的delete记录数对比,相同则提交delete操作 */
set @mystmt = concat("select count(*) into @v_his_num_after from ",i_table_target," where ",i_fieldname," >= ? and ",i_fieldname," < ?");
prepare stmt from @mystmt;
execute stmt using @v_arch_begin,@v_arch_end;
deallocate prepare stmt;
start transaction;
set @mystmt = concat("delete from ",i_table_source," where ",i_fieldname," >= ? and ",i_fieldname," < ? and ",i_other_cond);
prepare stmt from @mystmt;
execute stmt using @v_arch_begin,@v_arch_end;
set @v_del_num = row_count();
deallocate prepare stmt;
if @v_del_num = @v_his_num_after then
commit;
insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
values(i_table_source,@v_arch_begin,@v_arch_end,0,@v_his_num_after,@v_del_num,'success');
else
rollback;
insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
values(i_table_source,@v_arch_begin,@v_arch_end,3,@v_his_num_after,@v_del_num,'rollback, inserted rows num not equal to deleted rows num');
end if;
end if;
end;
3、有参(输入输出)存储过程
/**
* 1、数据迁移存储过程
*/
DROP PROCEDURE IF EXISTS PRO_DATA_MIGRATION;
CREATE PROCEDURE PRO_DATA_MIGRATION (
IN I_START_DATE VARCHAR (64),
IN I_END_DATE VARCHAR (64),
IN I_NAME_TABLES VARCHAR (5000),
OUT O_ERROR_MSG VARCHAR (1024)
)
BEGIN
/* 声明BODY内部操作变量 */
DECLARE I_START_DATE VARCHAR (64) DEFAULT '';
DECLARE I_END_DATE VARCHAR (64) DEFAULT '';
DECLARE I_NAME_TABLES VARCHAR (5000) DEFAULT '';
DECLARE T_ERROR_MSG VARCHAR (1024) DEFAULT '';
/* 自定义控制游标循环变量,默认FALSE */
DECLARE BOO_DONE INT DEFAULT FALSE;
SET T_ERROR_MSG = 'Factory IP is empty!!!';
SET BOO_DONE = TRUE;
IF BOO_DONE = TRUE THEN
SET T_ERROR_MSG = 'Factory IP is empty!!!';
END IF;
SET O_ERROR_MSG = T_ERROR_MSG;
END;
-- MySQL执行存储过程
CALL PRO_DATA_MIGRATION('2019-11-26 12:23:35','2019-12-26 12:23:35','TM_TEST',@O_ERROR_MSG);
SELECT @O_ERROR_MSG;