复合语句

2020-01-07  本文已影响0人  szn好色仙人

标签

[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

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

declare

存储程序中的变量

流程控制语句

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

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

leave

LEAVE label

loop

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

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();
#死循环，将一直进行输出

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

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关联
DECLARE cursor_name CURSOR FOR select_statement

#打开已声明的游标
OPEN cursor_name

#获取下一行数据
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

#关闭游标
CLOSE cursor_name

游标使用示例

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)

条件声明

DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

声明处理

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
}

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
}

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
}

单独的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的范围规则

​ stored program可以包含多个handler，当特定的条件在program中发生时，进行调用。hanlder的适用性取决于其自身定义的位置以及其关联的condition：

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

参考

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

上一篇 下一篇

猜你喜欢

热点阅读