復合語句

標簽

[begin_label:] BEGIN
    [statement_list]
END [end_label]

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]
  • begin ... end，loop鹊汛，repeat刁憋，while允許使用標簽
  • 標簽的規(guī)則如下：

    • begin_label后面必須跟著:

    • begin_label可以不帶end_label，如果end_label存在尘颓，則其必須與begin_label一樣

      create procedure Fun0()
begin
end;

create procedure Fun1()
szn : begin
end szn;

create procedure Fun2()
szn : begin
end;

    • end_label若存在疤苹，必須匹配begin_label

    • 標簽最多可以包含16個字符

begin ... end

[begin_label:] BEGIN
    [statement_list]
END [end_label]
  • begin ... end用于編寫復合語句惫皱，其可以出現(xiàn)在
    • 存儲過程stored procedures
    • 函數(shù)functions
    • 觸發(fā)器triggers
    • 事件evnets
  • begin ... end是可以嵌套的

declare

  • declare語句可以用于定義：
    • 本地變量
    • conditions
    • handlers
    • 游標
  • declare只能在begin ... end中使用旅敷，且必須位于任何其他語句之前
  • declare的順序：
    • 變量和conditions
    • 游標
    • handler

存儲程序中的變量

  • 使用declare定義本地變量

    DECLARE var_name [, var_name] ... type [DEFAULT value]
    • 上述語句在存儲程序中定義本地變量
    • 可以包含default賦予變量默認值，這個默認值可以是一個表達式
    • 若無default則變量的默認值是null
    • 本地變量的生命周期在定義其的begin ... end塊內(nèi)
    delimiter $$

create procedure Fun()
begin
  declare a int default 10;
  select a;

  begin 
      select a;
  end;

  begin 
      declare a int default 20;
      select a;
  end;

end $$

delimiter ;

call Fun();
/*
a
10

a
10

a
20
*/

  • 使用set設(shè)置本地變量

  • 將查詢結(jié)果賦值給本地變量

    • select ... into var_list

    • 打開游標韩脑，使用fetch ... into var_list

流程控制語句

  • mysql支持存儲程序中使用if首量，case加缘，iterate拣宏，leave宋下，loop学歧，while枝笨，repeat
  • 函數(shù)中支持return
  • 不支持for

case

#值匹配
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

#條件匹配
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE
  • case匹配失敗，且不包含else子句徙融，則會報錯
  • 處理when沒有匹配的情況张咳，此時else可以包含一個空的begin ... end 
delimiter $$

create procedure Fun(in v0 int, out v1 int, out v2 int)
begin
    case
        when v0 > 0 then
            set v1 = v0 + 1;
            set v2 = v0 + 2;
        when v0 < 0 then
            set v1 = v0 - 1;
            set v2 = v0 - 2;
        else
            set v1 = v0;
            set v2 = v0;
    end case;
end $$

delimiter ;

call Fun(-1, @a0, @a1);
call Fun(1, @b0, @b1);
call Fun(0, @c0, @c1);
select @a0, @a1, @b0, @b1, @c0, @c1;
/*
@a0     @a1     @b0     @b1     @c0     @c1
-2      -3      2       3       0       0
*/

delimiter $$

create procedure Fun(in v0 int, out v1 int, out v2 int)
begin
    case v0
        when 1 then
            set v1 = v0 + 1;
            set v2 = v0 + 2;
        when -1 then
            set v1 = v0 - 1;
            set v2 = v0 - 2;
        else
            set v1 = v0;
            set v2 = v0;
    end case;
end $$

delimiter ;

call Fun(-1, @a0, @a1);
call Fun(1, @b0, @b1);
call Fun(0, @c0, @c1);
select @a0, @a1, @b0, @b1, @c0, @c1;
/*
@a0     @a1     @b0     @b1     @c0     @c1
-2      -3      2       3       0       0
*/

delimiter $$

create procedure Fun(in v0 int)
begin
    case
        when v0 > 0 then
            begin 
            end;
        else
            begin 
            end;
    end case;
end $$

delimiter ;

call Fun(1);

if

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

delimiter $$

create procedure Fun(in v0 int, out v1 int, out v2 int)
begin
    if v0 > 0 then 
        set v1 = v0 + 1;
        set v2 = v0 + 2;
    elseif v0 < 0 then 
        set v1 = v0 - 1;
        set v2 = v0 - 2;
    else 
        set v1 = v0;
        set v2 = v0;
    end if;
