SQL - 存储过程

-- 创建一个存储进程delimiter $$
create procedure get_invoices_balance()
beginselect *from invoiceswhere invoice_total-payment_total>0;
end$$delimiter ;
-- 使用 '$$' 改变默认分隔符，告诉MySQL'$$'之间是一个整体，end$$结束call get_clients()	-- call 语句 可以调用 存储进程

-- 参数
drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state(state char(2))
beginselect * from clients cwhere c.state = state;
end $$
delimiter ;call get_clients_by_state('ca')

• 存储过程调用者无法提供参数，我们为参数配置默认值

• -- 提供默认参数
  drop procedure if exists get_clients_by_state;
  delimiter $$
  create procedure get_clients_by_state(state char(2))
  beginif state is null then set state='ca';end if ;	-- 表示 if 语句结束，因为if语句可以是多个select * from clients cwhere c.state = state;
  end $$
  delimiter ;call get_clients_by_state(null)-- 使用 if - else 
  drop procedure if exists get_clients_by_state;
  delimiter $$
  create procedure get_clients_by_state(state char(2))
  beginif state is null then select * from clients;else select * from clients c where c.state=state;end if;
  end $$
  delimiter ;call get_clients_by_state(null)-- 使用 ifnull()
  drop procedure if exists get_payments;
  delimiter $$
  create procedure get_payments(client_id int,payment_method_id tinyint)
  beginselect *from payments pwhere p.client_id=ifnull(client_id,p.client_id) and p.payment_method=ifnull(payment_method_id,p.payment_method);
  end$$
  delimiter ;call get_payments(null,null);

• 当我们使用存储进程来插入、更新、删除数据时，我们要进行参数验证，确保我们的过程不会意外地往数据库存储错误数据。

• -- 参数验证
  drop procedure if exists make_payment;
  delimiter $$
  create procedure make_payment
  (	invoice_id int,payment_amount decimal(9,2),payment_date date)
  beginif payment_amount<=0 then signal sqlstate	'Data Exception'set message_text = '不合理';	-- 错误，抛出异常,终止执行			end if ;update invoices i set i.payment_total=payment_amount,i.payment_date=payment_datewhere i.invoice_id=invoice_id;
  end$$
  delimiter ;call make_payment(1, -20, '2019-03-10');
  

• 我们可以 使用 参数 给 调用程序 返回 值

• drop procedure if exists get_unpaid_invoices_for_client;
  delimiter $$
  create procedure get_unpaid_invoices_for_client (client_id int,out invoices_count int,		-- 标记输出参数out invoices_total decimal(9,2))
  beginselect count(*),sum(invoice_total)into invoices_count,invoices_total -- 读取数据，复制到这些输出参数上from invoices iwhere i.client_id=client_id and payment_total=0;
  end$$
  delimiter ;-- 可以使用 MYSQL工作台提供的图形化工具，更简便
  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;

• 调用有输出参数的存储过程时使用这些变量，通过传递这些变量，来获取输出参数值
• set 语句定义变量，set @invoices_count=0

• 在存储过程或函数中的变量，一旦结束就被清空
• declare 语句声明变量

• -- 本地变量
  drop procedure if exists get_risk_factor;
  delimiter $$
  create procedure get_risk_factor()
  begindeclare riskfactor decimal(9,2) default 0;declare invoices_total decimal;declare invoices_count int;select count(*),sum(invoice_total)into invoices_count,invoices_totalfrom invoices;set riskfactor=invoices_total/invoices_count*5;select riskfactor;
  end$$
  delimiter ;

-- 设置函数
drop function if exists get_risk_factor_for_client;
delimiter $$
create function get_risk_factor_for_client (client_id int)
returns int
-- 设置函数属性，确定性，能读，能更改
-- deterministic 
-- modifies sql data
reads sql data
begin
declare riskfactor decimal(9,2) default 0;declare invoices_total decimal;declare invoices_count int;select count(*),sum(invoice_total)into invoices_count,invoices_totalfrom invoices iwhere i.client_id=client_id;set riskfactor=invoices_total/invoices_count*5;return ifnull(riskfactor,0);
end$$
delimiter ;-- 正常内置函数使用
select client_id,name,get_risk_factor_for_client(client_id)
from clients;