MySQL store过程 - 极客tutorial网

store过程overview

store过程 (Stored Procedure) is 一组 for 了completionspecificfunctions SQL语句collection, 经编译 after store in datalibraryin, userthrough指定store过程名字并给出parameter (such as果该store过程带 has parameter) 来执行它.

store过程优点

code复用: store过程可以被 many 次调用, reducingcode重复
improvingperformance: store过程 in creation时就被编译, 执行速度更 fast
reducingnetworktraffic: 只需传输store过程名 and parameter, 而不 is 整个SQL语句
增强security性: 可以授予user执行store过程 permission, 而不授予直接访问表 permission
逻辑集in: 业务逻辑集in in datalibrary端, 便于maintenance and management

store过程缺点

portability差: 不同datalibrarysystem store过程语法不同
debug difficult : 相比application程序code, store过程 debugtool较 few
version控制 difficult : store过程 versionmanagement不such asapplication程序code方便
占用datalibraryresource: store过程会占用datalibraryserver memory

store过程 creation

in MySQLin, usingCREATE PROCEDURE语句creationstore过程.

basic语法

CREATE PROCEDURE procedure_name ([parameters])
BEGIN
    -- SQL语句
END;

example: creation一个 simple store过程

DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT * FROM customers;
END //
DELIMITER ;

提示: in MySQL客户端in, 默认语句分隔符 is 分号 (;) , 但 is store过程体in可能package含 many 个分号. 因此, 我们需要usingDELIMITER语句临时更改分隔符, in store过程定义结束 after 再改回默认分隔符.

parameter传递

store过程可以接收parameter, MySQLsupport三种class型 parameter:

parameterclass型

IN: 输入parameter (默认) , 由调用者传递值给store过程
OUT: 输出parameter, store过程可以modify这个parameter 值并返回给调用者
INOUT: 输入输出parameter, 既可以传入值, 也可以return value

example: 带INparameter store过程

DELIMITER //
CREATE PROCEDURE GetCustomerByID(IN customer_id INT)
BEGIN
    SELECT * FROM customers WHERE id = customer_id;
END //
DELIMITER ;

example: 带OUTparameter store过程

DELIMITER //
CREATE PROCEDURE GetCustomerCount(OUT total_count INT)
BEGIN
    SELECT COUNT(*) INTO total_count FROM customers;
END //
DELIMITER ;

example: 带INOUTparameter store过程

DELIMITER //
CREATE PROCEDURE IncrementValue(INOUT value INT)
BEGIN
    SET value = value + 1;
END //
DELIMITER ;

variable声明 and using

in store过程in, 可以声明局部variable来storein间结果.

variable声明

DECLARE variable_name datatype [DEFAULT value];

example: using局部variable

DELIMITER //
CREATE PROCEDURE CalculateTotal()
BEGIN
    DECLARE total_amount DECIMAL(10,2);
    
    SELECT SUM(amount) INTO total_amount FROM orders;
    
    SELECT total_amount AS '总订单金额';
END //
DELIMITER ;

variable赋值

可以usingSET语句 or SELECT INTO语句 for variable赋值:

-- usingSET语句
SET variable_name = value;

-- usingSELECT INTO语句
SELECT column_name INTO variable_name FROM table_name WHERE condition;

控制语句

store过程support各种控制语句, 用于implementation complex 业务逻辑.

IF语句

DELIMITER //
CREATE PROCEDURE CheckCustomerStatus(IN customer_id INT)
BEGIN
    DECLARE order_count INT;
    
    SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = customer_id;
    
    IF order_count > 10 THEN
        SELECT 'VIP客户' AS status;
    ELSEIF order_count > 5 THEN
        SELECT '活跃客户' AS status;
    ELSE
        SELECT '普通客户' AS status;
    END IF;
END //
DELIMITER ;

CASE语句

DELIMITER //
CREATE PROCEDURE GetDiscountLevel(IN order_amount DECIMAL(10,2))
BEGIN
    DECLARE discount_level VARCHAR(20);
    
    CASE
        WHEN order_amount >= 1000 THEN
            SET discount_level = 'advanced折扣';
        WHEN order_amount >= 500 THEN
            SET discount_level = 'in级折扣';
        ELSE
            SET discount_level = '普通折扣';
    END CASE;
    
    SELECT discount_level AS '折扣级别';
END //
DELIMITER ;

LOOP语句

DELIMITER //
CREATE PROCEDURE CountToTen()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result VARCHAR(100) DEFAULT '';
    
    my_loop:
    LOOP
        SET result = CONCAT(result, counter, ', ');
        SET counter = counter + 1;
        
        IF counter > 10 THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
    
    SELECT TRIM(TRAILING ', ' FROM result) AS '计数结果';
END //
DELIMITER ;

WHILE语句

DELIMITER //
CREATE PROCEDURE CalculateFactorial(IN n INT, OUT factorial BIGINT)
BEGIN
    SET factorial = 1;
    
    WHILE n > 1 DO
        SET factorial = factorial * n;
        SET n = n - 1;
    END WHILE;
END //
DELIMITER ;

REPEAT语句

DELIMITER //
CREATE PROCEDURE GenerateSequence(IN start_num INT, IN end_num INT)
BEGIN
    DECLARE current_num INT DEFAULT start_num;
    DECLARE sequence VARCHAR(100) DEFAULT '';
    
    REPEAT
        SET sequence = CONCAT(sequence, current_num, ', ');
        SET current_num = current_num + 1;
    UNTIL current_num > end_num
    END REPEAT;
    
    SELECT TRIM(TRAILING ', ' FROM sequence) AS '序列';
