带有参数的SQL函数可能为NULL[英] SQL function with parameters that may be NULL

本文是小编为大家收集整理的关于带有参数的SQL函数可能为NULL的处理方法,想解了带有参数的SQL函数可能为NULL的问题怎么解决?带有参数的SQL函数可能为NULL问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我想使用以下函数(在 PostgreSQL 中):

create type holder as (userName varchar(50), processName varchar(50), 
    dateTime timestamp, macAddress varchar(30), labName varchar(50),
    subjectName varchar(50));

create or replace function returnData( 
   uName varchar(50), pName varchar(50), dTimeIn timestamp, dTimeOut timestamp,
   macAddress MACADDR, lName varchar(50), subjectName varchar(50)
 ) returns setof holder as $$
declare
    r holder%rowtype;
begin

    for r in  (SELECT DISTINCT u.name, p.process_name, i.date_time,
               c.mac_address, l.lab_name, d.subject_name
    FROM user u, session s, init i, process p, computer c, laboratory l,
         class a, subject d, occur_in o
    WHERE i.process_id = p.process_id AND i.session_id = s.session_id
       AND s.user_id = u.user_id AND s.comp_id = c.comp_id
       AND c.lab_id = l.lab_id AND l.lab_id = o.lab_id
       AND o.class_id = a.class_id AND a.subject_id = d.subject_id
       AND p.blocked = true
       --important part now
       AND u.name = userName AND p.name = processName
       AND i.dateTimeIn > dTimeIn AND i.dateTimeOut < dTimeOut
       AND c.mac_address = macAddress 
       AND l.name = lName AND d.name = subjectName
    )
    loop
        return next r;
    end loop;
    return;

end;
$$
language 'plpgsql';

我将其翻译成英文时可能有一些拼写错误,但只要我传递有效参数,实际功能就可以工作.但我的用户可能不想按用户名过滤结果,例如.或者他可以选择在某个日期之后提供数据,但不指定完成日期.我可以做类似的事情

...   
IF (userName IS NULL AND pName IS NULL AND ...)
    -- query without filtering user name nor process name,
    -- filtering or not the other parameters
IF (userName IS NOT NULL AND pName IS NULL AND ...)
    --filter user name, don't filter process name,
    -- filtering or not the other parameters
...

等等.但是,如果我没记错的话,我必须编写 49 个查询,因为我有 7 个可能会或可能不会被过滤的参数.我可以这样做,但如果可能的话,我想通过做类似的事情来简化我的查询

SELECT ...
FROM ...
WHERE ...
AND userName = *

如果用户不想过滤用户名,其中 * 将是参数 uName 中的值.有可能做这样的事情吗?有没有更好的方法来做到这一点?以及如何处理这两个时间戳?

推荐答案

   AND (userName is null or u.name = userName)
   AND (processName is null or p.name = processName)
   AND (dTimeIn is null or i.dateTimeIn > dTimeIn)
   AND (dTimeOut is null or i.dateTimeOut < dTimeOut)
   AND (macAddress is null or c.mac_address = macAddress)
   AND (lName is null or l.name = lName)
   AND (subjectName is null or d.name = subjectName)

本文地址:https://www.itbaoku.cn/post/1763924.html