end $$

delimiter ;

call Fun(-1, @a0, @a1);
call Fun(1, @b0, @b1);
call Fun(0, @c0, @c1);
select @a0, @a1, @b0, @b1, @c0, @c1;
/*
@a0     @a1     @b0     @b1     @c0     @c1
-2      -3      2       3       0       0
*/

iterate

ITERATE label
  • 只能出現(xiàn)在loop龙助，repeat提鸟，while中称勋，表示再次開始循環(huán)

leave

LEAVE label
  • 退出具有指定標簽的控制
  • 可以在begin ... end空厌，loop嘲更，repeat赋朦，while中使用

loop

[begin_label:] LOOP
    statement_list
END LOOP [end_label]
  • loop實現(xiàn)循環(huán)，通常通過leave來退出循環(huán)琳拨，在函數(shù)中也可以使用return來退出
delimiter $$

create procedure Fun()
begin
    declare v int default 3;
    
    szn:loop
        select v;
        set v = v - 1;
        
        if v > 0 then 
            iterate szn;
        end if;
        
    leave szn;
    end loop;
    
end $$

delimiter ;

call Fun();
/*
v
3

v
2

v
1
*/

drop procedure if exists Fun;

delimiter $$

create procedure Fun()
begin
    declare v int default 3;

    szn:loop
        select v;
        set v = v - 1;
    end loop;

end $$

delimiter ;

call Fun();
#死循環(huán)，將一直進行輸出

repeat

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

delimiter $$

create procedure Fun()
begin
    declare v int default 3;
    
    repeat 
        select v;
        set v = v - 1;
    until v < 0 #注意密任，此處不能有;
    end repeat;
    
end $$

delimiter ;

call Fun();
/*
v
3

v
2

v
1
*/

return

RETURN expr
  • 用于終止函數(shù)的執(zhí)行，并將返回值返回給函數(shù)調(diào)用者
  • 函數(shù)中至少要有一個return語句
  • 存儲過程淹遵、觸發(fā)器济炎、事件中不應該使用return

while

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

delimiter $$

create procedure Fun()
begin
    declare v int default 3;
 
    while v > 0 do 
        select v;
        set v = v - 1;
    end while;
    
end $$

delimiter ;

call Fun();
/*
v
3

v
2

v
1
*/

游標

#聲明一個游標须尚，并將其與一個select關(guān)聯(lián)
DECLARE cursor_name CURSOR FOR select_statement

#打開已聲明的游標
OPEN cursor_name

#獲取下一行數(shù)據(jù)
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

#關(guān)閉游標
CLOSE cursor_name

  • mysql支持在stored programs中使用游標，游標具有以下屬性

    • Asensitive: The server may or may not make a copy of its result table
    • Read only: Not updatable
    • Nonscrollable: Can be traversed only in one direction and cannot skip rows

  • 已打開的游標撩轰，使用fetch來獲取關(guān)聯(lián)的select的下一行數(shù)據(jù)

    • If no more rows are available, a No Data condition occurs with SQLSTATE value '02000'. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition)

  • 只有已經(jīng)被打開的游標蜡豹，才能被關(guān)閉镜廉。如果游標沒有顯式關(guān)閉齐遵，則會在begin ... end塊末尾自動關(guān)閉

  • 服務(wù)器端游標允許在服務(wù)器端生成結(jié)果集梗摇，并只傳輸客戶端請求的行。服務(wù)器端游標被實現(xiàn)為內(nèi)部的臨時表糜烹，當其大小小于max_heap_table_size和tmp_table_size的最小值時疮蹦，此臨時表為memory table阵苇，否則是myisam table慎玖，所以對于大型結(jié)果集笛粘，使用游標可能會很慢

    show variables like 'max_heap_table_size'
#16MB

show variables like 'tmp_table_size'
#39MB

show variables like 'version'
#8.0.17

游標使用示例

create table t(v0 int, name varchar(100));
insert into t(v0, name) values(0, "0"), (1, "1"), (2, "2"), (3, "3");

drop procedure if exists Fun;

delimiter $$

create procedure Fun(out vOut int)
begin 
    declare d int default false;
    declare v int;
    declare n varchar(100);
    
    declare c cursor for select v0, name from t;
    declare continue handler for not found set d = true;
    
    open c;

    create table if not exists tOut (name varchar(100));
    truncate tOut;

    set vOut = 0;
    
    szn:while true do
        fetch c into v, n;
        
        if d then 
            leave szn;
        end if;

        if v % 2 then 
            set vOut = vOut + v;
            insert into tOut(name) values(n);
        end if;
    end while;
    
    close c;
