在存储过程或函数中表明复杂的条件选择语句,

作者: www.9159.com  发布:2019-11-03

www.9159.com 1
一、分支结构
1.If函数
语法:if(条件,值1,值2)
功能:实现双分支
应用范围:begin – 中 或者 外面
2.case结构
语法一:结构switch结构
case 变量或者表达式
when 值1 then 语句1;
when 值2 then 语句2;

else 语法n;
end case;
语法二:case
when 条件1 then 语句1;
when 条件2 then 语句2;

else 语句n;
end case;
应用在begin – end 中或者外面
3.if结构
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;

else 语句n;
end if;
功能:类似于多重if
【只能应用在begin end中】
例1:创建函数,传入成绩,返回学生成绩等级
www.9159.com 2
www.9159.com 3
二、循环结构
www.9159.com 4
1.while
语法:【标签:】while 循环条件 do
循环体;
End while【标签】;
2.loop
语法:【标签:】loop
循环体;
End loop 【标签】;
【常用于模拟死循环】【借助标签来结束循环】
3.repeat
语法:【标签:】repeat
循环体;
Until 结束循环的条件
End repeat 【标签:】;
例2 向admin表中批量插入数据【不含循环控制语句】
www.9159.com 5
www.9159.com 6
例3 向admin表中批量插入数据,插入第6条的时候停止while循环
www.9159.com 7
www.9159.com 8
例4 只插入偶数次
www.9159.com 9
www.9159.com 10

在MySQL数据库中使用PHP语句可以直接得到想要的结果

MySQL/MariaDB中的符合语句结构有:BEGIN...END,if,case,while,loop,repeat,cursor,condition,handler。

1.标签语句

[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]
  • 标签label可以加在begin...end语句以及loop,repeat和while语句上
  • 语句中通过iterate和leave来控制流程,iterate表示返回指定标签位置,leave表示跳出标签

实例:

