본문 바로가기
728x90
반응형

Development/DataBase19

[Oracle]세로 data를 가로로 변형하기 - LISTAGG 기존에 세로로 나온 DATA를 가로로 바꿀때에는 쿼리가 복잡했었지만 오라클 11g에서는 LISTAGG라는 함수를 사용해서 간단하게 만들수 있다. WITH RGN AS (SELECT RGN_CD FROM (SELECT 'A' AS RGN_CD FROM DUAL UNION SELECT 'B' AS RGN_CD FROM DUAL UNION SELECT 'C' AS RGN_CD FROM DUAL UNION SELECT 'D' AS RGN_CD FROM DUAL UNION SELECT 'E' AS RGN_CD FROM DUAL ) ) SELECT LISTAGG(RGN_CD, ',') WITHIN GROUP (ORDER BY RGN_CD)AS RGN_CD FROM RGN 결과 RGN_CD -------- A,B,C.. 2011. 11. 8.
[Oracle]ORDER BY 절에서 NULL 값 위치 지정 ORDER BY COLUMNAME NULLS LAST -> NULL 값을 마지막으로 ORDER BY COLUMNAME NULLS FIRST -> NULL 값을 처음으로 2011. 9. 26.
[SQL] 테이블명, 컬럼명, PK를 조회할수 있는 쿼리 DB : Oarcle SELECT A.TABLE_NAME as 테이블명 , B.COMMENTS as 테이블한글명 , A.COLUMN_NAME as 컬럼명 , C.COMMENTS as 컬럼한글명 , A.DATA_LENGTH , A.DATA_TYPE , A.NULLABLE , D.CONSTRAINT_NAME , D.CONSTRAINT_TYPE FROM ALL_TAB_COLUMNS A , ALL_TAB_COMMENTS B , ALL_COL_COMMENTS C , ALL_CONSTRAINTS D WHERE A.TABLE_NAME = B.TABLE_NAME AND A.TABLE_NAME= C.TABLE_NAME AND A.TABLE_NAME= D.TABLE_NAME AND A.COLUMN_NAME= C.COLUM.. 2011. 9. 1.
[Oracle]ALL_COL_COMMENTS ColumnDatatypeNULLDescription OWNER VARCHAR2(30) NOT NULL Owner of the object TABLE_NAME VARCHAR2(30) NOT NULL Name of the object COLUMN_NAME VARCHAR2(30) NOT NULL Name of the column COMMENTS VARCHAR2(4000) Comment on the column 테이블의 컬럼명과 컬럼 한글명을 검색할때 사용하면 된다. 출처 : ORACLE DOCUMENTATION LIBRARY 2011. 9. 1.
[Oracle]ALL_TAB_COMMENTS ColumnDatatypeNULLDescription OWNER VARCHAR2(30) NOT NULL Owner of the object TABLE_NAME VARCHAR2(30) NOT NULL Name of the object TABLE_TYPE VARCHAR2(11) Type of the object COMMENTS VARCHAR2(4000) Comment on the object 테이블 명을 알고 싶을때 사용하면 유용하다.! 출처 : ORACLE DOCUMENTATION LIBRARY 2011. 9. 1.
[Oracle]ALL_CONSTRAINTS Column DatatypeNULLDescription OWNER VARCHAR2(30) NOT NULL Owner of the constraint definition CONSTRAINT_NAME VARCHAR2(30) NOT NULL Name of the constraint definition CONSTRAINT_TYPE VARCHAR2(1) Type of the constraint definition: C - Check constraint on a table P - Primary key U - Unique key R - Referential integrity V - With check option, on a view O - With read only, on a view H - Hash expressi.. 2011. 9. 1.
[Oracle]ALL_CONS_COLUMNS ColumnDatatypeNULLDescription OWNER VARCHAR2(30) NOT NULL Owner of the constraint definition CONSTRAINT_NAME VARCHAR2(30) NOT NULL Name of the constraint definition TABLE_NAME VARCHAR2(30) NOT NULL Name of the table with the constraint definition COLUMN_NAME VARCHAR2(4000) Name of the column or attribute of the object type column specified in the constraint definition Note: If you create a const.. 2011. 9. 1.
728x90
반응형

티스토리툴바