create procedure 存储过程名字([parameter])   
[characteristics]
[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
begin   
MySQL 语句;   
end;

[parameter]指定存储过程的参数列表。格式如下：

[IN|OUT|INOUT] param_name type

其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型。

同时，虽然创建语法就这么简单（中括号里为可选），但是实际上，我们有些规避风险的要求：（针对中括号部分）

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
 
routine_body:
    Valid SQL procedure statement or statements

DELIMITER ：在创建存储过程的时候一般都会用下面这句放在开头和结束，目的就是避免mysql把存储过程内部的";"解释成结束符号，最后通过“DELIMITER ;”来告知存储过程结束。注意：“DELIMITER //”语句的作用是将MYSQL的结束符设置为//，因为MYSQL默认的语句结束符为分号;，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END//”（也可其他形式）结束存储过程。

当使用DELIMITER命令时，应该避免使用反斜杠（\）字符，因为反斜杠是MYSQL的转义字符！！！

DELIMITER$$.....END$$ DELIMITER

LANGUAGE SQL：用来说明语句部分是SQL语句，未来可能会支持其它类型的语句。

NOT] DETERMINISTIC：如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”，否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC，默认的就是NOT DETERMINISTIC（非确定的）CONTAINS SQL：表示子程序不包含读或写数据的语句。

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

READS SQL DATA：表示子程序包含读数据的语句，但不包含写数据的语句。

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

SQL SECURITY DEFINER：表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。

SQL SECURITY INVOKER：表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行。（例如上面的存储过程我写的是由调用该存储过程的用户的权限来执行，当前存储过程是用来查询Employee表，如果我当前执行存储过程的用户没有查询Employee表的权限那么就会返回权限不足的错误，如果换成DEFINER如果存储过程是由ROOT用户创建那么任何一个用户登入调用存储过程都可以执行，因为执行存储过程的权限变成了root）

COMMENT 'string':备注，和创建表的字段备注一样。

characteristic使用注意：

在编写存储过程和函数时建议明确指定上面characteristic部分的状态，特别是存在复制的环境中，如果创建函数不明确指定这些状态会报错，从一个非复制环境将带函数的数据库迁移到复制环境的机器上如果没有明确指定DETERMINISTIC, NO SQL, or READS SQL DATA该三个状态也会报错。

WITH内部：

RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。说明在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。

FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

BEGIN--END :指定过程要执行的操作。

sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

创建例子：多个应用例子：

1）只返回单一记录集的存储过程--没传参数的例子：

//创建
delimiter $$
drop procedure if exists ptest;
create procedure ptest()
begin
select * from use1;
end $$
delimiter ;

//调用
call ptest();

2）没有输入输出的有返回值的存储过程:

//创建
delimiter $$
drop procedure if exists ptest1;
create procedure ptest1()
begin
insert into user1 (name) values ('辅助');
end $$
delimiter ;

//调用
call ptest();

3）无返回值--有传参数的例子：

//创建
delimiter $$
drop procedure if exists ptestUpdate;
create procedure ptestUpdate(IN n int)
begin
DECLARE i int;
DECLARE s int;
SET i = 0;

WHILE i <= n 
DO
    update user1 c set c.name='fuzhu' where id =i;
     set i = i + 1;
    END WHILE;
    SELECT s;
end $$
delimiter ;

//调用：
set @number = 6;
call ptestUpdate(@number);

4）存储过程参数：

//创建个例子
delimiter $$
drop procedure if exists ptest2;
create procedure ptest2(IN i int )
begin
DECLARE i int;
select * from user1  where id = 1;
end $$
delimiter ;

//调用：
set @id = 10;

call ptest2(@id);
------------
select @id as id_out;

//以上调用，会返回两个结果集

这里写图片描述

5）存储过程返回值（OUT使用）：

//创建
delimiter $$
CREATE PROCEDURE testOut(
 OUT total INT,
 OUT firstform INT,
 OUT secondform INT)
