mysql day8 第九章 存储过程

-- 第九章 存储过程
-- 存储过程中的每条语句都要用分号终止，改动默认分隔符
-- delimiter $$
-- create procedure get_clients()
-- begin 
-- select * from clients;
-- end$$
-- delimiter ;

-- call get_clients() -- 返回数据库中的所有客户

delimiter $$
create procedure get_invoice_with_balance()
begin
		select *
		-- from invoices
		-- where invoice_total - payment_total > 0
		from invoice_with_balance
		where balance > 0;
end$$
delimiter ;
-- drop procedure get_clients_by_state
-- delimiter $$
-- create procedure get_clients_by_state
-- (
-- state char(2)
-- )
-- begin
-- select * from clients c
-- where c.state = ifnull(state,c.state); -- 参数state为空，返回c.state
-- end $$

-- write a stored procedure called get_payments with 2 parameters,client_id -int 4,payment method id -tinyint 1 0-255，可以为空
-- delimiter $$
-- create procedure get_payments
-- (
-- 	client_id int, -- 形参
-- 	payment_method_id tinyint -- 实参
-- )
-- begin
-- 	select *
-- 	from payments p
-- 	where 
-- 		p.client_id = ifnull(client_id, p.client_id) 
--         and
-- 		p.payment_method = ifnull(payment_method_id, p.payment_method)
-- end $$
-- delimiter ;
-- call new_procedure(1,null)

-- 参数验证
-- 创建一个进程
-- CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`(
-- 	invoice_id int,
-- 	payment_amount decimal(9,2),
-- 	payment_date date
-- )
-- BEGIN
-- 		if payment_amount <= 0 then
-- 		signal sqlstate '22003'
-- 		set message_text = 'invalid payment amount'; -- 标识错误,22003处理超位值
--         end if;
-- update invoices i
-- set
-- 	i.payment_total = payment_amount,
-- 	i.payment_date = payment_date
-- 	where i.invoice_id = invoice_id;
-- END
-- call sql_invoicing.make_payment(2, 100, '2019-01-01'); -- 点执行输入参数会自动生成，在invoices中更新为100
-- call sql_invoicing.make_payment(2, -100, '2019-01-01'); -- 提示error

-- 输出参数
-- CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid_invoices_for_client`(
-- client_id int,
-- out invoices_count int,
-- out invoices_total decimal(9,2) -- 输出参数
-- )
-- BEGIN
-- 	select count(*),sum(invoice_total)
--     into invoices_count, invoices_total -- 读取count和sum的数据复制到这些输出参数
-- 	from invoice i
-- 	where i.client_id = client_id and payment_total = 0;
-- END
-- 调用call sql_invoicing.get_unpaid_invoices_for_client(3);

-- set @invoices_count = 0;
-- set @invoices_total = 0;
-- call sql_invoicing.get_unpaid_invoices_for_client(3, @invoices_count, @invoices_total);
-- select @invoices_count, @invoices_total;

-- CREATE DEFINER=`root`@`localhost` PROCEDURE `get_risk_factor`()
-- BEGIN
-- declare risk_factor decimal(9,2) default 0; -- 定义形参,default给默认值
-- declare invoices_total decimal(9,2); -- 定义形参
-- declare invoices_count int;

-- select count(*), sum(invoice_total) 
-- into invoices_count,invoices_total
-- from invoices;

-- set risk_factor = invoices_total / invoices_count * 5;
-- select risk_factor;
-- -- 创建的本地变量执行完会被抹去
-- END
-- 调用call sql_invoicing.get_risk_factor();

-- 创建自己的函数
-- CREATE DEFINER=`root`@`localhost` FUNCTION `get_risk_factor_for_client`(
-- client_id int
-- ) RETURNS int
--     READS SQL DATA
--     DETERMINISTIC
-- BEGIN
-- 	declare risk_factor decimal(9,2) default 0; -- 定义形参,default给默认值
-- 	declare invoices_total decimal(9,2); -- 定义形参
-- 	declare invoices_count int;

-- 	select count(*), sum(invoice_total) 
-- 	into invoices_count,invoices_total
-- 	from invoices i
-- 	where i.client_id = client_id;
--     set risk_factor = invoices_total / invoices_count * 5;
-- 	-- RETURN risk_factor;
--     RETURN ifnull(risk_factor,0);-- 风险因素为空值，可以返回0
-- END

-- 调用
-- select
-- 	client_id,
-- 	name,
-- 	get_risk_factor_for_client(client_id) as risk_factor-- 调用函数
-- from clients
-- 删除
-- drop function if exists get_risk_factor_for_client;