文档首页/ 数据仓库服务 GaussDB(DWS)/ 更多文档/ 工具指南（吉隆坡地区）/ DSC SQL语法迁移工具/ Teradata语法迁移/ 模式对象/ 视图迁移
更新时间：2023-03-17 GMT+08:00
查看PDF
分享

视图迁移

CREATE VIEW （缩写关键字为CV）和SELECT一同使用，用于创建视图。

Teradata和GaussDB(DWS)均支持关键词VIEW，但SELECT语句在迁移过程中会用()括住。详情请参见下方图片。

通过tdMigrateVIEWCHECKOPTIO....参数可以配置如何迁移包含WITH CHECK OPTION关键字的视图。如果该参数设置为false，则工具跳过该查询并记录日志。

如果CREATE VIEW包含LOCK关键字，则工具根据tdMigrateLOCKoption的设置决定如何迁移VIEW查询。

输入：CREATE VIEW

1
2
3
 
CREATE VIEW DP_STEDW.MY_PARAM 
AS 
SELECT RUNDATE FROM  DP_STEDW.DATE_TBL   WHERE   dummy = 1;

输出

1
2
3
4
5
 
CREATE OR REPLACE VIEW DP_STEDW.MY_PARAM 
AS 
SELECT RUNDATE
  FROM  DP_STEDW.DATE_TBL
        WHERE   dummy = 1;

输入：CREATE VIEW，使用FORCE关键字

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE 
OR REPLACE FORCE VIEW IS2010_APP_INFO (
  APP_ID, APP_SHORTNAME, APP_CHNAME, 
  APP_ENNAME
) AS 
select 
  t.app_id, 
  t.app_shortname, 
  t.app_chname, 
  t.app_enname 
from 
  newdrms.seas_app_info t 
WHERE 
  t.app_status <> '2';

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
CREATE
OR REPLACE
/*FORCE*/
VIEW IS2010_APP_INFO (
    APP_ID,
    APP_SHORTNAME,
    APP_CHNAME,
    APP_ENNAME ) AS
SELECT
    t.app_id,
    t.app_shortname,
    t.app_chname,
    t.app_enname
FROM
    newdrms.seas_app_info t
WHERE
t.app_status <> '2';

REPLACE VIEW

在Teradata中，REPLACE VIEW语句用于创建新视图，或重建现有视图。DSC将其迁移为GaussDB(DWS)中兼容的CREATE OR REPLACE VIEW语句中。

输入：REPLACE VIEW

1
2
3
4
5
6
7
 
REPLACE VIEW DP_STEDW.MY_PARAM AS SELECT
          RUNDATE
     FROM
          DP_STEDW.DATE_TBL
     WHERE
          dummy = 1
;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE
OR REPLACE VIEW DP_STEDW.MY_PARAM AS (
     SELECT
               RUNDATE
          FROM
               DP_STEDW.DATE_TBL
          WHERE
               dummy = 1
)
;

输入：REPLACE RECURSIVE VIEW

1
2
3
4
5
6
 
Replace RECURSIVE VIEW reachable_from (
emp_id,emp_name,DEPTH) 
AS (
SELECT root.emp_id,root.emp_name,0 AS DEPTH
FROM emp AS root
WHERE root.mgr_id IS NULL);

输出

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE VIEW reachable_from AS (
WITH RECURSIVE reachable_from (
emp_id,emp_name,DEPTH) 
AS (
SELECT root.emp_id,root.emp_name,0 AS DEPTH
FROM emp AS root
WHERE root.mgr_id IS NULL
) SELECT * FROM reachable_from);

REPLACE FUNCTION

输入

REPLACE FUNCTION up_load1.RPT_016_BUS_DATE()
RETURNS DATE 
LANGUAGE SQL 
CONTAINS SQL 
DETERMINISTIC 
SQL SECURITY DEFINER 
COLLATION INVOKER 
INLINE TYPE 1 
RETURN DATE'2017-08-22';

输出

CREATE OR REPLACE FUNCTION up_load1.RPT_016_BUS_DATE()
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
SECURITY DEFINER
AS 
$$
SELECT CAST('2017-08-20' AS DATE)
$$
;

CHECK OPTION

通过 tdMigrateVIEWCHECKOPTIO...参数可以配置如何迁移包含CHECK OPTION关键字的视图。

如果源数据库中出现含有CHECK OPRTION关键词的视图，则工具在目标数据库中注释掉CHECK OPRTION。

输入：VIEW，使用CHECK OPTION

1
2
3
4
5
6
 
CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

输出（tdMigrateVIEWCHECKOPTION=True）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE
     OR REPLACE VIEW mgr15 AS (
          SELECT
                    *
               FROM
                    employee
               WHERE
                    manager_id = 15 /*WITH CHECK OPTION */
     )
;

输出（tdMigrateVIEWCHECKOPTION=False）

1
2
3
4
5
6
 
CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

VIEW WITH RECURSIVE

GaussDB(DWS)不支持Teradata关键词RECURSIVE VIEW。因此，工具采用VIEW WITH RECURSIVE替代该关键词，如下图所示。

图1 输入视图：CREATE RECURSIVE VIEW
图2 输出视图

VIEW WITH ACCESS LOCK

通过tdMigrateLOCKOption参数可以配置如何迁移包含LOCK关键字的查询。如果tdMigrateLOCKOption设置为false，则该工具在迁移时将跳过该查询并记录日志。

输入：VIEW，使用ACCESS LOCK

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN 
 AS 
 LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS  FOR ACCESS 
 ( SELECT RUN_ID, PRODUCT_ID, CURRENCY
            , CASHFLOW, ENTITY, LCR
            , TIME_BUCKET, MT, Ctl_Id
            , File_Id, Business_Date
      FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ;

输出

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN 
 AS 
/* LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS */
 ( SELECT RUN_ID, PRODUCT_ID, CURRENCY
            , CASHFLOW, ENTITY, LCR
            , TIME_BUCKET, MT, Ctl_Id
            , File_Id, Business_Date
      FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ;
父主题： 模式对象