1. 存储过程

1.1 语法：

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

说明:

1. 参数前面符号的意思：
   • IN：有入参无返回值
   • OUT：无入参有返回值
   • INOUT：有入参有返回值
2. 形参可以是MySQL中的任何类型
3. 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: 表示存储过程是否是确定的，默认值是NOT DETERMINISTIC
   • CONTAINS SQL: 表示存储过程包含SQL语句，默认值是CONTAINS SQL
   • NO SQL: 表示存储过程不包含SQL语句
   • READS SQL DATA: 表示存储过程只读数据库，不修改数据库
   • MODIFIES SQL DATA: 表示存储过程修改数据库
   • SQL SECURITY DEFINER: 表示存储过程由定义者执行，默认值是INVOKE
   • COMMENT 'string': 表示存储过程注释

1.2 示例：

1. 无参数，无返回值
   • 定义一个存储过程，用于查询所有员工信息：

     DELIMITER $
     CREATE PROCEDURE select_all_data()
     BEGIN
     select * from emps;
     END $
     DELIMITER ;
     

   • 调用存储过程：

     CALL select_all_data();
     

2. 无参数，有返回值
   • 定义一个存储过程，查询emps表中的最低薪资，并将其通过OUT参数ms输出：

     DELIMITER $
     CREATE PROCEDURE select_min_salary(OUT ms DOUBLE)
     BEGIN
     select min(salary) into ms from emps;
     END $
     DELIMITER ;
     

   • 调用存储过程：

     CALL select_min_salary(@ms);
     SELECT @ms;
     

3. 有参数，无返回值
   • 创建一个存储过程，用于根据员工姓名查询员工薪资：

     DELIMITER $
     CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
     BEGIN
     select salary from emps where name=empname;
     END $
     DELIMITER ;
     

   • 调用存储过程：

     CALL show_someone_salary('张三');
     

4. 有参数，有返回值
   • 创建一个存储过程，用于根据部门编号查询部门员工平均薪资：

     DELIMITER $
     CREATE PROCEDURE show_dept_avg_salary(IN deptno INT, OUT avg_salary DOUBLE)
     BEGIN
     select avg(salary) into avg_salary from emps where deptno=deptno;
     END $
     DELIMITER ;
     

   • 调用存储过程：

     CALL show_dept_avg_salary(10, @avg_salary);
     SELECT @avg_salary;
     

2. 储存函数

2.1 语法：

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

说明：

1. 参数列表: FUNCTION中总是默认为IN参数。
2. RETURNS type表示函数返回的类型, RETURN 返回值 表示函数的返回值，函数体中必须包含此语句，返回值类型必须与RETURNS type一致。
3. [characteristics ...]与存储过程相同，不再赘述。
4. 函数体可以用BEGIN...END来表示函数体的开始和结束，如果只有一条语句，可以省略。

2.2 示例：

• 定义：

DELIMITER $
DROP FUNCTION IF EXISTS email_by_name;
CREATE FUNCTION email_by_name(name VARCHAR(25)) RETURNS VARCHAR(25)
    DETERMINISTIC
    CONTAINS SQL
    READS SQL DATA
BEGIN
    SET @email := (SELECT email FROM employees WHERE last_name = name);
    RETURN @email;
END $;
DELIMITER ;

• 调用：

SELECT email_by_name('Abel');

3. 存储过程和函数的比较

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

• 查看: SHOW CREATE PROCEDURE|FUNCTION 名称;
• 修改：ALTER PROCEDURE|FUNCTION 名称 AS 新的SQL;
• 删除：DROP PROCEDURE|FUNCTION 名称;