We have created a awesome themeFar far away,behind the word mountains, far from the countries
INFO ½ÇÀü DB¸ðµ¨¸µ°ú SQL°úÁ¤
¡á¿¹ 5_5_1 || VIEW »ý¼º ¿¹CREATE VIEW v_INSAAS SELECT SABUN, NAME, ENG_NAME, CMP_REG_NO, JOIN_GBN_CODE FROM INSA WHERE JOIN_GBN_CODE = 'RGL';
SELECT SABUN, ENG_NAME, JOIN_GBN_CODEFROM v_INSA;
¡á¿¹ 5_5_2 || µÎ °³ ÀÌ»ó Å×À̺í·Î VIEW »ý¼º ¿¹Á¦CREATE VIEW CP_INSAAS SELECT I.SABUN, I.ENG_NAME, C.CMP_NAME, C.CMP_ZIP ZIPCODE, C.CMP_ADDR1 ADDRFROM INSA I, INSA_COMPANY CWHERE 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¸¦ ÀÌ¿ëÇÑ INSERTINSERT 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¸¦ ÀÌ¿ëÇÑ DELETEDELETE CP_INSAWHERE 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') NULLABLEFROM USER_TAB_COLUMNS A, USER_TAB_COMMENTS B, USER_COL_COMMENTS CWHERE A.TABLE_NAME = B.TABLE_NAMEAND A.TABLE_NAME = C.TABLE_NAMEAND A.COLUMN_NAME = C.COLUMN_NAMEORDER 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 CWHERE A.SID=B.SIDAND B.ID1=C.OBJECT_IDAND B.TYPE='TM'AND C.OBJECT_NAME='CMM_CODE_DETAIL';
¡á¿¹ 5_5_10 || SYNONYM »ý¼º ¿¹CREATE PUBLIC SYNONYM SY_INSAFOR INSA.v_INSA;
DESC SY_INSA;