데이터베이스 질문과 답변
안녕하세요.
전체 테이블 대상으로 다음과 같은 정보를 조회하려고 합니다.
우선 테이블과 컬럼정보는 이런식으로 조회했습니다.
SELECT A.TABLE_NAME
, A.COMMENTS AS TBL_COMM
, B.COLUMN_NAME
, B.COMMENTS AS COL_COMM
FROM ALL_TAB_COMMENTS A, ALL_COL_COMMENTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_TYPE = 'TABLE'
TABLE1에 총 50건의 데이터가 있다고 가정할때, 테이블의 모든 컬럼에서 NULL값을 뺀 데이터 건수만 추출하고 싶습니다.
PK포함해서 필수컬럼은 당연히 ROW수만큼 데이터가 들어갈텐데 선택적으로 입력받는 컬럼은 NULL값이 들어갈테니 실제 값이 있는 경우만 카운트해야하고,
테이블마다 각각 다른 컬럼갯수를 가지고 있어서 그 부분도 동적으로 어떻게 풀어야할지 좀 막막하네요..
| 테이블명 | 테이블 설명 | 전체 데이터 건수 | 컬럼명 | 컬럼설명 | 컬럼 데이터 건수 |
| TABLE_1 | 테스트 테이블1 | 50 | ID | 아이디 | 50 |
| TABLE_1 | 테스트 테이블1 | 50 | NAME | 이름 | 50 |
| TABLE_1 | 테스트 테이블1 | 50 | DATA1 | 데이터1 | 45 |
| TABLE_1 | 테스트 테이블1 | 50 | DATA2 | 데이터2 | 10 |
| TABLE_1 | 테스트 테이블1 | 50 | DATA3 | 데이터3 | 13 |
| TABLE_1 | 테스트 테이블1 | 50 | DATA4 | 데이터4 | 27 |
| TABLE_1 | 테스트 테이블1 | 50 | DATA5 | 데이터5 | 6 |
| TABLE_1 | 테스트 테이블1 | 50 | DATA6 | 데이터6 | 5 |
고수분들의 조언 부탁드립니다.
by 마농
[2021.05.14 08:00:01]
가능은 합니다. 다만.
전체 사용자 및 전체 테이블을 대상으로 하기 보다는
특정 유저나 정해진 테이블만 돌리도록 대상 범위를 줄여야 할 것입니다.
또한 에러가 나지 않도록 다양한 조건이 추가되어야 할 것입니다.
http://gurubee.net/article/61164
http://gurubee.net/article/55486
-- Oracle 에서 테스트 --
SELECT a.owner
, a.table_name
, a.comments tbl_comm
, c.column_id
, b.column_name
, b.comments col_comm
, TO_NUMBER(
dbms_xmlgen.getxmltype(
'SELECT COUNT('||
CASE WHEN SUBSTR(c.data_type, 1, 9)
IN ('DATE','TIMESTAMP','FLOAT','NUMBER','BINARY_DO'
,'CHAR','NCHAR','VARCHAR','VARCHAR2','NVARCHAR','NVARCHAR2')
THEN b.column_name
ELSE 'NULL'
END
||') FROM '||a.owner||'.'||b.table_name
).Extract('//text()')
) cnt
FROM all_tab_comments a
, all_col_comments b
, all_tab_columns c
WHERE a.owner = b.owner
AND b.owner = c.owner
AND a.table_name = b.table_name
AND b.table_name = c.table_name
AND b.column_name = c.column_name
AND a.table_type = 'TABLE'
AND a.table_name NOT LIKE 'BIN$%'
AND a.owner NOT IN ('ANONYMOUS','APEX_040000','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP'
,'DIP','FLOWS_FILES','MDSYS','ORACLE_OCM','OUTLN','SYS','SYSTEM','XDB','XS$NULL')
AND a.owner IN ('SCOTT','HR')
-- AND a.table_name IN ('EMP','DEPT')
;
by 김코옹
[2021.05.14 10:17:47]
마농님 답변 감사합니다.
그런데 제가 티베로5를 사용중인데 JDBC-11032:An invalid data type was returned by the PSM function 와 같은 오류메시지가 나네요.
PSM Object 방식이 티베로6부터 지원하는걸로 알고있는데 맞는지 모르겠습니다..
혹시 cnt 가져오는 부분은 적어주신 저 방법 밖에는 없는걸까요?
by 마농
[2021.05.14 10:48:06]
일단 오라클 기준으로 답변 드렸습니다.
dbms_xmlgen 기능이 있는지 확인하세요.
) 버튼을 클릭하여 작성 하시면 됩니다.