存储过程

执行存储过程

MySQL称存储过程的执行为调用（CALL）

创建存储过程

CREATE PROCEDURE productpricing()
SELECT AVG(prod_price) AS priceavg FROM products;
DELIMITER
CREATE
PROCEDURE sqllearn.adb()
BEGIN
SELECT AVG(prod_price) AS priceavg FROM products;
END
DELIMITER ;
命令行应用程序需要更改命令行应用程序的语句分隔符
将语句分隔符改为$$
()是必需的
CALL productpricing()

删除存储过程

DROP PROCEDURE adb;
注意删除时没有()

使用参数

存储过程一般用于把结果返回给指定的变量
对productpricing修改
DELIMITER $$

USE sqllearn$$

DROP PROCEDURE IF EXISTS productpricing$$

CREATE DEFINER=root@localhost
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$$

DELIMITER ;
其中

• IN向存储过程传递数据
• OUT将存储过程的数据传出

  注意

  所有的Mysql变量都以@开始
  call productpricing(@pl,@ph,@pa);
  SELECT @pa;
  
  SELECT @pa,@pl,@ph;

  同时使用IN和OUT（传入参数并根据参数进行处理）

  DELIMITER $$

CREATE
PROCEDURE sqllearn.ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END$$
DELIMITER ;
CALL ordertotal(20005,@total);
SELECT @total;

建立智能存储过程

存储过程可以智能处理
针对某些顾客收取营业税（用一个boolen变量）
DELIMITER $$

USE sqllearn$$

DROP PROCEDURE IF EXISTS ordertotal$$

CREATE DEFINER=root@localhost PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMENT ‘Obtain order total,optionally adding tax’
BEGIN
—declare
DECLARE total DECIMAL(8,2);
DECLARE taxrate INT DEFAULT 6;

SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;

IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF ;
SELECT total INTO ototal;
END$$

DELIMITER ;

• 两个-为注释，（不能用中文字符）
• COMMIENT——会在show procedure status中显示

CALL ordertotal(20005,0,@total);
SELECT @total
CALL ordertotal(20005,1,@total);
SELECT @total

检查存储过程

SHOW CREATE PROCEDURE ordertotal
SHOW PROCEDURE STATUS