存储过程与自定义函数

1.分类

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

没有参数(无参数无返回)
仅仅带 IN 类型(有参数无返回)
仅仅带 OUT 类型(无参数有返回)
既带 IN 又带 OUT(有参数有返回)
带 INOUT(有参数有返回)

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

2.创建存储过程

-- 语法
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) 
[characteristics ...]
BEGIN
    存储过程体
END

参数前面的符号的意思
- IN: 当前参数为输入参数，也就是表示入参;
  存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
- OUT：当前参数为输出参数，也就是表示出参;
  执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
- INOUT：当前参数既可以为输入参数，也可以为输出参数。
形参类型可以是 MySQL数据库中的任意类型。
characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

LANGUAGE SQL :说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
[NOT] DETERMINISTIC :指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } :指明子程序使用SQL语句的限制。
- CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句;
- NO SQL表示当前存储过程的子程序中不包含任何SQL语句;
- READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句;
- MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
- 默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER } :执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
- DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程;
- INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END
编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

1. BEGIN...END:BEGIN...END 中间包含了多个语句，每个语句都以(;)号为结束符。
2. DECLARE:DECLARE 用来声明变量，使用的位置在于 BEGIN...END 语句中间，而且需要在其他语句使用之前进 行变量的声明。
3. SET:赋值语句，用于对变量进行赋值。
4. SELECT... INTO:把从数据表中查询的结果存放到变量中，也就是为变量赋值。

需要设置新的结束标记DELIMITER 新的结束标记

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

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

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

DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) [characteristics ...]
BEGIN
sql语句1; sql语句2;
END $

3.代码举例

创建存储过程select_all_data()，查看 emps 表的所有数据

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
    SELECT * FROM emps;
END $
DELIMITER ;

创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值。

CREATE PROCEDURE show_max_salary()
    LANGUAGE SQL
NOT DETERMINISTIC CONTAINS SQL
SQL SECURITY DEFINER COMMENT '查看最高薪资' BEGIN
        SELECT MAX(salary) FROM emps;
    END //
DELIMITER ;

创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms” 输出

DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
    BEGIN
        SELECT MIN(salary) INTO ms FROM emps;
    END //
DELIMITER ;

创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname 输入员工姓名

DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
    BEGIN
        SELECT salary FROM emps WHERE ename = empname;
    END //
DELIMITER ;

创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname 输入员工姓名，用OUT参数empsalary输出员工薪资。

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
    BEGIN
        SELECT salary INTO empsalary FROM emps WHERE ename = empname;
    END //
DELIMITER ;

创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。

DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
    BEGIN
        SELECT ename INTO empname FROM emps
        WHERE eid = (SELECT MID FROM emps WHERE ename=empname);
    END //
DELIMITER ;

4.调用存储过程

4.1 调用格式

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。

调用in模式的参数: CALL sp1('值');
调用out模式的参数:

SET @name;
CALL sp1(@name);
SELECT @name;

调用inout模式的参数:

SET @name=值; 
CALL sp1(@name); 
SELECT @name;

5.创建自定义函数

5.1 语法

-- 语法格式
CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型
[characteristics ...]
BEGIN
    函数体 #函数体中肯定有 RETURN 语句 
END

参数列表:指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。
RETURNS type 语句表示函数返回数据的类型; RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。
characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
函数体也可以用BEGIN...END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN...END。

5.2 调用函数

DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
    RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;

调用

SET @emp_id = 102;
SELECT email_by_id(102);

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

查看

使用SHOW CREATE语句查看存储过程和函数的创建信息

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

使用SHOW STATUS语句查看存储过程和函数的状态信息

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

从information_schema.Routines表中查看存储过程和函数的信息

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id' AND ROUTINE_TYPE = 'FUNCTION'

修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL ，表示子程序中不包含SQL语句。
READS SQL DATA ，表示子程序中包含读数据的语句。
MODIFIES SQL DATA ，表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。
- DEFINER, 表示只有定义者自己才能够执行。
- INVOKER, 表示调用者可以执行。
COMMENT 'string' 表示注释信息。

修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行

ALTER PROCEDURE CountProc 
MODIFIES SQL DATA
SQL SECURITY INVOKER ;

-- 查询修改后的信息:
SELECT specific_name,sql_data_access,security_type
FROM information_schema.`ROUTINES`
WHERE routine_name = 'CountProc' AND routine_type = 'PROCEDURE';

修改存储函数CountProc的定义。将读写权限改为READS SQL DATA，并加上注释信息“FIND NAME”

ALTER FUNCTION CountProc 
READS SQL DATA
COMMENT 'FIND NAME' ;

删除

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;