Generate custom SQL code with Template Language in Erwin-优快云博客

本文介绍如何使用Erwin的数据仓库工具通过自定义模板自动生成除手动创建外的所有表格视图。利用Erwin的前向工程功能，创建了三个模板以实现视图的创建与删除。

Secenario: I want to generate view for every table in data warehouse automately, except some views create manually. Fortunately, Erwin privode a back door in the function of Forward Engineer, so it could be implemented simply.

Reference Doc:

Template Language and Macro Reference.pdf

Editing Forward Engineering Templates.pdf

Ok, let's take a look at how to do that. Below is the steps.

1. open Erwin, click menu item Tool->Forward Engineer->Schema Generation.

2. Edit database template, the template editor will pop up.

3. create 3 template using the following code.

SPItemBegin [keep format] = Is View Existing
[
ForEachOfType("View")
{
    @if(Equal(Property("Name"),"%1"))
    {
        "Success"
    }
}
]
SPItemEnd

SPItemBegin [keep format] = Create View Of Entity
[
    "CREATE VIEW views."< QuotedName >
    "/nAS"
    "/nSELECT/n/t "
    ForEachOwnee("Attribute")
    {
        ListSeparator("/n/t,")
        Property("Physical_Name")
    }
    "/nFROM "
    [ FE::OwnerOverride( "true" ) "." ] < QuotedName >
    FE::EndOfStatement
]
SPItemEnd

SPItemBegin [keep format] = Drop View Of Entity

FE::Bucket( "35" )
[
    @if(ExecuteTest("Is View Existing","",Property("Physical_Name")))
    {

    }

    @elseif(Equal(Property("Name_Qualifier"),"adw"))
    {
        ["DROP VIEW views."Property("Physical_Name")]
        "/n/n"
        FE::EndOfStatement
    }
]
SPItemEnd

4. Modify template "Create Entity", "Drop Entity", they are the entry point of Forward engineer.

SPItemBegin [keep format] = Drop Entity
    [
    ...
        [
        FE::Bucket( "35" )
        Execute( "Drop View Of Entity")
        ]
    ...
    ]
SPItemEnd

SPItemBegin [keep format] = Create Entity
    [
    ...
        [
            FE::Bucket( "140" )

            Execute( "Create View Of Entity" )
        ]
    ...
    ]
SPItemEnd

5. Save what you made to another fet file. At this point, you can try this new function in "Schema generation" dialogue by clicking Preview buttion. If drop veiw and create view statement is shown, that's correct.