mysql> delimiter //
mysql> create procedure doiterate(in p1 int,out p2 int)
    -> begin
    -> label1:loop
    -> set p1 = p1 + 1;
    -> if p1 < 10 then iterate label1;end if;
    -> leave label1;
    -> end loop label1;
    -> set p2=p1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call doiterate(1,@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> call doiterate(5,@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

注:loop和repeat用法和while类似。

  存储过程中定义局部变量:  declare语句

在MySQL中,它们都只能在存储程序(procedure,function,trigger)内部使用,但在MariaDB中,从10.1.1版本之后,BEGIN...END,IF,CASE,LOOP,REPEAT,WHILE可以在存储程序的外部使用。但实际上,除了begin、case和if能正常单独定义在存储程序之外,loop、repeat、while都是鸡肋,因为无法给单独定义的结构打标签,只能无限循环而无法退出。

2.Declare语句

语法:

DECLARE var_name [, var_name] ...  type [DEFAULT value]
  • Declare语句通常声明本地变量、游标、条件或者handler
  • Declare语句只允许出现在begin...end语句中而且必须出现在第一行
  • Declare的顺序也有要求,通常是先声明本地变量,再是游标,然后是条件和handler
  • 使用default指定变量的默认值,如果没有指定默认值则为NULL
  • 声明的变量和被引用的数据表中的字段要区分开

存储过程中的变量

  • 本地变量可以通过declare语句声明
  • 声明后的变量可以通过select...into var_list进行赋值,或者通过set语句赋值,或者通过定义游标并使用fetch...into var_list赋值
mysql> delimiter //
mysql> create procedure sp1(v_sid int)
    -> begin
    -> declare xname varchar(64) default 'bob';
    -> declare xgender int;
    -> select sname,gender into xname,xgender from students where sid=v_sid;
    -> select xname,xgender;
    -> end//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> 
mysql> call sp1(1);
+--------+---------+
| xname  | xgender |
+--------+---------+
| Andrew |       1 |
+--------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

  declare 变量名 类型(长度) default 默认值;

1.BEGIN...END

[label:] BEGIN [NOT ATOMIC]
    [statement_list]
END [label]

begin...end默认只能在存储程序内部使用,此时可以使用label为begin...end打上标签。可以在end处使用标签来结束begin...end。如果end后指定了label,则end处的label必须和begin前的label完全相同。

可以使用NOT ATOMIC关键字让BEGIN在存储程序外部使用,但此时不能为begin...end打标签,且一定注意先修改delimiter,免得begin...end中的语句中途终止。

例如,下面三个begin...end语句块:

delimiter $$
begin not atomic        /* 在存储程序外部单独使用begin...end */
    declare a int;
    set a=3;
    select a;
end$$

create or replace procedure p1()
my_label: begin           /* 为begin打上标签 */
    declare b int;
    set b=3;
    select b;
end$$                  /* 可以不使用标签结束begin */

create or replace procedure p2()
my_label: begin
    declare c int;
    set c=3;
    select c;
end my_label$$         /* 使用的结束标签必须和开始标签一致 */
delimiter ;
call p1;
call p2;

在begin...end中,除了可以写一些SQL语句,还可以使用declare定义变量、条件、处理器和游标。但它们之间有顺序要求,顺序如下:

  • DECLARE local variables;
  • DECLARE CONDITIONs;
  • DECLARE CURSORs;
  • DECLARE HANDLERs;

还可以begin嵌套,内层的begin可以使用外层begin定义的变量。

delimiter $$
CREATE PROCEDURE t()
BEGIN
   DECLARE x TINYINT UNSIGNED DEFAULT 1;
   BEGIN
      DECLARE x CHAR(2) DEFAULT '02';
       DECLARE y TINYINT UNSIGNED DEFAULT 10;
       SELECT x, y;
   END;
   SELECT x;
END;
delimiter ;

3.流程控制语句

(1)case语句
在存储过程或函数中表明复杂的条件选择语句。
语法:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

说明:

  • case_value与when_value依次做相等对比,如果相等则执行对应的后面的SQL语句,否则接着对比;
  • 当search_condition满足true/1的结果时,则执行对应的SQL语句,否则执行else对应的SQL语句;

实例:

mysql> delimiter //
mysql> create procedure exp_case(v_sid int)
    -> begin
    -> declare v int default 1;
    -> select gender into v from students where sid=v_sid;
    -> case v
    -> when 0 then update students set gender=1 where sid=v_sid;
    -> when 1 then update students set gender=0 where sid=v_sid;
    -> else
    -> update students set gender=-1 where sid=v_sid;
    -> end case;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | 1      |       1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)

mysql> call exp_case(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | 0      |       1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)

另一种写法:
mysql> delimiter //
mysql> create procedure exp_case2(v_sid int)
    -> begin
    -> declare v int default 1;
    -> select gender into v from students where sid=v_sid;
    -> case
    -> when v=0 then update students set gender=1 where sid=v_sid;
    -> when v=1 then update students set gender=0 where sid=v_sid;
    -> else
    -> update students set gender=-1 where sid=v_sid;
    -> end case;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call exp_case2(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | 1      |       1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)

(2)IF语句
在存储过程或函数中表明基础的条件选择语句
语法:

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF
  • 如果search_condition满足true/1的条件,则执行对应的SQL语句,否则再判断elseif中的search_condition,都不满足则执行else中的SQL语句;
  • statement_list中可以包含一个或多个SQL语句

实例:

mysql> delimiter //
mysql> create function SimpleCompare(n int,m int)
    -> returns varchar(20)
    -> begin
    -> declare s varchar(20);
    -> if n > m then set s = '>';
    -> elseif n = m then set s = '=';
    -> else set s = '<';
    -> end if;
    -> set s = concat(n,'',s,'',m);
    -> return s;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
mysql> select SimpleCompare(5,6);
+--------------------+
| SimpleCompare(5,6) |
+--------------------+
| 5<6                |
+--------------------+
1 row in set (0.00 sec)

mysql> select SimpleCompare(15,34);
+----------------------+
| SimpleCompare(15,34) |
+----------------------+
| 15<34                |
+----------------------+
1 row in set (0.00 sec)

mysql> select SimpleCompare(78,78);
+----------------------+
| SimpleCompare(78,78) |
+----------------------+
| 78=78                |
+----------------------+
1 row in set (0.00 sec)

IF嵌套:

mysql> delimiter //
mysql> create function verboseCompare(n int,m int)
    -> returns varchar(50)
    -> begin
    -> declare s varchar(50);
    -> if n = m then set s = 'equals';
    -> else
    -> if n > m then set s = 'greater';
    -> else set s = 'less';
    -> end if;
    -> set s = concat('is',s,'than');
    -> end if;
    -> set s = concat(n,'',s,'',m,'.');
    -> return s;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select verboseCompare(4,5);
+---------------------+
| verboseCompare(4,5) |
+---------------------+
| 4islessthan5.       |
+---------------------+
1 row in set (0.00 sec)

(3)iterate语句
仅出现在loop,repeat,while循环语句中,表示重新开始此循环。
语法:

ITERATE label

(4)leave语句
表明指定标签的流程控制语句块,通常用在begin...end,以及loop,repeat,while循环汇总;

LEAVE label

实例:

mysql> delimiter //
mysql> create procedure doiterate2(in p1 int,out p2 int)
    -> begin
    -> label1:loop
    -> set p1=p1+1;
    -> if p1 < 10 then iterate label1;end if;
    -> leave label1;
    -> end loop label1;
    -> set p2=p1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

(5)loop语句
在存储过程或函数中表达循环执行的一种方式;
语法:

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

实例:

mysql> delimiter //
mysql> create procedure doiterate3(p1 int)
    -> begin
    -> label1:loop
    -> set p1=p1+1;
    -> if p1<10 then iterate label1;end if;
    -> leave label1;
    -> end loop label1;
    -> set @x=p1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

(6)repeat语句
在存储过程或函数中表达循环执行的一种方式;
语法:

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

实例:

mysql> delimiter //
mysql> create procedure doiterate4(p1 int)
    -> begin
    -> set @x=0;
    -> repeat
    -> set @x=@x+1;
    -> until @x>p1 end repeat;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call doiterate4(1000);
Query OK, 0 rows affected (0.02 sec)

mysql> select @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

(7)while语句
在存储过程或函数中表达循环执行的一种方式;
语法:

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]
  • 当search_condition返回true时,循环执行SQL语句,直到search_condition为false;

