ACCESSMySQL基础知识07存款和储蓄进程

 

正文首要内容如下所示:

MySQL存储进程

  1. 仓库储存进程的差不多介绍

  2. 储存进度的调用

  3. 积存进程的参数

  4. 存款和储蓄进程的变量

  5. 复合语句BEGIN-END

  6. 标准化语句IF-THEN-ELSE

  7. 分支语句CASE-WHEN-THEN-ELSE

  8. 决定流语句CASE-WHEN-THEN-ELSE

  9. 存款和储蓄进度的循环语句

9.1. WHILE-DO

9.2.  REPEAT-UNTIL

9.3. LOOP

  1. 储存进程中的其余语句

10.1. 赋值语句

  1. SELECT INTO 语句

  2. DECLARE错误处理程序

11.1. DECLARE错误处理程序的牵线

  1. 错误处理程序的处理方式

12 游标

  1. 动态SQL

13.1. 动态SQL的例子

  1. 动态SQL的注意事项

  2. 翻开存款和储蓄进程的音讯

14.1. 翻看存款和储蓄进程的定义

14.2. 翻看存款和储蓄进程的情景

14.3. 翻看存款和储蓄进度的元数据音信

 

1. 仓储进程的容易介绍

 

储存进程包括以下组成部分:

(1)存款和储蓄进程的称呼。

(2)存款和储蓄进程的参数。

(3)存款和储蓄进程的代码块。

(4)存款和储蓄进度包蕴的诠释。

(5)存款和储蓄进程的定义者用户。

(6)存款和储蓄进度的性状定义。

 

在概念存款和储蓄进程时,平日先将SQL语句的相间符暂且修改为其余不常见字符串,在储存过程定义甘休后再将分隔符修改为暗中认可的子集团。在输入存款和储蓄进度的代码的进度中,因为代码中间的分店分隔符会导致MySQL命令行工具认为必要将分店在此之前的SQL语句提交给SQL解析器进行处理,那在概念存款和储蓄进度时会导致语法错误。为了制止那种不供给的百无一用的发出,就要求提前修改分隔符。

 

 

—  删除已经存在的存款和储蓄进度。

drop procedure if exists sp_p1;

 

 

— 临时修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存款和储蓄进度

 名称:sp_p1

****************/

 create definer=’test’@’%’ procedure sp_p1(IN p_a decimal(4,1) ,

                        OUT p_b varchar(10) ,

                       INOUT p_c decimal(4,2),

                       IN p_d decimal(4,3),

                       IN a decimal(4,1)

 )

     begin

     declare a decimal(4,1) default 1.23;

     declare b varchar(10) default ‘bbbbbbbbbb’;

     declare c decimal(4,2) default 12.123;

     declare d decimal(4,3) default 0;

     declare e integer;

     set @p = p_d;

     set  d = p_d;

     select p_a,p_b,p_c, a,b,c,d;

     set p_a = a;

     set p_b = b;

     set p_c = c;

     select p_a,p_b,p_c, a,b,c,d;

     end$$

   delimiter ;

 

2. 存款和储蓄过程的调用

应用call语句来调用存款和储蓄过程。

 

在实施存储进度在此以前,可以提前准备好作为参数的变量的值。

mysql> set @a=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @b=’bb’;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @c=2;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @d=3;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @e=4;

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(@a,@b,@c,@d,@e);

+——+——+——+——+————+——-+——-+

| p_a  | p_b  | p_c  | a    | b          | c     | d     |

+——+——+——+——+————+——-+——-+

|  1.0 | NULL | 2.00 |  1.2 | bbbbbbbbbb | 12.12 | 3.000 |

+——+——+——+——+————+——-+——-+

1 row in set (0.00 sec)

 

+——+————+——-+——+————+——-+——-+

| p_a  | p_b        | p_c   | a    | b          | c     | d     |

+——+————+——-+——+————+——-+——-+

|  1.2 | bbbbbbbbbb | 12.12 |  1.2 | bbbbbbbbbb | 12.12 | 3.000 |

+——+————+——-+——+————+——-+——-+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

 

也足以直接对IN类型的变量传入直接值,对OUT类型变量直接传入未经赋值的变量名,对INOUT类型变量则需求先给变量赋值再盛传变量名。

 

mysql> call sp_p1(1,@v2,@v3,3,4);  

+——+——+——+——+————+——-+——-+

| p_a  | p_b  | p_c  | a    | b          | c     | d     |

+——+——+——+——+————+——-+——-+

|  1.0 | NULL | NULL |  1.2 | bbbbbbbbbb | 12.12 | 3.000 |