BEGIN
 -- 总表人数
 SELECT
            count(*) INTO total
        FROM
            alluser
        WHERE
            1=1;
 -- 子表一的人数
 SELECT
            count(*) INTO firstform
        FROM
            user1
        WHERE
            1=1;
 -- 子表二的人数
 SELECT
            count(*) INTO secondform
        FROM
            user2
        WHERE
           1=1;
END $$
delimiter ;

//调用：
call testOut(@total,@firstform,@secondform);
SELECT @total,@firstform,@secondform;

6）存储过程返回集、输入输出参数使用（inout）：

//创建
delimiter $$
drop procedure if exists pr_param_inout;
create procedure pr_param_inout
(
   inout id int
)
begin
   select id as id_inner_1;  -- id 值为调用者传进来的值，输出第一个数据结果集

   if (id is not null) then  -- if..else..中输出第二个数据结果集 
      set id = id + 1;

      select id as id_inner_2;
   else
      select 1 into id;
   end if;

   select id as id_inner_3; -- 输出第三个数据结果集
end $$
delimiter ;


//调用：
set @id = 10;

call pr_param_inout(@id);
------------------
//单纯测下输入
select @id as id_out;

这里写图片描述

（二）修改存储过程函数语法

ALTER {PROCEDURE | FUNCTION} ptest  [characteristic ...]
 
characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

（三）删除存储过程函数语法：（ptest是存储过程名，同下）

DROP {PROCEDURE | FUNCTION} [IF EXISTS] ptest

（四）查看存储过程和函数：

show procedure status like 'ptest'

分别查出的是：数据库名、存储过程名、PROCEDURE参数表示查询存储过程、修改时间、创建时间、安全类型、备注（注释）、字符集、表编码、数据库字符集

这里写图片描述

（五）查看存储过程和函数的创建语法：

SHOW CREATE {PROCEDURE | FUNCTION} ptest

SHOW CREATE PROCEDURE ptest;

分别查出的是：存储过程名、SQL模式、创建的语句、字符集、表编码、数据库编码

这里写图片描述

（六）查看存储过程和函数详细信息：

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='ptest'

红色的可以看到存储过程的SQL以及存储过程的权限。

这里写图片描述

（七）执行存储过程和存储函数需要拥有EXECUTE权限

存储过程和存储函数都是存储在服务器端的SQL语句的集合，要使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现

存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的。

EXECUTE权限的信息存储在information_schema数据库下面的USER_PRIVILEGES表中

CALL sp_name([parameter[,…]]) ;

<font color=#FF7F50 size=4>（八）存储过程流程控制函数：</font>

1. IF语句：

语法如下：具体例子，上面都有使用。

//search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

IF search_condition THEN statement_list 
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] 
END IF

2.CASE语句：

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。语法如下：

/*
    case_value参数表示条件判断的变量；
    when_value参数表示变量的取值；
    statement_list参数表示不同when_value值的执行语句。
*/
CASE case_value 
WHEN when_value THEN statement_list 
[WHEN when_value THEN statement_list] ... 
[ELSE statement_list] 
END CASE

CASE语句还有另一种形式。如下：

/*
    search_condition参数表示条件判断语句；
    statement_list参数表示不同条件的执行语句。
*/
CASE 
WHEN search_condition THEN statement_list 
[WHEN search_condition THEN statement_list] ... 
[ELSE statement_list] 
END CASE

CASE例子：

CASE age 
WHEN 20 THEN SET @count1=@count1+1; 
ELSE SET @count2=@count2+1; 
END CASE ;

也可写成下面这样：

CASE 
WHEN age=20 THEN SET @count1=@count1+1; 
ELSE SET @count2=@count2+1; 
END CASE ;

这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句，并且用END CASE替代END来终止！！

3.LOOP语句：

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP语句本身没有中途停止循环的语句，必须是遇到LEAVE语句等才能停止循环（也就成了死循环）。语法如下：

/*
    begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；
    statement_list参数表示需要循环执行的语句。
*/
[begin_label:] LOOP 
statement_list 
END LOOP [end_label]

4.LEAVE语句：

LEAVE语句主要用于跳出循环控制。其语法如下：

//label参数表示循环的标志。
LEAVE label 

