如何在数据库中使用存储过程 【数据库高效编程 - 学习笔记 第九章】

SQL 基本是一个命令实现一个处理，即所谓的：非程序型语言。
程序型语言，可以为了达成某一目的，将处理流程通过多个命令来编写。

非程序型语言，是不能编写流程的。

存储过程（Stored Procedure）：数据库中保存的一系列 SQL 命令的集合。
也可以看作相互间有关系的 SQL 命令组织在一起形成的一个小程序。

存储过程的优点：

1、提高执行性能。通过客户端执行 SQL 的时候，在数据库中，有解析到编译的前期准备过程。
但是，创建了存储过程，实际上，事先完成了解析、编译的处理，然后，保存在数据库中，因此，执行更快。

2、减轻网络负担。

3、防止对表的直接访问。（禁止对表的直接访问，只赋予相关存储过程的访问权限）

4、将数据库的处理黑匣子化。

调用存储过程时，可以指定参数。参数包括：输入参数、输出参数。通过关键字 IN、OUT、INOUT 来指定参数类型。

DELIMITER //
CREATE PROCEDURE sp_search_customer (IN p_name VARCHAR(20) )
BEGIN
    IF p_name IS NULL OR p_name = '' THEN
        SELECT * FROM customer;
    ELSE
        SELECT * FROM customer WHERE name LIKE p_name;
    END IF;
END

DELIMITER ;

SHOW PROCEDURE STATUS\G

SHOW CREATE PROCEDURE sp_search_customer\G

DROP PROCEDURE sp_search_customer;

CALL sp_search_customer('王%');

CALL sp_search_customer('');

DELIMITER //
CREATE PROCEDURE sp_search_customer2 (IN p_name VARCHAR(20), OUT p_cnt INT )
BEGIN
    IF p_name IS NULL OR p_name='' THEN
        SELECT * FROM customer;
    ELSE
        SELECT * FROM customer WHERE nam LIKE p_nam;
    END IF;
    SELECT FOUND_ROWS() INTO p_cnt;
END

DELIMITER ;

CALL sp_search_customer2('王%', @num);
SELECT @num;

DELIMITER //
CREATE PROCEDURE sp_search_employee(IN p_depart INT)
BEGIN
    IF p_depart = 1 THEN
        SELECT fname, lname, depart FROM employee WHERE depart = '研究部';
    ELSEIF p_depart = 2 THEN
        SELECT fname, lname, depart FROM employee WHERE depart = 'AC 部';
    ELSEIF p_depart = 3 THEN
        SELECT fname, lname, depart FROM employee WHERE depart = 'IT 部门';
    ELSEIF p_depart = 4 THEN
        SELECT fname, lname, depart FROM employee WHERE depart = '人事部';
    ELSE
        SELECT fname, lname, depart FROM employee WHERE depart = '经理部';
    END IF;
END


DELIMITER ;

CALL sp_search_employee(3);

DELIMITER //
CREATE PROCEDURE sp_search_employee2(IN p_depart INT)
BEGIN
    DECLARE tmp CHAR(4);
    CASE p_depart
        WHEN 1 THEN
            SET tmp = '研究部';
        WHEN 2 THEN
            SET tmp = 'AC 部';
        WHEN 3 THEN
            SET tmp = 'IT 部';
        WHEN 4 THEN
            SET tmp = '人事部';
        ELSE
            SET tmp = '经理部';
    END CASE;
    SELECT fname, lname, depart FROM employee WHERE depart = tmp;
END


DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_factorial(
    IN p_num INT,
    OUT p_result INT
)
BEGIN
    SET p_result = 1;
    WHILE p_num > 1 DO
        SET p_result = p_result * p_num;
        SET p_num = p_num - 1;
    END WHILE;
END

DELIMITER ;


CALL sp_factorial(5, @res);
SELECT @res;

DELIMITER //

CREATE PROCEDURE sp_factorial2(
    IN p_num INT,
    OUT p_result INT
)

BEGIN
    SET p_result = 1;
    
    REPEAT
        SET p_result = p_result * p_num;
        SET p_num = p_num - 1;
    UNTIL p_num <= 1 END REPEAT;
END

DELIMITER ;

CALL sp_factorial2(5, @res);
SELECT @res;