+——+——+——+——+————+——-+——-+

1 row in set (0.00 sec)

 

+——+————+——-+——+————+——-+——-+

| p_a  | p_b        | p_c   | a    | b          | c     | d     |

+——+————+——-+——+————+——-+——-+

|  1.2 | bbbbbbbbbb | 12.12 |  1.2 | bbbbbbbbbb | 12.12 | 3.000 |

+——+————+——-+——+————+——-+——-+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

v2是OUT类型变量,能够不赋起头值,直接传入变量名称。

v3是INOUT类型变量,常常要求先赋伊始值,再传播变量名称。如若直白传入未经赋初值的变量名称,则传出的变量的值为NULL。

 

 

3. 储存进程的参数

仓库储存进程的参数包罗以下二种档次:

(1)IN类型。参数由调用者将参数的值从外表传入,在蕴藏进程之中对IN类型的变量参数的值的修改,不会潜移默化外部变量的值。实际参数能够是变量大概值。

(2)OUT类型。参数由调用者将变量参数从表面传入,在仓库储存进度之中对OUT类型的变量参数的值的修改,将会促成表面变量的值的变动。实际参数只能是变量,不能够是值。OUT类型的参数平日用于存款和储蓄进度里面只有经过该参数向外部传递再次来到结果,而不关切外部通过该参数传入的值。

(3)INOUT类型。参数由调用者将变量参数从表面传入,在存款和储蓄进程之中对INOUT类型的变量参数的值的改动,将会导致表面变量的值的改变。实际参数只可以是变量,不可能是值。INOUT类型的参数日常用于存款和储蓄进度里面既会利用外部通过该参数传入的值,也会通过该参数向外部传递重回结果。

 

使用call语句来调用存款和储蓄进度。

mysql> set @a=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @b=’bb’;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @c=2;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @d=3;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @e=4;

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(@a,@b,@c,@d,@e);

+——+——+——+——+————+——-+——-+

| p_a  | p_b  | p_c  | a    | b          | c     | d     |

+——+——+——+——+————+——-+——-+

|  1.0 | NULL | 2.00 |  1.2 | bbbbbbbbbb | 12.12 | 3.000 |

+——+——+——+——+————+——-+——-+

1 row in set (0.00 sec)

 

+——+————+——-+——+————+——-+——-+

| p_a  | p_b        | p_c   | a    | b          | c     | d     |

+——+————+——-+——+————+——-+——-+

|  1.2 | bbbbbbbbbb | 12.12 |  1.2 | bbbbbbbbbb | 12.12 | 3.000 |

+——+————+——-+——+————+——-+——-+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> select @a,@b,@c,@d,@e;

+——+————+——-+——+——+

| @a   | @b         | @c    | @d   | @e   |

+——+————+——-+——+——+

|    1 | bbbbbbbbbb | 12.12 |    3 |    4 |

+——+————+——-+——+——+

1 row in set (0.00 sec)

 

由上述select语句能够看到,在履行完存款和储蓄进度之后,变量a,d,e的值没有别的变动,因为它们都以IN类型的参数,尽管在蕴藏进度sp_p1内部,它们的值被涂改了,也不会影响到表面变量的值。

而变量b,c的值变化了,因为它们是OUT或INOUT类型的参数,在蕴藏进程sp_p1的里边,它们的值倘诺被涂改,就会造成外部变量的值爆发相应变更。

 

 

4. 仓库储存进度的变量

 

在仓库储存进度之中,通过declare语句定义局地变量,这个变量仅仅在蕴藏进程之中有效。当一些变量的名号和存款和储蓄进程的参数的称号一致时,优用一些变量。

 

变量注脚的语法:

DECLARE var_name[,…] type [DEFAULT value]

 

变量赋值的语法:

SET var_name = expr [, var_name = expr] …

 

 

—  删除已经存在的储存进度。

drop procedure if exists sp_p1;

 

— 暂且修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存款和储蓄进度

 名称:sp_p1

****************/

 create definer=’test’@’%’ procedure sp_p1(IN a decimal(4,1) ,

                        OUT b varchar(10) ,

                       INOUT c decimal(4,2)  ,

                       IN d decimal(4,3),

                       IN e decimal(4,1)

 )

     begin

     declare a decimal(4,1) default 1.23;

     declare b varchar(10) default ‘bbbbbbbbbb’;

     declare c decimal(4,2) default 12.123;

     declare d decimal(4,3) default 0;

     declare e integer;

 

     select a,b,c,d,e;

       

    end$$

   delimiter ;

 