//例子：该示例循环执行count加1的操作。当count的值等于10时，则LEAVE语句跳出循环。
add_num: LOOP 
SET @count=@count+1; 
IF @count=10 THEN 
LEAVE add_num ; 
END LOOP add_num ;

5.ITERATE语句：

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。语法如下：

//label参数表示循环的标志。
ITERATE label 

//例子：该示例循环执行count加1的操作，count值为100时结束循环。如果count的值能够整除2，则跳出本次循环，不再执行下面的SELECT语句。
add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
ELSE IF MOD(@count,2)=0 THEN 
ITERATE add_num; 
SELECT * FROM employee ; 
END LOOP add_num ;

注意：LEAVE语句和ITERATE语句都用来跳出循环语句。但是两者是不同的。

LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。

6.REPEAT语句：

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。语法如下：

//其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。
[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label] 

//例子：该示例循环执行count加1的操作，count值为100时结束循环。
REPEAT 
SET @count=@count+1; 
UNTIL @count=100 
END REPEAT ;

7.WHILE语句：

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时，执行循环内的语句。语法如下：

/*
    search_condition参数表示循环执行的条件，满足该条件时循环执行；
    statement_list参数表示循环的执行语句。
*/
[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label] 


//例子：该示例循环执行count加1的操作，count值小于100时执行循环。
WHILE @count<100 DO 
SET @count=@count+1; 
END WHILE ;

8.光标。（存储过程的demo在本段最下面）

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。

光标作用：

光标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。有以下属性：

a.光标是只读的，也就是不能更新它；

b.光标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；

c.避免在已经打开光标的表上更新数据。

光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

1. 声明光标：

MySQL中使用DECLARE关键字来声明光标。语法如下：

//cursor_name参数表示光标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建光标的结果集
DECLARE cursor_name CURSOR FOR select_statement ;

//例子：下面光标名称为：cur_user ，SELECT 语句是从user表中查出name，age字段的值放到一结果集。
DECLARE cur_user CURSOR FOR SELECT name, age FROM user;

2. 打开光标：

MySQL中使用OPEN关键字来打开光标。语法如下：

//cursor_name参数表示光标的名称。
OPEN  cursor_name ;

//例子：打开一个cur_user的光标：
OPEN cur_user;

3. 使用光标：

MySQL中使用FETCH关键字来使用光标。语法：

/*
cursor_name参数表示光标的名称；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好
*/
FETCH cur_user INTO var_name[,var_name…] ;


//例子：将光标cur_user  中SELECT语句查询出来的信息存入user_name和user_age 中。user_name和user_age 必须在前面已经定义。
FETCH  cur_user  INTO user_name, user_age ;

4. 关闭光标：

MySQL中使用CLOSE关键字来关闭光标。语法：

//cursor_name参数表示光标的名称。关闭之后就不能使用FETCH来使用光标了。
CLOSE  cursor_name ;

注意：MYSQL中，光标只能在存储过程和函数中使用！！

存储过程光标DEMO：将表test_cursor数据复制到test_cursor2。顺便给出SQL异常表