end $$

delimiter ;

set @v = 0;
call Fun(@v);
select @v;
/*
@v
4
*/

select * from tOut;
/*
name
1
3
*/

條件(Condition) 處理(Handling)

  • stored program執(zhí)行中可能會出現(xiàn)需要特殊處理(Handle)的條件(Condition)趁怔，例如退出當前程序塊或繼續(xù)執(zhí)行。handler可以應付如下Condition：
    • 普通條件薪前，比如警告和異常
    • 指定的條件润努，比如一個具體的錯誤代號。指定的條件可以命名示括，并且在handler中進行引用

條件聲明

DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

  • declare ... condition語句聲明了一個錯誤情況，并進行命名垛膝，這個名字可以在declare ... handler中進行引用

  • 上述condition_value具有以下形式：

    • mysql_error_code：整數(shù)數(shù)字鳍侣，指示mysql錯誤碼。不應該為0吼拥，因為0代表正確

    • SQLSTATE [VALUE] sqlstate_value：5個字符組成的字符串倚聚，指示mysql錯誤，不要使用00開頭的值凿可，因為其代表正確

  • 使用命名的條件惑折，使得程序更加的清晰可讀。兩種條件聲明示例：

    drop procedure if exists Fun;

delimiter $$

create procedure Fun()
begin 

  declare table_is_not_exist_0 condition for 1146;
  declare table_is_not_exist_1 condition for sqlstate '42S02';

end $$

delimiter ;

聲明處理

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
}

  • declare ... handle語句聲明了一個handle枯跑，用于指定處理一個或多個情況惨驶。如果其中的一個情況發(fā)生，那么指定的statement將被執(zhí)行敛助，其中statement可以是set var_name = value這樣的簡單語句粗卜，也可以是begin ... end這樣的復合語句

  • handler_action表示statement執(zhí)行后的動作，如下：

    • condition：繼續(xù)執(zhí)行當前程序
    • exit：表示退出聲明handle時所處的begin ... end語句塊
    • undo：不支持

  • condition_value表示用于激活handle的指定條件或者是一類條件：

    • mysql_error_code：mysql錯誤碼
    • SQLSTATE [VALUE] sqlstate_value：5個字符組成的字符串纳击，指示mysql錯誤
    • condition_name：聲明的條件名稱
    • SQLWARNING：以01開頭的sqlstate的簡寫
    • NOT FOUND：以02開頭的sqlstate的簡寫续扔。在游標上下文中，如果沒有跟多行可用评疗，會觸發(fā)此錯誤
    • SQLEXCEPTION：不以00测砂，01，02開頭的sqlstate的簡寫

  • 當Condition被觸發(fā)百匆，且沒有對應的handle與之關(guān)聯(lián)砌些，則

    • 對于SQLEXCEPTION：

      • stored program在觸發(fā)對應條件處退出，就像在此處有定義了exit的handler一樣
      • 如果程序是在另一個stored program中調(diào)用，那么由調(diào)用者的handle的規(guī)則去處理
      drop procedure if exists Fun;
delimiter $$

create procedure Fun()
begin 

    signal sqlstate '03001';
    select "szn";

end $$

delimiter ;
call Fun();
#error 1644(03001): unhandled user-defined exception condition

      drop procedure if exists Fun0;
drop procedure if exists Fun1;
delimiter $$

create procedure Fun0()
begin 

    signal sqlstate '03001';
    select "Fun0";

end $$

create procedure Fun1()
begin 

    declare continue handler for sqlexception begin end;
    call Fun0();
    select "szn";

end $$

delimiter ;
call Fun1();
/*
szn
---
szn
*/

call Fun0();
#error 1644(03001): unhandled user-defined exception condition

    • 對于SQLWARNING存璃，程序?qū)⒗^續(xù)執(zhí)行仑荐，就像此處有定義了continue的handler一樣

      drop procedure if exists Fun;
delimiter $$

create procedure Fun()
begin 

    signal sqlstate '01001';
    select "szn";

end $$

