如题,如何在一个字段很多的表中 较简单的确定出有哪些字段的值不是null, 谢谢 各位高手
解决方案 »
- 求救~~~多行转字符串
- 关联日期最大的2条数据
- ORA-01009: 必需的参数缺失什么情况
- update 的简单写法
- 高人帮忙,帮小弟写几行查询语句
- oracle怎么计算逻辑读?
- 请那位好心的朋友帮忙把redhat9 下安装oracle(9.2.0.4)比较详细的文档发到帖子上,我们这上网限制
- 一个简单的问题,请高手们指点!!
- 在个人pc机上装Oracle 9i能行么?我的是赛扬1.7 内存512m
- LONG RAW 字段与SQL的IMAGE字段有什么区别?从LONG RAW字段中怎么取出??
- ORACLE建立触发器时出现WARNING:TRIGGER CREATED WITH COMPILATION ERRORS
- win7上阵,为什么oracle安装不上去??
这些字段的值全不为空?
CREATE OR REPLACE PROCEDURE printcol(i_tablename VARCHAR2) IS
cur SYS_REFCURSOR;
v_column_name VARCHAR2(40);
cnt PLS_INTEGER;
BEGIN
OPEN cur FOR 'select t.COLUMN_NAME from user_tab_columns t where t.TABLE_NAME=upper(''' || i_tablename || ''')';
LOOP
FETCH cur
INTO v_column_name;
EXIT WHEN cur%NOTFOUND;
cnt := 0;
EXECUTE IMMEDIATE 'select count(1) from ' || i_tablename || ' where ' || v_column_name ||
' is null '
INTO cnt;
IF cnt = 0 THEN
dbms_output.put_line(v_column_name);
END IF;
END LOOP;
END printcol;
CREATE OR REPLACE PROCEDURE printcol(i_tablename VARCHAR2) IS
cur SYS_REFCURSOR;
v_column_name VARCHAR2(40);
cnt PLS_INTEGER;
BEGIN
OPEN cur FOR 'select t.COLUMN_NAME from user_tab_columns t where t.TABLE_NAME=upper('''
|| i_tablename || ''')';
LOOP
FETCH cur
INTO v_column_name;
EXIT WHEN cur%NOTFOUND;
cnt := 0;
EXECUTE IMMEDIATE 'select count(1) from ' || i_tablename || ' where ' || v_column_name ||
' is null '
INTO cnt;
IF cnt = 0 THEN
dbms_output.put_line(v_column_name);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END printcol;
执行结果:
SQL> set serveroutput on;
SQL> exec printcol('test');
ID
TIME
PL/SQL procedure successfully completed
SQL>