We have created a awesome themeFar far away,behind the word mountains, far from the countries
INFO ½ÇÀü DB¸ðµ¨¸µ°ú SQL°úÁ¤
¡á¿¹ 5_5_11 || m_sal CURSOR »ý¼ºDECLARE CURSOR m_sal IS SELECT SABUN, ENG_NAME, SALARY, JOIN_GBN_CODE FROM INSA WHERE (SALARY, JOIN_GBN_CODE) IN (SELECT MAX(SALARY) M_SAL, JOIN_GBN_CODE FROM INSA GROUP BY JOIN_GBN_CODE); v_SAB INSA.SABUN%TYPE; v_ENG INSA.ENG_NAME%TYPE; v_SAL INSA.SALARY%TYPE; v_GBN INSA.JOIN_GBN_CODE%TYPE; BEGIN OPEN m_sal; LOOP FETCH m_sal INTO v_SAB, v_ENG, v_SAL, v_GBN; EXIT WHEN m_sal %NOTFOUND; DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('SABUN: '||v_SAB||' '||'-NAME: '||v_ENG||' '||'-SALARY: '||v_SAL||' '||'-GBN: '|| v_GBN ); END LOOP; DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------'); CLOSE m_sal; END; /
¡á¿¹ 5_5_12 || DB Link »ý¼º ±ÇÇѺο©GRANT CREATE PUBLIC DATABASE LINK, DROP PUBLIC DATABASE LINK TO <USER>;
¡á¿¹ 5_5_13 || tnsnames.ora ÆÄÀÏ¿¡ ¿ø°Ý DB Á¢¼Ó Á¤º¸ Ãß°¡XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 000.000.0.00)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) );
¡á¿¹ 5_5_14 || DB Link »ý¼ºCONNECT scott/tiger
CREATE PUBLIC DATABASE LINK SEONGPDCONNECT TO **** IDENTIFIED BY **********USING 'XE';
¡á¿¹ 5_5_15 || NLS_CHARACTER üũSELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
¡á¿¹ 5_5_16 || DB Link »ç¿ë Çü½ÄSELECT * FROM <¿ø°Ý DBÅ×À̺í¸í>@<DB LINK À̸§>;
¡á¿¹ 5_5_17 || DB Link »ç¿ë ¿¹INSERT INTO INSA @SEONGPD (SABUN, ENG_NAME) VALUES ('2013052901','KIM');
SELECT SABUN, ENG_NAME FROM INSA @SEONGPD WHERE SABUN='2013052901';
UPDATE INSA @SEONGPD SET ENG_NAME='PARK' WHERE SABUN='2013052901';
DELETE INSA @SEONGPD WHERE SABUN='2013052901';
¡á¿¹ 5_5_18 || SYNONYMÀ» ÀÌ¿ëÇÑ DB Link »ç¿ëCREATE SYNONYM SEONG FOR INSA@SEONGPD;
¡á¿¹ 5_5_19 || DB Link »èÁ¦____DROP DATABASE____ LINK <DB LINK À̸§>;
¡á¿¹ 5_5_20 || JOB »ý¼º °úÁ¤CREATE TABLE JOB_TAB( ----Table »ý¼ºTEST_JOB VARCHAR2(25));
CREATE OR REPLACE PROCEDURE p_JOB ----PROCEDURE »ý¼ºISBEGIN INSERT INTO JOB_TAB(TEST_JOB) VALUES (TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')); COMMIT;END p_JOB;/
DECLARE ----JOB »ý¼º JOBNO NUMBER;BEGIN DBMS_JOB.SUBMIT(JOBNO, 'p_JOB;', SYSDATE, 'SYSDATE+1/24/6');END;/
¡á¿¹ 5_5_21 || MAX_SAL_LEAD METERIALIZED VIEW »ý¼ºCREATE MATERIALIZED VIEW MAX_SAL_LEADBUILD IMMEDIATEREFRESH FORCESTART WITH SYSDATENEXT SYSDATE+(1/24/1)ENABLE QUERY REWRITEASSELECT SABUN, ENG_NAME, NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY, SALARY, JOIN_GBN_CODEFROM INSAWHERE (JOIN_GBN_CODE, SALARY) IN(SELECT JOIN_GBN_CODE, MAX(SALARY) FROM INSA GROUP BY JOIN_GBN_CODE); ¡á¿¹ 5_5_22 || ÀÏ¹Ý SQLÀ» ÅëÇÑ µ¥ÀÌÅÍ Á¶È¸SELECT SABUN, ENG_NAME, NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY, SALARY, JOIN_GBN_CODEFROM INSAWHERE (JOIN_GBN_CODE, SALARY) IN(SELECT JOIN_GBN_CODE, MAX(SALARY) FROM INSA GROUP BY JOIN_GBN_CODE)ORDER BY SALARY DESC;
¡á¿¹ 5_5_23 || MAX_SAL_LEAD VIEW ÅëÇÑ µ¥ÀÌÅÍ Á¶È¸SELECT * FROM MAX_SAL_LEAD;
¡á¿¹ 5_5_24 || INSA Å×ÀÌºí ¼öÁ¤ ÈÄ METERIALIZED VIEW µ¥ÀÌÅÍ È®ÀÎUPDATE INSA SET SALARY=6000 WHERE SABUN='2013010122';