¡á¿¹ 5_5_1 || VIEW »ý¼º ¿¹
CREATE VIEW v_INSA
AS SELECT SABUN, NAME, ENG_NAME, CMP_REG_NO, JOIN_GBN_CODE
FROM INSA
WHERE JOIN_GBN_CODE = 'RGL';
SELECT SABUN, ENG_NAME, JOIN_GBN_CODE
FROM v_INSA;
¡á¿¹ 5_5_2 || µÎ °³ ÀÌ»ó Å×À̺í·Î VIEW »ý¼º ¿¹Á¦
CREATE VIEW CP_INSA
AS SELECT I.SABUN, I.ENG_NAME, C.CMP_NAME, C.CMP_ZIP ZIPCODE, C.CMP_ADDR1 ADDR
FROM INSA I, INSA_COMPANY C
WHERE I.CMP_REG_NO = C.CMP_REG_NO(+);
SELECT * FROM CP_INSA;
¡á¿¹ 5_5_3 || ±âÁ¸ »ý¼º VIEW È®ÀÎ
SELECT * FROM USER_VIEWS;
¡á¿¹ 5_5_4 || v_INSA VIEW¸¦ ÀÌ¿ëÇÑ INSERT
INSERT INTO v_INSA(SABUN, NAME, ENG_NAME, JOIN_GBN_CODE)
VALUES('2013022803', '¹ÚÁØÇü', 'JUNE', 'RGL');
¡á¿¹ 5_5_5 || v_INSA VIEW¸¦ ÀÌ¿ëÇÑ UPDATE
____UPDATE v_INSA SET____ CMP_REG_NO='2222222206'
WHERE SABUN='2013022803';
¡á¿¹ 5_5_6 || CP_INSA VIEW¸¦ ÀÌ¿ëÇÑ DELETE
DELETE CP_INSA
WHERE SABUN = '2012010124';
¡á¿¹ 5_5_7 || VIEW »èÁ¦
DROP VIEW v_INSA;
¡á¿¹ 5_5_8 || DICTIONARY VIEW¸¦ ÀÌ¿ëÇÑ Å×À̺íÁ¤ÀǼ
SELECT DECODE(COLUMN_ID, 1, A.TABLE_NAME, NULL) TNAME,
DECODE(COLUMN_ID, 1, B.COMMENTS, NULL) TNAME_COMT,
A.COLUMN_NAME,
C.COMMENTS AS COLUMN_COMT,
DATA_TYPE||
DECODE(DATA_TYPE,
'NUMBER', DECODE(DATA_PRECISION,
NULL, DECODE(DATA_SCALE,
0, '(38)',
NULL, '',
'('||DATA_PRECISION||','||DATA_SCALE||')'
),
DECODE(DATA_SCALE,
0, '('||DATA_PRECISION||')',
NULL, '('||DATA_PRECISION||')',
'('||DATA_PRECISION||','||DATA_SCALE||')'
)
),
'DATE' , '', 'LONG' , '',
'LONGRAW', '', 'BLOB' , '',
'CLOB' , '', 'NBLOB' , '',
'NCLOB' , '',
'('||CHAR_COL_DECL_LENGTH||')') DATA_LEN,
DECODE(A.NULLABLE,'Y','','NOT NULL') NULLABLE
FROM USER_TAB_COLUMNS A,
USER_TAB_COMMENTS B,
USER_COL_COMMENTS C
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = C.TABLE_NAME
AND A.COLUMN_NAME = C.COLUMN_NAME
ORDER BY A.TABLE_NAME, A.COLUMN_ID;
¡á¿¹ 5_5_9 || DICTIONARY VIEW¸¦ ÀÌ¿ëÇÑ ¶ô(LOCK)ÀÇ Á¶È¸
SELECT A.SID, A.SERIAL#
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID=B.SID
AND B.ID1=C.OBJECT_ID
AND B.TYPE='TM'
AND C.OBJECT_NAME='CMM_CODE_DETAIL';
¡á¿¹ 5_5_10 || SYNONYM »ý¼º ¿¹
CREATE PUBLIC SYNONYM SY_INSA
FOR INSA.v_INSA;
DESC SY_INSA;