存储过程与游标

1.查看当前所有存储过程

方法一，列出所有的存储过程(包括各项信息)：

show procedure status;

方法二，查看特定的信息：

select `字段名` from MySQL.proc where db = '数据库名' and `type` = 'PROCEDURE' 

可选字段包括：(name,db,body等，查看对应的信息)

2.创建一个最简单的不带参数存储过程

create procedure 名字()
begin
    sql语句
end;

注：

在cmd中测试结束符号;会冲突，需要临时修改结束符号，如下:

delimiter //
create procedure 名字()
begin
    sql语句
end //
delimiter ;

3.删除存储过程

drop procedure 名字

4.调用无参数存储过程

call 名字();

5.创建带参数的存储过程

CREATE PROCEDURE productpricing(
    关键词 参数名 数据类型
                 ……
)
BEGIN
    相应sql语句
END;

注：

关键词有in(传递给存储过程)，out(从存储过程中传出)，inout(对存储过程传入和传出)

参数的数据类型与表中使用的数据类型相同

如：

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;

6.调用有参数存储过程

call 存储过程名(@参数名 ……)

用上例就是：

call productpricing(@pricelow,@pricehigh,$priceaverage);

 注：mysql所有变量都必须以@开始

调用上述与语句后，会将值传给三个变量，输出可以使用：

输出最高价格
select @pricehigh

输出所有值
select @pricehigh,@pricelow,@priceaverage

贴一个书中的完整的存储过程示例：

-- name:ordertotal
-- parameters: onumber = order number
-- textable = 0 if not taxable,1 if taxable
-- otatal = order tatal variable
create procedure ordertatal(
    in onumber int,
    in taxable boolean,
    out otatal decimal(8,2)     
) comment 'obtain order tatal,optionally adding tax'
begin
    -- declare variable for tatal
    declare total decimal(8,2)
    -- declare tax percentage
    declare taxrate int default 6;

    -- get the order tatal
    select sum(item_price*quantity) from orderitems where order_num = onumber into tatal;

    --is this taxable?
    if taxable then
        -- yes, so add taxrate to the total
        select tatal+(tatal/100*taxrate) into tatal;
    end if;
    -- and finally, save to out variable
    select total into ototal;
end;

使用如下：
call ordertatal(2005,0,@total);
select @total;

游标有点类似数组中的指针，可以帮助遍历，查询结果集中的一行数据,mysql的游标智能用于存储过程和函数中

创建游标：

declare 游标名 cursor for sql语句

打开游标：

open 游标名

关闭游标(游标关闭后如果没有重新打开则不能使用，需要重新open，不过不明确关闭游标，mysql会在end语句时关闭)：

close 游标名

贴一个书中的例子：

create procedure processorders()
begin
    declare done boolean default 0;#定义一个done的布尔值，默认是false
    declare o int;
    declare t decimail(8,2);
    declare ordernumbers cursor for select order_num from orders;#定义ordernumbers的游标，指向后面select语句的结果集
    declare continue handler for sqlstate '02000' set done=1;#02000代表未找到条件，其他条件可以参考mysql手册，表示当游标指向最后的时候，将done设为1

    create table if not exists ordertotals (order_num int ,total decimal(8,2));
    open ordernumbers;#打开游标
    repeat #开始循环
        fetch ordernumbers into o;#将游标当前所指向的数据赋值给局部变量o
        call ordertotal(o,1,t);调用上例中的存储过程
        insert into ordertotals(order_num,total) values (o,t);#将数据插入新建的表
    until done end repeat;#当done为true时结束循环
    close ordernumbers;#关闭游标
 end;

例子来源于《mysql必知必会》

转载于:https://www.cnblogs.com/pangziyibudong/p/6098531.html