Mysql
從 Select 語句中獲取值並將它們插入到臨時表儲存過程中
這是我的儲存過程。
我想從表中選擇數據，將其保存在變數中，然後插入其他變數。但是，我的 select 語句有 where。在其中，當天的值應該來自 for 循環。這意味著在 for 循環中，我將獲得特定日期的 DAYNAME(dateTime)。在此基礎上，我想獲取值並在插入語句中使用它。
DROP PROCEDURE IF EXISTS home_cleaner.getAvailableDateAndTime; CREATE PROCEDURE home_cleaner.`getAvailableDateAndTime`(dateTime date) BEGIN DECLARE dateTime datetime DEFAULT NOW(); DECLARE dateTimeCounter int DEFAULT 0; DECLARE dateTimeCounterLimit int DEFAULT 90; DECLARE result varchar(255) DEFAULT NULL; DECLARE result1 varchar(255) DEFAULT NULL; DECLARE result2 varchar(255) DEFAULT NULL; DECLARE result3 varchar(255) DEFAULT NULL; DECLARE result4 varchar(255) DEFAULT NULL; DECLARE c CURSOR FOR SELECT GROUP_CONCAT( cleaner_calender.from, "-", cleaner_calender.to) AS result FROM cleaner_calender WHERE day = DAYNAME(dateTime) AND status = "1"; DROP TABLE IF EXISTS foo; CREATE TABLE foo ( id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, date1 date NULL, dayNameP varchar(20) NULL, timings varchar(255) NULL ) ENGINE = innodb; OPEN c; WHILE dateTimeCounter < dateTimeCounterLimit DO // here i want to fetch result1 for the dateTime which will be incrementing FETCH c INTO result1; INSERT INTO foo(date1, dayNameP, timings) VALUES (dateTime, DAYNAME(dateTime), result1); SET dateTime = DATE_ADD(dateTime, INTERVAL 1 DAY); SET dateTimeCounter = dateTimeCounter + 1; END WHILE; CLOSE c; SELECT * FROM foo; END;已編輯
-- -- Table structure for table `cleaner_calender` -- CREATE TABLE IF NOT EXISTS `cleaner_calender` ( `cleaner_calender_id` bigint(20) unsigned NOT NULL, `cleaner_id` bigint(20) unsigned NOT NULL, `day` enum('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') NOT NULL, `from` time NOT NULL, `to` time NOT NULL, `status` tinyint(3) unsigned NOT NULL DEFAULT '1', `created_at` int(11) unsigned NOT NULL, `updated_at` int(11) unsigned DEFAULT NULL, `created_by` bigint(20) unsigned NOT NULL, `updated_by` bigint(20) unsigned DEFAULT NULL ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; -- -- Dumping data for table `cleaner_calender` -- INSERT INTO `cleaner_calender` (`cleaner_calender_id`, `cleaner_id`, `day`, `from`, `to`, `status`, `created_at`, `updated_at`, `created_by`, `updated_by`) VALUES (2, 13, 'Sunday', '00:30:00', '02:00:00', 2, 1435488637, 1435488637, 22, NULL), (3, 13, 'Sunday', '00:30:00', '02:00:00', 2, 1435488725, 1435488725, 22, NULL), (4, 13, 'Sunday', '00:30:00', '02:00:00', 2, 1435488770, 1435488770, 22, NULL), (5, 13, 'Monday', '02:00:00', '02:30:00', 2, 1435488771, 1435488771, 22, NULL), -- -- Indexes for table `cleaner_calender` -- ALTER TABLE `cleaner_calender` ADD PRIMARY KEY (`cleaner_calender_id`), ADD KEY `created_by` (`created_by`), ADD KEY `updated_by` (`updated_by`), ADD KEY `cleaner_id` (`cleaner_id`), ADD KEY `created_at` (`created_at`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `cleaner_calender` -- ALTER TABLE `cleaner_calender` MODIFY `cleaner_calender_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=54; -- -- Constraints for dumped tables -- -- -- Constraints for table `cleaner_calender` -- ALTER TABLE `cleaner_calender` ADD CONSTRAINT `cleaner_calender_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON UPDATE NO ACTION, ADD CONSTRAINT `cleaner_calender_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON UPDATE NO ACTION, ADD CONSTRAINT `cleaner_calender_ibfk_3` FOREIGN KEY (`cleaner_id`) REFERENCES `cleaners` (`cleaner_id`) ON UPDATE NO ACTION;
問題是我的
select查詢返回了不止一行，而我的程式碼沒有通過“循環”處理它們，因此它無法正常工作並給我錯誤。這是我的新程序…
BEGIN DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler. DECLARE dateTime datetime DEFAULT NOW(); DECLARE dateTimeCounter int DEFAULT 0; DECLARE dateTimeCounterLimit int DEFAULT 90; DECLARE result varchar(255) DEFAULT NULL; DECLARE resultTimePeriod varchar(255) DEFAULT NULL; DECLARE rsultTimeDiffH int DEFAULT NULL; DECLARE resultUnavailiability varchar(255) DEFAULT NULL; DECLARE resultDate date DEFAULT NULL; DECLARE resultPossible int DEFAULT NULL; DECLARE resultPossibleStartTime time DEFAULT NULL; DECLARE c CURSOR FOR SELECT CONCAT(cleaner_calender.from, "-", cleaner_calender.to) AS result, TIME_TO_SEC( TIMEDIFF(cleaner_calender.to, cleaner_calender.from)) AS timeDiffH, CONCAT(cleaner_unavailability.start_time, "-", cleaner_unavailability.end_time) AS unavailiability, cleaner_unavailability.`date`, CASE WHEN cleaner_unavailability.start_time IS NOT NULL && cleaner_unavailability.end_time IS NOT NULL THEN CASE WHEN ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) && ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) > TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) <= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN CASE WHEN TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, cleaner_calender.from)) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, cleaner_unavailability.end_time)) THEN TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, cleaner_calender.from)) ELSE TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, cleaner_unavailability.end_time)) END WHEN ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) && (TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) > TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, cleaner_calender.from)) WHEN (TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00"))) && ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) > TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) <= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, cleaner_unavailability.end_time)) WHEN (TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00"))) && (TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) > TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN "STEP 4" WHEN ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN TIME_TO_SEC( TIMEDIFF(cleaner_calender.to, cleaner_calender.from)) WHEN ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00"))) THEN TIME_TO_SEC( TIMEDIFF(cleaner_calender.to, cleaner_calender.from)) ELSE "STEP LAST" END ELSE TIME_TO_SEC( TIMEDIFF(cleaner_calender.to, cleaner_calender.from)) END AS possible, CASE WHEN cleaner_unavailability.start_time IS NOT NULL && cleaner_unavailability.end_time IS NOT NULL THEN CASE WHEN ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) && ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) > TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) <= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN CASE WHEN TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, cleaner_calender.from)) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, cleaner_unavailability.end_time)) THEN cleaner_calender.from ELSE cleaner_unavailability.end_time END WHEN ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) && (TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) > TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN cleaner_calender.from WHEN (TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00"))) && ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) > TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) <= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN cleaner_unavailability.end_time WHEN (TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00"))) && (TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) > TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN 0 WHEN ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) >= TIME_TO_SEC( TIMEDIFF( cleaner_calender.to, "00:00:00"))) THEN cleaner_calender.from WHEN ( TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.start_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00")) && TIME_TO_SEC( TIMEDIFF( cleaner_unavailability.end_time, "00:00:00")) < TIME_TO_SEC( TIMEDIFF( cleaner_calender.from, "00:00:00"))) THEN cleaner_calender.from ELSE 0 END ELSE cleaner_calender.from END AS possibleStartTime FROM cleaner_calender LEFT JOIN cleaner_unavailability ON cleaner_calender.cleaner_id = cleaner_unavailability.cleaner_id AND cleaner_unavailability.status = "1" AND cleaner_unavailability.`date` = DATE(dateTime) WHERE cleaner_calender.status = "1" AND day = DAYNAME(dateTime) HAVING possible > seconds; -- Declaring continue handler for the cursor stg_product_data DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; DROP TABLE IF EXISTS foo; CREATE TABLE foo ( id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, date1 date NULL, dayNameP varchar(20) NULL, timings varchar(255) NULL, tresultTimePeriod varchar(255) NULL, trsultTimeDiffH varchar(255) NULL, tresultUnavailiability varchar(255) NULL, tresultDate varchar(255) NULL, tresultPossible varchar(255) NULL, tresultPossibleStartTime varchar(255) NULL ) ENGINE = innodb; WHILE dateTimeCounter < dateTimeCounterLimit DO OPEN c; read_loop: LOOP -- Loop through the records FETCH c INTO resultTimePeriod, rsultTimeDiffH, resultUnavailiability, resultDate, resultPossible, resultPossibleStartTime; IF v_done THEN -- Checking if the cursor has fetched the last record LEAVE read_loop; -- Exit if last record had been fetched ELSE -- IF resultPossible IS NOT NULL -- THEN INSERT INTO foo(date1, dayNameP, timings, tresultTimePeriod, trsultTimeDiffH, tresultUnavailiability, tresultDate, tresultPossible, tresultPossibleStartTime) VALUES (dateTime, DAYNAME(dateTime), resultPossible, resultTimePeriod, rsultTimeDiffH, resultUnavailiability, resultDate, resultPossible, resultPossibleStartTime); -- END IF; END IF; END LOOP; SET dateTime = DATE_ADD(dateTime, INTERVAL 1 DAY); SET dateTimeCounter = dateTimeCounter + 1; SET v_done = FALSE ; CLOSE c; END WHILE; SELECT * FROM foo; END