MySQL自学笔记——存储过程

含义

存储过程的英文是Stored Procedure。它的思想很简单，就是一组经过预先编译的SQL语句的封装。存储过程预先存储在MySQl服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

好处

1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力

2、减少操作过程中的失误，提高效率

3、减少网络传输量(客户端不需要把所有的SQL语句通过网络发给服务器)

4、减少了SQL语句暴露在网上的风险，也提高了数据查询的安全性

分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下:

1、没有参数(无参数无返回)

2、仅仅带IN类型(有参数无返回)

3、仅仅带OUT类型(无参数有返回)

4、既带IN又带OUT (有参数有返回)

5、带INOUT (有参数有返回)

注意: IN、OUT、INOUT都可以在一个存储过程中带多个。

语法

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名，参数类型，...)
[characteritics...] #表示创建存储过程时指定的对存储过程的约束条件
BEGIN
     存储过程体
END

 characteristics 取值信息如下:

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL   |   NO SQL   I   READS SQL DATA   I   MODIFIES SQL DATA}

| SQL SECURITY { DEFINER   |   INVOKER }
| COMMENT ‘string'

 LANGUAGE SQL :说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL

[NOT] DETERMINISTIC: 指明存储过程执行的结果是否确定。

DETERMINISTIC表示结果是确定的。 每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。

如果没有指定任意一个值， 默认为NOT DETERMINISTIC

{ CONTAINS SQLI NO SQL | READS SQL DATA | MODIFIES SQL DATA } :指明子程序使用SQL语句的限制。

ONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句;

NO SQL表示当前存储过程的子程序中不包含任何SQL语句;

READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句;

MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。

默认情况下，系统会指定为CONTAINS SQL.

SQL SECURITY {DEFINER  |  INVOKER}:执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程

DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程

INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程

如果没有设置相关的值，则MySQL默认指定值为DEFINER。

COMMENT ‘string'

注释信息，可以用来描述存储过程。

知识点补充：DELIMITER(新的结束标记)

因为MySQL默认的语句结束符号为分号。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

比如:“DELIMITER //语句的作用是将MySQL的结束符设置为//，并以“END //"结束存储过程。存储过程定义完毕之后再使用"DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠('\') 字符，因为反斜线是MySQL的转义字符。

DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
			SELECT * from demo;
END $

DELIMITER ;

 使用存储过程：

CALL select_all_data();

带OUT参数

DELIMITER $

CREATE PROCEDURE show_min_salary(OUT ms DECIMAL(10,2))
BEGIN
			SELECT min(salary) INTO ms
			from demo;
END $

DELIMITER ;

使用存储过程： 

CALL show_min_salary(@ms);

SELECT @ms

 带IN参数

DELIMITER $

CREATE PROCEDURE show_someone_salary(IN empid int)
BEGIN
			SELECT salary from demo
			WHERE id=empid;
END $

DELIMITER ;

使用存储过程：

CALL show_someone_salary(2)

 带IN和OUT参数

DELIMITER //

create procedure show_someone_salary2(IN empid int , OUT empsalary DECIMAL(10,2))
begin
		select salary into empsalary
		from demo
		where id=empid;
end //

DELIMITER ;

使用存储过程：

set @empid=1;
call show_someone_salary2(@empid,@empsalary);

select @empsalary

带INOUT参数

DELIMITER //

create procedure show_salary(INOUT empid int)
BEGIN
			SELECT salary into empid
			from demo
			where id=empid;
END //

DELIMITER ;

 使用存储过程：

set @empid=2;

CALL show_salary(@empid);

SELECT @empid;

存储函数

---

语法

CREATE FUNCTION 函数名(参数名 参数类型)

RETURNS 返回值类型

[characteristics ...]  #表示创建存储过程时指定的对存储过程的约束条件

BEGIN

            函数体  #函数体中肯定有 RETURN 语句

END

 characteristics详情见存储过程

 例子：

CREATE FUNCTION  email_by_id()
RETURNS VARCHAR(20)
		DETERMINISTIC
		CONTAINS SQL
		READS SQL DATA
BEGIN
		RETURN(SELECT email FROM demo WHERE manager_id=1);
END

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS varchar(20)
        DETERMINISTIC
        CONTAINS SQL
        READS SQL DATA
BEGIN
        RETURN(SELECT email FROM demo WHERE manager_id=emp_id);
END

调用

SELECT 函数名(实参列表) 

SELECT email_by_id();
OR
SELECT email_by_id(1);

对比存储函数和存储过程

	关键字	调用语法	返回值	应用场景
存储过程	PORCEDURE	CALL  存储过程()	有0个或者多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

此外，存储函数可以放在查询语句中使用，存储过程不行。但存储过程的功能更加强大，包括能过执行对表的操作，这些是存储函数不具备的 

存储过程和函数的查看、修改、删除

---

查看

SHOW   CREATE   PROCEDURE  /  FUNCTION  存储过程名或函数名

SHOW   PROCEDURE  /   FUNCTION  STATUS  

SHOW   PROCEDURE  /   FUNCTION  STATUS  LIKE  模糊查询

SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME=' 函数名 / 存储过程名 '
AND ROUTINE_TYPE='FUNCTION / PROCEDRE'（这里必须为大写）

 修改

修改存储过程或函数，不影响存储函数或函数的功能，只是修改相关特性

ALTER  PROCEDURE /  FUNCTION  存储过程或函数名  [characteristics ...]

  此处characteristics 取值信息如下:

{ CONTAINS SQL   |   NO SQL   I   READS SQL DATA   I   MODIFIES SQL DATA}

| SQL SECURITY { DEFINER   |   INVOKER }
| COMMENT ‘string'

例子: 

ALTER FUNCTION email_by_id
SQL SECURITY INVOKER
COMMENT 'abababa'

删除 

DROP  PROCEDURE /  FUNCTION  存储过程或函数名