mysql 计算总价_Mysql 存储过程查询总价，函数查询总价

函数过程查询总价

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;