mysql存储过程
时间：2021-07-01 10:21:17
帮助过：17人阅读
查询如下：
SELECT
b.memberId,
b.memberName,
aa.orderid,
aa.productid,
aa.productname,
aa.other,
aa.num,
c.
paytime
FROM
(SELECT
a.orderid,
a.productid,
a.productname,
a.other,
SUM(a.
number) num
FROM
pengcz_order.
order_details_b2b a
GROUP BY a.orderid,
a.productid,
a.productname,
a.other) aa,
pengcz_order.`order_current` b,
pengcz_order.
`order_paymoney` c
WHERE aa.`orderId` = c.
`orderId`
AND aa.orderid = b.
orderid
AND aa.productid =‘3952584‘ LIMIT 1,20
;
转换成存储过程如下：
DELIMITER $$
USE `pengcz_order`$$
DROP PROCEDURE IF EXISTS `pub_shopProductRecordByProductId`$$
CREATE
DEFINER = `root`@`%
`
PROCEDURE `pengcz_order`.`pub_shopProductRecordByProductId`(IN productId VARCHAR(50),IN page INT,
IN pagesize INT)
COMMENT ‘根据产品id获取成交记录‘
BEGIN
/**
* 创建临时表
* 用于存放订单交易快照明细表查出来的集合
*/
DROP TABLE IF EXISTS `detail`;
CREATE TEMPORARY TABLE `detail` (
`orderId` VARCHAR(45) NOT
NULL,
`productId` VARCHAR(36) NOT
NULL,
`productName` VARCHAR(200)
NULL,
`other` VARCHAR(200)
NULL,
`num` INT(11) NOT
NULL DEFAULT ‘0‘
);
SET @SQL=CONCAT("insert into `detail` select `orderId`,`productId`,`productName`,`other`,SUM(number) from `pengcz_order`.`order_details_b2b` group by `orderId`,`productId`,`productName`,`other`"
);
PREPARE m FROM @SQL;
EXECUTE m;
DEALLOCATE PREPARE m;
/**
* 使用临时表做查找
*/
SET @SQL=CONCAT("select b.memberId,b.memberName,a.orderid,a.productid,a.productname,a.other,a.num,c.paytime from `detail` a,pengcz_order.`order_current` b,pengcz_order.`order_paymoney` c where a.`orderId` = c.`orderId` AND a.orderid = b.orderid AND a.productid =‘",productId,"‘ limit ",page,",",
pagesize);
PREPARE m FROM @SQL;
EXECUTE m;
DEALLOCATE PREPARE m;
/**
* 清理临时表
*/
DROP TABLE `detail`;
END$$
DELIMITER ;
CALL pub_shopProductRecordByProductId(‘3952584‘,1,20);
mysql存储过程
标签：
