选择除null或NVL全部为null以外的所有列 [英] Select all columns except null or NVL all null

问题描述

是否可以选择记录中字段不为null或NVL的所有字段为"的所有列? 我正在研究ORACLE

Is any way to select all columns in a record where fields are not null or NVL the null fields to ''? Im working on ORACLE

类似这样的东西

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

或这样

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

推荐答案

只需反转有关NULL逻辑列中所有表中的所有列至少都没有空值/"rel =" nofollow>从列表中的所有表中查找所有至少具有NULL值的列模式.

Just reverse the NULL logic in the demonstration about Find all columns having at least a NULL value from all tables in the schema.

例如，

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

FIND_NULL_COL is a simple user defined function(UDF) which will return 1 for the column which has at least one NULL value :

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状态:

Call the function in SQL to get the NULL status of all the column of any table :

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值的列.

So, NULL_STATUS "1" is the column which has NULL value(s).

这篇关于选择除null或NVL全部为null以外的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！