在上述存款和储蓄进程中,多少个部分变量分别与多少个参数同名,在select
a,b,c,d,e;语句中央银行使的将是某个变量,而不是参数。

 

mysql> set @a=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @b=’bb’;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @c=2;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @d=3;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @e=4;

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(@a,@b,@c,@d,@e);

+——+————+——-+——-+——+

| a    | b          | c     | d     | e    |

+——+————+——-+——-+——+

|  1.2 | bbbbbbbbbb | 12.12 | 0.000 | NULL |

+——+————+——-+——-+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

5. 复合语句BEGIN-END

最简便的蕴藏进程如下所示:

create procedure sp_t1()

select * from test;

该存款和储蓄进程的历程体中仅仅有一条语句。

 

若是存款和储蓄进度的进度体中须要蕴含多条语句,则要求使用BEGIN-END复合语句。

 

delimiter $$

create procedure sp_t3()

begin

select * from test;

select * from test;

end;

$$

delimiter ;

 

BEGIN-END复合语句的效应是将多条语句组合成一条语句,在那之中的享有语句遵照顺序依次执行。

 

6. 规格语句IF-THEN-ELSE

 

—  删除已经存在的贮存进程。

drop procedure if exists sp_p1;

 

— 临时修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存款和储蓄进程

 名称:sp_p1

****************/

 create definer=’test’@’%’ procedure sp_p1(IN a decimal(4,1) )

     begin

     declare  sa varchar(100);

     

     if a < 0  then

         set sa = ‘<0’;

     elseif a = 0 then

         set sa = ‘=0’;   

     elseif a < 10 then

         set sa = ‘<10’;

     else

         set sa = ‘>=10’;

     end if;

       

     select a, sa;

    end$$

   delimiter ;

 

举行理并了结果:

mysql> call sp_p1(-1);

+——+——+

| a    | sa   |

+——+——+

| -1.0 | <0   |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(0);

+——+——+

| a    | sa   |

+——+——+

|  0.0 | =0   |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(1);

+——+——+

| a    | sa   |

+——+——+

|  1.0 | <10  |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

mysql> call sp_p1(11);

+——+——+

| a    | sa   |

+——+——+

| 11.0 | >=10 |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

 

 

7. 分支语句CASE-WHEN-THEN-ELSE

二种语法格局描述如下:

(1)语法1:对三个变量举行分层判断。

CASE case_value

    WHEN when_value THEN statement_list

    [WHEN when_value THEN statement_list] …

    [ELSE statement_list]

END CASE

 

 

—  删除已经存在的贮存进度。

drop procedure if exists sp_p1;

 

— 一时半刻修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存款和储蓄进程

 名称:sp_p1

****************/

 create definer=’test’@’%’ procedure sp_p1(IN a decimal(4,1) )

     begin

     declare  sa varchar(100);

     

     case a

     when -1 then

         set sa = ‘-1’;

     when 0  then

         set sa = ‘0’;   

     when 1 then

         set sa = ‘1’;

     when 11 then

         set sa = ’11’;

     else

         set sa =’other’;

    end case;

      

     select a, sa;

    end$$

   delimiter ;

 

mysql> call sp_p1(0);

+——+——+

| a    | sa   |

+——+——+

|  0.0 | 0    |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(1);

+——+——+

| a    | sa   |

+——+——+

|  1.0 | 1    |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(11);

+——+——+

| a    | sa   |

+——+——+

| 11.0 | 11   |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(12);

+——+——-+

| a    | sa    |

+——+——-+

| 12.0 | other |

+——+——-+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

 

 

(2)语法2:能够不只是对某些固定的变量举办判定。

CASE

    WHEN search_condition THEN statement_list

    [WHEN search_condition THEN statement_list] …

    [ELSE statement_list]

END CASE

 

—  删除已经存在的仓库储存进度。

drop procedure if exists sp_p1;

 

— 权且修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存储进度

 名称:sp_p1

****************/

 create definer=’test’@’%’ procedure sp_p1(IN a decimal(4,1) )

     begin

     declare  sa varchar(100);

     

     case

     when a < 0 then

         set sa = ‘<0’;

     when a=0  then

         set sa = ‘=0’;   

     when a<10 then

         set sa = ‘<10’;

     when a<20 then

         set sa = ‘<20’;

     else

         set sa =’>=20′;

    end case;

      

     select a, sa;

    end$$

   delimiter ;

 

 

mysql> call sp_p1(0);

+——+——+

| a    | sa   |

+——+——+

|  0.0 | =0   |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(1);

+——+——+

