1.6（SQL学习笔记）存储过程

一、什么事存储过程

　　可以将存储过程看做是一组完成某个特定功能的SQL语句的集合。

　　例如有一个转账功能（A向B转账50），先将账户A中金额扣除50，然后将账户B中金额添加50.

　　那么我们可以定义一个名为转账的存储过程，将这些SQL语句组合在一次。

　　使用时直接调用这个存储过程就可以了，这样将操作封装起来，提高了可用性。　　

　　后续也便于管理。

　　可以将存储过程看做编程语言中的方法，只要把方法定义好了后，以后直接调用即可。

 

二、创建存储过程

　　

DELIMITER //
CREATE PROCEDURE 存储过程名称()
BEGIN
    语句1;
    语句2;
END //
DELIMITER ;

由于‘；’会被作为分隔符，存储过程中的‘；’会被解释为分隔符，而不是作为存储过程的一部分，会出现语法错误。

所以先采用 DELIMITER设置分隔符为//，这样存储过程中语句的；就不会解释为分隔符，而被完整的保留了下来。

等存储过程创建完毕，再将分隔符还原为‘；’。

 

删除存储过程：
DROP PROCEDURE 存储过程名; -- 不需要加()

查看存储过程：
SHOW PROCEDURE STATUS;

 

三、调用存储过程

　　调用存储过程很简单：

　　CALL 存储过程名();如果存储过程设置了参数，添加对应参数即可。

　　

　　结合上面的创建存储过程和调用存储过程，我们来看一个小例子。、

　　

DELIMITER //  -- 设置分隔符为//
CREATE PROCEDURE selectAll() -- 创建存储过程
BEGIN
    SELECT * FROM products;  -- 执行查询语句
END //
DELIMITER ; -- 还原分割符号

CALL selectAll();  -- 调用存储过程

 

四、创建带参存储过程

　　存储过程名后面的（）内可以添加参数。

　　参数声明格式：

　　参数性质 参数名 参数类型

　　

　　参数性质：可以是IN,OUT,INOUT中一种，

　　IN代表输入参数，传递给存储过程，存储过程中对其的修改不保留。只作为传入参数。类似函数的值传递。

　　OUT：该值可以被存储过程改变，改变会被保留下来。可以返回被修改后的值，类似函数的引用传递。

　　INOUT：可以作为参入参数，也可作为传出参数。

 

　　

DELIMITER //   -- 创建带参数的存储过程
CREATE PROCEDURE productpricing(
    OUT p_min DECIMAL(8,2), -- DECIMAL(P,D) P代表数字长度，D代表小数位个数。长8位有两位是小数位
    OUT p_max DECIMAL(8,2),  -- 指定参数 
    OUT P_avg DECIMAL(8,2)
)
BEGIN 
    SELECT MIN(prod_price) INTO p_min FROM products; -- 将表中最小的prod_price设置给p_min
    SELECT MAX(prod_price) INTO p_max FROM products;  -- 将查询值与参数绑定
    SELECT AVG(prod_price) INTO P_avg FROM products;
END //

DELIMITER ;

CALL productpricing(@p_min,@p_max,@p_avg); -- 调用存储过程
SELECT@p_min,@p_max,@p_avg;      -- 显示返回参数

 

SELECT value1.1 ,value2.1 INTO value1.2, value2.2;

可以将value1.1  value2.1的值分别赋给value1.2  value2.2

 

@valueName 代表声明一个用户变量，使用时直接采用@valueName即可，无需事先声明。

 

 

　　使用IN、OUT参数创建并调用存储过程，计算订单合计。　

DELIMITER //
CREATE PROCEDURE calTotal(
    IN id INT,    
    OUT total FLOAT  
)
    BEGIN
        SELECT SUM(quantity * item_price) 
        FROM orderitems
        WHERE order_num = id
        INTO total;   -- 将SUM()求和后的值赋给total
    END //

DELIMITER ;

CALL calTotal(20005,@total); -- 调用存储过程
SELECT @total;

 

 

 

 五、创建智能处理

　　在存储过程中可以添加，选择、循环等操作，使功能更强大。

DELIMITER //
CREATE PROCEDURE ordertotal(
        IN ordernumber DECIMAL(8,2),-- 订单编号
        IN tax BOOLEAN ,            -- 该商品是否计算税金，1为true计算，0位false不计算
        OUT total DECIMAL(8,2)      -- 最后价格
    )
BEGIN
    DECLARE total_tmp DECIMAL(8,2);  -- 含税价格
    DECLARE tax_t INT DEFAULT 6;     -- 定义默认税率，6%
    SELECT SUM(quantity*item_price) 
    FROM orderitems
    WHERE order_num = ordernumber
    INTO total_tmp;                  -- 首先计算商品不含税总价
    IF tax THEN    -- 如果tax为1（true) 计算含税总价
        SELECT total_tmp * (1 + (tax_t / 100)) INTO total_tmp; -- 
    END IF;
    SELECT total_tmp INTO total; -- 将最后价格赋给total
END//
DELIMITER ;
CALL ordertotal(20005,1, @total);  -- 计算20005号订单价格，含税， 1为true,0为false
SELECT @total; -- 显示计算后总价

 

DECLARE用于在BEGING/END中定义局部变量，只在当前BEGIN/END块中有效。

 

参考资料： 

《MySQL必知必会》

MySQL中变量的定义和变量的赋值使用