뷰 복사의 예

다음 예는 이종 데이터베이스 간에 뷰를 복사할 때 생성되는 DDL을 보여줍니다.

생성된 DDL은 데이터베이스 오브젝트 붙여넣기 마법사의 DDL 미리보기 페이지에 표시됩니다.

표 1은 Oracle Database 11g 소스 데이터베이스에서 Linux, UNIX 및 Windows용 DB2® 버전 9.7 목표 데이터베이스로 뷰를 복사하는 경우 생성되는 DDL의 예를 보여줍니다.

표 1. Oracle Database 11g에서 Linux, UNIX 및 Windows용 DB2 버전 9.7 데이터베이스로 뷰를 복사하는 예
Oracle Database 11g 뷰 정의 DB2 버전 9.7 데이터베이스에 대해 생성된 DDL
CREATE VIEW OM_view
   AS SELECT OM_name, sal*12 OM_sal
   FROM OM_table
   WHERE OM_number = 30;
CREATE VIEW OM_VIEW
   AS SELECT OM_name, sal*12 OM_sal
   FROM OM_table
   WHERE OM_number = 30;
CREATE VIEW OM_view1
   AS SELECT OM_emp_number,
             OM_name,
             OM_dept_number,
             OM_id
   FROM OM_table
   WHERE OM_id='VP'
      OR OM_id='MANAGER'
      OR OM_id='S/W Eng'
   WITH check option;
CREATE VIEW OM_view1
   AS SELECT OM_emp_number,
             OM_name,
             OM_dept_number,
             OM_id
   FROM OM_table
   WHERE OM_id='VP'
      OR OM_id='MANAGER'
      OR OM_id='S/W Eng'
   WITH check option;
CREATE VIEW OM_view2
  (OM_id, OM_name, OM_email,
   CONSTRAINT id_pk PRIMARY KEY (OM_id)
   RELY DISABLE NOVALIDATE
  )
   AS SELECT OM_id,
             OM_name,
             OM_email
   FROM OM_table;
CREATE VIEW DB2ADMIN.OM_view2
  (OM_id, OM_name, OM_email)
   AS SELECT OM_id,
             OM_name,
             OM_email
   FROM OM_table; 
CREATE VIEW OM_view_instr
   AS SELECT count(*) AS c1
   FROM OM_table6
   WHERE instr(ch30, 'character')>0;
CREATE VIEW OM_view_instr
   AS SELECT count(*) AS c1
   FROM OM_table6
   WHERE instr(ch30, 'character')>0; 
CREATE VIEW vw_1 AS
   SELECT * FROM vw_str_instr;
CREATE VIEW DB2ADMIN.VW_1
   AS SELECT "C1"
   FROM vw_str_instr
CREATE VIEW vw_2 AS
   SELECT * FROM vw_1;
CREATE VIEW DB2ADMIN.VW_2
   AS SELECT "C1" FROM vw_1
CREATE VIEW OM2AV010_1 AS
   SELECT large FROM large_tbl;

CREATE VIEW OM2AV010_2 AS
   SELECT title_1 FROM person;

CREATE VIEW OM2AV010_3 AS
   SELECT * FROM OM2AV010_1,OM2AV010_2;
CREATE VIEW DB2ADMIN.OM2AV010_1
   AS SELECT large FROM large_tbl

CREATE VIEW DB2ADMIN.OM2AV010_2
   AS SELECT title_1 FROM person

CREATE VIEW DB2ADMIN.OM2AV010_3
   AS SELECT "LARGE","TITLE_1"
   FROM OM2AV010_1,OM2AV010_2

표 2는 Linux, UNIX 및 Windows용 DB2 버전 9.7 소스 데이터베이스에서 Oracle Database 11g 목표 데이터베이스로 뷰를 복사하는 경우 생성되는 DDL의 예를 보여줍니다.

표 2. Linux, UNIX 및 Windows용 DB2 버전 9.7 데이터베이스에서 Oracle Database 11g로 뷰를 복사하는 예
DB2 버전 9.7 데이터베이스 뷰 정의 Oracle Database 11g에 대해 생성된 DDL
CREATE VIEW OM_LEAD
   AS SELECT OM_NO,
             OM_NAME,
             OM_EMP,
             OM_BASE+OM_BONUS AS OM_PAY
   FROM OM_table1, OM_table2
   WHERE OM_EMP = OM_NAME
     AND OM_EMPNO > 3;
CREATE OR REPLACE VIEW SYSTEM.OM_LEAD
   AS SELECT OM_NO,
             OM_NAME,
             OM_EMP,
             OM_BASE+OM_BONUS AS OM_PAY
   FROM OM_table1, OM_table2
   WHERE OM_EMP = OM_NAME
     AND OM_EMPNO > 3;
CREATE VIEW OM_view_proj
   AS SELECT * FROM OM_table3
   WHERE SUBSTR(OM_Name, 1, 2) = 'OM';
CREATE OR REPLACE VIEW SYSTEM.OM_view_proj
   AS SELECT "OM_NO",
             "OM_NAME",
             "OM_EMP",
             "OM_DATE"
   FROM OM_table3
   WHERE SUBSTR(OM_Name, 1, 2) = 'OM';
CREATE VIEW OM_VIEW_2
  (OM_NO, OM_NAME, OM_TITLE, OM_DATE)
   AS SELECT OM_NO,
             OM_NAME,
             OM_TITLE,
             OM_DATE
   FROM OM_table4
   WHERE OM_DEPT=2
   WITH CHECK OPTION;
CREATE VIEW SYSTEM.OM_VIEW_2
  (OM_NO, OM_NAME, OM_TITLE, OM_DATE)
   AS SELECT OM_NO,
             OM_NAME,
             OM_TITLE,
             OM_DATE
   FROM OM_table4
   WHERE OM_DEPT=2
CREATE VIEW OM_VIEW5
  (OM_NO, OM_NAME, OM_TITLE, OM_DATE,OM_email)
   AS SELECT OM_NO,
             OM_NAME,
             OM_TITLE,
              OM_DATE,OM_email
   FROM OM_table5
   WHERE OM_DEPT=12
   WITH LOCAL CHECK OPTION;

CREATE VIEW OM_view6
AS SELECT * FROM OM_VIEW5;

CREATE VIEW OM_view7
AS SELECT * FROM OM_VIEW6;
CREATE VIEW OM_VIEW5
   (OM_NO, OM_NAME, OM_TITLE, OM_DATE,OM_email)
   AS SELECT OM_NO,
             OM_NAME,
             OM_TITLE,
             OM_DATE,
             OM_email
   FROM OM_table5
   WHERE OM_DEPT=12

CREATE VIEW OM_view6
AS SELECT * FROM OM_VIEW5;

CREATE VIEW OM_view7
AS SELECT * FROM OM_VIEW6; 

피드백