■예 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 SEONGPD
CONNECT 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 생성
IS
BEGIN
   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_LEAD
BUILD IMMEDIATE
REFRESH FORCE
START WITH SYSDATE
NEXT SYSDATE+(1/24/1)
ENABLE QUERY REWRITE
AS
SELECT SABUN, ENG_NAME,
         NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY,
         SALARY, JOIN_GBN_CODE
FROM INSA
WHERE (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_CODE
FROM INSA
WHERE (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';