存储过程

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

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

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

3)存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
心得：功能强大，逻辑强大

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

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

基本语法

一.创建存储过程
CREATE PROCEDURE 存储过程名 (参数列表)

  BEGIN

        SQL语句代码块

   END

二.调用存储过程
1.基本语法：call存储过程名()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程
1.基本语法：
drop procedure存储过程名//

2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.其他常用命令

1.showprocedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.showcreate procedure存储过程名
显示某一个MySQL存储过程的详细信息

例子

1.新建数据表`sys_test

CREATE TABLE`sys_test` (

  `id` varchar(255)NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `compeny` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `tel` varchar(255) DEFAULT NULL,
  `work` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)

) ENGINE=InnoDBDEFAULT CHARSET=utf8;

在表中随机插入数据

 创建存储过程-- 不带参数

CREATE PROCEDUREabc ()
BEGIN
select * from sys_test;
END

调用存储过程

call abc();

关于输入输出参数

IN为输入， 定义参数时，可以不加，不加则默认为输入参数。OUT为输出，定义参数时，必须加上。INOUT为输入和输出，必须加上。表示该参数可以输入也可在处理后存放结果进行输出。

 

创建存储过程--IN 带参数

CREATE PROCEDURE  test1( IN X INT)

BEGIN

         SELECT X;

         SET X=100; ##设置变量

         SELECT X;

 

END

调用存储过程

call test1(5);

或者

SET @n=2; ##设置变量

call test1 (@n);

创建存储过程--out 带参数

CREATE DEFINER=`root`@`localhost` PROCEDURE `test3`(out n int)

BEGIN

SELECT COUNT(id) INTO n FROM sys_test ;

END

调用存储过程

CALL test3(@n);

SELECT @n as '总数';

注意事项：

自定义变量：DECLARE  a INT ; SET a=100;    可用以下语句代替：DECLARE a INT DEFAULT 100;

变量分为用户变量和系统变量，系统变量又分为会话和全局级变量

用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理

流程控制

一、顺序结构

二、分支结构
if
case

三、循环结构
for循环
while循环
loop循环
repeat until循环

区块定义，常用
begin
......
end;

例如：if…else

CREATEDEFINER=`root`@`localhost` PROCEDURE `test4`(inout p int)

BEGIN

    if p = 4 then

    set @pg = 400;

        else

          set @pg = 500;

    end if;

select@pg;

END

调用存储过程

set @P =2;

CALL test4(@P);