8、MySLQ存储过程

简述

成都创新互联公司是一家专注于成都网站设计、网站制作和德阳机房服务器托管的网络公司，有着丰富的建站经验和案例。

存储过程是SQL语句和控制流语句的语句串（语句集合）。它不仅可以带有输入

参数还可以带有输出参数，存储过程是能够通过介绍参数向调用者返回结果集，结果集的格式由调用者确定。返回状态值给调用者，指明调用是成功或是失败，包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。

较SQL语句存储过程的优点：

1、存储过程允许组件是编程，存储过程在被创建以后，可以在程序中多次调用，而不必重新编写存储过程的SQL语句，从而提高了程序的可移植性。

2、存储过程能够实现较快的存储速度。

3、存储过程能够减少网络流量。对于针对数据库对象的相同操作如查询，修改表如果这一操作所涉及的SQL语句被组织成存储过程，那么当计算机调用该存储过程时，网络中传送的只是该调用语句，而不是多条SQL语句，从而大大提高网络流量见底网络负载。

4、存储过程可被作为一种安全机制。

一、存储过程的创建

1.1 存储过程语法：

create procedure <过程名>(参数1、参数2、...)
begin
sql;
end

创建存储过程之前我们必须使用delimiter修改MySQL语句的默认结束符，否则不能创建成功。

存储过程语句的注释：

MySQL注释的两种风格

"--":单行注释

/*......*/：一般用于多行注释

语法：

delimiter <新执行符号>
delimiter //将默认结束符修改为//。

1.2 调用存储过程

call <过程名>(参数1、参数2、...);

1.3 存储过程参数类型

1.3.1 IN参数

作用：读取外部变量值，且有效范围仅限于存储过程内部

mysql> delimiter //
mysql> create procedure pin(in p_in int)
    -> begin
    -> select p_in;
    -> set p_in=2;
    -> select p_in;
    -> end;
    -> //

等同于 set @p_in=1;

call pin(2)与select @p_in结果作比较

实例1 存储过程的简单创建和调用

mysql> delimiter %
mysql> create procedure selcg()
    -> begin
    -> select * from category;
    -> end %
Query OK, 0 rows affected (0.00 sec)
mysql> call selcg()%
+---------+---------------+
| bTypeId | bTypeName     |
+---------+---------------+
|       1 | windows应用   |
|       2 | 网站          |
|       3 | 3D动画        |
|       4 | linux学习     |
|       5 | Delphi学习    |
|       6 | ***          |
|       7 | 网络技术      |
|       8 | 安全          |
|       9 | 平面          |
|      10 | AutoCAD技术   |
+---------+---------------+
10 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

定义存储过程getonebook，当输入书的ID后可以调用处对应的书籍记录

mysql> delimiter //
mysql> create procedure getonebook(in id int)
    -> begin
    -> select * from books where bId=id;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call getonebook(4)
    -> ;
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
| bId | bName                           | bTypeId | publishing            | price | pubDate    | author    | ISBN       |
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
|   4 | pagemaker 7.0短期培训教程       | 9       | 中国电力出版社        |    43 | 2005-01-01 | 孙利英    | 7121008947 |
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>

1.3.2 Out参数

作用：不都去外部变量值，在存储过程执行完毕后保留新值。

实例2、

mysql> delimiter //
mysql> create procedure pout(out p_out int)
    -> begin
    -> select p_out;
    -> set p_out=2;
    -> select p_out;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> set @p_out=1;

执行call @p_out存储过程之后，再次使用select @p_out则显示为2;则说明此时变量@P_out已经被赋予2；

1.3.3 如何调用存储过程out类型的返回值

例4、编辑存储过程，使返回值是书名相关信息。

mysql> delimiter //
mysql> create procedure demo(out pa varchar(200))
    -> begin
    -> select bName into pa from books where bId=2;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call demo(@a);
Query OK, 1 row affected (0.00 sec)
mysql> select @a;
+-----------------------+
| @a                    |
+-----------------------+
| ***与网络安全        |
+-----------------------+
1 row in set (0.00 sec)

1.3.4 Inout参数

作用：读取外部变量，在存储过程执行完毕后保留心新值<类似银行存款>

mysql> create procedure pinout(inout p_inout int)
    -> begin
    -> select p_inout;
    -> set p_inout=2;
    -> select p_inout;
    -> end;
    -> //

1.3.5 不加参数的存储过程

如果存储国恒在创建的时候没有指定参数类型，则需要在调用的时候指定参数值。

