MySQL存储过程procedure

1. 存储过程

存储过程：就是为了以后的使用而保存的一条或者多条SQL语句的集合。

1. 创建无参存储过程
create procedure productpricing()
begin
    select avg(prod_price) as priceaverage from products;
end;

使用无参存储过程
call productpricing();

删除存储过程
drop procedure productpricing;

2. 创建含参存储过程
create procedure productpricing(
    out pl decimal(8, 2),
	out ph decimal(8, 2),
	out pa decimal(8, 2)
)
begin
    select min(prod_price) into pl from products;
	select max(prod_price) into ph from products;
	select avg(prod_price) into pa from products;
end;

指定含参存储过程变量名
call productpricing(@pricelow, @pricehigh, @priceaverage);
使用含参存储过程
select @priceaverage;
select @pricelow, @pricehigh, @priceaverage;

3. 创建含有in、out参数的存储过程
create procedure ordertotal(
    in onumber int,
	out ototal decimal(8,2)
)
begin
    select sum(item_price*quantity) from orderitems where order_num = onumber into ototal;
end;

指定含有in、out参数的存储过程变量名
call ordertotal(20005, @total);
使用含有in、out参数的存储过程
select @total;

4. 建立只能存储过程
1) 获得合计 2) 判断时候含税(0为假，1为真) 3) 返回合计
-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable
create procedure ordertotal(
    in onumber int,
    in taxable boolean,
    out ototal decimal(8, 2)
) comment 'Obtain order total, optionally adding tax'
begin
    -- declare variable for total
    declare total decimal(8, 2);
    -- declare tax percentage
    declare taxrate int default 6;

    -- get the order total
    select sum(item_price*quantity) from orderitems where order_num = onumber into total;
	
    -- is this taxable?
    if taxable then
    -- yes, so add taxrate to the total
    select total+(total/100*taxrate) into total;
    end if;
    
    -- add finally, save to out variable
    select total into ototal;
end;

使用
call ordertotal(20005, 0, @total);
select @total;
call ordertotal(20005, 1, @total);
select @total;

5. 检查存储过程
show create procedure ordertotal;

列出所有存储过程
show procedure status;

查询单个存储过程详细信息
show procedure status like 'ordertotal';

1. MySQL命令行客户机的分隔符
    默认的MySQL语句分隔符为;。如果命令行使用程序要解释存储过程自身内的;字符，则它们不会成为存储过程的成分，这回使存储过程中的SQL出现句法错误。
    delimiter //告诉命令行使用程序使用//作为新句结束分隔符，在执行完存储过程后使用delimiter ;充值为默认。
    解决办法如下：
    delimiter //
    create procedure productpricing()
    begin
        select avg(prod_price) as priceaverage from products;
    end //
    delimiter ;
2. 所有MySQL变量都必须以@开始
3. MySQL注释使用--
4. 使用存储过程的好处
    1) 通过把处理封装在容易使用的单元中，简化复杂的操作；
    2) 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点延伸就是防止错误，需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性；
    3) 简化对变动的关联。如果表名，列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会；
    4) 提高新能。因为使用存储过程比使用单独的SQL语句要快；
    5) 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。存储过程的3个好处：简单、安全、性能高；
   缺点
    1) 存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验；
    2) 你可能没有创建存储过程的安全访问权限，许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程；