实例:

mysql> delimiter //
mysql> create procedure dowhile()
    -> begin
    -> declare v1 int default 5;
    -> while v1>0 do
    -> update students set gender=-1 where sid=v1;
    -> set v1=v1-1;
    -> end while;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call dowhile();
Query OK, 1 row affected (0.63 sec)

mysql> select * from students;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | -1     |       1 |
|   2 | Andy   | -1     |       1 |
|   3 | Bob    | -1     |       1 |
|   4 | Ruth   | -1     |       2 |
|   5 | Mike   | -1     |       2 |
|   6 | John   | 0      |       3 |
|   7 | Cindy  | 1      |       3 |
|   8 | Susan  | 1      |       3 |
+-----+--------+--------+---------+
8 rows in set (0.00 sec)

(8)return语句
在函数中,用来终结函数的执行并将指定值返回给调用者;
语法:

RETURN expr
  • 在函数中必须有至少一个return语句,当有多个return语句时则表明函数有多种退出方式;

实例:

mysql> delimiter //
mysql> create function doreturn()
    -> returns int
    -> begin
    -> select gender into @a from students where sid = 1;
    -> if @a=1 then return 1;
    -> elseif @a=0 then return 0;
    -> else return 999;
    -> end if;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select doreturn();
+------------+
| doreturn() |
+------------+
|        999 |
+------------+
1 row in set (0.00 sec)

mysql> select * from students where sid = 1;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | -1     |       1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)

  #num1 num2值互换#

2.true和false

在MySQL和MariaDB中,true和false是无条件的真、假。

true和1等价,false和0等价,且它们不区分大小写。任何时候,都可以互换它们,但任何时候,它们的结果都换转换为数值的1或0。即使将数据类型定义为int,也可以为其传递true字符串。

例如:

mysql> select true,false;
+------+-------+
| TRUE | FALSE |
+------+-------+
|    1 |     0 |
+------+-------+
begin not atomic 
    declare a int;
    set a=true;
    select a;
end$$
+------+
| a    |
+------+
|    1 |
+------+
delimiter //
CREATE PROCEDURE exchange (
    INOUT num1 INT (5),
    INOUT num2 INT (5)
)
BEGIN

