首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >MySQL:使存储过程的多个参数成为可选参数

MySQL:使存储过程的多个参数成为可选参数
EN

Stack Overflow用户
提问于 2018-07-17 20:08:10
回答 1查看 2.3K关注 0票数 0

我想创建一个具有多个输入可选参数的存储过程。如果至少给出了一个字段的参数,我仍然希望得到一些满足条件的结果。

下面是我的表定义:

CREATE TABLE `tblinquiries` (
  `UID` varchar(50) DEFAULT NULL,
  `ReviewDate` date NOT NULL,
  `InquiryId` varchar(50) NOT NULL,
  `AuditStatus` varchar(50) DEFAULT NULL,
  `AssignedTo` varchar(50) DEFAULT NULL,
  `Result` int(2) DEFAULT NULL,
  `ResultCategories` int(2) DEFAULT NULL,
  `AuditBy` varchar(50) DEFAULT NULL,
  `Remarks` varchar(200) DEFAULT NULL,
  `StartTime` datetime DEFAULT NULL,
  `EndTime` datetime DEFAULT NULL,
   PRIMARY KEY (InquiryId)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

以下是一个示例数据集:

INSERT INTO `tblinquiries` (`UID`, `ReviewDate`, `InquiryId`, `AuditStatus`, `AssignedTo`, `Result`, `ResultCategories`, `AuditBy`, `Remarks`, `StartTime`, `EndTime`) VALUES
('user1', '2018-07-07', '109933881', 'Assigned', 'auditor1', NULL, NULL, NULL, NULL, '2018-07-15 08:03:47', NULL),
('user2', '2018-07-07', '109933885', 'Assigned', 'auditor2', NULL, NULL, NULL, NULL, '2018-07-15 08:04:13', NULL),
('user3', '2018-07-07', '109933909', 'Complete', 'auditor3', 1, auditor3, NULL, NULL, '2018-07-15 08:06:42', '2018-07-15 08:10:42'),
('user4', '2018-07-08', '109933916', 'Check', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('user5', '2018-07-08', '109933917', 'Check', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('user6', '2018-07-08', '8790804', 'Complete', 'auditor1', 1, NULL, 'auditor1', NULL, '2018-07-14 21:19:25', '2018-07-14 21:21:29');

下面是我的存储过程:

DROP procedure IF EXISTS `spInquiries`;

DELIMITER $$
CREATE PROCEDURE `spInquiries` (IN `pReviewStartDate` DATE, IN `pReviewEndDate` DATE, IN `pAuditStartDate` DATE, IN `pAuditEndDate` DATE)
BEGIN
SELECT * FROM tblinquiries
  WHERE
  (ReviewDate BETWEEN pReviewStartDate AND pReviewEndDate)
  AND (DATE(EndTime) BETWEEN pAuditStartDate AND pAuditEndDate);
END$$

DELIMITER ;

经过大量的研究,我发现this stackoverflow question有一些相似之处。但是我不知道如何使WHERE子句的两个参数(例如- pAuditStartDate,pAuditEndDate)为空。

我已经添加了一个SQL here

我想要这两个命令的一些结果:

CALL spInquiries('', '', '2018-07-01', '2018-07-31');
CALL spInquiries('2018-07-01', '2018-07-31', '', '');

我怎样才能做到这一点呢?

EN

回答 1

Stack Overflow用户

发布于 2018-07-18 07:00:15

MySQL自动从字符串转换date类型,因此您可以使用string而不是date参数。顺便说一下,作为MySQL日期值,空日期('')无效。

在此之后,您可以更改该值,如果比较来自较低的日期,则使用'1900-01-01'作为默认值,使用now()表示较高的范围。

DELIMITER $$
drop procedure if exists `spInquiries`$$
CREATE PROCEDURE `spInquiries` (IN `pReviewStartDate` varchar(30), IN `pReviewEndDate` varchar(30),
                              IN `pAuditStartDate` varchar(30), IN `pAuditEndDate` varchar(30))
BEGIN

  -- sanitize the date ranges
  if pReviewStartDate = '' then 
    set pReviewStartDate = '1900-01-01';
  end if;
  if pAuditStartDate = '' then 
    set pAuditStartDate = '1900-01-01';
  end if;

  if pReviewEndDate = '' then 
    set pReviewEndDate = now();
  end if;

  if pAuditEndDate = '' then 
    set pAuditEndDate = now();
  end if;

  -- now you can run the query without problems

  SELECT * FROM tblinquiries
   WHERE
    (ReviewDate BETWEEN pReviewStartDate AND pReviewEndDate)
     AND (DATE(EndTime) BETWEEN pAuditStartDate AND pAuditEndDate);
END$$

结果:

mysql> CALL spInquiries('', '', '2018-07-01', '2018-07-31');
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+
| UID   | ReviewDate | InquiryId | AuditStatus | AssignedTo | Result | ResultCategories | AuditBy  | Remarks | StartTime           | EndTime             |
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+
| user3 | 2018-07-07 | 109933909 | Complete    | auditor3   |      1 |                0 | NULL     | NULL    | 2018-07-15 08:06:42 | 2018-07-15 08:10:42 |
| user6 | 2018-07-08 | 8790804   | Complete    | auditor1   |      1 |             NULL | auditor1 | NULL    | 2018-07-14 21:19:25 | 2018-07-14 21:21:29 |
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+


mysql> CALL spInquiries('2018-07-01', '2018-07-31', '', '');
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+
| UID   | ReviewDate | InquiryId | AuditStatus | AssignedTo | Result | ResultCategories | AuditBy  | Remarks | StartTime           | EndTime             |
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+
| user3 | 2018-07-07 | 109933909 | Complete    | auditor3   |      1 |                0 | NULL     | NULL    | 2018-07-15 08:06:42 | 2018-07-15 08:10:42 |
| user6 | 2018-07-08 | 8790804   | Complete    | auditor1   |      1 |             NULL | auditor1 | NULL    | 2018-07-14 21:19:25 | 2018-07-14 21:21:29 |
+-------+------------+-----------+-------------+------------+--------+------------------+----------+---------+---------------------+---------------------+

更新:

我更改了存储过程以满足新的参数

DELIMITER $$
drop procedure if exists `spInquiries`$$
CREATE PROCEDURE `spInquiries` (IN `pReviewStartDate` varchar(30), IN `pReviewEndDate` varchar(30),
                                IN `pAuditStartDate` varchar(30), IN `pAuditEndDate` varchar(30), 
                                pInquiryId VARCHAR(30))
BEGIN

  if pReviewStartDate = '' then 
    set @pReviewStartDate = '1900-01-01';
  else
    set @pReviewStartDate = pReviewStartDate;
  end if;
  if pAuditStartDate = '' then 
    set @pAuditStartDate = '1900-01-01';
  else
    set @pAuditStartDate = pAuditStartDate;
  end if;

  if pReviewEndDate = '' then 
    set @pReviewEndDate = now();
  else
    set @pReviewEndDate = pReviewEndDate;
  end if;

  if pAuditEndDate = '' then 
    set @pAuditEndDate = now();
  else
    set @pAuditEndDate = pAuditEndDate;
  end if;

  set @sql = 'SELECT * FROM tblinquiries WHERE 
            (ReviewDate BETWEEN ? AND ?)
             AND (DATE(EndTime) BETWEEN ? AND ?) ';

  if pInquiryId = '' then
    set @sql = concat(@sql,"AND InquiryId IS NULL");
  else
    set @sql = concat(@sql,"AND InquiryId =  '", pInquiryId,"'");
  end if; 

  PREPARE myquery FROM @sql;
  EXECUTE myquery using @pReviewStartDate,@pReviewEndDate,@pAuditStartDate,@pAuditEndDate;

END$$

DELIMITER ;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/51381003

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档