MySQL8.0学习笔记（8）—— stored procedures，function

stored procedures

创建stored procedures

存储过程（Stored Procedure）是：大型数据库系统中，一组为了完成特定功能的 SQL 语句集，这些SQL语句集存储在数据库中，经过第一次编译后，后续调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程是数据库的一个重要对象。

• 创建一个存储过程

use sql_invoicing;
//DELIMITER将标准分隔符分号";"更改为$$,保证存储过程内容的整体性
DELIMITER $$
//创建一个新的存储过程,存储过程的名字为get_clients()
CREATE PROCEDURE get_clients()	
//BEGIN和END之间的部分称为存储过程的主体
BEGINSELECT * FROM clients;
END$$
DELIMITER ;

• 调用一个存储过程

call sql_invoicing.get_clients();

使用MySqlWorkbeach创建stored procedures

自己写时，每次都要修改限定符有点麻烦，可以按照如下步骤

1. 每个database中有 Stored Procedures，右击选择CREATE Stored Procedures
   
2. 只需要关注自己需要填写的内容
   
3. 点击Apply按钮
   
   顺便说下删除stored procedures

DROP PROCEDURE IF EXISTS get_invoices;

4. 在点击右下角Apply

Parameters

对于stored procedures构成的参数可以进行传参

USE `sql_invoicing`;
DROP procedure IF EXISTS `git_clients`;DELIMITER $$
USE `sql_invoicing`$$
//state 参数名；char(2)参数类型
CREATE PROCEDURE `git_clients` (state char(2))
BEGIN
select * from  client c
where c.state = state;
END$$DELIMITER ;

• 调用
  

Default Parameters

• 可以指定参数的默认值来执行语句,假如state为NULL,返回全部，否则返回条件满足的数据

USE `sql_invoicing`;
DROP procedure IF EXISTS `git_clients`;DELIMITER $$
USE `sql_invoicing`$$
CREATE PROCEDURE `git_clients` (state char(2))
BEGINIF state IS NULL THENselect * FROM clients;ELSEselect * from  client cwhere c.state = state;END IF;
END$$DELIMITER ;//第二种写法
BEGINselect * from  client cwhere c.state = IFNULL(state,c.state);
END$$

Parameters Validation

对于参数有时候回去限制其的范围

• 能够对invoices表中的payment_amount 与payment_date进行修改且payment_amount 不为负数

USE `sql_invoicing`;
DROP procedure IF EXISTS `make_payment`;DELIMITER $$
USE `sql_invoicing`$$
CREATE PROCEDURE `make_payment` (invoice_id INT,
//DECIMAL(9,2)表示最大占9位的小数，2位小数点后面保留2位
payment_amount DECIMAL(9,2),payment_date DATE)
BEGINif payment_amount <= 0 THEN//22003错误码表示超过参数范围signal sqlstate '22003' //输出错误的打印信息set message_text = 'Invalid payment amount';end if;update invoices iset i.payment_total = payment_amount,i.payment_date = payment_datewhere i.invoice_id = invoice_id;
END$$DELIMITER ;

Out Parameters

对于传入参数，可以使用传出参数来获取结果需要关键词OUT

• 没有付的订单的客户

USE `sql_invoicing`;
DROP procedure IF EXISTS `get_unpaid_invoices_for_client`;DELIMITER $$
USE `sql_invoicing`$$
CREATE PROCEDURE `get_unpaid_invoices_for_client` (client_id INT,OUT invoices_count int,	//输出关键词outOUT invoices_total DECIMAL(9,2)
)
BEGINselect count(*), sum(invoice_total)into invoices_count,invoices_total	//将上面两个值传入输出值from invoices iwhere i.client_id = client_idand payment_total = 0;
END$$DELIMITER ;

执行结果

variables

• 全局变量，声明周期在整个连接的过程

set @invoices_count = 0;

• 局部变量，生命周期只在于函数周期内

USE `sql_invoicing`;
DROP procedure IF EXISTS `get_rist_factor`;DELIMITER $$
USE `sql_invoicing`$$
CREATE PROCEDURE `get_rist_factor` ()
BEGIN//声明local变量declare risk_factor decimal(9,2) default 0;declare invoices_total decimal(9,2);declare invoices_cout int;select count(*),sum(invoice_total)into invoices_cout,invoices_totalfrom invoices;set risk_factor = invoices_total / invoices_cout * 5;select risk_factor;
END$$DELIMITER ;

执行

function

function与stored procedures类似，最大的不同是function只能返回单个值，不像stored procedures返回复数值或者行，列。

USE `sql_invoicing`;
DROP function IF EXISTS `get_risk_factor_for_client`;DELIMITER $$
USE `sql_invoicing`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_risk_factor_for_client`(client_id int) RETURNS int
-- 每个函数至少有一个attribute-- deterministic 对于每次的输入值都有固定的值相对应，READS SQL DATA -- 表示是一个select语句用于查询-- modifies sql data 表示是一个修改函数例如inert into,update等
BEGINdeclare risk_factor decimal(9,2) default 0;declare invoices_total decimal(9,2);declare invoices_cout int;select count(*),sum(invoice_total)into invoices_cout,invoices_totalfrom invoices iwhere i.client_id = client_id;set risk_factor = invoices_total / invoices_cout * 5;RETURN IFNULL(risk_factor,0);
END$$DELIMITER ;

使用函数

select client_id,name,get_risk_factor_for_client(client_id) as risk_factor
from clients