DECLARE tmp INT (5) DEFAULT 0 ;
SET tmp = num1 ;
SET num1 = num2 ;
SET num2 = tmp ;
END ;//


SET @a = 100 ,@b = 200 ;//

SELECT
    @a ,@b ;//

CALL exchange (@a ,@b) ;//

SELECT
    @a ,@b ;//

3.if语句

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

其中search_condition是条件判断语句,statement_list是要执行的语句。

在MySQL中,if语句必须作为语句块写在存储过程或函数等程序结构中。在MariaDB 10.1.1之后,可以独立使用,但注意修改delimiter。

-- 独立使用if结构
delimiter $$
if 1>2 then 
    select 'true'; 
else 
    select 'false'; 
end if$$
delimiter ;

-- 在存储过程中使用
DELIMITER $$
CREATE or replace PROCEDURE proc1(a INT,OUT i INT)
BEGIN
    IF a > 0 THEN 
        SET @a1 = a + 100; 
    ELSEIF a = 0 THEN 
        SET @a1 = a + 200;
    ELSE 
        SET @a1 = a + 300; 
    END IF ;
    SET i:=@a1;
END$$
DELIMITER ;

-- 测试if结构
CALL proc1(1,@a);
CALL proc1(0,@b);
CALL proc1(-1,@c);

查看结果。

SELECT @a,@b,@c;
    @a      @b      @c  
------  ------  --------
   101     200       299

注意,还有一个if(expr,true_value,false_value)函数,如果expr为真,则返回true_value,否则返回false_value,所以这里的if函数和if流程控制语句是不同的。例如:

MariaDB [test]> select if(1>2,'true','false') as a;
+-------+
| a     |
+-------+
| false |
+-------+
MariaDB [test]> select if(1<2,'true','false') as a; 
+------+
| a    |
+------+
| true |
+------+

 

4.case语句

在MySQL中,有case表达式和case语句两种结构。

mysql> help case
topics:
   CASE OPERATOR
   CASE STATEMENT

它们的区别有:

  • case表达式使用end关键字作为结束符,而case语句使用end case作为结束符;
  • case表达式有可选项else,省略它的时候其默认值为null,case语句也有可选项else,但是省略它的时候没有默认值,且else语句中不能是null;
  • case表达式可以在任何地方使用(例如在select中),而case语句只能在存储过程或函数这样类型的编程结构中使用。
mysql> help case operator
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN
result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]
[ELSE result] END

Examples:
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

mysql> help case statement
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

虽然case表达式和case语句在性质上不一样,但是使用它们的思路是一样的。CASE value WHEN...只能与value进行等同性检查,case when ... then...则要灵活的多。

case operator的使用示例:

MariaDB [test]> select * from Student;
+------+----------+------+--------+
| sid  | name     | age  | class  |
+------+----------+------+--------+
|    1 | chenyi   |   22 | Java   |
|    2 | huanger  |   23 | Python |
|    3 | zhangsan |   21 | Java   |
|    4 | lisi     |   20 | C#     |
|    5 | wangwu   |   21 | Python |
|    6 | zhaoliu  |   19 | Java   |
|    7 | qianqi   |   22 | C      |
|    8 | sunba    |   20 | C++    |
|    9 | yangjiu  |   24 | Java   |
+------+----------+------+--------+

MariaDB [test]> select name,case when age>21 then 'older' else 'younger' end as oy from Student;     
+----------+---------+
| name     | oy      |
+----------+---------+
| chenyi   | older   |
| huanger  | older   |
| zhangsan | younger |
| lisi     | younger |
| wangwu   | younger |
| zhaoliu  | younger |
| qianqi   | older   |
| sunba    | younger |
| yangjiu  | older   |
+----------+---------+

case statement的使用示例:

DELIMITER $$
CREATE OR REPLACE PROCEDURE proc1(a INT,OUT i INT)
BEGIN
    CASE 
        WHEN a>0 THEN 
            SET @a1=a+100;
        WHEN a=0 THEN
            SET @a1=a+200;
        ELSE
            SET @a1=a+300;
    END CASE;
    SET i=@a1;
