mysql详解8：存储过程和函数

存储和管理sql代码
存储过程中的sql代码执行更快
更能加强数据安全性

创建存储过程
DELIMITER $$
CREATE PROCEDURE get_clients () BEGIN
SELECT
* FROM promote_advertisers;

END$$
DELIMITER;
DELIMITER $$表示修改默认分隔符
CALL get_clients() 使用存储过程

删除存储过程
DROP PROCEDURE IF EXISTS get_clients;

在存储过程中添加参数
DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
state CHAR(2)
)

BEGIN
SELECT * FROM clients c where c.state=state;
END$$
DELIMITER;

CALL get_client_by_state('CA')

用使用过程更新 删除数据
CREATE PROCEDURE make_payment
(
invoice_id INT,
payment_amount DECIMAL(9,2),
payment_date DATE
)
BEGIN
IF payment_amout<=0 THEN
SIGUNAL SQLSTATE '22003'
set MESSAGE_TEXT ="Invaild payment amount";
END IF;
update invoices i
set
i.payment_total =payment_amount,
i.payment_date = payment_date
where i.invoice_id =invoice_id;
END
通过参数验证以防传入错误参数

输出参数
CREATE PROCEDURE get_unpaid_invoice_for_client
(
client_id INT,
OUT invoices_count INT,
OUT invoice_total DECIMAL(9,2)
)
BEGIN
select count(*),SUM(invoice_total)
from invoicies i
where i.client_id =client_id
and payment_total =0;
END

SELECT @invoice_count,@invoices_total;

变量
set @invoice_count = 0
DECLARE 声明变量

CREATE PROCEDURE get_risk_factor()
BEGIN
DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
DECLARE invoice_total DECIMAL(9,2);
DECLARE invoice_count INT; ’

select count(*),sum(invoice_total)
into invoices_count,invoices_total
from invoices;
set risk_factor =invoice_total/invoice_count *5;
END

call sql_invoicing.get_risk_factor();

函数
函数只能返回单一值
CREATE FUNCTION get_risk_factor_for_client (
client_id INT
)
RETURNS INTEGER
BEGIN
DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
DECLARE invoice_total DECIMAL(9,2);
DECLARE invoice_count INT; ’
select count(*),sum(invoice_total)
into invoices_count,invoices_total
from invoices;
set risk_factor =invoice_total/invoice_count *5;
return IFNULL(risk_factor);
END
使用函数
select
client_id,
name,
get_risk_factor_for_client(client_id) as risk_factor;
删除函数
DROP FUNCTION IF EXISTS get_risk_factor_for_client;

其他约定
函数命名 下划线或者驼峰式