Oracle带可选入参的存储过程：实现无关联点位的用户字段补全聚合查询

你当前的查询逻辑和预期目标的偏差，本质是外连接顺序设计错误、过滤条件位置与连接类型冲突，具体问题点：

• 连接顺序完全错位：你现在的连接链路是 tabellaA 内连接 tabellaB → 右连TABELLAC（用户表）→ 右连TABELLAD（点位表），最终执行逻辑是优先全量保留TABELLAD的点位记录，左侧（用户、访问关系）没有匹配到的字段全部补NULL。当某个点位从来没有产生过任何访问关系时，关联链路会彻底断开，连之前匹配成功的用户基础信息都会被清空，这就是结果里无关联点位的用户字段全为NULL的直接原因。
• 过滤条件与外连接不兼容：你把用户筛选、时间有效性的判断全部放在了整个连接结果的WHERE子句里。一方面，右连生成的NULL值行无法通过 c.nome = 参数值 的判断逻辑；另一方面，sysdate BETWEEN a.datinivalacc AND a.datfinvalacc 这类针对a表的判断，遇到a表为NULL的行时结果为UNKNOWN，会打破外连接的保留逻辑，导致本来要展示的无访问关系点位行，要么被错误带上空用户信息，要么被异常过滤。
• 逻辑目标偏差：你预期的结果是「符合查询条件的每一个用户，都要对应全量点位，哪怕没有访问记录也要展示」，但当前的连接逻辑是「全量点位为基准，匹配到什么访问关系就显示什么，匹配不到就清空左边所有信息」，完全搞反了基准表的选择。

按照你需要的业务逻辑，需要重新设计连接顺序，核心思路是先固定要筛选的用户范围、全量点位范围，先生成「用户×点位」的完整基础组合，再把有效访问关系左关联到这个组合上，保证基础信息不丢失：

1. 单独筛选符合所有可选参数的用户集合，避免后续连接过程把用户过滤掉
2. 取全量需要展示的点位基础数据
3. 用上面两个集合生成笛卡尔组合，保证每个用户对应每一个点位的基础行都存在
4. 把带时间有效性判断的有效访问关系数据，左关联到上述基础组合上，没有匹配到的访问数自动补0

修改后的存储过程核心查询逻辑参考：

create or replace PROCEDURE PROVA 
(
    NOME            IN VARCHAR2,
    COGNOME          IN VARCHAR2,
    CARTA            IN VARCHAR2,
    output OUT SYS_REFCURSOR
)
AS 
BEGIN
OPEN output FOR
WITH filterd_user AS (
    -- 按可选参数筛选用户范围
    SELECT 
        c.nome,
        c.cog cognome,
        c.carta,
        c.profilo
    FROM TABELLAC c
    WHERE
        (NOME IS NULL OR c.nome = NOME)
        AND (COGNOME IS NULL OR c.cog = COGNOME)
        AND (CARTA IS NULL OR c.carta = CARTA)
),
all_point AS (
    -- 全量点位集合
    SELECT 
        d.codicepunto,
        d.s_denominazione
    FROM TABELLAD d
),
valid_access AS (
    -- 带时间有效期判断的有效访问关系
    SELECT
        b.punto,
        a.carta,
        a.numacc
    FROM tabellaA a
    INNER JOIN tabellaB b 
        ON a.id = b.id
    WHERE sysdate BETWEEN a.datinivalacc AND a.datfinvalacc
)
SELECT
    u.nome "NOME",
    u.cognome "COGNOME",
    u.carta "CARTA",
    u.profilo "PROFILO",
    p.s_denominazione "DENOMINAZIONE",    
    CASE
        WHEN COALESCE(SUM(va.numacc), 0) > 0 THEN 'TRUE'
        ELSE 'FALSE'
    END "ABILITATO",
    COALESCE(SUM(va.numacc), 0) "NUMERO ACCESSI"  
FROM filterd_user u
CROSS JOIN all_point p
LEFT JOIN valid_access va 
    ON u.carta = va.carta 
    AND p.codicepunto = va.punto
GROUP BY
    p.s_denominazione,
    u.carta,
    u.nome,
    u.cognome,
    u.profilo
ORDER BY
    p.s_denominazione;
END ;

• 不管某个用户和点位的组合有没有产生有效访问记录，用户的姓名、卡号、画像、点位名称都会完整保留，不会出现用户字段全为NULL的情况
• 所有可选参数的过滤都在最前端的用户筛选环节完成，不会因为外连接的NULL值导致过滤失效，传入任何参数都能准确返回符合条件的用户对应的全量点位数据
• 时间有效性的判断单独封装在有效访问集合里，不会打破左连接的保留逻辑，没有匹配到访问记录的行自动将访问数计为0

内容的提问来源于stack exchange，提问作者Datus Wayne