END$$
DELIMITER ;
-- 调用存储过程进行测试
CALL proc1(1,@x);
CALL proc1(0,@y);
CALL proc1(-1,@z);
-- 查看测试结果
SELECT @x,@y,@z;
    @x      @y      @z  
------  ------  --------
   101     200       299

  【if】  #根据学生编号和学生成绩判断学生成绩等级90分-A 80分-B 70分-C 60分-D 60分以下-E#

5.loop、leave和iterate

loop是循环,leave是跳出整个循环(类似于break),iterate是跳出当前循环进入下一个循环(类似于continue)。

 [begin_label:] LOOP
    statement_list
END LOOP [end_label]

在loop结构中,使用end loop关键字作为结束标记。

在loop中可以使用标签来标注该循环。如果要标记loop循环,则使用"begin_label:"和"end_label",但要求begin_label和end_label是完全相同的字符串标记(或者end不用标签直接结束也可以)。

add_num: loop
    set @a=@a+1
end loop add_num;

该语句由于没有跳出循环语句,所以将会死循环。

因此,loop一般会配合leave来使用。leave的作用是退出指定标记的语句结构,一般用在循环中用来退出循环(不仅是用在loop循环,其他循环结构也一样),相当于break。因为begin...end中也能使用标记,所以leave也能用在begin...end中。 例如下面的例子,计算从1加到给定的数。通过if判断退出循环的条件,达到退出条件时使用leave来退出。

delimiter $$
create or replace procedure proc1(n int)
begin
    declare sum,i int default 0;
    loop_label: loop
        set sum=sum+i;
        if i=n then
            leave loop_label;
        end if;
        set i=i+1;
    end loop loop_label;
    select sum;
end$$
delimiter ;

call proc1(100);   /* 从1加到100 */
   sum  
--------
    5050

iterate和leave的用法一样,只不过iterate是退出当前循环直接进入下一循环。

例如下面的存储过程,它的功能是计算传入数值范围内的奇数和。当超出传入值的范围时直接退出循环,当遇到偶数时直接进入下一循环。

delimiter $$
create or replace procedure proc2(n int)
begin
    declare i,sum int default 0;
    my_loop: loop
        set i=i+1;
        if i>n then 
            leave my_loop;
        elseif mod(i,2)=0 then
            iterate my_loop;
        else 
            set sum=i+sum;
        end if;
    end loop;
    select sum;
end$$
delimiter ;

调用存储过程来测试。

CALL proc4(9);
   sum  
--------
      25

虽然在MariaDB中,loop能单独定义在存储程序之外,但是由于定义在外面的时候无法打标签,而退出循环的leave却必须接标签才能退出loop,因此loop定义在存储程序之外完全是一个鸡肋的功能。同理,repeat和while也是一样的。

  select 字段列表 into 变量列表 from 表 where 条件  将某条记录中的字段列表中的值存入到变量列表中,变量列表中的变量一定是提前定义的。

6.repeat循环

loop循环结构中没有退出循环的条件,只能人为书写条件判断语句来判断何时退出循环。而repeat循环结构提供了直接定义退出循环条件的功能。

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

其中until部分定义的是退出循环的条件。注意,until语句后面不需要结束提示符,给出了结束符反而报错。

例如下面的存储过程中,当a变量等于传入参数i的时候退出循环。

delimiter $$
create or replace procedure proc5(i int)
begin
    declare a int default 1;
    declare sum int default 1;
    addnum: repeat
        set a=a+1;
        if mod(a,2)=1 then
            set sum=sum+a;
        end if;
    until a=i
    end repeat addnum;
    select sum;
end$$
delimiter ;
call proc5(10);
   sum  
--------
      25

    格式:

7.while循环

while循环和repeat循环使用方法一样,但是思路不一样。repeat是满足条件时退出,而while是满足条件时才执行循环中定义的语句。

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

例如下面的存储过程,仍然是计算传入值范围内的奇数和。但使用的是while循环。

DROP PROCEDURE IF EXISTS proc6;
DELIMITER $$
CREATE PROCEDURE proc6(i INT)
BEGIN
    SET @a=1,@sum=1;
    addnum: WHILE @a<i DO
        SET @a=@a+1;
        IF MOD(@a,2)=1 THEN
            SET @sum=@a+@sum;
        END IF;
    END WHILE addnum;
    SELECT @sum AS SUM;