mysql> create table t1(id int(10));
Query OK, 0 rows affected (0.03 sec
mysql> create procedure t2(n1 int)                                                                                             
    -> begin
    -> set @x=0;
    -> repeat set @x=@x+1;
    -> insert into t2 values(@x);
    -> until @x>n1
    -> end repeat;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> create table t2(id int(10));
Query OK, 0 rows affected (0.03 sec)

mysql> call t2(10); 循环10次
Query OK, 1 row affected (0.03 sec)
结果验证
mysql> select * from t2;

二、存储过程变量的使用

2.1 使用declare进行变量定义

变量定义：declare variable_name [,variable_name......]

datatype [default value];
datatype为MySQL的数据类型，如int，float，date，varchar(length)等
作用:变量赋值可以在不同额存储过程中的继承
create procedure decl()
mysql> delimiter //
mysql> create procedure decl()
    -> begin
    -> declare name varchar(20);
    -> set name=(select bName from books where bI=12);
    -> select name;
    -> end//
Query OK, 0 rows affected (0.00 sec)

二、存储过程的流程控制语句

2.1 BEGIN......END语句

定义由顺序执行的SQL语句构成的块。

语法格式：

BEGIN
Statement Block
END

2.2 IF...ELSE语句

该语句用来定义有条件执行的某些语句，其中ELSE语句是可选择的

语法格式：

IF Boolean_expression
statement
[ELSE [IF boolean_expression] statentent]

2.3 循环语句

1、while......end while:

while 1 do ... if *** then break;end while

2、repeat ......end repeat：

执行操作后检查结果，而while则是执行前进行检查

3、loop......end loop:

loop循环不需要初始化条件，类似while循环，同时repeat循环一样不需要结束条件，leave语句的意义是离不开循环。

4、LABLES标号

可以用在begin repeat while 或者loop语句前，语句标号只能在合法的语句前使用。可以跳出循环，使运行指令达到符合语句的最后异步

5、ITERATE迭代

通过引用符合语句的标号，来重新开始符合语句

查看存储过程：

show create procedure demo \G

查看所有存储过程

mysql> show procedure status\G;

修改存储过程：

使用alter语句修改

alter {procedure|function} sp_name [characteristic...]

characteristic:

{contains SQL| NO SQL|READS SQL DATA|MODIFIES SQL DATA}

| SQL SECURITY {DEFINER|INVOKER}

|COMMENT 'string'

sp_name参数表示存储过程或函数名称

characreristic参数指定存储过程函数的特性。

CONTAINS SQL表示子进程包含SQL语句，但不包含读或写数据的语句；

NO SQL表示子程序中不包含SQL语句

READSSQL DATA表示子程序博阿寒写数据的语句。

SQL SECURITY {DEFINER|INVOKER}指明谁有权限来执行

DEFINER表示只有定义者自己才能够执行

INVOKER表示调用者可以执行

COMMENT 'string'是注释信息

删除存储过程

语法一：drop procedure sp_name

语法二：drop procedure if exists sp_name

注：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

事务是由一组SQL语句组成的逻辑处理单元，要不全成功要不全失败。

事务处理：可以确保非事务性单元的多个操作都能够成功完成，否则不会更新数据资源。

数据库默认事务是自动提交的， 也就是发一条 sql 它就执行一条。如果想多条 sql 放在一个事务中执行，则需要使用事务进行处理。当我们开启一个事务，并且没有提交，mysql 会自动回滚事务。或者我们使用 rollback 命令手动回滚事务。

作用：事务是程序更加可靠，简化错误恢复

四大特性：

原子性(Autmic):事务在执行，要么全做，要么不做。

一致性(Consistency):事务必须是使数据库从一个一致性状态编导另一个一致性状态，一致性与原子性密切相关。在事务开始之前和结束之后，数据库的完整性没有被破坏。

隔离性(Isolation):一个事务的执行不能被其他事务干扰。及一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰，这些通常经过加锁来实现。

持久性(Durability):指一个事务一旦提交，他对数据中的数据的改变就应该是永久性的，接下来的其他操作或故障不应该对齐有任何影响。

mysql事物处理实例

MYSQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现
    begin开始一个事务
    rollback事务回滚
    commit事务确认
2.直接用set来改变mysql的自动提交模式
    mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过
    set autocommit = 0禁止自动提交
    set autocommit = 1开启自动提交

但要注意当用set autocommit = 0的时候，你以后所有的sql都将作为事务处理，直到你用commit确认或 rollback结束，注意当你结束这个事务的同时也开启了新的事务！按第一种方法只将当前的做为一个事务!
MYSQL只有 INNODB和BDB类型的数据表才支持事务处理，其他的类型是不支持的!