- 函数过程查询总价
DELIMITER $$
DROP FUNCTION IF EXISTS selectProjectTotalPrice$$
CREATE FUNCTION selectProjectTotalPrice(projectId varchar(50)) returns decimal(10,2)
BEGIN
-- 需要定义接收游标数据的变量
# 定义项目的总价
DECLARE projectTotalPrice decimal(10, 2) default 0.00;
# 定义每个配置的总价
DECLARE configTotalPrice decimal(10, 2) default 0.00;
# 定义配置id
DECLARE configId varchar(50);
-- 遍历数据结束标志
DECLARE NO_MORE_RECORD BOOLEAN DEFAULT FALSE;
-- 游标
DECLARE cur CURSOR FOR select t.id from config t where t.project_id = projectId and t.is_deleted = 0;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE_RECORD = TRUE;
-- 打开游标
OPEN cur;
-- 开始循环
read_loop:
LOOP
-- 提取游标里的数据，这里只有一个，多个的话也一样；
FETCH cur INTO configId;
-- 声明结束的时候
IF NO_MORE_RECORD THEN
LEAVE read_loop;
END IF;
-- 这里做你想做的循环的事件
select selectConfigSummary(configId) into configTotalPrice;
SET projectTotalPrice = projectTotalPrice + configTotalPrice;
END LOOP;
-- 关闭游标
CLOSE cur;
RETURN projectTotalPrice;
END $$
DELIMITER ;
- 存储过程查询总价
DELIMITER $$
DROP PROCEDURE IF EXISTS selectProjectPrice$$
CREATE PROCEDURE selectProjectPrice(IN projectId varchar(50))
BEGIN
-- 需要定义接收游标数据的变量
# 定义项目的总价
DECLARE projectTotalPrice decimal(10, 2) default 0.00;
# 定义每个配置的总价
DECLARE configTotalPrice decimal(10, 2) default 0.00;
# 定义配置id
DECLARE configId varchar(50);
-- 遍历数据结束标志
DECLARE NO_MORE_RECORD BOOLEAN DEFAULT FALSE;
-- 游标
DECLARE cur CURSOR FOR select t.id from config t where t.project_id = projectId and t.is_deleted = 0;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE_RECORD = TRUE;
-- 打开游标
OPEN cur;
-- 开始循环
read_loop:
LOOP
-- 提取游标里的数据，这里只有一个，多个的话也一样；
FETCH cur INTO configId;
-- 声明结束的时候
IF NO_MORE_RECORD THEN
LEAVE read_loop;
END IF;
-- 这里做你想做的循环的事件
select selectConfigSummary(configId) into configTotalPrice;
SET projectTotalPrice = projectTotalPrice + configTotalPrice;
END LOOP;
-- 关闭游标
CLOSE cur;
select projectTotalPrice;
END $$
DELIMITER ;
函数
# 查询配置的 ats 价格 = 总价 + 铜价
drop function if exists selectConfigAtsSummary;
create function selectConfigAtsSummary(configId varchar(50)) returns decimal(10, 2)
begin
declare summary decimal(10, 2);
SELECT sum(ifnull(total_price, 0) + ifnull(copper_price, 0)) into summary FROM config_ats t
where t.config_id = configId and t.is_deleted = 0;
if summary is null then
set summary = 0.00;
end if;
return summary;
end;
# 查询配置的 断路器价格 = 总价 + 铜价
drop function if exists selectConfigBreakerSummary;
create function selectConfigBreakerSummary(configId varchar(50)) returns decimal(10, 2)
begin
declare summary decimal(10, 2);
SELECT sum(ifnull(total_price, 0) + ifnull(copper_price, 0)) into summary FROM config_device_parts t
where t.config_id = configId and t.is_deleted = 0;
if summary is null then
set summary = 0.00;
end if;
return summary;
end;
# 查询配置的 配件价格 = 各种配件总价
drop function if exists selectConfigFittingSummary;
create function selectConfigFittingSummary(configId varchar(50)) returns decimal(10, 2)
begin
declare summary decimal(10, 2);
SELECT sum(ifnull(total_price, 0)) into summary FROM config_fitting t
where t.config_id = configId and t.is_deleted = 0;
if summary is null then
set summary = 0.00;
end if;
return summary;
end;
# 查询配置的 断路器配件价格 = 配置的所有断路器的所有配件之和
drop function if exists selectConfigDpfSummary;
create function selectConfigDpfSummary(configId varchar(50)) returns decimal(10, 2)
begin
declare summary decimal(10, 2);
select sum(ifnull(total_price, 0)) into summary from config_dpf t
where t.cdp_id in (
select cdp.id from config_device_parts cdp where cdp.config_id = configId and cdp.is_deleted = 0
) and t.is_deleted = 0;
if summary is null then
set summary = 0.00;
end if;
return summary;
end;
# 查询柜子本身价格
drop function if exists selectConfigSummary;
create function selectConfigSummary(configId varchar(50)) returns decimal(10, 2)
begin
declare summary decimal(10, 2);
select ifnull(t.sale_price, 0) into summary from device t
where t.id = (select c.cabinet_id from config c where c.id = configId);
if summary is null then
set summary = 0.00;
end if;
return summary;
end;
# FIXME 查询配电柜价格(未计算辅材？？百分比？？)
drop function if exists selectCabinetPrice;
create function selectCabinetPrice(configId varchar(50)) returns decimal(10, 2)
begin
declare summary decimal(10, 2);
select selectConfigSummary(configId) +
selectConfigAtsSummary(configId) +
selectConfigBreakerSummary(configId) +
selectConfigDpfSummary(configId) +
selectConfigFittingSummary(configId) into summary;
if summary is null then
set summary = 0.00;
end if;
return summary;
end;
