三十、存储过程和事件调度

一、概念

存储过程，我的理解是写一段面向过程的包含sql的代码，然后编译存储到数据库。

之后再用时可以在数据库里直接执行，也可以通过事件来规律地重复调用，还可以在java等中进行调用。

二、基本语法

1.创建和删除

create proc[edure]  存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

[区块名：]  begin
.........
end  [区块名]

drop  proc[edure]  if  exists   存储过程名

2.变量定义和赋值

变量定义                 declare   变量名  类型   [default  默认值];

直接赋值                  set    变量名 = 表达式;

查询赋值                  select  运算值[,...]   into  变量名[,...]  from  表名  [where  表达式];

3.语句

（1）条件语句

if   条件  then
.........
else
.........
end if;

（2）循环语句

(1).相当于java的while循环
WHILE  表达式  DO

.........

END WHILE ;

(2).相当于java的for循环
 LOOP

.........

END LOOP;

(3).相当于java的do..while循环

 REPEAT

.........

UNTIL  表达式

END REPEAT ;

4.游标

我的理解游标是对一个数据集的操作，这个数据集主要可以用于循环的判断，这个集合中的值可以顺次的与其他值做比较判断。游标的作用类似于指针或者数组下标。

declare  游标名 cursor  [local|global]  [forward_only|scroll]    for    select查询语句

 实例：
drop procedure mig_page_visit_often;
DELIMITER $$
CREATE PROCEDURE mig_page_visit_often()
BEGIN
DECLARE done INT DEFAULT 0; 
DECLARE v_ip varchar(50);
DECLARE v_inWhite int(6);
DECLARE v_haveSend int(6);
DECLARE v_cnt int(6);
DECLARE cur CURSOR FOR select ip,count from ip_access where time>=current_date;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
OPEN cur; 
REPEAT FETCH cur INTO v_ip,v_cnt;
IF NOT done THEN 
select count(1) into v_inWhite from ip_white where ip=v_ip;
if v_inWhite=0 then
select count(1) into v_haveSend from ip_sms_log where ip=v_ip and time>=current_date;
if v_haveSend=0 then
insert into ip_sms_log(ip,count,time) values (v_ip, v_cnt,now());
end if;
end if;
END IF; 
UNTIL done
END REPEAT; 
CLOSE cur; 
END
$$

 
说明：
(1)DECLARE done INT DEFAULT 0;这个用于处理游标到达最后一行的情况
(2)DECLARE cur1 CURSOR FOR select distinct(ip) from ip_access where time>=current_date;声明游标
(3) DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;声明游标的异常处理，设置一个终止标记
(4)   OPEN cur1; 打开游标
        当我们刚刚打开一个游标时，它并不指向第一条记录，而是指向第一条记录的前边。以书做个形象的比喻，游标不仅可以只想记录集中的每条记录（书内容呢每一页），也可以指向记录集外部没有记录的地方（书的封面或封底）。@@fetch_status有三种取值：0表示fetch正常执行、-1表示fetch超出了结果集、-2表示fetch所指向的行已经不存在了。
(5)   FETCH cur1 INTO v_ip;读取一行数据到变量

        fetch  next  from  游标名  into  变量名列表；是固定形式的读取下一个游标位置的数据的方法。当查询语句选择了多个字段的时候，读取时就需要向多个变量赋值。

        当开启了scroll（可在行之间来回跳跃）选项后，fetch可以用于读取next后移、prior前移、first第一行、last最后一行、absolute以数值定位到绝对行、relative以数值定位到相对行。
(6)   UNTIL done判断是否游标已经到达了最后
 
(7)   CLOSE cur1;关闭游标
(8) COMMIT;运行没有异常，提交事务
(9)可使用begin， end在发生异常时执行多个命令，如可将DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;改成
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'  begin  SET done = 1 end;
(10)游标使用顺序的总结：声明游标， 打开游标，从游标里读取，关闭游标。

(11)DECLARE {EXIT|CONTINUE} HANDLER FOR {error_number|{SQLSTATE error-string}|condition} SQL Statement;

           MYSQL允许两种错误处理器（也就是当一段程序出错后自动触发的代码），一种是exit处理，另外一种是 continue处理。 DECLARE EXIT HANDLER是用来处理异常的，意思是如果错误发生，程序接下来会做的操作。与exit不同的是，在CONTINUE处理执行后，原主程序仍然继续运行。

 

5.说明

（1）DELIMITER分隔符：

mysql> DELIMITER // 
mysql> CREATE PROCEDURE proc1(OUT s int) 
    -> BEGIN
    -> SELECT COUNT(*) INTO s FROM user; 
    -> END
    -> // 
mysql> DELIMITER ;
 
注：这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

（2）参数

参数共有三种参数类型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:调用时指定，并且可被改变和返回

（3）MySQL存储过程的基本函数

(1).字符串类

CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格

(2).数学类

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数

(3).日期时间类

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时

三、存储过程的调用

1. 直接调用

call  存储过程名();

2. 事件调用

创建mysql事件。

如每5分钟调用一次上面的存储过程：

CREATE EVENT e_call_proc
    ON SCHEDULE
      EVERY 5 MINUTE
    DO CALL mig_page_visit_often();

3. java中调用

例如下面新建一个存储过程：

下面是java调用mysql存储过程的方法：

import java.sql.callablestatement;
import java.sql.connection;
import java.sql.drivermanager;
import java.sql.sqlexception;
import java.sql.types;
public class sqlutils {
    string url = "jdbc:mysql://127.0.0.1:3306/userinfo";
    string username = "root";
    string password = "password";
    public connection getconnection() {
        connection con=null;
        try{
            drivermanager.registerdriver(new com.mysql.jdbc.driver());
            con = drivermanager.getconnection(url, this.username, this.password);
        }catch(sqlexception sw){
         }
        return con;
    }
    public void testproc(){
        connection conn = getconnection();
        callablestatement stmt = null;
        try{
            stmt = conn.preparecall("{call mappingproc(?)}");   
            stmt.registeroutparameter(1, types.integer);
            stmt.execute();
            int i= stmt.getint(1);
            system.out.println("count = " + i);
        }catch(exception e){
            system.out.println("hahad = "+e.tostring());
        }finally{
            try {
                stmt.close();
                conn.close();
            }catch (exception ex) {
                system.out.println("ex : "+ ex.getmessage());
            }
        }
    }
    public static void main(string[] args) {
        new sqlutils().testproc();
    }
}