sql - 选择除 null 以外的所有列或 NVL 全部为 null

Question

有什么方法可以选择记录中字段不为空或 NVL 空字段为“”的所有列？我在 ORACLE 上工作

像这样

SELECT * IS NOT NULL
FROM table
WHERE id_table ='001'

或者像这样

SELECT NVL(*,'')
FROM table
WHERE id_table ='001'

Answer 1

只需反转 NULL关于 Find all columns having at least a NULL value from all tables in the schema 的演示逻辑.

例如，

FIND_NULL_COL 是一个简单的用户定义函数(UDF)，它将为至少有一个 NULL 的列返回 1 值:

SQL> CREATE OR REPLACE
  2    FUNCTION FIND_NULL_COL(
  3        TABLE_NAME  VARCHAR2,
  4        COLUMN_NAME VARCHAR2)
  5      RETURN NUMBER
  6    IS
  7      cnt NUMBER;
  8    BEGIN
  9      CNT :=1;
 10      EXECUTE IMMEDIATE 'select count(1) from '
 11                        ||TABLE_NAME||' where ' ||COLUMN_NAME||' is not null
 12                        and deptno = 20' INTO cnt;
 13      RETURN
 14      CASE
 15      WHEN CNT=0 THEN
 16        1
 17      ELSE
 18        0
 19      END;
 20    END;
 21    /

Function created.

调用SQL中的函数获取任意表所有列的NULL状态:

SQL>   SET pagesize 1000
SQL>   column owner format A10;
SQL>   column column_name format A20;
SQL>   COLUMN TABLE_NAME FORMAT A20;
SQL>   column n format A1;
SQL>   SELECT c.OWNER,
  2      c.TABLE_NAME,
  3      c.COLUMN_NAME,
  4      C.NULLABLE,
  5      FIND_NULL_COL(c.TABLE_NAME,c.COLUMN_NAME) null_status
  6    FROM all_tab_columns c
  7    WHERE C.OWNER    =USER
  8    AND c.TABLE_NAME = 'EMP'
  9    ORDER BY C.OWNER,
 10      C.TABLE_NAME,
 11      C.COLUMN_ID
 12  /

OWNER      TABLE_NAME           COLUMN_NAME          N NULL_STATUS
---------- -------------------- -------------------- - -----------
SCOTT      EMP                  EMPNO                N           0
SCOTT      EMP                  ENAME                Y           0
SCOTT      EMP                  JOB                  Y           0
SCOTT      EMP                  MGR                  Y           0
SCOTT      EMP                  HIREDATE             Y           0
SCOTT      EMP                  SAL                  Y           0
SCOTT      EMP                  COMM                 Y           1
SCOTT      EMP                  DEPTNO               Y           0

8 rows selected.

SQL>

所以，NULL_STATUS “1”是具有 NULL 值的列。