关于procedure:
1、相当于一个封装的函数,可以用来输出相应条件下的数据,这一点和直接写where相同,但其可以用于程序化的更新update
创建procedure
# 创建procedure
dilimiter $$ # 修改分隔符
create procedure procedure_name()
begin
select * from tabel; # 每条语句后用;分隔
end$$
dilimiter ; # 修改分隔符
调用procedure
# 调用procedure
call procedure_name() # 若有参数需要填写参数
删除procedure
# 删除procedure
drop procedure procedure_name;
drop procedure if exists procedure_name;
添加参数
# 添加参数
# 类似于封装函数
dilimiter $$
create procedure procedure_name(
parameter int # 若有多个参数用,分开
)
begin
select * from tabel
where tabel.parameter=parameter;
end$$
dilimiter ;
call procedure_name(1); #调用时一定要填写参数
# output:parameter=1的值
添加参数默认值
# 参数填写null值返回默认参数值
dilimiter $$
create procedure procedure_name(
parameter int
)
begin
if parameter is null then
set parameter = 1 ; # 若为字符串要用引号,‘CA'
end if ;
select * from tabel
where tabel.parameter=parameter;
end$$
dilimiter ;
call procedure_name(null);
# output:parameter =1的值
# 参数填写为null时返回全部的值
dilimiter $$
create procedure procedure_name(
parameter int
)
begin
if parameter is null then
select * from tabel;
else
select * from tabel
where tabel.parameter=parameter;
end if ;
end$$
dilimiter ;
call procedure_name(null);
# output:select * from tabel所有的值
# 另一种方法
dilimiter $$
create procedure procedure_name(
parameter int
)
begin
select * from tabel
where tabel.parameter=ifnull(parameter,tabel.parameter);
# ifnull表示如果这里输入的parameter为null,输出所有的行
# 这里可以出现多个ifnull
end$$
dilimiter ;
call procedure_name(null);
# output:select * from tabel所有的值
用于更新的procedure
delimiter $$
create procedure make_payment
(
invoice_id int,
payment_amount decimal(9,2),
payment_date date
)
begin
# 防止出现负值
# 注意!过多的验证条件会拖慢进程
if payment_amount <=0 then
signal sqlstate '22003'
set messaget_test = 'invalid 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$$
delimiter ;
输出参数
delimiter $$
create procedure get_unpaid_invoices_for_client(
client_id int,
out invoices_count int, # out为输出变量
out invoices_total decimal(9,2)
)
begin
select count(*),sum(invoice_total)
into invoices_count,invoices_total # 输出变量赋值
from invoices i
where i.client_id=client_id and
payment_total =0;
end
变量:这里我也没搞懂
CREATE PROCEDURE get_risk_factor()
BEGIN
# 声明变量
declare risk_factor decimal(9,2) default 0;
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