ビューのコピーの例

以下の例は、異種のデータベース間でビューをコピーする際に生成される DDL を示しています。

生成された DDL は、「データベース・オブジェクトの貼り付け」ウィザードの「DDL のプレビュー」ページに表示されます。

表 1 では、Oracle Database 11g ソース・データベースから DB2® Version 9.7 for Linux, UNIX, and Windows ターゲット・データベースへのビューのコピーで生成される DDL の例を示します。

表 1. Oracle Database 11g から DB2 Version 9.7 for Linux, UNIX, and Windows データベースへのビューのコピーの例
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 では、DB2 Version 9.7 for Linux, UNIX, and Windows ソース・データベースから Oracle Database 11g ターゲット・データベースへのビューのコピーで生成される DDL の例を示します。

表 2. DB2 Version 9.7 for Linux, UNIX, and Windows データベースから 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; 

フィードバック