mysql存储过程

存储过程的描述：

是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量 。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

使用存储过程的优点：

• 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

• 当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

• 存储过程可以重复使用，可减少数据库开发人员的工作量。

• 安全性高，可设定只有某此用户才具有对指定存储过程的使用权。

• 减少网络传输和防止SQL注入。

使用存储过程的缺点：

• SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，如果如果把业务逻辑全放在存储过程里面，违背了这一原则。

• 如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。

• 开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。

• 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。

• 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

存储过程与函数的区别：

• 自定义函数有且只有一个返回值，就像普通的函数一样，可以直接在表达式中嵌入调用。 存储过程可以没有返回值，也可以有任意个输出参数，必须单独调用。

• 执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。

• 函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数都不可用等等。而存储过程的限制相对就比较少

• 函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数都不可用等等。而存储过程的限制相对就比较少

• 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。对于存储过程来说可以返回参数，而函数只能返回值或者表对象。

• 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

创建存储过程：

• 语法如下：

-- 创建存储过程语法
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
-- 创建自定义函数语法
CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

DEFINER：指明使用存储过程的访问权限。sp_name: 存储过程名称。proc_parameter:
    [ IN | OUT | INOUT ] param_name type
   IN：表示向存储过程中传入参数；存储过程默认为传入参数，所以参数in可以省略；
   OUT：表示向外传出参数； 
   INOUT：表示定义的参数可传入存储过程，并可以被存储过程修改后传出存储过程；
   param_name：参数名；
   type：参数的类型，可以为mysql任何合法得数据类型。
   如果有多个参数，参数之间可以用逗号进行分割。func_parameter:
    param_name typetype:
    所有MySQL支持的数据类型characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }
  LANGUAGE SQL子句是没有作用的。仅仅是为了说明下面过程的主体使用SQL语言编写。是系统默认的。
  如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”（ DETERMINISTIC），否则就是“非确定”的。默认的就是NOT DETERMINISTIC。
 CONTAINS SQL表示子程序不包含读或写数据的语句。
 NO SQL表示子程序不包含SQL语句。
 READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。
 MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。
 SQL SECURITY特征可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。routine_body: 包含合法的SQL过程语句。可以使用复合语句语法, 复合语句可以包含声明，循环和其它控制结构语句。

ex:

delimiter //drop procedure if exists test   //    如果存在test则删除create procedure test                                         /* 存储过程名 */(in inparms int, out outparams varchar(32))                       /* 输入、输出参数 */begin                                                           /* 语句块头 */
     declare str char(10);                                      /* 变量声明 */
     if inparms = 1 then                                        /* IF条件开始*/
         set str = 'hello';                                        /* 赋值 */
     else
         set str = 'world';
     end if;                                                     /* IF结束 */
  
     insert into word(word) values (str);                                /* SQL语句 */
     select word from word limit 1 into outparams;
end//delimiter ;call test(1, @out);   /*调用存储过程*/

查看存储过程：

• 显示存储过程：show create procedure sp_name
  ex:

--查看test存储过程
show create procedure test;

• 显示存储过程特征：show procudure status [like 'pattern']
  ex:

--查看test存储过程状态信息
show  procedure status like 'test%'\G;

• 显示存储过程的CODE信息：show procedure code sp_name
  ex:

show procedure code test//;

调用存储过程：call sp_name([parameter[,...]])

ex:

call test(1,@out);

ps:存储过程之间不能相互调用

修改存储过程:

• 语法如下：

    alter
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_body

ps:用法可以在MySQL命令窗口输入help alter procedure 查看

删除存储过程：drop{procedure| function} [if exists] sp_name

ex:

-- 删除test存储过程
drop procedure if exists test;

作者：StrongZhao
链接：https://www.jianshu.com/p/69e8f36fcea2