SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
存储过程是可编程的函数,在数据库中创建并保存,可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式。
创建
MySQL中,创建存储过程的基本形式如下:
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
- sp_name参数是存储过程的名称
- proc_parameter表示存储过程的参数列表
- characteristic参数指定存储过程的特性,默认是LANGUAGE SQL:说明routine_body部分是由SQL语言的语句组成
- routine_body参数是SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。
proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下:
[ IN | OUT | INOUT ] param_name type
- IN表示输入参数
- OUT表示输出参数
- INOUT表示既可以是输入,也可以是输出
- param_name参数是存储过程的参数名称
- type参数指定存储过程的参数类型,该类型可以是MySQL数据库的任意数据类型。
示例:
CREATE PROCEDURE `procdelhistory`(IN p_datefrom DATETIME)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_table VARCHAR(100);
DECLARE v_tablespace VARCHAR(100);
DECLARE v_prefix VARCHAR(64);
DECLARE v_keep_day INTEGER(10);
DECLARE partion_control CURSOR FOR
SELECT table_name,table_space,prefix,keep_day FROM partition_tab;
DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
INSERT INTO alarm_device_t(ALARM_OID,ALARM_TITLE,OCCUR_TIME,ALARM_CONTENT,ALARM_LEVEL,ALARM_TYPE,AGENT_TYPE)
VALUES('PROCEDURE','存储过程异常',DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'),'调用删除历史数据过程错误','4','存储过程','100');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN partion_control;
REPEAT
FETCH partion_control INTO v_table, v_tablespace, v_prefix, v_keep_day;
IF NOT done THEN
SELECT v_table, v_tablespace, v_prefix, v_keep_day;
CALL remove_history_script_proc(p_datefrom,v_tablespace,v_table,v_keep_day,v_prefix);
END IF;
UNTIL done END REPEAT;
CLOSE partion_control;
END
创建了一个名为procdelhistory的存储过程,它的作用是从partition_tab表查询要进行清理的信息,然后调用remove_history_script_proc执行清理。
其中
DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
定义了自定义异常处理:
- EXIT 表示遇到错误时马上退出
- SQLEXCEPTION,SQLWARNING,NOT FOUND 表示匹配所有异常
DECLARE 语句后面跟的INSERT INTO为异常时需要执行的sql语句。所以上面语句的意思为,遇到异常时,alarm_device_t插入异常信息,然后退出程序。