1. 了解Mysql中的存储过程

什么是存储过程？

  • 它是事先在数据库中存储的编译过的SQL语句的集合，解决了SQL层面上代码的封装与复用问题。

存储过程和函数的区别？

  • 返回值要求不同
    • 函数：必须要有返回值
    • 存储过程：没有返回值。
  • 参数的类型不同
    • 函数：只能是 IN
    • 存储过程：IN、OUT、INOUT

存储过程的优点

  • 调用简单：封装隐藏了复杂的业务逻辑
  • 效率更高：因为SQL被预编译过
  • 安全性高：可以直接给开发者存储过程的执行权限，而不必给基础表的权限，一定程度上更加安全

存储过程的缺点

  • CPU使用率增加：因为数据库服务器的设计不当于逻辑运算。
  • 难以调适：MySQL不提供调试存储过程的功能。
  • 开发维护移植问题：项目进展变得不愉快

使用注意（存储过程和函数）

  • 存储函数（function），存储过程（procedure）都称之为存储例程（routine）
  • 创建存储过程需要 CREATE ROUTINE 的权限
  • 修改和删除存储过程需要 ALTER ROUTINE 的权限
  • 执行存储过程需要 EXECUTE 的权限
  • 储存过程中不能使用 LOAD DATA INFILE 这样的语句
  • 支持事务
  • 存储函数可以直接在SELECT语句中调用

2. 存储过程一基本语法

创建语法

  1. CREATE
  2.     [DEFINER = { user | CURRENT_USER }]
  3. 　PROCEDURE sp_name ([proc_parameter[,...]])
  4.     [characteristic ...] routine_body
  6. proc_parameter:
  7.     [ IN | OUT | INOUT ] param_name type
  9. characteristic:
  10.     COMMENT 'string'
  11.   | LANGUAGE SQL
  12.   | [NOT] DETERMINISTIC
  13.   | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  14.   | SQL SECURITY { DEFINER | INVOKER }
  16. routine_body:
  17. 　　Valid SQL routine statement
  19. [begin_label:] BEGIN
  20. 　　[statement_list]
  21. 　　　　……
  22. END [end_label]

创建

create procedure 存储过程名(参数)
create function 存储函数名(参数)

删除

DROP PROCEDURE 存储过程名

修改

ALTER PROCEDURE

调用

call 存储过程名称(参数)

参数

  • IN
  • OUT
  • INOUT

3. 使用例子

1. demo1

-- 定义一个存储过程删除一条记录
delimiter$$

CREATE PROCEDURE deleteOneStudent(IN s_id int)
BEGIN 
    DELETE FROM student
    where id = s_id;
END$$

delimiter;

-- 调用
call deleteOneStudent(907);

2. demo2

/* 创建之前，如果存在相同的存储过程就删除 */
DROP PROCEDURE IF EXISTS create_employee;

/* 修改分隔符为 //  */
DELIMITER //

CREATE PROCEDURE create_employee (
    OUT new_emp_no INT,
    IN first_name VARCHAR(20),
    IN last_name VARCHAR(20),
    IN gender enum('M','F'),
    IN birth_date date,
    IN emp_dept_name VARCHAR(40),
    IN title VARCHAR(50)
)
BEGIN
    /* 为emp_dept_no 和salary 声明变量 */
    DECLARE emp_dept_no char(4);
    DECLARE salary int DEFAULT 60000;

    /* 查询employees表的最大 emp_no,将这个变量值给 new_emp_no */
    SELECT MAX(emp_no) INTO new_emp_no FROM employees;
    /* 增加 new_emp_no */
    SET new_emp_no = new_emp_no + 1;

    /* 数据插入到 employees表中 */
    INSERT INTO employees VALUES (new_emp_no, birth_date, first_name, 
                                last_name, gender, CURDATE());

    /* 找到dept_name 对应的 dept_no */
    SELECT emp_dept_name;
    SELECT dept_no INTO emp_dept_no FROM departments WHERE dept_name = emp_dept_name;
    SELECT emp_dept_no;

    /* 数据插入到 dept_emp表中 */
    INSERT INTO titles VALUES(new_emp_no, title, CURDATE(), '9999-01-01');

    /* 以title为条件查询的薪水 */
    IF title = 'Staff'
        THEN SET salary = 100000;
    ELSEIF title = 'Senior Staff'
        THEN SET salary = '1200000';
    END IF;

    /* 数据插入到salaries表中 */
    INSERT INTO salaries VALUES(new_emp_no, salary, CURDATE(), '9999=01-01');
END //

/* 改回分隔符 */ 
DELIMITER ;

/* 调用过程 */
CALL create_employee (
    @new_emp_no,
    'John',
    'Smith',
    'M',
    '1984-06-19',
    'Research',
    'Staff'
);