delimiter ;
call Fun();
/*
szn
---
szn
*/

    • 對于NOT FOUND：

      • 如果條件是被正常觸發(fā)的，則表現(xiàn)為continue
      • 如果條件是被signal或者resignal觸發(fā)的纵东，則表現(xiàn)為exit 
      drop procedure if exists Fun;
delimiter $$

create procedure Fun()
begin 

    declare v0 int;
    declare c cursor for select v from t;

    drop table if exists t;
    create table t(v int);
    insert into t(v) values(1);

    open c;
    fetch c into v0;
    select v0;

    fetch c into v0;
    select "szn";
    
    close c;

end $$

delimiter ;
call Fun();
/*
v0
1

error 1329 (02000): no data - zero rows fetched, selectd, or processed
*/

      drop procedure if exists Fun;
delimiter $$

create procedure Fun()
begin 

    signal sqlstate '02000';
    select "szn";

end $$

delimiter ;
call Fun();
#error1643 (02000): unhandled user-defined not found condition

  • 外部label不能在handler中使用粘招。所以statement中，不允許使用leave或者iterate偎球，來引用外層的label

    drop procedure if exists Fun;

delimiter $$

create procedure Fun()
szn : begin 

  declare continue handler for not found 
  begin
  #   leave szn;      非法
  #   iterate szn;    非法
  end;

end $$

delimiter ;

  • 示例：

    drop procedure if exists Fun;

delimiter $$

create procedure Fun()
begin 

  declare table_is_not_exist_0 condition for 1146;

  declare continue handler for table_is_not_exist_0
  begin
      select "Error : Table Not Found";
  end;

  select * from t;

end $$

delimiter ;

call Fun();
/*
若表t存在洒扎，則輸出表t的內(nèi)容，否則輸出Error : Table Not Found
*/

Signal

SIGNAL condition_value
    [SET signal_information_item
    [, signal_information_item] ...]

condition_value: {
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
}

signal_information_item:
    condition_information_item_name = simple_value_specification

condition_information_item_name: {
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
}

  • signal用于主動引發(fā)一個錯誤衰絮，并提供給handler袍冷、外部程序、客戶端

  • signal中的condition_value指明了返回的錯誤猫牡，condition_value可以是以下值：

    • sqlstate值胡诗，包含5個字符
    • 使用declare ... condition聲明的condition的名字。此condition必須以sqlstate定義淌友，而不能以mysql_error_code定義(參見前面的condition的兩種定義方式)

  • signal聲明語句中包含可選的多個set語句

    • condition_information_item_name列表中包含了可以被set的值

      Item Name             Definition
---------             ----------
CLASS_ORIGIN          VARCHAR(64)
SUBCLASS_ORIGIN       VARCHAR(64)
CONSTRAINT_CATALOG    VARCHAR(64)
CONSTRAINT_SCHEMA     VARCHAR(64)
CONSTRAINT_NAME       VARCHAR(64)
CATALOG_NAME          VARCHAR(64)
SCHEMA_NAME           VARCHAR(64)
TABLE_NAME            VARCHAR(64)
COLUMN_NAME           VARCHAR(64)
CURSOR_NAME           VARCHAR(64)
MESSAGE_TEXT          VARCHAR(128)
MYSQL_ERRNO           SMALLINT UNSIGNED
      • 以上使用的字符集是utf8
      • 以上值不允許設(shè)為null

    • 同一個變量不允許set多次

    • simple_value_specification即被設(shè)置的變量的值煌恢，可以是

      • stored procedure的參數(shù)
      • 函數(shù)的參數(shù)
      • stored procedure中定義的本地變量
      • 用戶定義的變量
      • 系統(tǒng)變量
      • 字面量
      drop procedure if exists Fun;

delimiter $$

create procedure Fun(in v int)
begin 
    
    declare table_is_not_exist condition for sqlstate '42S02';

    if v = 1 then 
        signal sqlstate '42S03';
        
    elseif v = 2 then 
        signal sqlstate '42S04'
            set column_name = '1',
                table_name = '2';
                    
    else 
        signal table_is_not_exist;
        
    end if;

end $$

delimiter ;

call Fun(0);
#error 1644 (42S02):Unhandled user-defined exception condition

call Fun(1);
#error 1644 (42S03):Unhandled user-defined exception condition

call Fun(2);
#error 1644 (42S04):Unhandled user-defined exception condition

  • signal一般在stored program中使用，但他是mysql擴展震庭，也可以在處理程序上下文中使用

    signal sqlstate '42S03';
