文档首页 > > 工具指南> DSC SQL语法迁移工具> SQL语法迁移参考> Teradata语法迁移> 视图迁移

视图迁移

分享
更新时间:2020/04/21 GMT+08:00

CREATE VIEW (缩写关键字为CV)和SELECT一同使用,用于创建视图。

Teradata和GaussDB T、GaussDB A 和 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 T、GaussDB A 和 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 T、GaussDB A 和 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 ) ;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!非常感谢您的反馈,我们会继续努力做到更好!
反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问