CREATE TABLE IF NOT EXISTS `test_cursor` (  
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  

CREATE TABLE IF NOT EXISTS `test_cursor2` (  
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  


 INSERT INTO `test_cursor` VALUES (1, '辅助1');  
INSERT INTO `test_cursor` VALUES (2, '辅助2');  
INSERT INTO `test_cursor` VALUES (3, '辅助3');  
INSERT  INTO `test_cursor` VALUES (4, '辅助4');



DELIMITER //
drop procedure if exists p_test_cursor;
CREATE PROCEDURE  p_test_cursor()
Begin
DECLARE ID int(11);                 --  获取暂存变量ID，来存查到的ID
DECLARE done INT DEFAULT 0;         --  条件状态量
DECLARE  p_name VARCHAR(255);       --  获取暂存变量name，来存查到的name
DECLARE cur_product  CURSOR FOR SELECT * FROM test_engine.test_cursor;      --  定义光标
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; --  定义状态量，这个是个条件处理,针对NOT FOUND，是一个异常触发器。02000异常是SELECT INTO 语句或 INSERT 语句的子查询的结果为空表触发。
OPEN  cur_product;              --  打开光标
REPEAT                      --  开始循环
FETCH  cur_product INTO ID,p_name;   
IF NOT done THEN
    INSERT INTO test_cursor2 VALUES (ID,p_name);    --  一条条循环去插入
END IF;  
UNTIL done END REPEAT;      --  当done=1的时候结束循环
CLOSE  cur_product;
end;
//
DELIMITER ;

//调用
call  p_test_cursor();

（2）存储过程的优化：

优化是方方面面的，这里即使罗列也很片面。我就罗列存储过程的开发要求规范吧。规范起来也是一种大优化。

（一）SQL优化。存储过程封装的SQL自身的优化；SQL优化详情请见本系列前几篇。

（二）操作前检查。在向表插入数据之前，要判断该条数据在表中是否已经存在了，如果存在，则不再插入数据。同理，在从表中查询数据之前，要先判断该条数据在表中是否存在，如果存在，才能从表中查找数据。

（三）结果标识规范化。不管向表插入数据的操作执行成功与否，都应该有一个标识值来表示执行的结果，这样也方便开发人员对程序流程的追踪和调试。比如，在每条leave语句之前，都应该有一个返回值，我们为此定义一个输出参数。

（四）列出具体的字段名。在以后的软件版本中，新增了字段，那么这条insert语句极有可能会报错。所以罗列出所需字段是规范化很重要的一点。

（五）失败返回。在执行insert语句之后，要用MySQL中自带的@error_count参数来判断插入数据是否成功，方便开发人员跟踪执行结果。比如：如果该参数的值不为0，表示插入失败，那么我们就用一个返回参数值来表示操作失败。

DEMO:

@error_count如果该参数的值不为0，表示插入失败，那么我们就用一个返回参数值来表示操作失败。

drop procedure if exists p_optimization;
delimiter $$
create procedure p_optimization
(
    in    testid int,
    out   p_result    int   -- 0-succ, other-fail
)
         p_optimization_label:begin         -- 循环开始
       declare  p_usertype   int;
       declare  p_datacount  int;

       select count(*) into p_datacount from user1 where id=testid;         -- 第一个结果集
       if p_datacount> 0 then               --  第一个判断开始，p_datacount大于0就跳出循环
       begin
           select name into p_usertype from user1 where id=testid;
       end;
       else
       begin
           set p_result = 1;                        
           leave p_optimization_label;      -- 循环结束
       end;
       end if;                                      --  第一个判断结束

       select count(*) into p_datacount from user1 where id=testid;
       if p_datacount = 0 then                  --  第二个判断开始
       begin
           insert into user1(name) values('辅助');
           if @error_count!=0 then          -- 内嵌的判断开始
           begin
               set p_result= 3;
           end;
           else
           begin
               set p_result= 0;
           end;
           end if;                              -- 内嵌的判断结束
       end;
       else
       begin
           set p_result = 2;
       end;
       end if;                          --  第二个判断结束

       leave p_optimization_label;
end;
$$
delimiter ;


//调用测试：
set @id = 10;
call p_optimization(@id,@p_result);
select @id,@p_result;

这里写图片描述

三、存储函数（自定义函数）概述：

（1）什么是存储函数？？

封装一段sql代码，完成一种特定的功能，必须返回结果。其余特性基本跟存储过程相同。

（2）与存储过程的区别：

1）存储函数有且只有一个返回值，而存储过程不能有返回值。就是说能不能使用return。（函数可返回返回值或者表对象，绝对不能返回结果集）

这里写图片描述

2）函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。

3）存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。比如：工期计算、价格计算。

4）存储过程可以调用存储函数。但函数不能调用存储过程。

5）存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

（3）语法：

创建：

create function 函数([函数参数[,….]]) Returns 返回类型
　　Begin
　　　　If(
　　　　　　Return (返回的数据)
　　　　Else 
　　　　　　Return (返回的数据)
　　　　end if;
　　end;

流控制（Flow-of-control）语句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的.

变量声明(DECLARE)以及指派(SET)是合法的.

允许条件声明.

异常处理声明也是允许的.

但是在这里要记住函数有受限条件：就是不能返回结果集。不能返回结果集。（可以使用select、insert、update等...）

修改、删除和查看状态与存储过程语法基本一样。只不过把procedure替换成function而已。

本博主找到一份详细的MySQL默认函数集合

（4）DEMO：下载地址

（一）helloworld：

//创建
DELIMITER $$
CREATE FUNCTION hello()
RETURNS VARCHAR(255)
BEGIN
RETURN 'Hello  world,I am 辅助';
END $$
DELIMITER ;

//调用：
SELECT hello();

（二）带参数的函数，并且使用insert语句，同理update与delete

//创建
DELIMITER $$
CREATE FUNCTION hello1(userid int ,pname VARCHAR(20))
 RETURNS INT UNSIGNED
   BEGIN
  INSERT into user1(id,name) VALUES(userid ,pname);
     RETURN LAST_INSERT_ID();
    END $$

//调用：因为我给的demo中没有弄自增主键，只能返回个0了。
select hello1(3,'辅助');

（三）带参数的函数，并使用查询语句，这样的返回是返回一个值--只针对单值返回

DELIMITER //
CREATE FUNCTION getName(id INT)
RETURNS CHAR(50)
RETURN (SELECT name FROM user1 WHERE id=id);
//
DELIMITER ;


//调用：
SELECT getName(1);

另一种针对单值返回的方式：把查询出来的用一个参数包装，然后再返回。


DELIMITER //
CREATE FUNCTION getName1(id INT)
RETURNS VARCHAR(50)
Begin
declare number varchar(50);
SELECT count(*)   into number FROM user1 u WHERE u.id=id;
RETURN (number);
end;
//
DELIMITER ;

//调用：
SELECT getName1();

（四）针对多记录返回：

光标的使用，原理使用方式同上面存储过程中的描述。

//创建
DELIMITER //
drop FUNCTION  if exists p_function_cursor;
CREATE FUNCTION  p_function_cursor()   --  创建函数
RETURNS VARCHAR(50)
Begin           
DECLARE done INT DEFAULT 0;     
DECLARE  p_name VARCHAR(255);       --  一个暂存变量去存我们查出来的东西
DECLARE cur_product  CURSOR FOR SELECT name FROM test_engine.test_cursor;       --  定义光标
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
DROP TEMPORARY TABLE IF EXISTS infologs;
 CREATE TEMPORARY TABLE infologs (  --  创建临时表，以此返回多记录

 Id int(11) NOT NULL AUTO_INCREMENT,  

 Msg varchar(255) NOT NULL,  

 PRIMARY KEY (Id)  

 )ENGINE=InnoDB,COLLATE='utf8_general_ci';  

OPEN  cur_product;      --  打开光标
REPEAT                  --  开始循环 
FETCH  cur_product INTO p_name;   --  取出光标的内容到临时变量
IF NOT done THEN         --  
    INSERT INTO infologs(Msg) VALUES (p_name);   --  插入到临时表 
END IF;  
UNTIL done END REPEAT;       --  当done=1的时候结束循环
CLOSE  cur_product;       --  关闭光标
return (select Msg from infologs where Id=2); --  返回查出来的单记录。但是我们可以直接访问内存中的infologs表
end;
//
DELIMITER ;


//调用：分别是调用函数，以及 直接查内存的表
#SELECT p_function_cursor();
#select * from infologs;

MySQL优化笔记（六）--存储过程和存储函数

当一个大型系统在建立时，会发现，很多的SQL操作是有重叠的，个别计算是相同的，比如：业务系统中，计算一张工单的计算方式。当遇到这些情况时，我们运用存储过程就是一个非常棒的优化啦。

本系列：demo下载

（一）MySQL优化笔记（一）--库与表基本操作以及数据增删改

（二）MySQL优化笔记（二）--查找优化（1）（非索引设计）

（三）MySQL优化笔记（二）--查找优化（2）（外连接、多表联合查询以及查询注意点）

（四） MySQL优化笔记（三）--索引的使用、原理和设计优化

（五） MySQL优化笔记（四）--表的设计与优化（单表、多表）

（六）MySQL优化笔记（五）--数据库存储引擎

（七）MySQL优化笔记（六）--存储过程和存储函数

（八）MySQL优化笔记（七）--视图应用详解

（九） MySQL优化笔记（八）--锁机制超详细解析（锁分类、事务并发、引擎并发控制）

本文章结构：（1）存储过程概述；（2）存储过程使用、优化；（3）存储函数概述；（4）存储函数使用、优化。

一、存储过程概述：

（1）什么是存储过程？

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

（2）存储过程优点：

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

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

存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。

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

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

比如一个表不直接允许用户直接访问，但要求允许用户访问和修改其中一个或多个字段，那就可以通过一个存储过程来实现并允许该用户使用该存储过程。

（3）存储过程缺点：

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

2）可移植性差。因为存储过程必须跟你的业务逻辑紧密结合才合理，导致它的迁移性很差。

二、 存储过程使用、优化：

（1）存储过程的使用

（一）存储过程创建：

[parameter]指定存储过程的参数列表。格式如下：

其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型。

同时，虽然创建语法就这么简单（中括号里为可选），但是实际上，我们有些规避风险的要求：（针对中括号部分）

当使用DELIMITER命令时，应该避免使用反斜杠（\）字符，因为反斜杠是MYSQL的转义字符！！！

LANGUAGE SQL：用来说明语句部分是SQL语句，未来可能会支持其它类型的语句。

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

READS SQL DATA：表示子程序包含读数据的语句，但不包含写数据的语句。

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

SQL SECURITY DEFINER：表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。

COMMENT 'string':备注，和创建表的字段备注一样。

characteristic使用注意：

WITH内部：

RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

BEGIN--END :指定过程要执行的操作。

sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

创建例子：多个应用例子：

1）只返回单一记录集的存储过程--没传参数的例子 ：

2）没有输入输出的有返回值的存储过程:

3）无返回值--有传参数的例子：

4）存储过程参数：

5）存储过程返回值（OUT使用）：

6）存储过程返回集、输入输出参数使用（inout）：

（二） 修改存储过程函数语法

（三）删除存储过程函数语法：（ptest是存储过程名，同下）

（四）查看存储过程和函数：

分别查出的是：数据库名、存储过程名、PROCEDURE参数表示查询存储过程、修改时间、创建时间、安全类型、备注（注释）、字符集、表编码、数据库字符集

（五）查看存储过程和函数的创建语法：

分别查出的是：存储过程名、SQL模式、创建的语句、字符集、表编码、数据库编码

（六）查看存储过程和函数详细信息：

红色的可以看到存储过程的SQL以及存储过程的权限。

（七）执行存储过程和存储函数需要拥有EXECUTE权限

存储过程和存储函数都是存储在服务器端的SQL语句的集合，要使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现

存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的。

EXECUTE权限的信息存储在information_schema数据库下面的USER_PRIVILEGES表中

<font color=#FF7F50 size=4>（八）存储过程流程控制函数：</font>

1. IF语句：

语法如下：具体例子，上面都有使用。

2.CASE语句：

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。语法如下：

CASE语句还有另一种形式。如下：

CASE例子：

也可写成下面这样：

这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句，并且用END CASE替代END来终止！！

3.LOOP语句：

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP语句本身没有中途停止循环的语句，必须是遇到LEAVE语句等才能停止循环（也就成了死循环）。语法如下：

4.LEAVE语句：

LEAVE语句主要用于跳出循环控制。其语法如下：

5.ITERATE语句：

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。语法如下：

注意：LEAVE语句和ITERATE语句都用来跳出循环语句。但是两者是不同的。

二、存储过程使用、优化：

1）只返回单一记录集的存储过程--没传参数的例子：

（二）修改存储过程函数语法

1）存储函数有且只有一个返回值，而存储过程不能有返回值。就是说能不能使用return。（函数可返回返回值或者表对象，绝对不能返回结果集）

2）函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。