mysql - 如何使用辅助函数创建更新两个字段 mySQL 的存储过程

Question

所以我在 mySQL 上有一些表:

CREATE TABLE IF NOT EXISTS `salarygrade` (
  `GRADE` INT(11) NOT NULL,
  `HOURLYRATE` FLOAT NOT NULL,
  PRIMARY KEY (`GRADE`));

================================================== =============================

   CREATE TABLE IF NOT EXISTS `staffongrade` (
  `STAFFNO` INT(11) NOT NULL,
  `GRADE` INT(11) NOT NULL,
  `STARTDATE` DATE NULL DEFAULT NULL,
  `FINISHDATE` DATE NULL DEFAULT NULL,
  INDEX `STAFFONGRADE_FK` (`STAFFNO` ASC),
  INDEX `STAFFONGRADE2_FK` (`GRADE` ASC),
  PRIMARY KEY (`GRADE`, `STAFFNO`),
  CONSTRAINT `FK_STAFFONG_STAFFONGR_SALARYGR`
  FOREIGN KEY (`GRADE`)
  REFERENCES `salarygrade` (`GRADE`),
  CONSTRAINT `FK_STAFFONG_STAFFONGR_STAFF`
  FOREIGN KEY (`STAFFNO`)
  REFERENCES `staff` (`STAFFNO`));

================================================== =============================