##error 1644 (42S03):Unhandled user-defined exception condition

  • signal在存儲過程中使用的例子：

    drop procedure if exists Fun0;
drop procedure if exists Fun1;
drop procedure if exists Fun2;


delimiter $$


create procedure Fun0()
begin 
  signal sqlstate value '42S02';
end $$


create procedure Fun1()
begin 

  declare exit handler for sqlexception 
  begin 
  end;
  
  signal sqlstate value '42S02';

end $$


create procedure Fun2()
begin 

  declare exit handler for sqlexception 
  begin 
      signal sqlstate value '99999'
          set message_text = 'szn';
  end;
  
  signal sqlstate value '42S02';

end $$


delimiter ;


call Fun0();
#error 1644(42S02)： unhandled user-defined exception condition

call Fun1();
#無錯誤輸出

call Fun2();
#error 1644(99999): szn

    drop procedure if exists Fun;

delimiter $$

create procedure Fun()
begin 

  declare exit handler for sqlstate '99999'
  begin 
      select "szn";
  end; 

  declare continue handler for sqlstate '42S02' 
  begin 
      signal sqlstate value '99999'
          set message_text = 'szn';
  end;
  
  signal sqlstate value '42S02';

end $$


delimiter ;

call fun();
#error 1644(99999):szn

  • condition_value總是一個sqlstate值瑰抵，要么是直接指定，要么是通過condition間接指定归薛。sqlstate的前兩個字符指明了其類別：

    • '00'：表示成功谍憔，不允許指定此值

    • '01'：

      • 表示警告
      • show warnings可顯示此signal
      • sqlwarning handler可以捕捉這個信號
      #此類 condition_information_item_name 中值的默認值
MESSAGE_TEXT = 'Unhandled user-defined warning condition';
MYSQL_ERRNO = ER_SIGNAL_WARN

    • '02'：表示not found

      • not found handler可以捕捉此信號

      • 對游標無影響

        drop table if exists t;
create table t(v0 int);
insert into t(v0) values(1), (2), (3);


drop procedure if exists Fun;

delimiter $$

create procedure Fun(out vOut int)
begin 
  declare v int;
  declare c cursor for select v0 from t;

  declare continue handler for not found 
  begin 
      select "szn";
  end;
  
  
  open c;
  set vOut = 0;
  
  fetch c into v;
  set vOut = vOut + v;
  signal sqlstate '02000';
      
  fetch c into v;
  set vOut = vOut + v;    
  
  close c;
end $$

delimiter ;

set @v = 0;
call Fun(@v);
#輸出szn

select @v;
#3

      • 若此類型信號未進行捕獲，則立刻結(jié)束語句塊

      #此類 condition_information_item_name 中值的默認值
MESSAGE_TEXT = 'Unhandled user-defined not found condition';
MYSQL_ERRNO = ER_SIGNAL_NOT_FOUND

    • 大于'02'：表示異常

      • sqlexception可以捕獲此信號
      • 若此類型信號未進行捕獲主籍，則立刻結(jié)束語句塊
      #此類 condition_information_item_name 中值的默認值
MESSAGE_TEXT = 'Unhandled user-defined exception condition';
MYSQL_ERRNO = ER_SIGNAL_EXCEPTION

  • 在調(diào)用了signal后

    • C API
      • mysql_sqlstate()：返回sqlstate值
      • mysql_errno()：返回MYSQL_ERROR和MESSAGE_TEXT
    • Sql語句：show warnings和show errors指明了MYSQL_ERROR和MESSAGE_TEXT

Resignal

RESIGNAL [condition_value]
    [SET signal_information_item
    [, signal_information_item] ...]

condition_value: {
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
}

signal_information_item:
    condition_information_item_name = simple_value_specification

condition_information_item_name: {
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
}

  • 在condition handler中，resignal傳遞可更改的error condition

    • signal是創(chuàng)建error condition逛球，而resignal是依賴已有的error condition并能進行更改

  • resignal和signal的condition_value及signal_information_item的規(guī)則是相同的

  • resignal的condition_value和set子句都是可選的

單獨的Resignal

  • 單獨的resignal意味著僅傳遞錯誤千元，不進行更改
drop procedure if exists Fun;
drop table t if exists;


delimiter $$

create procedure Fun()
begin 
    
    declare continue handler for sqlexception 
    begin 
        select "szn0";
        resignal;
        select "szn1";
    end;
    
    drop table t;
    
end $$