| a    | sa   |

+——+——+

|  1.0 | <10  |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(11);

+——+——+

| a    | sa   |

+——+——+

| 11.0 | <20  |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(23);

+——+——+

| a    | sa   |

+——+——+

| 23.0 | >=20 |

+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

8. 操纵流语句CASE-WHEN-THEN-ELSE

 

CASE-WHEN-THEN-ELSE分支语句也能够用于SELECT语句。用于SELECT语句的CASE-WHEN-THEN-ELSE语句和前边普通的CASE-WHEN-THEN-ELSE语句的语法很类似;不一样在于,语句末尾以END结尾,而不是END CASE。

 

三种语法格局如下:

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

 

 

以第壹种语法为例:

 

—  删除已经存在的囤积进程。

drop procedure if exists sp_p1;

 

— 一时修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存款和储蓄进程

 名称:sp_p1

****************/

 create definer=’test’@’%’ procedure sp_p1()

     begin

     

     select a, (case

     when a < 0 then ‘<0’

     when a=0  then  ‘=0’   

     when a<10 then ‘<10’

     when a<20 then ‘<20’

     else ‘>=20’  

    end ) as “case”

    from test;

 

    end$$

   delimiter ;

 

推行结果:

mysql>  call sp_p1();

+—-+——+

| a  | case |

+—-+——+

| -1 | <0   |

|  0 | =0   |

|  1 | <10  |

| 11 | <20  |

| 21 | >=20 |

+—-+——+

5 rows in set (0.00 sec)

 

 

 

9. 储存进度的循环语句

 

9.1. WHILE-DO

—  删除已经存在的存款和储蓄进度。

drop procedure if exists sp_p1;

 

— 权且修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存款和储蓄进度

 名称:sp_p1

****************/

create definer=’test’@’%’ procedure sp_p1(IN a integer )

     begin

     declare  sa varchar(100);

     declare  i  integer;

     set sa = ”;

     set i = 0;

     while  i < a do

       set sa = concat(sa, cast( i as char) );

       set i = i+1;

     end while;

 

     select a, i,sa;

    end$$

   delimiter ;

 

 

执行结果如下:

mysql> call sp_p1(4);

+——+——+——+

| a    | i    | sa   |

+——+——+——+

|    4 |    4 | 0123 |

+——+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(10);

+——+——+————+

| a    | i    | sa         |

+——+——+————+

|   10 |   10 | 0123456789 |

+——+——+————+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

 

9.2. REPEAT-UNTIL

 

—  删除已经存在的蕴藏过程。

drop procedure if exists sp_p1;

 

— 权且修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存款和储蓄进度

 名称:sp_p1

****************/

create definer=’test’@’%’ procedure sp_p1(IN a integer )

     begin

     declare  sa varchar(100);

     declare  i  integer;

     set sa = ”;

     set i = 0;

    

    repeat

       set sa = concat(sa, cast( i as char) );

       set i = i+1;

     until i >= a

     end repeat;

 

     select a, i,sa;

    end$$

   delimiter ;

 

 

施行结果如下:

 

mysql> call sp_p1(4);

+——+——+——+

| a    | i    | sa   |

+——+——+——+

|    4 |    4 | 0123 |

+——+——+——+

