mysql 函数延时_mysql定义异常/表不存在异常/延时函数/now()-sysdate()

delimiter $$

drop procedure if exists lims_data.sp_test_100$$

create procedure lims_data.sp_test_100(

outcur_time1bigint unsigned,

outcur_time2bigint unsigned,

out cntint unsigned

)

begin

declare _sleepint unsigned default 0;

declare continue handler for 1146

begin

end;

select now() + 0 into cur_time1;

select count(*) into cnt from lims_data.abscafase;

while cur_time1 + 5 >= (now() + 0) do

set _sleep = SLEEP(1);

end while;

select now() + 0 into cur_time2;

end $$

delimiter ;

call lims_data.sp_test_100(@a, @b, @c);

select @a, @b, @c;

select sysdate(), now(), sleep(1), now(), sysdate();

总结:

1:异常可以由多种值来触发

DECLARE handler_action HANDLER

FOR condition_value [, condition_value] ...

statement

handler_action:

CONTINUE

| EXIT

| UNDO

condition_value:

mysql_error_code

| SQLSTATE [VALUE] sqlstate_value

| condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

2:可以定义多个continue handler 但是 对于一个触发条件只能定义一个handler

3:set _sleep = SLEEP(1); 延时函数 以前用的比较少 要它时还真不能少

4:now()和sysdate()都是显示当前时间

区别:now()显示执行开始的时间

sysdate()显示当前执行时间

eg:  如果一个sql块执行需要10s   now()显示的是10s前的时刻 而sysdate()显示的是当前时刻