mysql存储过程简介

创建存储过程
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 @pricehigh, @pricelow, @priceaverage;

删除存储过程
DROP PROCEDUCE productpricing;

有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;

执行
CALL ordertotal(20005, @total);

查看
SELECT @total;

不像多数DBMS，MySQL游标只能用于存储过程（和函数）
创建游标
CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num
   FROM orders;
END;

打开游标
OPEN ordernumbers;

实践是检验真理的唯一标准，是骡子是马拉出来溜溜。
一把这些代码输进去，发现不对。

DELIMITER //
CREATE PROCEDURE peopleage(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
   SELECT MIN(age)
   INTO pl
   FROM people;
   SELECT MAX(age)
   INTO ph
   FROM people;
   SELECT AVG(age)
   INTO pa
   FROM people;
END;

//
DELIMITER ;
注：需要用DELIMITER来作为开始结束

巴特西

mysql存储过程简介

最新文章

热门文章