तो मेरे पास 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 (in c_title varchar(30)) नामक एक संग्रहीत कार्यविधि बनाना चाहता हूं जो एक अभियान का शीर्षक लेता है और CAMPAIGNFINISHDATE को वर्तमान तिथि और ACTUALCOST को अभियान की लागत में अपडेट करके अभियान को समाप्त करता है, जिसकी गणना अलग-अलग तारीखों पर अलग-अलग कर्मचारियों द्वारा किए गए घंटों की संख्या से की जाती है, और वेतन ग्रेड (यह स्टाफ आईडी और समय सीमा के आधार पर STARTDATE और FINISHDATE के आधार पर staffongrade टेबल।

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 लौटाता है:

select rate_on_date(1, "2018-02-02") as Grade_On_Date;

gradetable

इसके मापदंडों के लिए मुझे लगता है कि मुझे इसे workson तालिका से प्राप्त करना होगा जो इस तरह दिखता है:

workson

मैंने पेग्रेड प्राप्त करने के लिए एक चुनिंदा कथन का उपयोग करने का प्रयास किया है:

select hourlyrate as 'grade' from salarygrade where rate_on_date(1, "2018-02-02") = grade;

pay

ACTUALCOST की गणना करने के लिए मुझे लगता है कि मुझे ग्रेड लागत के साथ HOUR कॉलम को गुणा करके गणना करनी होगी, और WDATE और STAFFNO कॉलम का उपयोग workson मेरी संग्रहीत कार्यविधि के लिए पैरामीटर के रूप में तालिका जो अभियान शीर्षक को इनपुट करके अभियान के CAMPAIGNFINISHDATE और ACTUALCOST की गणना और अद्यतन करेगी। लेकिन मैं ऐसा करने के बारे में कैसे जाउंगा?

मैं इस प्रक्रिया को बनाने के बारे में उलझन में हूं, और यह भी उलझन में हूं कि मेरी संग्रहीत प्रक्रिया में इन सहायक कार्यों का सही तरीके से उपयोग कैसे किया जाए। मुझे ऐसा लगता है कि यह प्रश्न काफी लंबा है लेकिन मैं वास्तव में नहीं जानता कि इस समस्या को हल करने के लिए मुझे क्या पूछना चाहिए या किस दिशा में जाना चाहिए।

0
Daichi 23 अक्टूबर 2019, 13:47

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)

डैश मिल गया है इसलिए मैं आपको अपडेट को एक प्रक्रिया में छोड़ने के लिए छोड़ दूँगा।

यदि स्टाफ़ऑनग्रेड में समाप्ति तिथि के लिए NULL है तो डेटा की थोड़ी सफाई की आवश्यकता है। सादगी के लिए मैं अंतराल को भरने के लिए एक अस्थायी तालिका बनाउंगा और प्रोजेक्टफिनिशडेट का उपयोग करने के लिए अद्यतन विवरण बदल दूंगा (यदि यह ज्ञात नहीं है तो उपयुक्त भविष्य की तारीख को प्रतिस्थापित करें)। यह कोड अपडेट से पहले आपकी प्रक्रिया में डाला जाएगा

इसलिए

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)

जो सभी अंतिम फिनशडेट्स को शून्य के रूप में छोड़ देता है जिसे हम कोलेस का उपयोग करके अपडेट स्टेटमेंट में फंसा सकते हैं

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;
1
P.Salmon 24 अक्टूबर 2019, 10:36