END$$
DELIMITER ;

      If 条件 then

        执行代码

      Else if 条件 then

        执行代码

        .....

      Else

        执行代码

      End if; 

delimiter //
CREATE PROCEDURE marks_level_if (
    IN stu_no INT (4),
    IN cla_no INT (4),
    OUT _level CHAR (4)
)
BEGIN

DECLARE _mark INT (4) DEFAULT 0 ; SELECT
    mark INTO _mark
FROM
    marks
WHERE
    sid = stu_no
AND cid = cla_no ;

IF _mark >= 90 THEN
SET _level = "A" ;

ELSEIF _mark >= 80 THEN
SET _level = "B" ;

ELSEIF _mark >= 70 THEN
SET _level = "C" ;

ELSEIF _mark >= 60 THEN
SET _level = "D" ;

ELSE
SET _level = "E" ;

ENDIF ;
END ;//


SET @result = "" ;//

CALL marks_level_if (1, 1 ,@result) ;//

SELECT
    @result ;//

www.9159.com , 

  【case】  #根据学生编号和学生成绩判断学生成绩等级90分-A 80分-B 70分-C 60分-D 60分以下-E#

    CASE 变量

      WHEN 值1 THEN 代码1

      WHEN 值2  THEN 代码2 ...

      ELSE 代码n

    END CASE

  Or:

    CASE

      WHEN  条件1 THEN 代码1

      WHEN  条件2  THEN 代码2 ...

    ELSE 代码n

    END CASE;

delimiter //
CREATE PROCEDURE marks_level_case (
    IN stu_no INT (4),
    IN cla_no INT (4),
    OUT _level CHAR (4)
)
BEGIN

DECLARE _mark INT (4) DEFAULT 0 ; SELECT
    mark INTO _mark
FROM
    marks
WHERE
    sid = stu_no
AND cid = cla_no ; 

CASE

WHEN _mark >= 90 THEN
SET _level = "A" ;

WHEN _mark >= 80 THEN
SET _level = "B" ;

WHEN _mark >= 70 THEN
SET _level = "C" ;

WHEN _mark >= 60 THEN
SET _level = "D" ;
ELSE

SET _level = "E" ;
END CASE ;
END ;//


SET @result = "" ;//

CALL marks_level_case (1, 2 ,@result) ;//

SELECT
    @result ;//

 

  【while】  #1-10累加的和#

    while循环结构

      格式:

    while 条件 do

      代码

    end while;

delimiter //
CREATE PROCEDURE marks_level_while (IN num INT(5), INOUT sum INT(5))
BEGIN

WHILE num > 0 DO

SET sum = sum + num ;
SET num = num - 1 ;
ENDWHILE ;
END ;//


SET @result = 0 ;//

CALL marks_level_while (10 ,@result) ;//

SELECT
    @result ;//

 

  【repeat】  #1-10累加的和#

    repeat

      循环体

    until 条件

    end repeat;

delimiter //
CREATE PROCEDURE marks_level_repeat (IN num INT(5), INOUT sum INT(5))
BEGIN

REPEAT

SET sum = sum + num ;
SET num = num - 1 ; UNTIL num = 0
END REPEAT;
END ;//


SET @result = 0 ;//

CALL marks_level_repeat (10 ,@result) ;//

SELECT
    @result ;//

 

  【loop】  #1-10累加的和#

    ITERATE 循环体名字: 继续循环

    Leave  循环体名字: 退出循环

    循环名字:Loop

      if 条件 then

        leave   循环体名字

      else

        iterate 循环体名字

      end if;

      if 条件 then

        leave   循环体名字

      else

        iterate 循环体名字

      end if;

    end loop;

delimiter //
CREATE PROCEDURE marks_level_loop (IN num INT(5), INOUT sum INT(5))
BEGIN
    lp :
LOOP

IF num > 0 THEN

SET sum = sum + num ;
END IF ;
SET num = num - 1 ;
IF num <= 0 THEN
    LEAVE lp ;
ELSE
    ITERATE lp ;
END IF ;
END LOOP;
END ;//


SET @result = 0 ;//

CALL marks_level_loop (10 ,@result) ;//