delimiter ;

call Fun();
/*
szn0
----
szn0

error 1051 (42S02):unknow table 'd0.t'
*/

show errors;
/*
Level   Code    Message
----    ----    -------------------
Error   1051    unknow table 'd0.t'
*/

drop table t;
#error 1051 (42S02):unknow table 'd0.t'

僅更改signal_information_item的Resignal

drop procedure if exists Fun;
drop table t if exists;


delimiter $$

create procedure Fun()
begin 

    declare continue handler for sqlexception 
    begin 
        select "szn";
        resignal
            set mysql_errno = 5,
                message_text = "hello";
    end;
    
    drop table t;
    
end $$

delimiter ;

call Fun();
/*
szn
---
szn

error 5 (42S02):hello
*/

show errors;
/*
Level   Code    Message
----    ----    -------------------
Error   5       hello
*/

drop table t;
#error 1051 (42S02):unknow table 'd0.t'

更改condition_value及可選的signal_information_item的Resignal

drop procedure if exists Fun;
drop table t if exists;


delimiter $$

create procedure Fun()
begin 

    declare continue handler for sqlexception 
    begin 
        select "szn";
        resignal sqlstate '04123'
            set mysql_errno = 5,            #可選
                message_text = "hello";     #可選
    end;
    
    drop table t;
    
end $$

delimiter ;

call Fun();
/*
szn
---
szn

error 5 (04123):hello
*/

show errors;
/*
Level   Code    Message
----    ----    -------------------
Error   1051    unknow table 'd0.t'
Error   5       hello
*/

drop table t;
#error 1051 (42S02):unknow table 'd0.t'

Handler的范圍規(guī)則

? stored program可以包含多個handler，當特定的條件在program中發(fā)生時颤绕，進行調(diào)用幸海。hanlder的適用性取決于其自身定義的位置以及其關(guān)聯(lián)的condition：

  • begin ... end中declare的handler，其范圍只適用于begin ... end塊剩余的sql語句奥务。如果handler自身觸發(fā)了condition物独，其自身無法處理這個condition，并且begin ... end塊內(nèi)的任何其他handler也無法處理

    BEGIN -- outer block
  DECLARE EXIT HANDLER FOR ...;  -- handler H1
  DECLARE EXIT HANDLER FOR ...;  -- handler H2
  stmt1;
  stmt2;
END;
#H1和H2適用于由stmt1和stmt2觸發(fā)的condition氯葬，但是無論是H1還是H2都無法處理在H1和H2內(nèi)部觸發(fā)的condition

  • handler的僅適用于declare其本身的begin ... end塊內(nèi)挡篓，無法處理外部引發(fā)的condition

    BEGIN -- outer block
  BEGIN -- inner block
    DECLARE EXIT HANDLER FOR ...;  -- handler H1
    stmt1;
  END;
  stmt2;
END;
#在上述例子中，H1適用于由stmt1引發(fā)的condition，而不適用于stmt2引發(fā)的condition

  • handler是否被觸發(fā)官研，不僅取決于其自身聲明的位置和條件秽澳，還受當前塊中的其他handler影響。當condition被觸發(fā)戏羽，服務(wù)器將搜索當前begin ... end塊中適用的handler担神，如果當前沒有合適的handler，服務(wù)器將繼續(xù)搜索外部塊中的handler始花，當服務(wù)器在指定的塊中找到了一個或者多個可用的handler妄讯，將按照如下優(yōu)先級進行選擇

    • 最局部的且符合condition的handler優(yōu)于外部的handler
    • 關(guān)聯(lián)了mysql error code的handler
    • 關(guān)聯(lián)了sqlstate value的handler
    • 關(guān)聯(lián)了sqlexception的handler
    • 關(guān)聯(lián)了sqlwarning、not found的handler

  • 同時具有多個可用的且優(yōu)先級相同的handler是可能的酷宵。比如說一條語句可能同時觸發(fā)多個general warning并且對應的handler都存在捞挥。此時無法保證哪個handler會被觸發(fā)

Demo

drop procedure if exists Fun0;
drop procedure if exists Fun1;
drop procedure if exists Fun2;
drop procedure if exists Fun3;
drop table if exists t;


delimiter $$


#Fun0
create procedure Fun0()
begin 

    declare continue handler for sqlexception 
        select "sql exception";
        
    declare continue handler for sqlstate '42S02'
        select "sql state";

    drop table t;

end $$


