mysql存储过程 菜鸟_MySQL存储过程

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) 你可能没有创建存储过程的安全访问权限，许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程；