mysql存储过程

存储过程

存储过程优点

1.存储过程只在创建时编译，以后每次执行都不需要重新编译，而一般得sql语句每次执行都需要重新编译，所以使用存储过程可以提高数据库执行效率。
2.当对表进行复杂操作时(如对多个表进行update,insert,query,delete时),可将这些复杂操作用存储过程封装起来与数据库提供的事务处理结合使用，这些操作，如果用程序来完成就变成了一条条的sql语句，可能需要多次连接数据库，而使用存储过程，只需要连接一次数据库就可以了。
3.存储过程可以重复使用，可以减少数据库开发人员的工作量
4.安全性高，可以设定只有某用户才具有对指定存储过程的使用权

存储过程缺点

1.可移植性很差,不同的数据库不兼容
2.对于很简单的sql语句，存储过程没什么优势
3.使用存储过程不一定会减少网络传输，（存储过程的sql语句并不多，而且sql语句执行比较快，sql语句执行的频率也不大）
4.如果只有一个用户使用数据库，那么存储过程对于安全也没有什么影响
5.团队开发需要统一标准，否则后期维护是个麻烦
6.在大并发访问量的情况下，不宜写过多的设计运算的存储过程
7.业务逻辑复杂时，特别是涉及到对很大的表进行操作时，不如在前端先简化业务逻辑

互联网企业并发较大，不合适使用存储过程，内部使用的项目，并发较小，可以使用存储过程

创建存储过程

存储过程的参数

in参数的例子

in参数的特点；读取外部变量值，且有效范围仅限于存储过程内部

delimiter //
create procedure `p1`(in p int)
begin
    select p;
    set p=2;
    select p;
end//
delimiter ;

执行结果

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

mysql> select @p;
+------+
| @p   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> call p1(@p);
+------+
| p    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| p    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @p;
+------+
| @p   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
out参数

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

delimiter //
create procedure `p2`(out p int)
begin
    select p;
    set p=2;
    select p;
end//
delimiter ;

执行结果

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

mysql> call p2(@p);
+------+
| p    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

+------+
| p    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @p;
+------+
| @p   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
inout参数

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

delimiter //
create procedure p3(inout p int)
begin
    select p;
    set p=2;
    select p;
end//
delimiter ;

例子

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

mysql> call p3(@p);
+------+
| p    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| p    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @p;
+------+
| @p   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
不带参数类型
delimiter //
create procedure `p5`(p int)
begin
    set @x=0;
    repeat
        insert into `t5` value (@x);
        set @x=@x+1;
        until @x>p
    end repeat;
end//
delimiter ;

变量定义

mysql中使用declare进行变量定义，语法

declare variable_name[,variable_name....] datetype [default value];

其中datetype为mysql的数据类型，例如int,float,date,varchar(lenght),例如

declare i int unsigned default 1000;
declare n number(8,2) default 10.11;
declare d date default '2016-10-27';
declare t datetime default '2016-10-27 13:12:08';
declare c varchar(88) default 'default string';

变量赋值

mysql中使用set命令对变量赋值，语法
set variable_name=value[,variable_name=value....]
注意，变量赋值是可以在不同存储过程中继承的

mysql> create procedure p6() set @last='last';
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p7() select concat('Last string is',@last);
Query OK, 0 rows affected (0.00 sec)

mysql> call p7;
+--------------------------------+
| concat('Last string is',@last) |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p6;
Query OK, 0 rows affected (0.00 sec)

mysql> call p7;
+--------------------------------+
| concat('Last string is',@last) |
+--------------------------------+
| Last string islast             |
+--------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

变量作用域

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

注释

"--" 单行注释
"/

...

/" 用于多行注释

循环语句以及流程控制语句

与触发器中的语法一致，可参考mysql触发器

查看存储过程状态

三种方式

mysql> show procedure status where Name='p7'\G;
*************************** 1. row ***************************
                  Db: test
                Name: p7
                Type: PROCEDURE
             Definer: root@%
            Modified: 2016-10-27 13:22:11
             Created: 2016-10-27 13:22:11
       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)

ERROR: 
No query specified

mysql> show create procedure p7\G;
*************************** 1. row ***************************
           Procedure: p7
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `p7`()
select concat('Last string is',@last)
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> select * from information_schema.Routines where ROUTINE_NAME='p1' and ROUTINE_TYPE='PROCEDURE'\G;
*************************** 1. row ***************************
           SPECIFIC_NAME: p1
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: 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
    select p;
    set p=2;
    select p;
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: 2016-10-27 11:34:14
            LAST_ALTERED: 2016-10-27 11:34:14
                SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@%
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

修改存储过程

mysql不支持修改存储过程中的代码，就不记录了

删除存储

drop procedure `p1`;

标签: mysql, mysql存储过程

添加新评论