SQL存储过程

存储过程是一个包含一堆SQL代码的数据库对象。在应用代码里，调用这些过程来获取或保存数据。

创建一个存储过程

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;
END$$

DELIMITER ;

使用存储过程

CALL get_clients()

也可以在工作台建立存储过程

删除存储过程

DROP PROCEDURE IF EXISTS get_clients

不会因为没有储存过程而报错。

在存储过程中添加参数

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2) --VA
)
BEGIN
    SELECT * FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;

使用存储过程

CALL get_clients_by_state('CA')

 如果不在括号写东西的话就会报错。

带有默认值的参数

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2) --VA
)
BEGIN
    IF state IS NULL THEN 
        SET state='CA';
    END IF;

    SELECT * FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;

以及如何在不输入时返回所有的值：
 

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2) --VA
)
BEGIN


    SELECT * FROM clients c
    WHERE c.state = IFNULL(state,c.state);
END$$

DELIMITER ;

 参数验证

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    invoice_id INT
    payment_date DATE
)
BEGIN
    IF payment_amout <=0 THEN
        SIGNAL SQLSTATE '22003' 
            SET MESSAGE_TEXT = 'Invalid payment amount';
    END IF ;
    UPDATE invoice i
    SET 
        i.payment_date=payment_date
    WHERE i.invoice_id = invoice_id;


END$$

DELIMITER ;

但是如果验证太多的话会影响存储。

输出参数

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client
(
    client_id INT,
    OUT invoices_count TINYINT
    OUT invoice_total INT
    
)
BEGIN
    SELECT COUNT(*),SUM(invoice_total)
    INTO invoice_count, invoice_total
    FROM invoice i
    WHERE i.client_id =client_id
        AND payment_total =0;


END$$

DELIMITER ;

 变量

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client
(

    
)
BEGIN
    DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9,2) 
    
    SELECT COUNT(*),SUM(invoice_total)
    INTO invoice_count, invoice_total
    FROM invoice i

    SET risk_factor = invoice_total

    SELECT risk_factor

END$$

DELIMITER ;

 函数

只能返回单一值

CREATE FUNCTION get_risk
(
    client_id INT ,
)
RETURNS INTEGER

READS SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9,2) 
    
    SELECT COUNT(*),SUM(invoice_total)
    INTO invoice_count, invoice_total
    FROM invoice i
    WHERE i.client_id = client_id

    SET risk_factor = invoice_total

    SELECT risk_factor

    RETURN risk_factor;
END

应用案例如下：

SELECT
    client_id,
    name,
    get_risk(client_id)
FROM clients

 利用drop function删除函数