watist's blog ... I am interested and passionate in building software.

오라클 데이터베이스 리뷰에 유용한 스키마 조회 쿼리입니다. ----------------------------------------------------------- -- TABLE 조회 ----------------------------------------------------------- SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM ALL_TABLES WHERE OWNER = 'MYNAME'; SELECT * FROM ALL_OBJECTS WHERE OWNER = 'MYNAME'; SELECT SUBSTR(TABLE_NAME, 1, 2) AS AREA1 , SUBSTR(TABLE_NAME, 4, 1) AS AREA2 , SUBSTR(TABLE_NAME, 4, ..

-- TABLE SELECT A.NAME, B.COMMENT$ FROM OBJ$ A, COM$ B WHERE A.NAME LIKE 'BEMO%' AND TYPE# = 2 AND B.COL#(+) IS NULL AND A.OBJ# = B.OBJ#(+); -- VIEW SELECT A.NAME, B.COMMENT$ FROM OBJ$ A, COM$ B WHERE A.NAME LIKE 'BEMO%' AND TYPE# = 4 AND B.COL#(+) IS NULL AND A.OBJ# = B.OBJ#(+); -- COLUMN SELECT O.NAME, C.NAME, CO.COMMENT$ FROM SYS.OBJ$ O, SYS.COL$ C, SYS.COM$ CO WHERE O.NAME LIKE 'BEMO%' AND O..

전에 작성한 [오라클] CTE를 이용한 데이터 입력 2탄, CTE를 이용한 데이터 수정입니다. CREATE TABLE MYT (ID INT, NAME VARCHAR(100), INDATE DATE); INSERT INTO MYT VALUES (1, '김', SYSDATE); INSERT INTO MYT VALUES (2, '이', SYSDATE); INSERT INTO MYT VALUES (3, '박', SYSDATE); SELECT * FROM MYT; UPDATE MYT A SET NAME = NVL((WITH TAB AS (SELECT 1 ID, '최' NAME FROM DUAL UNION SELECT 2 ID, '황' NAME FROM DUAL) SELECT NAME FROM TAB B WHERE..

사실 너무 간단한 거지만, 될지 안될지 좀 고민했던 거라... 뭘 고민했단 건지ㅡㅡ;; 그냥 코드 나갑니다~~ ㅎㅎ CREATE TABLE MYT2 (NAM VARCHAR2(10)); SELECT * FROM MYT2; INSERT INTO MYT2 WITH S1 AS (SELECT 'TEST2' FROM DUAL UNION SELECT 'TEST3' FROM DUAL) SELECT * FROM S1; SELECT * FROM MYT2; -- 성공!! DROP TABLE MYT2;