#Fun1
create procedure Fun1()
begin 

    declare continue handler for sqlstate '42S02'
        select "sql state";
    
    begin 
        declare continue handler for sqlexception 
            select "sql exception";

        drop table t;
    end;

end $$


#Fun2
create procedure Fun2()
begin 

    declare continue handler for sqlstate '42S02'
        select "sql state";
    
    begin 
        declare continue handler for sqlexception 
            select "sql exception";
    end;

    drop table t;

end $$


#Fun3
create procedure Fun3()
begin 

    begin 
    declare continue handler for sqlstate '42S02'
        select "sql state";
    
    declare continue handler for sqlexception 
        select "sql exception";
    end;

    drop table t;

end $$


delimiter ;

call Fun0();
/*
sql state
---------
sql state
*/

call Fun1();
/*
sql exception
-------------
sql exception
*/

call Fun2();
/*
sql state
---------
sql state
*/

call Fun3();
#error 1051 (42S02): unknow table 'd0.t'

Condition Handling and OUT or INOUT Parameters

  • 如果存儲過程因為一個沒有處理的exception而退出，被更改的out和inout參數(shù)不會被傳遞給調(diào)用者

    drop procedure if exists Fun;
delimiter $$

create procedure Fun(out v0 int, inout v1 int)
begin 

  set v0 = 1;
  set v1 = 2;

  select "Inner", v0, v1;
  signal sqlstate '04001';

end $$

delimiter ;

call Fun(@a, @b);
/*
Inner v0  v1
-------------
Inner 1   2
*/

select @a, @b;
/*
@a        @b
------------
null  null
*/

參考

https://dev.mysql.com/doc/refman/8.0/en/sql-compound-statements.html

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末忧吟，一起剝皮案震驚了整個濱河市砌函，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌溜族，老刑警劉巖讹俊，帶你破解...
    沈念sama閱讀 216,470評論 6贊 501
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異煌抒，居然都是意外死亡仍劈，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,393評論 3贊 392
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門寡壮，熙熙樓的掌柜王于貴愁眉苦臉地迎上來贩疙，“玉大人，你說我怎么就攤上這事况既≌饨Γ” “怎么了？”我有些...
    開封第一講書人閱讀 162,577評論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵棒仍，是天一觀的道長悲靴。 經(jīng)常有香客問我，道長莫其，這世上最難降的妖魔是什么癞尚？ 我笑而不...
    開封第一講書人閱讀 58,176評論 1贊 292
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮乱陡，結(jié)果婚禮上浇揩，老公的妹妹穿的比我還像新娘。我一直安慰自己憨颠，他們只是感情好胳徽，可當我...
    茶點故事閱讀 67,189評論 6贊 388
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著，像睡著了一般膜廊。 火紅的嫁衣襯著肌膚如雪乏沸。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 51,155評論 1贊 299
  • 城市分裂傳說
    那天爪瓜，我揣著相機與錄音蹬跃，去河邊找鬼。 笑死铆铆，一個胖子當著我的面吹牛蝶缀，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播薄货，決...
    沈念sama閱讀 40,041評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼翁都，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了谅猾？” 一聲冷哼從身側(cè)響起柄慰，我...
    開封第一講書人閱讀 38,903評論 0贊 274
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎税娜，沒想到半個月后坐搔，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,319評論 1贊 310
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡敬矩，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,539評論 2贊 332
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年概行，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片弧岳。...
    茶點故事閱讀 39,703評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡凳忙，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出禽炬，到底是詐尸還是另有隱情涧卵，我是刑警寧澤，帶...
    沈念sama閱讀 35,417評論 5贊 343
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布瞎抛，位于F島的核電站艺演，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏桐臊。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 41,013評論 3贊 325
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一晓殊、第九天 我趴在偏房一處隱蔽的房頂上張望断凶。 院中可真熱鬧，春花似錦巫俺、人聲如沸认烁。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,664評論 0贊 22
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽却嗡。三九已至舶沛，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間窗价，已是汗流浹背如庭。 一陣腳步聲響...
    開封第一講書人閱讀 32,818評論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來泰國打工， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留撼港，地道東北人坪它。 一個月前我還...
    沈念sama閱讀 47,711評論 2贊 368
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像帝牡，于是被迫代替她去往敵國和親往毡。 傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
    茶點故事閱讀 44,601評論 2贊 353

推薦閱讀更多精彩內(nèi)容