Ejemplos de copia de vistas

Estos ejemplos muestran el DDL que se genera al copiar vistas entre bases de datos heterogéneas.

El DDL generado se muestra en la página Vista previa de DDL del asistente Pegar objetos de base de datos.

La Tabla 1 muestra ejemplos del DDL que se genera para copiar vistas de una base de datos de origen Oracle Database 11g en una base de datos de destino DB2 Versión 9.7 para Linux®, UNIX® y Windows®.

Tabla 1. Ejemplos de copia de vistas de Oracle Database 11g en una base de datos DB2 Versión 9.7 para Linux, UNIX y Windows
Definición de vista de Oracle Database 11g DDL generado para la base de datos DB2 Versión 9.7
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

La Tabla 2 muestra un ejemplo del DDL que se genera para copiar vistas de una base de datos de origen DB2 Versión 9.7 para Linux, UNIX y Windows en una base de datos de destino Oracle Database 11g.

Tabla 2. Ejemplos de copia de vistas de una base de datos DB2 Versión 9.7 para Linux, UNIX y Windows en Oracle Database 11g
Definición de vista de la base de datos DB2 Versión 9.7 DDL generado para Oracle Database 11g
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; 

Comentarios