关于存储过程

什么是存储过程？

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

存储过程与函数和触发器的区别？

从语法上看，存储过程和函数以及触发器是十分类似的。我们甚至可以说，触发器和函数就是一种特殊的存储过程。不过它们之间还是有一些区别的。
触发器用于完成一些触发条件所引起的操作。触发器的执行是自动化的。
自定义函数只能通过return语句返回单个值或者表对象，而存储过程不能调用return语句，但是可以通过out参数返回多个值。函数可以在SQL语句中结合使用。函数不能用临时表，只能用表变量，还有一些系统函数都不可用等等。
存储过程用于完成一系列的SQL操作，批量化的完成数据库操作工作，由使用者调用执行。

为什么使用存储过程？

存储过程只在创造时进行编译，以后每次执行存储过程都不需要再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。
当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事物处理结合一起使用。这些操作，如果用程序来完成，就变成一条条的SQL语句，可能要多次连输数据库，而换作存储过程，只需要连接一次数据库就可以了。
存储过程可以重复使用，可减少数据库开发人员的工作量。
安全性高，可设定只有某用户才具有对指定存储过程的使用权限。

为什么不使用存储过程？

可移植性差。
对于很简单的SQL语句，存储过程没有什么优势。
存储过程中不一定会减少网络传输。
如果只有一个用户使用数据库，那么存储过程从安全上讲也没什么优势。
团队开发时需要先统一标准。否则后期维护时个麻烦。
在大并发量访问的情况下，不宜写过多涉及运算的存储过程。
业务逻辑复杂时，特别是涉及到对很大的表进行操作的时候，不如在前端先简化业务逻辑。

存储过程中变量定义

MySQL中使用DECLARE进行变量定义。用法：DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中，dataype为MySQL的数据类型，例如： int, float, date, varchar(length)
例如：

DECLARE I_int int unsigned default 4000000;

DECLARE I_numeric number(8,2) DEFAULT 9.95;

DECLARE I_date date DEFAULT ‘1999-12-31’；

DECLARE I_datetime datetime DEFAULT '1999-12-31 23:59:59';

DECLARE I_varchar varchar(255) DEFAULT 'This will not be padded';

存储过程中变量赋值

存储过程中变量赋值和MySQL的变量赋值语法是一样的，使用SET命令进行变量赋值。语法：SET 变量名 = 表达式 [,variable_name = expression ...]
注意：变量赋值是可以在不同存储过程中继承的。如下图例子：

存储过程变量的作用域

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

一个简单的例子

存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型，IN,OUT,INOUT，形式如：
CREATE PROCEDURE([ [IN |OUT |INOUT ] 参数名数据类型...])
IN 输入参数：表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值。
OUT 输出参数：该值可在存储过程内部被改变，并可返回。
INOUT 输入输出参数：调用时指定，并且可被改变和返回。

IN参数的存储过程例子

IN参数的特点在于，读取外部变量的值，且有效范围仅限存储过程内部。如下图例子：

OUT参数的存储过程例子

OUT参数的特点在于，不读取外部变量值，在存储过程执行完毕后保留新值。如下图例子：

INOUT参数的存储过程例子

INOUT参数的特点在于，读取外部变量值，在存储过程执行完毕后保留新值。如下图例子：

不带参数的存储过程例子

如果在创建存储过程时没有指定参数类型，则需在调用的时候指定参数值。如下图例子：

IF-THEN-ELSE

mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_demo_8(IN pin8 int)
    -> begin
    -> declare var int;
    -> set var=pin8+1;
    -> if var=1 then insert number values (15);
    -> end if;
    -> if var=2 then insert number values (22);
    -> else insert into number values (33);
    -> end if;
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL pro_demo_8();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.pro_demo_8; expected 1, got 0
mysql> CALL pro_demo_8(@pin8);
Query OK, 1 row affected (0.03 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  33 |
+-----+
12 rows in set (0.00 sec)

mysql> SET @pin8=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL pro_demo_8(@pin8);
Query OK, 1 row affected (0.06 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  33 |
|  15 |
|  33 |
+-----+
14 rows in set (0.00 sec)

CASE

mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_demo_9(IN pin9 int)
    -> begin
    -> declare var int;
    -> set var=pin9+1;
    -> case var
    -> when 1 then insert into number values (111);
    -> when 2 then insert into number values (222);
    -> else insert into number values (333);
    -> end case;
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> SET @pin9=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL pro_demo_9(@pin9);
Query OK, 1 row affected (0.03 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  33 |
|  15 |
|  33 |
| 111 |
+-----+
15 rows in set (0.00 sec)

mysql> SET @pin9=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL pro_demo_9(@pin9);
Query OK, 1 row affected (0.03 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  33 |
|  15 |
|  33 |
| 111 |
| 222 |
+-----+
16 rows in set (0.00 sec)

mysql> SET @pin9=3;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL pro_demo_9(@pin9);
Query OK, 1 row affected (0.04 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  33 |
|  15 |
|  33 |
| 111 |
| 222 |
| 333 |
+-----+
17 rows in set (0.00 sec)

mysql>

WHILE

mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_demo_10()
    -> begin
    -> declare var int;
    -> set var=0;
    -> while var<6 do
    -> insert into number values (var);
    -> set var=var+1;
    -> end while;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL pro_demo_10();
Query OK, 1 row affected (0.21 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  33 |
|  15 |
|  33 |
| 111 |
| 222 |
| 333 |
|   0 |
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+
23 rows in set (0.00 sec)

mysql>

REPEAT

mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_demo_11()
    -> begin
    -> declare v int;
    -> set v=0;
    -> repeat
    -> insert into number values(v);
    -> set v=v+1;
    -> until v>=5
    -> end repeat;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL pro_demo_11();
Query OK, 1 row affected (0.18 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  33 |
|  15 |
|  33 |
| 111 |
| 222 |
| 333 |
|   0 |
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   0 |
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
28 rows in set (0.00 sec)

mysql>

LOOP

mysql> truncate number;
mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_demo_12()
    -> begin
    -> declare v int;
    -> set v=0;
    -> myloop01:loop
    -> insert into number values (v);
    -> set v=v+1;
    -> if v>=5 then
    -> leave myloop01;
    -> end if;
    -> end loop myloop01;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL pro_demo_12();
Query OK, 1 row affected (0.27 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|   0 |
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
5 rows in set (0.00 sec)

mysql>

ITERATE

mysql> truncate number;
mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_demo_13()
    -> begin
    -> declare v int default 0;
    -> myloop01:loop
    -> set v=v+1;
    -> if v<10 then ITERATE myloop01;
    -> elseif v>20 then leave myloop01;
    -> else insert into number values (v);
    -> end if;
    -> end loop myloop01;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL pro_demo_13();
Query OK, 1 row affected (0.41 sec)

mysql> select * from number;
+-----+
| num |
+-----+
|  10 |
|  11 |
|  12 |
|  13 |
|  14 |
|  15 |
|  16 |
|  17 |
|  18 |
|  19 |
|  20 |
+-----+
11 rows in set (0.00 sec)

mysql>

查看存储过程状态

mysql> show procedure status like 'pro_demo_%'\G

查看指定存储过程内容

mysql> SHOW CREATE PROCEDURE pro_demo_12\G
*************************** 1. row ***************************
           Procedure: pro_demo_12
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_demo_12`()
begin
declare v int;
set v=0;
myloop01:loop
insert into number values (v);
set v=v+1;
if v>=5 then
leave myloop01;
end if;
end loop myloop01;
end
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql>

posted @ 2018-04-16 09:25 Chiliast 阅读(472) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

爱代码

代码改变世界

关于存储过程

公告