END //
DELIMITER ;

exceptionprocessing

MySQLproviding了exceptionprocessingmechanism, 可以捕获 and processingstore过程in error.

DECLARE HANDLER语句

DECLARE handler_type HANDLER FOR condition_value [, condition_value] ... statement

example: exceptionprocessing

DELIMITER //
CREATE PROCEDURE InsertCustomer(IN customer_name VARCHAR(50), IN email VARCHAR(100))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '插入失败: 发生error' AS message;
    END;
    
    START TRANSACTION;
    INSERT INTO customers(name, email) VALUES(customer_name, email);
    COMMIT;
    SELECT '插入成功' AS message;
END //
DELIMITER ;

common 条件值

SQLSTATE [VALUE] sqlstate_value: specific SQLstatus码
MySQL error code: specific MySQLerror码
NOT FOUND: 没 has 找 to data
SQLEXCEPTION: 任何SQLexception
SQLWARNING: 任何SQLwarning

store过程 management

查看store过程

-- 查看所 has store过程
SHOW PROCEDURE STATUS;

-- 查看specificstore过程 定义
SHOW CREATE PROCEDURE procedure_name;

modifystore过程

MySQL不support直接modifystore过程, 需要先delete再重 new creation:

-- deletestore过程
DROP PROCEDURE IF EXISTS procedure_name;

-- 重 new creationstore过程
CREATE PROCEDURE procedure_name()
BEGIN
    --  new  store过程code
END;

执行store过程

-- 执行无parameter store过程
CALL procedure_name();

-- 执行带parameter store过程
CALL procedure_name(parameter1, parameter2, ...);

-- 执行带OUTparameter store过程
CALL procedure_name(@output_parameter);
SELECT @output_parameter;

store过程best practices

命名规范

using has 意义名称, such asGetCustomerByID而不 is proc1
可以using before 缀来区分store过程, such assp_ or usp_
parameter名应该清晰表达其用途, such as@customer_id

performanceoptimization

避免 in store过程inusing big 量临时表
合理usingindex, 确保queryefficiency
避免 in 循环in执行昂贵 operation
考虑usingNO SQL, READS SQL DATAetc.features声明

security性

避免 in store过程inusing动态SQL, such as必须using, 确保parameter化query
最 small 化store过程 permission
for 输入parameterforverification, 防止SQL注入

可maintenance性

添加comment, 说明store过程用途, parameter and return value
将 complex store过程拆分 for many 个较 small store过程
usingversion控制managementstore过程code

实践case

case: 订单processingstore过程

creation一个store过程, 用于processing new 订单, including插入订单记录, updatelibrary存 and 计算总价.

DELIMITER //
CREATE PROCEDURE ProcessOrder(
    IN customer_id INT,
    IN product_id INT,
    IN quantity INT,
    OUT order_id INT,
    OUT total_amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '订单processing失败: 发生error' AS message;
    END;
    
    DECLARE product_price DECIMAL(10,2);
    
    START TRANSACTION;
    
    -- 获取产品价格
    SELECT price INTO product_price FROM products WHERE id = product_id;
    
    -- 计算总价
    SET total_amount = product_price * quantity;
    
    -- 插入订单记录
    INSERT INTO orders(customer_id, order_date, total_amount)
    VALUES(customer_id, NOW(), total_amount);
    
    -- 获取订单ID
    SET order_id = LAST_INSERT_ID();
    
    -- 插入订单详情
    INSERT INTO order_details(order_id, product_id, quantity, unit_price)
    VALUES(order_id, product_id, quantity, product_price);
    
    -- updatelibrary存
    UPDATE products SET stock = stock - quantity WHERE id = product_id;
    
    COMMIT;
    SELECT '订单processing成功' AS message;
END //
DELIMITER ;

互动练习

练习1: creationstore过程

creation一个store过程GetProductByCategory, 根据classificationID获取产品list, 并计算每个classification 产品数量.

DELIMITER //
CREATE PROCEDURE GetProductByCategory(IN category_id INT)
BEGIN
    DECLARE product_count INT;
    
    SELECT COUNT(*) INTO product_count FROM products WHERE category_id = category_id;
    
    SELECT * FROM products WHERE category_id = category_id;
    SELECT product_count AS '产品数量';
END //
DELIMITER ;

练习2: 带exceptionprocessing store过程

creation一个store过程TransferFunds, 用于 in 两个account之间转账, package含exceptionprocessing.

DELIMITER //
CREATE PROCEDURE TransferFunds(
    IN from_account_id INT,
    IN to_account_id INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '转账失败: 发生error' AS message;
    END;
    
    DECLARE from_balance DECIMAL(10,2);
    
    START TRANSACTION;
    
    -- check余额
    SELECT balance INTO from_balance FROM accounts WHERE id = from_account_id;
    
    IF from_balance < amount THEN
        ROLLBACK;
        SELECT '转账失败: 余额不足' AS message;
        LEAVE;
    END IF;
    
    -- 扣款
    UPDATE accounts SET balance = balance - amount WHERE id = from_account_id;
    
    -- 存款
    UPDATE accounts SET balance = balance + amount WHERE id = to_account_id;
    
    COMMIT;
    SELECT '转账成功' AS message;
END //
DELIMITER ;