1 row in set (0.01 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

mysql> call sp_p1(10);

+——+——+————+

| a    | i    | sa         |

+——+——+————+

|   10 |   10 | 0123456789 |

+——+——+————+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

 

 

 

 

 

9.3. LOOP

 

—  删除已经存在的蕴藏进程。

drop procedure if exists sp_p1;

 

— 目前修改SQL语句的相间符为$$

 delimiter $$

 

/****************

 存款和储蓄进程

 名称:sp_p1

****************/

create definer=’test’@’%’ procedure sp_p1(IN a integer )

     begin

     declare  sa varchar(100);

     declare  i  integer;

     set sa = ”;

     set i = 0;

    

     coe2coe:

     loop

       set sa = concat(sa, cast( i as char) );

       set i = i+1;

       if i >= a then

          leave coe2coe;

       else

          iterate coe2coe;

       end if;

     end loop;

 

     select a, i,sa;

    end$$

   delimiter ;

 

 

在那之中,ITERATE语句用于后续在循环中进行下一回迭代,类似于程序设计语言中的continue语句;LEAVE语句用于跳出循环,类似于程序设计语言中的break。

ITERATE和LEAVE不仅仅能够用于LOOP循环,仍是能够用于WHILE-DO循环和REPEAT-UNTIL循环。

 

执行结果如下:

mysql> call sp_p1(4);

+——+——+——+

| a    | i    | sa   |

+——+——+——+

|    4 |    4 | 0123 |

+——+——+——+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> call sp_p1(10);

+——+——+————+

| a    | i    | sa         |

+——+——+————+

|   10 |   10 | 0123456789 |

+——+——+————+

1 row in set (0.01 sec)

 

 

10. 储存进度中的其它语句

 

10.1. 赋值语句

语法:

SET var_name = expr [, var_name = expr] …

用来对贰个或多少个变量举办赋值。

 

10.2. SELECT INTO 语句

语法:

SELECT col_name[,…] INTO var_name[,…] table_expr

 

用来将SELECT的结果集的多个或七个列的值赋值为相应的变量。

 

mysql> select 1,2,3 into @a,@b,@c;

Query OK, 1 row affected (0.00 sec)

 

mysql> select @a,@b,@c;

+——+——+——+

| @a   | @b   | @c   |

+——+——+——+

|    1 |    2 |    3 |

+——+——+——+

1 row in set (0.00 sec)

 

mysql> select count(*), min(a) , max(a) , avg(a) into @a,@b,@c,@d
from test;

Query OK, 1 row affected (0.00 sec)

 

mysql> select @a,@b,@c,@d;

+——+——+——+————-+

| @a   | @b   | @c   | @d          |

+——+——+——+————-+

|    5 |   -1 |   21 | 6.400000000 |

+——+——+——+————-+

1 row in set (0.00 sec)

 

当结果集带有多行数据,而付出的变量唯有3个时,将唤起出错。

mysql> select * from test;

+—-+

| a  |

+—-+

| -1 |

|  0 |

|  1 |

| 11 |

| 21 |

+—-+

5 rows in set (0.00 sec)

 

mysql> select a into @a from test;

ERROR 1172 (42000): Result consisted of more than one row

 

变量的个数必须和SELECT的结果集的列的个数匹配。

mysql> select a into @a,@b,@c,@d,@e from test;

ERROR 1222 (21000): The used SELECT statements have a different number
of columns

 

 

11. DECLARE错误处理程序

11.1. DECLARE错误处理程序的牵线

declare条件处理程序用于定义一些特定条件下的处理程序,比如出现某种错误和警示时实施一定的处理。

假诺存在以下的数码表test:

mysql> show create table test;

+——-+———————————————————————————————————-+

| Table | Create Table
                                                                                            |

+——-+———————————————————————————————————-+

| test  | CREATE TABLE `test` (

  `a` int(11) NOT NULL,

  PRIMARY KEY (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+——-+———————————————————————————————————-+

1 row in set (0.01 sec)

 

mysql> truncate table test;

Query OK, 0 rows affected (0.07 sec)

 

mysql> select * from test;

Empty set (0.00 sec)

 

先定义三个常备状态下的存储进程:

drop procedure if exists sp_p1;

delimiter $$

create procedure sp_p1()

begin

 

set @x=1;

select @x;

 

insert into test values(1);

set @x=2;

select @x;

 

insert into test values(1);

set @x=3;

select @x;

 

insert into test2 values(1);

set @x=3;

select @x;

 

end;

$$

delimiter ;

 

进行该存款和储蓄进度:

mysql> call sp_p1;

+——+

| @x   |

+——+

|    1 |

+——+

1 row in set (0.00 sec)

 

+——+

| @x   |

+——+

|    2 |

+——+

1 row in set (0.02 sec)

 

ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’

mysql> select * from test;

+—+

| a |

+—+

| 1 |

+—+

1 row in set (0.00 sec)

在那种境况下,当执行到第3个insert语句时,由于主键的绝无仅有约束,该语句会造成错误:

ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’

末尾促成那些蕴藏进程终止执行,即今后的SQL语句不再被实施。

 

再看一下概念了错误处理程序的情形:

概念以下的贮存进程:

drop procedure if exists sp_p2;

delimiter $$

create procedure sp_p2()

begin

declare continue handler for sqlstate ‘23000’ select ‘handler 23000’ as
“handler1”;

declare continue handler for SQLEXCEPTION select ‘handler SQLERROR’ as
“handler2”;

 

set @x=1;

select @x;

 

insert into test values(1);

set @x=2;

select @x;

 

insert into test values(1);

set @x=3;

select @x;

 

insert into test2 values(1);

set @x=3;

select @x;

 

end;

$$

delimiter ;

 

上述存款和储蓄进程中,定义了四个错误处理程序:

(1)处理程序1:

declare continue handler for sqlstate ‘23000’ select ‘handler 23000’ as
“handler1”;

在产生E奥迪Q5RO昂科拉错误代码2三千时,将推行该语句定义的select语句。

错误代码2两千的意思如下:

ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’

 

(2)处理程序2:

declare continue handler for SQLEXCEPTION select ‘handler SQLERROR’ as
“handler2”;

在发出任何未定义过处理程序的ELX570ROEnclave时,将执行该语句定义的select语句。

比如:

ERROR 1146 (42S02): Table ‘test.test2’ doesn’t exist

 

先清空test数据表:

truncate table test;

 

施行该存款和储蓄进度:

mysql> call sp_p2();

+——+

| @x   |

+——+

|    1 |

+——+

1 row in set (0.00 sec)

 

+——+

| @x   |

+——+

|    2 |

+——+

1 row in set (0.07 sec)

 

+—————+

| handler1      |

+—————+

| handler 23000 |

+—————+

1 row in set (0.07 sec)

 

+——+

| @x   |

+——+

|    3 |

+——+

1 row in set (0.07 sec)

 

+——————+

| handler2         |

+——————+

| handler SQLERROR |

+——————+

1 row in set (0.07 sec)

 

+——+

| @x   |

+——+

|    3 |

+——+

1 row in set (0.07 sec)

 

Query OK, 0 rows affected (0.07 sec)

 

mysql> select * from test;

+—+

| a |

+—+

| 1 |

+—+

1 row in set (0.00 sec)

能够看来,在概念了多少个错误处理程序今后,整个存款和储蓄进程得到了一体化的实践。在举行第3个INSE本田UR-VT语句时,还是会爆发SQL错误2两千,而第一个INSE奥迪Q5T语句则会发出SQL错误42S02。可是在这一次实施中并没有生出暗中认可的SQL错误新闻,而且也不曾停歇存储进程的执行。那是因为第三个SQL错误2三千发出时,执行了第四个错误处理程序,输出了handler1,在其次个SQL错误42S02产生时,执行了第一个错误处理程序,输出了handler2。

 

 

11.2. 错误处理程序的处理格局

 

(1)两种种处理情势。

MySQL的DECLARE错误处理程序在发生错误时得以有三种处理格局:

(a)EXIT。当发生错误时,退出程序,即退出存款和储蓄进程。

(b)CONTINUE。当发生错误时,继续执行程序。

 

 

(2)EXIT方式。

drop procedure if exists sp_p2;

delimiter $$

create procedure sp_p2()

begin

declare EXIT handler for sqlstate ‘23000’ select ‘handler 23000’ as
“handler1”;

declare EXIT handler for SQLEXCEPTION select ‘handler SQLERROR’ as
“handler2”;

 

set @x=1;

select @x;

 

insert into test values(1);

set @x=2;

select @x;

 

insert into test values(1);

set @x=3;

select @x;

 

insert into test2 values(1);

set @x=3;

select @x;

 

end;

$$

delimiter ;

 

实践结果:

mysql> truncate table test;

Query OK, 0 rows affected (0.02 sec)

 

mysql> call sp_p2();

+——+

| @x   |

+——+

|    1 |

+——+

1 row in set (0.00 sec)

 

+——+

| @x   |

+——+

|    2 |

+——+

1 row in set (0.00 sec)

 

+—————+

| handler1      |

+—————+

| handler 23000 |

+—————+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

(3)CONTINUE方式。

在后边已经介绍过,此处不再赘述。

 

 

 

 

 

12. 游标

 

(1)MySQL游标介绍。

 

MySQL游标是只读、不帮助滚动、以及敏感的游标。

只读:不得以由此游标来对记录举办修改和删除。

不辅助滚动:只只好从头到尾顺序读取每一条记下;不能够随随便便定位到钦定记录。

乖巧:游标指向原始记录数据,原始记录数据的生成将展现到通过游标读取到的笔录数据。不敏感指游标指向的记录数据只是原始记录数据的一份复制品,原始记录数据的变化不会反映到通过游标读取到的记录数据。

 

 

(2)MySQL游标例子:

 

比方条件:

mysql> create table test2 (id integer primary key, name varchar(100),
birthday date);

Query OK, 0 rows affected (0.08 sec)

 

mysql> insert into test2 values( 1,’zhangsan’,’2017-01-02′);

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into test2 values( 2,’lisi’,’2017-02-03′);

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into test2 values( 3,’wangwu’,’2017-03-04′);

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test2;

+—-+———-+————+

| id | name     | birthday   |

+—-+———-+————+

|  1 | zhangsan | 2017-01-02 |

|  2 | lisi     | 2017-02-03 |

|  3 | wangwu   | 2017-03-04 |

+—-+———-+————+

3 rows in set (0.00 sec)

 

mysql> create table test3 ( id integer primary key, name
varchar(100), birthday date);

Query OK, 0 rows affected (0.07 sec)

 

近来须求采用存款和储蓄进程将test2表的数码总体复制到test3表。

 

储存进度的概念如下:

 

drop procedure if exists sp_p3;

delimiter $$

create procedure sp_p3()

begin

declare cnt integer;

declare id integer;

declare name varchar(100);

declare birthday date;

declare EOF  boolean;

declare c cursor for select * from test2;

declare continue handler for NOT FOUND set EOF=true;

 

set cnt = 0;

set EOF = false;

open c;

loop1:

while EOF = false do

 fetch c into id,name,birthday;

 if EOF = true then

   leave loop1;

 end if;

 insert into test3 (id,name,birthday) values (id,name,birthday);

 set cnt = cnt + 1;

end while;

close c;

 

select  concat(cast( cnt as char) , ‘ records copied!’) as “COPIED”;

end;

$$

delimiter ;

 

存款和储蓄进程中定义了四个游标(CUKugaSO凯雷德):

declare c cursor for select * from test2;

应用open语句打开游标:

open c;

在循环中通过fetch语句取妥当前记下的字段的值到对应的变量中:

fetch c into id,name,birthday;

使用declare错误处理程序检查和测试是或不是到了游标的末梢一条记下。

declare continue handler for NOT FOUND set EOF=true;

接纳close语句关闭游标:

close c;

 

 

举行该存款和储蓄进度,结果如下:

mysql> call sp_p3();

+——————-+

| COPIED            |

+——————-+

| 3 records copied! |

+——————-+

1 row in set (0.04 sec)

 

Query OK, 0 rows affected (0.04 sec)

 

查看test3表:

mysql> select * from test3;

+—-+———-+————+

| id | name     | birthday   |

+—-+———-+————+

|  1 | zhangsan | 2017-01-02 |

|  2 | lisi     | 2017-02-03 |

|  3 | wangwu   | 2017-03-04 |

+—-+———-+————+

3 rows in set (0.00 sec)

 

 

13. 动态SQL

 

13.1. 动态SQL的例子

 

MySQL提供的prepare、execute和deallocate prepare那三条语句来支撑动态SQL效率。

 

drop procedure if exists sp_p4;

delimiter $$

create procedure sp_p4(in id integer, in name varchar(100), out cnt
integer)

begin

declare s varchar(100);

set s = ‘insert into test4 (id,name,time) values (?,?,now())’;

set @sql = s;

set @id = id;

set @name = name;

prepare stmt from @sql;

execute stmt using @id,@name;

deallocate prepare stmt;

select count(*) into cnt  from test4;

end;

$$

delimiter ;

 

调用结果:

mysql> truncate table test4;

Query OK, 0 rows affected (0.02 sec)

 

mysql> call sp_p4(1,’001′,@a);

Query OK, 1 row affected (0.01 sec)

 

mysql> call sp_p4(2,’002′,@a);

Query OK, 1 row affected (0.02 sec)

 

mysql> select @a;

+——+

| @a   |

+——+

|    2 |

+——+

1 row in set (0.00 sec)

 

mysql> select * from test4;

+—-+——+———————+

| id | name | time                |

+—-+——+———————+

|  1 | 001  | 2017-08-28 17:06:23 |

|  2 | 002  | 2017-08-28 17:06:26 |

+—-+——+———————+

2 rows in set (0.00 sec)

 

 

13.2. 动态SQL的注意事项

动态SQL的注意事项如下:

(1)动态SQL能够用来MySQL客户端程序,也足以用于存款和储蓄进度,但不得以用来函数,也不能够用来触发器。

在创立包罗动态SQL的函数时发生如下错误:

ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or
trigger

 

(2)动态SQL中的变量不能够是数据表的称呼。

当数据表的名目是变量时,将报语法错误。

能够使用CONCAT等函数将数据表的称谓的变量拼接组装到动态SQL语句中,再对任何变量使用PREPARE和EXECUTE语句进行动态SQL的参数实行绑定。

 

(3)动态SQL中的SQL不可能是动态SQL相关的言语,即无法是PREPARE语句和EXECUTE以及DEALLOCATE PREPARE语句。

 

(4)动态SQL中待绑定的变量只好是会话变量,即类似@a,@b那样的变量,仅在此时此刻对话中有效,无法一向利用declare定义的一些变量。

 

(5)动态SQL语句使用PREPARE语句中定义的名目来唯一标识。假使对二个称呼定义多个PREPARE语句,则会自行销毁此前定义的动态SQL语句,仅保留最后一个动态SQL。

 

 

14. 翻开存款和储蓄进度的音信

 

14.1. 查阅存款和储蓄进程的概念

mysql>  show create procedure sp_p1;

+———–+——————————————————————————————————————————————-+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+———————-+———————-+——————–+

| Procedure | sql_mode
                                                                                                                                 |
Create Procedure
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
character_set_client | collation_connection | Database Collation |

+———–+——————————————————————————————————————————————-+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+———————-+———————-+——————–+

| sp_p1     |
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
| CREATE DEFINER=`test`@`%` PROCEDURE `sp_p1`(IN p_a
decimal(4,1) ,

                        OUT p_b varchar(10) ,

                       INOUT p_c decimal(4,2),

                       IN p_d decimal(4,3),

                       IN a decimal(4,1)

 )

begin

     declare a decimal(4,1) default 1.23;

     declare b varchar(10) default ‘bbbbbbbbbb’;

     declare c decimal(4,2) default 12.123;

     declare d decimal(4,3) default 0;

     declare e integer;

     set @p = p_d;

     set  d = p_d;

     select p_a,p_b,p_c, a,b,c,d;

     set p_a = a;

     set p_b = b;

     set p_c = c;

     select p_a,p_b,p_c, a,b,c,d;

 

     select ‘======================’;

     set e = 0;

     while e < 6 do

     select e;

     set e = e + 1;

     end while;

     select ‘=========================’;

     select e;

     end | utf8                 | utf8_general_ci      |
latin1_swedish_ci  |

+———–+——————————————————————————————————————————————-+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+———————-+———————-+——————–+

1 row in set (0.00 sec)

 

 

 

 

14.2. 翻开存款和储蓄进程的场所

 

mysql> show procedure status like ‘sp_p1’\G

*************************** 1. row
***************************

                  Db: test

                Name: sp_p1

                Type: PROCEDURE

             Definer: test@%

            Modified: 2017-08-25 20:38:05

             Created: 2017-08-25 20:38:05

       Security_type: DEFINER

             Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

  Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

 

 

14.3. 查阅存款和储蓄进度的元数据消息

mysql> select * from information_schema.routines where
routine_name =’sp_p1’\G

*************************** 1. row
***************************

           SPECIFIC_NAME: sp_p1

         ROUTINE_CATALOG: def

          ROUTINE_SCHEMA: test

            ROUTINE_NAME: sp_p1

            ROUTINE_TYPE: PROCEDURE

               DATA_TYPE:

CHARACTER_MAXIMUM_LENGTH: NULL

  CHARACTER_OCTET_LENGTH: NULL

       NUMERIC_PRECISION: NULL

           NUMERIC_SCALE: NULL

      DATETIME_PRECISION: NULL

      CHARACTER_SET_NAME: NULL

          COLLATION_NAME: NULL

          DTD_IDENTIFIER: NULL

            ROUTINE_BODY: SQL

      ROUTINE_DEFINITION: begin

     declare a decimal(4,1) default 1.23;

     declare b varchar(10) default ‘bbbbbbbbbb’;

     declare c decimal(4,2) default 12.123;

     declare d decimal(4,3) default 0;

     declare e integer;

     set @p = p_d;

     set  d = p_d;

     select p_a,p_b,p_c, a,b,c,d;

     set p_a = a;

     set p_b = b;

     set p_c = c;

     select p_a,p_b,p_c, a,b,c,d;

 

     select ‘======================’;

     set e = 0;

     while e < 6 do

     select e;

     set e = e + 1;

     end while;

     select ‘=========================’;

     select e;

     end

           EXTERNAL_NAME: NULL

       EXTERNAL_LANGUAGE: NULL

         PARAMETER_STYLE: SQL

        IS_DETERMINISTIC: NO

         SQL_DATA_ACCESS: CONTAINS SQL

                SQL_PATH: NULL

           SECURITY_TYPE: DEFINER

                 CREATED: 2017-08-25 20:38:05

            LAST_ALTERED: 2017-08-25 20:38:05

                SQL_MODE:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

         ROUTINE_COMMENT:

                 DEFINER: test@%

    CHARACTER_SET_CLIENT: utf8

    COLLATION_CONNECTION: utf8_general_ci

      DATABASE_COLLATION: latin1_swedish_ci

1 row in set (0.01 sec)

 

 

 

 

 

 

 

相关文章