MySQL程式設計提示與樣例

日期間隔表示式
date_add(now(),interval expr type)

on duplicate key update no_id='xxx'

sql語句中流程函式
IF(value,t,f) 如果value是真，返回t；否則返回f
IFNULL(value1,value2) 如果value1不為空，返回value1；否則返回value2
case when [value1] then [result1]...else [default] end
case [expr] when [value1] then [result1]...else [default] end

found_row()：上一條sql語句返回的條目
變數賦值
set last_month_start=date_add(current_date(),interval 1 month)

delimiter $$
create procedure p_name()
begin
   declare continue handler for sqlstate '23000' set @x=1
end;
$$
delimiter ;

handler_type現在還只支援continue和exit兩種，continue表示繼續執行下面的語句，exit則表示執行停止，undo現在還不支援
condition_value的值可以是透過Declare定義的condition_name,可以是sqlstate的值或者mysql-error-code的值或者sqlwarning、not found、sqlexception,這3個值是3種定義好的類別

sqlwarning是對所有以01開頭的sqlstate程式碼速記
not found是對所有以02開頭的sqlstate程式碼速記
sqlexception是對所有沒有被sqlwarning或not found捕獲的sqlstate的程式碼速記

mysql-error-code：sql報錯後error後面緊跟的數值
sqlstate：sql報錯後括號中的數值

leave:相當於shell中的break
iterate：相當於shell中的continue

上面的程式碼可以改為
first:  declare continue handler for 1062 set @x=1
second: declare c_name condition for sqlstate '23000'
        declare continue handler for duplicatekey set @x=1
third:  declare continue handler for sqlexception set @x=1

delimiter $$
create procedure p_name()
begin
  declare i_staff_id int;
  declare d_amount decimal(5,2);
  declare cur_payment cursor for select staff_id,amount from payment;
  declare exit handler for not found close cur_payment;
 
  set @x1=0
  set @x2=0

  open cur_payment;
  repeat
    fetch cur_payment into i_staff_id,d_amount;
      if i_staff_id=2 then
          set @x1=@x1+d_amount;
      else
         set @x2=@x2+d_amount;
      end if;
  until 0 end repeat;
close cur_payment;
end;
$$
delimiter ;

自動回滾
create procedure sp_auto_rollback_demo()
begin
declare exit handler for sqlexception rollback;
start transaction
insert into b select 1;
insert into b select 2;
commit;
end;

自動回滾的加強型
create procedure sp_auto_rollback_demo()
begin
declare exit handler for sqlexception begin rollback; select -1;end;
start transaction
insert into b select 1;
insert into b select 2;
commit;
select 1;
end;


注：mysql沒有列印語句，只能在迴圈中加入insert語句，將遊標遍歷的值儲存下來；

delimiter $$
create procedure actor_insert()
begin
  set @x=0
  ins:LOOP
     set @x=@x+1;
     if @x=10 then
       leave ins;
     elseif mod(@x,2)=0 then
       iterate ins;
     end if;
     insert into actor(actor_id,first_name,last_name) values(@x+200,'Test',@x);
  end loop ins;
end;
$$
delimiter ;

while迴圈和repeat迴圈的區別在於，while是滿足條件才執行迴圈，repeat是滿足條件退出；
delimiter $$
create procedure loop_demo()
begin
  set @x=1,@x1=1;
  repeat
   set @x=@x+1;
  until @x>0 end repeat;
 
  while @x1<1 do
    set @x1=@x1+1;
  end while;
end;
$$

事件排程
create event test_event_2
on schedule at current_timestamp + interval 1 hour
do insert into test.test(id1,create_time) values('test',now());

create event test_event_1 on schedule every 5 second do insert into test.test(id1,create_time) values('test',now());
create event test_event_1 on schedule every 5 minute do insert into test.test(id1,create_time) values('test',now());
CREATE  EVENT `create5dayvol` ON SCHEDULE EVERY 1 DAY STARTS '2015-01-14 09:00:00' ON COMPLETION PRESERVE ENABLE DO CALL create_5vol()

show event\G
show variables like '%sche%';
+-----------------+---------------+
| Variable_name   | Value  |
+-----------------+---------------+
| event_scheduler | OFF   |
+-----------------+---------------+
set global event_scheduler=1;

alter event e_name disable;

觸發器
create trigger t_name
after(before) insert on t1
for each row
begin