SELECT
    @result ;//

 

  【cursor】  #根据学生编号和学生成绩判断学生成绩等级 90分发500 80分发400 70分发300#

    游标: mysql中用来存放查询结果记录的一种数据集合。

    设置了错误句柄: 

      declare continue/exit  handler for sqlsate '错误'   set 语句

            监视错误句柄的操作      设置监视句柄监视的错误码  执行了错误操作后,执行的语句

    游标的定义:将查询结果存入游标中

      declare 游标名 cursor for select语句。

    打开游标

      open 游标名

    从游标中取出数据:变量列表要和游标中的查询字段数据和类型一一对应。

      fetch 游标名 into 变量列表

      获取数据时,如果游标中没有了数据,报出02000的错误,然后执行错误句柄。

    关闭游标:

      close 游标名

/* 存储过程游标控制结构 */
delimiter //
CREATE PROCEDURE marks_level_cursor ()
BEGIN

DECLARE stu_no INT (4) DEFAULT 0 ;
DECLARE cla_mark INT (4) DEFAULT 0 ;
DECLARE tag INT (4) DEFAULT 0 ;
DECLARE moneys INT (4) DEFAULT 0 ;
DECLARE cur CURSOR FOR SELECT
    s.sid,
    m.mark
FROM
    students s
LEFT JOIN marks m ON s.sid = m.sid ;
DECLARE EXIT HANDLER FOR SQLSTATE "02000"
SET tag = 1 ; OPEN cur ;
WHILE ! tag DO
    FETCH cur INTO stu_no,
    cla_mark ;
IF cla_mark >= 90 THEN
SET moneys = 500 ;

ELSEIF cla_mark >= 80 THEN
SET moneys = 400 ;

ELSEIF cla_mark >= 70 THEN
SET moneys = 300 ;

ELSE
SET moneys = 100 ;
ENDIF ; 

UPDATE students
SET moneys = moneys
WHERE
    sid = stu_no ;
ENDWHILE ; 

CLOSE cur ;
END ;//

CALL marks_level_cursor () ;//

SELECT * FROM students ;//

 

    触发器:可以自动执行的mysql程序逻辑,用户对表执行了某个操作后,系统自动调用的程序逻辑。

      插入,删除,修改操作后,系统自动调用.
      mysql中只支持单触发器:一个表的一个操作上只能有一个函数。
      触发器和其他视图,存储过程不同,他只能依附在表机构中,没有独立的文件。
      格式:
        create trigger 触发器名()
        before|after
        insert|update|delete
        on 表名
        for each row 每一条记录都要调用触发器
        begin
          触发器代码。
        end;
      触发触发器时间:
        before:操作之前
        after:操作之后
      触发行为: 就是在什么操作上创建触发器
        insert
        update
        delete
      insert table student("sex") value("men")
      关键字:
        old: 老数据
        news: 新数据
        插入: 只有新数据,没有老数据。 news.sex
        修改:被修改的记录时老数据,即将替换的数据,是new数据
        删除:只有老数据,没有新数据(老数据:即将删测那条记录)

  【trigger】  #插入内容前修改插入内容 插入"南京"时 自动插入"NJ_南京"#

delimiter //
CREATE TRIGGER insert_students_trigger BEFORE INSERT ON students FOR EACH ROW
BEGIN

IF new.addr = "山东" THEN
SET new.addr = "SD_山东" ;

ELSEIF new.addr = "上海" THEN
SET new.addr = "SH_上海" ;

ELSEIF new.addr = "南京" THEN
SET new.addr = "NJ_南京" ;

END IF ;
END ;//

  【trigger】  #删除学生信息时,把相关成绩删除#

delimiter //
CREATE TRIGGER delect_students_trigger BEFORE DELETE ON marks FOR EACH ROW
BEGIN

DECLARE stu_no INT (4) DEFAULT 0 ; 
SELECT sid INTO stu_no FROM students WHERE sid = old.sid;
IF stu_no THEN
    DELETE
FROM
    marks
WHERE
    sid = stu_no ;
ENDIF;
END ;//

 

本文由9159.com发布于www.9159.com,转载请注明出处:在存储过程或函数中表明复杂的条件选择语句,

关键词: