存储过程

发表于 更新于 分类于 阅读次数：

参考

https://blog.csdn.net/aigao3209/article/details/101336418

一、存储过程

什么是存储过程

大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都这么简单。

经常会有一个完整的操作需要很多条才能完成。

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

为什么要使用存储过程

  • (1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

  • (2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

  • (3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

  • (4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

  • (5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

为什么不使用存储过程：

  • 1） 可移植性差

  • 2） 对于简单的SQL语句，存储过程没什么优势

  • 3） 如果存储过程中不一定会减少网络传输

  • 4） 如果只有一个用户使用数据库，那么存储过程对安全也没什么影响

  • 5） 团队开发时需要先统一标准，否则后期维护成本大

  • 6） 在大并发量访问的情况下，不宜写过多涉及运算的存储过程

  • 7） 业务逻辑复杂时，特别是涉及到对很大的表进行操作的时候，不如在前端先简化业务逻辑

定义存储过程
语法：

1
2
3
4
5
6
7
create procedure 过程名（参数1，参数2....）

begin

sql语句;

end

创建存储过程之前我们必须修改mysql语句默认结束符 ; 要不能我们不能创建成功

使用delimiter可以修改执行符号

DELIMITER是分割符的意思，因为MySQL默认以”;”为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

语法：

1
2
3
4
5
6
7
8
9
10
11
delimiter 新执行符号

mysql> delimiter %  这样结束符就为%

mysql> create procedure selCg()

    -> begin

    -> select * from category;

    -> end %

调用存储过程
语法：

1
2
3
4
call 过程名（参数1，参数2）;

mysql> call selCg() %

存储过程参数类型
In参数
特点：读取外部变量值，且有效范围仅限存储过程内部

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> delimiter //

mysql> create procedure pin(in p_in int)

    -> begin

    -> select p_in;

    -> set p_in=2;

    -> select p_in;

    -> end;

-> //

mysql> delimiter ;  使用完马上恢复默认的

mysql> set @p_in=1;

等同于

对比下，

例：定义存储过程 getOneBook，当输入某书籍 id 后，可以调出对应书籍记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> create procedure getOneBook(in b int)

-> begin

-> select * from books where bId=b;

-> end //

Query OK, 0 rows affected (0.01 sec)

mysql> call getOneBook(3);//

+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+

| bId | bName                       | bTypeId | publishing                  | price | pubDate    | author | ISBN      |

+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+

|   3 | 网络程序与设计－asp | 2       | 北方交通大学出版社|    43 | 2005-02-01 | 王玥| 75053815x |

+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+

1 row in set (0.00 sec)

Out参数
特点：不读取外部变量值，在存储过程执行完毕后保留新值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> delimiter //

mysql> create procedure pout(out p_out int)

    -> begin

    -> select p_out;

    -> set p_out=2;

    -> select p_out;

    -> end;

    -> //

mysql> delimiter ;

mysql> set @p_out=1;

mysql> call pout(@p_out);

等同于

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
call pout()=2; set @p_out=2;

create procedure pout(out p_outs int) 杯子A的定义

    -> begin

    -> set p_outs=8;  p_outs 理解为杯子A，作用于存储过程内部

    -> select p_outs;

    -> end; 存储过程执行完毕，杯子A中放的是8

mysql> set @p_out=1; 系统中又定义一个杯子B

select @p_out； 1 杯子B中放的是1

mysql> call pout(@p_out); 该语句执行时，存储过程内部用过杯子A去保存执行的结果，然后执行完毕，用户拿杯子B，去接杯子A的值，实际上就是等于将杯子A的值赋值给杯子B，所以，执行完后，杯子B里面放的是8

call pout()=8（这个8放在杯子A中）; set @p_out（杯子B）=8 （这个8是从杯子A中来的）;

select @p_out； 8


不论你怎么赋值都是2 注意此处的call pout(@p_out); 中的这个@p_out这个参数,是我们在系统中定义的那个@p_out

我们的存储过程中的那个p_out参数是存储过程中自己用的,作用范围仅限于存储过程的的begin到end之间,所以这两个p_out是不同的两个参数

这里只是刚好名字一样而已.

In传入参数,是外部将值传给存储过程来使用的,而out传出参数是为了讲存储过程的执行结果回传给调用他的程序来使用的.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> create procedure demo(out pa varchar(200))

    -> begin

    -> select bName into pa from books where bId=3;

    -> end //

调用，执行：

mysql> call demo(@a); //

查看变量@a 中的值：

mysql> select @a;//

+-----------------------------+

| @a                          |

+-----------------------------+

| 网络程序与设计－asp         |

+-----------------------------+

Inout参数
特点：读取外部变量，在存储过程执行完后保留新值<类似银行存款>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> delimiter //

mysql> create procedure pinout(inout p_inout int)

    -> begin

    -> select p_inout;

    -> set p_inout=2;

    -> select p_inout;

    -> end;

    -> //

mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call pinout(@p_inout);

不加参数的情况
如果在创建存储过程时没有指定参数类型，则需要在调用的时候指定参数值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> create table t2(id int(11)); 创建表

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;

    -> //

mysql> delimiter ;

mysql> call t2(5); 循环5次

存储过程变量的使用
MySQL中使用declare进行变量定义

变量定义：DECLARE variable_name [,variable_name…] datatype [DEFAULT value];

datatype为MySQL的数据类型，如:int, float, date, varchar(length)

变量赋值： SET 变量名 = 表达式值 [,variable_name = expression …]

变量赋值可以在不同的存储过程中继承

1
2
3
4
5
6
7
8
9
10
11
mysql> create procedure decl()

    -> begin

    -> declare name varchar(200);

    -> set name=(select bName from books where bId=12);

    -> select name;

-> end//

存储过程语句的注释
做过开发的都知道，写注释是个利人利己的事情。便于理解维护

MySQL注释有两种风格

“–“：单行注释

“/…../”:一般用于多行注释

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> create procedure decl() --procedure name is decl

->/*procedure body

    ->/* start begin */

    -> begin

    -> declare name varchar(200);

    -> set name=(select bName from books where bId=12);

    -> select name;

-> end//

存储过程流程控制语句
变量作用域：
内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派给会话变量来保存其值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql > DELIMITER // 

mysql > CREATE PROCEDURE proc3() 

     -> begin

     -> declare x1 varchar(5) default 'outer'; 

     -> begin

     -> declare x1 varchar(5) default 'inner'; 

     -> select x1; 

     -> end; 

     -> select x1; 

     -> end; 

     -> // 

mysql > DELIMITER ;

条件语句
1：if-then -else语句

2：case语句：

循环语句：
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迭代

通过引用复合语句的标号,来从新开始复合语句

查看存储过程
查看存储过程内容：

mysql> 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参数表示存储过程或函数的名称

characteristic参数指定存储函数的特性

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

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

READS SQL DATA表示子程序中包含读数据的语句

MODIFIES SQL DATA表示子程序中包含写数据的语句

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

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

INVOKER表示调用者可以执行

COMMENT ‘string’是注释信息。

–

/**/

删除存储过程
语法：

方法一：DROP PROCEDURE 过程名

1
mysql> drop procedure p_inout;

方法二：DROP PROCEDURE IF EXISTS存储过程名

这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程