CREATE TABLE IF NOT EXISTS `campaign` (
  `CAMPAIGN_NO` INT(11) NOT NULL,
  `TITLE` VARCHAR(30) NOT NULL,
  `CUSTOMER_ID` INT(11) NOT NULL,
  `THEME` VARCHAR(40) NULL DEFAULT NULL,
  `CAMPAIGNSTARTDATE` DATE NULL DEFAULT NULL,
  `CAMPAIGNFINISHDATE` DATE NULL DEFAULT NULL,
  `ESTIMATEDCOST` INT(11) NULL DEFAULT NULL,
  `ACTUALCOST` FLOAT NULL DEFAULT NULL,
  PRIMARY KEY (`CAMPAIGN_NO`),
  INDEX `OWNS_FK` (`CUSTOMER_ID` ASC),
  CONSTRAINT `FK_CAMPAIGN_OWNS_CUSTOMER`
    FOREIGN KEY (`CUSTOMER_ID`)
    REFERENCES `customer` (`CUSTOMER_ID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT);

================================================== =============================

CREATE TABLE IF NOT EXISTS `workson` (
  `STAFFNO` INT(11) NOT NULL,
  `CAMPAIGN_NO` INT(11) NOT NULL,
  `WDATE` DATE NOT NULL,
  `HOUR` FLOAT NULL DEFAULT NULL,

  PRIMARY KEY (`STAFFNO`, `CAMPAIGN_NO`, `WDATE`),
  INDEX `WORKSON_FK` (`STAFFNO` ASC),
  INDEX `FK_WORKSON_WORKSON2_CAMPAIGN_idx` (`CAMPAIGN_NO` ASC),
  CONSTRAINT `FK_WORKSON_WORKSON2_CAMPAIGN`
    FOREIGN KEY (`CAMPAIGN_NO`)
    REFERENCES `campaign` (`CAMPAIGN_NO`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_WORKSON_WORKSON_STAFF`
    FOREIGN KEY (`STAFFNO`)
    REFERENCES `staff` (`STAFFNO`));

我想创建一个名为 sp_finish_campaign(在 c_title varchar(30)) 的存储过程，它获取营销事件的标题并通过更新 CAMPAIGNFINISHDATE 来完成营销事件为当前日期，ACTUALCOST 为事件费用，该费用根据不同员工在不同日期投入的小时数以及薪资等级(根据员工 ID 和时间范围而变化)计算得出。基于 staffongrade 表的 STARTDATE 和 FINISHDATE。

为了计算ACTUALCOST，我创建了一个辅助函数:

DELIMITER //

CREATE FUNCTION rate_on_date(staff_id int, given_date date)
    RETURNS int
    DETERMINISTIC
BEGIN
    DECLARE salaryGrade int;
    SET salaryGrade = (select grade from staffongrade
    where staffno = staff_id AND (given_date BETWEEN STARTDATE AND FINISHDATE));
RETURN salaryGrade;
END //
DELIMITER ;

它根据我提供的 staff_id 和 given_date 参数返回工资 grade:

选择rate_on_date(1, "2018-02-02") 作为Grade_On_Date;

对于这个参数，我假设我必须从 workson 表中获取它，如下所示:

我尝试使用 select 语句来获取工资等级:

从工资等级中选择每小时工资作为“等级”
其中rate_on_date(1, "2018-02-02") = 成绩;

要计算ACTUALCOST，我假设我必须通过将HOUR列与成绩成本相乘来进行计算，并使用WDATE和workson 表中的 STAFFNO 列作为我的存储过程的参数，该存储过程将计算和更新的 CAMPAIGNFINISHDATE 和 ACTUALCOST通过在其中输入事件标题来进行事件。但我该如何去做呢？

我只是对如何创建此过程感到困惑，并且对如何在存储过程中正确使用这些辅助函数感到困惑。我觉得这个问题很长，但我真的不知道该问什么或应该采取什么方向来解决这个问题。

Answer 1

你并不真的需要一个函数。 mysql 可以进行多表更新(请参阅 https://dev.mysql.com/doc/refman/8.0/en/update.html )在您的情况下它可能看起来像这样

update  campaign c
join 
(select c.campaign_no,
         sum(hour * hourlyrate) cost
from campaign c
join workson  w on w.campaign_no = c.campaign_no
join staffongrade s on s .staffno = w.staffno and w.wdate between s.startdate and s.finishdate
join salarygrade g on g.grade = s.grade
group by c.campaign_no
) s 
on s.campaign_no = c.campaign_no
set actualcost = s.cost
where c.campaign_no = 1
;

子查询执行需要的操作

如果你简化数据，这应该很容易证明；

drop table if exists salarygrade,campaign,workson,staffongrade;

CREATE TABLE  `salarygrade` 
(  GRADE INT NOT NULL,
  hOURLYRATE decimal(10,2) NOT NULL
);
insert into salarygrade values(1,10),(2,20);

cREATE TABLE IF NOT EXISTS `staffongrade` (
  `STAFFNO` INT(11) NOT NULL,
  `GRADE` INT(11) NOT NULL,
  `STARTDATE` DATE NULL DEFAULT NULL,
  `FINISHDATE` DATE NULL DEFAULT NULL
  );

insert into staffongrade values
(1,1,'2019-01-01','2019-06-30'),(1,2,'2019-06-01','2019-12-31'),(2,1,'2019-01-01','2019-01-31');

CREATE TABLE IF NOT EXISTS `campaign` (
  `CAMPAIGN_NO` INT(11) NOT NULL,
  `CAMPAIGNSTARTDATE` DATE NULL DEFAULT NULL,
  `CAMPAIGNFINISHDATE` DATE NULL DEFAULT NULL,
  `ESTIMATEDCOST` INT(11) NULL DEFAULT NULL,
  `ACTUALCOST` FLOAT NULL DEFAULT NULL
  );

insert into campaign values (1,'2019-01-01','2019-12-31',null,null);

CREATE TABLE IF NOT EXISTS `workson` (
  `STAFFNO` INT(11) NOT NULL,
  `CAMPAIGN_NO` INT(11) NOT NULL,
  `WDATE` DATE NOT NULL,
  `HOUR` FLOAT NULL DEFAULT NULL
  );

insert into workson values
(1,1,'2019-01-01',1),(1,1,'2019-12-01',1),(2,1,'2019-01-01',1);

select * from campaign;
+-------------+-------------------+--------------------+---------------+------------+
| CAMPAIGN_NO | CAMPAIGNSTARTDATE | CAMPAIGNFINISHDATE | ESTIMATEDCOST | ACTUALCOST |
+-------------+-------------------+--------------------+---------------+------------+
|           1 | 2019-01-01        | 2019-12-31         |          NULL |         40 |
+-------------+-------------------+--------------------+---------------+------------+
1 row in set (0.00 sec)

必须冲过去，所以我会让您将更新放入程序中。

如果 Staffongrade 的完成日期为 NULL，则需要进行一些数据清理。为简单起见，我将创建一个临时表来填补空白并更改更新语句以使用项目完成日期(如果未知，则替换为合适的 future 日期)。此代码将在更新之前插入到您的程序中

所以

insert into staffongrade values
(1,1,'2019-01-01',null),(1,2,'2019-07-01',null),(2,1,'2019-01-01',null);

drop temporary table if exists staffongradetemp;
create temporary table staffongradetemp like staffongrade;

insert into staffongradetemp 
select s.STAFFNO,s.GRADE,s.STARTDATE,
         case when s.FINISHDATE is not null then s.finishdate 
         else date_sub((select s1.startdate 
            from staffongrade s1 
             where s1.STAFFNO = s.STAFFNO and s1.startdate > s.STARTDATE 
             order by startdate limit 1), interval 1 day)
         end
from staffongrade s
;

select * from staffongradetemp;

+---------+-------+------------+------------+
| STAFFNO | GRADE | STARTDATE  | FINISHDATE |
+---------+-------+------------+------------+
|       1 |     1 | 2019-01-01 | 2019-06-30 |
|       1 |     2 | 2019-07-01 | NULL       |
|       2 |     1 | 2019-01-01 | NULL       |
+---------+-------+------------+------------+
3 rows in set (0.00 sec)

这使得所有最后的 finshdates 都为空，我们可以使用合并在更新语句中捕获它

update  campaign c
join 
(select c.campaign_no,
         sum(hour * hourlyrate) cost
from campaign c
join workson  w on w.campaign_no = c.campaign_no
join **staffongradetemp s** on s .staffno = w.staffno and w.wdate between s.startdate and **coalesce(s.finishdate,c.CAMPAIGNFINISHDATE)**
join salarygrade g on g.grade = s.grade
where c.campaign_no = 1
group by c.campaign_no
) s 
on s.campaign_no = c.campaign_no
set actualcost = s.cost
where 1 = 1;