sql - Oracle 条件存储过程中的 WHERE

Question

我想编写一个存储过程来选择承运人，并带有一个附加参数“i_showEmptyCarrier”来指定是否需要隐藏或显示空承运人。

+---------------------------+
|          Carriers         |
+----+----------+-----------+
| id | label    | location  |
+----+----------+-----------+
| 1  | carrier1 | warehouse |
+----+----------+-----------+
| 2  | carrier2 | warehouse |
+----+----------+-----------+
| 3  | carrier3 | factory   |
+----+----------+-----------+

我需要查询“产品”表以检查承运人是否为空。

+-------------------------------------------+
|                  products                 |
+----+-----------+----------------+---------+
| id | carrierid | productiondate | deleted |
+----+-----------+----------------+---------+
| 1  | 1         | 09/09/2020     | 1       |
+----+-----------+----------------+---------+
| 2  | 1         | 09/09/2020     | 0       |
+----+-----------+----------------+---------+
| 3  | 1         | 09/09/2020     | 0       |
+----+-----------+----------------+---------+
| 4  | 2         | 10/09/2020     | 0       |
+----+-----------+----------------+---------+
| 5  | 2         | 10/09/2020     | 0       |
+----+-----------+----------------+---------+

所以在这种情况下，carrier3 是空的。
我想要的存储过程逻辑是:

PROCEDURE GetCarriers
(
    i_showEmptyCarrier IN number,
    c_Carriers OUT t_cursor
)
AS
BEGIN
OPEN c_Carriers FOR 
SELECT 
label,
(   select count(*) 
    from products 
    where products.carrierid = carriers.carrierid
    and records.deleted = 0
) as nrOfProducts
FROM carriers
if(i_showEmptyCarrier == 1) {
//select carriers without a product
WHERE nrOfProducts = 0 ;
}
else{
//select carriers with a product
WHERE nrOfProducts > 0 ;
}
END GetCarriers;

因此，如果 'i_showEmptyCarrier' = 1，则将选择 3 号空载体，

+----------+--------------+
| label    | nrOfProducts |
+----------+--------------+
| carrier3 | 0            |
+----------+--------------+

否则只选择运营商 1 和 2。

+----------+--------------+
| label    | nrOfProducts |
+----------+--------------+
| carrier1 | 2            |
+----------+--------------+
| carrier2 | 2            |
+----------+--------------+

Answer 1

如何修改查询以使用 left join和 conditional aggregation最后比较输入，

SELECT label,nrOfProducts
FROM
(
  SELECT c.label,SUM(CASE WHEN p.deleted = 0 THEN 1 ELSE 0 END) nrOfProducts
   FROM carriers c
  LEFT JOIN products p 
  ON p.carrierid = c.id
  GROUP BY c.label
)
WHERE ((&i_showEmptyCarrier = 1 AND nrOfProducts = 0) 
       OR (&i_showEmptyCarrier = 0 AND nrOfProducts > 0));

您仍然可以在查询中使用 where 子句而不是 if-else。