Oracle 检测未使用变量和常量

1. 在Oracle SQL Developer的用户报表中可以定义查询SQL

2. SQL文如下

WITH declarations AS (
    SELECT name, signature, type, object_name, line, col
    FROM user_identifiers
    WHERE 1=1
      AND object_name IN (
                              'PKG_XXXX01'
                            , 'PKG_XXXX02'
      )  -- Package name
      AND object_type = 'PACKAGE BODY'
      AND usage = 'DECLARATION'
      AND type IN ('VARIABLE', 'CONSTANT')    -- 过滤变量和常量
),
references AS (
    SELECT DISTINCT signature
    FROM user_identifiers
    WHERE 1=1
      AND object_name IN (
                              'PKG_XXXX01'
                            , 'PKG_XXXX02'
      )  -- Package name
      AND object_type = 'PACKAGE BODY'
      AND usage IN ('REFERENCE', 'ASSIGNMENT')
)
SELECT d.object_name, lower(d.name) AS unused_variable, d.type, d.line
FROM declarations d
LEFT JOIN references r ON d.signature = r.signature
WHERE r.signature IS NULL
ORDER BY d.object_name, d.line