MySQL基础知识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)

 

当结果集带有多行数据,而被起之变量只生一个时常,将唤起出错。

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)

在这种情况下,当行到第2单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”;

以出ERROR错误代码23000时不时,将执该语句定义之select语句。

错误代码23000之意思如下:

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

 

(2)处理程序2:

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

当发出其他非定义了处理程序的ERROR时,将推行该语句定义的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)

可以看,在概念了一定量只错误处理程序之后,整个存储过程获得了整机的执行。在执行第2个INSERT语句时,仍然会发出SQL错误23000,而第3只INSERT语句则会生SQL错误42S02。但是当这次实施着并无来默认的SQL错误信息,而且为绝非终止存储过程的行。这是盖第一独SQL错误23000来时,执行了第一个错误处理程序,输出了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 ;

 

囤过程被定义了一个游标(CURSOR):

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)

 

 

 

 

 

 

 

相关文章